Data Form Wizard for ASP.NET with only 3 lines of code

• Download source files - 42.5 KB
• Download demo project - 158.17 KB

Introduction

Since VS.NET has come with the great tool of Data Form Wizard, I thought why not have a similar one not only in Windows Forms but also in ASP.NET.

As both Windows apps and ASP.NET apps use the same ADO.NET classes, it's possible and great to make such a tool in ASP.NET.

One important thing we took care about is that it can go fine and smoothly using DataSet in making such a control for Windows app usage but for Web apps, the best practice is to use DataReaders, so it was really an exhausting work because readers are read only, forward only.

Two years ago, I've started writing such a control; really it was so long a task but I think finally the output was good and fair for our usage of such a tool we made.

Background

But after two months (in 2002) of our work with our data form control, and after the project has been finished, the great book "Unleashed ASP.NET" of Stephen Walther was published, and while reviewing the book, I found that the author has made the same control but with a lot of enhancements, and really it was great and better than mine. It has a lot of features and really well customized even more than the Data Form Wizard of Microsoft VS.NET for Windows apps, and it uses DataReaders not DataSets, so it was really light.

I thought that there is no need to publish an article that talks about that control because sure Microsoft will do the same or better one with the new VS.NET "Whidbey". But after I'd reviewed the new features of the ASP.NET V.2.0 "Whidbey", I was disappointed because Microsoft didn't make it! So I saw it'll be useful to publish such an article.

The control itself is a set of controls that's inherited from the System.Web.UI.WebControls classes, and it's divided into two sets:

1. Set of controls dealing with SQL Server.
2. Set of OleDB controls that can deal with any such engine.

I've experienced the SQL one, it was really great with only some bugs that can be overcome. But regarding the OleDB one, I've tried it with Oracle 9i but it didn't work because of a bug in receiving the connection string. (I hope it'll be fixed sooner.)

Using the code

I've made a sample that shows how to use this great control and overcome its bugs perfectly.

1. First, open the VS.NET and choose new ASP.NET Web project.
2. Right click on the tool box and choose Add\Remove Items, go to the bin folder of the attached project folder, and then choose Superexpert.DataForm.dll that will add the two sets of controls of the DataForm.

   As you see, the OleDB are the green ones and the SQL are in red.

3. First, start with picking the SqlDataPanel on which you will be able to add all the controls you want (as shown in the previous image).
4. Set the SqlDataPanel properties to make it point to the required table and key field as shown in the image below:

Tip:

Don't try to set the connection string from the design mode. I've tried that but it didn't work, so make it better in the page load handler. (My connection string is located in the web.config and points to the famous Northwind database of SQL Server.)

VB.NET

SqlDataPanel1.Connection = New _
  SqlConnection(System.Configuration._
  ConfigurationSettings.AppSettings("ConnStr"))

5. Pick the SqlDataNavigator control which contains the four navigation buttons, and then attach the navigator to the SqlDataPanel by writing this line of code in the page load event handler:
   VB.NET

   SqlDataNavigator1.ControlToNavigate = Me.SqlDataPanel1

   Also, you should attach the same connection string to the navigator control in the page load handler:

   VB.NET

   SqlDataNavigator1.Connection = Me.SqlDataPanel1.Connection

6. Put all the controls you will use in this form, but from the listed sets. For e.g.:

   If you want to display a field in a TextBox control, then you have to choose the DataTextBox control instead and put it on your SqlDataPanel.

7. Two properties for each data control needed to be set:
   • DataField: holds the name of the bounded field.
   • DataType: holds the data type of the bounded field.

8. Run the application and you will see that you can navigate through the Employees table of the Northwind so smoothly and fast.

Hint:

If you downloaded the control directly from Stephen's web site, it'll show you an evaluation message, the attached one in this article doesn't show any evaluation messages.

9. Stephen's Control has Add record, Update and Delete buttons, but after trying, I found that they don't work, so you have to handle each of them manually as you deal with regular buttons, as shown in this sample:

And here is all the code for handling this example:

Private Sub Page_Load(ByVal sender As System.Object, _
         ByVal e As System.EventArgs) Handles MyBase.Load

    ' Put user code to initialize the page here

    ' Passing the Data Panel to the navigator coz it'll not

    ' work from design mode properties (bug)


    SqlDataNavigator1.ControlToNavigate = Me.SqlDataPanel1

    ' also passing the connection string from web.config

    ' becoz passing it from the design mode will not work (bug)


    SqlDataPanel1.Connection = New _
      SqlConnection(System.Configuration.ConfigurationSettings.AppSettings(_
                                                                 "ConnStr"))

    'and finally passing it to the navigator too.

    SqlDataNavigator1.Connection = Me.SqlDataPanel1.Connection
End Sub

'Handling the add record btn

Private Sub DataAddButton1_Click(ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles DataAddButton1.Click

    Dim insertStr As String = "insert into Employees" & _ 
          " (LastName,FirstName,Title,City,Country,HomePhone,Notes) values('"_ 
          + Me.LastName_Txt.Text + "','" + Me.FirstName_Txt.Text + _
          "','" + Me.Title_Txt.Text + "', '" + Me.City_Txt.Text + _
          "','" + Me.Cntry_Txt.Text + "','" + Me.HomePhone_Txt.Text + _
          "','" + Me.Notes_Txt.Text + "')"

    Dim conn As New _
      SqlConnection(System.Configuration.ConfigurationSettings.AppSettings(_
                                                                  "ConnStr"))
    Dim cmd As New SqlCommand(insertStr, conn)

    Try
      conn.Open()
      cmd.ExecuteNonQuery()
    Finally
      conn.Close()
    End Try
End Sub
 
'Handling the delete btn

Private Sub Delete_Btn_Click(ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles Delete_Btn.Click

    Dim delStr As String = _
       "delete from Employees where EmployeeID='" + _
       Me.EmpID_Txt.Text + "'"

    Dim conn As New _
      SqlConnection(System.Configuration.ConfigurationSettings.AppSettings(_
                                                                  "ConnStr"))

    Dim cmd As New SqlCommand(delStr, conn)

    Try
      conn.Open()
      cmd.ExecuteNonQuery()
    Finally
      conn.Close()
    End Try
End Sub
 
'Handling the update btn


Private Sub DataUpdateButton1_Click(ByVal sender As System.Object, _
   ByVal e As System.EventArgs) Handles DataUpdateButton1.Click

   Dim updateStr As String = "update Employees set LastName='" + _
                             Me.LastName_Txt.Text + "',FirstName='" + _
                             Me.FirstName_Txt.Text + "',Title='" + _
                             Me.Title_Txt.Text + "',City='" + _
                             Me.City_Txt.Text + "',Country= '" + _
                             Me.Cntry_Txt.Text + "',HomePhone='" + _
                             Me.HomePhone_Txt.Text + "',Notes='" + _
                             Me.Notes_Txt.Text + "' where EmployeeID='" + _
                             Me.EmpID_Txt.Text + "'"

    Dim conn As New _
      SqlConnection(System.Configuration.ConfigurationSettings.AppSettings(_
                                                                  "ConnStr"))

    Dim cmd As New SqlCommand(updateStr, conn)

    Try
      conn.Open()
      cmd.ExecuteNonQuery()
    Finally
      conn.Close()
    End Try
End Sub