Database Reference
In-Depth Information
SqlParameter parmCurrencyCode = new SqlParameter("@CCode", SqlDbType.NVarChar, 3);
SqlParameter parmCurrencyName = new SqlParameter
("@Name", SqlDbType.NVarChar, 50);
parmCurrencyCode.Value = currencyCode;
parmCurrencyName.Value = currencyName;
cmdInsertCurrency.Parameters.Add(parmCurrencyCode);
cmdInsertCurrency.Parameters.Add(parmCurrencyName);
After setting the parameters, you will set the INSERT statement, which will perform the actual task,
but because you chose only two parameters for this Sales.Currency table, for the third column, which is
a date column, you will pass the GetDate() function.
cmdInsertCurrency.CommandText ="INSERT Sales.Currency
(CurrencyCode, CurrencyName, ModifiedCurrencyDate)" +
" VALUES(@CCode, @Name, GetDate())";
Next, open the connection and execute the command.
conn.Open();
cmdInsertCurrency.ExecuteNonQuery();
The most important point to remember is that this code will actually be invoked from inside SQL
Server Management Studio, so the exception handling catch block will need extra attention.
catch (SqlException ex)
{
SqlContext.Pipe.Send("An error occured" + ex.Message + ex.StackTrace);
}
The SqlContext class allows you to invoke function to show errors in SQL Server's Error window.
Deploying a SQL CLR Stored Procedure into SQL Server
Once an assembly of a SQL CLR C# type for a particular type of database object is created, it needs to be
deployed in SQL Server. Once deployed, SQL Server uses it like any other T-SQL database object.
Try It: Deploying SQL CLR C# Stored Procedure in SQL Server
In this exercise, you'll deploy the created assembly into the SQL2012Db database, and upon execution,
this will insert currency into the AdventureWorks.Sales.Currency table.
1. Open SQL Server 2012 Management Studio, and connect to the SQL Server.
2. Select the SQL2012 database (if you don't have this database, you can use any
database of your choice), and click New Query, which will open a new blank
query window.
3. In the opened query window, insert the code in Listing 20-2.
 
Search WWH ::




Custom Search