Click here to Skip to main content
15,867,488 members
Articles / Programming Languages / C#
Article

Simple Database Application - Part 1

Rate me:
Please Sign up or sign in to vote.
2.80/5 (24 votes)
24 Jan 20032 min read 180.6K   58   14
Connecting to Database using C# and ADO.NET

Introduction

Accessing database is pretty simple job using C# and ADO. In this article, the sample gives you the guidelines for a C# programmer which demonstrates how to use ADO.NET objects including ADOCOnnection, ADOCommand, ADODataReader. Microsoft Data Access Technology got changed with the ADO.NET I access database Employee.mdb, which has a table called "Employee". I will add the employee information to the database .

Using the Code

To write a code in ADO you need to use the following at the beginning of your code.

C#
using System;
using System.Drawing;
using System.ComponentModel;
using System.WinForms;
using System.Data.ADO ;
using System.Data ;
using System.Threading ;

Proceeding with the next step, make sure that all of your GUI is in place for adding the data ( all the text boxes and labels , that is pretty simple so I am not going to cover this), Next will will go ahead and get the connection to the database( note the connection to the database should be running on a different thread, so that it will not mess up the GUI. to do this we have to start a thread as shown below.

C#
ThreadStart DBThread = new ThreadStart(ConnectToDatabase) ;
        Thread DB = new Thread(DBThread) ;
        DB.Start() ;

As you can see above we have successfully started the thread, This thread calls the method ConnectToDataBase, let see what this ConnectToDatabase method is all about.

C#
  public void ConnectToDatabase()
  {
      Infobar.Text="Please Wait, Connecting to database...." ;
      string conn=
"Provider=Microsoft.Jet.OLEDB.4.0 ;Data Source=Employee.mdb" ;
      // here we create a new ADOConnection  
      // Object which takes the connection string
        ADOConnection dbConn = new ADOConnection(conn) ;
        
        //Make a Select Command
        string empStr = "Select id from Employee" ;
        // creates a new ADOCommand object which takes 
        // a string and connection
        ADOCommand sqlCommand =new ADOCommand(empStr,dbConn);
        // finally open the connection to the database
        dbConn.Open();
        //now it's time to read some data from the database, 
        //which can be done as shown below.
        ADODataReader reader;
        
        //Execute the command and get the Data into "reader"
        sqlCommand.Execute(out reader) ;
        int numRecords=0 ;
        //Get the current number of records present in the database.
        while(reader.Read())
        {
          numRecords++ ;
        }
        numRecords++ ;
        //update the employee Id textbox with the Number 
        //of records present plus one.
        employeeID.Text = numRecords.ToString() ;
        
        Infobar.Text="Connected - Now you can Add records";
  }
Hope the above code is not much confusing, It is lot simpler than writing the code in VC++ ADO. now it's time to write a method that actually takes all the parameters from the GUI Form and writes it to the database with the click of a button. for this we will create a button called Add In your InitializeComponents Method the code would look like this
C#
Add.Text = "AddToDatabase";
Add.Click += new System.EventHandler(WriteToDataBase);
This gets as simple as it looks, Actually writing to database is much simpler as shown below in the code.
C#
protected void WriteToDatabase(object sender, System.EventArgs e)
 {

   if(employeeID.Text!=""&&employeeLastName.Text!=""
     &&employeeFirstName.Text!=""&&employeeAge.Text!=""
     &&employeeSSN.Text!="")
   {

     string conn="Provider=Microsoft.Jet.OLEDB.4.0 ;"+
               "Data Source=Employee.mdb" ;
     // get the connection object and open the connection
     ADOConnection dbConn = new ADOConnection(conn) ;
     dbConn.Open();
     //prepare a statement to get all the values from the text boxes.
     string strInsert = "INSERT INTO Employee (employeeID, " +
       "employeeLastName, employeeFirstName, "+
       "employeeAge, employeeSSN) VALUES ( "
       +employeeID.Text+", '"
       +employeeLastName.Text+"' , '"
       +employeeFirstName.Text+"' , "
       +employeeAge.Text+", "
       +employeeSSN.Text+")";

     ADOCommand adoInsert = new ADOCommand(strInsert,DBConn) ;
     //Execute the statement
     adoInsert.ExecuteNonQuery() ;
     Infobar.Text="Data is being Added to Database " ;
     // after adding the data to the database we need to reset all
     //the data from the text boxes or to say clear the fields in the
     // text boxes so that we can add again.
     int iClear=int.Parse(employeeID.Text);
     iClear++;
     employeeID.Text=i.ToString() ;
     employeeLastName.Text="" ;
     employeeFirstName.Text="" ;
     employeeAge.Text="" ;
     employeeSSN.Text="" ;
     Infobar.Text="Connected - Now you can Add more records...";
     DBConn.Close() ;
   }
   else
   {
     MessageBox.Show("All fields must be filled.", "Error",
        MessageBox.IconExclamation);
   }
 }

Hope this was helpful. Will write more on how to view the database values and how to delete those values in my coming articles.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here



Comments and Discussions

 
GeneralMy vote of 1 Pin
JaneDoe7-Jun-09 3:25
JaneDoe7-Jun-09 3:25 
GeneralMy vote of 2 Pin
Bart1980123430-Nov-08 21:52
Bart1980123430-Nov-08 21:52 
QuestionStop double login to SQL Server. Pin
A H Khan27-Jun-06 22:06
A H Khan27-Jun-06 22:06 
GeneralAccess database from C#.Net via ODBC Pin
Will L Pittenger12-Oct-04 22:46
Will L Pittenger12-Oct-04 22:46 
GeneralRe: Access database from C#.Net via ODBC Pin
Shahriar Nour Khondokar30-Mar-10 18:34
Shahriar Nour Khondokar30-Mar-10 18:34 
Generalerror message Pin
domza27-Sep-04 0:14
domza27-Sep-04 0:14 
GeneralFew more details Pin
Md Saleem Navalur14-Jun-04 0:33
Md Saleem Navalur14-Jun-04 0:33 
GeneralError Message Appear Pin
softman20021-Mar-04 23:08
softman20021-Mar-04 23:08 
GeneralRe: Error Message Appear Pin
domza27-Sep-04 0:18
domza27-Sep-04 0:18 
GeneralHave one question to expand upon on this article Pin
michin127-Oct-03 5:33
michin127-Oct-03 5:33 
GeneralRe: Have one question to expand upon on this article Pin
Shahriar Nour Khondokar30-Mar-10 18:39
Shahriar Nour Khondokar30-Mar-10 18:39 
GeneralNice Article. Neately explained Pin
Dave Marcus25-Jan-03 11:54
Dave Marcus25-Jan-03 11:54 
GeneralOld version of .NET! Pin
Rickard Andersson2025-Jan-03 11:51
Rickard Andersson2025-Jan-03 11:51 
GeneralRe: Old version of .NET! Pin
leppie25-Jan-03 21:08
leppie25-Jan-03 21: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.