This is a basically a Database Application illustrating the latest Microsoft ADO.NET DataSet Features.
Features:
- Navigating the records : prev, next, first and last
- Adding a new record
- Deleting a record
- Saving all changes made to DataSet
- Discarding all changes made to DataSet
Since this is a sample application and not a production code, proper exception handling, ui, code commenting/xml documentation has not carried out. The main idea is to give users an idea on how to proceed with ADO.NET DataSet programming.
For creation of the program, Visual Studio.NET has not been used, instead NotePad was used. Therefore for compilation and running, a batch file is also included. Since Visual Studio.NET puts lots of its own code, which many programmers hate.
Source Code:
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);
}
}
}