Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Command
The core data access logic in Listing 1 occurs with the setup of a Command object. The Command object is used to interact with the .NET data provider using SQL data manipulation statements, stored procedures, or TableDirect manipulation in the case of the OLEDB .NET data provider. It provides a number of methods to execute data manipulation such as ExecuteNonQuery or to return values from the data source with ExecuteScalar, ExecuteReader, and ExecuteXmlReader.
ExecuteNonQuery
The simplest method of the Command object is ExecuteNonQuery, which doesn't return data from the data source, but rather the number of rows affected by the operation. Listing 1 shows how our INSERT SQL command returns the number of rows affected that we output to the console, confirming success of the data manipulation.
Handling Errors and Closing Connections
The code from Listing 1 also shows how to correctly wrap the execution of a SQL insert operation inside a local transaction on the data source and a try/catch/finally block in the client code. The try/catch/finally assists us with accomplishing both tasks.
The catch block is used to catch an SQLException error object thrown when our data access statements go awry. This allows us to properly roll back the transaction and display the appropriate information to the end user.
The finally block is used to ensure proper resource management of the Connection object. Since .NET relies on non-deterministic finalization, the client has the responsibility of telling the Connection object when we're done with it. Since the finally block executes whether or not an exception is thrown, it's an ideal place to put the closing logic into the client program.
Running the Program
The first time we run through the program, the insert is successful, as the following output shows:
INSERT Executed with 1 rows affected.
If we run the program a second time, the SQL provider throws a SQLException, which we catch to abort the local transaction and print the error message shown below:
INSERT Failed with SQL Exception: Violation of PRIMARY KEY constraint 'PK_Customers'. Cannot insert duplicate key in object 'Customers'. The statement has been terminated.
Connection Pooling
A common question asked by those new to ADO.NET is how to do database connection pooling. The SQL .NET data provider provides its own implementation of connection pooling that is turned on by default. If you want to change the operation of this pool, there are connection string parameters that you can refer to in the .NET framework SDK documentation.
The OLEDB .NET data provider doesn't implement its own connection pool but rather relies on the mechanisms of OLEDB to do the connection pooling. It works in the same manner as in ADO.
In our examples for both SQL and OLEDB, the defaults work great for 99% of all situations (and especially well for demos!). The key thing to remember is to use the same connection string, as it's the matching value for reusing an already enlisted connection in either provider's pool.
ExecuteScalar
The next phase in our tutorial covers how to get a result set from our Command object. This happens through three methods: ExecuteScalar, ExecuteReader, and ExecuteXmlReader. The first two we cover with our examples in this article. ExecuteXmlReader is left for a later article in this series because it ties directly into the XML capabilities of SQL Server 2000.
Listing 2 shows a piece of code that retrieves the ContactName column from the row we inserted in the demo from Listing 1 and the entire rowset of Customers who have a CustomerID starting with the letter D, which is also inclusive of our inserted row.
Listing 2 SQL Provider Query Demo
using System;
using System.Data;
using System.Data.SqlClient;
namespace ConnectedOpsDemo
{
class SqlCmdQueryDemo
{
[STAThread]
static void Main(string[] args)
{
SqlConnection conn = null;
SqlDataReader rdr = null;
try
{
// open connection to the data source
conn = new SqlConnection("server=(local);database=Northwind;"
+ "integrated security=true;");
conn.Open();
// return ContactName from DALEM row via ExecuteScalar
SqlCommand cmd =
new SqlCommand(
"SELECT ContactName FROM Customers WHERE CustomerID='DALEM'",
conn);
string contactname = (string) cmd.ExecuteScalar();
Console.WriteLine(
"QUERY: {0} \nReturned ContactName={1} from ExecuteScalar",
cmd.CommandText,"DALEM");
// change CommandText to select a result set from the
//Customers table
cmd.CommandText =
"SELECT * FROM Customers WHERE CustomerID LIKE '%D%'";
// execute command to return a datareader that closes our
// parent connection when it is closed
rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
Console.WriteLine(
"\nQUERY: {0} \nReturned the following rows " +
"from ExecuteReader",
cmd.CommandText);
// read through all rows
while (rdr.Read())
{
// display CustomerID (position 0),
//ContactName (by name) columns
// from the DataReader
Console.WriteLine("CustomerID: {0} ContactName: {1}",
rdr.GetString(0), rdr["ContactName"]);
}
}
finally
{
// close the reader in the face of exceptions
if (rdr != null)
{
if (!rdr.IsClosed)
rdr.Close();
}
}
}
}
}
The ExecuteScalar returns an object reference which we cast to a string since we know we have a character value coming back from the ContactName column. This example is much more efficient than returning a result set object like the DataReader when all you need is a single value.
ExecuteReader
The ExecuteReader method of the Command object is our gateway to returning rowsets from a data source. It returns an object reference to a DataReader holding the result set. We then iterate through the DataReader to display row data. The console output of the ExecuteScalar and ExecuteReader are shown in Listing 3.
Listing 3 SQL Provider Query Demo Output
QUERY: SELECT ContactName FROM Customers WHERE CustomerID='DALEM' Returned ContactName=DALEM from ExecuteScalar QUERY: SELECT * FROM Customers WHERE CustomerID LIKE '%D%' Returned the following rows from ExecuteReader CustomerID: BOLID ContactName: Martín Sommer CustomerID: DALEM ContactName: Dale Michalk CustomerID: DRACD ContactName: Sven Ottlieb CustomerID: DUMON ContactName: Janine Labrune CustomerID: GALED ContactName: Eduardo Saavedra CustomerID: GODOS ContactName: José Pedro Freyre CustomerID: LINOD ContactName: Felipe Izquierdo CustomerID: MAISD ContactName: Catherine Dewey CustomerID: OLDWO ContactName: Rene Phillips CustomerID: QUEDE ContactName: Bernardo Batista CustomerID: SPECD ContactName: Dominique Perrier CustomerID: SUPRD ContactName: Pascale Cartrain CustomerID: TRADH ContactName: Anabela Domingues CustomerID: WANDK ContactName: Rita Müller