Data bound fruit basket with SQL Server Compact Edition
- From: Jamal Mazrui <empower@xxxxxxxxx>
- To: programmingblind@xxxxxxxxxxxxx
- Date: Tue, 4 Dec 2007 17:12:53 -0500 (EST)
From the archive at
http://www.EmpowermentZone.com/ssce_fruit.zip
Previously, I did a fruit basket program in C# 2.0 with data binding via
Microsoft Access or SQLite, available at
http://www.EmpowermentZone.com/db_fruit.zip
The present program is written in C# 3.0 with data binding via Microsoft
SQL Server Compact Edition 3.5 (previously called Mobile or EveryWhere
Edition). This version of SQL Server was released with the .NET Framework
3.5 and Visual Studio 2008. It is a freely distributable, embeddable
database system, designed for both mobile devices and desktop computers
(but not web servers).
A Database is contained in a single file with a .sdf extension, and may be
ported simply by copying the file. The software's home page is at
http://www.microsoft.com/sql/editions/compact/default.mspx
The fruit basket program requires the runtime engine available from there.
Designer support for compact database applications is provided in Visual
Studio 2008, including the Express Editions.
The code for this program tries to be minimalistic by using a layout
panel, inferred types, in-line properties, and anonymous event handlers.
The ssee_fruit.sdf database is created if not found. It tracks fruit
added to or deleted from the basket, so the same content is present at the
start of a new program session.
Jamal
/*
content of ssce_fruit.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, "ssce_fruit.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 table adapter
var sSelect = "select * from " + sTable;
var selectCommand = new SqlCeCommand(sSelect, connect);
var adapter = new SqlCeDataAdapter(selectCommand);
var builder = new SqlCeCommandBuilder(adapter);
var table = new DataTable(sTable);
adapter.Fill(table);
// 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 = table, DisplayMember = 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 {
table.Rows.Add(null, sFruit);
adapter.Update(table);
lstBasket.SelectedIndex = lstBasket.Items.Count - 1;
txtFruit.Clear();
} // if fruit to add
}; // add
// Transfer new ID generated by database into table
table.RowChanged += (o, e) => {
if(e.Action != DataRowAction.Add) return;
var sTransfer = "SELECT @@identity";
var transferCommand = new SqlCeCommand(sTransfer, connect);
e.Row["ID"] = transferCommand.ExecuteScalar( );
}; // transfer
// Define Delete event handler;
btnDelete.Click += (o,e) => {
var iFruit = lstBasket.SelectedIndex;
if (iFruit == -1) MessageBox.Show("No fruit to delete!", "Alert");
else {
table.Rows[iFruit].Delete();
adapter.Update(table);
if (iFruit == lstBasket.Items.Count) iFruit--;
lstBasket.SelectedIndex = 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 ssce_fruit.cs
__________
View the list's information and change your settings at
//www.freelists.org/list/programmingblind
Other related posts:
