Understanding ADO .Net

Understanding ADO .Net

In the previous post we saw how to create a simple WinForm application. Now let's extend that application to have a separate Data Layer. We will be using ADO .NET as our primary data access technology. Again this if for beginners and I know that Microsoft whats new developers to use Entity Framework going forward, but using ADO .NET will server as a good tool to get your hands dirty with data access concept in general.

So, let's get started. Open up the solution we built in the previous blog post.

Adding Data Project

Now as per what we discussed in class, we need build our Data Access Layer into a separate class library. So let's do that next.

• Right click on the solution and click Add New Project.

• Select the Class Library Project Template.

  You can see in the solution explorer, that we have three projects now under our solution.

• Next, let's rename the Class1.cs file to DBHelper.cs

Preparing the Data Project

In our data access project we will need to make use of our Model project so let's add reference of the Model project to the Data project.

The Category class defined in Model project looks like this.

public class Category
{
    public int CategoryID
    { get; set; }

    public string Name 
    { get; set; }

    public bool IsActive 
    { get; set; }

    public DateTime DateCreated 
    { get; set; }
}

On the sql sever side I have crated a database called DemoDB and in there a table Named Category. Following is the design view of the table.

Introducing ADO .NET

• ADO .NET packs bunch of classes we will be using to access data in our applications. Let's add the connection string in our application that points to the database.

  public static string ConnectionString = "Server=(local);Database=DemoDB;Trusted_Connection=True;";

• Now let's add a simple method that inserts data we are going to collect from our Form and insert the data into this SQL table.

  public static bool AddCategory(Category newCategory)
  {
      bool isSuccess = false;
      string sql = @"INSERT INTO [dbo].[Category]
                                         ([Name]
                                         ,[IsActive]
                                         ,[DateCreated])
                                         VALUES
                                         ('"+ newCategory.Name + "','"+
                                         newCategory.IsActive +"','"+
                                         DateTime.Now +"')";
      SqlConnection conn = new SqlConnection(connectionString);
      try
      {   
          SqlCommand cmd = new SqlCommand(sql, conn);
          conn.Open();
          int result = cmd.ExecuteNonQuery();
          if(result > 0)
          {
              isSuccess = true;
          }
      }
      catch(Exception ex)
      {
      }
      finally
      {
          conn.Close();
      }
      return isSuccess;
  }
  

Modifying the Form1 UI

• Now that everything is setup from Database and Data Access Layer point of view, let's update our form by adding controls like this.

Notice how have named these controls, that way it is easier to write our backend without worrying about which controls refers to what property.

• Next, add the reference of the Data and Model project to your Winform application.

• Finally, let's double click the button and you will get the click event for the button. Here we just need to collect all the information user has entered in the database and call our Data Access Layer method.

  private void btnAddCategory_Click(object sender, EventArgs e)
  {
      Category c = new Category();
      c.Name = txtName.Text;
      c.IsActive = chkIsActive.Checked;
      c.DateCreated = DateTime.Now;
      bool isAdded = DBHelper.AddCategory(c);
      if(isAdded)
      {
          MessageBox.Show("Categroy Inserted Successfuly");
      }
      else
      {
          MessageBox.Show("Error Inserting Category");
      }
  }
  

That's it. Let's go ahead and run the project. Enter some data and click Add. You should see the message similar to below. If you see error message put a break point on the Catch block of our Data Access Layer method to see what is causing the exception.