Reader Level:
ARTICLE

ADO.NET DataSet Programming

Posted by Suresh S Articles | ADO.NET March 20, 2001
A sample project shows you how to add, edit, delete, navigate, save records in a database using DataSet.
  • 0
  • 0
  • 47417
Download Files:
 

This is a basically a Database Application illustrating the latest Microsoft ADO.NET DataSet Features.

Features:

  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

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);
}
}
}

Article Extensions
Contents added by Mathew on Jul 02, 2012

What a foolish article. This man is stupid..

He has displayed the designer code while writing a ADO.net article so in Business, Data and logic layers are all merged into this so called Ado.net tutorial.

C# corner should not allow any new learners to post any useless articles else it would be passed into Oblivion..


Regards,

Pushpak Sharma..


Contents added by Garg Atur on Mar 31, 2010

// this article is a load of crap of some attention seeker trying to impress his colleagues in India that he writes articles on

// some forum and should be scrapped asap. Send the author back to school.

COMMENT USING

Trending up