Building database application with ADO.NET for a beginner.

Yulianto.

3.39/5 (28 votes)

Dec 26, 2004

CPOL

3 min read

141502

2963

An article on building database with ADO.NET application for a beginner.

Using the code

To build an application which can view, insert, add, and delete record(s), we will need Connection, DataAdapter, and Command object. There are four kinds of connection, adapter, and command objects, which are:

Data provider for SQL server version 7.0 or later.
Data provider for OLE DB.
Data provider for ODBC.
Data provider for Oracle.

So, if you're using SQL server, you will then use SqlConnection, SqlDataAdapter, and SqlCommand.

Creating Connection, DataAdapter, and Command object
To easily build these things, add a new connection at Server Explorer. After successfully adding a new connection, choose the table which we're gonna work on from the connection, and drag it to your form. It will automatically create one connection, one DataAdapter, and four Command objects. Check it out on your code. The four Command objects are executed when you want to select, insert, update, or delete record(s). You will need primary key on your table to have these four commands. Otherwise, you won't have update and delete Command objects.

To build it with the hard way (manually):
```
        
        OleDbConnection myConnection = new OleDbConnection(ConnectionString);
        OleDbCommand myCommand = new OleDbCommand(SelectQuery,myConnection);
        myConnection.Open();
        OleDbDataAdapter adapter=new OleDbDataAdapter();
        adapter.SelectCommand = myCommand;
```
Prepare the container where, we will put our table.
To view your table, there are numerous ways. Here, I will use the DataGrid, and the TextBox to view our record. When using the TextBox, we will be using Buttons to navigate from one record to another.
Binding the control.
Select your DataAdapter, right click, and choose Generate DataSet. DataSet is a representation of in-memory cache of data. In another word, it's a copy of your database. It can contain more than one table for one DataSet. You must first fill the DataSet from your DataAdapter. Using that DataSet, you can fill the DataGrid:
```
     oleDbDataAdapter1.Fill(dataSet11,"Student");
     dataGrid1.SetDataBinding(dataSet11,"Student");
```
The best place to place that binding is when your form loads (where else?).

To view the record at the TextBox, we will use the help of DataReader object. There might be an easy way for this. Tell me, when you find it. Here I will create the object with the name reader of type System.Data.OleDb.OleDbDataReader. The code to read the DataReader and put it into the TextBox follows:
```
        oleDbConnection1.Open();
        reader=oleDbSelectCommand1.ExecuteReader();
        reader.Read();
        nim.Text =reader.GetInt32(0).ToString();
        name.Text =reader.GetString(1); oleDbConnection1.Close();
```
Navigating through the records
To navigate through records, I created a new variable representing the present record position, index. To get to a particular position, simply read the DataReader until reaching the record that we want.
```
        for(int i=0;i < index;i++) 
            reader.Read(); 
```
Update, delete, and add the DataGrid
To save after updating, inserting, and deleting from the DataGrid, get the changes made to the DataGrid by executing GetChanges() from the table of the DataSet. When you do something to the DataGrid, it willl also affect the DataSet. Next, do the update to the DataAdapter object. This will save your changes to the database.
```
        DataTable table=dataSet11.Tables["Student"].GetChanges();    
        if(table!=null)   
        {
            oleDbDataAdapter1.Update(table);   
            dataSet11.Tables["Student"].AcceptChanges();
        }
```
AcceptChanges() will clear the changes in DataSet, and makes it a clean DataSet.

To delete a record from the DataGrid, I use popup menu. When the user clicks on the DataGrid, find which row is being clicked with:
```
        if(dataGrid1.HitTest(e.X, e.Y).Type==
        System.Windows.Forms.DataGrid.HitTestType.Cell
        ||dataGrid1.HitTest(e.X, e.Y).Type==
        System.Windows.Forms.DataGrid.HitTestType.RowHeader)
        {
            dataGrid1.Select(dataGrid1.HitTest(e.X, e.Y).Row); 
            dataGridRow=dataGrid1.HitTest(e.X, e.Y).Row; 
        }
```
HitTest will get information, such as row and column number of a clicked point of a grid, about the grid using a specific point, in this case, the mouse position when it clicks. After getting the row index, deleting the record is a piece of cake.
```
dataSet11.Tables["Student"].Rows[dataGridRow].Delete();
```

Update, delete, and add by another method

To delete, when using the TextBox way, get the table and the row index of the row we're going to delete from the index we've created before.

        dataSet11.Tables["Student"].Rows[index].Delete();  
        //save changes
        DataTable table=dataSet11.Tables["Student"].GetChanges();    
        if(table!=null)   
        {
            oleDbDataAdapter1.Update(table);   
            dataSet11.Tables["Student"].AcceptChanges();
        }

The code for updating:

        DataRow row=dataSet11.Tables["Student"].Rows[index-1];
        row[0]=nim.Text; 
        row[1]=name.Text;
        //save changes
        DataTable table=dataSet11.Tables["Student"].GetChanges();    
        if(table!=null)   
        {
            oleDbDataAdapter1.Update(table);   
            dataSet11.Tables["Student"].AcceptChanges();
        }

The code for adding:

        DataRow row;
        DataTable table=
        dataSet11.Tables["Student"]; row=
        table.NewRow(); row[0]=nim.Text;
        row[1]=name.Text;
        table.Rows.Add(row);
        //save changes
        DataTable table=dataSet11.Tables["Student"].GetChanges();    
        if(table!=null)   
        {
            oleDbDataAdapter1.Update(table);   
            dataSet11.Tables["Student"].AcceptChanges();
        }

You can delete, update, and delete with another way. Remember the Command object above? For example, if you're going to delete a record with the StudentNo 5 and with the name "Charlie", set the parameter of the command first, and then execute the command.

        oleDbConnection1.Open();  
        OleDbParameter myParm = 
                 oleDbDeleteCommand1.Parameters["Original_StudentNo"];
        myParm.Value = 5;
        myParm=oleDbDeleteCommand1.Parameters["Original_Name"]; 
        myParm.Value = "Charlie";
        myParm=oleDbDeleteCommand1.Parameters["Original_Name1"]; 
        myParm.Value = "Charlie";
        oleDbDeleteCommand1.ExecuteNonQuery();  
        oleDbConnection1.Close();

Conclusion

You can see DataSet as a copy of your tables, DataAdapter as your database, and DataReader as collection of rows. You can manipulate record(s), through DataSet, get the changes, and finally save it.