DataAccess – Part I

DataAccess in Windows Forms is a much misunderstood concept. I cannot count the number of times I see people dropping DataAdapters and TableAdapters directly on a Form and accessing their database directly from the Form in this manner. This is a major no-no!!

The DataAccess classes should be totally separated from your User Interface (UI) or, as some people prefer to call it, the Presentation Layer. And forget TableAdapters (see my blog post about them)!

Interaction between the DataAccess layer and the UI layer can be done with either Business object classes or, as I prefer, with DataSets (preferably, strongly Typed DataSets).

Base DataAccess Class

First, you need to have a base DataAccess class that all of your other DataAccess classes should inherit from, and get the connection in it's constructor: Let’s start with the simple DataAccess examples and work to the more complex (but more flexible) version in Part II of this blog.

public class BBDataAccess
{
    protected SqlConnection oConnection;

    public BBDataAccess
    {
        // Note that I wouldn't actually hard-code the connection string like this. 
        // It should be in your config settings. 
        // Old (1.1), obsolete but still works:
        // string MyConnString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
        // Current (2.0)
        // string MyConnString = System.Configuration.ConfigurationManager.AppSettings["ConnectionString"];
 
       this.oConnection = new SqlConnection("server=(local);database=MyDataBase;uid=sa;pwd=MyPassword");
    }
}

Depending on how complicated your application is, you may want to have a separate DataAccess class for different functionality, each one sub-classing from the BBDataAccess base class.

Getting Data

Retrieving the data is easy. Note that this DataAccess method returns a DataSet, as mentioned above:

public class MyDataAccess : BBDataAccess
{
    public DataSet GetMyData()
    {
        // Note that a Fill does not require a Connection Open/Close. The Fill leaves
        // the connection in the state it was found (leaves it open if it was open, 
        // and if it was closed, it opens it, Fills, then closes again).
    
        SqlDataAdapter da = new SqlDataAdapter("select * from bob", this.oConnection);
        DataSet ds = new DataSet();
        da.Fill(ds, "MyTable");
    
        return ds;
    }
}

Saving Data

You have a few more options when updating the database.

• DataAdapter.Update() method, with CommandBuilder

First, you can use the Update method of the DataAdapter. In order for this to work, the tables in your database must have a PrimaryKey defined.

You can do it using the CommandBuilder, which will generate update commands for you. (Note: if you use a Stored Proc, the CommandBuilder only generates the proper insert/update/delete commands for the first table retreived from the Stored Proc.) (Another Note: using the CommandBuilder entails an extra round-trip to the server.):

public void UpdateMyData(DataSet ds)
{
    // The same applies for the Update. It's not necessary to Open/Close the connection.
    SqlDataAdapter da = new SqlDataAdapter("select * from bob", this.oConnection);
    SqlCommandBuilder sb = new SqlCommandBuilder(da);


    da.Update(ds);
}

• Without CommandBuilder

Or you can create the various update commands yourself instead of using the CommandBuilder:

public void UpdateMyData(DataSet ds)
{

    SqlCommand sc = new SqlCommand();
    sc.Connection = this.oConnection;
    da = new SqlDataAdapter(sc);

    da.InsertCommand = new SqlCommand("Insert into bob (xyz, abc) VALUES ( @xyz, @abc )", sc.Connection);
    
    // Note that with these parameters, the update will simply insert "xyz" and "abc" 
    // into every inserted row. Probably not what you want. 
    // See the Do-It-Yourself option below for a better idea.
    da.InsertCommand.Parameters.AddWithValue("@xyz", "xyz");
    da.InsertCommand.Parameters.AddWithValue("@abc", "abc");    
 
    // do the same for da.DeleteCommand & da.UpdateCommand
 
    da.Update(ds);
}

• Do-It-Yourself

Or, you can take total control, not use the da.Update() and do it all yourself (this is basically the same code that gets done behind the scenes by the da.Update() method:

public void UpdateMyData(DataSet ds)
{
    SqlCommand sc = new SqlCommand();
    sc.Connection = this.oConnection;
    sc.Connection.Open();
    foreach (DataRow Row in ds.Tables[0].Rows)
    {
        switch (Row.RowState)
        {
            case DataRowState.Added :
                // or use a StoredProc, which I prefer
                sc.CommandText = "Insert into bob (xyz, abc) VALUES ( @xyz, @abc )";
                sc.Parameters.Clear();
                sc.Parameters.Add("@xyz", Row["xyz"]);
                sc.Parameters.Add("@abc", Row["abc"]);
                sc.ExecuteNonQuery();
                break;
                
            // Do the same for DataRowState Deleted and Modified
            case DataRowState.Deleted :
                break;
            case DataRowState.Modified :
                break;
        }
    }
    sc.Connection.Close();
}

Summary

So, that’s it for this blog. In Part II, I’ll discuss creating a base DataAccess class by programming to the various IDb interfaces. By doing this, all your DataAccess sub-classes are totally "disassociated" from knowing what your back-end database is. Stay tuned …