Operations with Regular ADO.NET Objects

I have prepared a little overview of some other ADO.NET objects that are frequently used in managed code. If you already have experience with ADO.NET, you can skip over this section.

SqlCommand

SqlCommand is designed to execute against a SQL Server database, a Transact-SQL stored procedure, or a batch. These are set in the CommandText property, and then the SqlCommand can be executed using one of many methods.

Which methods you should use depends primarily on the type of result that you expect. ExecuteReader is used for stored procedures and batches that return a result. The result is in the form of a SqlDataReader object. ExecuteXmlReader is identical with the exception that it returns an XmlReader object. ExecuteScalar is designed for commands that return a result with a single value. ExecuteNonQuery is used for stored procedures and batches that do not return a result but only modify a database. There is an equivalent set of BeginExecute… and EndExecute… methods that is designed for asynchronous execution, but I do not think you will use them in CLR stored procedures.

It is possible to set the CommandText property to another value, once you finish working with the previous command instance.

If an error occurs during execution of a command, a SqlException is generated. If the error has a severity 20 or higher, SqlConnection will typically be closed. The user has to reopen the connection to continue.

SqlCommand and Parameters

The following example demonstrates how to call a stored procedure that has input and output parameters and a return value. To set these values you must utilize the SqlParameter class and add them to the Parameters collection of a SqICommand object:

 [SqlProcedure] public int ap_Eq_Insert(string Make, string Model, string EqType) { using (SqlConnection conn = new SqlConnection("Context Connection=true")) {    // ALTER procedure [dbo].[ap_Equipment_Insert]    //        @chvMake varchar(50),    //        @chvModel varchar(50),    //        @chvEqType varchar(50),    //        @intEqId int OUTPUT    conn. Open () ;    SqlCommand cmd = new SqlCommand("dbo.ap_Equipment_Insert", conn);    cmd.CommandType = CommandType.StoredProcedure;    cmd.Parameters.AddWithValue("@chvMake", Make);    cmd.Parameters["@chvMake"].Direction = ParameterDirection.Input;    cmd.Parameters.AddWithValue("@chvModel", Model);    cmd.Parameters["@chvModel"].Direction = ParameterDirection.Input;    cmd.Parameters.AddWithValue("@chvEqType", EqType);    cmd.Parameters["@chvEqType"].Direction = ParameterDirection.Input;    SqlParameter paramEgld = cmd.Parameters.Add("SintEqId", SqlDbType.Int);    paramEqId.Direction = ParameterDirection.Output;    SqlParameter paramRC = cmd.Parameters.Add("@return", SqlDbType.Int);    paramRC.Direction = ParameterDirection.ReturnValue;    //execute command    cmd.ExecuteNonQuery() ;    //return id      int returnValue = (int)paramRC.Value;      int EqId = (int)paramEqId.Value;      return EqId; } } 

I have added all input parameters to the Parameters collection using the AddWithValue method. The next line sets the direction of the parameter:

 cmd.Parameters.AddWithValue("@chvMake", Make); cmd.Parameters["@chvMake"].Direction = ParameterDirection.Input; 

Note 

Before .NET Framework 2.0, you could set the value of a parameter using the Add method. You may find some older ADO.NET examples on the Internet that are still doing this.

To add the output and return values, I will declare objects of the SqlParameter class:

 SqlParameter paramEqId = cmd.Parameters.Add("@intEqId", SqlDbType.Int); paramEqId.Direction = ParameterDirection.Output; SqlParameter paramRC = cmd.Parameters.Add("@return", SqlDbType.Int); paramRC.Direction = ParameterDirection.ReturnValue; 

References to these objects are needed after the stored procedure is executed to read the output and return values:

 int returnValue = (int)paramRC.Value; int EqId = (int)paramEqId.Value; 

SqlDataReader

SqlDataReader is designed to provide a forward-only stream of rows (cursor) from a SQL Server database. It cannot be created directly using only a constructor. It must be created using the ExecuteReader method of the SqlCommand class.

The fact that the class provides a forward-only stream of rows is significant because it will prevent you from doing anything else on the SqlConnection that it is based on, until you close the SqlDataReader object. Even the RecordsAffected property of SqlDataReader should be read only after executing the Close method.

The SqlDataReader class contains a Read method that reads the next row of the result. If the row is not available, the method will return False. Therefore, rows can be read one by one using, for example, a while statement:

 [SqlProcedure] public static void cp_EqType_GetCommaDelimList(out string EqType) // returns comma-delimited list of EqTypes // example of getting data from a stored procedure that returns result // example of using reader object {    using (SqlConnection conn = new SqlConnection("Context Connection=true"))   {      EqType = "";      // Set up the command object used to execute the stored proc SqlCommand cmd = new SqlCommand("dbo.ap_EqType_List", conn); cmd.CommandType = CommandType.StoredProcedure;      //execute sp      conn. Open () ;      using (SqlDataReader reader = cmd.ExecuteReader())      {         while (reader.Read()) // Advance one row, until you can            {               // Return output parameters from returned data stream              //id = reader.GetInt32(0); // do not need first column              EqType = EqType + reader.GetString(l) + ", ";            }      }   } } 

When a row is read, you can access the values in its columns using names or ordinal numbers. To avoid data type conversion, it is recommended that you use one of the special Get… functions (such as GetString, GetInt32, GetIntl6, and GetDateTime).