HOUR 19. C# Special Topics

Just as there are special techniques available in Visual Basic .NET, these same techniques are available with C#. Using these techniques you can write more robust and powerful stored procedures. In this hour you’ll learn:

• How to work with temporary tables

• How to add error handling to your stored procedures

• How to use transaction processing within your stored procedures

---

Working with Temporary Tables

---

By the Way

The code and text in this hour are very similar to the code and text found in Hour 17. The intention is to show you the similarities and differences between the Visual Basic .NET examples and the C# examples.

---

Temporary tables can be useful in certain situations. When you need them they are invaluable. They can contain data from heterogeneous data sources (even a text file). Using temporary tables, you have access to methods that facilitate data access and retrieval. These methods enable you to sort, filter, index, and select their rows.

The stored procedure found in Listing 19.1 first opens a connection. It then builds a string. The string first defines the structure of a temporary table. You know that the table is temporary because of the pound sign (#) preceding the table name. The string then contains an INSERT INTO statement that inserts into the temporary table the results of selecting data from the Person.Contact table. Finally, the SQL statement selects the contents of the LastName and FirstName fields from the temporary table, ordering the results by LastName and FirstName.

After building the string, the code instantiates a command object, passing it the SQL statement along with a reference to the open connection. The ExecuteReader method of the command object executes the SQL statement contained in the command object, returning the result to the SqlDataReader object (rdr). Finally, the code sends the SqlDataReader object, via a pipe, back to SQL Server. If you had selected data from the Person.Contact table, because no sort was applied, the data would have appeared in ContactID order. Because you selected the data from the temporary table, ordering the results by LastName combined with FirstName, the data appears in the specified order. You can find this code in the FirstSQLCLRClass solution, located on the sample code disk. The code is contained in the class called FirstSQLCLR.

LISTING 19.1. A Procedure That Uses a Temporary Table

---

public static void UsingTemporaryTables()
{
    using (SqlConnection conn =
        new SqlConnection("context connection=true"))
    {
        conn.Open();
         string sql = "CREATE TABLE #TempContacts";
        sql += "        (ContactID int NOT NULL PRIMARY KEY, ";
        sql += "        LastName varchar(50),";
        sql += "        FirstName varchar(50))";
        sql += "  INSERT INTO #TempContacts ";
        sql += "  (ContactID, LastName, FirstName) ";
        sql += "  SELECT Person.Contact.ContactID, ";
        sql += "  Person.Contact.LastName, Person.Contact.FirstName ";
        sql += "  FROM Person.Contact";
        sql += "  SELECT LastName, FirstName ";
        sql += "  FROM #TempContacts ORDER BY LastName, FirstName";

        SqlCommand cmd = new SqlCommand(sql, conn);
         SqlDataReader rdr = cmd.ExecuteReader();
        SqlContext.Pipe.Send(rdr);
   }
}

---

You must place the function in a C# class, and then build the solution to create a DLL. After you create the DLL you are ready for the next step. If you have not yet registered the firstsqlclrclass.dll, the next step is to register the assembly in SQL Server Management Studio Express. This process was covered in Hour 17.

If you try to register an assembly, and it is already registered, you get an error. You must first delete the assembly and then reregister it. Before you can delete an assembly, you must first delete all the stored procedures within it. In summary, when you update a DLL, you must go through the process of deleting the stored procedures, deleting the assembly, adding the assembly, and adding the stored procedures. You can easily use a SQL script to help you with this process. Note that if available, the assembly is listed under the Assemblies node (see Figure 19.1).

Figure 19.1. After the query has executed, the assembly is listed under the Assemblies node.

After you have ensured that the assembly is registered, you are ready to add any of its functions as stored procedures within SQL Server. The code found in Listing 19.2 accomplishes this task.

LISTING 19.2. Creating the Stored Procedure Called TemporaryTables

---

CREATE PROCEDURE TemporaryTables
AS
EXTERNAL NAME
FirstSQLCLRClass.[FirstSQLCLRClass.FirstSQLCLR].UsingTemporaryTables

---

---

By the Way

You may find the EXTERNAL NAME syntax used here does not work. If that is the case, use the following syntax: FirstSQLCLRClass.FirsSQLCLR.UsingTemporaryTables

---

You execute the stored procedure as you would any stored procedure (see Listing 19.3).

LISTING 19.3. Executing the Stored Procedure Called FirstListEmployees

---

use Adventureworks
Go
exec TemporaryTables

---

As shown in Listing 19.1, the stored procedure returns a SQLDataReader with the last name and first name from the Contact table, ordering the result set by last name combined with first name. The results appear in Figure 19.2.

Figure 19.2. The stored procedure returns data sorted by last name and first name.

Including Error Handling in the Stored Procedures That You Build

All stored procedures should contain error handling. In this way you control what happens when an error occurs. In Listing 19.4 the code first evaluates the groupName parameter to determine whether it is Null. If it is Null, the code sends the text Group Name must be filled in. back to SQL Server. Otherwise the code instantiates a new connection. It opens the connection. Next it builds a string containing a SQL IF statement. The statement evaluates the DepartmentName parameter. If it is Null, it builds a SELECT statement, selecting the text No Department Name and DepartmentID = 0. Otherwise (Department Name Not Null) the code builds an INSERT INTO statement that inserts data into the HumanResources.Department table and a SELECT statement that selects the message Success and the DepartmentID of @@Identity. @@Identity is a system variable that contains the primary key value of the last inserted row. Finally, the code instantiates a command object, setting its parameters to the parameter values passed into the stored procedure. It uses the execute method of the command object to execute the stored procedure, sending the results back to SQL Server.

LISTING 19.4. A Stored Procedure Containing Error Handling

---

public static void HandlingRuntimeErrors
    (System.Data.SqlTypes.SqlString departmentName,
    System.Data.SqlTypes.SqlString groupName)
{
    if (groupName.IsNull)
        // Error handled in C#
        SqlContext.Pipe.Send("Group Name must be filled in.");
    else
    {
        using (SqlConnection conn =
            new SqlConnection("context connection=true"))
        {
            conn.Open();
            // Error handled in T-SQL
            string sql = "IF @DepartmentName Is Null ";
            sql += "    BEGIN ";
            sql += "        SELECT Message = 'No Department Name', ";
            sql += "        DepartmentID = 0";
            sql += "    END ";
            sql += "ELSE ";
            sql += "    BEGIN ";
            sql += "        INSERT INTO HumanResources.Department";
            sql += "        (Name, GroupName) VALUES ;
            sql += "        (@DepartmentName, @GroupName) ";
            sql += "        SELECT Message = 'Success', ;
            sql += "        DepartmentID = @@Identity ";
            sql += "    END ";

            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.Parameters.Add(new SqlParameter
                ("@DepartmentName", System.Data.SqlDbType.VarChar, 50));
            cmd.Parameters[0].Value = departmentName;
            cmd.Parameters.Add(new SqlParameter
                ("@GroupName", System.Data.SqlDbType.VarChar, 50));
            cmd.Parameters[1].Value = groupName;
             SqlDataReader rdr = cmd.ExecuteReader();
            SqlContext.Pipe.Send(rdr);
        }
   }
}

---

Because the assembly was created earlier, there is no need to take that step again. It is necessary that you define the stored procedure within SQL Server Management Studio Express. The code in Listing 19.5 accomplishes this task. Notice that the code creates a SQL Server procedure called HandlingErrors based on the .NET procedure called HandlingRuntimeErrors.

---

By the Way

If you are creating all the examples yourself, you need to first rebuild the solution. You must then unregister and reregister the assembly in Management Studio Express so that it can see the new procedure. It is important to note that you will not be able to unregister the assembly until you delete all its stored procedures from the list of stored procedures in Management Studio Express. If you are using the sample code provided with this book, it will not be necessary for you to take any of these steps.

---

LISTING 19.5. Creating the Stored Procedure Called SecondListEmployees

---

CREATE PROCEDURE HandlingErrors
(@DepartmentName NVarChar(20), @GroupName NVarChar(2))
AS
EXTERNAL NAME
FirstSQLCLRClass.[FirstSQLCLRClass.FirstSQLCLR].HandlingRuntimeErrors

---

You must pass the HandlingErrors stored procedure two parameters when you execute it (see Listing 19.6). The first is the department name, and the second is the group name. The results of passing Widget Department and Null as parameters the first time, and then Null and Manufacturing as parameters the second time appear in Figure 19.3 and Figure 19.4.

LISTING 19.6. Executing the Stored Procedure Called HandlingErrors

---

Use AdventureWorks
Go
Exec HandlingErrors 'Widget Department', Null
Go
Exec HandlingErrors Null, 'Manufacturing'

---

Figure 19.3. After parameters are passed to the stored procedure, the output appears with appropriate errors on the Results tab.

Figure 19.4. After parameters are passed to the stored procedure, the output appears with appropriate errors on the Messages tab.

Notice that whereas the Send method sends the specified text to the Messages pane (see Figure 19.3), it sends the DataReader object to the Results pane (see Figure 19.4).

Sometimes you may actually want to raise errors in your code. You may want to do this to simulate an error condition in your application, or to treat a user error like a system error, passing it through your standard error handler. Listing 19.7 provides an example. The procedure receives two parameters, departmentName and groupName. It instantiates and opens a connection. It then builds a SQL statement that inserts data into the HumanResources.Department table, using the parameter values passed to the procedure. Next it sets DepartmentID equal to the value of @@Identity, the system variable containing the primary key value of the last inserted row. After the procedure builds the SQL statement, it declares and instantiates a command object. It then adds parameters to the command object, setting their values to the values passed in as parameters to the procedure. Next it uses the ExecuteAndSend method of the SqlContext.Pipe object, passing it the command object, to return the results of the SQL statement to SQL Server. The difference between this procedure and the HandlingErrors procedure lies in the Catch block. If an error occurs, the code uses the Send method of the SqlContext.Pipe object to send a custom error message back to SQL Server.

LISTING 19.7. Sending a Custom Error Message to SQL Server

---

public static void RaisingErrors
    (System.Data.SqlTypes.SqlString departmentName,
    System.Data.SqlTypes.SqlString groupName)
{
    try
    {
        using (SqlConnection conn =
            new SqlConnection("context connection=true"))
        {
            conn.Open();
            string sql = "SET NOCOUNT ON ";
            sql += "INSERT INTO HumanResources.Department(Name, GroupName) ";
            sql += "VALUES (@DepartmentName, @GroupName) ";
            sql += "SELECT DepartmentID = @@Identity";

            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.Parameters.Add(new SqlParameter("@DepartmentName",
                System.Data.SqlDbType.VarChar, 50));
            cmd.Parameters[0].Value = departmentName;
            cmd.Parameters.Add(new SqlParameter("@GroupName",
                System.Data.SqlDbType.VarChar, 50));
            cmd.Parameters[1].Value = groupName;
             SqlContext.Pipe.ExecuteAndSend(cmd);
        }
   }
   catch (Exception ex)
   {
       // This error will get sent, rather than the other,
       // to suppress this, comment out the next line.
       SqlContext.Pipe.Send
      ("An exception occurred during the execution of RaisingErrors " +
      ex.ToString());
   }

---

Before executing the stored procedure, you must define it within SQL Server Management Studio Express. The code in Listing 19.8 accomplishes this task. It creates a procedure called RaisingErrors, based on the .NET function called RaisingErrors.

LISTING 19.8. Adding the Procedure Called RaisingErrors to SQL Server

---

Use AdventureWorks
Go
CREATE PROCEDURE RaisingErrors(@departmentName NVarChar(50), @groupName
NVarChar(50))
AS
EXTERNAL NAME FirstSQLCLRClass.[FirstSQLCLRClass.FirstSQLCLR].RaisingErrors

---

Now that you have defined the stored procedure to SQL Server, you are ready to execute it. Listing 19.9 provides an example. It passed Widget Department and Null as parameters to the stored procedure. Figure 19.5 shows the resulting error message. Notice that the highlighted text contains the text that the procedure sent when it raised the error.

LISTING 19.9. Executing the Procedure Called RaisingErrors

---

Use AdventureWorks
Go
Exec RaisingErrors 'Widget Department', Null

---

Figure 19.5. The output appears with the custom error message.

Using Transactions to Protect Your Data

Sometimes you want to ensure that a series of statements execute completely or not at all. For example, if you were performing a bank transaction, you would want to ensure that both the debit and credit occurred before committing the data. Transactions can help you solve this dilemma. Listing 19.10 provides an example.

The code begins by using a try...catch block to establish error handling. It then declares and instantiates a connection object. It opens the connection and then begins to build a SQL string. The SQL string first declares three variables: @DepartmentID, @LocalError, and @LocalRows. The code then adds a BEGIN TRANSACTION statement to the SQL string. From this point on, everything that follows is part of a transaction. Next the code adds an INSERT INTO statement to the SQL statement, inserting the parameter values into the appropriate fields in the HumanResources.Department table. The code immediately tests to see whether an error occurred by testing the values of the system variables @@Error and @@Rowcount. @@Error contains the number of the error that occurred, and @@Rowcount contains the number of rows affected by the SQL statement that immediately precedes it.

After the error number and the number of rows affected have been captured, the code evaluates them to determine whether their values are indicative of problems. A non-zero error number means that an error occurred. Zero rows affected also means that an error occurred. If @LocalError is non-zero or @LocalRows is zero, a ROLLBACK TRANSACTION occurs. The ROLLBACK TRANSACTION obliterates any processing that occurred since the BEGIN TRANSACTION statement. An END statement then terminates processing. Otherwise, the code attempts to commit the transaction. It sets the DepartmentID equal to the value of the system variable @@Identity. It sets the value of Error equal to zero and the value of NumRows equal to @LocalRows.

After the SQL statement is built, it is time to execute it. The code declares and instantiates a command object. It then adds two parameters to the command object, one for departmentName and one for groupName. The code uses the ExecuteAndSend method of the SQLContext.Pipe object to execute the SQL statement, returning the results to SQL Server.

If an error occurs during processing, execution jumps to the Catch block. The Catch block sends the message Transaction Rolled Back to SQL Server. If no error occurs, the COMMIT TRAN statement commits the data to disk.

LISTING 19.10. A Stored Procedure That Receives Input Parameters

---

public static void UsingTransaction
    (System.Data.SqlTypes.SqlString departmentName,
    System.Data.SqlTypes.SqlString groupName)
{
    try
    {
        using (SqlConnection conn = new SqlConnection
            ("context connection=true"))
        {
            conn.Open();
            string sql = "SET NOCOUNT ON ";
            sql += "DECLARE @DepartmentID int, @LocalError int, @LocalRows int ";

            sql += "BEGIN TRANSACTION ";
            sql += "INSERT INTO HumanResources.Department(Name, GroupName) ";
            sql += "VALUES (@DepartmentName, @GroupName) ";
            sql += "SELECT @LocalError = @@Error, @LocalRows = @@RowCount ";
            sql += "IF NOT @LocalError = 0 or @LocalRows = 0 ";
            sql += "    BEGIN ";
            sql += "        ROLLBACK TRANSACTION ";
            sql += "        SELECT DepartmentID = Null, Error = @LocalError, ";
            sql += "        NumRows = @LocalRows ";
            sql += "    END ";
            sql += "ELSE ";
            sql += "    BEGIN ";
            sql += "        COMMIT TRAN ";
            sql += "     SELECT @DepartmentID = @@Identity ";
            sql += "        SELECT DepartmentID = @DepartmentID, ";
            sql += "        Error = 0, NumRows = @LocalRows ";
            sql += "    END ";

            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.Parameters.Add(new SqlParameter("@DepartmentName",
                System.Data.SqlDbType.VarChar, 50));
            cmd.Parameters[0].Value = departmentName;
            cmd.Parameters.Add(new SqlParameter("@GroupName",
                System.Data.SqlDbType.VarChar, 50));
            cmd.Parameters[1].Value = groupName;

             SqlContext.Pipe.ExecuteAndSend(cmd);

       }
   }
   catch (Exception ex)
   {
       // This error will get sent, rather than the other,
       // to suppress this, comment out the next line.
       SqlContext.Pipe.Send("Transaction Rolled Back");
   }

}

---

As usual, you must first create the stored procedure within SQL Server Management Studio Express before you can execute it. Listing 19.11 provides an example. Note that when you declare the stored procedure, you declare the name of the parameter and its type.

LISTING 19.11. Creating a Stored Procedure That Contains Transactions

---

Use AdventureWorks
Go
CREATE PROCEDURE UsingTransaction(@departmentName NVarChar(50), @groupName
NVarChar(50))
AS
EXTERNAL NAME FirstSQLCLRClass.[FirstSQLCLRClass.FirstSQLCLR].UsingTransaction

---

To execute the stored procedure, you must pass the designated parameter(s). Listing 19.12 provides an example. It first passes 'Bolt Creation' and 'Manufacturing' to the stored procedure. This should successfully insert a row into the HumanResources.Department table (see Figure 19.6). If you execute the stored procedure a second time, you will get an error and the code will roll back the transaction (see Figure 19.7). This is because the table does not allow duplicate department names.

Figure 19.6. The stored procedure executes successfully.

Figure 19.7. The stored procedure executes with an error and the transaction is rolled back.

LISTING 19.12. Executing a Stored Procedure That Receives Input Parameters

---

Use AdventureWorks
Go
Exec UsingTransaction 'Widget Creation', 'Manufacturing'

---

Summary

Several techniques help to make the stored procedures that you write more powerful and robust. These techniques include the use of temporary tables, error handling, and transaction processing in the stored procedures that you build. In this hour you learned these three invaluable techniques.

Q&A

Workshop

Quiz

Quiz Answers

Activities

Create, build, declare, and execute a stored procedure that inserts data into the Purchasing.Vendor table. Make sure that you write code to insert data into all the columns because all but one of the fields is required. Add error handling and transaction processing to the stored procedure. Make sure that one row is affected and that no error occurred before committing the transaction. Return an error message back to SQL Server when an error occurs.