One thing that ASP.NET is not short of is ways of accessing data. In this article we’ll look at three of these, where they differ and the circumstances in which you can make most effective use of them.
The SQLDataSource is an ASP.NET control that is designed to allow data access from within ASP.NET pages without needing to directly deal with ADO.NET classes. The Datareader provides a quick and convenient method of retrieving data from a database, and the DataSet object provides a rich and sophisticated environment that effectively allows the creation of in memory versions of database objects.
Each object has it’s own advantages and disadvantages. So let’s take a look at them in turn. Just to complicate matters, the SQLDataSource can be configured to return DataSet or DataReader objects, as well as link directly to ASP.NET controls.
The examples here will use the simple database table used for the SQLDataSource articles previously published. Just note as well that for simplicity the returned results are rendered before the HTML tags, obviously with a live example you would have to add the returned results within the body element.
As this has been described previously, all I will do here is summarise it’s use outside the control-populating function we’ve seen previously. The default object used to return data from the database is the DataSet, but this is ‘hidden’ within the SQLDataSource object.
In the below listing, note that we do not add the control to the web page; we run it within VB.NET script and simply print the contents of the field chosen to the web page using Response.write().
<%@ Page Language="VB" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
Dim dsNames As SqlDataSource
Protected Sub Page_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Init
dsNames = New SqlDataSource
' First of all get the connection string from the web.config file and assign it to the SQLDataSource
Dim sConnString As String
sConnString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings("Demo-SQL").ConnectionString
dsNames.ConnectionString = sConnString
' Now configure the SELECT statement.
dsNames.SelectCommand = "SELECT [ID], [FirstName], [LastName] FROM [names] "
' Now go through each datarow within the DataSet returned as default by the
' SQLDataSource.
Response.Write("<h1>Firstnames</h1>")
' The DataSourceSelectArguments can normally be passed as an Empty collection
' The collection specifies additional parameters - not SelectParameters, but
' more to do with the Data Source - is needed.
For Each datarow As Data.DataRowView In dsNames.Select(DataSourceSelectArguments.Empty)
Response.Write(datarow("FirstName").ToString() + "<br />")
Next
End Sub
</script>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
</div>
</form>
</body>
</html>
The main issue with using the SQLDataSource control against using the individual DataSet or DataReader objects is one of ‘weight’. The SQLDataSource control is pretty heavy in terms of resources used by your program because it is being everything to everyone. HOWEVER, as we’ll soon see, it’s easy to set up and use.
I tend to use the SQLDataSource mainly when I’m wanting to link it directly to controls on the web page. If I don’t have the need for the direct connection to the User Interface, I tend to use either a DataSet or a DataReader, depending upon what I’m doing.
The DataSet is best viewed as a sort of ‘in memory’ database. A DataSet object can be used with any number of different sources of data, a separate object known as a DataAdapter is provided by the manufacturer of the data source to populate a DataSet object.
A DataSet object contains one or more DataTable objects, and, like tables in a database, these DataTables may have relationships between them and can even have constraints applied to individual data items within the DataTable.
The below listing shows the DataSet in action, pay special attention to the code involved in adding a record.
<%@ Page Language="VB" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
Protected Sub Page_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Init
Dim sConnString As String = System.Web.Configuration.WebConfigurationManager.ConnectionStrings("Demo-SQL").ConnectionString
Dim cConnection As New System.Data.SqlClient.SqlConnection(sConnString)
cConnection.Open()
' Set up the SQL command
Dim oCommand As New System.Data.SqlClient.SqlCommand("SELECT [ID], [FirstName]," & _
" [LastName] FROM [names] ORDER BY [FirstName]", cConnection)
' Create the DataAdapter - specific to SQL Server
Dim oDataAdapter As New System.Data.SqlClient.SqlDataAdapter(oCommand)
' Create the DataSet - the Dataset is a 'universal' object that is used with
' a data source specific DataAdapter
Dim oDataSet As New System.Data.DataSet
' Fill the DataSet from the Data Adapter for SQL Server
oDataAdapter.Fill(oDataSet)
' Now do soemthing - first of all display some data.
Response.Write("<h1>Old Data</h1>")
For i As Integer = 0 To oDataSet.Tables(0).Rows().Count - 1
Response.Write(oDataSet.Tables(0).Rows(i).Item("FirstName").ToString() + "<br />")
Next
'============================================================================
'
' Start of Insert Code
'
'============================================================================
' Now add a record. This is very convoluted and explains why SQLDataSource is
' rather more popular than the dataset.
' First of all set the InsertCommand and parameters up. This requires us to set up a
' SQLCommand object to do the INSERT operation. This could be done at the start of the
' routine during the initial set up of the dataadapter object.
oDataAdapter.InsertCommand = New System.Data.SqlClient.SqlCommand("INSERT INTO [names] " & _
"([FirstName],[LastName]) VALUES(@Fname, @Lname)")
' Now set up the parameters. Note that we specify the data type, length and the field within
' the table to which the Parameter will map.
oDataAdapter.InsertCommand.Parameters.Add("@Fname", Data.SqlDbType.VarChar, 20, "FirstName")
oDataAdapter.InsertCommand.Parameters.Add("@Lname", Data.SqlDbType.VarChar, 20, "LastName")
' Now set the connection
oDataAdapter.InsertCommand.Connection = cConnection
' Now we add the new row to the table.
Dim dt As System.Data.DataTable
' We have one table in the dataset, so reference it by index number
dt = oDataSet.Tables(0)
' Now create a blank DataRow using the NewRow method
Dim dr As System.Data.DataRow
dr = dt.NewRow()
' Now populate the new row by specifying the field name that the
' data is to be written to, and the value.
dr("Firstname") = "Alan"
dr("Lastname") = "a'Dale"
' Now add the row to our table.
dt.Rows.Add(dr)
' Now instruct the dataadapter control to Update the underlying database
' with the record we've just added to the dataset table.
oDataAdapter.Update(oDataSet)
'============================================================================
'
' End of Insert Code
'
'============================================================================
' Now re-read the data and display it on the page.
Response.Write("<h1>New Data</h1>")
For i As Integer = 0 To oDataSet.Tables(0).Rows().Count - 1
Response.Write(oDataSet.Tables(0).Rows(i).Item("FirstName").ToString()" & _
" " & oDataSet.Tables(0).Rows(i).Item("LastName").ToString() + "<br />")
Next
' All done - close the connection
cConnection.Close()
End Sub
</script>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
</div>
</form>
</body>
</html>
As can be seen, the SQLDataSource wraps up a lot of the DataSet configuration behind the scenes for ease of use. Similar code would be required for UPDATE – that is, an UpdateCommand SQLCommand object, parameters attached to the UpdateCommand object and code to extract the row from the Rows collection of the table, update it, then call the DataAdapter update method.
The DataReader object is more restricted in functionality than the DataSet or the SQLDataSource. It provides read forward and read only access to the underlying data; that is, it supplies a set of data that can only be moved through from start to end, and the data is not modifiable. The DataReader simply reads a record, processes it, then moves on to the next one in the database.
The DataReader object is, however, considerably ‘lighter’ in code terms than the DataSet and is much faster in performance when carrying out reads that a DataSet. The performance of a DataReader only declines slowly as the number of records read increases – a thousand records can easily be read in under a second, for example, whereas a similar exercise with a DataSet might take several seconds to run.
DataReader objects are incredibly useful when you need to read data in form some particular purpose that doesn’t require modification – reports, programmatic population of Dropdowns or other lists, etc.
<%@ Page Language="VB" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
Protected Sub Page_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Init
Dim sConnString As String = System.Web.Configuration.WebConfigurationManager.ConnectionStrings("Demo-SQL").ConnectionString
Dim cConnection As New System.Data.SqlClient.SqlConnection(sConnString)
cConnection.Open()
' Set up the SQL command
Dim oCommand As New System.Data.SqlClient.SqlCommand("SELECT [ID], [FirstName], [LastName]" & _
" FROM [names] ORDER BY [FirstName]", cConnection)
' Create a DataReader to ferry information back from the database
Dim oReader As Data.SqlClient.SqlDataReader
oReader = oCommand.ExecuteReader()
'Iterate through the results. Just keep reading from the oreader object whilst
'the Read() method returns true.
Response.Write("<h1>Data</h1>")
While oReader.Read()
Response.Write(oReader("FirstName") & " " & oReader("LastName") & "<br />")
End While
' Close the connection (will automatically close the reader)
cConnection.Close()
End Sub
</script>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
</div>
</form>
</body>
</html>
As you can see the code involved in retrieving the data is much more straight forward than with the DataSet. The DataReader is excellent for handling those little ‘behind the scenes’ tasks involving retrieving data, such as getting a user’s attributes or permissions from a database, etc.
I very rarely use the Dataset; the SQLDataSource wraps it up so nicely that there’s no reason much of the time for me to torture myself. There are a couple of nice things about it, including it’s ability to directly output an XML version of the DataSet, but the rest of the time I stick to using the SQLDataSource. The DataReader has it’s uses and I find myself using it on quite a few occasions, especially where I just need quick read access to data.
Get the best asp web hosting provider now and save 30%
Plug and play ASP membership script that integrates with PayPal to let you charge recurring membership fees.
