Beginning Visual C#supAND#174;/sup 2005

This chapter has covered the basic ADO.NET operations without your having to know anything about the SQL database query language. All ADO.NET commands that read and write from the data source are translated to SQL commands that execute the raw database operations.

Practical use of ADO.NET in real-life working situations will require some knowledge of SQL; for a much more complete introduction to SQL than there is space for here, refer to a good book on SQL such as Beginning SQL Server 2005 Programming (ISBN 0-7645-8433-2) or Professional SQL Server 2005 Programming (ISBN 0-7645-8434-0), both from Wiley Publishing, Inc.

That said, there are a few basics to cover here.

SQL Commands in Data Adapters

In the examples given earlier, you used SQL SELECT commands that return all the rows of a table, such as:

SqlDataAdapter thisAdapter = new SqlDataAdapter( "SELECT * FROM Customers", thisConnection);

This SELECT command returns all the rows and columns of the customer table when the Fill() method is called, and loads them into the memory of your program. This is fine for a small table like the Customers table of Northwind, which has only 11 columns and less than 100 rows of data; however, it is not likely to work well for a large table typical of those encountered in many business applications with 100,000 or even 1,000,000 rows.

You need to construct the SELECT command so that it only brings in the data you actually need to process. One way is to limit the number of columns used if your program really only interacts with some of the columns, with a SELECT statement specifying only the desired columns, such as:

SELECT CustomerID, CompanyName FROM Customers

However, you typically don't want to do this when adding rows, because you will want to specify values for all columns.

Use of WHERE with SELECT

Another technique for minimizing the amount of data loaded into memory is to always specify a WHERE clause on the SQL SELECT statement, which limits the number of rows selected. For example, the statement

SELECT * FROM Customers WHERE CustomerID = 'ZACZI'

will load only the one row containing Zachary Zithers into memory, using a fraction of the memory required to load the entire table. A range can be specified with WHERE clauses as well, so a statement like

SELECT * FROM Orders WHERE OrderID BETWEEN 10900 AND 10999

will only load the rows with OrderID in the range shown.

If you can limit the number of rows being loaded from a large table with a WHERE clause, always do so. Never load all the rows of a table into your DataSet and then search them with a foreach loop; use the SELECT statement with WHERE to do this kind of search instead.

Your goal is to find the most effective balance between processing data locally on the client where your ADO.NET program is executing and processing on the server where the SQL is executed. The ADO.NET object model and C# are better suited than SQL for complex calculations or navigational logic. Fill your DataSet with the data from the tables you want to process, and execute this kind of logic on the client. However, limiting the number of rows selected from each table with appropriate conditions will greatly increase the performance (especially if the data is being transferred across a network) and decrease the memory usage.

Viewing SQL SELECT, UPDATE, INSERT, and DELETE Commands

SQL uses four basic commands for querying, updating, adding, and deleting rows from a table. These are, respectively, the SELECT, UPDATE, INSERT, and DELETE commands. In earlier examples, you have used the CommandBuilder object to create the SQL commands used to update the database:

SqlDataAdapter thisAdapter = new SqlDataAdapter("SELECT CustomerID from Customers", thisConnection); SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter);

The command builder generates the SQL commands for modifying the data (UPDATE, INSERT, and DELETE ) based on the SELECT command.

In the program, you create in the following Try It Out, you can see the generated commands with the GetUpdateCommand(), GetInsertCommand(), and GetDeleteCommand() methods of the CommandBuilder object.

Try It Out – Show SQL Example

Follow these steps to create the ShowSQL example in Visual Studio 2005:

1. Create a new console application called ShowSQL in the C:\BegVCSharp\Chapter24 directory, and add the usual using directives to the top of the code:

   #region Using Directives using System; using System.Data; // Use ADO.NET namespace using System.Data.SqlClient; // Use SQL Server data provider namespace using System.Collections.Generic; using System.Text; #endregion

2. Now add the following code to the Main() method:

   static void Main(string[] args) { // Specify SQL Server-specific connection string SqlConnection thisConnection = new SqlConnection( @"Server=(local)\sqlexpress;Integrated Security=True;" + "Database=northwind"); thisConnection.Open(); SqlDataAdapter thisAdapter = new SqlDataAdapter("SELECT CustomerID from Customers", thisConnection); SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter); Console.WriteLine("SQL SELECT Command is:\n{0}\n", thisAdapter.SelectCommand.CommandText); SqlCommand updateCommand = thisBuilder.GetUpdateCommand(); Console.WriteLine("SQL UPDATE Command is:\n{0}\n", updateCommand.CommandText); SqlCommand insertCommand = thisBuilder.GetInsertCommand(); Console.WriteLine("SQL INSERT Command is:\n{0}\n", insertCommand.CommandText); SqlCommand deleteCommand = thisBuilder.GetDeleteCommand(); Console.WriteLine("SQL DELETE Command is:\n{0}", deleteCommand.CommandText); thisConnection.Close(); Console.Write("Program finished, press Enter/Return to continue:"); Console.ReadLine(); }

   The output of this example is shown in Figure 24-16.

   Figure 24-16

How It Works

Note that the UPDATE and DELETE commands use a WHERE clause that was generated by the CommandBuilder object.

The question marks (?) are markers for parameters, where the ADO.NET runtime will substitute an actual value into the command; for example, when you used the Delete() method to delete the row containing CustomerID ZACZI, at the time Update() was called, the command

DELETE FROM Customers WHERE ( CustomerID = 'ZACZI' )

was executed to remove the ZACZI row.

Notice that to output the SELECT command you used the SelectCommand property to get the command directly from the DataAdapter. The DataAdapter also has the UpdateCommand, InsertCommand, and DeleteCommand properties to get or set the SQL commands used at update time directly. A developer familiar with SQL can optimize these commands to perform better than the commands automatically generated by CommandBuilder, especially when all columns are included in the SQL SELECT statement.

Direct Execution of SQL Commands

If your program needs to perform a set-oriented operation such as deleting or updating all rows meeting a certain condition, it is much more efficient, especially for large tables, to do this as a single SQL command than to do extended processing in C# code.

ADO.NET provides the SqlCommand or OleDbCommand objects for executing SQL commands. These objects provide methods for executing SQL commands directly. You used the ExecuteReader() method at the beginning of the chapter when you looked at the DataReader object. Here, you look at the other methods for executing SQL statements — ExecuteScalar() and ExecuteNonQuery().

Retrieving Single Values

On many occasions, it is necessary to return a single result from a SQL query, such as the number of records in a given table. the ExecuteScalar() method allows you to achieve this — this method is used to execute SQL commands that return only a scalar (a single value), as opposed to returning multiple rows, as with ExecuteReader().

In the next Try It Out, you use the ExecuteScalar() method of SqlCommand to execute the query.

Try It Out – Retrieving Single Values with ExecuteScalar()

As a first example, let's consider a program that gets a count of the rows in the Customers table. This is similar to the DataReader example at the start of the chapter, but uses a different SQL statement and method of execution.

1. Create a new console application called ExecuteScalarExample in the C:\BegVCSharp\ Chapter24 directory, and add the usual using directives to the top of the code:

   #region Using Directives using System; using System.Data; // Use ADO.NET namespace using System.Data.SqlClient; // Use SQL Server data provider namespace . . .

2. Now add the following code to the Main() method:

   static void Main(string[] args) { SqlConnection thisConnection = new SqlConnection(@"Server=(local)\sqlexpress;Integrated Security=True;" + "Database=northwind"); thisConnection.Open(); SqlCommand thisCommand = thisConnection.CreateCommand(); thisCommand.CommandText = "SELECT COUNT(*) FROM Customers"; Object countResult = thisCommand.ExecuteScalar(); Console.WriteLine("Count of Customers = {0}", countResult); thisConnection.Close(); Console.Write("Program finished, press Enter/Return to continue:"); Console.ReadLine(); }

How It Works

This program uses the SQL Server .NET data provider. The core of the program is the same as the first example in this chapter, opening a connection to SQL Server on the local machine with integrated security and the Northwind database.

You create a SqlCommand object and assign the SELECT COUNT(*) command to its CommandText property. COUNT() is a SQL function that returns the count of rows that match the WHERE condition. Then you call the ExecuteScalar() method of SqlCommand to execute the query to retrieve the count. You display the count and exit. When executed against the Northwind database, the program displays:

Count of Customers = 91

(Provided that you've deleted Zachary Zithers Ltd!) This is equivalent to loading the Customers table into the DataTable object and using the Count property of the Rows object as in earlier examples. Why would you want to do the job this way? It depends on the structure of your data and what else you are doing in your program. If you have a small amount of data, or are loading all the rows into your DataSet for any other reason, it makes sense to just use DataTable.Rows.Count. However, if you wanted to count the exact number of rows in a very large table with 1,000,000 rows, it is much more efficient to issue a SELECT COUNT(*) query with the ExecuteScalar() method rather than trying to load 1,000,000 rows into memory.

Retrieving No Data

A rather strange heading, but bear in mind that data modification operations such as SQL INSERT, UPDATE, and DELETE do not return data. What is interesting for these commands is the number of rows affected. This number is returned by the ExecuteNonQuery() method.

Assume that one of your suppliers has increased all prices by 5 percent for all of its products. The following Try It Out shows how to use the SqlCommand object to execute a SQL UPDATE command to increase all the prices by 5 percent for products supplied by that supplier.

Try It Out – Data Modification with ExecuteNonQuery

Follow these steps to create the ExecuteNonQueryExample in Visual Studio 2005:

1. Create a new console application called ExecuteNonQueryExample in the C:\BegVCSharp\ Chapter24 directory, and add the usual using directives to the top of the code:

   #region Using Directives using System; using System.Data; // Use ADO.NET namespace using System.Data.SqlClient; // Use SQL Server data provider namespace . . .

2. Next add the following code to the Main() method:

   static void Main(string[] args) { SqlConnection thisConnection = new SqlConnection( @"Server=(local)\sqlexpress;Integrated Security=True;" + "Database=northwind"); thisConnection.Open(); SqlCommand thisCommand = thisConnection.CreateCommand(); thisCommand.CommandText = "UPDATE Products SET " + "UnitPrice=UnitPrice*1.05 WHERE SupplierId=12"; int rowsAffected = thisCommand.ExecuteNonQuery(); Console.WriteLine("Rows Updated = {0}", rowsAffected); thisConnection.Close(); Console.Write("Program finished, press Enter/Return to continue:"); Console.ReadLine(); }

How It Works

This program opens the connection just as in the previous example. You create a SqlCommand object and assign the UPDATE command shown as the text of the command. Then you call the ExecuteNonQuery() method of SqlCommand to execute the query, returning the number of rows affected in the database. You display the number of rows and exit. When executed against the Northwind database, the program displays

Rows Updated = 5

indicating that the prices were adjusted for five products.

Calling a SQL Stored Procedure

Finally, here's an example of calling a SQL stored procedure, which is a procedure written in SQL and stored in the database. Stored procedures encapsulate complex SQL queries and database procedures in a single unit that can be called by multiple application programs or directly by users; the database administrator can be sure that the exact same steps are followed whenever a stored procedure is called, ensuring consistent use of the database. Users and application developers don't have to remember complex SQL query syntax; all they have to do is know the name of the stored procedure and what it does. Northwind contains several stored procedures. You will call the Ten Most Expensive Products procedure, which you can see in the Server Explorer, as shown in Figure 24-17.

Figure 24-17

As you can see, it returns two columns, TenMostExpensiveProducts and UnitPrice. Let's try it out!

Try It Out – Calling a Stored Procedure

Essentially, this program is a variation on the very first example in this chapter, DataReading. Just like SQL commands, stored procedures can return single values, in which case you would use ExecuteScalar or ExecuteNonQuery, as you just saw with regular SQL commands. Alternately, they can return query results that are read with a DataReader, like this example in this Try It Out.

1. Create a new console application called SQLStoredProcedure in the C:\BegVCSharp\ Chapter24 directory, and add the usual using directives to the top of the code:

   #region Using Directives using System; using System.Data; // Use ADO.NET namespace using System.Data.SqlClient; // Use SQL Server data provider namespace . . .

2. Next, make the Main() method look like the following (note that it is just the same as DataReading except for the highlighted differences):

   static void Main(string[] args) { // Specify SQL Server-specific connection string SqlConnection thisConnection = new SqlConnection( @"Server=(local)\sqlexpress;Integrated Security=True;" + "Database=northwind"); // Open connection thisConnection.Open(); // Create command for this connection SqlCommand thisCommand = thisConnection.CreateCommand(); // Set command to Stored Procedure type. thisCommand.CommandType = CommandType.StoredProcedure; thisCommand.CommandText = "Ten Most Expensive Products"; // Execute DataReader for specified command SqlDataReader thisReader = thisCommand.ExecuteReader(); // While there are rows to read while (thisReader.Read()) { // Output product name and price columns Console.WriteLine("\t{0}\t{1}", thisReader["TenMostExpensiveProducts"], thisReader["UnitPrice"]); } // Close reader thisReader.Close(); // Close connection thisConnection.Close(); Console.Write("Program finished, press Enter/Return to continue:"); Console.ReadLine(); }

Press F5 to execute in the debugger; you see the results shown in Figure 24-18.

Figure 24-18

How It Works

This program opens the connection just as in previous examples. You create a SqlCommand object set the CommandType parameter to CommandType.StoredProcedure, an enumeration within ADO.NET for the purpose of supporting stored procedure calls. When CommandType is set to StoredProcedure, the CommandText holds the name of the stored procedure, not a SQL command. The rest of the program is exactly the same as if a SQL command were executed. The columns returned have different names, so the printout of the values differs slightly only for that reason.