使用SQL CLR，方法必须是公有静态的建立sql server project这样会自动部署到sql server上去using System;using System.Collections.Generic;using System.Text;using System.Data;using System.Data.SqlClient;using Microsoft.SqlServer.Server;using System.Data.SqlTypes;using System.Security.Cryptography;
public class SimpleCLR_VS{[Microsoft.SqlServer.Server.SqlFunction]public static string GetPath(){ return "sdf";// System.Environment.CurrentDirectory;}
[Microsoft.SqlServer.Server.SqlFunction]public static SqlBinary HashString(string clearText){ UnicodeEncoding ue = new UnicodeEncoding(); byte[] mes = ue.GetBytes(clearText); SHA1Managed sm = new SHA1Managed(); return sm.ComputeHash(mes);}
[Microsoft.SqlServer.Server.SqlProcedure]public static void GetClr(){ using (SqlConnection conn = new SqlConnection("context connection = true")) { conn.Open(); SqlCommand command = new SqlCommand("select * from Table_1",conn); SqlContext.Pipe.ExecuteAndSend(command); }}
[Microsoft.SqlServer.Server.SqlProcedure]public static void GetRandomData(int count){ SqlDataRecord record = new SqlDataRecord( new SqlMetaData("RowID", SqlDbType.Int), new SqlMetaData("Value", SqlDbType.Int));
SqlContext.Pipe.SendResultsStart(record); Random rd =new Random(); for (int i = 0; i < count; i++) { record.SetInt32(0, i); int r = rd.Next(100); record.SetInt32(1, r); SqlContext.Pipe.SendResultsRow(record); } SqlContext.Pipe.SendResultsEnd();}}
