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:
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 AsString = System.Configuration.ConfigurationSettings.AppSettings.Get ("ConnectionString")
For this demonstration, I simply modified the following to the Web.config to maintain the database connection string:
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:
PublicOverloadsSub SQLExecute(ByVal strSQL AsString) DimConnAs SqlConnection Dim cmd As SqlCommand Try Conn= New SqlConnection(_ConnectionString) cmd = New SqlCommand(strSQL, Conn) With cmd .Connection.Open() .ExecuteNonQuery() .Connection.Close() .Dispose() EndWith
Catch ex As Exception Try If cmd.Connection.State = ConnectionState.Open Then cmd.Connection.Close() cmd.Dispose() EndIf Catch e As Exception 'do nothing...if still error persists EndTry ThrowNew Exception(ex.Message & ". SQL Statement: " & strSQL) EndTry EndSub
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 AsString) As System.Data.DataTable DimConnAs SqlConnection Dim da As SqlDataAdapter Try Conn= New SqlConnection(_ConnectionString) Dim dt AsNew 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 EndTry ThrowNew Exception(ex.Message & ". SQL Statement: " & sqlSELECT) EndTry EndFunction
The above is mainly used to retrieve a set of rows from the database.
This is the successive part from the previous section. Proceeding further, we have the following:
PublicFunction getDataRow(ByVal sqlSELECT AsString) As System.Data.DataRow Dim da As SqlDataAdapter Try Dim dt AsNew DataTable da = New SqlDataAdapter(sqlSELECT, _ConnectionString) da.Fill(dt) da.Dispose() If dt.Rows.Count = 0 ThenReturnNothingElseReturn dt.Rows(0) 'return only first row Catch ex As Exception Try da.Dispose() Catch e As Exception 'do nothing...if still error persists EndTry ThrowNew Exception(ex.Message & ". SQL Statement: " & sqlSELECT) EndTry EndFunction
The above is mainly used to get a single row from the database. Proceeding further, we have the following:
Dim cmd As SqlCommand Dim value AsString = "" 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() EndWith Return value Catch ex As Exception Try If cmd.Connection.State = ConnectionState.Open Then cmd.Connection.Close() cmd.Dispose() EndIf Catch e As Exception 'do nothing...if still error persists EndTry ThrowNew Exception(ex.Message & ". SQL Statement: " & sqlSELECT) EndTry EndFunction
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):
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() EndSub
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 AsObject, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles DataGrid1.ItemCommand Dim DeptId AsString = e.Item.Cells(2).Text SelectCase e.CommandName Case "OnEdit" ShowEdit(DeptId) Case "OnDel" Delete(DeptId) EndSelect EndSub
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 AsString = e.Item.Cells(2).Text
You must make sure that the "CommandName" you specify in the above code matches with the columns defined earlier.
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 EndSub
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 AsString) Dim dr As DataRow = db.getDataRow("select * from dept where deptno=" & DeptID) If dr IsNothingThen Me.lblMsg.Text = "Dept Not found" Me.lblMsg.Visible = True ExitSub EndIf Me.txtDeptno.Text = dr("Deptno") Me.txtDeptno.Enabled = False Me.txtDname.Text = dr("Dname") Me.txtLocation.Text = dr("Loc") Me.frDept.Visible = True EndSub
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 EndSub
The above method simply hides the "editing layout" and existing error messages.
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 IfNotMe.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 ExitSub EndTry 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 ExitSub EndIf '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 ExitSub EndTry EndIf 'after successfully saving...update the grid Me.frDept.Visible = False BindGrid() EndSub
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.
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 AsString) db.SQLExecute("delete from dept where deptno=" & DeptID) BindGrid() EndSub
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) returntrue; else returnfalse; } </script>
To activate the above script, I included the following:
PrivateSub DataGrid1_ItemDataBound(ByVal sender AsObject, ByVal e As System.Web.UI.WebControls.DataGridItemEventArgs) Handles DataGrid1.ItemDataBound SelectCase e.Item.ItemType Case ListItemType.Item, ListItemType.AlternatingItem e.Item.Cells(1).Attributes.Add("onclick", "return getConfirm();") EndSelect EndSub
To work with my sample, I simply created a table "dept" using the following command:
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.