- 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
Commands: Making Things Happen
The SqlClient provider implements the SqlCommand class to execute action statements and submit queries to the database. When you have created your connection, you can get the command object from the CreateCommand method on your connection, as the code in Listing 4-5 shows.
Listing 4-5: Create a command from the connection object
//get a command through CreateCommand
SqlConnection conn = new SqlConnection("context connection=true");
SqlCommand cmd = conn.CreateCommand();
Another way of getting to the command is to use one of the SqlCommand’s constructors, which Listing 4-6 shows.
Listing 4-6: Using SqlCommand’s constructor
//use constructor that takes a CommandText and Connection
string cmdStatement = "select * from authors";
SqlConnection conn = new SqlConnection("context connection=true");
SqlCommand cmd = new SqlCommand(cmdStatement, conn);
We have seen how a SqlCommand is created; now let’s look at what we can do with the command. Table 4-3 lists the public methods, properties, and events. (The table doesn’t show public members inherited from System.Object or the extra asynchronous versions of the execute-related methods.)
Table 4-3: Public Members of SqlCommand
|
Name |
Return Value/Type |
Member Type |
|
Constructor() |
|
Constructor |
|
Constructor(String) |
|
Constructor |
|
Constructor(String, SqlConnection) |
|
Constructor |
|
Constructor(String, SqlConnection, SqlTransaction) |
|
Constructor |
|
Cancel() |
void |
Method |
|
CreateParameter() |
SqlParameter |
Method |
|
Dispose() |
void |
Method |
|
ExecuteNonQuery() |
int |
Method |
|
ExecuteReader() |
SqlDataReader |
Method |
|
ExecuteReader(CommandBehavior) |
SqlDataReader |
Method |
|
ExecuteScalar() |
Object |
Method |
|
ExecuteXmlReader() |
XmlReader |
Method |
|
Prepare() |
void |
Method |
|
ResetCommandTimeout |
void |
Method |
|
CommandText |
String |
Property |
|
CommandTimeout |
int |
Property |
|
CommandType |
CommandType |
Property |
|
Connection |
SqlConnection |
Property |
|
Notification |
SqlNotificationRequest |
Property |
|
NotificationAutoEnlist |
Boolean |
Property |
|
Parameters |
SqlParameterCollection |
Property |
|
Transaction |
SqlTransaction |
Property |
|
UpdatedRowSource |
UpdateRowSource |
Property |
|
StatementCompleted |
StatementCompleted EventHandler |
Event |
For those of you who are used to the SqlClient provider, most of the members are recognizable, but as with the connection object when used inside SQL Server, there are some differences:
- The new asynchronous execution methods are not available when running on the server.
- You can have multiple SqlCommands associated with the special context connection, but cannot have multiple active SqlDataReaders at the same time on this connection. This functionality, known as multiple active resultsets (MARS), is available only when using the data provider from a client.
- You cannot cancel a SqlCommand inside a stored procedure using the SqlCommand’s Cancel method.
- SqlNotificationRequest and SqlDependency do not work with commands issued inside SQL Server.
When you execute parameterized queries or stored procedures, you specify the parameter values through the Parameters property of the SqlCommand class. This property can contain a SqlParameterCollection that is a collection of SqlParameter instances. The SqlParameter instance contains a description of the parameter and also the parameter value. Properties of the SqlParameter class include parameter name, data type (including precision and scale for decimal parameters), parameter length, and parameter direction. The SqlClient provider uses named parameters rather than positional parameters. Use of named parameters means the following:
- The parameter name is significant; the correct name must be specified.
- The parameter name is used as a parameter marker in parameterized SELECT statements, rather than the ODBC/OLE DB question-mark parameter marker.
- The order of the parameters in the collection is not significant.
- Stored procedure parameters with default values may be omitted from the collection; if they are omitted, the default value will be used.
- Parameter direction must be specified as a value of the ParameterDirection enumeration.
This enumeration contains the values Input, Output, InputOutput, and ReturnCode. Although Chapter 3 mentioned that in T-SQL, all parameters defined as OUTPUT can also be used for input, the SqlClient provider (and ADO.NET is general) is more precise. Attempting to use the wrong parameter direction will cause an error, and if you specify ParameterDirection.Output, input values will be ignored. If you need to pass in a value to a T-SQL procedure that declares it as OUTPUT, you must use Param-eterDirection.InputOutput. Listing 4-7 shows an example of executing a parameterized T-SQL statement.
Listing 4-7: Using a parameterized SQL statement
SqlConnection conn = new SqlConnection("context connection=true");
conn.Open();
SqlCommand cmd = conn.CreateCommand();
// set the command text
// use names as parameter markers
cmd.CommandText =
"insert into jobs values(@job_desc, @min_lvl, @max_lvl)";
// names must agree with markers
// length of the VarChar parameter is deduced from the input value
cmd.Parameters.Add("@job_desc", SqlDbType.VarChar);
cmd.Parameters.Add("@min_lvl", SqlDbType.TinyInt);
cmd.Parameters.Add("@max_lvl", SqlDbType.TinyInt);
// set values
cmd.Parameters[0].Value = "A new job description";
cmd.Parameters[1].Value = 10;
cmd.Parameters[2].Value = 20;
// execute the command
// should return 1 row affected
int rows_affected = cmd.ExecuteNonQuery();