.neT: Ado objects

<%@ Import Namespace="System" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>

<%@ Import Namespace="System" %> 

<%@ Import Namespace="System.Data" %> 

<%@ Import Namespace="System.Data.SqlClient" %>

MS Access->System.Data.OleDb
Oracle->System.Data.OracleClient
ODBC->System.Data.Odbc

The Connection

After we import all the necessary namespaces, we’re ready to connect to our database. Now, whether you implement the DataReader or Dataset, your initial database connection will still be as follows:

view plain print ?

SqlConnection objConnect = new SqlConnection (Your Connection String);
objConnect.Open();

SqlConnection objConnect = new SqlConnection (Your Connection String); 

 

objConnect.Open();

Listed below are the common connection object methods we could work with:

Open - Opens the connection to our database
Close - Closes the database connection
Dispose - Releases the resources on the connection object. Used to force garbage collecting, ensuring no resources are being held after our connection is used. Incidentally, by using the Dispose method you automatically call the Close method as well.
State - Tells you what type of connection state your object is in, often used to check whether your connection is still using any resources. Ex. if
(ConnectionObject.State == ConnectionState.Open)

The DataReader

Command Object Methods

Here are some methods the DataReader works with through its Command object. The four Execute methods all pertain to an action performed by the Command object, wile the remaining methods are used to enhance the Command object’s own functionality.

ExecuteReader - Simply executes the SQL query against the database, using the Read() method to traverse through data, as illustrated below
ExecuteNonQuery – Used whenever you work with SQL stored procedures with parameters, as illustrated in the Stored Procedures section below
ExecuteScalar - Returns a lightning fast single value as an object from your database Ex. object val = Command.ExecuteScalar(); Then check if != null.
ExecuteXmlReader - Executes the SQL query against SQL Server only, while returning an XmlReader object. See .NET documentation for more information
Prepare – Equivalent to ADO’s Command.Prepared = True property. Useful in caching the SQL command so it runs faster when called more than once. Ex. Command.Prepare();
Dispose – Releases the resources on the Command object. Used to force garbage collecting, ensuring no resources are being held after our connection is used. Incidentally, by using the Dispose method you automatically call the Connection object’s Close method as well.

Thus, after we establish our initial database connection all we need to do to retrieve data with a DataReader is to use the Command object to query the open database. The Command.ExecuteReader method used via the Command Object creates our DataReader.

view plain print ?

SqlCommand objCommand = new SqlCommand(Sql String, objConnect);
SqlDataReader objDataReader = objCommand.ExecuteReader();

SqlCommand objCommand = new SqlCommand(Sql String, objConnect);  

  

SqlDataReader objDataReader = objCommand.ExecuteReader();

If you hadn’t noticed, the connection object above is obviously set for SQL Server. For the others like Access, you’d use the OleDbConnection object, easily accommodated by replacing the
SQL -> SqlCommand with
OleDB-> OleDbCommand objCommand.
This applies to the SqlDataReader->OleDbDataReader as well.
This interchangeability works across the .NET framework, so keep it in mind.
Once we have our DataReader object, we set up our DataReader’s Read() method in a while loop and traverse and display our data results by index position, rather than objDataReader["Name"].ToString(). I prefer this method as it’s a little quicker, provided you can remember your column names!

view plain print ?

while (objDataReader.Read() == true) {
Response.Write (objDataReader[0].ToString() + " ");
}

while (objDataReader.Read() == true) {  

  

Response.Write (objDataReader[0].ToString() + "<BR>");  

  

}

DataReader Methods

Here are some common methods available to the Datareader:

Read – Moves the record pointer to the first row, which allows the data to be read by column name or index position. Can check for data existence with conditional, if (DataReader.Read() = true)
HasRows - New only with .NET v1.1. HasRows checks if any data exists, and is used instead of the Read method. Ex. if (DataReader.HasRows).
IsClosed - A method that can determine if the DataReader is closed. Ex. if (DataReader.IsClosed == false)
NextResult - Equivalent to ADO’s NextRecordset Method, where a batch of SQL statements are executed with this method before advancing to the next set of data results. As with the loop just listed, you can add DataReader.NextResult() after the first loop within multiple SQL statements, and then begin a new loop for the next set.
Close – Closes the DataReader

After all’s said and done, it’s always good programming practice to close, release and clear all connections and resources:

view plain print ?

objDataReader.Close();
objDataReader = null;
objCommand.Dispose();
objCommand = null;
objConnect.Close();
objConnect= null;

objDataReader.Close();  

objDataReader = null;  

objCommand.Dispose();  

objCommand = null;   

objConnect.Close();  

objConnect= null;

DataReader Object Command Behaviors

view plain print ?

objCommand.ExecuteReader(CommandBehavior.CloseConnection);
or objCommand.ExecuteReader(CommandBehavior.SingleResult);

objCommand.ExecuteReader(CommandBehavior.CloseConnection);   

or objCommand.ExecuteReader(CommandBehavior.SingleResult);

The behaviours are:

SingleResult - Similar to ExecuteScalar, returns a single value
SingleRow - As it sounds, returns us a single row
SequentialAccess – Is a very efficient method of in-order data access and useful in two ways. Firstly, it’s used with the DataReader’s Get Methods where you can return results in sequential order from the database using the columns’ ordinal positions, it’s superior (in terms of speed) to using the objDataReader["Column1"].ToString() or objDataReader[0].ToString() methods. And secondly, the Data Reader’s many available Get Methods, i.e. objDataReader.GetString(0) or objDataReader.GetInt32(0), allow us to bypass any initial data conversion that would take place. However, non-Null data must be present for it to work, and all data retrieval must be in sequential order, dependent on your SQL query.
CloseConnection - Forces the DataReader and its Connection object to close, once it reads through the data

The Completed DataReader

Here is our Datareader code in its entirety. You could run this within any function, or on Page_Load. Furthermore, the tags that enclose the ADO code below are common to all code examples listed throughout this article:

view plain print ?

<%@ Page Language="C#" Debug="False" Explicit="True"%>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
void Page_Load (Object Source, EventArgs E)
{
SqlConnection objConnect = new SqlConnection
("server=(local);uid=sa;pwd=;database=Northwind;");
objConnect.Open();
SqlCommand objCommand = new SqlCommand("Select * from
Suppliers Order by ContactName Asc", objConnect);
objCommand.Prepare(); SqlDataReader objDataReader =
objCommand.ExecuteReader CommandBehavior.CloseConnection);
while (objDataReader.Read() == true) {
Response.Write (objDataReader
[2].ToString() + " ");
}
objDataReader.Close();
objDataReader = null;
objCommand.Dispose();
objCommand = null;
objConnect.Close();
objConnect= null;
}

Stored Procedures

view plain print ?

CREATE PROCEDURE ShowSuppliers (
@txt varchar(50)
)
AS
Select CompanyName, City, Country from Suppliers Where
Country like "%" + @txt + "%"

CREATE PROCEDURE ShowSuppliers (   

@txt varchar(50)   

)   

AS   

Select CompanyName, City, Country from Suppliers Where    

Country like "%" + @txt + "%"

If you wanted to simply execute a Stored Procedure, pass it a value, and then read through that, how would you do it? Easy! Prior to calling the ExecuteReader method, replace the SQL String in the SqlCommand constructor with the name of the Stored Procedure, then specify to the Command Object’s CommandType Property that you’re working with a Stored Procedure. After you’ve done this, implement the Parameters collection (via the Param SqlParameter variable) to set up the value you wish to pass to your stored procedure, like so:

view plain print ?

SqlCommand objCommand = new SqlCommand("ShowSuppliers", objConnect);
objCommand.CommandType = CommandType.StoredProcedure;
SqlDataReader objDataReader = objCommand.ExecuteReader
(CommandBehavior.CloseConnection);
SqlParameter Param = objCommand.Parameters.Add("@txt",SqlDbType.VarChar, 50);
Param.Value = "US";

SqlCommand objCommand = new SqlCommand("ShowSuppliers",   

objConnect);   

objCommand.CommandType = CommandType.StoredProcedure;   

   

SqlDataReader objDataReader = objCommand.ExecuteReader   

(CommandBehavior.CloseConnection);   

   

SqlParameter Param = objCommand.Parameters.Add("@txt",    

SqlDbType.VarChar, 50);   

Param.Value = "US";   

   

// ... Get Data

Then display your data using the DataReader methods, like the while loop code example listed in the last section, or via the DataGrid example that’s coming up.

Stored Procedures – and Alternative Method

An alternative method of working with Stored Procedures within this context is the Command.ExecuteNonQuery() method. This is useful when working with more multifaceted Stored Procedures that have input, output and return values. Using them is not that much more complicated; simply implement the Parameters collection shown below, i.e. Param.Direction = ParameterDirection.Input or .OutPut or .ReturnValue; and apply the .Value =, whatever the value type:

view plain print ?

CREATE PROCEDURE ShowSuppliers (
@txt varchar(50),
@Name varchar (50) output,
@Company varchar (50) output,
@Country varchar (50) output
)
AS
Select @Name = ContactName, @Company = CompanyName,
@Country = Country from Suppliers Where Country like "%" + @txt + "%"
GO

CREATE PROCEDURE ShowSuppliers (   

@txt varchar(50),   

@Name varchar (50) output,   

@Company varchar (50) output,   

@Country varchar (50) output   

)   

AS   

Select @Name = ContactName, @Company = CompanyName,    

@Country = Country from Suppliers Where Country like "%" + @txt + "%"   

GO

Here we’ve created a slightly more elaborate Stored Procedure to illustrate the ExecuteQuery method. As you can see, this not only contains our initial search word variable – @txt, but we could now obtain several output values.

view plain print ?

// ... Database Connection / Command here like above
SqlParameter Param = objCommand.Parameters.Add("@txt",
SqlDbType.VarChar, 50);
Param.Direction = ParameterDirection.Input;
Param.Value = "US";
Param = objCommand.Parameters.Add("@Name", SqlDbType.VarChar,50);
Param.Direction = ParameterDirection.Output;
Param = objCommand.Parameters.Add("@Company", SqlDbType.VarChar,50);
Param.Direction = ParameterDirection.Output;
Param = objCommand.Parameters.Add("@Country", SqlDbType.VarChar,50);
Param.Direction = ParameterDirection.Output;
objCommand.ExecuteNonQuery();
Response.Write (objCommand.Parameters["@Name"]
.Value.ToString() + " ");
Response.Write (objCommand.Parameters["@Company"]
.Value.ToString() + " ");
Response.Write (objCommand.Parameters["@Country"]
.Value.ToString() + " ");

// ... Database Connection / Command here like above   

   

SqlParameter Param = objCommand.Parameters.Add("@txt",    

SqlDbType.VarChar, 50);   

Param.Direction = ParameterDirection.Input;   

Param.Value = "US";   

   

Param = objCommand.Parameters.Add("@Name", SqlDbType.VarChar,50);   

Param.Direction = ParameterDirection.Output;   

   

Param = objCommand.Parameters.Add("@Company", SqlDbType.VarChar,50);   

Param.Direction = ParameterDirection.Output;   

   

Param = objCommand.Parameters.Add("@Country", SqlDbType.VarChar,50);   

Param.Direction = ParameterDirection.Output;   

   

objCommand.ExecuteNonQuery();   

   

Response.Write (objCommand.Parameters["@Name"]   

.Value.ToString() + "<BR>");   

Response.Write (objCommand.Parameters["@Company"]   

.Value.ToString() + "<BR>");   

Response.Write (objCommand.Parameters["@Country"]   

.Value.ToString() + "<BR>");

The only thing here that’s dissimilar from our last Sproc example is the fact that we’ve just added a new Parameter Direction to reflect the values we wish to retrieve. In turn, we Response.Write them in the way shown here. For added information on this and working with Stored Procedures with return values, refer to the .NET documentation.
But now let’s imagine you want to display data in a datagrid using the datareader, rather than using the while loop and formatting a table through HTML. But in this case, you don’t require any of the DataGrid’s common properties, like paging. How would this be accomplished? In a similar way to the DataReader example code at the end of the last section. Everything remains the same, except that after you run the ExecuteReader() method, you simply bind your DataGrid’s datasource to the Datareader as shown.

view plain print ?

MyDataGrid.DataSource = objDataReader;
MyDataGrid.DataBind();

MyDataGrid.DataSource = objDataReader;   

   

MyDataGrid.DataBind();

Aside from binding our Datagrid, it’s just as easy to bind other server controls. For instance, to bind a DropDownList control, you obviously change your DataGrid Control to a DropDownList, though the Datasource and Binding techniques are the same. The only difference is dependent on the control you use, as they all have their own unique properties that can be assigned. How do we make our DropDownList display our query results? Right after your ExecuteReader method, specify its DataTextField – the value to be displayed, and the DataValueField – the value to be passed when an item is selected. Here goes:

view plain print ?

// ... our Data Access was here. Then we assign
our new control's properties
MyDropList.DataSource = objDataReader;
MyDropList.DataTextField = "Country";
MyDropList.DataValueField = "Country";
MyDropList.DataBind();
//And our control placed in our runat="server"
form tags on our page
<ASP:DropDownList id="MyDropList" runat="server" />

// ... our Data Access was here. Then we assign    

our new control's properties   

   

MyDropList.DataSource = objDataReader;   

   

MyDropList.DataTextField = "Country";   

MyDropList.DataValueField = "Country";   

   

MyDropList.DataBind();   

   

//And our control placed in our runat="server"    

form tags on our page   

   

<ASP:DropDownList id="MyDropList" runat="server" />

The DataSet

The DataSet can be thought of as an in-memory storage container for all your data. It allows us far more capability and power over the manipulation and presentation of our data, even extending to XML. Here, the DataAdapter acts as the intermediary object responsible for joining our database with our DataSet, as the Command Object is with the DataReader.
Unlike the DataReader object, the DataAdapter/DataSet not only gives you the ability to work with disconnected data stores, but can also be used for common editing functions such as inserting, deleting, and updating your data, and even setting transactional logic.

Working with DataSet

In this example, we’ll demonstrate a common end result when working with a DataAdapter and DataSet: the DataGrid server control for sole data presentation.
We already know how to open a connection to our database. However, as we’re going to deal with our DataSet, the order of events changes at this point. Recall us using the Command Object to query the database? When working with Datasets, we would use the DataAdapter to do the querying, and the Fill() method to populate our DataSet with results:

view plain print ?

SqlDataAdapter objDataAdapter = new SqlDataAdapter ("Select
CompanyName, ContactName, City, Country, Region from Suppliers", objConnect);
DataSet objDS = new DataSet();
objDataAdapter.Fill (objDS);

SqlDataAdapter objDataAdapter = new SqlDataAdapter ("Select     

CompanyName, ContactName, City, Country, Region from     

Suppliers", objConnect);    

    

DataSet objDS = new DataSet();    

    

objDataAdapter.Fill (objDS);

Well, we use the SqlDataAdapter to execute the SQL statement, and upon this action it returns data to fill our DataSet. Once our DataSet object is created, it in turn exposes the Datatable, and within it a collection of rows and columns that we’ll examine later on. Incidentally, you could, if you so chose, name the newly create Datatable, like so: objDataAdapter.Fill (objDS,"MyTable"); Note: For an OleDB database use OleDbDataAdapter.

The most common method for exposing data upon the creation of a disconnected data store would be the Fill() method, which we’ve mentioned, and will demonstrate in just a moment. At this point you may be wondering why the DataReader has been explored in such detail. Well, here we’re focusing on data access and presentation, and the DataReader is more commonly used for these purposes. The DataSet, which has roughly an equivalent number of methods, is geared more towards data management. We momentarily dealt with its Fill() method, as this is what enabled us to retrieve data easily. Additional DataSet methodology would involve complex methods for data manipulation. I would refer you to the .NET documentation at this point, since we won’t cover this information here.
The DataGrid Control within .NET is probably the most commonly used control, as it offers paging, filtering and sorting. This is the reason for the common DataSet / Datagrid relationship. The Datareader, being a forward/read-only object, cannot support these conditions unless you cleverly program it to do so.

Displaying the Data

Now that we’ve discussed the chosen methods of data retrieval, we have to look at how we’ll display it. We can do this by binding our DataSet to the DataGrid. We assign the DataGrid’s DataSource property to our DataSet, and then bind it to view our results:

view plain print ?

MyDataGrid.DataSource = objDS
MyDataGrid.DataBind();

MyDataGrid.DataSource = objDS    

MyDataGrid.DataBind();

We’ve learned that the DataSet itself is capable of holding multiple tables of data, each being a DataTable. From this point you could further manipulate your data before you bind it with the DataTable object, as we’ll illustrate further on.
What’s more, by using a DataView we can create different views on our Dataset’s DataTable, which enables us to filter or sort the data. To do this, you’d assign your DataGrid’s Datasource property to the DataView, then bind it.

view plain print ?

DataView objView = objDS.Tables[0].DefaultView;
objView.RowFilter = "Country like '%US%'";

DataView objView = objDS.Tables[0].DefaultView;    

    

objView.RowFilter = "Country like '%US%'";

view plain print ?

objView.Sort = "Country asc";
MyDataGrid.DataSource = objView;
MyDataGrid.DataBind();

objView.Sort = "Country asc";    

    

MyDataGrid.DataSource = objView;    

    

MyDataGrid.DataBind();

The results would then reflect the filtering or sorting you’ve established. For instance, you could implement this by pulling in a value from a dropdown list box that specifies what you wish to filter out or sort by.
Also keep in mind that within your .NET page all server controls are placed within runat="server" form tags. Here’s the entire code:

view plain print ?

<script runat="server" language="C#">
void Page_Load(Object Source,EventArgs E) {
SqlConnection objConnect = new SqlConnection
("server=(local);uid=sa;pwd=;database=Northwind;");
objConnect.Open();
SqlDataAdapter objDataAdapter = new SqlDataAdapter
("Select CompanyName, ContactName, City, Country, Region from
Suppliers", objConnect);
DataSet objDS = new DataSet();
objDataAdapter.Fill (objDS);
MyDataGrid.DataSource = objDS;
//DataView objView = objDS.Tables[0].DefaultView;
//objView.RowFilter = "Country like '%US%'";
//MyDataGrid.DataSource = objView;
MyDataGrid.DataBind();
objDataAdapter.Dispose();
objDataAdapter = null;
objDS.Dispose();
objDS = null;
objConnect.Close();
objConnect = null;
}
</script>
<form runat="server">
<ASP:DataGrid id="MyDataGrid" runat="server" />
</form>

<script runat="server" language="C#">    

    

void Page_Load(Object Source,EventArgs E) {    

    

SqlConnection objConnect = new SqlConnection     

("server=(local);uid=sa;pwd=;database=Northwind;");     

    

objConnect.Open();    

    

SqlDataAdapter objDataAdapter = new SqlDataAdapter     

("Select CompanyName, ContactName, City, Country, Region from     

Suppliers", objConnect);    

    

DataSet objDS = new DataSet();    

    

objDataAdapter.Fill (objDS);    

    

MyDataGrid.DataSource = objDS;    

    

//DataView objView = objDS.Tables[0].DefaultView;    

//objView.RowFilter = "Country like '%US%'";    

//MyDataGrid.DataSource = objView;    

    

MyDataGrid.DataBind();    

    

objDataAdapter.Dispose();     

objDataAdapter = null;     

objDS.Dispose();    

objDS = null;    

objConnect.Close();    

objConnect = null;    

    

}    

    

</script>    

    

<form runat="server">    

<ASP:DataGrid id="MyDataGrid" runat="server" />    

</form>

Recall in the last section how I quickly illustrated binding to a DropDownList? Well the same process could be applied to the DataSet, except that in this instance it would occur after the Fill method has been executed (as opposed to the ExecuteReader), and each would be assigned a different datasource.
Before we conclude, let’s revisit our DataTable for a moment. If you wanted more control over your data you could set up the DataTable from your dataset, and loop through it just like you did with the DataReader. Therefore, after using the Fill() method explained above, you can loop through your DataTable DataRows with something like this:

view plain print ?

DataTable dsReader = objDS.Tables[0];
foreach(DataRow row in dsReader.Rows) {
Response.Write (row[0] + " ");
}

DataTable dsReader = objDS.Tables[0];     

foreach(DataRow row in dsReader.Rows) {    

    

Response.Write (row[0] + "<BR>");    

    

}

Conclusion

In summary, this introductory tutorial should’ve whet your appetite, and has hopefully encouraged you to jump right in and create an application or two. In addition, be sure to read the .NET documentation and QuickStart tutorials for more information on everything we’ve examined, and more so on what we’ve referred to in passing (including reading XML with the DataReader, implementing transactions, and inserting, editing and updating your data with a DataSet).
You should now understand the ease of database access and data presentation that’s available within ADO.NET, and have a feel for the further directions these objects, notably the DataSet, can take. I hope you now have a firmer grasp on the power of .NET, with more than enough information to get you up and running.