Geek Goddess

NAS Gone Bad

2011-11-23T08:54:00.001-08:00

Today’s post is not about my usual topic (.NET programming), but about the solution I used when my NAS (Network Attached Storage) went bad. It was a Western Digital My Book World Edition with a 1 TB Caviar Green SATA WD10EADS 5-1/4 inch drive (the one with the white light). The thing is, I was pretty sure my drive (and therefore my data!) was just fine. It’s just that the enclosure and all the electronics that make it “network attachable” was the part that went south. Or so I hoped.

I took it apart (did I mention that I *hate* doing hardware stuff!!) and got the drive out. I thought I bookmarked a link to a video showing how to take it apart, but I guess I didn’t bookmark it and I can’t find the same video at the moment. Google for it though, you’ll find some “how-to” for taking the drive out of the enclosure. If you’re a whiz at hardware, you probably don’t even need a how-to video. It’s not rocket-science … even I could do it! ;0)

Then I hooked it up to a SATA-to-USB converter and plugged the USB into my laptop. At this point, Windows Explorer would not assign a drive letter, but I could see the drive with the Disk Management utility. It was listed as having a Healthy but Unknown partition.

After a little more Googling, I discovered that the drives that get put into these NAS devices are just about always formatted to some kind of Linux format … not readable by Windows!! Grrrr …. now I have a lot more Googling to do!

I found lots of discussions on the topic … apparently some of these NAS devices (not just Western Digital) give up the ghost on a regular basis! You can buy NAS devices that are disk-less … in other words, they’re just the enclosure with all the electronics for making it network attachable and you supply your own drive (I assume an unformatted drive). Which means that if Western Digital sold the same enclosure disk-less for this particular model, then I could just buy the enclosure, pop in my drive and be back in business!!! Unfortunately, I couldn’t find one and I suspect that they don’t exist for my model. Grrrr …. back to the drawing board!

I found a ton of links talking about how to create a Linux boot disk and boot your machine to Linux and enter in a whole bunch of gibberish Linux commands and it may (or may not) do the trick. There were like 20 steps in some of these how-to discussions! And I know absolutely nothing about Linux. I really did not want to mess with this! So, I kept looking …

Then I found a nifty little driver that could allow a Windows machine to be able to read a Linux formatted drive! Because it’s a driver, it would work seamlessly with Windows. This was just what I was looking for! And it’s free! I downloaded it (Ext2 Driver for Windows). It will work for both Ext2 and Ext3 formatted drives. It installed just fine on my computer. Unfortunately, it didn’t do the trick … why? Because my drive wasn’t formatted with Ext2 or 3!! More Googling showed me that it was probably an XFS formatted drive. Arrggghhh!!! Will it never end?!?!?!

One more round of Googling and I finally found a solution! It wasn’t free, but it was only about $30. I could live with that if it worked (UFS Explorer). It’s a utility that can read all kinds of partition formats and there was a free trial download (limited to copying files smaller than 64KB) … so I downloaded it to take it for a testdrive. If it worked, I would buy it. You can only read/copy from the drive … you can’t write to it. But I intended to copy all the files off of the drive, then reformat it to NTFS and install it directly on my machine (yuck … more hardware stuff). Luckily I already had a 2 TB USB drive and I had plenty of room on that drive. The trial showed that it copied the small files flawlessly, so I plunked down the $30 and bought it. It also worked flawlessly, copying every single one of my files off of the XFS partition! Wow! Disaster averted!

Now, all that’s left to do is reformat the 1 TB and stick it in my machine. But, since that’s hardware stuff, I’ll procrastinate on that task just a little bit longer. =0)

XmlSerializer and Implemented Interfaces

2011-10-08T17:09:00.001-07:00

Here’s the scenario:

Get some data from calls to a third-party API.
Serialize that data to XML.
Use XSLT to Transform that XML into a common set of classes.

The API utilizes Interfaces for all the objects it returns and it would have been nice if I could have simply used either XmlSerializer or DataContractSerializer on the objects I received, but of course nothing is simple. Suffice it to say that it doesn’t work, for either serializer.

So, I’ve spent the last day or two working on implementing my own classes from the various third-party Interfaces for the objects I need. Basically, I’ll be using the API to get some data. Then I’ll have to copy the data into my own classes and then serialize it to XML.

Theoretically, creating classes that implement on Interface should be easy (simply right-click on the Interface and choose “Implement Interface”. But, nooooo … even though I’m using Visual Studio 2010, it still generates properties the same way it did before the “invention” of automatic properties. In other words, instead of generating a property like this:

       public int VehicleID { get; set; }
    

The property gets generated like this:

      public int VehicleID
{
    get
    {
        throw new NotImplementedException();
    }
    set
    {
        throw new NotImplementedException();
    }
}
    

When you’re talking about implementing a ton of properties, you don’t want to have to fix each of these manually. I knew I could find a way to generate these Interface properties differently, but I had forgotten all about code snippets. Had the old “slap on the forehead” moment when I read this blog post, written by Daniel Cazzulino, about how to do this:

http://blogs.clariusconsulting.net/kzu/how-to-replace-default-interface-property-implementation-expansion-with-automatic-properties/

Duh! Why hadn’t I remembered how to do that myself? Gettin’ old I guess. =0(

But wait, there’s more. Some of these Interface properties did NOT have setters and so they get generated with only the get. While this doesn’t cause a problem with DataContractSerializer, the XmlSerializer will not work unless you have both getters and setters. Daniel mentions, in an update at the end of his blog post, how to take care of this, by producing private setters for the generated property:

      public int IncidentID { get; private set; }
    

Unfortunately, because I’m copying data to my own classes, my class needs to have a public setter. In my case, this doesn’t really matter as far as the functionality of these classes, because I’m only using them so I can serialize the data to XML, so I don’t care that the “set” is exposed publicly.

The two serializers produce very different XML. The task I will tackle next week is to see if that matters when I use that XML for my XSLT Transformations. For those interested, I’ll probably post the results of what happens with that little experiment next week.

DISTINCT - LINQ versus DataSet

2011-09-30T21:39:00.001-07:00

Wow, it’s been way too long since the last time I wrote a blog post. I’ve been busy, but that’s really no excuse. In my last post in April, I wrote about using LINQ with Typed DataSets. I did some benchmark comparisons with Selects and LINQ was considerably faster. Today I’m going to compare the DISTINCT capabilities of LINQ versus DataSets.

As before, I’m using the same set of data … a DataTable containing 270,000 rows. I’ve filled the DataTable such that there’s a random number of duplicates in the rows, but it seems that the DISTINCT values always fall between 4750 and 4800 rows.

There are some interesting results. When selecting DISTINCT values, over all columns in the DataTable, it definitely is best to use the old DataSet methods rather than LINQ. And, also surprising, is that the old-style 2.0 Typed DataSets (and likewise, plain old vanilla DataSets, not Typed) are actually faster than newer 3.5 Typed DataSets (refer to my last post, linked to above, that explains what is the difference between the two).

       // ds20 is a 2.0 Typed DataSet (a regular DataTable)
// ds35 is the 3.5 Typed DataSet (a TypedTableBase) 
DataTable dt20 = ds20.Personnel.DefaultView.ToTable(true);
DataTable dt35 = ds35.Personnel.DefaultView.ToTable(true);

// Note that if you MUST use the DataRowComparer.
// If you don't you get all rows returned ... it won't find the duplicates.
DataTable dtLinq20 = ds20.Personnel.AsEnumerable()
    .Select(row => row).Distinct(DataRowComparer.Default)
    .CopyToDataTable();

// note that the only difference in syntax between
// 2.0 and 3.5 is that you don't use .AsEnumerable()
DataTable dtLinq35 = ds35.Personnel
    .Select(row => row).Distinct(DataRowComparer.Default)
    .CopyToDataTable();
      

Benchmarking results show that the DefaultView.ToTable() is 7 times faster than LINQ for ds20 (and 8.5 times faster for ds35). The DefaultView.ToTable() is only slightly faster for ds20 than ds35 ( not significantly though) and with LINQ, ds20 is about 1.5 times faster than ds35. Most peculiar!

ToTable (ds20): 77,937,500 ticks
ToTable (ds35): 90,718,750 ticks
LINQ (ds20): 554,656,250 ticks
LINQ (ds35): 781,218,750 ticks

But, there’s where the superiority of the DefaultView.ToTable() ends. Once you start looking for specific columns to be distinct, LINQ is faster. Let’s look at the one column scenario: First, the DataSet code (since all code for the DataSet methods are exactly the same for ds20 as for ds35, I’ll only show one):

      DataTable dt20 = ds20.Personnel.DefaultView.ToTable(true, "firstname");
      

Pretty straightforward … nothing fancy. The benchmark times for both ds20 and ds35 are almost identical.

But now, the LINQ gets trickier. Unfortunately, you’ve got to create the resulting DataTable (including the column) before you can “fill” it from the LINQ query. If there’s a workaround for this, I sure haven’t found it. Here are the two LINQ queries:

      // note that you have to create the DataTable first, 
// AND add the column!
DataTable dt = new DataTable();
dt.Columns.Add("firstname");
DataTable dtLinq20 = ds20.Personnel.AsEnumerable()
    .Select(row =>
    {
        DataRow newRow = dt.NewRow();
        newRow["firstname"] = row.Field<string>("firstname");
        return newRow;
    })
    .Distinct(DataRowComparer.Default).CopyToDataTable();

// note that there are 3 differences in syntax for ds20 & ds35:
// 1) as before, you don't use .AsEnumerable()
// 2) you must check for null
// 3) you can use the typed column syntax (row.firstname)
DataTable dt = new DataTable();
dt.Columns.Add("firstname");
DataTable dtLinq35 = ds35.Personnel
    .Select(row =>
    {
        DataRow newRow = dt.NewRow();
        newRow["firstname"] = row.IsfirstnameNull() ? "" : row.firstname;
        return newRow;
    }).Distinct(DataRowComparer.Default).CopyToDataTable();
      

Now for the benchmark numbers. LINQ is more than 5 times faster than the DefaultView.ToTable() … (5.25 times faster for ds20 and 5.5 for ds35).

ToTable: 15,406,250 ticks
LINQ (ds20): 2,937,500 ticks
LINQ (ds35): 2,781,250 ticks

As more columns get added, the LINQ advantage becomes diminished. At some point, the DefaultView.ToTable() methodology becomes preferable. I suspect that that point depends both on the number of columns you wish to have distinct, as well as the number of rows that you are processing. The syntax for the DefaultView.ToTable() becomes slightly different when you have more than one column. Here it is:

      DataTable dt20 = ds20.Personnel.DefaultView
    .ToTable(true, new string[] { "firstname", "lastname" });
      

The LINQ syntax doesn’t change, you just keep adding the columns:

      DataTable dt = new DataTable();
dt.Columns.Add("firstname");
dt.Columns.Add("lastname");
DataTable dtLinq35 = ds35.Personnel
    .Select(row =>
    {
        DataRow newRow = dt.NewRow();
        newRow["firstname"] = row.IsfirstnameNull() ? "" : row.firstname;
        newRow["lastname"] = row.IslastnameNull() ? "" : row.lastname;
        return newRow;
    }).Distinct(DataRowComparer.Default).CopyToDataTable();
      

In the case above, of two columns, LINQ was just slightly under 5 times faster.

So, just for giggles, I thought I’d benchmark half the columns. My DataTable has 32 columns, so I tried a distinct on the first 16 columns in my DataTable. And now, it seemed, that we were back to having DefaultView.ToTable() being superior by about 7 times (turns out I was wrong … keep reading). Now, my curiosity got the better of me … I wasted some more time trying to find “the sweet spot” … the number of columns where the performance was about equal. After all, I was a math major in college and I love to play with numbers!

But, as I played more with this, I discovered something very interesting. The time it takes for LINQ depends on the order of the columns!!! In other words, if the first column in your list doesn’t have a lot of distinct values (mine had 7), but the next one in your list does (mine had 4700+), it will take a lot longer. If you swap the column order of those two columns, BAM! Very fast! Instead of the LINQ query being 7 times slower than the DefaultView.ToTable(), it was now 2.25 times faster!

Curiously, this doesn’t seem to matter with the DefaultView.ToTable() … it benchmarked the same amount of time with the columns in any order!

ToTable: 42,750,000
LINQ (original order of 16 columns): 305,375,000
LINQ (swapped order of columns): 19,125,000

I suspect that my very first test, the one with all the columns,suffered from this anomaly … in other words, the LINQ query was so very much slower because that problem column (the one with only 7 distinct values) happened to be the first column in the DataTable. and the syntax of that query didn’t specify columns.

After discovering this quirk, I went back and re-ran the LINQ query that used only 2 columns, but I did it this time with these two particular columns where I noticed this problem. Sure enough, with only a two column distinct query, if the columns were in the wrong order, the query slowed to a crawl … becoming 14.5 times slower than the DefaultView.ToTable()!!! Wow!

So, what’s the takeaway from this little experiment? Good question … I guess it’s that the data makes the difference. If you’re pretty sure about the data in the columns not being too lopsided, distinct-wise, then LINQ is clearly faster for probably all scenarios except the one including all columns (I did not test this, it’s only a guess … the most columns I tested were half). However, if you’re not certain of the distinctness of the data in your columns, perhaps it’s best to stick with the tried-and-true DefaultView.ToTable() method.

LINQ With DataSets

2011-04-06T18:19:00.001-07:00

Today's blog post is going to be about using LINQ with DataSets. As anyone who reads my blog regularly (or various forum posts) might already know, I've been a big fan of Typed DataSets since the very beginning of .NET (all the way back to the pretty buggy 1.0 version). However, LINQ is fairly new to me. LINQ *itself* is not new to me ... it has been around for a few years now and I've certainly been aware of it. But I haven't utilized it much because, until .NET 3.5, there was no real support for Typed DataSets and that's really all I wanted to be able to use if for. Sure, you could still use LINQ with both regular DataSets and Typed DataSets ... as long as you used .AsEnumerable() ... but you couldn't take advantage of the Typed nature of a Typed DataSet, so I didn't see any point in messing around with it.

I've recently begun using it more with Typed DataSets because, surprisingly to me, for some things it seems to be faster. Since .NET 3.5, Typed DataSets have been getting auto-generated slightly different than they have in the past (and I'm not talking about the TableAdapter generation, which I despise ... see my blog post about avoiding that fiasco: http://geek-goddess-bonnie.blogspot.com/2010/04/create-xsd.html).

Prior to 3.5, the DataTable definition in a generated Typed DataSet looked something like this:

public partial class PersonnelDataTable : global::System.Data.DataTable, global::System.Collections.IEnumerable
{
    // rest of the code here
}

The minor difference is this:

public partial class PersonnelDataTable : global::System.Data.TypedTableBase<PersonnelRow>
{
    // rest of the code here
}

TypedTableBase is derived from DataTable, so really nothing changes. But, it allows the DataTable to be used in LINQ queries without specifying .AsEnumerable(), allows us to use the Typed column name properties of the DataRows in our LINQ queries *and* it's also much faster.

As I already mentioned above, LINQ *can* easily be used with regular DataSets/DataTables (it's use is NOT limited to Typed DataSets), but I am not including examples of that in this blog post. As with the 2.0 Typed DataSets, all that is needed is to use .AsEnumerable() with the DataTable, so the code will be almost identical.

So, let's see a few examples.

First, let's look at a typical use for a DataSet, selecting some rows. Using LINQ with a Typed DataSet ends up being quite a bit faster than using the DataTable.Select() method. In all my tests, I used a DataTable containing 270,000 rows. The Select in this test, selected 30,000 of those rows. I also did comparisons between 2.0 DataTable and 3.5 TypedTableBase, just for the heck of it.

// ds20 is a 2.0 Typed DataSet, which uses plain a plain old DataTable
DataRow[] dsSelect20 = ds20.Personnel.Select("lastname = 'Berent'");
DataRow[] linqSelect = ds20.Personnel.AsEnumerable()
 .Where(row => row.Field<string>("lastname") == "Berent")
 .Select(row => row).ToArray();

The above are your only two choices when using untyped or 2.0 Typed DataSets. Benchmark timing results show that LINQ is about 6 times faster than the old DataTable.Select() method:

Select: 5,625,000 ticks
LINQ: 937,500 ticks

If you're using 3.5 Typed DataSets, you have a few more options with LINQ. Generally, your LINQ statement will look like this:

// ds35 is a 3.5 Typed DataSet, which uses the new TypedTableBase class for it's DataTable
DataRow[] linqSelectLastName = ds35.Personnel
 .Where(row => row.lastname == "Berent")
 .Select(row => row).ToArray();

Note the differences: you don't need Personnel.AsEnumerable() and you can use the typed row column names, row.lastname. There is one caveat though to the above syntax. If any row in the Personnel table contains DBNull.Value in the lastname column, the above syntax will throw an exception. You must also check for the null, so the actual statement will need to be this:

DataRow[] linqSelectLastName = ds35.Personnel
 .Where(row => row.IslastnameNull() == false && row.lastname == "Berent")
 .Select(row => row).ToArray();

You could also use the untyped syntax of the column and then you don't need to check for DBNull, but I like to take advantage of the typed nature of a Typed DataSet. That is the purpose of them, after all. =0) Also, using the untyped syntax is slightly slower, but probably not significantly so.

DataRow[] linqSelectUntypedLastName = ds35.Personnel
 .Where(row => row.Field<string>("lastname") == "Berent")
 .Select(row => row).ToArray();

Incidentally, my data *does* contain DBNull.Value in the lastname column, so that my benchmark timing tests are valid. The timing for the DataSet.Select() yields roughly the same results either way, but the LINQ is about 1.5 times faster with the TypedTableBase than with a regular DataTable, making LINQ about 9 times faster than a regular DataSet.Select()!! Here are the results using a 3.5 Typed DataSet:

Select: 5,937,500 ticks
LINQ: 625,000 ticks
LINQ (untyped syntax): 781,250 ticks

So, I think that I'll wind up this post for now. There are plenty of other uses of LINQ that I should compare with old DataSet/DataTable functionality, but I think I'll save that for another post. This one is long enough!

Until next time ... happy coding!

Passing Data Between Forms

2011-01-30T21:57:00.001-08:00

There are several different approaches one could make to "pass" information, such as a DataSet, from one Form to another. I see this question asked a lot on the forums, so here's a quick summary. You could use one of these approaches, or combine several of them so that the developer has the flexibility to use whichever approach is appropriate. I'm going to show 3 different solutions to the problem:

First, let's assume we have a MainForm, and it instantiates and shows Form1, and needs to pass a DataSet to it.

1) Form1 can have a DataSet parameter in its constructor:

public class Form1 : Form
{
    private DataSet oData;
    public Form1(DataSet ds)
    {
        this.oData = ds;
        InitializeComponent();
    }
}
// called from MainForm like this:
Form1 oForm = new Form1(this.dsCustomer);
oForm.Show();

2) Form1 can expose its DataSet field as a public Property:

public class Form1 : Form
{
    public DataSet oData {get;set;} 
    public Form1()
    {
        InitializeComponent();
    }
}
// called from MainForm like this:
Form1 oForm = new Form1();
oForm.oData = this.dsCustomer;
oForm.Show();

3) Another option is to use Interfaces. For example, say that your Form1 allows the user to make some changes to the data in the DataSet and you'd like to have MainForm automatically reflect those changes ... even while you're still working in Form1.

First, let's define the Interface:

public interface IDisplayCustomer
{
    CustomerDataSet dsCustomer {get;set};
    void DisplayCustomer();
}

MainForm would then be defined like this:

public class MainForm : Form, IDisplayCustomer
{
    public CustomerDataSet dsCustomer {get;set;}
    public void DisplayCustomer()
    {
        // code here to do stuff with this.dsCustomer
    }
    ...
    // then code elsewhere to instantiate and show Form1:
    this.dsCustomer = new CustomerDataSet(); // or other code to fill the dataset
    Form1 oForm = new Form1(this);
    oForm.Show();
}

And Form1 would look like this:

public class Form1 : Form
{
    private CustomerDataSet oData;
    private IDisplayCustomer CallingControl;
    public Form1 (IDisplayCustomer callingControl)
    {
        this.CallingControl = callingControl;
        this.oData = callingControl.dsCustomer;
    }
    public void DoStuff()
    {
        // code to do stuff with this.oData 
        ...
        // and then redisplay in calling form
        this.CallingControl.DisplayCustomer();
    }
}

TransactionScope and SqlServer

2010-12-23T09:03:00.001-08:00

Well, here it is, almost the end of the month, and as usual I’m behind in my blog posting. It seems that I’m lucky to get one post a month this past year. But this month, being almost the end of December, I should at least wish all my readers “Happy Holidays”.

Now, on to today’s topic. This may be old news; after all, TransactionScope has been around since 2.0, but I hadn’t been using it back then. I’ve just really started utilizing it during the past year and recently discovered some interesting gotchas.

First, let’s start with a little snippet of code:

using (TransactionScope scope = new TransactionScope())
{
    try
    {
        // transactional stuff in here, perhaps calls to SQL Server

        ...

        scope.Complete();
    }
    catch (Exeception ex)
    {
        // do whatever you need to here, we will NOT complete the transaction
        Console.WriteLine("*** ERROR " + ex.Message);
    }
}

Now, this is all well and good … or so you think. But, there is one little gotcha here …. and that is that when TransactionScope is instantiated without any parameters, the default for its IsolationLevel is Serialized … the most restrictive that there is. On the other hand, for Transactions in SQL Server, the default IsolationLevel is ReadCommitted and, for the most part, that is what you typically will want to use.

In my case, I’m doing a lot of writing to the database, in lots of different threads. If I don’t mess with the TransactionScope, then IsolationLevel.Serialized will severely slow things down, because the database tables, in effect, get locked … because Serializable prevents new data from being added. Wow, not really what I intended!

Well, OK, so I guess we can change the above snippet to this:

using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions(){IsolationLevel = IsolationLevel.ReadCommitted}))
{    
    try    
    {        
        // transactional stuff in here, perhaps calls to SQL Server 
              
        ...        

        scope.Complete();    
    }    
    catch (Exeception ex)    
    {        
        // do whatever you need to here, we will NOT complete the transaction        
        Console.WriteLine("*** ERROR " + ex.Message);    
    }
}

Wow, that’s messy, but it’s necessary.

But wait, there’s more!! If your current transaction is operating under another ambient transaction (“ambient” is like a parent transaction … I don’t know why they didn’t use the same terminology), the IsolationLevel has to be the same! An exception is thrown if it’s not! So, you’ve got to remember to use this same syntax every time you instantiate a TransactionScope! Yuck!

But, fortunately, there’s a better idea. It’s much easier and cleaner to use a Utility class with static methods for this sort of thing:

public class Utils
{
    /// <summary>
    /// The reason for this method to return a TransactionScope is because the default IsolationLevel 
    /// when not specified is Serializable, which is the most restrictive level and will cause all kinds
    /// of deadlock problems with Sql Server!!
    /// 
    /// We can add more overloads later if we want more options.
    /// The TransactionScoope that gets returned here is Required and IsolationLevel.ReadCommitted.
    /// </summary>
    /// <returns></returns>
    public static TransactionScope GetTransactionScope()
    {
        return new TransactionScope(TransactionScopeOption.Required, new TransactionOptions(){IsolationLevel = IsolationLevel.ReadCommitted});
    }
}

And now, we make sure that all our developers know to always use this method when creating a TransactionScope, like this:

using (TransactionScope scope = Utils.GetTransactionScope())
{    
    try    
    {        
        // transactional stuff in here, perhaps calls to SQL Server 
              
        ...        

        scope.Complete();    
    }    
    catch (Exeception ex)    
    {        
        // do whatever you need to here, we will NOT complete the transaction        
        Console.WriteLine("*** ERROR " + ex.Message);    
    }
}

Happy Coding and Happy Holidays!

ForceBind

2010-11-20T17:11:00.001-08:00

There are situations in a Windows.Form where the value of a control (such as the value in the .Text property of a TextBox) does not get propagated to its databound object. This can happen as the result of a MenuItem click or a ToolBar button click because these controls do not cause the current control (the ActiveControl, which is the TextBox in my example) to lose focus, thus not forcing the control's value into its databound object. You need something that will force this for every control you have. It can get complicated.

I have a method that I have named ForceBind() in most of my controls (those that have it implement an Interface I have created for this type of behavior). If the control is a container object that implements that Interface (such as a Panel, UserControl, etc.) it will call the ForceBind() method of *it's* ActiveControl (if *that* control implements the Interface).

The actual ForceBind() method is pretty complicated in my controls and relies on the fact that I have a DataBind() method on my controls that tell it which DataTable and DataColumn I am binding that control to. However, I've done a little experimenting and I see that we can find other ways of finding those fields.

I realize that nowadays, most people use a BindingSource rather than Binding directly to a DataTable or DataView. I have not updated my classes yet to differentiate between the various binding DataSources, but in the code I will show below, it is not all that difficult to add that functionality. I leave that as an exercise for the reader …

Here's the ForceBind() method for MyTextBox (along with the code for determining the m_BoundTable, m_BoundColumn and m_BoundProperty fields):

private DataTable m_BoundTable    = (DataTable)this.DataBindings[0].DataSource;
private string    m_BoundColumn   = this.DataBindings[0].DataBindings[0].BindingMemberInfo.BindingMember;
private CurrencyManager oCurrency = (CurrencyManager)this.BindingContext[m_BoundTable];
public virtual void ForceBind()
{
    if (this.DataBindings.Count == 0)
        return;
    int nRow = -1;
    if (this.m_BoundTable != null && this.m_BoundColumn != "")
    {
        if (this.oCurrency != null)
            nRow = this.oCurrency.Position;
        if (nRow >= 0)
        {
            object oValue = this.Text;
            System.RuntimeTypeHandle handle = System.Type.GetTypeHandle(this);
            System.Type eType = System.Type.GetTypeFromHandle(handle);
            ConvertEventArgs e = new ConvertEventArgs(oValue, eType);
            this.ParseHandler(this, e);
        
            DataRow row = this.m_BoundTable.DefaultView[nRow].Row;
            if (row[this.m_BoundColumn] != e.Value)
            {
                try
                {
                    row[this.m_BoundColumn] = e.Value;
                }
                catch (Exception)
                { }
                        
                this.OnValidated(new EventArgs());
            }
        }
    }
}

You would call this method whenever there's the possibility of an ActiveControl not losing its focus, say in your own base class for your ToolBar Buttons, for example.

Multi-Tier Applications

2010-10-31T21:21:00.001-07:00

Reading the MSDN forums, I can’t count the number of times I see posters putting all their eggs in one basket, so to speak. Their UI forms contain their Business logic AND their Data Access logic. Their DataSets are defined in the same UI project. Microsoft and Visual Studio, unfortunately, makes this too easy to do … what with all the drag-and-drop stuff from the Server Explorer directly onto a Form designer. I’m sorry, but this is really a great big no-no!!!

These activities should be broken up into multiple tiers, or layers. To simplify, think of three different projects/dlls in your solution. MyUI, MyBiz, and MyDataAccess (I'd actually throw in a 4th one, MyWebService, but let's not complicate matters at this point. Oh wait, I'd have a separate DataSet project too, but as I said, let's keep it simple for now). In your MyUI project, you'd have all the different forms that you plan to use in your UI. The same for MyBiz and MyDataAccess projects, all the different Biz classes and DataAccess classes.

So, to start, your form would be similar to this (to get your data when the form first opens):

using MyCompany.MyApp.Business.MyBiz;
namespace MyCompany.MyApp.WinUI.MyUI
{
  public class MyForm : MyBaseForm
  {
    private long          CustomerKey;
    private MyDataSet     dsData;
    private CustomerBiz   oBiz;
    public MyForm(long key)
    {
      this.CustomerKey = key;
      InitializeComponent();
      this.FillData();
    }
    public void FillData()
    {
      // To simplify, I'm directly calling a Biz class.
      // In reality, I use a Web Service here instead
      // which in turn calls the Biz class.
      oBiz = new CustomerBiz();
      dsData = oBiz.GetCustomer(this.CustomerKey);
    }
  }
}

Now in your MyBiz project, you'd have a Biz class:

using MyCompany.MyApp.DataAccess.MyDataAccess
namespace MyCompany.MyApp.Business.MyBiz
{
  public class CustomerBiz
  {
    private MyDataSet dsData;
    public MyDataSet GetCustomer(long CustomerKey)
    {
      CustomerAccess oDA = new CustomerAccess();
      this.dsData = oDA.GetCustomer(CustomerKey);
      // if you have other Biz things to do to this customer
      // do it here before returning the DataSet
      return this.dsData;
    }
  }
}

And, lastly, in your MyDataAccess project, you'd have this class:

namespace MyCompany.MyApp.DataAccess.MyDataAccess
{
  public class CustomerAccess
  {
    public MyDataSet GetCustomer(long CustomerKey)
    {
      // Here's where you'd put all the SqlCommand and DataAdapter stuff
      // and fill your DataSet.
      return dsData;
    }
  }
}

Now, that's the "simple" version, just to get the concept. Let's take it a step further:

We have 4 "layers" ... UI, Web Services, Business, Data Access.

These are more than 4 projects, because each layer is further broken down by module. Let's take the DataAccess layer as an example:

As in all our layers, there are DataAccess parent classes from which all DataAccess classes inherit. These parent classes have all the basic functionality needed for DataAccess and we consider it part of our "framework" ... it has it's own project. See my 3-part DataAccess series for more info about this:

http://geek-goddess-bonnie.blogspot.com/2009/09/dataaccess-part-i.html
http://geek-goddess-bonnie.blogspot.com/2009/10/dataaccess-part-ii.html
http://geek-goddess-bonnie.blogspot.com/2009/10/dataaccess-part-iii.html

Each module in our app has a separate DataAccess project. So, we'll have a DataAccess.Personnel project and a DataAccess.Inspection project, etc. and the classes in those projects inherit from the parent classes in the "framework" project. (As you probably know, these separate projects become separate .DLLs).

The Business layer got a little more complicated, but the architecture of it is the same. We actually have 2 Business layers ... server-side and client-side. The server-side classes remain on the server where they are accessed from the Web Services. The client-side classes are brought down to the client from the server to be used by the UI classes, but they can also be used on the server.

So, that's it in a nutshell. I think it's a good start, to get you thinking about how you should structure your application.

Exception Handling

2010-10-23T17:26:00.001-07:00

Today’s post is about having “global” Exception handling at the Application Level. What I mean by that is handling an exception at the very "top" of an application, in your MainForm, in case exception handling as been missed by the developer in other modules, forms or whatever. Something like this will do it:

[STAThread]
static void Main(string[] args)
{
    // Creates an instance of the methods that will handle the exception.
    CustomExceptionHandler eh = new CustomExceptionHandler();
    // Adds the event handler to to the event.
    Application.ThreadException += new ThreadExceptionEventHandler(eh.OnThreadException);
    Application.SetUnhandledExceptionMode(UnhandledExceptionMode.CatchException);
    
    Application.Run(new MainForm());
}
// Creates a class to handle the exception event.
internal class CustomExceptionHandler
{
    // Handles the exception event.
    public void OnThreadException(object sender, ThreadExceptionEventArgs t)
    {
        DialogResult result = this.ShowThreadExceptionDialog(t.Exception);
        // Exits the program after displaying message to the user. 
        // In Development mode, the developer will have more options (Abort/Retry/Ignore).
        if (result == DialogResult.OK || result == DialogResult.Abort)
            Application.Exit();
    }
    // Creates the error message and displays it.
    private DialogResult ShowThreadExceptionDialog(Exception e)
    {
        DialogResult retval;
        string msgUser  = "An error occurred please contact the adminstrator with the following information:\n\n";
        string msgDev   = "An unhandled exception occurred (Abort/Retry/Ignore buttons are only displayed to Developers) \n\n";
        
        string msgTrace = "Error: " + e.Message + "\n\n";
        if (e.InnerException != null)
            msgTrace   += "       " + e.InnerException.Message + "\n\n";
        msgTrace += "Error Method: " + e.TargetSite + "\n\n" +
                    "Stack Trace: "  + e.StackTrace;
        if (System.Diagnostics.Debugger.IsAttached)
            retval = MessageBox.Show(msgDev + msgTrace, "Application Error", MessageBoxButtons.AbortRetryIgnore, MessageBoxIcon.Exclamation);
        else
            retval = MessageBox.Show(msgUser + msgTrace, "Application Error", MessageBoxButtons.OK, MessageBoxIcon.Stop);
        
        return retval;
    }
}

You'll notice that in my example, I only allow the user to Retry or Ignore if they're the developer and debugging. I assume that in such a case, the developer will want to see right off the bat what went wrong and where, but if this happens to a real user, it's typically NOT a good idea to allow them to continue, as it may easily lead to further corruption of data. You can also expand on this class to add error-logging or whatever you wish.

Hook Into Form’s Events From Controls On Form

2010-09-26T15:14:00.001-07:00

Today’s blog post will be another excerpt from my .Net Tips column in the March 2008 Issue of the Universal Thread Magazine.

Following is one methodology one could use to accomplish hooking into Form events from a Control.

Let's use an example to illustrate this ... we'd like our Control to hook into the Form's Load and Closing events. Let's use the ListView as an example. First, we'll sub-class the ListView Control. As you can see we override the OnParentChanged event in the ListView sub-class.

We utilize the TopLevelControl (not the Parent). If the TopLevelControl is null then we hookup event handlers backwards through the control hierarchy as each control is parented. When we finally get to a TopLevelControl for a Form type we hookup the load/close event handlers.

public class MyListView : System.Windows.Forms.ListView
{
   private Form _parentForm = null;
   public MyListView()
   {
   }
   protected override void OnParentChanged(EventArgs e)
   {
      base.OnParentChanged(e);
            
      if (this._parentForm != null)
         return;
      if (this.DesignMode == false)
      {
         if (this.TopLevelControl != null &amp;&amp; this.TopLevelControl is Form)
         {
            this._parentForm = (Form)this.TopLevelControl;
            this.EstablishParentEvents();
         }
         else
         {
            Control LastParent = this;
            while(LastParent != null)
            {
               LastParent.ParentChanged += new EventHandler(LastParent_ParentChanged);
               LastParent = LastParent.Parent;
            }
         }
      }
   }
   private void EstablishParentEvents()
   {
      this._parentForm.Closing += new CancelEventHandler(MyListView_Closing);
      this._parentForm.Load += new EventHandler(MyListView_Load);
   }
   private void MyListView_Closing(object sender, CancelEventArgs e)
   {
      MessageBox.Show("The parent form is closing!");
      if (this._parentForm != null)
      {
         this._parentForm.Closing -= new CancelEventHandler(MyListView_Closing);
         this._parentForm.Load -= new EventHandler(MyListView_Load);
      }
   }
   private void MyListView_Load(object sender, EventArgs e)
   {
      MessageBox.Show("The parent form is loading!");
   }
   private void LastParent_ParentChanged(object sender, EventArgs e)
   {
      Control Source = (Control)sender;
      if (Source.TopLevelControl != null &amp;&amp; Source.TopLevelControl is Form)
      {
         this._parentForm = (Form)Source.TopLevelControl;
         this.EstablishParentEvents();
      }
   }
}

Thanks to Neil Tonkin in Message #1084683 on the Universal Thread.

Interesting Clipboard Functionality

2010-08-29T16:31:00.001-07:00

Last week’s blog post was an excerpt from my .Net Tips column in the April 2008 Issue of the Universal Thread Magazine. Well, that was such a good issue for Tips, that I'm going to post another one from the same issue. I hope no one minds! ;0)

Did you ever need to copy an instantiated class to the clipboard, retreive it and access a property or method on it? Well, here's how to do it:

Clipboard.SetData("MyClassFormat", new MyClass("42"));
if (Clipboard.ContainsData("MyClassFormat"))
{
    MyClass o = Clipboard.GetData("MyClassFormat") as MyClass;
    if (o != null)
    {
        // do stuff here, such as execute a method
        // o.MyMethod();
        // or set a property
        // o.MyProperty = "xyz";
    }
}
else
{
    MessageBox.Show("Ain't nothing there!!");
}

There's only one gotcha and that is that your class must have the [Serializable] attribute.

Thanks to Einar Kvandahl in Message #1135004 on the Universal Thread.

Determine new PK after an Insert

2010-08-22T16:56:00.001-07:00

Today’s blog post will be another excerpt from my .Net Tips column in the April 2008 Issue of the Universal Thread Magazine.

You have a couple of options to get the PK value of a newly inserted record. I am assuming SQL Server database in these examples.

First, if you are not using Stored Procs and simply sending an INSERT INTO statement, just add a "SELECT SCOPE_IDENTITY() at the end of your query and be sure to execute your query by reading the result back (into a datareader or dataset for example).

Your query string should look like this:

string Sql = "INSERT INTO MyTable (ColumnOne) VALUES ('Bob') SELECT SCOPE_IDENTITY() AS MyPK";

And, here's the code that shows how to execute and read the results back using a DataReader:

int MyPK = 0;
SqlCommand sc = new SqlCommand(Sql, new SqlConnection(this.TestConnection));
sc.Connection.Open();
SqlDataReader rd = sc.ExecuteReader(CommandBehavior.CloseConnection);
while (rd.Read())
{
  MyPK = Convert.ToInt32(rd["MyPK"]);
}

Alternatively, if you're using a Stored Proc, you'd use an OUTPUT parameter in your Stored Proc, and a ParameterDirection.InputOutput in your code:

-- The Stored Proc
CREATE PROCEDURE MySP 
          @PK        int     = NULL OUTPUT,
          @ColumnOne char(8) = NULL,
          @ColumnTwo char(4) = NULL
AS
  INSERT MyTable (ColumnOne, ColumnTwo)
         SELECT @ColumnOne, @ColumnTwo
  SELECT @PK = SCOPE_IDENTITY()

And here's how you would call it in your code:

Command.CommandText = "MySP";
Command.Parameters.Add("@PK", 0);
Command.Parameters["@PK"].Direction = ParameterDirection.InputOutput;
Command.Parameters.Add("@ColumnOne", OneValue);
Command.Parameters.Add("@ColumnTWo", TwoValue);
Command.ExecuteNonQuery();
MyNewPK = Command.Parameters["@PK"].Value;

Thanks to Éric Moreau (and another post by me) in Messages #1090511 and #930339 on the Universal Thread.

Security Issues with EventSources in EventLogs

2010-07-31T18:04:00.001-07:00

If you rely on the System.Diagnostics.EventLog class to log your server-side errors (such as those that may occur in a web service), you may run into security issues if you try to use an EventSource that doesn’t already exist. Since this is server-side, the easiest thing to do is to use a Web Service web method, which can just be accessed through IE.It only needs to be done once. Here's the web method:

The line containing "Events.EventSources" is simply an Enum containing the names of EventSources you might use in your app. If you only have one, then you can just get rid of that whole foreach loop and just hardcode the name of your source in the if.

[WebMethod(Description = "Set server Event Log sources.")]
public string SetEventLogSources(string Username, string Password, string Domain)
{
    //This will keep track of the impersonation token
    const int LOGON_TYPE_INTERACTIVE = 2;
    const int LOGON_TYPE_PROVIDER_DEFAULT = 0;
    string logName = "Application";
    IntPtr userToken = IntPtr.Zero;
    if (LogonUser(Username, Domain, Password, LOGON_TYPE_INTERACTIVE, LOGON_TYPE_PROVIDER_DEFAULT, ref userToken))
        {
        //Initialize user token 
        WindowsIdentity oIdentity = new WindowsIdentity(userToken);
        WindowsImpersonationContext oContext = oIdentity.Impersonate();
        foreach (string source in Enum.GetNames(typeof(Events.EventSources)))
        {
            if (EventLog.SourceExists(source) == false)
                EventLog.CreateEventSource(source, logName);
        }
        //Undo impersonation
        oContext.Undo();
        return "Event source registration successful!";
    }
    else
    {
        return "Unable to process user credentials for event source registration.";
    }
}
// Using this api to get an accessToken of specific Windows User by its user name and password    
[DllImport("advapi32.dll", CharSet = CharSet.Unicode, SetLastError = true)]
static public extern bool LogonUser(string userName, string domain, string passWord, int logonType, int logonProvider, ref IntPtr accessToken);

Be sure you have included the following “using”s:

using System.Diagnostics;
using System.Security.Principal;
using System.Runtime.InteropServices;

Program To The Interface

2010-06-05T18:08:00.001-07:00

One of the biggest benefits of using Interfaces (in my opinion) is to be able to "program to the Interface", as they say. Let me explain with a real-world example:

Suppose I have created this Interface:

public interface IFillFromFinder
{
    void FillListView(DataSet dsList);
    void ClearListView();
}

Now, suppose I have a class that contains a ListView and implements the Interface:

public class MySearchClass : MyUserControl, IFillFromFinder
{
    #region Declarations
    protected ListView     oListView;
    protected MyFinderForm oFinder;
    #endregion
    #region Methods
    public void Search(ListView listView)
    {
        this.oListView = listview;
        this.ClearListView();
        this.oFinder = new MyFinderForm(this);
        oFinder.ShowDialog();
    }
    // Interface methods to implement 
    public void ClearListView()
    {
        // code here for clearing listview
    }
    public void FillListView(DataSet dsList)
    {
        // code here for filling listview
    }
    #endregion
}

As you can see, this class calls a Finder dialog Form, that gathers information from the User and performs a query against the backend data. If the Finder Dialog has been called from a control that implements this Interface (as in the above call from the Search() method), then it can also fill that control's ListView object by calling the methods on the Interface.

Here's the relevant part of my Finder Dialog. Note that the oListControl is defined using the Interface. MyFinderForm needs to know absolutely nothing else about what's calling it, other than that it implements the IFillFromFinder interface.

public class MyFinderForm : MyDialogForm    
{
    #region Declarations
    public MyListDataSet oData = new MyListDataSet();
    protected IFillFromFinder oListControl = null;
    #endregion
    #region Constructors
    public MyFinderForm(IFillFromFinder CallingControl)
    {            
        this.oListControl = CallingControl;            
        InitializeComponent();
    }        
    public MyFinderForm()        
    {            
        InitializeComponent();
    }
    #endregion
        
    #region Events
    private void cmdOK_Click(object sender, System.EventArgs e)
    {
        this.FillDataSet();
        // If this dialog form was called by a class that implemented IFillFromFinder, 
        // then call call the Interface method to fill the ListView.
        if (this.oListControl != null)
        {
            if (this.chkReplace.Checked == true)
                this.oListControl.ClearListView();
            this.oListControl.FillListView(this.oData);
        }
    }

    #endregion
}

Here's another good example. Suppose I need to perform some kind of action, or access a property, on a Control on my Form, but not all Controls on my Form have the appropriate method or property. Interfaces come in handy for this, as these two examples illustrate:

foreach (IMyControl control in this.Controls)
{
    control.MyMethod();
    control.MyProperty = "whatever";
}

-or-

if (SomeControl is IMyControl)
{
    ((IMyControl)SomeControl).MyMethod();
    ((IMyControl)SomeControl).MyProperty = "whatever";
}

I hope this sort of helps explain interfaces by using a real world example.

Inheritance and Constructors

2010-05-18T13:12:00.001-07:00

Here's a common scenario: say you have a base class that does some work that relies on the setting of one of its properties. In the example I'll show, I have an abstract class designed to fill a DataSet from SQL, based on the TableName member of the class.
Each sub-class will set TableName, because each sub-class will be filling it's own table.

Our first attempt at creating these two classes looks like this:

public class BizObj
{
   protected string TableName="default";
   private DataSet oData;
   public BizObj()
   {
      this.LoadData();  
   }   
   public void LoadData()
   {
      SqlConnection oConn = new SqlConnection("...");
      SqlDataAdapter oAdapter = new SqlDataAdapter("SELECT * FROM " + this.TableName, oConn);
      oAdapter.Fill(this.oData);
   }
}
public class AuthorBizObj: BizObj
{
   public AuthorBizObj()
   {
      this.TableName="Authors";
   }
}

This does not work. Why? Because when instantiating any class, its base class constructor always fires first. My example used an abstract base class, but the same applies to a non-abstract class. So, in this example, the BizObj (base class) constructor fires before the AuthorBizObj (sub-class) constructor. By then, it's too late to set the TableName (which has already been set in the BizObj base class).

The trick to fixing this is to use a virtual Property, rather than a member, and to override that Property in each sub-class:

public class BizObj
{
   protected virtual string TableName { get; set; }
   private DataSet oData;
   public BizObj()
   {
      this.LoadData();
   }
   public void LoadData()
   {
      SqlConnection oConn = new SqlConnection("...");
      SqlDataAdapter oAdapter = new SqlDataAdapter("SELECT * FROM " + this.TableName, oConn);
      oAdapter.Fill(this.oData);
   }
}
public class AuthorBizObj: BizObj
{
   protected override string TableName
   {
      get {return "Authors;}
   }
    public AuthorBizObj()
    {
        // whether or not you have this constructor
        // does not affect the how the constructor fires
        // I left a non-functional constructor in this example
        // simply for illustrative purposes. You can leave it
        // out if you have nothing to code in it.
    }
}

So, there are two important points to take away from the above illustration:

1) When a class has been sub-classed, the base class constructor always runs before the sub-class constructor.

2) The member variables are instanced in just the opposite order: the base class member variables are set after the sub-class member variables (the above examples don’t quite illustrate this point). Keep in mind, I’m talking about member variables (which cannot be virtual and so cannot be overridden in a sub-class). My examples above used virtual properties (with getters/setters), which aren’t set until they’re accessed.

Create An XSD

2010-04-18T09:14:00.001-07:00

In a post I wrote back in September about why I dislike TableAdapters (see TableAdapters Are Crap), I mentioned that the TableAdapter wizard puts a lot of excess stuff in your .xsd that doesn’t belong there. So, how do you avoid that? Well, first you’ve got to have already created an .xsd. I’ll show an easy little utility you can write yourself to use to do this.

Secondly, you’ve got to avoid using the DataSet Designer in such a way that it puts all that excess stuff into your .xsd. How do you do that? That’s pretty easy actually … as long as you don't open an .xsd with the DataSet Designer, but open it with the XML Editor instead, you won't have to worry about getting all the extra stuff for support of TableAdapters generated in your .xsd (it's not the opening, but the saving of changes that generates the code).

So, use the code I’ll show you in a moment to create an .xsd. Then, simply add that .xsd to your DataSet project, right-click on the .xsd and choose "Run Custom Tool". This is what will generate the Typed DataSet for you. If that option doesn't show up in your context menu, choose Properties instead and type "MSDataSetGenerator" in the Custom Tool property. After that, any time you make a change to the .xsd in the XML Editor and save the change, the Typed DataSet gets regenerated.

Now, on to the code … this example is fairly simple but quite usable. You can make it more robust if you want to (for example, add code to find other SQL Servers rather than use only a default) . Create a Form, put 3 textboxes and a button on it. Don’t forget that you need to add a “using System.Data.SqlClient;” in order to use the SQL stuff.

public partial class FormCreateXsd : Form
{
    private string TestConnection = "server=(local);uid=sa;pwd=MyPassword";
    public FormCreateXsd()
    {
        InitializeComponent();
    }
    private void CreateDataSet()
    {
        // set up the connection with the database name
        string connectionString = this.TestConnection + ";database=" + this.txtDatabaseName.Text;
        try
        {
            // set up the Sql Command and DataAdapter with the Stored Proc name
            SqlCommand sc = new SqlCommand(this.txtStoredProcName.Text, new SqlConnection(connectionString));
            sc.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter da = new SqlDataAdapter(sc);
            // set up the DataSet with the DataSet name
            DataSet ds = new DataSet();
            string[] parts = this.txtDataSetName.Text.Split('.');
            ds.DataSetName = parts[0];
            // and fill the DataSet
            da.Fill(ds);
            // set the filename and write out the schema
            string filename = this.txtDataSetName.Text;
            if (parts[parts.Length - 1].ToLower() != "xsd")
                filename += ".xsd";
            ds.WriteXmlSchema(filename);
            MessageBox.Show(filename + " successfully created");
        }
        catch (Exception ex)
        {
            string msg = ex.Message;
            if (ex.InnerException != null)
                msg += "\r\n" + ex.InnerException.Message;
            MessageBox.Show("The following error occurred: " + ex.Message);
        }
    }
    private void button1_Click(object sender, EventArgs e)
    {
        if (this.txtDatabaseName.Text == "" || this.txtDataSetName.Text == "" || this.txtStoredProcName.Text == "")
            return;
        else
            this.CreateDataSet();
    }
}

That’s it. Happy coding!

Custom Events

2010-03-28T14:32:00.001-07:00

UPDATE: I've updated my examples below, and added to some of the text, to take into account tergiver's suggestion, in his comment below, that I should probably use the generic EventHandler delegate (introduced in .NET 2.0) rather than defining custom delegates (which dates back to .NET 1.x). In fact, I've totally taken out the custom delegate code, since I doubt many people are still using .NET 1.1 anymore. If anyone reading this who is not using .NET 2.0 and higher would like to know about the custom delegate declarations and usage, let me know in a comment.

I tend to write introductory topics in my blog. Not always, but typically. And not because I’m a newcomer to .NET (I’ve been using .NET since the beginning of 2002), but because all the complicated topics seem to be covered by everyone else and I think there is still a need to address simpler topics.

Today’s topic is no different. Even though writing custom events isn’t all that complicated, I still see a lot of questions on the Forums asking how to do it. So, let’s get to it.

Say you have a custom UserControl that you need to have raise an event when, for example, a user types "FOO" in a textbox that is on the Control.

Minimally, in your UserControl, you need the following things:

// First you must specify the event that you will be raising:

public event EventHandler MyFooBar;
// Then, when you need to fire the event in your UserControl, do this:
if (this.MyTextBox.Text == "FOO")
    this.OnMyFooBar(new EventArgs());
// Lastly, this raises the MyFooBar event:
protected virtual void OnMyFooBar(EventArgs e)
{
    if (MyFooBar != null)
        MyFooBar(this, e);
}

Then, in your form, you just set up the usual delegates and EventHandlers:

this.oMyControl.MyFooBar += new System.EventHandler(this.oMyControl_MyFooBarHandler);
private void oMyControl_MyFooBarHandler(object sender, System.EventArgs e)
{
    // whatever your form code needs to be, such as:
    MessageBox.Show("FOO was specified!");
}

Or, the alternative way to do this since anonymous methods were introduced in 2.0:

this.oMyControl.MyFooBar += delegate
{
    // whatever your code needs to be, such as
    MessageBox.Show("FOO was specified!");
};

You can even get more fancy, creating custom EventArgs and utilizing generic delegates, but the above code is sufficient for simple things, when just the built-in System.EventArgs is all you need. For fancier, custom stuff, try this:

First, custom event args something like this:

public class MyCustomEventArgs : EventArgs 
{ 
  public bool IsBarSpecified { get; set; }
}

Your UserControl code then gets changed to this:

// Change the event so that it can be handled by a generic delegate

public event EventHandler<MyCustomEventArgs> MyFooBar;
// Firing the event gets changed to something like this:
if (this.MyTextBox.Text.ToUpper().Contains("FOO"))
{
    MyCustomEventArgs e = new MyCustomEventArgs();
    if (this.MyTextBox.Text.ToUpper().Contains("BAR"))
        e.IsBarSpecified = true;
    else
        e.IsBarSpecified = false;
    this.OnMyFooBar(e);
}
//And raising the MyFooBar event is the same, other than changing to the custom EventArgs:
protected virtual void OnMyFooBar(MyCustomEventArgs e)
{
    if MyFooBar != null)
        MyFooBar(this, e);
}

And in your Form, you'd have this instead:

this.oMyControl.MyFooBar += new EventHandler<MyCustomEventArgs>(this.oMyControl_MyFooBarHandler);
private void oMyControl_MyFooBarHandler(object sender, MyCustomEventArgs e)
{
    // whatever your code needs to be, such as
    string message = "FOO was specified!"; 
    if (e.IsBarSpecified == true)
        message += " BAR too!");
    MessageBox.Show(message);
}

Or this, if you want to use an anonymous delegate (note that since I'm utilizing the custom MyCustomEventArgs, I'll need to specify them here, whereas I didn't need any of the parameters in the first example):

this.oMyControl.MyFooBar += delegate(object sender, MyCustomEventArgs e)
{
    // whatever your code needs to be, such as
    string message = "FOO was specified!"; 
    if (e.IsBarSpecified == true)
        message += " BAR too!");
    MessageBox.Show(message);
};

Uncommitted Child Table Changes

2010-03-07T14:30:00.001-08:00

A few months ago, there was a question on the MSDN forums that reminded me of a similar question quite some time ago that I had answered. The issue crops up with parent/child DataTables using Relations. The symptoms of the problem are that your Child table is often left with uncommitted changes, even if you thought you properly did an .EndEdit() on that Child table. This blog post will explain why that happens and what to do about it.

OK, so let’s get some sample stuff set up first.

// First, let's set the BindingSource using MyRelation
this.bsParent = new BindingSource();
this.bsChild = new BindingSource();
this.bsParent.DataSource = this.MyDataSet;
this.bsParent.DataMember = "MyTable";
this.bsChild.DataSource = this.bsParent;
this.bsChild.DataMember = "MyRelation";
// now bind a grid and a textbox
this.oGrid.DataSource = this.bsParent;
this.txtLastName.DataBindings.Add("Text", this.bsChild, "description");

If you make a change in the TextBox (bound to the Child table), and move to other rows in the Grid (the Parent table), maybe even making other changes in the TextBox for each Grid row, then click on a Save button, when you do this.MyDataSet.GetChanges(), you will be missing some of those changes from your Child table. Here’s why:

1) Data in a DataRow has several different versions, one of which is a “Proposed” version and your changes are still stuck in this Proposed state and the .GetChanges() method only gets those with the “Current” version. See my earlier blog entry for a more in-depth explanation of DataRow versions and one way of handling this issue: http://geek-goddess-bonnie.blogspot.com/2009/09/fun-with-datasets.html

2) Because you’ve made changes to child records that are related to different parent records, then the bsChild.EndEdit() only commits the Proposed changes for the current relation, even though you've changed other rows with different parents.

At first, I thought a good solution to this was to create a third BindingSource associated with the entire Child table, having nothing whatsoever to do with the Relationship. That way, when you Save, you could call bsChildTable.EndEdit() rather than bsChild.EndEdit(). Sounds good in theory, but unfortunately, it did *NOT* work.

So, are we stuck using the CommitProposedChanges() method I created in the above-mentioned earlier blog post? (You *did* read that post I hope). Well, it will still work just fine. But, because we are using relationships and BindingSources based on those relationships, we can speed it up a bit as follows:

// defining ParentTable simply for clarity of the example
DataTable ParentTable = this.MyDataSet.Tables["MyTable"];
for (int i = 0; i < ParentTable.Rows.Count; i++)
{
    this.bsParent.Position = i;
    this.bsParent.EndEdit();
    this.bsChild.EndEdit();
}

So, all we’re doing is spinning through the Parent table, moving the “record pointer” (setting the .Position property) to each parent row. This then “re-sets” the current relationship with each iteration through the Parent table’s rows so that the bsChild.EndEdit() applies to each successive relation.

CheckBox Class Bound to Non-Booleans

2010-02-28T21:40:00.001-08:00

Well, it’s been awhile since I’ve posted. I have no excuse, other than I’ve been super-busy, but aren’t we all? So … lousy excuse. One of my kids, who calls frequently, always starts out the conversation with “So, what are you doing?” and I always reply “Working”. That invariably gets a response from him of “How can an un-employed person always be working?!?” Well, just because I’m not getting paid, doesn’t mean that I’m not busy working on the next latest-and-greatest idea to save humanity … or at least make life easier. ;0)

Anyway, that said, I’m going to post a pretty quick-and-dirty class here, but it addresses an issue I see frequently asked on the forums. As we all know, a CheckBox is typically bound to a boolean value. I mean, it’s either checked or it’s not … true or false. But, sometimes developers have to deal with legacy data from legacy databases, or maybe just poorly designed databases, where something other than a boolean (or bit in many databases) is used to represent true/false … character strings such as “T”/”F” or “Y”/”N”.

The key point to making this work, is to handle the Format and Parse events of the Binding. This class can be extended to be able to used with other strings, such as “T”/”F”, but I’ll leave that as an exercise for the reader:

public class BBCheckBoxString : System.Windows.Forms.CheckBox
{
    protected Binding oBinding = null;

    public virtual void DataBind(System.Data.DataTable Data, string Column)
    {
        this.Checked = false;
        this.oBinding = new Binding("Checked", Data, Column);

        this.oBinding.Format += new ConvertEventHandler(this.FormatHandler);
        this.oBinding.Parse += new ConvertEventHandler(this.ParseHandler);

        this.DataBindings.Add(this.oBinding);
    }
    protected virtual void FormatHandler(object sender, ConvertEventArgs e)
    {
        if (e.Value.ToString() == "Y")
            e.Value = true;
        else
            e.Value = false;
    }
    protected virtual void ParseHandler(object sender, ConvertEventArgs e)
    {
        if ((bool)e.Value == true)
            e.Value = "Y";
        else
            e.Value = "N";
    }
}

Pretty easy, right? The Format event takes the value of the column in your DataTable (“Y”/”N”) and converts it to true/false to be displayed as a checkmark (or empty box). The Parse handler does the opposite. It takes the value of the Checked property (true/false) and converts it to “Y”/”N” to be placed back in the data column.

Getting Non-Null Data Redux

2009-12-30T09:15:00.001-08:00

It has been pointed out to me that my previous post about this topic is a bit out-dated. Yes, it is and I did mention in that post that the code I posted was from an old 1.1 class that I never bothered to update when things like extension methods came along in subsequent .NET versions.

Well, I guess I’ve been chastised enough for it, so I’ll post some new code using extension methods. But first, I do want to say something about extension methods. They are cool, they make some things much easier (as I’ll illustrate at the end of this post) but they can also be over-used and therefore abused (IMHO). When over-using extension methods, it may be very easy to forget that these new methods are not native to .NET, that you (or another developer on your team) has created them. But, I suppose that will be brought home to you when you work on other apps that don’t have the extension method code in them … it just may take you awhile to remember, “Oh yeah, Intellisense isn’t showing this method because it isn’t there natively. Darn!”

One other note about the previously posted code (which I will correct in that post), is that I used code like this:

if (Test != DBNull.Value && Test != null)

When, of course, it should have been the other way around, silly me:

if (Test != null && Test != DBNull.Value)

So, without further ado, here’s the revised version using extension method (and really, all that has to be done is to add “this” to each method signature and I also changed the name of the class):

public static class BBExtensions
{
    public static object GetNonNull(this object Test, object Default)
    {
        if (Test != null && Test != DBNull.Value)
            return Test;        
        else
            return Default;    
    }    
    public static string GetNonNull(this object Test, string Default)    
    {        
        if (Test != null && Test != DBNull.Value)         
        {            
            if(Test is DateTime)             
            {                
                DateTime TestDT = Convert.ToDateTime(Test);                
                DateTime SqlNull = new DateTime(1900, 1, 1);
                if(TestDT == SqlNull)                    
                    return Default;            
            }            
            else if (Test is bool)            
            {                
                bool YesNo = Convert.ToBoolean(Test);                
                if (YesNo)                    
                    return "Yes";                
                else                    
                    return "No";            
            }                                
            return Test.ToString().Trim();        
        }        
        else            
            return Default;    
    }    
    public static int GetNonNull(this object Test, int Default)    
    {        
        if (Test != null && Test != DBNull.Value)            
            return Convert.ToInt32(Test);        
        else            
            return Default;    
    }    
    public static DateTime GetNonNull(this object Test, DateTime Default)    
    {                
        if (Test != null && Test != DBNull.Value)        
        {            
            DateTime TestDT = Convert.ToDateTime(Test);            
            DateTime SqlNull = new DateTime(1900, 1, 1);            
            DateTime NetNull = new DateTime(1, 1, 1);                        
            if(TestDT != SqlNull && TestDT != NetNull)                
                return TestDT;            
            else                
                return Default;        
        }        
        else            
            return Default;    
    }
    public static string GetNonNullDate(this object Test, string Default)
    {    
        if (Test != null && Test != DBNull.Value)     
        {        
            if(Test is DateTime)         
            {            
                DateTime TestDT = Convert.ToDateTime(Test);            
                DateTime SqlNull = new DateTime(1900, 1, 1);                    
                if(TestDT != SqlNull)                
                    return TestDT.ToShortDateString();        
            }                        
            return Default;    
        }    
        else        
            return Default;
    }
    public static DateTime GetNonNullDate(this object Test)
    {    
        if (Test != null && Test != DBNull.Value && Test is DateTime)         
            return Convert.ToDateTime(Test);    
        else        
            return new DateTime(1900, 1, 1);
    }
}

So, now why is this better than the old CommonFunctions class I had? Well, here’s how you had to use the old class:

int MyInt = CommonFunctions.GetNonNull(MyDataSet.Tables[0].Rows[0]["MyColumn"], 0);
// -or-
DateTime MyDatetime = CommonFunctions.GetNonNullDate(MyDataSet.Tables[0].Rows[0]["MyDateColumn"])

And here’s the extension method way of doing this, a bit cleaner:

int MyInt = MyDataSet.Tables[0].Rows[0]["MyColumn"].GetNonNull(0);
// -or-
DateTime MyDatetime = MyDataSet.Tables[0].Rows[0]["MyDateColumn"].GetNonNullDate();

DefaultValue for Properties

2009-12-20T15:53:00.001-08:00

When in the Designer for a Form, everyone knows that you can go to the Properties window and change a property on the form or a control. You most likely also know that you can then right-click on any changed property, and choose "Reset" to set it back to its Default Value.

What code do you need in your own controls to get that to work? You need code in two places: in the Constructor to set the value to begin with, and a [DefaultValue] attribute for the Property itself:

public class MyTextBox : TextBox
{
    private string m_MyProperty;
    public MyTextBox
    {
        this.m_MyProperty = "";
        
        // As an additional bonus, I'm showing you two ways to do color
        this.BackColor = System.Drawing.Color.FromArgb(90, 100, 240);
        this.ForeColor = System.Drawing.Color.Firebrick; ;
    }
    [DefaultValue("")]
    public string MyProperty
    {
        get {return this.m_MyProperty;}
        set {this.m_MyProperty = value;}
    }
    [DefaultValue(typeof(System.Drawing.Color), "90,100,240")]
    public override System.Drawing.Color BackColor
    {
        get
        {
            return base.BackColor;
        }
        set
        {
            base.BackColor = value;
        }
    }
    [DefaultValue(typeof(System.Drawing.Color), "Firebrick")]
    public override System.Drawing.Color ForeColor
    {
        get
        {
            return base.ForeColor;
        }
        set
        {
            base.ForeColor = value;
        }
    }
}

Some properties aren't virtual and can't be overridden. For those properties, you can use "new" instead of "override".

The [DefaultValue] attribute serves two purposes:

It allows the property to be easily Reset to it's Default Value from the Property Sheet.
It prevents the code that sets the default value from actually showing up in the InitializeComponent() method.

That second point is pretty important. Let me illustrate why. Say that you've not used the [DefaultValue] attribute in your TextBox, but simply set the value of the BackColor and ForeColor properties in your MyTextBox constructor, like this:

public class MyTextBox : TextBox
{
    public MyTextBox()
    {
        this.BackColor = Color.DarkGreen;
        this.ForeColor = Color.Firebrick;
    }
}

When you drop this TextBox onto a Form, the BackColor and ForeColor will be explicitly set in the code generated in the InitializeComponent() method of the Form (IOW, you’ll have the code to set BackColor and ForeColor for every TextBox you drop on your design surface). Consequently, if you later decide to change the color in your MyTextBox class, you have to revisit every single Form that you ever dropped a MyTextBox on, to change it to the new default value. Not good!

Conversely, let's look at the opposite scenario where you do include a property with a [DefaultValue] attribute, but you don't initialize that in the constructor. So, your class looks like this:

public class MyTextBox : TextBox
{
    // bad code! Do not try this at home! ;0)
    public MyTextBox()
    {
    }
    
    [DefaultValue(typeof(System.Drawing.Color), "DarkGreen")]
    public override System.Drawing.Color BackColor
    {
        get {return base.BackColor;}
        set {base.BackColor = value;}
    }
    [DefaultValue(typeof(System.Drawing.Color), "Firebrick")]
    public override System.Drawing.Color ForeColor
    {
        get {return base.ForeColor;}
        set {base.ForeColor = value;}
    }
}

When you drop MyTextBox on a Form now, it will default to a color of SystemColors.Control for the BackColor property and SystemColors.WindowText for the ForeColor property, and the IDE will generate the code in the InitializeComponent() method of the Form where you dropped MyTextBox onto, unless you go to the Property Sheet, right-click the BackColor and choose "Reset" (and likewise for ForeColor). Once you do this, the correct color appears for the TextBox in the Designer, the IDE removes the setting of the BackColor and ForeColor properties in the InitializeComponent() method and all looks fine ... until you decide to change the default to something else in your MyTextBox class ... since your TextBox doesn't initialize its BackColor and ForeColor properties in its constructor, the Form goes back to displaying SystemColors.Control and SystemColors.WindowText (even though it's not hard-coded in the InitializeComponent() method). Again, this is not good!

Suffice it to say that you simply must do both -- initialize it in the constructor and specify the [DefaultValue] attribute. Just something that you're going to have to remember to do!!

Hooking Into the Form's Events, from Form Controls

2009-11-30T16:57:00.001-08:00

Have you ever had the need to have a Control on your Form hook into some of the Form’s events? For example, say you have a Control on your Form that needs to do something extra when the Form Loads or the Closing event is triggered.

Let's use an example to illustrate how we might accomplish this. Let's use the ListView as an example. First, we'll sub-class the ListView Control. As you can see, we override the OnParentChanged event in the ListView sub-class.

We’ll utilize the TopLevelControl (not the Parent). If the TopLevelControl is null then we hookup event handlers backwards through the control hierarchy as each control is parented. When we finally get to a TopLevelControl for a Form type we hookup the load/closing event handlers.

public class MyListView : System.Windows.Forms.ListView
{
   private Form FormParent = null;
   public MyListView()
   {
   }
   protected override void OnParentChanged(EventArgs e)
   {
      base.OnParentChanged(e);
            
      if (this.FormParent != null)
         return;
      if (this.DesignMode == false)
      {
         if (this.TopLevelControl != null && this.TopLevelControl is Form)
         {
            this.FormParent = (Form)this.TopLevelControl;
            this.EstablishParentEvents();
         }
         else
         {
            Control LastParent = this;
            while(LastParent != null)
            {
               LastParent.ParentChanged += new EventHandler(LastParent_ParentChanged);
               LastParent = LastParent.Parent;
            }
         }
      }
   }
   private void EstablishParentEvents()
   {
      this.FormParent.Closing += new CancelEventHandler(MyListView_Closing);
      this.FormParent.Load += new EventHandler(MyListView_Load);
   }
   private void MyListView_Closing(object sender, CancelEventArgs e)
   {
      // put your extra code here
      if (this.FormParent != null)
      {
         this.FormParent.Closing -= new CancelEventHandler(MyListView_Closing);
         this.FormParent.Load -= new EventHandler(MyListView_Load);
      }
   }
   private void MyListView_Load(object sender, EventArgs e)
   {
      // put your extra code here
   }
   private void LastParent_ParentChanged(object sender, EventArgs e)
   {
      Control Source = (Control)sender;
      if (Source.TopLevelControl != null && Source.TopLevelControl is Form)
      {
         this.FormParent = (Form)Source.TopLevelControl;
         this.EstablishParentEvents();
      }
   }
}

Thanks to Neil Tonkin in Message #1084683 from the Universal Thread

Reflection Class Redux

2009-11-20T21:51:00.001-08:00

I’m sorry to say that there was a bug of sorts in the MyReflectionClass posted in my blog entry back in September (Reflection In .NET). It was in the LoadAssembly() method and it’s been corrected in the original post.

One of the comments in that original post (the comment has been modified now) was that you might need to include the full path of the Assembly as part of the Assembly name. Well, we really do have to take into account a path to the Assembly, but not as part of its name, which was the implication of my original comment.

Sorry for any confusion or pulling of hair that this has caused. =0(

Dynamic Menu Items

2009-11-19T16:34:00.001-08:00

There was a question recently on the Universal Thread about dynamically adding MenuItems to a Form. The data for these dynamically added Items can be obtained from anywhere, like from your database. This fits in quite nicely with the first substantial post I wrote in this blog back in September, Reflection in .NET, because you can use these dynamically added Items to launch new DLLs that you might create (for example, a custom form for one of your customers), via Reflection, without having to recompile and re-distribute your entire application. Just supply the newly-created DLL. At my last company, we used DevExpress SideBars and incorporated this dynamic functionality that way.

So, without further ado, here’s the first thing: the code you need to dynamically add the menu items. Note that this creates different sub-classes of MenuItems, depending on your data:

private void TestMenuAddItemsDynamically()
{
    MenuItem item;  // This is so you can use MenuItem sub-classes
    foreach (DataRow row in this.oData.Rows)
    {
        if (string.IsNullOrEmpty(row["assembly"].ToString()) == false)
        {
            item = new LaunchMenuItem(row["description"].ToString(), row["assembly"].ToString(), row["class"].ToString());
            this.AddMenuItem("Custom Forms", item);
        }
        else
        if (string.IsNullOrEmpty(row["url"].ToString()) == false)
        {
            item = new FavoritesMenuItem(row["description"].ToString(), row["url"].ToString());
            this.AddMenuItem("Favorites", item);
        }
        else
        {
            item = new DefaultMenuItem(row["description"].ToString());
            this.AddMenuItem("Dynamic Menu", item);
        }
    }
}

Next, I’ll show you the LaunchMenuItem class, since it is what this post is focusing on.

// Used to launch a DLL by Reflection
public class LaunchMenuItem : MenuItem
{
    protected string AssemblyName;
    protected string ClassName;
    public LaunchMenuItem(string text, string assemblyName, string className)
        : base(text)
    {
        this.Click += new System.EventHandler(this.ClickHandler);
        this.AssemblyName = assemblyName;
        this.ClassName = className;
    }
    protected virtual void ClickHandler(object sender, System.EventArgs e)
    {
        MyReflectionClass oReflect = new MyReflectionClass(this.AssemblyName, this.ClassName);
        // In it's simplest usage, I will assume that the class I am instantiating is a Form
        // Expanding on this concept is left as an exercise for the reader.
        // I've also left out error checking for the instantiated object
        string message = "";
        Form oForm = oReflect.InstantiateClass(ref message) as Form;
        if (oForm == null)
            MessageBox.Show(message);
        else
            oForm.Show();
    }
}

Note that the above class uses the MyReflectionClass from my previous post on the subject, Reflection In .NET.

The other custom MenuItem classes would contain other things to do in their Click event handlers, such as browsing to a URL link inside a BrowserControl on your Form (for the FavoritesMenuItem class). I’m not including that code in this example … I leave it as an exercise for the reader.

There’s one more method to show, and that’s a common method that will add any MenuItem to the Form’s Menu. Notice that it does things like check to see if the top menu item already exists (“Custom Forms”, “Favorites” and “Dynamic Menu” in the above example). It adds it if it doesn’t, and then adds the new MenuItems under it.

// Generic method for adding to any Menu
private void AddMenuItem(string name, MenuItem item)
{
    string lookForName = name.Replace("&", "");
    // Find top-level menu item
    MenuItem addTo = null;
    for (int i = 0; i < this.Menu.MenuItems.Count; i++)
    {
        MenuItem topLevel = this.Menu.MenuItems[i];
        if (string.Compare(topLevel.Text.Replace("&", ""), lookForName, true) == 0)
        {
            addTo = topLevel;
            break;
        }
    }
    // Was a top-level menu item found?
    if (addTo == null)
    {
        addTo = new MenuItem(name);
        this.Menu.MenuItems.Add(addTo);
        addTo.Index = this.Menu.MenuItems.Count - 2;
    }
    addTo.MenuItems.Add(item);
}

That about wraps it up. I hope this has given my readers lots of ideas to go forth and try out some cool dynamic stuff.

Setting Focus to a Control at Form Load

2009-11-15T22:00:00.001-08:00

I used to write a “.Net Tips and Tricks” column for an online magazine associated with a forum called the Universal Thread. The magazine has been known by several names, the Universal Thread Magazine, Level Extreme .NET Magazine and simply the UT Mag. Unfortunately, after being in “publication” since 2001, the last issue was in April of this year and it doesn’t look like it will be published anymore.

So, if no one minds, I think it’s time to start including a few of the Tips from my columns over the years. The format of my column was to garner tidbits of wisdom from other people’s posts on the UT, re-work them into a good format for a Tip, and credit the people who wrote the post (sometime it was even from a post that I wrote).

So, let’s start out with this --- how about a simple WinForms tip. Nothing earth-shattering here, but worth mentioning.

Normally, the Focus() method of a control is used to move the focus to that control:

private void MyButton_Click(object sender, System.EventArgs e)
{
    // Move the Focus elsewhere
    this.MySpecialTextBox.Focus();
}

This works at any time, except during Form/Control Load. Then, you simply have to set the ActiveControl:

private void MyForm_Load(object sender, System.EventArgs e)
{
    // Start the Form with the Focus on a certain control
    this.ActiveControl = this.MySpecialTextBox;
}

Thanks to Kevin McNeish in Message #1080245 on the Universal Thread.

Getting Non-Null Data

2009-11-05T15:49:00.001-08:00

UPDATE: See my new post on this topic. (I also corrected code in this current post, where I changed to test for null before testing for DBNull.Value ... it was an oversight on my part. Sorry, hope it didn't cause anyone problems).

I see questions along these lines all the time:

//I'm doing this:int CustID = (int)dsCustomer.Tables["Customer"].Rows[0]["CustID"];
// It throws an exception because CustID is DBNull in the data. 
// How do I handle this?

I have a CommonFunctions class that I use for things such as a GetNonNull( ) method (it's an old class, dating back to the 1.1 days, but it still works fine and so I have never updated it for 2.0, haven't really looked to see if it's even necessary). This will work for any type of object, not just data in a DataSet, but is seems that DataSet access is the commonly asked question.

This is implemented with many overloads, but here's an example of just a few:

public class CommonFunctions
{
    public static object GetNonNull(object Test, object Default)
    {
        if (Test != null && Test != DBNull.Value)
            return Test;
        else
            return Default;
    }
    public static string GetNonNull(object Test, string Default)
    {
        if (Test != null && Test != DBNull.Value) 
        {
            if(Test is DateTime) 
            {
                DateTime TestDT = Convert.ToDateTime(Test);
                DateTime SqlNull = new DateTime(1900, 1, 1);
            
                if(TestDT == SqlNull)
                    return Default;
            }
            else
            if (Test is bool)
            {
                bool YesNo = Convert.ToBoolean(Test);
                if (YesNo)
                    return "Yes";
                else
                    return "No";
            }
                    
            return Test.ToString().Trim();
        }
        else
            return Default;
    }
    public static int GetNonNull(object Test, int Default)
    {
        if (Test != null && Test != DBNull.Value)
            return Convert.ToInt32(Test);
        else
            return Default;
    }
    public static DateTime GetNonNull(object Test, DateTime Default)
    {
        
        if (Test != null && Test != DBNull.Value)
        {
            DateTime TestDT = Convert.ToDateTime(Test);
            DateTime SqlNull = new DateTime(1900, 1, 1);
            DateTime NetNull = new DateTime(1, 1, 1);
            
            if(TestDT != SqlNull && TestDT != NetNull)
                return TestDT;
            else
                return Default;
        }
        else
            return Default;
    }
}

These are just a sample of the overloads I use. I have a few more such as for bool, decimal, long and even a few differently named methods specifically for Dates, such as these next two:

public static string GetNonNullDate(object Test, string Default)
{
    if (Test != null && Test != DBNull.Value) 
    {
        if(Test is DateTime) 
        {
            DateTime TestDT = Convert.ToDateTime(Test);
            DateTime SqlNull = new DateTime(1900, 1, 1);
        
            if(TestDT != SqlNull)
                return TestDT.ToShortDateString();
        }
                
        return Default;
    }
    else
        return Default;
}
public static DateTime GetNonNullDate(object Test)
{
    if (Test != null && Test != DBNull.Value && Test is DateTime) 
        return Convert.ToDateTime(Test);
    else
        return new DateTime(1900, 1, 1);
}

Anyway, you get the point. Note that these are static methods, so no instantiation of the CommonFunctions class is necessary.To use them, you would simply have something like this:

int MyInt = CommonFunctions.GetNonNull(MyDataSet.Tables[0].Rows[0]["MyColumn"], 0);// -or-
DateTime MyDatetime = CommonFunctions.GetNonNullDate(MyDataSet.Tables[0].Rows[0]["MyDateColumn"])

DataAccess - Part III

2009-10-27T12:59:00.001-07:00

If you’ve already read my previous posts about DataAccess (Part I and Part II), you’ll notice that I said nothing about writing this in 3 parts. However, I realized that I left out another, better methodology for writing DataAccess classes. So, I thought I better address that pronto.

This better methodology involves classes using anonymous delegates. These classes can live directly in your DataAccess base class, or you can put them in your Business base class (so that your Business classes can effectively manage multiple “transactions” involving multiple DataAccess classses, rather than having it all be done at the DataAccess layer). To simplify matters, I will show this methodology only in the DataAccess base class and leave it as an exercise to the reader to extend the concepts to the Business layer.

What follows is a very simplified, but workable, version … I’ve left out some of the “nice-to-have” features that don’t affect the basic usability of the class (such as timeouts & transactions). Some of my method names might seem a bit “crude”, but I didn’t want it to resemble actual code in use. You know, copyright and all that. The point is to get the concepts across and let you expand on those concepts on your own.

So, first let’s look at the few key points. Here are the basic additions to the DataAccess class I showed in Part II that make this whole thing work:

public delegate void TheMethod();
private TheMethod m_DoIt;
private TheMethod m_UndoIt;
public TheMethod DoIt
{ 
    set { m_DoIt = value; } 
}
public TheMethod UndoIt
{
    set { m_UndoIt = value; }
}
public virtual bool Start()
{
    bool success = false;
    // Enter the retry loop
    while (!success)
    {
        try
        {
            // Commented out because I haven't included transactions in my example
            //this.BeginTransaction();
            this.m_DoIt();
            // Here is where you'd commit your database transactions
            //this.CommitTransaction();
            success = true;
        }
        catch (Exception e)
        {
            if (this.RetryRequested(e) && this.attempt < this.maxRetries)
            {
                // Try to undo any changes before we retry
                // If we can't, just abort
                if (!this.UndoChanges())
                {
                    this.AddToErrorMessage(e);
                    this.ForceCleanup();
                    break;
                }
                
                this.attempt++;
                // Might want to sleep for random period
                //System.Random rand = new System.Random();
                //System.Threading.Thread.Sleep(rand.Next(MinSleepTime, MaxSleepTime));
            }
            else
            {
                // Max number of retries exceeded or an unrecoverable error 
                // - fail and return error message
                this.AddToErrorMessage(e);
                this.ForceCleanup();
                break;
            }
        }
    }
    return (success);
}
protected bool UndoChanges()
{
    try
    {
        if (this.m_UndoIt != null)
            this.m_UndoIt();
    }
    catch (Exception e)
    {
        this.AddToErrorMessage(e);
        return false;
    }
    return true;
}

I’ll explain how it works in a minute, but first let’s see the code you would use in your sub-classes to utilize the delegates:

public CustomerDataSet GetCustomer(long customerkey)
{
    CustomerDataSet ds = null;
    this.DoIt = delegate()
    {
        ds = new CustomerDataSet();
        this.ClearParameters();
        this.AddParms("CustomerKey", customerkey); 
        this.FillData(ds, "csp_CustomerGet");
    };
    this.Start();
    return ds;
}
public bool SaveCustomer(CustomerDataSet dsChanged, CustomerDataSet dsDeleted)
{
    this.DoIt = delegate()
    {
        // you'd also want to wrap all this in a transaction, I just haven't
        // shown transactions in this example
        this.SaveTable(dsChanged.Customer, "csp_CustomersPut");
        this.SaveTable(dsChanged.Orders, "csp_OrdersPut");
        if (dsDeleted != null)
        {
            this.SaveTable(dsDeleted.Orders, "csp_OrdersDelete");
            this.SaveTable(dsDeleted.Customer, "csp_CustomerDelete");
        }
    };
    this.UndoIt = delegate()
    {
        dsChanged.RejectChanges();
        dsDeleted.RejectChanges();
    };
    return this.Start();
}

Now, I obviously have not shown all the methods in the class (I’ll post the entire simplified version of the class at the end of this post). But, let me explain how this works. Basically, you set up two anonymous delegates, DoIt and UndoIt, then run the Start() method which in turn runs the code defined in the delegates. The beauty of it all is that the Start() method contains the only try/catch stuff you need and therefore handles any and all exceptions. It completely does away with the necessity of writing try/catch blocks in all your DataAccess methods, and having to have the developer remember to check return values and or ErrorMessages from every method call.

In the Start() method, the DoIt delegate code runs first. If there are any exceptions thrown, it checks to see if we want to Retry the operation. If so, first we run the UndoIt delegate code, if any exists, and then run through the loop and try again. Note that the GetCustomer() method doesn’t have any UndoIt code, whereas the SaveCustomer() method does. This is because the dsChanged or the dsDeleted DataSets could possibly be changed in the process of attemping to Save, and thus we want to put them back to their original state before attempting the Save again. This isn’t an issue in the GetCustomer() method.

Now, as I mentioned, this greatly simplifies the original DataAccess class that I previously posted (no more try/catch stuff needed), so I’ll post the entire class as it now stands:

public class BBDataAccess
{
    #region Declarations associated with the new delegate approach
    public delegate void TheMethod();
    private TheMethod m_DoIt;
    private TheMethod m_UndoIt;
    public TheMethod DoIt
    { 
        set { m_DoIt = value; } 
    }
    public TheMethod UndoIt
    {
        set { m_UndoIt = value; }
    }
    // you can make these into properties, or make them protected
    // so the defaults can be changed
    private int maxRetries = 3;
    private int attempt;
    #endregion
    #region Declarations from the original DataAccess class
    private IDbConnection oConnection;
    private IDbCommand oCommand;
    private IDbDataAdapter oAdapter;
    private IDataParameterCollection oParms;
    public string ConnectionString = "";
    public string ErrorMessage = "";
    // Tweak, if necessary, for other database servers (Oracle)
    private string AtSign = "@";
    private static int DeadlockRollbackError = 1205; 
    #endregion
    #region Constructor/Destructor
    public BBDataAccess()
    {
        BBConnection o = new BBConnection();
        this.oConnection = o.GetConnection();
        this.ConnectionString = o.ConnectionString;
        this.oCommand = this.GetIDbCommand();
        this.oAdapter = this.GetIDbDataAdapter(this.oCommand);
        this.oCommand.Connection = this.oConnection;
        this.oCommand.CommandType = CommandType.StoredProcedure;
    }
    #endregion
    // Tweak, if necessary, for other database servers (Oracle)
    private string AtSign = "@";
    private static int DeadlockRollbackError = 1205; 
    #endregion
    #region Constructor/Destructor
    public BBDataAccess()
    {
        BBConnection o = new BBConnection();
        this.oConnection = o.GetConnection();
        this.ConnectionString = o.ConnectionString;
        this.oCommand = this.GetIDbCommand();
        this.oAdapter = this.GetIDbDataAdapter(this.oCommand);
        this.oCommand.Connection = this.oConnection;
        this.oCommand.CommandType = CommandType.StoredProcedure;
    }
    #endregion
    #region Methods associated with the new delegate approach
    public virtual bool Start()
    {
        bool success = false;
        // Enter the retry loop
        while (!success)
        {
            try
            {
                // Start the transaction and execute the methods. Any failures from here
                // to the final commit may result in an exception being thrown and caught which
                // may lead to a retry or an abort depending on the type of error
                // Here is where you would start your database transactions
                // It's commented out because I haven't included it in my example
                //this.BeginTransaction();
                this.m_DoIt();
                // Here is where you'd commit your database transactions
                //this.CommitTransaction();
                success = true;
            }
            catch (Exception e)
            {
                // Typically we'll want a Retry if the database error was the
                // result of a deadlock, but you can Retry for any reason you wish.
                if (this.RetryRequested(e) && this.attempt < this.maxRetries)
                {
                    // Try to undo any changes before we retry
                    // If we can't, just abort
                    if (!this.UndoChanges())
                    {
                        this.AddToErrorMessage(e);
                        this.ForceCleanup();
                        break;
                    }
                    
                    this.attempt++;
                    // Might want to sleep for random period
                    //System.Random rand = new System.Random();
                    //System.Threading.Thread.Sleep(rand.Next(MinSleepTime, MaxSleepTime));
                }
                else
                {
                    // Max number of retries exceeded or an unrecoverable error 
                    // - fail and return error message
                    this.AddToErrorMessage(e);
                    this.ForceCleanup();
                    break;
                }
            }
        }
        return (success);
    }
    protected bool RetryRequested(Exception e)
    {
        bool retryRequested = false;
        if (e is BBRetryException) retryRequested = true;
        while (e.InnerException != null && !retryRequested)
        {
            e = e.InnerException;
            if (e is BBRetryException) retryRequested = true;
        }
        return retryRequested;
    }
    /// <summary>
    /// Try to undo any changes to the operation parameters so that we can retry 
    /// If the undo fails, the operation is aborted instead
    /// </summary>
    /// <returns></returns>
    protected bool UndoChanges()
    {
        try
        {
            if (this.m_UndoIt != null)
                this.m_UndoIt();
        }
        catch (Exception e)
        {
            this.AddToErrorMessage(e);
            return false;
        }
        return true;
    }
    // You can handle error message differently if you wish
    // For this example, I simply set up a public ErrorMessage string
    // that will be an empty string if there were no errors.
    protected void AddToErrorMessage(Exception e)
    {
        this.AddToErrorMessage(e.Message);
        if (e.InnerException != null)
            this.AddToErrorMessage(e.InnerException.Message);
    }
    protected void AddToErrorMessage(string msg)
    {
        this.ErrorMessage += msg + "\r\n";
    }
    /// <summary>
    /// Force abort the transaction and cleanup the database connection without raising exceptions
    /// Any problems are still logged to the error messages list. Used when cleaning up during
    /// exception processing where we already know there may be some problem with the database
    /// connection
    /// </summary>
    protected void ForceCleanup()
    {
        try
        {
            // commented because I haven't included Transactions in my example
            //this.RollbackTransaction();
        }
        catch (Exception e)
        {
            this.AddToErrorMessage(e);
        }
        try
        {
            this.CloseConnection();
        }
        catch (Exception e)
        {
            this.AddToErrorMessage(e);
        }
    }
    #endregion
    #region Get/Use IDb Interface Objects
    protected IDbCommand GetIDbCommand()
    {
        return new SqlCommand();
    }
    protected IDbDataAdapter GetIDbDataAdapter(IDbCommand command)
    {
        return new SqlDataAdapter((SqlCommand)command);
    }
    protected IDbDataParameter GetIDbDataParameter(string ParmName, object ParmValue)
    {
        return new SqlParameter(ParmName, ParmValue);
    }
    protected void DeriveParameters(IDbCommand command)
    {
        SqlCommandBuilder.DeriveParameters((SqlCommand)command);
    }
    #endregion
    #region Parameter Methods
    
    // Then there are the various protected methods for adding and setting Parameters, filling a DataSet, etc.    
    // It's these various methods that get used in the classes that are sub-classed from this "base" class.    
    // Here's just a few of them:    
    protected void ClearParameters()
    {
        this.oCommand.Parameters.Clear();
        this.ErrorMessage = "";
    }
    protected void AddParms(string ParmName, object ParmValue)
    {
        if (ParmName.StartsWith(this.AtSign) == false)
            ParmName = this.AtSign + ParmName;
        if (ParmValue != DBNull.Value)
            this.oCommand.Parameters.Add(this.GetIDbDataParameter(ParmName, ParmValue));
    }
    protected void AddParms(string ParmName, object ParmValue, ParameterDirection direction)
    {
        this.AddParms(ParmName, ParmValue);
        this.SetParmDirection(ParmName, direction);
    }
    protected void SetParmDirection(string ParmName, ParameterDirection direction)
    {
        if (ParmName.StartsWith(this.AtSign) == false)
            ParmName = this.AtSign + ParmName;
        if (this.oCommand.Parameters.IndexOf(ParmName) > -1)
            ((IDbDataParameter)this.oCommand.Parameters[ParmName]).Direction = direction;
    }
    protected void SetAllParameters(DataRow Row)
    {
        this.ClearParameters();
        for (int i = 0; i < Row.Table.Columns.Count; i++)
        {
            this.AddParms(Row.Table.Columns[i].ColumnName, Row[i]);
        }
    }
    #endregion
    #region Database Methods, using the IDb interface members
    protected void FillData(DataSet ds, string StoredProcName)
    {
        this.oCommand.CommandText = StoredProcName;
        this.oAdapter.Fill(ds);
    }
    /// <summary>
    /// In order to reduce programmer error and to mimic the native functionality of the
    /// DataAdapter.Fill() command, we will open a connection if it has not already 
    /// been opened, Execute the Command and then leave it in the state that it was in
    /// prior to that (close it if needed or leave it open).
    /// </summary>
    /// <param name="StoredProcName"></param>
    /// <returns></returns>
    protected void ExecuteCommand(string StoredProcName)
    {
        bool IsAlreadyOpen = (this.oConnection.State == ConnectionState.Open);
        this.OpenConnection();
        this.oCommand.CommandText = StoredProcName;
        // 
        try 
        { 
            this.oCommand.ExecuteNonQuery(); 
        }
        catch (SqlException ex)
        {
            // Check if the sql exception is because of a deadlock rollback
            // If so we can attempt a retry
            if (ex.Number == DeadlockRollbackError)
                throw new BBRetryException("Deadlock", ex);
            else
                throw ex;
        }
        catch (Exception ex)
        {
            throw ex;
        }

        if (IsAlreadyOpen == false)
            this.CloseConnection();
    }
    protected void SaveTable(DataTable Table, string SaveProc)
    {
        // save each row
        foreach (DataRow row in Table.Rows)
        {
            this.SetAllParameters(row);
            this.ExecuteCommand(SaveProc);
        }
    }
    #endregion
    protected void OpenConnection()
    {
        if (this.oConnection.State != ConnectionState.Open)
        {
            this.ErrorMessage = "";
            this.oConnection.Open();
        }
    }
    public void CloseConnection()
    {
        if (this.oConnection.State != ConnectionState.Closed && this.oCommand.Transaction == null)
            this.oConnection.Close();
    }
}
public class BBRetryException : Exception
{
    public BBRetryException() { }
    public BBRetryException(string error) : base(error) { }
    public BBRetryException(string error, Exception e) : base(error, e) { }
}

Why and How to Sub-class Base Classes

2009-10-17T15:43:00.001-07:00

One of the “basic rules of programming” that I always live by (and I’m not alone), is that one should always sub-class many of the native base class controls. This is one of the first things you should do before undertaking any serious development, in any language. There are always little quirks in behavior in some of the base class controls that you want to get around for every instance of an object.

Say, as an example, that you've developed a few forms for your application. You've used the base class controls. At some point, you find something's not quite acting right and you want to change that behavior or you may just want to add something (some properties or something) to every ComboBox, for example, that you use. Now, since you used the base class controls to begin with ... guess what? You've outta luck!! You've got a lot of work ahead of you to change all those. Whereas, if you had sub-classed all the controls first (even if you haven't as yet put code in those sub-classes) and used your sub-classed controls on your forms, then you'll have no extra work to do when you decide that you need to make changes to your sub-classed control (and yes, even the form should be sub-classed).

Basically, you'll want a class library that contains your sub-classed UI controls, like textbox, button, etc. Something like this:

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;

namespace MyCompany.MyFramework.WinUI.MyClasses
{
    public class MyComboBox : System.Windows.Forms.ComboBox
    {
        // code here
    }

    public class MyTextBox : System.Windows.Forms.TextBox
    {
        // code here
    }

    public class MyButton : System.Windows.Forms.Button
    {
        // code here
    }
}

That's it. These controls can't be sub-classed visually, but as you can see, it's easy enough to do it in code. I have all the basic controls sub-classed in one class library file. Once they're added to the ToolBox, then can be dragged onto any design surface in the IDE.

Dealing with Carriage Returns in Web Service Methods

2009-10-11T14:14:00.001-07:00

When returning data from a database through a Web Service (as a DataSet, or as an XML representation of the data in the DataSet), you may have issues with new lines (carriage return/line feed) in your string data.

I use a SQL Server database. The "new line" data must be stored in the database like this: "\r\n". But, when passing this through a web service, it must be passed like this: "&#13". Consequently, to address this discrepancy in format, I use two methods in my Web Service base class: a ConvertCarriageReturnForGets() method and a ConvertCarriageReturnForSaves() method.

[WebService(Namespace = "http://mycompany.com/")]
public class BBWebServices : System.Web.Services.WebService
{
    #region Declarations

    private string CRLF    = "&#13;";
    private string NewLine = "\r\n";

    #endregion

    #region Convert CarriageReturn Methods

    /// <summary>
    /// Converts CarriageReturn/LineFeed characters so they are passed correctly through the Web Service
    /// </summary>
    /// <param name="dt"></param>
    /// <param name="ColumnName"></param>
    protected void ConvertCarriageReturnForGets(DataTable dt, string ColumnName)
    {
        if (dt.Columns.Contains(ColumnName))
        {
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                if (dt.Rows[i][ColumnName] != DBNull.Value)
                    dt.Rows[i][ColumnName] = dt.Rows[i][ColumnName].ToString().Replace(this.NewLine, this.CRLF);
            }
        }
    }
    /// <summary>
    /// Converts CarriageReturn/LineFeed characters so they are correctly saved to the database
    /// </summary>
    /// <param name="dt"></param>
    /// <param name="ColumnName"></param>
    protected void ConvertCarriageReturnForSaves(DataTable dt, string ColumnName)
    {
        if (dt.Columns.Contains(ColumnName))
        {
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                if (dt.Rows[i][ColumnName] != DBNull.Value)
                    dt.Rows[i][ColumnName] = dt.Rows[i][ColumnName].ToString().Replace(this.CRLF, this.NewLine);
            }
        }
    }

    #endregion
}

Use the methods like this in your Web Service classes:

[WebService(Namespace = "http://mycompany.com/")]
public class Customer : BBWebServices
{
    [WebMethod(Description="Returns Customer Info for one customer")]
    public string GetCustomer(long CustomerKey)
    {
        CustomerBiz oBiz = new CustomerBiz();
        CustomerDataSet ds = oBiz.GetCustomer(CustomerKey);

        this.ConvertCarriageReturnForGets(ds.CustomerNotes, "notes");
        
        string XML = ds.GetXml();
        return XML;
    }
    [WebMethod(Description="Saves info for one customer")]
    public bool SaveCustomer(string ChangeXML, string DeleteXML)
    {
        CustomerDataSet dsChanged = new CustomerDataSet();
        CustomerDataSet dsDeleted = new CustomerDataSet();

        try
        {
            // FillWithXml() is a method I have defined in my DataSet classes
            // It simply uses the DataSet's ReadXml() method with a string reader
            dsChanged.FillWithXml(ChangeXML);
            if (DeleteXML != "")
                dsDeleted.FillWithXml(DeleteXML);
        }
        catch (Exception ex)
        {
            Message = ex.Message;
            return false;
        }

        CustomerBiz oBiz = new CustomerBiz();
        this.ConvertCarriageReturnForSaves(dsChanged.CustomerNotes, "notes");

        bool IsOK = o.SaveCustomer(dsChanged, dsDeleted);
        return IsOK;
    }
}

DataAccess – Part II

2009-10-04T17:46:00.001-07:00

In Part I of this topic, I tried to explain the basic functionality you should strive for in a DataAccess layer in your applications:

Keep the DataAccess totally separate from your UI layer.
Pass data objects between the layers.

In Part II, I will attempt to show you how to abstract your DataAccess layer one more step. The key is to program to the IDb interfaces, as I will show below.

My DataAccess layer has two base classes: BBConnection and BBDataAccess. All the methods are coded to the various DataAccess Interfaces (the IDb interface objects). DataSets are used simply for transporting data back and forth. I know that there are plenty of camps who advocate Biz objects for this, but I'm a believer of Typed DataSets for transporting my data between front-end and back-end.

The BBConnection class basically has a GetConnection() method that finds the Connection String from the app's config info and returns an instantiated Connection. It returns it as an interface, the IDbConnection.

/// <summary>
/// For another type of database, replace the SqlConnection in the constructor
/// </summary>
public class BBConnection
{
    #region Declaration
    // Use a variable to store the connection string, if you try and get it from the SqlConnection object, it strips the password
    public string ConnectionString = "";
    #endregion
    #region Methods
    public IDbConnection GetConnection()
    {
        return this.GetConnection("");
    }
    public IDbConnection GetConnection(string tConnectionString)
    {
        string cConnection = tConnectionString;
        if (tConnectionString == "")
            cConnection = ConfigurationSettings.AppSettings["ConnectionString"];
        SqlConnection conn = new SqlConnection(cConnection);
        this.ConnectionString = cConnection;
        return conn;
    }
    #endregion
}

Probably a slightly better design than what I show below, would be to use a Factory Pattern ... but my code was initially refactored from more tightly-coupled-to-SQL code, and at the time was easier done the way I did it. I'll leave that as an exercise for the reader! ;-)

The BBDataAccess class, simplified, is something like this:

public class BBDataAccess
{
    #region Declarations
    private IDbConnection oConnection;
    private IDbCommand oCommand;
    private IDbDataAdapter oAdapter;
    private IDataParameterCollection oParms;
    public string ConnectionString = "";
    protected string ErrorMessage = "";
    #endregion
    #region Constructor/Destructor
    public BBDataAccess()
    {
        BBConnection o = new BBConnection();
        this.oConnection = o.GetConnection();
        this.ConnectionString = o.ConnectionString;
        this.oCommand = this.GetIDbCommand();
        this.oAdapter = this.GetIDbDataAdapter(this.oCommand);
        this.oCommand.Connection = this.oConnection;
        this.oCommand.CommandType = CommandType.StoredProcedure;
    }
    #endregion
    #region Get/Use IDb Interface Objects
    protected IDbCommand GetIDbCommand()
    {
        return new SqlCommand();
    }
    protected IDbDataAdapter GetIDbDataAdapter(IDbCommand command)
    {
        return new SqlDataAdapter((SqlCommand)command);
    }
    protected IDbDataParameter GetIDbDataParameter(string ParmName, object ParmValue)
    {
        return new SqlParameter(ParmName, ParmValue);
    }
    protected void DeriveParameters(IDbCommand command)
    {
        SqlCommandBuilder.DeriveParameters((SqlCommand)command);
    }
    #endregion
    // Then there are the various protected methods for adding and setting Parameters, filling a DataSet, etc.
    // It's these various methods that get used in the classes that are sub-classed from this "base" class.
    // Here's just a few of them:
    protected void ClearParameters()
    {
        this.oCommand.Parameters.Clear();
        this.ErrorMessage = "";
    }
    protected void AddParms(string ParmName, object ParmValue)
    {
        try
        {
            if (ParmName.StartsWith(this.AtSign) == false)
                ParmName = this.AtSign + ParmName;
            if (ParmValue != DBNull.Value)
                this.oCommand.Parameters.Add(this.GetIDbDataParameter(ParmName, ParmValue));
        }
        catch (Exception ex)
        {
            this.ErrorMessage += ex.Message;
        }
    }
    protected void AddParms(string ParmName, object ParmValue, ParameterDirection direction)
    {
        this.AddParms(ParmName, ParmValue);
        this.SetParmDirection(ParmName, direction);
    }
    protected bool FillData(DataSet ds, string StoredProcName)
    {
        try
        {
            this.oCommand.CommandText = StoredProcName;
            this.oAdapter.Fill(ds);
        }
        catch (Exception ex)
        {
            this.ErrorMessage += ex.Message;
            this.CloseConnection();
            return false;
        }
        return true;
    }
}

In this way, your DataAccess sub-classes are totally "disassociated" from knowing what your back-end data is (could be SqlServer, as the above class is, but if you also have an Oracle customer, you have another base class exactly the same as the above class, but it uses Oracle-specific stuff instead and you use the appropriate DLL depending on your which database your customer has ... however, you're still programming to the interface (the IDb Interface objects), so your sub-classes, which are in different projects (and thus different DLLs) don't care and don't need to be changed.

So, an example of a sub-class might be this:

public class CustomerAccess : BBDataAccess
{
    #region Public Get Methods
    public DataSet GetCustomerInfo(long CustomerKey)
    {
        CustomerDataSet ds = new CustomerDataSet();
        this.ClearParameters();
        this.AddParms("CustomerKey", CustomerKey);
        this.FillData(ds, "csp_Customer_Get");
        return ds;
    }
    // more methods
    #endregion
}

UPDATE: Since posting my 2-part DataAccess "article", I've realized that it should have been 3-part. There's another, better methodology for DataAccess that I've used and I'd like to show you all. So, see Part III of my series.

DataAccess – Part I

2009-09-29T16:55:00.001-07:00

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.):

      publicvoid 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 …

Deleting DataRows

2009-09-25T08:33:00.001-07:00

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).

TableAdapters Are Crap!

2009-09-22T08:54:00.001-07:00

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.

Fun With DataSets

2009-09-21T17:59:00.001-07:00

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.

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();
            }
        }
    }
}

Keeping DataGrids And Other UI Controls In Sync

2009-09-18T17:20:00.001-07:00

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.

Reflection in .NET

2009-09-17T15:15:00.000-07:00

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
}

3 Years Later

2009-09-14T14:13:00.000-07:00

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)

My First Blog

2006-09-02T07:49:00.000-07:00

Can you believe it? I am such a geek and yet, until now, had not fallen for the blog craze. I created this one on a whim this morning. We'll see how well I keep it up.

UPDATE: This was added long after (several years!!): I’m using this post to test some code snippet plug-ins. This post can certainly be ignored!

public void SetParmDirection(string ParmName, ParameterDirection direction)
 {
     if (ParmName.StartsWith("@") == false)
         ParmName = "@" + ParmName;
 
     if (this.oCommand.Parameters.IndexOf(ParmName) > -1)
         this.oCommand.Parameters[ParmName].Direction = direction;
 }

Another plug-in:

public void SetParmDirection(string ParmName, ParameterDirection direction)
{
    if (ParmName.StartsWith("@") == false)
        ParmName = "@" + ParmName;

    if (this.oCommand.Parameters.IndexOf(ParmName) > -1)
        this.oCommand.Parameters[ParmName].Direction = direction;
}

And now I’m going to test a combination of the two. This is tricky in that I have to steal the div tags from the first plug-in and wrap them around the html from the second plug-in … making sure to change the ids as well. Let’s see if this works well or not:

      // ds20 is a 2.0 Typed DataSet, which uses plain a plain old DataTable

DataTable dt20 = ds20.Personnel.DefaultView.ToTable(true);

DataTable dtLinq = ds20.Personnel.AsEnumerable()
    .Select(row => row).Distinct(DataRowComparer.Default)
    .CopyToDataTable();
// Note that if you MUST use the DataRowComparer.
// If you don't you get all rows returned ... it won't find the duplicates.

    

And another:

      // ds35 is the 3.5 Typed DataSet
// note that the only difference in syntax is that you don't need .AsEnumerable()
DataTable dt35 = ds35.Personnel.Select(row => row).Distinct(DataRowComparer.Default).CopyToDataTable();
    

OK … so it displays fine in my editor. Let’s publish it now to the blog and make sure it’s still OK.