Shulou(Shulou.com)06/02 Report--

SQL2005-based CLR stored procedure is how, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

In SQL Server 2005, you can create database objects in a SQL Server instance by programmatically creating assemblies based on the Microsoft .NET Framework common language runtime (CLR) creation. Database objects that can take advantage of the many programming models provided by CLR include triggers, stored procedures, functions, aggregate functions, and types.

Although Transact-SQL is specifically designed for direct data access and manipulation in databases, it does not provide programming constructs to make data manipulation and computation easier. For example, Transact-SQL does not support arrays, collections, for-each loops, bit shifts, or classes.

CLR stored procedures provide object-oriented features such as encapsulation, inheritance, and polymorphism. Now, the relevant code can be easily organized in classes and namespaces. When using a large amount of server code, this makes it easier to organize and maintain the code and implement some database functions by using managed code.

For computation and complex execution logic, CLR-based managed code is better suited than Transact-SQL. One of the advantages of managed code is type safety, which fully supports many complex tasks, including string processing and regular expressions.

The functionality provided in the .NET Framework library provides access to thousands of pre-generated classes and routines. It can be easily accessed from any stored procedure, trigger, or user-defined function. The base class library includes classes that provide functions for string manipulation, advanced mathematical operations, file access, encryption, and so on.

For data access that requires little or no procedural logic, use Transact-SQL. It is best to use managed code for CPU-intensive functions and procedures with complex logic.

Let's look at how to create SQL Server 2005-based CLR stored procedures in Visual Studio 2005:

When we open Visual Studio 2005, there is a SQL Server project under the corresponding database column, and a new project is created, named StoredStu. After confirmation, a database connection dialog box is displayed. After the database is connected, we have the following two tables in the database, as follows:

StuInfo (Student Information Table)

Nation (nationality table)

Create a new file, select the option "stored procedure", name it StoredStu, and write the following code in the file:

Using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; public partial class StoredProcedures {[Microsoft.SqlServer.Server.SqlProcedure] public static void StoredStu () {/ / place the code SqlPipe sp = SqlContext.Pipe; string sql = "Select * from StuInfo" here Using (SqlConnection conn = new SqlConnection ("context connection=true")) {conn.Open (); SqlCommand cmd = new SqlCommand (); cmd.CommandType = CommandType.Text; cmd.Connection = conn; cmd.CommandText = sql; SqlDataReader rdr = cmd.ExecuteReader (); sp.Send (rdr) }} [SqlProcedure] public static void GetStuInfo (string strNationCode) {string sql = "select Stu.StudentName, Stu.StudentNo, Stu.StudentUniversity,Na.NationName from StuInfo Stu inner join NationNa on Stu.NationCode = Na.NationCode where Stu.NationCode ='" + @ strNationCode + "'"; 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 paramstrNationCode = new SqlParameter ("@ strNationCode", SqlDbType.VarChar, 11); paramstrNationCode.Direction = ParameterDirection.Input; paramstrNationCode.Value = strNationCode Cmd.Parameters.Add (paramstrNationCode); SqlDataReader rdr = cmd.ExecuteReader (); sp.Send (rdr);}

Now let's compile it first, select build solution from the build menu, and when you finish compiling the project, you need to deploy it. Also select deploy solution from the build menu, which will automatically deploy the written stored procedure to SQL Server 2005.

To ensure that SQL can execute managed code, we also need to execute the following statement in SQL Server 2005:

EXEC sp_configure "clr enabled", 1; RECONFIGURE WITH OVERRIDE; GO

The results of the execution display are as follows:

The configuration option 'clr enabled' has been changed from 0 to 1. Please run the RECONFIGURE statement to install.

This allows you to create the CLR stored procedure.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.