Basic Database operations using ADO.NET

Description

Last few months I had the opportunity to port some applications written in VB6.0 and Java into VB.NET/C#. I found very interesting database features incorporated into the .NET as ADO.NET. The whole database programming no matter which language, has been rewashed, cleansed and sprayed with a new programming model -- disconnected data architecture, tight integration with XML, common data representation with the capability to get data from multiple and varied data sources and optimized facilities. At last Microsofts Universal Data Access (UDA) strategy, where focus of content rather than data format and storage media has paid off. ADO.NET was specifically designed to be the data access layer of the .NET framework.  

I took this opportunity in bringing you a small application which takes you through the basic database operations in C# using ADO.NET on a table in a sqlserver database -- Adding a new record, Editing an existing record, Deleting an existing record, Navigating between records.

There are lot of articles (especially on c-sharpcorner) that might have taken you through the summary of different ADO.NET objects available to access a given database. But, I just wanted to get you through this once more to clear up any cloud surrounding between you and the ADO.NET programming. ADO.NET primarily consists of

CONNECTION Establishes a connection to a specific data source.

COMMAND Executes a command at a data source.

DATAADAPTER Populates a Data Set and restores updates with data source.

DATAREADER Reads a forward-only, read-only stream of data from a data source.

DATASET Is an off-line store of a data store. And the data store typically can be populated from multiple data sources.

Sample Application

Name of the Database: sjData
Name of the Table: Student
Columns in the Table: SNo, FirstName, LastName, Score 

I used the VS.NET to create this small app. I placed four labels and four text boxes for each column in the table. Four buttons for navigating between records. Six buttons,  one each for Add, Delete, Edit,  Update, Cancel  and  Close. 

Close: When clicked will close the form.

Add: Allows you to enter values into the text boxes. All the buttons are disabled and only Update and Cancel buttons are enabled. After user enters the values into the text boxes you can click on Update to add the record or click on Cancel to cancel the operation.

Edit: Allows you to edit an existing record.

Delete: Allows you to delete an existing record.

Update: Is enabled when user selects Edit or Add buttons.

Cancel: Is enabled when user selects Edit or Add buttons.

SqlConnection1: establishes a connection to the sjData database in the Sqlserver database.

SqlDataAdapter1: The dataadapter sits between a dataset and a database and fills data from the datasource to the dataset. 

dataSet11: Dataset contains data from the Student table.

sqlSelectCommand1: Responsible to execute the Select command given to it in the commandtext property.

sqlInsertCommand1: Responsible to execute the Insert command given to it.

sqlDeleteCommand1: Responsible to execute the delete command given to it.

sqlUpdateCommand1: Responsible to execute the update command given to it.

To data bind the text boxes to the appropriate fields of the table:

Select the text box and in the properities window click on the DataBindings property collection and select the text property which contains the dataset and the fields the dataset is supposed to populate. Set the text box to the appropriate field of the dataset. Follow the same for all the text boxes.

This what VS.NET generates for the above operation

this.txtSNo.DataBindings.Add

(new System.Windows.Forms.Binding("Text", this.dataSet11, "Student.SNo"));

Navigating between records

Every windows form has a BindingContext object. Any datasource that you bind to a windows control to will have an associated CurrencyManager object. The CurrencyManager keeps track of the position and otherwise supervises bindings to that data source. The BindingContext object keeps track of all of the CurrencyManager objects on a form.

this.BindingContext[dataSet11,"Student"].Position=0; //Move First this.BindingContext[dataSet11,"Student"].Position=this.BindingContext[dataSet11,"Student"].Count-1; // Move Last
this.BindingContext[dataSet11,"Student"].Position+=1; //Move Next
this.BindingContext[dataSet11,"Student"].Position-=1; // Move Previous

Deleting an existing record

DataRow dr;
int vSNo=Int32.Parse(txtSNo.Text);
dr=dataSet11.Tables["Student"].Rows.Find(vSNo)
dr.Delete();
sqlDataAdapter1.Update(dataSet11,"Student");

Adding a new record

int vSNo=Int32.Parse(txtSNo.Text);
int vScore=Int32.Parse(txtScore.Text);
DataRow drAdd
drAdd=dataSet11.Tables["Student"].NewRow();
drAdd[0]=vSNo;
drAdd[1]=txtFName.Text;
drAdd[2]=txtLName.Text;
drAdd[3]=vScore;
dataSet11.Tables["Student"].Rows.Add(drAdd);
sqlDataAdapter1.Update(dataSet11,"Student");

Editing an existing record

int vSNo=Int32.Parse(txtSNo.Text);
int vScore=Int32.Parse(txtScore.Text);
drUpdate.BeginEdit();
drUpdate["SNo"]=vSNo;
drUpdate["FirstName"]=txtFName.Text;
drUpdate["LastName"]=txtLName.Text;
drUpdate["Score"]=vScore;
drUpdate.EndEdit();
sqlDataAdapter1.Update(dataSet11,"Student");