Showing posts with label vb. Show all posts
Showing posts with label vb. Show all posts

Monday, October 21, 2013

Managing Data Among Multiple Forms (Part 3)

Part 1 here
Part 2 here

Firstly, let me apologise for having taken so long to finish this three-part series.  Parts 1 and 2 showed how you CAN manage data among multiple forms but this part 3 will show how you SHOULD do it.  That’s rather important I’d say, so let’s get into it.

The first and most important point to note is that forms are objects like any other, so moving data between forms is done just like it is for any other objects.  How do you usually pass data into an object?  You either set a property or else call a method and pass an argument.  How do you usually get data out of an object?  You either get a property or else call a method and get the return value.  That’s exactly how you pass data into and get data out of a form because forms are objects.

The second point to note is that, generally speaking, a control should only be accessed by the form it is on.  While it’s legal to access a control from outside its form, good practice dictates that you should not do so.  With the first point in mind, that means that getting data from a control on a different form means getting data from the other form and the other form getting it from the control.  Likewise, passing data to a control on another form means passing data to the other form and the other form passing it to the control.

This can be demonstrated fairly easily by displaying a list of records in one form and editing the selected record in another form.  To build such an example, start by creating a new Windows Forms Application project.  To Form1, add a DataGridView, a Button and a BindingSource.  Now add the following code to populate the grid with a few records at startup.

C#

  1. private void Form1_Load(object sender, EventArgs e)
  2. {
  3.     var table = new DataTable();
  4.  
  5.     table.Columns.Add("ID", typeof(int));
  6.     table.Columns.Add("Name", typeof(string));
  7.  
  8.     table.Rows.Add(1, "Peter");
  9.     table.Rows.Add(2, "Paul");
  10.     table.Rows.Add(3, "Mary");
  11.  
  12.     this.bindingSource1.DataSource = table;
  13.     this.dataGridView1.DataSource = this.bindingSource1;
  14. }

VB

  1. Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
  2.     Dim table As New DataTable
  3.  
  4.     With table.Columns
  5.         .Add("ID", GetType(Integer))
  6.         .Add("Name", GetType(String))
  7.     End With
  8.  
  9.     With table.Rows
  10.         .Add(1, "Peter")
  11.         .Add(2, "Paul")
  12.         .Add(3, "Mary")
  13.     End With
  14.  
  15.     Me.BindingSource1.DataSource = table
  16.     Me.DataGridView1.DataSource = Me.BindingSource1
  17. End Sub

Add a second form to the project and add a TextBox and a Button to that form.  What we’re going to do is click the Button in Form1 to open Form2, get the record selected in the DataGridView in Form1 and edit its Name field in the TextBox in Form2.  Now remember, Form2 cannot access the DataGridView in Form1 and Form1 cannot access the TextBox in Form2.  How to move the data back and forth?  The answer is that Form1 will set a property of Form2 to pass the initial data in and get the same property to get the final data out while, internally, that property of Form2 will access the TextBox.  Which property?  Well, one that you define yourself.

C#

  1. public string TextBoxText
  2. {
  3.     get
  4.     {
  5.         return this.textBox1.Text;
  6.     }
  7.     set
  8.     {
  9.         this.textBox1.Text = value;
  10.     }
  11. }

VB

  1. Public Property TextBoxText As String
  2.     Get
  3.         Return Me.TextBox1.Text
  4.     End Get
  5.     Set(value As String)
  6.         Me.TextBox1.Text = value
  7.     End Set
  8. End Property

Back in Form1, we need to handle the Click event of the Button, open Form2 and pass it the Name from the record selected in the DataGridView.

C#

  1. private void button1_Click(object sender, EventArgs e)
  2. {
  3.     using (var dialogue = new Form2())
  4.     {
  5.         var selectedRecord = (DataRowView)this.bindingSource1.Current;
  6.  
  7.         // Pass the initial data into the dialogue.
  8.         dialogue.TextBoxText = (string)selectedRecord["Name"];
  9.  
  10.         // Display the modal dialogue.
  11.         if (dialogue.ShowDialog() == DialogResult.OK)
  12.         {
  13.             // The user clicked OK so get the final data from the dialogue.
  14.             selectedRecord["Name"] = dialogue.TextBoxText;
  15.             this.bindingSource1.EndEdit();
  16.         }
  17.     }
  18. }

VB

  1. Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
  2.     Using dialogue As New Form2
  3.         Dim selectedRecord = DirectCast(Me.BindingSource1.Current, DataRowView)
  4.  
  5.         'Pass the initial data into the dialogue.
  6.         dialogue.TextBoxText = CStr(selectedRecord("Name"))
  7.  
  8.         'Display the modal dialogue.
  9.         If dialogue.ShowDialog() = DialogResult.OK Then
  10.             'The user clicked OK so get the final data from the dialogue.
  11.             selectedRecord("Name") = dialogue.TextBoxText
  12.             Me.BindingSource1.EndEdit()
  13.         End If
  14.     End Using
  15. End Sub

All that’s left to do now is for Form2 to return a result of OK when the user clicks the Button.

C#

  1. private void button1_Click(object sender, EventArgs e)
  2. {
  3.     this.DialogResult = DialogResult.OK;
  4. }

VB

  1. Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
  2.     Me.DialogResult = DialogResult.OK
  3. End Sub

Run the project now and Form1 will appear displaying the three records.  Select one of the records and click the Button.  You will see Form2 open with the Name field value from the selected record in the TextBox.  Try editing the name and then click the Close button on the title bar of Form2.  The dialogue will close and you’ll see that the Name field of the selected record remains unchanged.  That’s because the DialogResult returned by ShowDialog was Cancel rather than OK.

Click the Button on Form1 again and this time, after editing the name in the TextBox, click the Button on Form2.  This time, you’ll see that Form2 closes and the Name field of the selected record is updated to the value that you entered in the TextBox.  Congratulations!  You just passed data between two forms the right way.

That’s nice and all but what if, in our example, you wanted to do something back in Form1 without closing Form2?  As it stands, ShowDialog returning is Form1’s notification that it should get some data from Form2 and update its own DataGridView.  If we don’t call ShowDialog though, it can’t return and we can’t use it as a notification.  What to do?  Well, how are you usually notified that something has happened in .NET code?  You handle an event.

If you want to learn all the details about custom events, I suggest that you check out my blog post here.  I’m going to do it quick and dirty here because the point of this post is how to handle the event and use that notification rather than the details of how to generate it in the first place.

In Form2, we need to add an event that will notify anyone listening that the text in the TextBox has changed and, instead of closing the form when the Button is clicked, we need to raise that event.

C#

  1. public event EventHandler TextBoxTextChanged;
  2.  
  3. private void button1_Click(object sender, EventArgs e)
  4. {
  5.     if (this.TextBoxTextChanged != null)
  6.     {
  7.         this.TextBoxTextChanged(this, EventArgs.Empty);
  8.     }
  9. }

VB

  1. Public Event TextBoxTextChanged As EventHandler
  2.  
  3. Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
  4.     RaiseEvent TextBoxTextChanged(Me, EventArgs.Empty)
  5. End Sub

We now need to handle that event in Form1.  When it’s raised, we need to do as before and get the data from Form2 to update the selected record.

C#

  1. private Form2 dialogue;
  2. private DataRowView selectedRecord;
  3.  
  4. private void button1_Click(object sender, EventArgs e)
  5. {
  6.     if (this.dialogue == null || this.dialogue.IsDisposed)
  7.     {
  8.         this.selectedRecord = (DataRowView)this.bindingSource1.Current;
  9.  
  10.         this.dialogue = new Form2();
  11.         this.dialogue.TextBoxTextChanged += dialogue_TextBoxTextChanged;
  12.         this.dialogue.FormClosed += dialogue_FormClosed;
  13.  
  14.         // Pass the initial data into the dialogue.
  15.         this.dialogue.TextBoxText = (string)this.selectedRecord["Name"];
  16.  
  17.         this.dialogue.Show();
  18.     }
  19.  
  20.     this.dialogue.Activate();
  21. }
  22.  
  23. private void dialogue_TextBoxTextChanged(object sender, EventArgs e)
  24. {
  25.     // Get the final data from the dialogue.
  26.     this.selectedRecord["Name"] = dialogue.TextBoxText;
  27.     this.bindingSource1.EndEdit();
  28. }
  29.  
  30. private void dialogue_FormClosed(object sender, FormClosedEventArgs e)
  31. {
  32.     // Remove event handlers when the form closes.
  33.     this.dialogue.TextBoxTextChanged -= dialogue_TextBoxTextChanged;
  34.     this.dialogue.FormClosed -= dialogue_FormClosed;
  35. }

VB

  1. Private WithEvents dialogue As Form2
  2. Private selectedRecord As DataRowView
  3.  
  4. Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
  5.     If Me.dialogue Is Nothing OrElse Me.dialogue.IsDisposed Then
  6.         Me.selectedRecord = DirectCast(Me.BindingSource1.Current, DataRowView)
  7.  
  8.         Me.dialogue = New Form2()
  9.  
  10.         'Pass the initial data into the dialogue.
  11.         Me.dialogue.TextBoxText = CStr(selectedRecord("Name"))
  12.  
  13.         Me.dialogue.Show()
  14.     End If
  15.  
  16.     Me.dialogue.Activate()
  17. End Sub
  18.  
  19. Private Sub dialogue_TextBoxTextChanged(sender As Object, e As EventArgs) Handles dialogue.TextBoxTextChanged
  20.     'Get the final data from the dialogue.
  21.     Me.selectedRecord("Name") = dialogue.TextBoxText
  22.     Me.BindingSource1.EndEdit()
  23. End Sub

If you run the project again you will see that you can open the dialogue and edit the selected record multiple times without closing the dialogue.  If you close the dialogue and select another record then you can open a new dialogue and edit that as well.

This is a slightly contrived example but hopefully you get the idea.  If you want to update a control in a form then only do it in that form.  If you need to push and/or pull data between forms then you do so by getting or setting properties and/or calling methods of that form.  If you need to notify a form that data is available to get then you do so with an event.

Happy trails!

Post compiled using Windows Live Writer with Paste as Visual Studio Code plug-in.

Tuesday, April 3, 2012

Managing Data Among Multiple Forms (Part 2)

Part 1 here

In part 1 of this series, I looked at using default instances in VB and how that gives you direct access to controls on any form, anywhere, any time.  I also showed how you could simulate default instances in C#.  Default instances are a handy feature for beginners but I wouldn’t recommend their use to anyone who’s serious about programming.  They’re an easy way to start out though.

This instalment will describe the use of what’s commonly as global variables.  This is another technique that makes life easier for beginners but should generally be avoided because it’s not really architecturally sound.  I’ll cover it here though for completeness and because it is also an easy way to start out, when most of what you do will not be architecturally sound.

As the name suggests, global variables are intended to give global access, i.e. you can access them anywhere, any time.  Just like default form instances, the idea is that you don’t have to create an object explicitly in order to access them, which means that you’re not limited to accessing that object only where it’s created.  There are various ways that that can be achieved, but the simplest is to use a module in VB or the equivalent in C#, which is a static class.

We’ll use basically the same example as last time, transferring text from a TextBox on one form to a TextBox on another form and then back again.  This time though, instead of one form passing data to and retrieving data from the other form, each will deal only with the global variable.

So, first things first, let’s declare that global variable.  In VB, add a new module to your project and add the following code:

Module Module1

    'The text that will be transferred between TextBoxes.
    Public textBoxText As String

End Module
In C#, add a new class to your project and add the following code:

static class Class1
{
    // The text that will be transferred between TextBoxes.
    public static string textBoxText;
}
Notice the addition of the ‘static’ keyword to both the class and the field.  A static member is one that is a member of the class itself rather than each specific instance and a static class is one that can only have static members.  The class itself doesn’t actually have to be static but it’s the correct thing to do.
Set up the user interface as before, with a TextBox and a Button on Form1 and a TextBox on Form2.  Create a Click event handler for the Button on Form1 and add the following code:

VB
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    'Push the data to the global variable from the TextBox on this form.
    textBoxText = Me.TextBox1.Text

    'Show the second form as a modal dialogue.
    Using dialogue As New Form2
        dialogue.ShowDialog()
    End Using

    'Pull the data from the global variable to the TextBox on this form.
    Me.TextBox1.Text = textBoxText
End Sub
C#
private void button1_Click(object sender, EventArgs e)
{
    // Push the data to the global variable from the TextBox on this form.
    Class1.textBoxText = this.textBox1.Text;

    // Show the second form as a modal dialogue.
    using (Form2 dialogue = new Form2())
    {
        dialogue.ShowDialog();
    }

    // Pull the data from the global variable to the TextBox on this form.
    this.textBox1.Text = Class1.textBoxText;
}
Note that in both VB and C# I have included a ‘Using’/’using’ statement.  This is to create the dialogue and destroy it again after it’s been used.  I’m not using a default instance in the VB code to show that this technique of using global variables doesn’t depend on them.  Also note that the global variable is qualified with the class name in the C# code but it is not qualified with the module name in the VB code.  You can qualify module members in VB if you want to but the module name can be omitted, which is behaviour consistent with modules in VB6.

Now, create handlers for the Load and FormClosed events in Form2 and add this code:

VB
Private Sub Form2_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    'Pull the data from the global variable to the TextBox on the this form.
    Me.TextBox1.Text = textBoxText
End Sub

Private Sub Form2_FormClosed(sender As Object, e As FormClosedEventArgs) Handles Me.FormClosed
    'Push the data to the global variable from the TextBox on this form.
    textBoxText = Me.TextBox1.Text
End Sub

C#
private void Form2_Load(object sender, EventArgs e)
{
    // Pull the data from the global variable to the TextBox on the this form.
    this.textBox1.Text = Class1.textBoxText;
}

private void Form2_FormClosed(object sender, FormClosedEventArgs e)
{
    // Push the data to the global variable from the TextBox on this form.
    Class1.textBoxText = this.textBox1.Text;
}
That’s all we need.  You can now run the project and do as for the previous example.  Enter some text in the TextBox on Form1 and then click the Button and you’ll see that text appear in the TextBox on Form2.  Edit that text and close the form and you’ll see the new text appear in the TextBox on the first form.

Looking back at the code, you can see that neither form refers directly to the TextBox on other.  In fact, there’s no reference to Form1 at all in Form2.  Both forms use the global variable as an intermediary, with each form having no specific dependency on the other.

That’s it for this instalment on global variables.  In the next instalment, I’ll start looking at how to do things the “proper” way.

Part 3 here

Sunday, April 1, 2012

Managing Data Among Multiple Forms (Part 1)

Lots of people ask questions about how to pass data between forms.  It’s a slightly tricky question to answer because there are several ways to do it, all variations on a theme, and the “proper” way is the most complex.  “Complex” is a relative term though, as none of them are particularly difficult.  I’m going to dedicate a separate post to each of the various options, using the same basic example in each case.  That example will involve two forms, each with a TextBox.  The user will enter text into the TextBox on the first form and click a Button.  That will open the second form and transfer the text to be displayed in the TextBox on that.  The user can then edit the text on the second form and, when they close it, the new text will be transferred back to the TextBox on the first form.

The first example is VB-specific, but can be simulated in C#.  I have previously posted about default instances here, so for more general information you should start there.  Here we will concentrate on actually passing data between two default instances.  So, first let’s set up the project as described above.  Create a new VB Windows Forms Application project and add a TextBox and a Button to the Form1 that’s created by default.  Add a second form and add a TextBox to that as well.

There are two options for passing the data around: push and pull.  The data producer can push the data to the consumer or the consumer can pull it from the producer.  We’ll construct this example that uses both in two different combinations.  First, let’s make Form1 push the initial data to Form2 and then pull the new data back again.

So, on Form1, double-click the Button you added and add the following code:

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    'Push the data to the TextBox on the second form from the TextBox on this form.
    Form2.TextBox1.Text = Me.TextBox1.Text

    'Show the second form as a modal dialogue.
    Form2.ShowDialog()

    'Pull the data from the TextBox on the second form to the TextBox on this form.
    Me.TextBox1.Text = Form2.TextBox1.Text
End Sub

The first line gets the text from the TextBox on the current form and displays it in the TextBox on the default instance of Form2.  The second line displays the default instance of Form2 as a modal dialogue.  The third line gets the text from the TextBox on the default instance of Form2 and displays it in the Textbox on the current form.  Now, run the project, enter some text into the TextBox on Form1 and click the Button.  You’ll see that whatever you entered into the TextBox on Form1 displayed in the TextBox on Form2.  Edit the text in the TextBox and close Form2 and you’ll then see the new text displayed in the TextBox on Form1.

In that example, Form1 does all the work, pushing data one way and pulling it the other.  Let’s change things up a bit and let Form2 do the work.  Change your Button’s Click event handler to this:

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    'Show the second form as a modal dialogue.
    Form2.ShowDialog()
End Sub

Form1 is still displaying Form2 but it is not moving any of the data around anymore.  Double-click Form2 to create a Load event handler and add the following code:

Private Sub Form2_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    'Pull the data from the TextBox on the first form to the TextBox on the this form.
    Me.TextBox1.Text = Form1.TextBox1.Text
End Sub

That will get the data from the TextBox on the default instance of Form1 and display it in the TextBox on the current form just before Form2 is displayed.  It’s worth mentioning at this point that the startup form in a VB Windows Forms Application project is always the default instance of its type.  Using the drop-down lists at the top of the code window, create a FormClosed event handler and add this code:

Private Sub Form2_FormClosed(sender As Object, e As FormClosedEventArgs) Handles Me.FormClosed
    'Push the data to the TextBox on the first form from the TextBox on this form.
    Form1.TextBox1.Text = Me.TextBox1.Text
End Sub

That will get the data from the TextBox on the current form and display it in the TextBox on the default instance of Form1 just after Form2 closes.  Now, as before, run the project, enter text in the TextBox on Form1, click the Button, edit the text in the TextBox on Form2 and then close Form2.  As before, you’ll see whatever you entered on Form1 transferred to Form2 and whatever you change it to on Form2 transferred back to Form1.

That’s basically it.  You can access default instances anywhere in your project so you can always access any and all public members of that instance whenever you want.  In VB, when you add a control or component to a form in the designer they will be public by default, so you can access those controls directly from other forms whenever you want.  I don’t really recommend using default instances or public controls, but it’s the easy option for beginners.

Now, as I said earlier, default instances are a VB-specific feature but, if you want, you can simulate them in C#.  Given that default instances are aimed at beginners and this C# implementation requires use of a custom generic class, it defeats the purpose somewhat, but it’s an interesting case study nonetheless.

Here is a relatively simple class that will partially simulate default instances in C#:

///
/// Provides VB-style default instance behaviour for forms.
///
///
/// The type of form for which a default instance is provided.
///
public class DefaultInstance where TForm : Form, new()
{
    ///
    /// Refers to the current default instance.
    ///
    private static TForm _instance;

    ///
    /// Gets the default instance of the form type.
    ///
    public static TForm Instance
    {
        get
        {
            // Check whether there is no current default instance or that instance has been displayed.
            if (_instance == null || _instance.IsDisposed)
            {
                // Create a new default instance.
                _instance = new TForm();
            }

            return _instance;
        }
    }
}
Just like default instances in VB, this class requires that the form class have a parameterless constructor.  Unlike the default instances in VB, the instance maintained by this class is not thread-specific.  It could be reimplemented to provide that behaviour but, to be honest, I’m not sure that that would be an improvement.  As I said in the post on default instances that I linked to earlier, the fact that they are thread-specific is actually an encumbrance at times.  Presumably there is a reason that Microsoft chose to implement them that way though, so maybe there are other issues that I’m not aware of.

Anyway, what I’ve presented there is enough for our purposes here.  Where you would normally just use the form class name in VB code to refer to the default instance, e.g.

SomeFormClass.Show()

you would use the DefaultInstance class in C# code like this:

DefaultInstance<SomeFormClass>.Instance.Show();

A little more verbose but not a big deal.

So, create a new C# Windows Forms Application project and add the same forms and controls as specified previously.  Again, double-click the Button on Form1 to create a Click event handler and add the following code:

private void button1_Click(object sender, EventArgs e)
{
    // Push the data to the TextBox on the second form from the TextBox on this form.
    DefaultInstance<Form2>.Instance.textBox1.Text = this.textBox1.Text;

    // Show the second form as a modal dialogue.
    DefaultInstance<Form2>.Instance.ShowDialog();

    // Pull the data from the TextBox on the second form to the TextBox on this form.
    this.textBox1.Text = DefaultInstance<Form2>.Instance.textBox1.Text;
}

Run the project as before and you’ll see the text transferred from Form1 to Form2 and back again.
That works when Form1 does all the work but there is an extra step required when Form2 is doing the work.  As I said earlier, the startup form is always the default instance of its type in VB applications.  That’s what allowed us to refer to the default instance of Form1.  In our C# project, we need to edit the Main method so that it uses our DefaultInstance class to create the startup form.  That means that the Main method, found in the Program.cs code file, must look like this:

///
/// The main entry point for the application.
///
[STAThread]
static void Main()
{
    Application.EnableVisualStyles();
    Application.SetCompatibleTextRenderingDefault(false);
    Application.Run(DefaultInstance<Form1>.Instance);
}

Because the startup form is now the default instance of its type, Form2 can access it directly via the DefaultInstance class.  Note that another difference between VB and C# is that controls and components added to a form in the designer are private by default.  That really is the proper way to go but then we’re using default instances here so we’re not doing things the proper way.  With that in mind, you’ll need to change the access modifier of the controls on the two forms from Private to Public.

Change the Button.Click event handler in Form1 to this:

private void button1_Click(object sender, EventArgs e)
{
    // Show the second form as a modal dialogue.
    DefaultInstance<Form2>.Instance.ShowDialog();
}

and add a Load event handler and a FormClosed event handler to Form2 like this:

private void Form2_Load(object sender, EventArgs e)
{
    // Pull the data from the TextBox on the first form to the TextBox on the this form.
    this.textBox1.Text = DefaultInstance<Form1>.Instance.textBox1.Text;
}

private void Form2_FormClosed(object sender, FormClosedEventArgs e)
{
    // Push the data to the TextBox on the first form from the TextBox on this form.
    DefaultInstance<Form1>.Instance.textBox1.Text = this.textBox1.Text;
}

Run the project as before and again see that the data is transferred from Form1 to Form2 and back again.

That’s really all there is to it.  If you use default instances every time you display a form then you can use that default instance to access the form from anywhere and at any time in your application.  You can access any public members of that form so, if your controls are public, you can manipulate them directly from other forms.

The techniques shown here make transferring data between forms easy for those who don’t have a lot of experience.  In the second part of this series, I’ll demonstrate another option that makes it easy for beginners but is not conducive to writing applications properly.  That technique is the use of so-called global variables.

Part 2 here
Part 3 here

Monday, July 6, 2009

Database-independent Data Access Layer

We all know that well-written applications separate the various areas of responsibility into multiple layers, right? Generally speaking, the less each of those layers depend on specific functionality of the others the better. One of those areas of responsibility that warrants separation is data access. There are numerous tools about to generate data access code for you these days but, even if you're more of a roll-your-own kinda person, there's still help at hand. ADO.NET provides the tools to write data access code that is independent of the underlying data source and it has done since version 2.0.

The lynchpin of this functionality is the DbProviderFactory class. It provides methods to generate instances of all the usual ADO.NET objects, e.g. connections, commands and data adapters, for any specific ADO.NET provider.

Let’s start with a whirlwind tour of the System.Data and System.Data.Common namespaces. Amongst other things, the System.Data namespace contains the interfaces that declare the set of functionality required by ADO.NET data access objects, e.g. IDataReader for all data readers and IDbConnection for all connections. The System.Data.Common namespace contains classes that provide a basic concrete implementation of these interfaces, e.g. DbDataReader implements IDataReader and DbConnection implements IDbConnection. Each data source-specific ADO.NET provider will then declare a set of classes that inherit those from System.Data.Common to provide a data source-specific set of functionality, e.g. SqlClient.SqlDataReader inherits DbDataReader and OleDb.OleDbConnection inherits DbConnection.

The System.Data.Common namespace also includes the DbProviderFactory class, which provides an abstract implementation of an ADO.NET object factory. Each data source-specific ADO.NET provider should inherit this class and provide its own data source-specific implementation, e.g. SqlClientFactory and OleDbFactory. Third party ADO.NET providers can and should do the same thing, e.g. MySql.Data.MySqlClient.MySqlClientFactory.

Now, ADO.NET factory classes are singletons. Each concrete factory implementation will have a static/Shared Instance field that you can use to get the one and only instance of that type. Doing so directly defeats the purpose somewhat though. You will normally get a DbProviderFactory instance using the DbProviderFactories helper class, which is also a member of the System.Data.Common namespace. The DbProviderFactories class includes two static/Shared methods: GetFactoryClasses will return a DataTable containing information about all ADO.NET factories available and GetFactory will return an instance of a specific ADO.NET factory class.

To see how this works, try creating a new Windows Forms application, adding a DataGridView to the default form and then binding that grid to the result of the DbProviderFactories.GetFactoryClasses method:

C#

this.dataGridView1.DataSource = DbProviderFactories.GetFactoryClasses();

VB

Me.DataGridView1.DataSource = DbProviderFactories.GetFactoryClasses()

If you run that application you will see a row each for factories named “Odbc Data Provider”, “OleDb Data Provider”, “OracleClient Data Provider” and “SqlClient Data Provider”. If you’ve installed Microsoft SQL Server CE then you’ll also see a row for a factory named “Microsoft SQL Server Compact Data Provider”. If you’ve installed some third party data provider then you’ll see a row for that too. For instance, if you’ve installed MySQL’s Connector/Net then you’ll see a row for a factory named “MySQL Data Provider”. If you’ve installed Oracle’s ODAC then you’ll see a row for a factory named “Oracle Data Provider for .NET”.

To get an appropriate instance of the DbProviderFactory class for your data source you would normally call the DbProviderFactories.GetFactory method. You can pass that method either a DataRow from the DataTable returned by the GetFactoryClasses method or else one of the values from the InvariantName column of that table. For instance, you could pass the string “System.Data.SqlClient” to the GetFactory method and it would return the value of the SqlClientFactory.Instance field.

This provides us with two simple and logical ways to create an ADO.NET factory. Firstly, we can call GetFactories and display the results to the user, e.g. in a ComboBox, for them to choose a provider:

C#

this.comboBox1.DisplayMember = "Name";
this.comboBox1.DataSource = DbProviderFactories.GetFactoryClasses();

VB

Me.ComboBox1.DisplayMember = "Name"
Me.ComboBox1.DataSource = DbProviderFactories.GetFactoryClasses()

We can then call GetFactory and pass the row they selected as an argument:

C#

DataRow providerRow = ((DataRowView)this.comboBox1.SelectedItem).Row;
DbProviderFactory factory = DbProviderFactories.GetFactory(providerRow);

VB

Dim providerRow As DataRow = DirectCast(Me.ComboBox1.SelectedItem, DataRowView).Row
Dim factory As DbProviderFactory = DbProviderFactories.GetFactory(providerRow)

Alternatively, we can store the invariant name of our desired provider somewhere, e.g. in the config file, and pass that to GetFactory as an argument:

C#

string invariantName = Properties.Settings.Default.ProviderFactoryInvariantName;
DbProviderFactory factory = DbProviderFactories.GetFactory(invariantName);

VB

Dim invariantName As String = My.Settings.ProviderFactoryInvariantName
Dim factory As DbProviderFactory = DbProviderFactories.GetFactory(invariantName)

So, once we have our factory object, what do we do with it? Well, we use it to create all our data access objects, including connections, commands, parameters, data adapters and more. For instance, the following code might form part of your repository layer:

C#

private readonly DbProviderFactory _factory;
 
public string ConnectionString { get; set; }
 
public Repository(string factoryName)
{
    this._factory = DbProviderFactories.GetFactory(factoryName);
}
 
public Repository(DataRow factoryRow)
{
    this._factory = DbProviderFactories.GetFactory(factoryRow);
}
 
public DbConnection GetConnection()
{
    return this.GetConnection(this.ConnectionString);
}
 
public DbConnection GetConnection(string connectionString)
{
    DbConnection connection = this._factory.CreateConnection();
 
    connection.ConnectionString = connectionString;
 
    return connection;
}
 
public DbParameter GetParameter()
{
    return this._factory.CreateParameter();
}
 
public DbParameter GetParameter(string parameterName,
                                object value)
{
    DbParameter parameter = this.GetParameter();
 
    parameter.ParameterName = parameterName;
    parameter.Value = value;
 
    return parameter;
}
 
public DbCommand GetCommand()
{
    return this._factory.CreateCommand();
}
 
public DbCommand GetCommand(string commandText)
{
    DbCommand command = this.GetCommand();
 
    command.CommandText = commandText;
 
    return command;
}
 
public DbCommand GetCommand(string commandText,
                            IDictionary parameters)
{
    DbCommand command = this.GetCommand(commandText);
 
    foreach (string parameterName in parameters.Keys)
    {
        command.Parameters.Add(this.GetParameter(parameterName,
                                                 parameters[parameterName]));
    }
 
    return command;
}
 
public DataTable GetDataTable(string procName,
                              IDictionary parameters)
{
    DbCommand command = this.GetCommand(procName,
                                        parameters);
    DbConnection connection = this.GetConnection();
 
    command.Connection = connection;
    command.CommandType = CommandType.StoredProcedure;
 
    connection.Open();
 
    DbDataReader reader = command.ExecuteReader(CommandBehavior.KeyInfo |
                                                CommandBehavior.CloseConnection);
    DataTable table = new DataTable();
 
    table.Load(reader);
    reader.Close();
 
    return table;
}

VB

Private ReadOnly _factory As DbProviderFactory
Private _connectionString As String
 
Public Property ConnectionString() As String
    Get
        Return Me._connectionString
    End Get
    Set(ByVal value As String)
        Me._connectionString = value
    End Set
End Property
 
Public Sub New(ByVal factoryName As String)
    Me._factory = DbProviderFactories.GetFactory(factoryName)
End Sub
 
Public Sub New(ByVal factoryRow As DataRow)
    Me._factory = DbProviderFactories.GetFactory(factoryRow)
End Sub
 
Public Function GetConnection() As DbConnection
    Return Me.GetConnection(Me.ConnectionString)
End Function
 
Public Function GetConnection(ByVal connectionString As String) As DbConnection
    Dim connection As DbConnection = Me._factory.CreateConnection()
 
    connection.ConnectionString = connectionString
 
    Return connection
End Function
 
Public Function GetParameter() As DbParameter
    Return Me._factory.CreateParameter()
End Function
 
Public Function GetParameter(ByVal parameterName As String, _
                             ByVal value As Object) As DbParameter
    Dim parameter As DbParameter = Me.GetParameter()
 
    parameter.ParameterName = parameterName
    parameter.Value = value
 
    Return parameter
End Function
 
Public Function GetCommand() As DbCommand
    Return Me._factory.CreateCommand()
End Function
 
Public Function GetCommand(ByVal commandText As String) As DbCommand
    Dim command As DbCommand = Me.GetCommand()
 
    command.CommandText = commandText
 
    Return command
End Function
 
Public Function GetCommand( _
ByVal commandText As String, _
ByVal parameters As IDictionary(Of String, Object)) As DbCommand
    Dim command As DbCommand = Me.GetCommand(commandText)
 
    For Each parameterName As String In parameters.Keys
        command.Parameters.Add(Me.GetParameter(parameterName, _
                                               parameters(parameterName)))
    Next parameterName
 
    Return command
End Function
 
Public Function GetDataTable( _
ByVal procedureName As String, _
ByVal parameters As IDictionary(Of String, Object)) As DataTable
    Dim command As DbCommand = Me.GetCommand(procedureName, parameters)
    Dim connection As DbConnection = Me.GetConnection()
 
    command.Connection = connection
    command.CommandType = CommandType.StoredProcedure
 
    connection.Open()
 
    Dim reader As DbDataReader = command.ExecuteReader(CommandBehavior.KeyInfo Or _
                                                       CommandBehavior.CloseConnection)
    Dim table As New DataTable()
 
    table.Load(reader)
    reader.Close()
 
    Return table
End Function

The GetDateTable method takes the name of a stored procedure and a dictionary of parameter values keyed on name as arguments. It then invokes other methods that use the factory to create a connection and a command with parameters. The DbProviderFactory class can also generate connection string builders, data adapters and command builders, so you can implement many other scenarios in a similar fashion. In this way you can build up a complete data access layer with nary a reference to any specific data source.