Simple Database Application - Part 1

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.