TransactionScope and SqlServer

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

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

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

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

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 && 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 && 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

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

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

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

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

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

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

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

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

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.