Six commonly used software that damages hard disks for a long time

Use SQL Server 2005 CLR Stored Procedures

A new feature of SQL Server 2005 is the integration of. net CLR. The benefit of integrating. net CLR is that developers can easily create stored procedures, triggers, and custom functions using the. net language they are familiar with. net. In this article, we will use C # To create a stored procedure. Why don't T-SQL be used to create a stored procedure? Because the development of T-SQL to the present, has been a long time, in some occasions has its limitations, such as T-SQL is not object-oriented, some syntax is too complex and so on. If you use the object-oriented. NET language to write data objects such as stored procedures, due to the powerful features of the. net language, you can write more robust and better stored procedures. Note that the stored procedures written in. NET through SQL Server 2005 are all managed code, just like the general applications written in. NET. In addition, CLR programming languages provide rich structures (such as arrays and lists) that are not available in T-SQL ). The CLR programming language has better performance than the T-SQL (which is an interpreted language) because the hosted code is compiled. For operations involving arithmetic computing, string processing, conditional logic, and so on, the performance of managed code may be an order of magnitude higher than the T-SQL. In this article, although you can use T-SQL to write stored procedures, but in order to illustrate the problem, or to C # To write stored procedures. The procedure is as follows:

First, open Visual Studio 2005 beta 2, select the c # language, and create a new database project named sqlproject1. Visual Studio 2005 beta 2 will ask you what database to associate. Because we use the pubs database, we select the machine name as the local machine, set the SQL verification method, and select the pubs database. (Note, in SQL Server 2005, pubs and northwind database is no longer SQL Server 2005 built-in database, need to http://go.microsoft.com/fwlink? LinkId = 31995 to download ). After the project is created, select Add project, select store procedure stored procedure, name it Authors. cs, then press OK, and enter the following code:

Using System;
Using System. Data;
Using System. Data. SQL;
Using System. Data. SqlTypes;
Using Microsoft. SqlServer. Server;
Using System. Data. SqlClient;

Public partial class StoredProcedures
{
[Microsoft. SqlServer. Server. SqlProcedure]

Public static void GetAuthors ()
{
SqlPipe sp = SqlContext. Pipe;
Using (SqlConnection conn = new SqlConnection ("context connection = true "))
{
Conn. Open ();
SqlCommand cmd = new SqlCommand ();
Cmd. CommandType = CommandType. Text;
Cmd. Connection = conn;
Cmd. CommandText = "Select DatePart (second, GetDate ()" + "As timestamp, * from authors ";
SqlDataReader rdr = cmd. ExecuteReader ();
Sp. Send (rdr );
}
}

[SqlProcedure]

Public static void GetTitlesByAuthor (string authorID)
{
String SQL = "select T. title, T. price, T. type," + "T. pubdate 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 "))
{
Conn. Open ();
SqlPipe sp = SqlContext. Pipe;
SqlCommand cmd = new SqlCommand ();
Cmd. CommandType = CommandType. Text;
Cmd. Connection = conn;
Cmd. CommandText = SQL;
SqlParameter paramauthorID = new SqlParameter ("@ authorID", SqlDbType. VarChar, 11 );
ParamauthorID. Direction = ParameterDirection. Input;
ParamauthorID. Value = authorID;
Cmd. Parameters. Add (paramauthorID );
SqlDataReader rdr = cmd. ExecuteReader ();
Sp. Send (rdr );
}
}
}