Use C # To create a stored procedure for SQL Server

Introduction
Typically, developers use T-SQL to create stored procedures, functions, and triggers for SQL Server. Currently, SQL Server 2005 fully supports the. NET Universal Language Runtime (CLR. This means that you can use. NET languages such as C # and VB. NET to develop SQL Server Stored Procedures, functions, and triggers. The integration of SQL Server and CLR brings us n many benefits, such as real-time compilation, type security, enhanced security, and enhanced programming model. In this article, I will show you how to use C # To create a stored procedure for SQL Server.

Background
When using SQL Server Stored Procedures, the most common task is to read or save data from the database. Its common applications are as follows:
· Execute some simple logic without any return values. No output parameters.
· Execute some logic and return the result through one or more output parameters.
· Execute some logic and return one or more records read from the table.
· Execute some logic and return one or more rows of records. These records are not read from the table, but custom data rows.

To demonstrate how to use C # To develop SQL Server Stored Procedures for these applications, I will give examples one by one.

Enable CLR Integration
Before using C # To write a stored procedure, you must enable the CLR integration feature of your SQL Server. It is disabled by default. Open your SQL Server Management studio and execute the following script.
Sp_configure 'clr enabled', 1
Go
Reconfigure
Go
Here, we run the system stored procedure "sp_configure", which provides two parameters: "CLR enabled" and "1 ". To disable CLR integration, the stored procedure is executed, but the second parameter must be changed to "0. In addition, do not call "reconfigure" to make the new settings work ".

SQL Server Project
Open Visual Studio and select "new project" from the File menu ". In the Create Project dialog box, select database under Visual C ". Select the "SQL server project" template.

After the project name is set, click "OK.

Soon, the project you created requires you to select an SQL Server database.

Follow the prompts to proceed step by step. Even if you choose to cancel, you can select a database again in the "project"-"properties" dialog box. For example, if you have a northwind database on your computer, select it in the "Create Database reference" dialog box and click "OK. After that, the SQL server project will write the stored procedure we developed into this database during deployment (you will know what is going on later ).

Next, right-click the project you created and choose add> stored procedure ". The dialog box shown in is displayed:

Select the "Stored Procedure" template and enter a proper name. Then, click the "add" button.

After adding the namespace, you will find that this is actually a class that has been imported into the needed namespace.
Using system;
Using system. Data;
Using system. Data. sqlclient;
Using system. Data. sqltypes;
Using Microsoft. sqlserver. server;
Note the expanded namespace (the last two using ). The system. Data. sqltypes namespace contains many different types, which can be used to replace the Data Types of SQL Server. The classes in the Microsoft. sqlserver. Server namespace are responsible for CLR integration of SQL Server.

Stored Procedure with no return value
In this section, we will see how to write a stored procedure that executes some logic but does not return any values or output parameters. In this example, a stored procedure named "changecompanyname" is created to modify the value of the companyName field in the MERs table. This stored procedure requires two parameters: customerid (ID of the customer whose company name needs to be changed) and companyName (new company name ). After the changecompanyname stored procedure is completed, the Code is as follows:
[Sqlprocedure]
Public static void changecompanyname
(Sqlstring customerid, sqlstring companyName)
{
Sqlconnection CNN = new sqlconnection
("Context connection = true ");
CNN. open ();
Sqlcommand cmd = new sqlcommand ();
Cmd. Connection = CNN;
Cmd. commandtext = "Update MERs set
CompanyName = @ P1 where customerid = @ p2 ";
Sqlparameter p1 = new sqlparameter ("@ p1", companyName );
Sqlparameter P2 = new sqlparameter ("@ p2", customerid );
Cmd. Parameters. Add (P1 );
Cmd. Parameters. Add (P2 );
Int I = cmd. executenonquery ();
CNN. Close ();
Sqlcontext. Pipe. Send (I. tostring ());
}
Take a closer look at the changecompanyname () method. It is a static method and has no return value (void ). It requires two parameters named mermerid and companyName. Note that the data types of these two parameters are both sqlstring. Sqlstring can be used to replace the nvarchar data type in SQL Server. This method is modified using a [sqlprocedure] attribute. This attribute is used to mark the changecompanyname () method as an SQL server stored procedure.

In the method, we create a sqlconnection object and set its connection string to "context connection = true ". "Context connection" allows you to use the user currently logged on to the database as verification information for your login database. In this example, the changecompanyname () method is converted to a stored procedure and then saved to the northwind database. Therefore, the "context connection" here refers to the northwind database. In this way, you do not need to write any verification information about the login database.

Next, open the database connection. Set the connection and commandtext attributes of the sqlcommand object to perform the update operation. At the same time, we also need to set two parameters. In this way, you can execute the update operation by calling the executenonquery () method. Next, close the connection.

Finally, the return value of the executenonquery () method is sent to the client. You can also skip this step. Now let's take a look at the use of the sqlcontext class. The sqlcontext class is used to transmit processing results between the server and the client. In this example, the send () method is used to send a string to the caller.

Returns the stored procedure of one or more records read from the table.
When using stored procedures, we often select one or more records. You can create a stored procedure in two ways.

First, create a method named getallcustomers (). The Code is as follows:
[Sqlprocedure]
Public static void getallcustomers ()
{
Sqlconnection CNN = new sqlconnection
("Context connection = true ");
CNN. open ();
Sqlcommand cmd = new sqlcommand ();
Cmd. Connection = CNN;
Cmd. commandtext = "select * from MERs ";
Sqldatareader reader = cmd. executereader ();
Sqlcontext. Pipe. Send (Reader );
Reader. Close ();
CNN. Close ();
}
The getallcustomers () method uses a [sqlprocedure] attribute for modification. Create a sqlconnection and a sqlcommand object in the method. Then execute the SELECT statement using the executereader () method. Next, use the send () method to send the obtained sqldatareader data to the client. The last step is to disable sqldatareader and sqlconnection. In this method, it is the sqldatareader we have created. In fact, we can also hand over this task to the sqlcontext class for completion. The Code is as follows:
[Sqlprocedure]
Public static void getcustomerbyid
(Sqlstring customerid)
{
Sqlconnection CNN = new sqlconnection
("Context connection = true ");
CNN. open ();
Sqlcommand cmd = new sqlcommand ();
Cmd. Connection = CNN;
Cmd. commandtext = "select * from MERs
Where customerid = @ p1 ";
Sqlparameter p1 = new sqlparameter ("@ p1", customerid );
Cmd. Parameters. Add (P1 );
Sqlcontext. Pipe. executeandsend (CMD );
CNN. Close ();
}
The getcustomerbyid () method requires a parameter-customerid, which returns a record of a customer from the MERs table. Except for the executeandsend () method, you should be familiar with the code in this method. The executeandsend () method receives a sqlcommand object as a parameter. When executed, it returns the dataset to the client.

Stored Procedure with output parameters
When using stored procedures, we often return a calculated value through output parameters. So, let's take a look at how to create a stored procedure with one or more output parameters.
[Sqlprocedure]
Public static void getcompanyname
(Sqlstring customerid, out sqlstring companyName)
{
Sqlconnection CNN = new sqlconnection
("Context connection = true ");
CNN. open ();
Sqlcommand cmd = new sqlcommand ();
Cmd. Connection = CNN;
Cmd. commandtext = "select companyName from
Customers where customerid = @ p1 ";
Sqlparameter p1 = new sqlparameter
("@ P1", customerid );
Cmd. Parameters. Add (P1 );
Object OBJ = cmd. executescalar ();
CNN. Close ();
CompanyName = obj. tostring ();
}
This is a method named getcompanyname (). It requires two parameters. The first parameter is mermerid, which is an input parameter; the second parameter is companyName, which is an output parameter (specified by the keyword out ). Both parameters are of the sqlstring type. The getcompanyname () method receives a customerid parameter and returns companyName (as the output parameter ).

The code in this method first sets the sqlconnection and sqlcommand objects. Then, execute the SELECT statement using the executescalar () method. The value returned by the executescalar () method is an object type, which is actually the company name. Finally, set the output parameter companyName to this value.

Returns the stored procedure of one or more rows of custom data.
When we use stored procedures, we read more data from some tables. However, in some cases, the data we need may not be in any table. For example, you may generate a data table based on some computations. Because its data is not obtained from the table, the above method is not applicable. Fortunately, the CLR integration feature of SQL Server provides us with a solution to this problem. See the following code:
[Sqlprocedure]
Public static void getcustomrow ()
{
Sqlmetadata [] metadata = new sqlmetadata [2];
Metadata [0] = new sqlmetadata
("Customerid", sqldbtype. nvarchar, 50 );
Metadata [1] = new sqlmetadata
("CompanyName", sqldbtype. nvarchar, 50 );
Sqldatarecord record = new sqldatarecord (metadata );
Record. setstring (0, "alfki ");
Record. setstring (1, "Alfreds futterkiste ");
Sqlcontext. Pipe. Send (record );
}
The getcustomrow () method returns a record and sends it to the client. This method first declares a sqlmetadata object. You can use this sqlmetadata class when you want to use a Custom column. In our example, two columns of the nvarchar type and the length is 50 are created. Then a sqldatarecord object is created. The sqldatarecord class can be used to represent a custom row. Its constructor requires a sqlmetadata array as the parameter. The setstring () method of the sqldatarecord object is used to set the column value. In addition, there are many different methods like setstring (), which can be used to process different data types. Finally, call the send () method to send the sqldatarecord object to the client.

In the preceding example, only one row of data is returned to the caller. What if multiple rows are returned? See the following code:
[Sqlprocedure]
Public static void getmultiplecustomrows ()
{
Sqlmetadata [] metadata = new sqlmetadata [2];
Metadata [0] = new sqlmetadata
("Customerid", sqldbtype. nvarchar, 50 );
Metadata [1] = new sqlmetadata
("CompanyName", sqldbtype. nvarchar, 50 );
Sqldatarecord record = new sqldatarecord (metadata );
Sqlcontext. Pipe. sendresultsstart (record );
Record. setstring (0, "alfki ");
Record. setstring (1, "Alfreds futterkiste ");
Sqlcontext. Pipe. sendresultsrow (record );
Record. setstring (0, "anasd ");
Record. setstring (1, "Ana Trujillo emparedados y helados ");
Sqlcontext. Pipe. sendresultsrow (record );
Sqlcontext. Pipe. sendresultsend ();
}
The getmultiplecustomrows () method returns multiple sqldatarecord objects to the client. The values for creating custom columns and setting columns are the same as those in the previous example. However, we use the sendresutlsstart () method to transmit data. The sendresultsrow () method also sends a sqldatarecord object to the client, but we can call it multiple times to send multiple records. Finally, call the sendresultsend () method to mark the completed data transmission operation.

We have developed the stored procedure. Now you can compile this project into an assembly (. dll ). However, our work has not ended yet. We also need to deploy this Assembly and stored procedures to the SQL Server database. There are two ways to do this-manual and automatic. Manually register your assembly with T-SQL statements and deploy the stored procedure to the SQL Server database. In this example, I will use an automatic method to deploy the stored procedure to the SQL Server database.

Right-click your project and select "deployment" from the menu.

In this way, you can automatically register the Assembly and deploy the stored procedure. Note that the "deployment" option appears only when you add a database reference when creating a project. If you cannot add a database reference for some reason, you can set it in the Project Properties dialog box.

If you view the northwind database in SQL Server Management studio, you can see similar results.

Note that all the methods we created (with the "Lock" icon) appear under the Stored Procedure node, and our Assembly appears under the Assembly node.

That's all. It's easy. Now you can call these stored procedures in your program. You can also test them in SQL Server Management studio.

 

Original article: http://www.knowsky.com/345516.html