Click here to Skip to main content
Click here to Skip to main content

Building database application with ADO.NET for a beginner.

, 23 Mar 2005
Rate this:
Please Sign up or sign in to vote.
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:

  1. Data provider for SQL server version 7.0 or later.
  2. Data provider for OLE DB.
  3. Data provider for ODBC.
  4. Data provider for Oracle.

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

  1. 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;
  2. 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.

  3. 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();
  4. 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(); 
  5. 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();
  6. 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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Yulianto.
CEO Odihost
Indonesia Indonesia
Learned programming since elementry school with QBASIC. Until now have learned a bit about VB, java, html, php, asp, delphi. He is taking his master degree in finance. Currently working for a great company(desain web), and stock trading. Own various website about option strategy, lose weight, and Invest Money.

Comments and Discussions

 
GeneralFinding PinmemberArielR10-Oct-08 12:03 
QuestionDragging table onto form Pinmemberen3rg911-Jul-07 9:02 
AnswerRe: Dragging table onto form PinmemberYulianto.14-Jul-07 4:40 
GeneralRe: Dragging table onto form Pinmemberen3rg916-Jul-07 8:56 
GeneralRe: Dragging table onto form PinmemberYulianto.16-Jul-07 18:06 
QuestionRe: Dragging table onto form Pinmemberthides29-Dec-07 5:27 
GeneralIndonesian Pinmemberebelebelebel18-Apr-07 23:56 
GeneralRe: Indonesian PinmemberYulianto.19-Apr-07 0:13 
GeneralRe: Indonesian Pinmemberebelebelebel19-Apr-07 2:26 
GeneralRe: Indonesian PinmemberYulianto.19-Apr-07 14:23 
QuestionCoping rows problem Pinmemberdr4cul427-Nov-06 3:43 
AnswerRe: Coping rows problem PinmemberYulianto.27-Nov-06 18:52 
AnswerRe: Coping rows problem Pinmemberdr4cul427-Nov-06 21:56 
GeneralRe: Coping rows problem PinmemberYulianto.27-Nov-06 23:26 
GeneralRe: Coping rows problem Pinmemberdr4cul428-Nov-06 0:06 
GeneralRe: Coping rows problem PinmemberYulianto.28-Nov-06 20:46 
GeneralRe: Coping rows problem Pinmemberdr4cul428-Nov-06 21:07 
GeneralNot bad, but there's a few problems. PinmemberAdam Goossens28-Mar-05 20:49 
GeneralRe: Not bad, but there's a few problems. PinmemberYulianto AKA goodmast3r28-Mar-05 21:02 
GeneralDemo Project PinmemberACorbs31-Dec-04 22:22 
GeneralRe: Demo Project Pinmembergoodmast3r3-Jan-05 21:46 
GeneralRe: Demo Project PinmemberACorbs14-Jan-05 8:44 
GeneralRe: Demo Project Pinmembergoodmast3r17-Jan-05 21:38 
GeneralRe: Demo Project PinmemberACorbs17-Feb-05 15:11 
GeneralRe: Demo Project PinmemberYulianto AKA goodmast3r17-Feb-05 23:08 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140721.1 | Last Updated 23 Mar 2005
Article Copyright 2004 by Yulianto.
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid