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
- 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);
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 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
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
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++)
- 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.
AcceptChanges() will clear the changes in
DataSet, and makes it a clean
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:
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.
- 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.
The code for updating:
The code for adding:
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.
OleDbParameter myParm =
myParm.Value = 5;
myParm.Value = "Charlie";
myParm.Value = "Charlie";
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.