Source Code : Form1.cs
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Data.SqlServerCe;
namespace System.Data.SqlServerCe
{
public partial class Form1 : Form
{
SqlCeConnection connection;
SqlCeEngine engine;
SqlCeCommand command;
SqlCeDataReader dataReader;
string SelectedId = "";
string Str;
int itemIndex;
public Form1()
{
InitializeComponent();
engine = new SqlCeEngine("Data Source='DataFile.sdf';");
connection = new SqlCeConnection(engine.LocalConnectionString);
}
private void Form1_Load(object sender, EventArgs e)
{
if (!(File.Exists("DataFile.sdf")))
{
engine.CreateDatabase();
connection.Open();
command = connection.CreateCommand();
command.CommandText = "CREATE TABLE DataFile (Id int IDENTITY(1,1), Name nvarchar(50) NOT NULL, City nvarchar(50) NOT NULL, Telephone nvarchar(50), EmailAddress nvarchar(50))";
command.ExecuteScalar();
connection.Close();
}
else
{
ShowMyDatabase();
}
}
private void ShowMyDatabase()
{
connection.Open();
command = connection.CreateCommand();
command.CommandText = "SELECT * FROM DataFile ORDER BY id";
dataReader = command.ExecuteReader();
itemIndex = 0;
listView1.Items.Clear();
while (dataReader.Read())
{
for (int i = 0; i < dataReader.FieldCount; i++)
{
Str = dataReader.GetValue(i).ToString();
switch (i)
{
case 0: listView1.Items.Add(Str); break;
case 1: listView1.Items[itemIndex].SubItems.Add(Str); break;
case 2: listView1.Items[itemIndex].SubItems.Add(Str); break;
case 3: listView1.Items[itemIndex].SubItems.Add(Str); break;
case 4: listView1.Items[itemIndex].SubItems.Add(Str); break;
};
}
itemIndex++;
}
connection.Close();
}
private void btnAdd_Click(object sender, EventArgs e)
{
connection.Open();
command.CommandText = "INSERT INTO DataFile(Name, City,Telephone,EmailAddress) VALUES(?,?,?,?)";
command.Parameters.Add("Name", this.txtName.Text);
command.Parameters.Add("City", this.txtCity.Text);
command.Parameters.Add("Telephone", this.txtTelephone.Text);
command.Parameters.Add("EmailAddress", this.txtEmail.Text);
command.ExecuteScalar();
connection.Close();
this.txtName.Clear();
this.txtCity.Clear();
this.txtTelephone.Clear();
this.txtEmail.Clear();
ShowMyDatabase();
this.txtName.Focus();
}
private void listView1_ItemSelectionChanged(object sender, ListViewItemSelectionChangedEventArgs e)
{
if (e.IsSelected)
{
btnDelete.Enabled = true;
btnChange.Enabled = true;
SelectedId = listView1.Items[e.ItemIndex].Text;
for (int i = 1; i < listView1.Items[e.ItemIndex].SubItems.Count; i++)
{
switch (i)
{
case 1: this.txtName.Text = listView1.Items[e.ItemIndex].SubItems[i].Text; break;
case 2: this.txtCity.Text = listView1.Items[e.ItemIndex].SubItems[i].Text; break;
case 3: this.txtTelephone.Text = listView1.Items[e.ItemIndex].SubItems[i].Text; break;
case 4: this.txtEmail.Text = listView1.Items[e.ItemIndex].SubItems[i].Text; break;
}
}
}
else
{
this.txtName.Text = "";
this.txtCity.Text = "";
this.txtTelephone.Text = "";
this.txtEmail.Text = "";
btnDelete.Enabled = false;
btnChange.Enabled = false;
}
}
private void btnDelete_Click(object sender, EventArgs e)
{
if (listView1.SelectedItems.Count != 0)
{
connection.Open();
command = connection.CreateCommand();
command.CommandText = "DELETE FROM DataFile WHERE (Id = ?)";
command.Parameters.Add("Id", SelectedId);
command.ExecuteScalar();
connection.Close();
ShowMyDatabase();
this.txtName.Clear();
this.txtCity.Clear();
this.txtTelephone.Clear();
this.txtEmail.Clear();
}
}
private void btnChange_Click(object sender, EventArgs e)
{
if (listView1.SelectedItems.Count != 0)
{
connection.Open();
command = connection.CreateCommand();
command.CommandText = "UPDATE DataFile SET Name = ?, City =?,Telephone=?, EmailAddress=? WHERE Id = ?";
command.Parameters.Add("Name", this.txtName.Text);
command.Parameters.Add("City", this.txtCity.Text);
command.Parameters.Add("Telephone", this.txtTelephone.Text);
command.Parameters.Add("EmailAddress", this.txtEmail.Text);
command.Parameters.Add("Id", SelectedId);
command.ExecuteScalar();
connection.Close();
ShowMyDatabase();
this.txtName.Clear();
this.txtCity.Clear();
this.txtTelephone.Clear();
this.txtEmail.Clear();
}
}
private void btnSearch_Click(object sender, EventArgs e)
{
connection.Open();
command = connection.CreateCommand();
if (radSearchbyName.Checked)
{
command.CommandText = "SELECT * FROM DataFile WHERE Name='" + this.txtSearchName.Text + "'";
}
else
{
command.CommandText = "SELECT * FROM DataFile WHERE City='" + this.txtSearchCity.Text + "'";
}
dataReader = command.ExecuteReader();
itemIndex = 0;
listView1.Items.Clear();
while (dataReader.Read())
{
for (int i = 0; i < dataReader.FieldCount; i++)
{
Str = dataReader.GetValue(i).ToString();
switch (i)
{
case 0: listView1.Items.Add(Str); break;
case 1: listView1.Items[itemIndex].SubItems.Add(Str); break;
case 2: listView1.Items[itemIndex].SubItems.Add(Str); break;
case 3: listView1.Items[itemIndex].SubItems.Add(Str); break;
case 4: listView1.Items[itemIndex].SubItems.Add(Str); break;
};
}
itemIndex++;
}
connection.Close();
}
private void txtName_TextChanged(object sender, EventArgs e)
{
if (txtName.Text.Length > 0)
{
btnAdd.Enabled = true;
}
else
{
btnAdd.Enabled = false;
btnChange.Enabled = false;
btnDelete.Enabled = false;
}
}
private void radioBox_CheckedChanged(object sender, EventArgs e)
{
if (radSearchbyName.Checked)
{
txtSearchName.Enabled = true;
txtSearchCity.Enabled = false;
}
else
{
txtSearchName.Enabled = false;
txtSearchCity.Enabled = true;
}
}
private void linkLabel1_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
{
ShowMyDatabase();
}
}
}
Source Code : Form1.Designer.cs
namespace System.Data.SqlServerCe
{
partial class Form1
{
///
/// Required designer variable.
///
private System.ComponentModel.IContainer components = null;
///
/// Clean up any resources being used.
///
/// true if managed resources should be disposed; otherwise, false.
protected override void Dispose(bool disposing)
{
if (disposing && (components != null))
{
components.Dispose();
}
base.Dispose(disposing);
}
#region Windows Form Designer generated code
///
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
///
private void InitializeComponent()
{
System.ComponentModel.ComponentResourceManager resources = new System.ComponentModel.ComponentResourceManager(typeof(Form1));
this.listView1 = new System.Windows.Forms.ListView();
this.columnHeader1 = new System.Windows.Forms.ColumnHeader();
this.columnHeader2 = new System.Windows.Forms.ColumnHeader();
this.columnHeader3 = new System.Windows.Forms.ColumnHeader();
this.columnHeader4 = new System.Windows.Forms.ColumnHeader();
this.columnHeader5 = new System.Windows.Forms.ColumnHeader();
this.groupBox1 = new System.Windows.Forms.GroupBox();
this.groupBox2 = new System.Windows.Forms.GroupBox();
this.txtName = new System.Windows.Forms.TextBox();
this.txtCity = new System.Windows.Forms.TextBox();
this.txtTelephone = new System.Windows.Forms.TextBox();
this.txtEmail = new System.Windows.Forms.TextBox();
this.label4 = new System.Windows.Forms.Label();
this.label3 = new System.Windows.Forms.Label();
this.label2 = new System.Windows.Forms.Label();
this.label1 = new System.Windows.Forms.Label();
this.groupBox3 = new System.Windows.Forms.GroupBox();
this.btnDelete = new System.Windows.Forms.Button();
this.btnChange = new System.Windows.Forms.Button();
this.btnAdd = new System.Windows.Forms.Button();
this.groupBox4 = new System.Windows.Forms.GroupBox();
this.radSearchbyCity = new System.Windows.Forms.RadioButton();
this.radSearchbyName = new System.Windows.Forms.RadioButton();
this.btnSearch = new System.Windows.Forms.Button();
this.txtSearchName = new System.Windows.Forms.TextBox();
this.txtSearchCity = new System.Windows.Forms.TextBox();
this.label5 = new System.Windows.Forms.Label();
this.label6 = new System.Windows.Forms.Label();
this.linkLabel1 = new System.Windows.Forms.LinkLabel();
this.groupBox1.SuspendLayout();
this.groupBox2.SuspendLayout();
this.groupBox3.SuspendLayout();
this.groupBox4.SuspendLayout();
this.SuspendLayout();
//
// listView1
//
this.listView1.Columns.AddRange(new System.Windows.Forms.ColumnHeader[] {
this.columnHeader1,
this.columnHeader2,
this.columnHeader3,
this.columnHeader4,
this.columnHeader5});
this.listView1.FullRowSelect = true;
this.listView1.GridLines = true;
this.listView1.HideSelection = false;
this.listView1.Location = new System.Drawing.Point(4, 11);
this.listView1.MultiSelect = false;
this.listView1.Name = "listView1";
this.listView1.Size = new System.Drawing.Size(497, 178);
this.listView1.TabIndex = 10;
this.listView1.UseCompatibleStateImageBehavior = false;
this.listView1.View = System.Windows.Forms.View.Details;
this.listView1.ItemSelectionChanged += new System.Windows.Forms.ListViewItemSelectionChangedEventHandler(this.listView1_ItemSelectionChanged);
//
// columnHeader1
//
this.columnHeader1.Text = "Id";
this.columnHeader1.Width = 40;
//
// columnHeader2
//
this.columnHeader2.Text = "Name";
this.columnHeader2.Width = 110;
//
// columnHeader3
//
this.columnHeader3.Text = "City";
this.columnHeader3.Width = 110;
//
// columnHeader4
//
this.columnHeader4.Text = "Telephone Number";
this.columnHeader4.Width = 120;
//
// columnHeader5
//
this.columnHeader5.Text = "Email";
this.columnHeader5.Width = 110;
//
// groupBox1
//
this.groupBox1.Controls.Add(this.linkLabel1);
this.groupBox1.Controls.Add(this.listView1);
this.groupBox1.Location = new System.Drawing.Point(8, 4);
this.groupBox1.Name = "groupBox1";
this.groupBox1.Size = new System.Drawing.Size(507, 211);
this.groupBox1.TabIndex = 100;
this.groupBox1.TabStop = false;
//
// groupBox2
//
this.groupBox2.Controls.Add(this.txtName);
this.groupBox2.Controls.Add(this.txtCity);
this.groupBox2.Controls.Add(this.txtTelephone);
this.groupBox2.Controls.Add(this.txtEmail);
this.groupBox2.Controls.Add(this.label4);
this.groupBox2.Controls.Add(this.label3);
this.groupBox2.Controls.Add(this.label2);
this.groupBox2.Controls.Add(this.label1);
this.groupBox2.Location = new System.Drawing.Point(8, 221);
this.groupBox2.Name = "groupBox2";
this.groupBox2.Size = new System.Drawing.Size(266, 119);
this.groupBox2.TabIndex = 13;
this.groupBox2.TabStop = false;
//
// txtName
//
this.txtName.Location = new System.Drawing.Point(103, 13);
this.txtName.Name = "txtName";
this.txtName.Size = new System.Drawing.Size(155, 20);
this.txtName.TabIndex = 0;
this.txtName.TextChanged += new System.EventHandler(this.txtName_TextChanged);
//
// txtCity
//
this.txtCity.Location = new System.Drawing.Point(103, 37);
this.txtCity.Name = "txtCity";
this.txtCity.Size = new System.Drawing.Size(155, 20);
this.txtCity.TabIndex = 1;
//
// txtTelephone
//
this.txtTelephone.Location = new System.Drawing.Point(103, 61);
this.txtTelephone.Name = "txtTelephone";
this.txtTelephone.Size = new System.Drawing.Size(155, 20);
this.txtTelephone.TabIndex = 2;
//
// txtEmail
//
this.txtEmail.Location = new System.Drawing.Point(103, 85);
this.txtEmail.Name = "txtEmail";
this.txtEmail.Size = new System.Drawing.Size(155, 20);
this.txtEmail.TabIndex = 3;
//
// label4
//
this.label4.AutoSize = true;
this.label4.Location = new System.Drawing.Point(15, 88);
this.label4.Name = "label4";
this.label4.Size = new System.Drawing.Size(82, 13);
this.label4.TabIndex = 3;
this.label4.Text = "Email Address :";
//
// label3
//
this.label3.AutoSize = true;
this.label3.Location = new System.Drawing.Point(15, 64);
this.label3.Name = "label3";
this.label3.Size = new System.Drawing.Size(67, 13);
this.label3.TabIndex = 2;
this.label3.Text = "Telephone :";
//
// label2
//
this.label2.AutoSize = true;
this.label2.Location = new System.Drawing.Point(15, 40);
this.label2.Name = "label2";
this.label2.Size = new System.Drawing.Size(33, 13);
this.label2.TabIndex = 1;
this.label2.Text = "City :";
//
// label1
//
this.label1.AutoSize = true;
this.label1.Location = new System.Drawing.Point(15, 16);
this.label1.Name = "label1";
this.label1.Size = new System.Drawing.Size(44, 13);
this.label1.TabIndex = 0;
this.label1.Text = "Name :";
//
// groupBox3
//
this.groupBox3.Controls.Add(this.btnDelete);
this.groupBox3.Controls.Add(this.btnChange);
this.groupBox3.Controls.Add(this.btnAdd);
this.groupBox3.Location = new System.Drawing.Point(8, 344);
this.groupBox3.Name = "groupBox3";
this.groupBox3.Size = new System.Drawing.Size(266, 43);
this.groupBox3.TabIndex = 3;
this.groupBox3.TabStop = false;
//
// btnDelete
//
this.btnDelete.Enabled = false;
this.btnDelete.Location = new System.Drawing.Point(171, 9);
this.btnDelete.Name = "btnDelete";
this.btnDelete.Size = new System.Drawing.Size(87, 30);
this.btnDelete.TabIndex = 6;
this.btnDelete.Text = "Delete";
this.btnDelete.UseVisualStyleBackColor = true;
this.btnDelete.Click += new System.EventHandler(this.btnDelete_Click);
//
// btnChange
//
this.btnChange.Enabled = false;
this.btnChange.Location = new System.Drawing.Point(90, 9);
this.btnChange.Name = "btnChange";
this.btnChange.Size = new System.Drawing.Size(75, 30);
this.btnChange.TabIndex = 5;
this.btnChange.Text = "Change";
this.btnChange.UseVisualStyleBackColor = true;
this.btnChange.Click += new System.EventHandler(this.btnChange_Click);
//
// btnAdd
//
this.btnAdd.Enabled = false;
this.btnAdd.Location = new System.Drawing.Point(9, 9);
this.btnAdd.Name = "btnAdd";
this.btnAdd.Size = new System.Drawing.Size(75, 30);
this.btnAdd.TabIndex = 4;
this.btnAdd.Text = "Add";
this.btnAdd.UseVisualStyleBackColor = true;
this.btnAdd.Click += new System.EventHandler(this.btnAdd_Click);
//
// groupBox4
//
this.groupBox4.Controls.Add(this.radSearchbyCity);
this.groupBox4.Controls.Add(this.radSearchbyName);
this.groupBox4.Controls.Add(this.btnSearch);
this.groupBox4.Controls.Add(this.txtSearchName);
this.groupBox4.Controls.Add(this.txtSearchCity);
this.groupBox4.Controls.Add(this.label5);
this.groupBox4.Controls.Add(this.label6);
this.groupBox4.Location = new System.Drawing.Point(287, 221);
this.groupBox4.Name = "groupBox4";
this.groupBox4.Size = new System.Drawing.Size(228, 166);
this.groupBox4.TabIndex = 12;
this.groupBox4.TabStop = false;
this.groupBox4.Text = "Search by";
//
// radSearchbyCity
//
this.radSearchbyCity.AutoSize = true;
this.radSearchbyCity.Location = new System.Drawing.Point(77, 19);
this.radSearchbyCity.Name = "radSearchbyCity";
this.radSearchbyCity.Size = new System.Drawing.Size(42, 17);
this.radSearchbyCity.TabIndex = 8;
this.radSearchbyCity.TabStop = true;
this.radSearchbyCity.Text = "City";
this.radSearchbyCity.UseVisualStyleBackColor = true;
//
// radSearchbyName
//
this.radSearchbyName.AutoSize = true;
this.radSearchbyName.Checked = true;
this.radSearchbyName.Location = new System.Drawing.Point(18, 19);
this.radSearchbyName.Name = "radSearchbyName";
this.radSearchbyName.Size = new System.Drawing.Size(53, 17);
this.radSearchbyName.TabIndex = 7;
this.radSearchbyName.TabStop = true;
this.radSearchbyName.Text = "Name";
this.radSearchbyName.UseVisualStyleBackColor = true;
this.radSearchbyName.CheckedChanged += new System.EventHandler(this.radioBox_CheckedChanged);
//
// btnSearch
//
this.btnSearch.Location = new System.Drawing.Point(120, 122);
this.btnSearch.Name = "btnSearch";
this.btnSearch.Size = new System.Drawing.Size(87, 30);
this.btnSearch.TabIndex = 11;
this.btnSearch.Text = "Search ...";
this.btnSearch.UseVisualStyleBackColor = true;
this.btnSearch.Click += new System.EventHandler(this.btnSearch_Click);
//
// txtSearchName
//
this.txtSearchName.Location = new System.Drawing.Point(65, 49);
this.txtSearchName.Name = "txtSearchName";
this.txtSearchName.Size = new System.Drawing.Size(142, 20);
this.txtSearchName.TabIndex = 9;
//
// txtSearchCity
//
this.txtSearchCity.Enabled = false;
this.txtSearchCity.Location = new System.Drawing.Point(65, 73);
this.txtSearchCity.Name = "txtSearchCity";
this.txtSearchCity.Size = new System.Drawing.Size(142, 20);
this.txtSearchCity.TabIndex = 10;
//
// label5
//
this.label5.AutoSize = true;
this.label5.Location = new System.Drawing.Point(15, 76);
this.label5.Name = "label5";
this.label5.Size = new System.Drawing.Size(33, 13);
this.label5.TabIndex = 9;
this.label5.Text = "City :";
//
// label6
//
this.label6.AutoSize = true;
this.label6.Location = new System.Drawing.Point(15, 52);
this.label6.Name = "label6";
this.label6.Size = new System.Drawing.Size(44, 13);
this.label6.TabIndex = 8;
this.label6.Text = "Name :";
//
// linkLabel1
//
this.linkLabel1.ActiveLinkColor = System.Drawing.Color.Blue;
this.linkLabel1.AutoSize = true;
this.linkLabel1.LinkColor = System.Drawing.Color.Red;
this.linkLabel1.Location = new System.Drawing.Point(427, 192);
this.linkLabel1.Name = "linkLabel1";
this.linkLabel1.Size = new System.Drawing.Size(74, 13);
this.linkLabel1.TabIndex = 11;
this.linkLabel1.TabStop = true;
this.linkLabel1.Text = "Show All Data";
this.linkLabel1.LinkClicked += new System.Windows.Forms.LinkLabelLinkClickedEventHandler(this.linkLabel1_LinkClicked);
//
// Form1
//
this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);
this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
this.AutoSizeMode = System.Windows.Forms.AutoSizeMode.GrowAndShrink;
this.ClientSize = new System.Drawing.Size(523, 396);
this.Controls.Add(this.groupBox4);
this.Controls.Add(this.groupBox3);
this.Controls.Add(this.groupBox2);
this.Controls.Add(this.groupBox1);
this.Icon = ((System.Drawing.Icon)(resources.GetObject("$this.Icon")));
this.MaximizeBox = false;
this.Name = "Form1";
this.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen;
this.Text = "SQL Server Compact Edition ( SqlServerCe Example )";
this.Load += new System.EventHandler(this.Form1_Load);
this.groupBox1.ResumeLayout(false);
this.groupBox1.PerformLayout();
this.groupBox2.ResumeLayout(false);
this.groupBox2.PerformLayout();
this.groupBox3.ResumeLayout(false);
this.groupBox4.ResumeLayout(false);
this.groupBox4.PerformLayout();
this.ResumeLayout(false);
}
#endregion
private System.Windows.Forms.ListView listView1;
private System.Windows.Forms.GroupBox groupBox1;
private System.Windows.Forms.GroupBox groupBox2;
private System.Windows.Forms.Label label1;
private System.Windows.Forms.GroupBox groupBox3;
private System.Windows.Forms.TextBox txtName;
private System.Windows.Forms.TextBox txtCity;
private System.Windows.Forms.TextBox txtTelephone;
private System.Windows.Forms.TextBox txtEmail;
private System.Windows.Forms.Label label4;
private System.Windows.Forms.Label label3;
private System.Windows.Forms.Label label2;
private System.Windows.Forms.Button btnDelete;
private System.Windows.Forms.Button btnChange;
private System.Windows.Forms.Button btnAdd;
private System.Windows.Forms.GroupBox groupBox4;
private System.Windows.Forms.Button btnSearch;
private System.Windows.Forms.TextBox txtSearchName;
private System.Windows.Forms.TextBox txtSearchCity;
private System.Windows.Forms.Label label5;
private System.Windows.Forms.Label label6;
private System.Windows.Forms.RadioButton radSearchbyCity;
private System.Windows.Forms.RadioButton radSearchbyName;
private System.Windows.Forms.ColumnHeader columnHeader1;
private System.Windows.Forms.ColumnHeader columnHeader2;
private System.Windows.Forms.ColumnHeader columnHeader3;
private System.Windows.Forms.ColumnHeader columnHeader4;
private System.Windows.Forms.ColumnHeader columnHeader5;
private System.Windows.Forms.LinkLabel linkLabel1;
}
}
Download :exe
http://sites.google.com/site/ogremyfiles/SqlServerCe_EXE.rar
Source Code
http://sites.google.com/site/ogremyfiles/SqlServerCe_SourceCode.rar
