Developing Data Navigation Control (VCR) For Windows Forms

Introduction

Under windows forms data bound forms are very common. Many forms have buttons for data navigation such as next, previous, last and first. Such navigation control is commonly called as VCR control as it resembles VCR buttons. This sample code shows how to develop such a VCR custom control that you can use in your projects.

Source Code

The source code contains two files:

VCRControl.vb that represents VCR custom control
Form1.vb that represents sample client for VCR control

The VCR custom control exposes a property called DataSource that needs to be set to CurrencyManager class of your form. Once that is done you can use navigation buttons to navigate through records. The control also shows current record in the form of "x of y".

Screen Shot

Following image shows VCR navigation control in action

Developing Generic Data Access Layer Using ADO.NET 2.0

Introduction

Let's accept the real world fact that you need to write applications targeting not only SQL Server but also many other databases including MS-Access, Oracle and other ODBC databases. Classic ADO was a generic object model. You used to use the same Connection class with SQL Server, Access and Oracle. In ADO.NET, however, you need to use different classes depending on the provider you are using. That means before starting the coding phase you need to be aware of target database. If you develop an application using SQL Server data provider and later on decide to use Oracle you must change your code. At first thought using OLEDB data provider or ODBC data provider might come into your mind. However, considering the performance penalty that is not always a good idea. Won't it be nice to have a generic data access layer which will allow you to write provider independent code? That's exactly what this article will talk about.

Writing generic code with .NET 1.x

In .NET framework 1.x, people wrote generic data access code using interface based approach. You might be aware that all the data provider classes implement certain common interfaces. You can download one such implementation here. You can use the same approach in ADO.NET 2.0 also. However, ADO.NET 2.0 simplifies the job for you. Microsoft has opted for inheritance based approach rather than interface based one. This means all the data provider classes inherit from certain common base classes. For the sake of backward compatibility they still implement the interfaces as before. The following table lists these generic classes:

Data provider class	Base class
Connection	DbConnection
Command	DbCommand
Parameter	DbParameter
DataReader	DbDataReader
Dataadapter	DbDataAdapter
Transaction	DbTransaction

That means SqlConnection and OracleConnection classes inherit from a common base class called DbConnection.

Taking advantage of polymorphism via inheritance we can now write generic code capable of dealing with multiple database in neutral manner. You may feel - "previously there were interfaces now there are common base classes. How does that make your life easy?". But wait. ADO.NET 2.0 has more to offer. In addition to the classes mentioned in the above table, ADO.NET 2.0 also provides a set of classes called "Factory" classes. These factory classes help you to create instances of these base classes dynamically. This ability makes it possible to store the choice of your data provider in a configuration file and then at run time create instances of corresponding data provider classes. For example, SQL server dta provider has a factory class called SqlClientFactory that allows you to create instances of SqlConnection, SqlCommand and so on. Similar classes exists for other data providers also. The SqlClientFactory class has various methods as listed in the following table:

Factory class method	Purpose
CreateConnection	Creates an instance of SqlConnection class
CreateCommand	Creates an instance of SqlCommand class
CreateParameter	Creates an instance of SqlParameter class
CreateDataAdapter	Creates an instance of SqlDataAdapter class
CreateCommandBuilder	Creates an instance of SqlCommandBuilder class

Example of writing generic data access code

Let's see with a code snippet how the above classes can be used.

public void ExecuteQuery(string sql,string provider)
{
	DbConnection cnn=null;
	DbCommand cmd=null;
	DbProviderFactory factory = null;
	switch(provider)
	{
	   case "sqlclient":
		   factory = SqlClientFactory.Instance;
		   break;
	   case "oracleclient":
		   factory = OracleClientFactory.Instance;
		   break;
	}
	cnn = factory.CreateConnection();
	cmd = factory.CreateCommand();

	//now use cnn and cmd as usual to execute a query
}

Note, how we used Instance property of SqlClientFactory and OracleClientFactory classes to get an instace of corresponding factory class.

BinaryIntellect DatabaseHelper Open Source Component

BinaryIntellect DatabaseHelper is an open source component that utilizes above features of ADO.NET 2.0 to give you a generic data access layer. You can download the complete source code in C# and VB.NET athttp://www.binaryintellect.com/products/. The download includes a set of samples along with documentation. Here are some core features of the component:

Source code available in C# as well as VB.NET
Generic data access for all the four data providers of .NET
Developed using new Factory classes of ADO.NET 2.0
Very small amount of code needed from your side
Flexible and easy in use
Support for parameterized queries and stored procedures
Support for error handling
Support for error logging
Support for transactions
Samples included

Comparing BinaryIntellect DatabaseHelper with MSDAAB

It is possible that you are already using Microsoft Data Access Application Block as a data access layer and might be wondering how BinaryIntellect DatabaseHelper differs from MSDAAB. Though we will not discuss which one is better than the other we will see some main differences between the two.

Difference 1: Target database

MSDAAB is exclusively fro SQL Server databases. However, using BinaryIntellect DatabaseHelper you can write code for all the four data providers of .NET 2.0.

Difference 2: Overall Class Design

MSDAAB extensively uses static methods. There are around 9 overloads per method. As against that BinaryIntellect DatabaseHelper needs to be instantiated and there are four overloads per method.

Difference 3: Working with parameters

using MSDAAB you typically write code as shown below:

SqlParameter[] p=new SqlParameter[2];
p[0]=new SqlParameter("@fname","Bipin");
p[1]=new SqlParameter("@lname","Joshi");
SqlHelper.ExecuteNonQuery("your query here",p);

The problem with this code is that you have provider specific classes such as SqlParameter embedded in your calling code. Even if you decide to use interface based approach you will have parameter specific code in the caller. Let's see the equivalent code in BinaryIntellect DatabaseHelper:

DatabaseHelper db = new DatabaseHelper();
db.AddParameter("@fname", "Bipin");
db.AddParameter("@lname", "Joshi");
object obj=db.ExecuteScalar("your query here");

If you notice the above code it doesn't use any data provider classes in the caller. This means your calling code (such as business logic layer) is database independent.

Difference 4: Executing multiple queries

Many times you need to fire multiple queries at one go. For better performance you would like to held the connection open, fire required queries and then close the connection. MSDAAB allows you to do that but you need to supply the SqlConnection instance from the calling code. This again means that you have data provider classes in the business logic layer. As against this BinaryIntellect DatabaseHelper provides a simple approach. have at look at the following code:

DatabaseHelper db = new DatabaseHelper();
object o = db.ExecuteNonQuery
("query here",ConnectionState.KeepOpen);
object o = db.ExecuteNonQuery("query here");

You do not need to create any connection object in your code. Simply indicate whether you would like to keep the connection open via ConnectionState enumeration. If you do not specify this option the underlying connection is closed.

Difference 5: Working with transactions

MSDAAB expects you to supply the transaction object from your code. This again gives rise to the problem mentioned above. On the other hand BinaryIntellect DatabaseHelper allows much easier way to execute queries under a transaction.

DatabaseHelper db = new DatabaseHelper();
db.BeginTransaction();
int i = db.ExecuteNonQuery
("first query",ConnectionState.KeepOpen);
int j = db.ExecuteNonQuery
("second query", ConnectionState.KeepOpen);
db.CommitTransaction();

Simple. Isn't it?

Difference 6: Error handling

MSDAAB does not provide any error handling facility as such. BinaryIntellect DatabaseHelper on the other hand allows you to handle errors in the component itself. This can simplify your code.

DatabaseHelper db = new DatabaseHelper();
db.HandleErrors = true;
object obj = db.ExecuteScalar("query here");
Console.WriteLine(db.LastError);

Two properties - HandleErrors and LastError does the job for you.

Difference 7: Error logging

MSDAAB does not provide any facility to log database errors (which is a common requirement in many projects). BinaryIntellect allows you to log errors to a text file at a specified location.

DatabaseHelper db = new DatabaseHelper();
db.LogErrors = true;
db.LogFile=@"D:\Bipin\Errorlog.txt";
object obj = db.ExecuteScalar("query here");

Difference 8: Performance

Since MSDAAB uses classes specific to SQL Server, it will give you better performance as compared to generic classes used in BinaryIntellect DatabaseHelper. However, many times the performance difference will be very small.

Summary

ADO.NET 2.0 simplifies writing generic data access code with the help of common base classes and factory classes. If the application requirement calls for provider independence then these classes can significantly help you generalize your code.

Hello WinForms - An Introduction To Win Forms

What are Win Forms?

In earlier versions of VB and other Visual Studio products there were different 'Forms' engines. So forms developed using VB were different than other languages. With Visual Studio.NET the picture has changed. Now, all the tools supporting NGWS make use of a common forms engine. The forms thus created are called as Win Forms. This leads to many benefits (also refer VS.NET documentation for more details):

Common look and feel
Added functionality ( Yes! VB forms can now have scrollbars!)
Significantly reduces control-container interoperability issues.
Win Forms takes full advantage of the security features of the NGWS runtime.
Win Forms offers support to Web Services.
Win Forms provides rich graphics with the help of GDI+, a new version of the Windows Graphical Device Interface
Win Forms offers a rich set of controls
Win Forms offers full support for the ADO+ data model.
Win Forms offers support for ActiveX controls.
Win Forms takes advantage of the NGWS Runtime enhanced licensing model.
Win Forms offers a printing framework that enables applications to provide comprehensive reports.

First WinForms application

Now, let us create a simple form which will just display "Hello Win Forms" This is very simple example but illustrates general structure of VB.NET classes for displaying forms.

   Imports System
   Imports System.WinForms

   Namespace Bipin.Samples

       Public Class HelloWinForms
       Inherits System.WinForms.Form

           Shared Sub Main()
               Application.Run(New HelloWinForms())
           End Sub

           Public Sub New()
               MyBase.New
               Me.Text = "Hello Win Forms"
           End Sub

       End Class

   End Namespace

Note the following points :

We have imported namespaces called System and Systems.WinForms. The later contains all the controls (like label, button etc) we will be using next.
We declared our own namespace called Bipin.Samples
The namespace contains a class called HelloWinForms which is inherited from Form class
In VB.NET constructors are represented by a method named New(). This method calls constructor of the base class (mybase.New) and sets title of the form to "Hello Win Forms"
An instance of the form is created in Shared (which is nothing but Static in C++/Java) method called Main()

Compiling the application

You can compile the application using command line compiler vbc

vbc file_name /r:System.WinForms.dll /r:System.Drawing.dll

Here, file name is the name of source file i.e. xxxx.vb. The switch /r points namespaces [r]eferenced in the application.

Adding Controls to the Form

Now, we will see how to add controls to the form

   Imports System
   Imports System.WinForms
   Imports System.Drawing

   Namespace Bipin.Samples

       Public Class HelloWinForms
       Inherits System.WinForms.Form

			Dim label1 as new label

           Shared Sub Main()
               Application.Run(New HelloWinForms())
           End Sub

           Public Sub New()
               MyBase.New
               Me.Text = "Hello Win Forms"
               label1.text="Hello Win Forms"
               label1.location=new point(100,100)
               me.controls.add(label1)
           End Sub

       End Class

   End Namespace

We have created an instance of label class called label1
The newly created label is added to the form in the constructor using me.add method of the form
Then we set some properties like location and text of the control. Note that point class is present in System.Drawing namespace, hence we import it.
Also, note that you can set properties of the control at any time - before adding to the form as well as after adding to the form

Event handling

Now, let us proceed further and see how to handle events of the controls.

   Imports System
   Imports System.WinForms
   Imports System.Drawing

   Namespace Bipin.Samples

       Public Class HelloWinForms
       Inherits System.WinForms.Form

		   Dim button1 as new Button

           Shared Sub Main()
               Application.Run(New HelloWinForms())
           End Sub

           Public Sub New()
               MyBase.New
               Me.Text = "Hello WinForms"
               button1.text="Click Me"
               button1.location=new point(100,100)
               button1.addonclick(addressof button_click)
               me.controls.add(button1)
           End Sub

           public sub button_click
           (sender as object,evt as eventargs)
	           	Messagebox.show("Hello Win Forms")
           end sub

       End Class

   End Namespace

This example is similar to previous one but uses a command button. When user clicks on the button a message box will be displayed saying "Hello Win Forms" Here is how we handled click event of the button :

To indicate that you want to handle certain event you must register a sub or function with that event (Java people will find themselves at home!)
This is done using AddOnxxxxx (source as object, evt as eventargs) syntax where xxxxx represents the event name (Click in our case)
This method also requires address of the function/sub which handles the event (button_click in our case)
The event handling must match syntax shown i.e first argument of type object and second of type eventargs. In some cases (like mouse events) extra information is passed to the event handler via this second argument
Note that now you can use same function to handle events from multiple controls
We have displayed message box using NGWS Messagebox.show() method. If you want to use old VB methods/functions you need to import Microsoft.VisualBasic in your application

I hope you must have got overall idea about win forms. The next articles of this series will cover techniques like creating menus and using other controls. So, visit soon!

Developing Data Navigation Control (VCR) For Windows Forms

Your comment on this post:

Related Articles