Wednesday, December 30, 2009

Getting Non-Null Data Redux

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

Sunday, December 20, 2009

DefaultValue for Properties

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:

  1. It allows the property to be easily Reset to it's Default Value from the Property Sheet.
  2. 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!!

Monday, November 30, 2009

Hooking Into the Form's Events, from Form Controls

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

Friday, November 20, 2009

Reflection Class Redux

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(

Thursday, November 19, 2009

Dynamic Menu Items

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.

Sunday, November 15, 2009

Setting Focus to a Control at Form Load

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.

Thursday, November 05, 2009

Getting Non-Null Data

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"])


Tuesday, October 27, 2009

DataAccess - Part III

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

#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) { }
}


Saturday, October 17, 2009

Why and How to Sub-class Base Classes

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.

Sunday, October 11, 2009

Dealing with Carriage Returns in Web Service Methods

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

Sunday, October 04, 2009

DataAccess – Part II

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.