Procedure for writing and executing a yukon CLR generic language runtime stored procedure 1. Create a class and reference system.data.sqlserver and system.data.sql2. Implement business logic in the public method 3. Using the command line tool or Visual Studio. Net compiles this C # program 4. Sign up with this C # assembly using the database 5. Register this .NET routine 6. Using the .NET routine
-------------------------------------------------- ------------------------------ in Visual Studio 2005 File -> New-> Project in Visual Studio 2005 Project -> Add New Item-> Stored ProCedure Automatically generates the following frame code in Visual Studio 2005
using System; using System.Data; using System.Data.Sql; using System.Data.SqlServer; using System.Data.SqlTypes; public partial class StoredProcedures {[SqlProcedure] public static void StoredProcedure1 () {// Put your code here }}; ----------------------------------------------- ---------------------------------
Realize business logic as follows: Enter your name and a first letter, StoredProceduretest will display Welcome information in the Messages window Show the author's first letter from the Bookauthors table, the author's first letters, the author's first letters, the public partial class storedprocedures {[SQLPROCEDURE] public static void StoredProcedureTest (string name, string firstLetter) {SqlPipe myPipe = SqlContext.GetPipe (); string strMsg = "Hello" name.Trim () "!"; myPipe.Send (strMsg); SqlCommand myCommand = SqlContext.GetCommand () ; string strSQL = string.Format ( "select * from bookAuthors where authorname like '{0}%'", firstLetter); myCommand.CommandText = strSQL; myPipe.Send (myCommand.ExecuteReader ());}};
-------------------------------------------------- ------------------------------
Compilation 1.IDE build-> Build Solution
2. Manually compile in YUKON BETA, the program called SQLACCESS.DLL in yukon beta, can be used to compile the above code under the command prompt: CSC.exe / T: library / r: " / SQLACCESS.DLL "/out:mydllname.dll mysourceName.cs Deploy 1.ide BUILD-> Deploy Solution 2. Manual create assembly myassemblyname from 'path / mydllname.dll'
Create Procedure MyProcedureName (@Parameter Data_Type) AS EXTERNAL NAME Assembly_name.class_name .method_name
Example: Create Procedure StoredProceduretest (@name nvarchar (200), @nametter nvarchar (1)) AS External Name SqlSerClrtest.storedProcedures.StoredProceduRetest
Execute StoredProceduRetest 'vivian', 'M'
