ADO.NET DataSet Programming

1. Navigating the records : prev, next, first and last
2. Adding a new record
3. Deleting a record
4. Saving all changes made to DataSet
5. Discarding all changes made to DataSet

using System;
using System.WinForms;
using System.Data;
using System.Data.ADO;
///<summary>
/// Class to demonstrate usage of ADO.NET DataSet Feature
///<summary>
public class DBApp : Form
{
private Button btnReject;
private Button btnSave;
private Button btnDelete;
private Button btnAdd;
private Button btnFirst;
private Button btnLast;
private Button btnNext;
private Button btnPrev;
private Label label2;
private Label label1;
private TextBox txtPosition;
private TextBox txtName;
private TextBox txtAge;
//Database members
private ADOConnection adoConnection; //ADO connection object
private ADODataSetCommand adoDataSetCommand; //ADO DataSet Command Object
private DataSet dataSet; //DataSet Object
//listManager used for navigation of records through controls of
//form bound to data through bindmanager
private ListManager listMgr;
//entry point of application
public static void Main()
{
//initilize the form
DBApp dbApp = new DBApp();
dbApp.InitializeComponent();
dbApp.InitializeDatabase();
dbApp.InitializeBinding();
dbApp.ShowPosition();
Application.Run(dbApp);
}
//function to initialize list manager and bind
//form controls to database
public void InitializeBinding()
{
listMgr = this.BindingManager[dataSet,"users"];
txtName.Bindings.Add("Text", dataSet, "users.Name");
txtAge.Bindings.Add("Text", dataSet, "users.age");
}
public void InitializeDatabase()
{
//Data Source = MSSQL DataBase Server = suresh
//uid = user id
//pwd = password
//Database name = Initial Catalog = csharp
//Provider = Database Driver = SQLOLEDB.1
String strConnection = "Provider= SQLOLEDB.1; Data Source=suresh; uid=sa; pwd=sa; Initial Catalog=csharp";
adoConnection = new ADOConnection(strConnection);
adoDataSetCommand = new ADODataSetCommand();
//it will automatically generate update, delete and insert statements
adoDataSetCommand.SelectCommand.CommandText = "select name, age from users where 1=1";
adoDataSetCommand.SelectCommand.ActiveConnection = adoConnection;
dataSet = new DataSet();
//important to mention the tablename
//Fill the in memory dataset with the database values
adoDataSetCommand.FillDataSet(dataSet, "users");
}
//function to initialize various controls on the form as well
//as the form properties
public void InitializeComponent()
{
//form properties
this.Text = "Database Application";
this.ClientSize = new System.Drawing.Size (440, 200);
this.BorderStyle = FormBorderStyle.FixedDialog;
this.MaximizeBox = false;
this.MinimizeBox = false;
btnAdd = new System.WinForms.Button ();
btnAdd.Location = new System.Drawing.Point (300, 20);
btnAdd.Size = new System.Drawing.Size (128, 32);
btnAdd.TabIndex = 10;
btnAdd.Text = "Add New Record";
btnAdd.FlatStyle= FlatStyle.Popup;
btnAdd.Click += new System.EventHandler (this.btnAdd_Click);
txtPosition = new System.WinForms.TextBox ();
txtPosition.Location = new System.Drawing.Point (144, 120);
txtPosition.AutoSize = false;
txtPosition.TabIndex = 7;
txtPosition.Size = new System.Drawing.Size (40, 32);
txtPosition.Enabled = false;
btnPrev = new System.WinForms.Button ();
btnPrev.Location = new System.Drawing.Point (96, 120);
btnPrev.Size = new System.Drawing.Size (40, 32);
btnPrev.TabIndex = 5;
btnPrev.Text = "<";
btnPrev.Click += new System.EventHandler (this.HandleButtonClick);
btnDelete = new System.WinForms.Button ();
btnDelete.Location = new System.Drawing.Point (300, 60);
btnDelete.Size = new System.Drawing.Size (128, 32);
btnDelete.TabIndex = 11;
btnDelete.Text = "Delete Current Record";
btnDelete.FlatStyle= FlatStyle.Popup;
btnDelete.Click += new System.EventHandler (this.btnDelete_Click);
btnNext = new System.WinForms.Button ();
btnNext.Location = new System.Drawing.Point (192, 120);
btnNext.Size = new System.Drawing.Size (40, 32);
btnNext.TabIndex = 6;
btnNext.Text = ">";
btnNext.Click += new System.EventHandler (this.HandleButtonClick);
btnReject = new System.WinForms.Button ();
btnReject.Location = new System.Drawing.Point (300, 140);
btnReject.Size = new System.Drawing.Size (128, 32);
btnReject.TabIndex = 13;
btnReject.Text = "Reject All Changes";
btnReject.FlatStyle= FlatStyle.Popup;
btnReject.Click += new System.EventHandler (this.btnReject_Click);
label1 = new System.WinForms.Label ();
label1.Location = new System.Drawing.Point (48, 24);
label1.Text = "Name of User";
label1.Size = new System.Drawing.Size (88, 24);
label1.TabIndex = 0;
btnLast = new System.WinForms.Button ();
btnLast.Location = new System.Drawing.Point (240, 120);
btnLast.Size = new System.Drawing.Size (40, 32);
btnLast.TabIndex = 8;
btnLast.Text = ">|";
btnLast.Click += new System.EventHandler (this.HandleButtonClick);
btnSave = new System.WinForms.Button ();
btnSave.Location = new System.Drawing.Point (300, 100);
btnSave.Size = new System.Drawing.Size (128, 32);
btnSave.TabIndex = 12;
btnSave.Text = "Save All Changes";
btnSave.FlatStyle= FlatStyle.Popup;
btnSave.Click += new System.EventHandler (this.btnSave_Click);
btnFirst = new System.WinForms.Button ();
btnFirst.Location = new System.Drawing.Point (48, 120);
btnFirst.Size = new System.Drawing.Size (40, 32);
btnFirst.TabIndex = 9;
btnFirst.Text = "|<";
btnFirst.Click += new System.EventHandler (this.HandleButtonClick);
label2 = new System.WinForms.Label ();
label2.Location = new System.Drawing.Point (48, 60);
label2.Text = "Age of User";
label2.Size = new System.Drawing.Size (88, 32);
label2.TabIndex = 2;
txtName = new TextBox();
txtName.Location = new System.Drawing.Point (140, 24);
txtName.Text = "";
txtName.TabIndex = 1;
txtName.Size = new System.Drawing.Size (140, 20);
txtAge = new TextBox();
txtAge.Location = new System.Drawing.Point (140, 60);
txtAge.Text = "";
txtAge.TabIndex = 3;
txtAge.Size = new System.Drawing.Size (140, 20);
Controls.Add (btnReject);
Controls.Add (btnSave);
Controls.Add (btnDelete);
Controls.Add (btnAdd);
Controls.Add (btnFirst);
Controls.Add (btnLast);
Controls.Add (txtPosition);
Controls.Add (btnNext);
Controls.Add (btnPrev);
Controls.Add (label2);
Controls.Add (label1);
Controls.Add(txtName);
Controls.Add(txtAge);
}
//function to Handle Event generated by clicking of navigator buttons
// <, >, |<, >|
protected void HandleButtonClick (object sender, System.EventArgs e)
{
if(sender.GetType().ToString().Equals("System.WinForms.Button"))
{
string buttonText = ((Button)sender).Text;
switch(buttonText)
{
case "<":
//previous record
if(listMgr.Position > 0)
{
listMgr.Position -= 1;
}
ShowPosition();
break;
case ">":
//next record
int iCnt;
iCnt = listMgr.Count - 1;
if(listMgr.Position < iCnt)
{
listMgr.Position += 1;
}
ShowPosition();
break;
case ">|":
//last record
listMgr.Position = listMgr.Count-1;
ShowPosition();
break;
case "|<":
//first record
listMgr.Position = 0;
ShowPosition();
break;
}
}
}
//function to display position of current record
//in list manager
public void ShowPosition()
{
txtPosition.Text = listMgr.Position.ToString();
}
//function to discard all changes made to Dataset/ListManager
protected void btnReject_Click (object sender, System.EventArgs e)
{
dataSet.RejectChanges();
listMgr.Refresh();
listMgr.Position=0;
}
//function to save all changes made to DataSet to Database
protected void btnSave_Click (object sender, System.EventArgs e)
{
DataSet tempDataSet = dataSet.GetChanges();
try
{
if(tempDataSet!=null)
{
if(!tempDataSet.HasErrors)
{
dataSet.Merge(tempDataSet);
//very important to put the table name
adoDataSetCommand.Update(dataSet,"users");
dataSet.AcceptChanges();
}
}
}
catch (System.Exception exception)
{
Console.WriteLine(exception.StackTrace);
DataRow[] modifiedRows = tempDataSet.Tables["users"].GetErrors();
for(int i=0;i<modifiedRows.Length;i++)
{
if(modifiedRows[i].HasErrors)
{
Console.WriteLine(modifiedRows[i].RowError);
}
}
}
}
//function to delete current record from DataSet/ ListManager
protected void btnDelete_Click (object sender, System.EventArgs e)
{
dataSet.Tables["users"].Rows[listMgr.Position].Delete();
listMgr.Refresh();
listMgr.Position=0;
ShowPosition();
}
//Function to add New Record to DataSet/ListManager
protected void btnAdd_Click (object sender, System.EventArgs e)
{
try
{
TablesCollection tc = dataSet.Tables;
int i = tc.IndexOf("users");
DataRow newRow = tc[i].NewRow();
newRow["name"] = "";
tc[i].Rows.Add(newRow);
listMgr.Refresh();
listMgr.Position = listMgr.Count-1;
ShowPosition();
}
catch (System.Exception exception)
{
Console.WriteLine(exception.StackTrace);
}
}
}