Re: Data bound fruit basket with SQL Server Compact Edition

After studying documentation further, below is a shorter version that uses
a ResultSet cursor instead of CommandBuilder, DataAdapter, DataTable, and
the transfer ID event handler.

Jamal

/*
content of resultset.cs
Fruit Basket program in C# 3.0 with SQL Server Compact Edition 3.5
Public domain by Jamal Mazrui
*/

// Import namespaces
using System;
using System.Data;
using System.Data.SqlServerCe;
using System.IO;
using System.Windows.Forms;

// Define class
class FruitBasket {

// Define entry point of program
static void Main() {
// Define full path of database file
var sSdf = Path.Combine(Application.StartupPath, "resultset.sdf");
// Define database connection string
var sConnect = "Data Source=" + sSdf;

// Define connection
using (var connect = new SqlCeConnection(sConnect)) {
var sTable = "Basket";
var sColumn = "Fruit";

// Open connection if database exists, else create it
if (File.Exists(sSdf)) connect.Open();
else {
using (var engine = new SqlCeEngine(sConnect)) engine.CreateDatabase();
connect.Open();
var sCreate = "create table " + sTable + " (ID int identity primary key, "
+ sColumn + " nvarchar(20))";
var createCommand = new SqlCeCommand(sCreate, connect);
createCommand.ExecuteNonQuery();
} // if database exists

// Define result set
var sSelect = "select * from " + sTable;
var selectCommand = new SqlCeCommand(sSelect, connect);
var resultSet = selectCommand.ExecuteResultSet(ResultSetOptions.Scrollable
| ResultSetOptions.Updatable);

// Create controls;
var tlp = new TableLayoutPanel {ColumnCount = 3, RowCount = 2};
var lblFruit = new Label {Text = "&Fruit:", Parent = tlp};
var txtFruit = new TextBox {Parent = tlp};
var btnAdd = new Button {Text = "&Add", Parent = tlp};
var lblBasket = new Label {Text = "&Basket:", Parent = tlp};
var lstBasket = new ListBox {DataSource = resultSet, DisplayMember =
sColumn, ValueMember = sColumn, Parent = tlp};
var btnDelete = new Button {Text = "&Delete", Parent = tlp};

// Define Add event handler;
btnAdd.Click += (o, e) => {
var sFruit = txtFruit.Text.Trim();
if (sFruit.Length == 0) MessageBox.Show("No fruit to add!", "Alert");
else {
var record = resultSet.CreateRecord();
record.SetString(1, sFruit);
resultSet.Insert(record);
int iFruit = lstBasket.Items.Count - 1;
lstBasket.SelectedIndex = iFruit;
txtFruit.Clear();
} // if fruit to add
}; // add

// Define Delete event handler;
btnDelete.Click += (o,e) => {
int iFruit = lstBasket.SelectedIndex;
if (iFruit == -1) MessageBox.Show("No fruit to delete!", "Alert");
else {
resultSet.Delete();
if (iFruit == lstBasket.Items.Count) iFruit--;
resultSet.ReadAbsolute(iFruit);
} // if fruit to delete
}; // delete

// Finalize dialog;
var dlg = new Form {Text = "Fruit Basket", AcceptButton = btnAdd,
StartPosition = FormStartPosition.CenterScreen, AutoSize = true,
AutoSizeMode = AutoSizeMode.GrowAndShrink};
dlg.Controls.Add(tlp);
// Define closing event handler
dlg.Closing += (o, e) => e.Cancel = (MessageBox.Show("Close program?",
"Confirm", MessageBoxButtons.YesNo) == DialogResult.No);
dlg.ShowDialog();
connect.Close();
} // using connect
} // Main method
} // FruitBasket class

// End of resultset.cs

__________
View the list's information and change your settings at 
http://www.freelists.org/list/programmingblind

Other related posts: