Tuesday, September 29, 2009

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 …

Friday, September 25, 2009

Deleting DataRows

This will seem, to many, to be a really elementary, very basic, newbie (or, noobie if you prefer) sort of topic. And it is. But, I still see questions about deleting DataRows asked a great many times on the Forums.

Most people are used to looping through rows in a DataView using either for or foreach loops, starting at 0 until the you've finished iterating through all the rows. This works fine except in the case where you're deleting rows along the way. In this case, you should use a for loop and iterate backwards through the DataView. This is true whether it's the DefaultView of a DataTable or your own DataView that you’ve created.

for(int i= MyView.Count-1; i >=0; i--)
{
if (MyView[i]["MyCriteria"].ToString() == "DeleteMe")
{
MyView[i].Delete();
}
else
{
// do some processing here
}
}

So, why does this cause problems if you don't iterate backwards? Because, once a Row is marked as Deleted, it's gone from the DataView (since, typically, DataViews do not include Deleted rows), and if you were iterating forward through the DataView, you'd end up skipping past the row immediately after the one that was just deleted thereby not processing every row in your view.

Deleting backwards through the DataView gets around this problem. As does deleting from the DataTable (in either direction) instead of a DataView (although this may not always be the best solution, depending your processing needs).

Tuesday, September 22, 2009

TableAdapters Are Crap!

I haven't played around that much with TableAdapters, mainly because what I *have* seen, I didn't like. The TableAdapter Wizard puts crap into my .xsd that certainly doesn't belong there, such as connection strings. My DataSet .xsd's should be DataSet schema and that is all! The wizard *totally* messed with my existing .xsd and I just don't like all the extra stuff it put in there.

I have heard people say that the TableAdapter stuff can be decoupled from the generated DataSet (which it should be, IMHO ... DataAccess stuff has no business being in a DataSet), but I didn't see any automated way to do it ... just cut/paste elsewhere manually and then of course you'd need to do that each time you made changes (I may have missed something though).

Another thing, it seems to generate code only for one DataTable ... even though my StoredProc returns many tables and my .xsd contains many tables. I may have missed something, but this is what it looks like to me. If this is the case, this is totally useless to me.

One of the problems that I see with the TableAdapter is the inflexibility. The generated class tightly-couples the DataSet to the database, and I don't believe that DataSets should be used that way. I use DataSets simply as a data transport mechanism. My DataSets know nothing about where their data comes from, nor should they.

OK, technically speaking, the DataSet class doesn't know about where its data comes from, just because I'm using a TableAdapter. But the fact the TableAdapter gets generated inside the DataSet.designer.cs means that the DataSet DLL is no longer database agnostic, and that's a very bad thing for correct client-server / SOA architecture. There is no more separation of layers when done this way. The DataAccess classes should be a totally separate layer with their own DLLs, just as the DataSet should live in their own DLLs.

My advice is to skip the TableAdapter Wizard ... it generates a lot of bloated and useless code and you could code it up yourself just as easily and more efficiently. Some of it may be useful to initially see how things work maybe, but then you should just use the DataAdapters yourself instead of using a TableAdapter which basically “wraps” the DataAdapter.  This is just my opinion, so take it for what it's worth.

UPDATE 4/18/2010: I have another related post that may be useful to readers of this post. Please check out my post: Create An Xsd.

Monday, September 21, 2009

Fun With DataSets

Data in a DataRow has several different versions. First, there's the original version. Then, when it's being edited (either in a UI control, like a TextBox or programmatically), it has a Proposed version and once it's done being edited, that becomes the Current version. Sometimes when entering data in a UI control, the row is left in the Proposed state and the Edit needs to be ended programmatically. This doesn't always happen and I'm not entirely sure why it's seemingly random (maybe it's an ADO.NET bug).
Say, for example, that you have made only one change in your entire DataSet and, for that unexplained reason, the DataRow is still in its Proposed version. If you check the DataSet.HasChanges() method, it will return a false. If you do a DataSet.GetChanges() you will not get anything returned. That's not a good thing. UPDATE: See my most recent post, DataBinding DataSets: Directly vs BindingSource ,explaining why this may not apply if you use BindingSource to DataBind your Controls.
But there's a solution to this little gotcha: here's a method I *always* call before I attempt to check for .HasChanges() or do a .GetChanges() before saving data:

protected virtual void CommitProposedChanges(DataSet ds)
{
    if (ds == null)
        return;
 
    for (int nTable = 0; nTable < ds.Tables.Count; nTable++)
    {
        for (int nRow = 0; nRow < ds.Tables[nTable].Rows.Count; nRow++)
        {
            if (ds.Tables[nTable].Rows[nRow].HasVersion(DataRowVersion.Proposed))
            {
                ds.Tables[nTable].Rows[nRow].EndEdit();
            }
        }
    }
}

UPDATE: If you read the comments below, you’ll see that that there was some talk about the speed of for vs foreach. It turns out that I have had a misconception for many, many, *many* years that for was faster than foreach. Perhaps it used to be back in the .NET 1.1 days, but it certainly hasn’t been the case since them. See my recent blog post about this here: http://geek-goddess-bonnie.blogspot.com/2016/02/compare-speed-of-for-vs-foreach.html
Consequently, I’m going to add another method to this post, using foreach instead of for:

protected virtual void CommitProposedChanges(DataSet ds)
{
    if (ds == null)
        return;
 
    foreach (DataTable dt in this.dsGlobal.Tables)
        foreach (DataRow row in dt.Rows)
        {
            if (row.HasVersion(DataRowVersion.Proposed))
                row.EndEdit();
        }

}



 

Friday, September 18, 2009

Keeping DataGrids And Other UI Controls In Sync

Have you ever noticed that sometimes your databound WinForm controls don't seem to want to stay in sync? Most likely you have a problem with the way you're databinding the controls. This is pretty elementary and basic stuff, but you may be surprised at how often I see questions about this very thing.

There are two ways (two different syntaxes) to specify databinding for any control and this results in two distinct binding objects. The problem is that you can't mix and match ... you have to stay consistent throughout the form.

Here are examples of the two different syntaxes:

// If you bound your DataGrid with:
this.oGrid.DataSource = MyDataSet;
this.oGrid.DataMember = "MyTable";

// Then you have to use the following syntax with TextBoxes:
this.txtLastName.DataBindings.Add("Text", MyDataSet, "MyTable.LastName");


//But if you bound your grid with:
this.oGrid.DataSource = MyDataSet.Tables["MyTable"];

//Then you use this syntax for TextBoxes:
this.txtLastName.DataBindings.Add("Text", MyDataSet.Tables["MyTable"], "LastName");

Personally, I prefer the second syntax and use it religiously.

UPDATE: I should also talk about using a BindingSource:

If you’re using a BindingSource, then you won’t have this problem at all, because then you are using the exact same binding object with no way to screw it up!

You could still setup a BindingSource with either syntax:

BindingSource bsMyTable = new BindingSource();

// First syntax:
this.bsMyTable.DataSource = MyDataSet;
this.bsMyTable.DataMember = "MyTable";

// Second syntax:
this.bsMyTable.DataSource = MyDataSet.Tables["MyTable"];

// If you're using Typed DataSets, that second syntax becomes:
this.bsMyTable.DataSource = MyDataSet.MyTable

As before, I prefer the second syntax. And, if you are using Typed DataSets (which I do), the second syntax is even more important since there’s less of a chance of a typo screwing things up.

// And the binding for the two controls will always be the same!
this.oGrid.DataSource = this.bsMyTable;
this.txtLastName.DataBindings.Add("Text", this.bsMyTable, "LastName");

It’s much easier and cleaner, I think, to use a BindingSource!

Thursday, September 17, 2009

Reflection in .NET

UPDATE (11/20/09): There was a bug of sorts in the MyReflectionClass posted below. It was in the LoadAssembly() method and it’s been corrected, because we really do have to take into account a path to the Assembly.  Sorry for any confusion or pulling of hair that this has caused. =0(

This is probably one of the most asked questions I see: How can I dynamically instantiate a class from a string containing the name of the class? And here is what I always reply to that question:

Reflection is a handy way to not only dynamically load controls, but also to dynamically download server-side components automatically.

Below is a simplified version of a Reflection class (other things could be added to it, for example allowing parameters to be passed).

It's easy to use too. Here's one way to use it:

MyReflectionClass oReflection = new MyReflectionClass(assembly, classname);
string message = "";
object o = oReflection.InstantiateClass(ref message);
if (o == null)
MessageBox.Show(message);
else
{
// go on with your processing
}

And here's the simplified class:

public class MyReflectionClass
{
#region Declarations
private string m_AssemblyName;
private string m_ClassName;
#endregion

#region Constructors
public MyReflectionClass()
{
}
/// If your Assemblies live in places other than the same path as your app,
/// you’ll need to modify this class to include code for obtaining the full path.
/// This full-path could be a local path, or a server URL path.
public MyReflectionClass(string assemblyName, string className)
{
this.AssemblyName = assemblyName;
this.ClassName = className;
}
#endregion

#region Methods

public Assembly LoadAssembly(ref string Message)
{
Assembly oAssembly = null;

try
{
// In my real class, I have configuration classes that handles this
string PathOrURL = MethodToObtainPathOrURL();
oAssembly = Assembly.LoadFrom(PathOrURL + this.m_AssemblyName + ".DLL");
}
catch (System.IO.FileNotFoundException)
{
Message = this.m_AssemblyName + " could not be found at the specified URL!" + (char)13 + (char)13 +
"Check that you have correctly entered the component URL and that your network or Internet " +
"connection is functioning correctly.";
return oAssembly;
}
catch (System.BadImageFormatException)
{
Message = this.m_AssemblyName + " is invalid or damaged!" + (char)13 + (char)13 +
"Contact your system administrator.";
return oAssembly;
}
catch (System.Exception ex)
{
Message = ex.Message;
return oAssembly;
}

return oAssembly;
}

public object InstantiateClass(ref string Message)
{
Assembly oAssembly = null;
return this.InstantiateClass(ref oAssembly, ref Message);
}
public object InstantiateClass(ref Assembly oAssembly, ref string Message)
{
object oClass = null;
if (oAssembly == null || oAssembly.FullName.Contains(this.m_AssemblyName) == false)
oAssembly = this.LoadAssembly(ref Message);

try
{
// Create an instance of the desired type from the assembly
if (oAssembly != null)
oClass = oAssembly.CreateInstance(this.m_ClassName);
}
catch (Exception ex)
{
Message = ex.Message;
return oClass;
}

return oClass;
}

#endregion

#region Properties
public string AssemblyName
{
get { return this.m_AssemblyName; }
set
{
this.m_AssemblyName = value.Trim();
if (this.m_AssemblyName.ToUpper().EndsWith(".DLL", true, null))
this.m_AssemblyName = this.m_AssemblyName.Remove(this.m_AssemblyName.Length - 4);
}
}
public string ClassName
{
get { return this.m_ClassName; }
set
{
this.m_ClassName = value.Trim();
if (this.m_ClassName.Contains(this.m_AssemblyName) == false)
this.m_ClassName = this.m_AssemblyName + "." + this.m_ClassName;
}
}
#endregion
}

Monday, September 14, 2009

3 Years Later

Wow, how time flies! Three years ago, I thought I'd try my hand at blogging. Guess what ... didn't do a thing! But I have a great excuse ... that was just about the time that things started getting crazy at work. Well, I don't have to worry about that any more. I have joined the ranks of the unemployed ... unfortunately ... and through no fault of my own, I might add. So, now is a good time to start putting more of an effort into this blogging thing I think. For my blog posts here, I intend to utilize the questions (and my answers) to the thousands of forum threads I've participated in over the last 7 years (I'm just guessing ... I didn't actually count them to see if there are thousands ... seems like it though). Might be interesting. Might be informative. Might also be boring, but we'll see. ;0)