The Simplest CRUD Operations for Lookup Tables in ASP.NET

---

A downloadable file for this article is available here.

---

The Design

In general, every application needs a few lookup tables.  These lookup tables are also considered "system parameters" by many developers.  It is not a hurdle at all to write CRUD (Create, Read, Update, Delete) operations using ASP.NET.  But there exist too many methods for implementing these operations. In this article, I shall introduce you to the fastest template to include CRUD operations, which is very suitable for lookup tables. 

In my sample, I actually worked with existing "flow" and "grid" layouts. It may not be suitable for certain browsers.  If you want extreme browser compatibility, I suggest you to modify the screen design with HTML tables and panels. 

My sample mainly contains a datagrid and a "server grid layout" control (with few buttons to operate).  All of them have been pushed into a flow layout control for  intelligent maintenance of space on the web page.  The datagrid is equipped with two added columns as specified below:

  <Columns>
    <asp:ButtonColumn  Text="&lt;img border=0 
      src='images/icon_edit.gif'&gt;" CommandName="OnEdit">
       </asp:ButtonColumn>

    <asp:ButtonColumn  Text="&lt;img border=0
      src='images/icon_Delete.gif'&gt;"  CommandName="OnDel">
       </asp:ButtonColumn>
  </Columns>

From the above you can understand that I simply added two Button Columns to the data grid to work with "edit" and "delete" operations for the respective row.  I am getting the connection string from the web.config using the following statement in the class SQLHelper.

  Dim _ConnectionString As String =
    System.Configuration.ConfigurationSettings.AppSettings.Get
    ("ConnectionString")

For this demonstration, I simply modified the following to the Web.config to maintain the database connection string:

  <?xml version="1.0" encoding="utf-8" ?>
  <configuration>
    <appSettings>
    <add key="ConnectionString" value="Data Source=.;Initial
Catalog=Northwind;User Id=sa" />
    </appSettings>
  <system.web>
.
.
.

The SQLHelper

This is a simple encapsulated class I added for my convenience. It mainly handles database related communications. Actually, you can extend it further according to your requirements.

The first method inside the SQLHelper class is as follows:

    Public Overloads Sub SQLExecute(ByVal strSQL As String)
        Dim ConnAs SqlConnection
        Dim cmd As SqlCommand
        Try
            Conn= New SqlConnection(_ConnectionString)
            cmd = New SqlCommand(strSQL, Conn)
            With cmd
                .Connection.Open()
                .ExecuteNonQuery()
                .Connection.Close()
                .Dispose()
            End With

        Catch ex As Exception
            Try
                If cmd.Connection.State = ConnectionState.Open
Then
                    cmd.Connection.Close()
                    cmd.Dispose()
                End If
            Catch e As Exception
                'do nothing...if still error persists
            End Try
            Throw New Exception(ex.Message & ". SQL Statement: "
& strSQL)
        End Try
    End Sub

The above method will simply try to execute any SQL statement (except SELECT).  This can be generally used for any DML or DDL operations.  Proceeding further, we have the following:

  PublicFunction getDataTable(ByVal sqlSELECT As String) As
System.Data.DataTable
        Dim ConnAs SqlConnection
        Dim da As SqlDataAdapter
        Try
            Conn= New SqlConnection(_ConnectionString)
            Dim dt As New DataTable

            da = New SqlDataAdapter(sqlSELECT, Conn)
            da.Fill(dt)
            da.Dispose()
            Return dt

        Catch ex As Exception
            Try
                da.Dispose()
            Catch e As Exception
                'do nothing...if still error persists
            End Try
            Throw New Exception(ex.Message & ". SQL Statement: "
& sqlSELECT)
        End Try
    End Function

The above is mainly used to retrieve a set of rows from the database.

The SQLHelper: continued

This is the successive part from the previous section.  Proceeding further, we have the following:

  PublicFunction getDataRow(ByVal sqlSELECT As String) As
System.Data.DataRow
        Dim da As SqlDataAdapter
        Try
            Dim dt As New DataTable
            da = New SqlDataAdapter(sqlSELECT, _ConnectionString)
            da.Fill(dt)
            da.Dispose()
            If dt.Rows.Count = 0 Then Return Nothing Else Return
dt.Rows(0) 'return only first row
        Catch ex As Exception
            Try
                da.Dispose()
            Catch e As Exception
                'do nothing...if still error persists
            End Try
            Throw New Exception(ex.Message & ". SQL Statement: "
& sqlSELECT)
        End Try
    End Function

The above is mainly used to get a single row from the database.  Proceeding further, we have the following:

  PublicFunction getRowValue(ByVal sqlSELECT As String) As String
        Dim ConnAs SqlConnection

        Dim cmd As SqlCommand
        Dim value As String = ""
        Try
            Conn= New SqlConnection(_ConnectionString)

            cmd = New SqlCommand(sqlSELECT, Conn)
            With cmd
                .Connection.Open()
                value = .ExecuteScalar() & "" 'concatenating an
empty string..to eliminate null or nothing
                .Connection.Close()
                .Dispose()
            End With
            Return value
        Catch ex As Exception
            Try
                If cmd.Connection.State = ConnectionState.Open
Then
                    cmd.Connection.Close()
                    cmd.Dispose()
                End If
            Catch e As Exception
                'do nothing...if still error persists
            End Try
            Throw New Exception(ex.Message & ". SQL Statement: "
& sqlSELECT)
        End Try
    End Function

The above method is mainly used to retrieve a single value from the database. To use all of the above methods, simply declare the following line, just before any page load event (or in a global module):

  Dim db As New SQLHelper

Retrieving information from the database

To retrieve information from the database onto the grid, you try to define a simple method something like the following:

  PrivateSub BindGrid()
        Me.DataGrid1.DataSource = db.getDataTable("select * from
dept")
        Me.DataGrid1.DataBind()
    End Sub

The above would simply retrieve all of the information from the table "dept" and assign it to "datagrid."  When any button in the datagrid is hit, it must execute the respective method. The following code will do that based on the definitions of the columns you added to the datagrid (please refer to the first section).

  PrivateSub DataGrid1_ItemCommand(ByVal source As Object, ByVal
e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles
DataGrid1.ItemCommand
        Dim DeptId As String = e.Item.Cells(2).Text
        Select Case e.CommandName
            Case "OnEdit"
                ShowEdit(DeptId)
            Case "OnDel"
                Delete(DeptId)
        End Select
    End Sub

When you click on "edit" or "delete," you must find the "departmentID" present in the row on which the user clicked.  The following statement would give you the same:

        Dim DeptId As String = e.Item.Cells(2).Text

You must make sure that the "CommandName" you specify in the above code matches with the columns defined earlier.

Dealing with DML operations

I included a button with the caption "Add."  Actually, it does nothing other than clear the values existing in all the controls.  Let us go through the following code:

  PrivateSub btnAdd_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles btnAdd.Click
        Me.txtDeptno.Text = ""
        Me.txtDname.Text = ""
        Me.txtLocation.Text = ""
        Me.txtDeptno.Enabled = True
        Me.frDept.Visible = True
    End Sub

The above simply clears all controls and makes the "editing layout" visible so that you can enter a new record.  When the user clicks on "edit" on the datagrid, it will invoke the following method:

  PrivateSub ShowEdit(ByVal DeptID As String)
        Dim dr As DataRow = db.getDataRow("select * from dept
where deptno=" & DeptID)
        If dr Is Nothing Then
            Me.lblMsg.Text = "Dept Not found"
            Me.lblMsg.Visible = True
            Exit Sub
        End If
        Me.txtDeptno.Text = dr("Deptno")
        Me.txtDeptno.Enabled = False
        Me.txtDname.Text = dr("Dname")
        Me.txtLocation.Text = dr("Loc")
        Me.frDept.Visible = True
    End Sub

You can understand that the above simply retrieves a record from the database and displays the same on the screen.  The button "Cancel" is provided with the following code:

  PrivateSub btnCancel_Click(ByVal sender As System.Object, ByVal
e As System.EventArgs) Handles btnCancel.Click
        Me.frDept.Visible = False
        Me.lblMsg.Visible = False
    End Sub

The above method simply hides the "editing layout" and existing error messages.

Dealing with DML Operations: continued

The heart of the application is the "save" button.  Let us go through the following code first:

  PrivateSub btnSave_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles btnSave.Click
        If Not Me.txtDeptno.Enabled Then 'edit mode
            Try
                'update it
                db.SQLExecute("update dept set dname='" &
Me.txtDname.Text & "', loc='" & Me.txtLocation.Text & "' where
deptno=" & Me.txtDeptno.Text)
            Catch ex As Exception 'any other error
                Me.lblMsg.Text = ex.Message
                Me.lblMsg.Visible = True
                Exit Sub
            End Try
        Else 'add new mode
            'validation for unique deptno
            If db.getRowValue("select 'exists' from dept where
deptno=" & Me.txtDeptno.Text) = "exists" Then
                Me.lblMsg.Text = "Dept already exists"
                Me.lblMsg.Visible = True
                Exit Sub
            End If
            'proceeding to insert
            Try
                db.SQLExecute("insert into dept
(deptno,dname,loc) values (" & Me.txtDeptno.Text & ",'" &
Me.txtDname.Text & "','" & Me.txtLocation.Text & "')")
            Catch ex As Exception 'any other error
                Me.lblMsg.Text = ex.Message
                Me.lblMsg.Visible = True
                Exit Sub
            End Try
        End If
        'after successfully saving...update the grid
        Me.frDept.Visible = False
        BindGrid()
    End Sub

The above method simply separates every operation for ease in understanding.  Each case works with a separate DML option.  The above includes validation along with the operations.

Points to note

Even though I didn't talk about "delete" in the above sections, it is worth mentioning.  Actually, deleting a record is as simple as including the following:

  PrivateSub Delete(ByVal DeptID As String)
        db.SQLExecute("delete from dept where deptno=" & DeptID)
        BindGrid()
    End Sub

I included the following JavaScript to add confirmation for the delete operation.

  <script  language="javascript">
            function getConfirm() 
            { 
                  if (confirm("Are you sure to delete record?")
==true) 
                        return true; 
                  else
                        return false; 
            }
            </script>

To activate the above script, I included the following:

  PrivateSub DataGrid1_ItemDataBound(ByVal sender As Object,
ByVal e As System.Web.UI.WebControls.DataGridItemEventArgs)
Handles DataGrid1.ItemDataBound
        Select Case e.Item.ItemType
            Case ListItemType.Item, ListItemType.AlternatingItem
                e.Item.Cells(1).Attributes.Add("onclick", "return
getConfirm();")
        End Select
    End Sub

To work with my sample, I simply created a table "dept" using the following command:

  CREATE TABLE [dbo].[dept] (
      [deptno] [int] NOT NULL ,
      [dname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
      [loc] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
  ) ON [PRIMARY]

Try to insert some rows into the above table using following commands:

  insert into dept values (10,'Accounting','Dallas')

  insert into dept values (20,'Production','Washington')

The entire code for this article is freely available in the form of a zip file.  That downloadable solution was developed using Microsoft Visual Studio 2003 Enterprise Architect together with SQL Server 2000 on Microsoft Windows 2003 Standard Edition. I didn't really test it in any other version. 

Any doubts, bugs, errors, suggestions, feedback etc. are highly appreciated at jag_chat@yahoo.com.

blog comments powered by Disqus