Write execution your first Yukon CLR (Common language runtime) stored procedures

Stored Procedure | Execute write execute your first Yukon CLR (Common language runtime) stored procedure
　

　

Author: Suhil Srinivas

Translation: Zhu Yi

Original source: http://www.c-sharpcorner.com/Longhorn/Yukon/First-CLR-Procedure.asp

　

Describe
This article describes the steps to write and execute a Yukon CLR common language runtime stored procedure. This article is also a starting point for developing SQL stored procedures for mixed data types. Require proper installation of SQL Server Yukon Beta1 describes Yukon as the latest version of Microsoft's SQL Server, and one of its most important features is its and. NET integration. For more information on Yukon, please see the Yukon FAQ. One use. NET compatible language, the SQL Server object is called a. NET routine. One. NET routines can be of the following type 1. Stored procedure? 2. User-defined scalar-valued function? 3. User-defined function value function? 4. User-defined trigger one. NET stored procedures and earlier SQL stored procedures are used without distinction. In earlier versions of SQL Server, a stored procedure was a batch or batches of T-SQL statements. One. NET stored procedures can be composed of T-SQL and. NET compliant languages. Write your first one. NET routines include the following steps (for the purposes of this article, I use C # as a. NET development language): 1. Create a class and reference System.Data.SqlServer and SYSTEM.DATA.SQL2. Implement business logic in a public method 3. Compile this C # program 4 using command-line tools or Visual Studio.NET. Use the database to register this C # assembly 5. Register with the database. NET routine 6. Use the. NET routines for the purposes of this article, we develop a stored procedure as our first. NET routines. You can use Visual Studio.NET or your favorite text editor to write this C # program. The program code is as follows and assumes that there is a product table in the database. Using system.data.sql;using system.data.sqlserver;//////This are my-i-I-routine.///public class Mynetroutines{?pu Blic static void Firstproc () {? SqlPipe mypipe = Sqlcontext.getpipe (); Mypipe.send ("Hello World"); SqlCommand cmd = Sqlcontext.getcommand (); cmd.commandtext = "SELECT * from Product"; Mypipe.send (cmd. ExecuteReader ());} Now let meLine to explain the above code. Development. NET routines require all classes to be under the System.Data.SqlServer and SYSTEM.DATA.SQL namespaces. These namespaces include analogies such as sqlcontext that can be used to access SQL Server. Unlike ado.net, you need a connection and a connection string. Because the assembly is loaded into the database, it does not need to be shown to establish a connection. One. NET routines can use In-process Managed provider to access SQL Server resources. The namespaces mentioned above contain implementations of the in-process Managed provider. In this article we focus on two classes: SqlContext and SqlPipe. A SqlContext class contains methods that can get a database instance connection, commands, and so on. SqlPipe user implementation sends query result and message to client, and ado.net inside response class have many similarities. Sqlpipe.send () has four overloaded forms, which are used to communicate with clients, and the overloaded methods are as follows: 1. Sqlpipe.send (String meg) 2. Sqlpipe.send (SqlError se) 3. Sqlpipe.send (Isqlrecord record) 4. Sqlpipe.send (Isqlreader Reader) You can send a messge, reader,error, or record to a client program. The difference between send (string) and send (reader) is that in the workspace of SQL Server, the message is sent to the messages pane and reader is sent to the results pane. In Yukon Beta, the System.Data.SqlServer namespace is in the program called SqlAccess.dll, which compiles the above code at a command prompt using the following command: csc.exe/t:library/r: "
Qlaccess.dll "/out:mynetroutines.dll MyNETRoutines.cs? Once the program is generated and the assembly MyNETRoutines.dll contains this stored procedure, the following code is used to execute the stored procedure. Use adventureworkscreate ASSEMBLY test from ' C:\Yukon\Projects\MyNETRoutines.dll ' gocreate procedure Firstprocas EXTERNAL NAME mynetroutines:mynetroutines::firstprocgoexec Firstproc
? figure I and figure II are the results of the implementation
Figure I

Figure II
Summarizes. NET runtime integration with the database engine for use by developers. NET base Class library to develop complex business logic provides a lot of flexibility. But you must be aware of the pros and cons of using the runtime. Therefore, before deciding to implement the business logic using T-SQL or. NET compliant languages, you need to take full account. --------------------------Author introduces Suhil Srinivas, an experienced developer who uses a variety of Microsoft technologies for development. Suhil is an MCP, and he is very enthusiastic about coaching and helping novices. He has extensive experience in building web-based, wireless networking and back-office systems, and has been consulting for a variety of businesses, particularly Suhil has been engaged in credit and payment processing.　　In addition, he is eduware knowledge Systems (EKS) sensible long. As a company, Eks has several products that apply to educational establishments of any size. One of the main products is the SCHOOL-ERP system, which has completely changed the daily management mode of educational institutions. Other products are also available for small and medium-sized medical service providers. EKS also provides consulting services and provides development services to overseas customers through offices in Bangalore, India.