SQL Server 2005 Beta 2 Chinese Character Data Extremely Improfread Execology (CLR, Assemblies) [SQL Server 2005 New Features: Common Language Runtime (CLR) Integration, Assembly Runtime (CLR) Integration, Assemblies] First confirmed Microshaoft installed on Windows (of course just the server side of SQL Server) installed Microshaoft SQL Server 2005 beta 2, including: .NET Framework 2.0 (v2.0.40607) of course must be installed!
After confirming the successful installation: 1. Open Notepad.exe Written the following C # program and save the file with C: /Microshaoft.cs:
// namespace microshaoft // Namespace can have no // {? public class classx? {/ * ?? Standard complete C # class standard full program entry main function ?? public static void main (String [] args {} ?? Can not be used to write Assembly for SQL functions, stored procedures, triggers! * / ?? public static void main () ?? {???? // main function can be used for testing ???? //System.console.writeline (StringConvert "); ????? // system.console.read ();
???? for (int i = 0; i <100; i ); // delay ??} // ?? public classx () // ?? {// ??? // constructor //? ?} ?? public static string StringConvert (string x) // static must be static (class) method ?? {??? return Microsoft.VisualBasic.Strings.StrConv (x, Microsoft.VisualBasic.VbStrConv.TraditionalChinese, System.Globalization .Cultureinfo.currentculture.lcid); ?? // Difficultover can also be switched to add parameter control, do not repeat it ???} // ?? public static void stringconvert (String X, INT i) // does not allow method Carrier // ?? {// ??? // If the method does not return value, can be used for stored procedures or triggers, etc. // ??}?} //}
2. Run the CMD under Windows to enter the command line console:
• Perform the following command line:? C: /Windows / Microsoft.Net/framework/v2.0.40607/csc.exe / T: library /out:c:/microshaoft.lib.dll C: /Microshaoft.cs / R: C : /Windows/Microsoft.Net/framework/v2.0.40607/Microsoft.visualbasic.dll
• If there is any error, reference csc.exe /? Help, or MSDN correction! • This example will be generated after the correct execution, this example will be generated: c: /microshaoft.lib.dll file!
3. You can use the SQLCMD command line tool:
C: /> sqlcmd -U sa -P password -S ServerName / InstanceName1> CREATE DATABASE test2> go1> USE test2> CREATE ASSEMBLY ASM_Microshaoft FROM 'c: /microshaoft.lib.dll'3> goChanged database context to' test '. 1> CREATE FUNCTION [dbo]. [UDF_StringConvert] (@ NVARCHAR (MAX)) 2> RETURNS? NVARCHAR (MAX) 3> AS4> EXTERNAL NAME ASM_Microshaoft. [ClassX]. [StringConvert] 5> go1> SELECT dbo. [UDF_StringConvert ] ('Tudikacillus ") 2> GO ------------------ Jiankacillonacillobacillus (1 ROWS Affected) 1> EXIT
C: />
Of course, you can also use the graphical tool SQL Server Management Studio, execute in New Query:
Create Database Testgouse Testcreate Assembly ASM_MICROSHAOFT from 'C: /Microshaoft.lib.dll'go
Create function [dbo]. [Udf_stringconvert] (@ nvarchar (max)) returns? Nvarchar (max) ASEXTERNAL NAMEASM_MICROSHAOFT. [Classx]. [StringConvert] GO
Select dbo. [UDF_STRINGCONVERT] ('Tudikariobacillus ")
In summary, we have a two-package package for powerful .NET Framework, extend the function of T-SQL!
I have tried to do it directly:
Create assembly asm_ms_vb from 'c: /windows/microsoft.net/framework/v1.1.4322/MICROSOFT.VISUALBASIC.DLL'
But get the following error message:
MSG 6596, Level 16, State 1, Line 1CREATE Assembly Failed Because Assembly 'Microsoft.visualBasic' Is A System Assembly. Consider Creating a User Assembly To Wrap Desired FunctionAlity.
Certificate can't prove!
Continue summary: Writing features that can be used for SQL Server 2005 Database Assembly:
The method used for assembly call should be:? Public static modified disclosure, static, class method If the method used to create a T-SQL function should have a return value of the .NET basic data type compatible with the SQL data type. Such as: string, int The method of the T-SQL trigger does not need to return value Void, nor does it require parameters.
In addition, the procedures in the method, do not have: system.console.writeline (""); system.console.read (); such an interactive statement!
Writing a Function and Store Procedure with T-SQL To Note: After Create Assembly ASM_MS_VB FROM 'C: /WINDOWS / Microsoft.Net/framework/visualbasic.dll', the function, stored procedure There can be only one sentence in the prior art body of the trigger, namely: external namessembly_name. [Namespace_name.class_name]. [Method_name]
In addition, the function, the parameter list of the stored procedure must match the parameter list of the class method of Assembly, such as:
Alter Function [DBO]. [UDF_STRINGCONVERT] (@ nvarchar (max)) Returns? Nvarchar (max) ASEXTERNAL NAMEASM_MICROSHAOFT. [Classx]. [StringConvert]
Go
Create Procedure SP_TEST @ nvarchar (max) ASEXTERNAL NAMEASM_MICROSHAOFT. [Classx]. [Main]
Go
Create Trigger Trigger_Teston Tablenamefor INSERTASEXTERNAL NameASM_MICROSHAOFT. [Classx]. [Main]
Pay attention to the standard full C # class standard full program entry main function? Public static void main (string [] args) {} is not available for functions, stored procedures, triggers!
