Database Manipulation with ADO.NET for Beginners

• Download source files - 67 KB

Introduction

This is the second part of Using ADO.NET for Beginners. It is a simple Personal Address Program that returns results from a database table, writes the output to textboxes, and uses buttons (First, Previous, Next, Last) to navigate through the records.

You can also manipulate the data by clicking the toolbar buttons, namely Add/New record, Save the new record, Delete the current record, and Edit/Update the records.

I chose Microsoft Access database (as reflected in the ADO.NET OleDb objects) because it's easy to use and you don't need to have Microsoft SQL Server running. But ADO.NET is highly optimized if you are working with Microsoft SQL Server databases (as reflected in the ADO.NET SQL objects).

How It Works?

After getting connected to the MS Access database, all the records will be displayed in the textboxes. In order to navigate through the records, you can use buttons Next, Previous, First, Last. You can find a detailed explanation about the use of the navigation buttons in the first part: Using ADO.NET for Beginners.

Insert a New Record

If you click the New button in the toolbar, the following methods are invoked:

• fnEnableToolbarButtons(true, "Save") to enable the "Save" button
• fnEnableToolbarButtons(false, "Delete") to disable the "Delete" button
• fnEnableToolbarButtons(false, "Edit") to disable the "Edit" button
• fnEnableButtonsNextPreviousLastFirst(false) to disable the navigation buttons
• fnClearAllTextBox() to clear all the textboxes
• fnEnableDisableTextBox(true) to enable the textboxes for editing

Here are the code snippets of the methods I used for the New button:

Depending on the two parameters of the method called, we enable or disable the ToolbarButton. For example: fnEnableToolbarButtons(true, "Save"); means we check the parameters are "Save" and "true" and enable the "Save" button. If we use "false" instead of "true", we disable the "Save" button.

private void fnEnableToolbarButtons(bool b, string s1) 
{ 
    if (s1=="New") 
      this.toolBarButtonNew.Enabled=b; 
    else if (s1=="Save")
      this.toolBarButtonSave.Enabled=b;
    else if (s1=="Delete")
      this.toolBarButtonDelete.Enabled=b;
    else if (s1=="Edit")
      this.toolBarButtonEdit.Enabled=b;
    else if (s1=="Refresh")
      this.toolBarButtonRefresh.Enabled=b; 
}

The method "fnEnableButtonsNextPreviousLastFirst" has only one bool parameter. In a foreach loop, we iterate through all the controls on the form and check if it's a Button, then enable or disable it with the bool parameter.

public void fnEnableButtonsNextPreviousLastFirst(bool flag) 
{ 
    string str; 
    foreach(Control ctrl in this.Controls) 
    { 
       str = Convert.ToString(ctrl.GetType()); 
       if(str == "System.Windows.Forms.Button")             
          ctrl.Enabled = flag;
    }
}

In the method "fnClearAllTextBox()", we iterate through all the controls on the form and check if it is a TextBox, then we clear the contents of the TextBox.

private void fnClearAllTextBox() 
{ 
   string str; 
   foreach(Control ctrl in this.Controls)
   { 
     str=Convert.ToString(ctrl.GetType()); 
     if(str=="System.Windows.Forms.TextBox")             
        ctrl.Text = "";
   }
}

public void fnEnableDisableTextBox(bool flag) 
{
   string str; 
   foreach(Control ctrl in this.Controls) 
   { 
      str = Convert.ToString(ctrl.GetType()); 
      if(str == "System.Windows.Forms.TextBox") 
        ctrl.Enabled = flag; 
   }
}

Save the New Record

If you click the New button to insert a new record, the Save button will be enabled so that you can save the new record.

The code snippet of the method for the Save a new record is shown here:

private void fnSaveNewRecord() 
{
  try
  {
     string strInsert;
     strInsert = "insert into PersonTable" + 
        "(FirstName, LastName, Title, City,Country)" 
        + " values('" + this.textboxFirstname.Text + "', '"
        + this.textboxLastname.Text + "', '"
        + this.textboxTitle.Text + "', '"
        + this.textboxCity.Text + "', '"
        + this.textboxCountry.Text + "')";
     if (this.textboxFirstname.Text !="" && 
           this.textboxLastname.Text !=""&& 
           this.textboxTitle.Text !="" && 
           this.textboxCity.Text !="" 
           && this.textboxCountry.Text !="") 
     { 
         this.oleDbDataAdapter1.InsertCommand.CommandText = strInsert;
         //do the insert
         this.oleDbDataAdapter1.InsertCommand.ExecuteNonQuery();
         //Get the last PersonID from the table and display it in TextBox
         fnGetLastPersonID(); 
         fnEnableButtonsNextPreviousLastFirst(true);
         fnEnableToolbarButtons(true, "Edit"); 
         fnEnableToolbarButtons(true, "Delete"); 
         fnEnableToolbarButtons(false, "Save");
         this.fnEnableDisableTextBox(false); 
     }else 
     {
         MessageBox.Show("You have to fill the TextBoxes...", 
                 "WARNING", MessageBoxButtons.OK,MessageBoxIcon.Warning);
                  this.textboxFirstname.Focus();
     }
  }
  catch (Exception ex)
  {
     MessageBox.Show("Error in inserting new record : " + 
        ex.Message, "Insert Error", MessageBoxButtons.OK, 
        MessageBoxIcon.Information);
     fnRefreshDataSet();
  } //try-catch
}

As you can see, there are some methods to be invoked in the method "fnSaveNewRecord()". One of them is "fnGetLastPersonID()" which gets the last PersonID from the table and displays it in the TextBox "textboxPersonID". After a new record is inserted and the INSERT command executed, I get the new PersonID of the new inserted record.

Here, you should pay attention to how the DataType AutoNumber problem in the table "PersonTable" was solved. I chose a different way for the AutoNumber issue.

First of all, you have to create the SELECT command only for the PersonID column. And then:

• Execute DataReader and the command with the connection and string command (strCom).
• Run the while loop and get all values (contents) of the column 0 (GetValue(0)).
• Store it in an object variable, convert it to integer until the end of the column (0) values.

When the while-loop is finished, you will get the last value in the first column (0) GetValue(0) outside while-loop. Increment the PersonID by 1 and display it in the TextBox "textboxPersonID".

Here is the code of the method "fnGetLastPersonID()":

private void fnGetLastPersonID() 
{
    string strCom = "Select Max(PersonID) from PersonTable" ; 
    OleDbCommand cmd =new OleDbCommand(strCom,this.oleDbConnection1);
    OleDbDataReader reader;
    reader =cmd.ExecuteReader() ;
    int i=0 ; //how many records in the table?, only for test
    int iPersonid=0; //the integer value of the first column(0) contents
    while(reader.Read()) 
    {
       i++ ;
       // GetValue(0) means: The contents
       // of the first column(0)(PersonID) in the table
       object obValue = reader.GetValue(0);
       iPersonid = Convert.ToInt32(obValue.ToString());
    }
    i++ ; 
    this.textboxPersonID.Text=iPersonid.ToString(); //display it in TextBox
    reader.Close(); //close the DataReader otherwise error
    MessageBox.Show("Record with new PersonID: "+iPersonid.ToString()+ 
           " inserted successfully", "Save New", MessageBoxButtons.OK, 
           MessageBoxIcon.Information); //inform the user
}

Delete the Current Record

The current record is deleted with the help of the method "fnDeleteCurrentRecord()". If the Delete button is clicked, you will be asked whether you want to delete the record or not. If the answer is Yes, the DELETE command will be executed and the method "fnRefreshDataSet()" is invoked.

The code snippet is as follows:

private void fnDeleteCurrentRecord 
{
    DialogResult dr=MessageBox.Show("Are you sure you want to delete" + 
                    " this record ? ", "Confirm deleting", 
                    MessageBoxButtons.YesNo, MessageBoxIcon.Question); 
     //convert textboxPersonID into integer
    int pid=int.Parse(this.textboxPersonID.Text); 
    if (dr ==DialogResult.Yes)
    {
      string strDel="DELETE FROM PersonTable WHERE PersonID= "+pid;
      fnExecuteQuery(strDel); 
      fnRefreshDataSet(); 
      MessageBox.Show("Record deleted..."); 
    }
    else
      MessageBox.Show("Record NOT deleted..."); 
}

Edit the Current Record

If you navigate through the records, the current record is disabled to edit in the textboxes. As soon as you click the Edit button, all the textboxes will be enabled to edit, and the Update button will appear in the toolbar so that you can save the current updated record.

this.toolBarButtonNew.Enabled=false; 
this.fnEnableDisableTextBox(true); 
fnSetEditUpdateText("Update");

Here is the method "fnSetEditUpdateText()" which gets a string as parameter:

private void fnSetEditUpdateText(string str)
{
  this.toolBarButtonEdit.Text=str; 
}

Update/Save the Edited Current Record

Here are the methods called if you click the Update button:

fnUpdateRecord(); 
fnSetEditUpdateText("Edit"); 
fnEnableToolbarButtons(true, "New"); 
this.fnEnableDisableTextBox(false);

First, the method "fnUpdateRecord()" is invoked to update the record. Second, the text of the Update button will be changed to "Edit" with the fnSetEditUpdateText("Edit") method. And then the method "fnEnableToolbarButtons(true, "New")" is called to set the "New" button enabled. Lastly, the method "fnEnableDisableTextBox(false)" will be called to disable all the textboxes with the help of the parameter "false".

There you have the code snippet of the method "fnUpdateRecord()":

private void fnUpdateRecord() 
{
  try 
  {
     string strUpdateQuery = "update PersonTable set FirstName='"
    +this.textboxFirstname.Text+"',LastName='"
    +this.textboxLastname.Text+"' ,Title='"
    +this.textboxTitle.Text+"' ,City='"
    +this.textboxCity.Text+"' ,Country='"
    +this.textboxCountry.Text +"' WHERE PersonID= "+this.textboxPersonID.Text;
    fnExecuteQuery(strUpdateQuery);
    fnRefreshDataSet();  
    MessageBox.Show("Record updated..."); 
  }catch(Exception ex) 
  {
     MessageBox.Show(ex.ToString());
  }
}

Refresh/Cancel the Rows

With the Refresh button, all the textboxes are set to be disabled to edit. The text of the Update button will be changed to "Edit", Save button is disabled (false), Edit, Delete, and New buttons are enabled (true). With the method "fnEnableButtonsNextPreviousLastFirst(true)", all the navigation buttons (Next, Previous, Last, First) will be enabled. And lastly, the method "fnRefreshDataSet()" is invoked to refresh rows in the DataSet. In other words, the first record is going to be displayed.

Here is the code snippet of the method, fnRefreshDataSet():

private void fnRefreshDataSet()
{
  this.dataSet11.Clear(); //clear the contents of dataset 
  this.oleDbDataAdapter1.Fill(this.dataSet11,"PersonTable"); 
}

In Conclusion

I hope this is a useful program for your ADO.NET database projects. If you wish, you can add new textboxes (Phone, Fax, Zip code, etc.) on the Form to extend the program. I'd be glad to receive any suggestions or comments.

Again, here is the link for the first part of this application: Using ADO.NET for Beginners.

Good coding!!

History

• 17^th May, 2005: Initial version

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.