- Programming with SqlClient
- Context: The SqlContext Class
- Connections
- Commands: Making Things Happen
- Obtaining Results
- Transactions
- Pipe
- Creating and Sending New Rowsets
- Using the WindowsIdentity
- Calling a Web Service from SQLCLR
- Exception Handling
- SqlTriggerContext
- SqlClient Classes That You Can’t Use on the Server
- Where Are We?
This chapter is from the book
This chapter is from the book
This chapter is from the book
Obtaining Results
Execution of SQL commands can return the following:
- A numeric return code
- A count of rows affected by the command
- A single scalar value
- One or more multirow results using SQL Server’s default (cursorless) behavior
- A stream of XML
Some commands, such as a command that executes a stored procedure, can return more than one of these items—for example, a return code, a count of rows affected, and many multirow results. You tell the provider which of these output items you want by using the appropriate method of SqlCommand, as shown in Table 4-4.
When you return data from a SELECT statement, it is a good idea to use the lowest-overhead choice. Because of the amount of internal processing and the number of object allocations needed, ExecuteScalar may be faster than ExecuteReader. You need to consider the shape of the data that is returned, of course. Using ExecuteReader to return a forward-only, read-only cursorless set of results is always preferred over using a server cursor. Listing 4-8 shows an example of when to use each results-returning method.
Listing 4-8: Returning rows with SqlClient
SqlConnection conn = new SqlConnection("context connection=true");
conn.Open();
SqlCommand cmd = conn.CreateCommand();
// 1. this is a user-defined function
// returning a single value (authorname) as VARCHAR
cmd.CommandText = "GetFullAuthorNameById";
// required from procedure or UDF
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@id", "172-32-1176");
String fullname = (String)cmd.ExecuteScalar();
// use fullname
cmd.Parameters.Clear();
// 2. returns one row
cmd.CommandText = "GetAuthorInfoById";
// required from procedure or UDF
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@id", "172-32-1176");
SqlDataReader rdr1 = cmd.ExecuteReader();
// use fields in SqlDataReader
rdr1.Close();
cmd.Parameters.Clear();
// 3. returns multiple rows
cmd.CommandText = "select * from authors";
cmd.CommandType = CommandType.Text;
SqlDataReader rdr2 = cmd.ExecuteReader();
while (rdr2.Read())
// process rows in SqlDataReader
{ }
rdr2.Close();
SqlDataReader encapsulates multiple rows that can be read in a forward-only manner. You move to the next row in the set by using the SqlDataReader’s Read() method, as shown in Listing 4-8. After you call ExecuteReader, the resultant SqlDataReader is positioned before the first row in the set, and an initial Read positions it at the first row. The Read method returns false when there are no more rows in the set. If more than one rowset is available, you move to the next rowset by calling SqlDataReader’s NextResult method. While you are positioned on a row, the IDataRecord interface can be used to read data. You can use loosely typed ordinals or names to read the data in single columns. Using ordinals or names is a syntactic shortcut to using IDataRecord.GetValue(n). This returns the value as a .NET Framework System.Object, which must be cast to the correct type.
Table 4-4: How to Obtain Different Result Types
|
Result Desired |
Mechanism to Obtain It |
|
Return code |
Parameter with ParameterDirection of ReturnCode |
|
Count of rows affected |
Returned value from SqlCommand.ExecuteNonQuery or Use SqlCommand.ExecuteReader and SqlDataReader.RecordsAffected |
|
Scalar value |
Use SqlCommand.ExecuteScalar |
|
Cursorless mode results |
Use SqlCommand.ExecuteReader |
|
XML stream |
Use SqlCommand.ExecuteXmlReader |
If you know the data type of the value, you can use more strongly typed column accessors. Both SQL Server providers have two kinds of strongly typed accessors. IDataReader.GetDecimal(n) is an example; this returns the value of the first column of the current row as a .NET Framework System.Decimal data type. If you want full SQL Server type fidelity, it is better to use SqlDataReader’s SQL Server–specific accessors, such as IDataReader.GetSqlDecimal(n); these return instances of structures from the System.Data.SqlTypes namespace. These types are isomorphic with SQL Server data types; examples of their use and reasons why they are preferable to the .NET Framework base data types when used inside the server are covered in Chapter 3. Listing 4-9 shows an example of using each type.
Listing 4-9: Getting column values from a SqlDataReader
SqlConnection conn = new SqlConnection("context connection=true");
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "select * from authors";
cmd.CommandType = CommandType.Text;
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read() == true)
{
string s;
// 1. Use ordinals or names
// explicit casting, if you know the right type
s = (string)rdr[0];
s = (string)rdr["au_id"];
// 2. Use GetValue (must cast)
s = (string)rdr.GetValue(0);
// 3. Strong typed accessors
s = rdr.GetString(0);
// 4. Accessors for SqlTypes
SqlString s2 = rdr.GetSqlString(0);
}
Although you can process results obtained inside .NET Framework procedural code, you can also pass these items back to the client. This is accomplished through the SqlPipe class, which is described later in the chapter. Note that each of the classes returns rows, which must be processed sequentially; these results cannot be updated in place.