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

Simple Database Application - Part 1

, 24 Jan 2003
Rate this:
Please Sign up or sign in to vote.
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.

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.

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.

  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
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.
 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

About the Author


Comments and Discussions

 
GeneralMy vote of 1 PinmemberJaneDoe7-Jun-09 3:25 

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
Web04 | 2.8.140721.1 | Last Updated 25 Jan 2003
Article Copyright 2003 by Samar Aarkotti
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid