CLR stored procedures using SQL Server 2005
A new feature of SQL Server 2005 is the integration of .net's CLR. The advantage of integrating .net CLR is that it can easily enable developers to create stored procedures, triggers, custom functions, etc. using their familiar .net language. In this article, stored procedures will be created in C#. Why not use T-SQL to create stored procedures? Because T-SQL has been developing for a long time and has its limitations in some occasions, such as T-SQL is not object-oriented and some syntaxes are too complicated. If you use an object-oriented .NET language to write data objects such as stored procedures, due to the powerful characteristics of the .net language, you can write more robust and better stored procedures. Note that stored procedures written in .NET through SQL Server 2005 are the same as those written in general application programs in .NET language, and are managed codes. In addition, the CLR programming language provides rich constructs (such as arrays and lists, etc.) that are not available in T-SQL. Compared to T-SQL, which is an interpreted language, the CLR programming language has better performance because the managed code is compiled. For works involving arithmetic calculations, string processing, conditional logic, etc., the performance of managed code may be one order of magnitude better than T-SQL. In this article, although stored procedures can be written in T-SQL, in order to illustrate the problem, stored procedures are still written in C#. The steps are as follows:
First, open Visual Studio 2005 beta 2, select the C# language, and create a new database project, named SQLProject1. At this time, Visual Studio 2005 beta 2 will ask you what database to associate with. Since we use the pubs database, we choose the machine name as the local machine, set up the SQL verification method, and select the pubs database. (Note that in SQL Server 2005, the pubs and northwind databases are no longer the built-in databases of SQL Server 2005, and you need to go/fwlink/?LinkId=31995Go to download). Next, after the project is completed, select the new project, select the store procedure stored procedure, and name it, press OK, and enter the following code:
using System;
using ;
using ;
using ;
using ;
using ;
public partial class StoredProcedures
{
[]
public static void GetAuthors()
{
SqlPipe sp = ;
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
();
SqlCommand cmd = new SqlCommand();
= ;
= conn;
= "Select DatePart(second, GetDate()) " + " As timestamp,* from authors";
SqlDataReader rdr = ();
(rdr);
}
}
[SqlProcedure]
public static void GetTitle*yAuthor(string authorID)
{
string sql = "select , , , " + " from authors A" +
" inner join titleauthor TA on A.au_id = TA.au_id " +
" inner join titles T on TA.title_id = T.title_id " +
" where A.au_id = '" + @authorID + "'";
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
();
SqlPipe sp = ;
SqlCommand cmd = new SqlCommand();
= ;
= conn;
= sql;
SqlParameter paramauthorID = new SqlParameter("@authorID", , 11);
= ;
= authorID;
(paramauthorID);
SqlDataReader rdr = ();
(rdr);
}
}
}
Let's look at the above code. First, the Authors class is declared, and namespaces such as ;,;,,; are introduced. Among them, in the namespace, there are two very important classes:
·SqlContext: A method contained in the SqlContext class can obtain a database instance connection, command, transaction, etc.
·SqlPine: The user implements sending query results and messages to the client, which has many similarities with the Response class inside.
There are two static methods in the Authors class, namely GetAuthors and GetTitle*yAuthor. Where, the GetAuthors method returns all data of the authors table in the pubs database, and GetTitle*yAuthor returns the book written by the specified author.
In the GetAuthors method, first refer to the SqlPine object by calling the pipe attribute of the sqlContext class:
SqlPipe sp = ;
Next, use the SqlConnection object to connect to the database. Note that in the string connecting to the database, use "context=true" to indicate that the user who has logged in to the database is used to log in:
using (SqlConnection conn = new SqlConnection("context connection=true"))
();
Create an instance of the SqlCommand object and set its properties:
SqlCommand cmd = new SqlCommand();
= ;
= conn;
= "Select DatePart(second, GetDate()) " + " As timestamp,* from authors";
Execute SQL statements by calling the ExecuteReader method of the SqlCommand object.
SqlDataReader rdr = ();
Finally, use the SqlPipe object to return the result set to the client. This can be done using the Send method
(rdr);
It should be noted that after we create a stored procedure, we must deploy it. We first select build sqlproject1 in the build menu and will compile the class we just created. After compiling the project, you can deploy it. Deployment is also very simple. Select the build menu and then select Deploy sqlproject1, which will automatically deploy the newly written stored procedure to SQL Server 2005.
Finally, we need to execute the following statement in SQL Server management studio to ensure that managed code can be executed in SQL Server:
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE WITH OVERRIDE;
GO
Previous page1234Next pageRead the full text