Introduction
This is a beginner's introduction to using data classes in ASP.NET. I have tried to keep it as simple as possible while introducing the concepts. To that end, there is neither client-side nor server-side input validation for example, I have just concentrated on relevant code to the subject. I have written it in VB.NET because I think novices will find it easier to translate from that to C#.NET than the other way round, should they need to. The sample web application project attached, built in Visual Studio 2008, references a SQLite database, as this only requires a reference to System.Data.SQLite.dll, but of course can easily be adapted for any other.
Background
Why Use Data Classes - Why this Article?
It pains me to see code where a datagrid, for example, is bound to a database and columns are bound using something like:
<%# Container.DataItem("columnName") %>
Apart from anything else, this is prone to typo errors, and is anyway time-consuming to type. A major advantage of using classes is that intellisense kicks in, and does half your work for you. (Assuming you’re using an IDE that supports Intellisense, that is.) And your code will be cleaner and much easier to read, and maintain. Once you get to grips with them (and they aren’t hard), you will find your productivity will rocket, and coding (even more) enjoyable. I’ve never found overly verbose explanations too much help – far better, in my experience, to see some code.
To work then...
Using the Code
The sample project manages the names and dates of birth of ... some group. So we’ll start with a simple database structure:
Table name: tblPeople
Columns:
ID (integer, autoincrement)
sFirstName (text)
sLastName (text)
dDOB (date, possibly NULL if not known.)
(It's a good idea to prefix field names with something that indicates their type – e.g. ‘s’ for a String, ‘i’ for an Integer, etc.)
The sample project will create this when first run, and populate it with a few rows.
So that’s our database. Now, in our project, we declare a class reflecting this database table structure: (see classes.vb in the download project.)
Friend Class clsPeople
Private _ID As Integer
Public Property ID() As Integer
Set(ByVal value As Integer)
_ID = value
End Set
Get
Return _ID
End Get
End Property
Private _sLastName As String
Public Property sLastName() As String
Set(ByVal value As String)
_sLastName = value
End Set
Get
Return _sLastName
End Get
End Property
Private _sFirstName As String
Public Property sFirstName() As String
Set(ByVal value As String)
_sFirstName = value
End Set
Get
Return _sFirstName
End Get
End Property
Private _dDOB As Nullable(Of Date)
Public Property dDOB() As Nullable(Of Date)
Set(ByVal value As Nullable(Of Date))
_dDOB = value
End Set
Get
Return _dDOB
End Get
End Property
Public Sub New()
_ID = 0
_sLastName = ""
_sFirstName = ""
_dDOB = Nothing
End Sub
This defines our class structure, in which we can store (Set) and retrieve (Get) data records.
If we now want to retrieve all the records in our database and display them in a datagrid, we can use the following method: (see adp.vb)
Friend Function collPeople() As Collection
Dim coll As New Collection
Dim cls As clsPeople
Dim objCmd As New SQLiteCommand
Dim objReader As SQLiteDataReader
Try
openDbConn()
objCmd.Connection = dbCon.thisConn
objCmd.CommandText = "select ID,sLastName,sFirstName,dDOB from tblPeople order by sLastName"
objReader = objCmd.ExecuteReader
If objReader.HasRows Then
Do While objReader.Read
cls = New clsPeople
cls.ID = objReader.GetInt32(0)
cls.sLastName = objReader.GetString(1)
cls.sFirstName = objReader.GetString(2)
If Not IsDBNull(objReader.GetValue(3)) Then cls.dDOB = CDate(objReader.GetValue(3))
coll.Add(cls)
Loop
End If
objReader.Close()
closeDbConn()
Return coll
Catch ex As Exception
closeDbConn()
WriteLog("colPeople", ex.Message)
Return Nothing
End Try
End Function
This function returns a collection of our class objects, each one set to a record in the database.
Now, we bind this in our web form like so:
On the HTML page (default.aspx):
<asp:DataGrid runat="server" ID="dgX" DataKeyField="ID" AutoGenerateColumns="false" ShowFooter="true" AllowPaging="true" PageSize="10">
<PagerStyle Mode="NumericPages" Position="TopAndBottom" />
<HeaderStyle BackColor="#e0e0e0" />
<FooterStyle BackColor="#f0f0f0" />
<Columns>
<asp:BoundColumn DataField="ID" Visible="False" ReadOnly="true"></asp:BoundColumn>
<asp:TemplateColumn HeaderText="Name" ItemStyle-Width="400px">
<ItemTemplate><asp:Literal runat="server" ID="sName"></asp:Literal></ItemTemplate>
<EditItemTemplate>
<asp:TextBox runat="server" ID="sLastName" _
Width="190px" MaxLength="50"></asp:TextBox>
<asp:TextBox runat="server" ID="sFisrtName" _
Width="190px" MaxLength="50"></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox runat="server" ID="sLastName" _
Width="190px" MaxLength="50" _
Text="(Last name)"></asp:TextBox>
<asp:TextBox runat="server" ID="sFisrtName" _
Width="190px" MaxLength="50" _
Text="(First name)"></asp:TextBox>
</FooterTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="D.O.B." _
ItemStyle-Width="120px">
<ItemTemplate><asp:Literal runat="server" _
ID="sDOB"></asp:Literal></ItemTemplate>
<EditItemTemplate><asp:TextBox runat="server" _
ID="sDOB" Width="110px" _
MaxLength="11"></asp:TextBox></EditItemTemplate>
<FooterTemplate><asp:TextBox runat="server" _
ID="sDOB" Width="110px" _
MaxLength="11"></asp:TextBox></FooterTemplate>
</asp:TemplateColumn>
<asp:EditCommandColumn ButtonType="LinkButton" _
CancelText="cancel" EditText="edit" _
UpdateText="update" ItemStyle-Width="120px" _
ItemStyle-HorizontalAlign="Center"></asp:EditCommandColumn>
<asp:TemplateColumn ItemStyle-Width="50px" _
ItemStyle-HorizontalAlign="Right">
<ItemTemplate><asp:LinkButton runat="server" _
ID="lbDelete" CommandName="delete" _
Text="delete"></asp:LinkButton></ItemTemplate>
<FooterTemplate><asp:LinkButton runat="server" _
ID="lbAdd" CommandName="insert" _
Text=" add"></asp:LinkButton></FooterTemplate>
</asp:TemplateColumn>
</Columns>
</asp:DataGrid>
Of course, we could use bound columns for all the bindings, but I want to show the usefulness of data classes in the code behind...
In the code behind, we simply bind our collection to the database:
Dim coll As Collection = adp.collPeople()
If Not coll Is Nothing Then
dgX.DataSource = coll
dgX.DataBind()
dgX.PagerStyle.Visible = CBool(coll.Count > dgX.PageSize)
End If
And in the ItemDataBound event:
Private Sub dgX_ItemDataBound(ByVal sender As Object, _
ByVal e As System.Web.UI.WebControls.DataGridItemEventArgs) Handles dgX.ItemDataBound
Select Case e.Item.ItemType
Case ListItemType.AlternatingItem, ListItemType.Item
Dim cls As clsPeople = CType(e.Item.DataItem, clsPeople)
Dim sName As Literal = CType(e.Item.FindControl("sName"), Literal)
Dim sDOB As Literal = CType(e.Item.FindControl("sDOB"), Literal)
Dim btnDel As LinkButton = e.Item.Cells(4).Controls(0)
sName.Text = cls.sLastName.ToUpper & ", " & cls.sFirstName
If cls.dDOB Is Nothing Then
sDOB.Text = "(unknown)"
Else
sDOB.Text = String.Format("{0:dd-MMM-yyyy}", CDate(cls.dDOB))
End If
btnDel.Attributes.Add("onclick", "return confirm('Are you sure you want to delete this item?')")
Case ListItemType.EditItem
Case ListItemType.Footer
Case Else
End Select
End Sub
In such a simple example as this, there may not seem much advantage to using data classes, but imagine a more complex data structure, and a more complex UI requirement, and you can quickly see how writing this binding code becomes much easier – bear in mind that Visual Studio’s intellisense will recognise your data class, and as soon as you type...
cls.
... you will be presented with a list of all the class properties.
Similarly, when adding or updating records, we simply declare a new clsPeople and set the class properties accordingly (equal to the value of text boxes on our form, for example) and then pass this class to an add or update function: e.g.:
Friend Function AddPeople(ByVal cls As clsPeople) As Integer
Dim objCmd As New SQLiteCommand
Try
openDbConn()
objCmd.Connection = dbCon.thisConn
objCmd.CommandText = "insert into tblPeople (sLastName,sFirstName,dDOB) values (?,?,?)"
objCmd.Parameters.AddWithValue("@sLastName", cls.sLastName)
objCmd.Parameters.AddWithValue("@sFirstName", cls.sFirstName)
If cls.dDOB Is Nothing Then
objCmd.Parameters.AddWithValue("@dDOB", DBNull.Value)
Else
objCmd.Parameters.AddWithValue("@dDOB", cls.dDOB)
End If
objCmd.ExecuteNonQuery()
objCmd.Parameters.Clear()
objCmd.CommandText = "select last_insert_rowid()"
cls.ID = objCmd.ExecuteScalar
closeDbConn()
Return cls.ID
Catch ex As Exception
closeDbConn()
WriteLog("AddPeople", ex.Message)
Return 0
End Try
End Function
Points of Interest
I cannot stress enough how much doing things this way will make your life easier. Readability, maintainability, and ease of coding will all contribute to enabling you to turn projects around in record time!
History
