C # and SQL Server Stored Procedures (created): use C # To create SQL server stored procedures.

From: http://blog.csdn.net/hillspring/article/details/2304900

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.

EnableCLRIntegration

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 ServerProject

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 database named "beifeng trading" 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.

Note: ThisProgramOperate the "North Wind trade" database and "Order Master" table. The fields in the table are: Order Number in sequence.(INT), Delivery City(Nvarchar (15 ))...Other fields are omitted.

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 "changecityname" is created to modify the value of the "Shipping City" field in the "ordering main file" table. This stored procedure requires two parameters: ID (the order number ID of the shipping city needs to be changed) and City (the new "Shipping City "). After the changecityname stored procedure is completeCodeAs follows:(Procedure1)

[Microsoft. sqlserver. server. sqlprocedure]

Public static void changecityname (sqlint32 ID, sqlstring City)

{

// Place the code here

Sqlconnection con = new sqlconnection ("context connection = true ");

Con. open ();

Sqlcommand cmd = new sqlcommand ();

Cmd. Connection = con;

Cmd. commandtext = "Update order main file set delivery city = @ city where order number = @ ID ";

Sqlparameter p1 = new sqlparameter ("@ City", city );

Sqlparameter P2 = new sqlparameter ("@ ID", ID );

Cmd. Parameters. Add (P1 );

Cmd. Parameters. Add (P2 );

Int ROW = cmd. executenonquery ();

Con. Close ();

Sqlcontext. Pipe. Send (row. tostring ());

}

};

Take a closer look at the changecityname () method. It is a static method and has no return value (void ). It requires two parameters named ID and city. Note that the data types of these two parameters are sqlint32 and 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 changecityname () 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 changecityname () method is converted to a stored procedure and then saved to the Database "beifeng trade. Therefore, the "context connection" here refers to the "North Wind trade" 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:(Procedure2.1)

[Microsoft. sqlserver. server. sqlprocedure]

Public static void getallinfo ()

{

Sqlconnection con = new sqlconnection ("context connection = true ");

Sqlcommand cmd = new sqlcommand ();

Cmd. Connection = con;

Cmd. commandtext = "select * from ordering main file ";

Con. open ();

Sqldatareader reader = cmd. executereader ();

Sqlcontext. Pipe. Send (Reader );

Reader. Close ();

Con. Close ();

}

The getallinfo () 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:(Procedure2.2)

[Microsoft. sqlserver. server. sqlprocedure]

Public static void getallinfobyid (sqlint32 ID)

{

Sqlconnection con = new sqlconnection ("context connection = true ");

Con. open ();

Sqlcommand cmd = new sqlcommand ();

Cmd. Connection = con;

Cmd. commandtext = "select * from ordering main file where order number = @ p1 ";

Sqlparameter p1 = new sqlparameter ("@ p1", ID );

Cmd. Parameters. Add (P1 );

Sqlcontext. Pipe. executeandsend (CMD );

Con. Close ();

}

The getallinfobyid () method requires a parameter-ID, which returns records of a row from the "ordering primary" 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.(Procedure3)

[Microsoft. sqlserver. server. sqlprocedure]

Public static void getcityname (sqlint32 ID, out sqlstring City)

{

Sqlconnection con = new sqlconnection ("context connection = true ");

Con. open ();

Sqlcommand cmd = new sqlcommand ();

Cmd. Connection = con;

Cmd. commandtext = "select city of delivery from main order file where order number = @ ID ";

Sqlparameter id = new sqlparameter ("@ ID", ID );

Cmd. Parameters. Add (ID );

Object OBJ = cmd. executescalar ();

Con. Close ();

City = obj. tostring ();

}

This is a method named getcityname (). It requires two parameters. The first parameter is ID, which is an input parameter, and the second parameter is city, which is an output parameter (specified by the keyword out ). The two parameters are sqlint32 and sqlstring. The getcityname () method receives an ID parameter and returns the city (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 city 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:(Procedure4.1)

[Microsoft. sqlserver. server. sqlprocedure]

Public static void getrow ()

{

Sqlmetadata [] metadata = new sqlmetadata [2];

Metadata [0] = new sqlmetadata ("ID", sqldbtype. INT );

Metadata [1] = new sqlmetadata ("city", sqldbtype. nvarchar, 15 );

Sqldatarecord record = new sqldatarecord (metadata );

Record. setint32 (0, 10248 );

Record. setstring (1, "Beijing ");

Sqlcontext. Pipe. Send (record );

}

The getrow () 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, we created two columns of the nvarchar type with a length of 15. 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:(Procedure4.2)

[Microsoft. sqlserver. server. sqlprocedure]

Public static void getmultiplerow ()

{

Sqlmetadata [] metadata = new sqlmetadata [2];

Metadata [0] = new sqlmetadata ("ID", sqldbtype. INT );

Metadata [1] = new sqlmetadata ("city", sqldbtype. nvarchar, 15 );

Sqldatarecord record = new sqldatarecord (metadata );

Sqlcontext. Pipe. sendresultsstart (record );

Record. setint32 (0, 10249 );

Record. setstring (1, "Tianjin ");

Sqlcontext. Pipe. sendresultsrow (record );

Record. setint32 (0, 10250 );

Record. setstring (1, "Tianjin ");

Sqlcontext. Pipe. sendresultsrow (record );

Sqlcontext. Pipe. sendresultsend ();

}

The getmultiplerow () 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.

author profile: Bipin Joshi is the administrator of dotnetbips.com. He is the initiator of http://www.binaryintellect.com/. he is the training and consulting service of this company's .net framwork. He provides developers with training in Mumbai, India. He is also a member of Microsoft MVP (ASP. NET) and aspinsiders.