Click here to Skip to main content
12,405,303 members (65,251 online)
Click here to Skip to main content
Add your own
alternative version

Stats

122.3K views
2.7K downloads
62 bookmarked
Posted

Building database application with ADO.NET for a beginner.

, 23 Mar 2005 CPOL
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)

Share

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.

You may also be interested in...

Comments and Discussions

 
GeneralFinding Pin
ArielR10-Oct-08 12:03
memberArielR10-Oct-08 12:03 
QuestionDragging table onto form Pin
en3rg911-Jul-07 9:02
memberen3rg911-Jul-07 9:02 
AnswerRe: Dragging table onto form Pin
Yulianto.14-Jul-07 4:40
memberYulianto.14-Jul-07 4:40 
GeneralRe: Dragging table onto form Pin
en3rg916-Jul-07 8:56
memberen3rg916-Jul-07 8:56 
GeneralRe: Dragging table onto form Pin
Yulianto.16-Jul-07 18:06
memberYulianto.16-Jul-07 18:06 
QuestionRe: Dragging table onto form Pin
thides29-Dec-07 5:27
memberthides29-Dec-07 5:27 
GeneralIndonesian Pin
ebelebelebel18-Apr-07 23:56
memberebelebelebel18-Apr-07 23:56 
GeneralRe: Indonesian Pin
Yulianto.19-Apr-07 0:13
memberYulianto.19-Apr-07 0:13 
GeneralRe: Indonesian Pin
ebelebelebel19-Apr-07 2:26
memberebelebelebel19-Apr-07 2:26 
GeneralRe: Indonesian Pin
Yulianto.19-Apr-07 14:23
memberYulianto.19-Apr-07 14:23 
QuestionCoping rows problem Pin
dr4cul427-Nov-06 3:43
memberdr4cul427-Nov-06 3:43 
AnswerRe: Coping rows problem Pin
Yulianto.27-Nov-06 18:52
memberYulianto.27-Nov-06 18:52 
AnswerRe: Coping rows problem Pin
dr4cul427-Nov-06 21:56
memberdr4cul427-Nov-06 21:56 
GeneralRe: Coping rows problem Pin
Yulianto.27-Nov-06 23:26
memberYulianto.27-Nov-06 23:26 
GeneralRe: Coping rows problem Pin
dr4cul428-Nov-06 0:06
memberdr4cul428-Nov-06 0:06 
GeneralRe: Coping rows problem Pin
Yulianto.28-Nov-06 20:46
memberYulianto.28-Nov-06 20:46 
GeneralRe: Coping rows problem Pin
dr4cul428-Nov-06 21:07
memberdr4cul428-Nov-06 21:07 
GeneralNot bad, but there's a few problems. Pin
Adam Goossens28-Mar-05 20:49
memberAdam Goossens28-Mar-05 20:49 
GeneralRe: Not bad, but there's a few problems. Pin
Yulianto AKA goodmast3r28-Mar-05 21:02
memberYulianto AKA goodmast3r28-Mar-05 21:02 
GeneralDemo Project Pin
ACorbs31-Dec-04 22:22
memberACorbs31-Dec-04 22:22 
GeneralRe: Demo Project Pin
goodmast3r3-Jan-05 21:46
membergoodmast3r3-Jan-05 21:46 
GeneralRe: Demo Project Pin
ACorbs14-Jan-05 8:44
memberACorbs14-Jan-05 8:44 
GeneralRe: Demo Project Pin
goodmast3r17-Jan-05 21:38
membergoodmast3r17-Jan-05 21:38 
GeneralRe: Demo Project Pin
ACorbs17-Feb-05 15:11
memberACorbs17-Feb-05 15:11 
GeneralRe: Demo Project Pin
Yulianto AKA goodmast3r17-Feb-05 23:08
memberYulianto AKA goodmast3r17-Feb-05 23:08 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    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 | Terms of Use | Mobile
Web02 | 2.8.160721.1 | Last Updated 23 Mar 2005
Article Copyright 2004 by Yulianto.
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid