C#编写扩展存储过程,实际上是利用C#写个dll,注册之后在sql中调用而已。理论性的东西本人过于薄弱,讲不出什么道道,还是先来看一个简单的示例吧:
1、首先,建一个类库项目,新建类文件CsharpHelper.cs.包含代码如下:
Code
using System;
using System.Runtime.InteropServices;
using System.Reflection;
using System.Runtime.CompilerServices;
[assembly: AssemblyTitle("SQLInterop")]
[assembly: AssemblyDescription("Test SQL .NET interop")]
[assembly: AssemblyVersion("1.0.0.1")]
[assembly: AssemblyDelaySign(false)]
namespace SQLInterop
{
public interface ICsharpHelper
{
string Hello();
}
[ClassInterface(ClassInterfaceType.AutoDispatch)]
public class CsharpHelper : ICsharpHelper
{
public string Hello()
{
return "Hello from CSharp";
}
}
}
注意记得把自动生成的那个类库信息文件删掉。
2、使用VS里的命令提示,生成强命名文件:sn -k HelperKey.snk
3、编译这个类文件,记得把snk加上去:csc /t:library /keyfile:HelperKey.snk CsharpHelper.cs
4、注册dll:regasm /tlb:CsharpHelper.tlb CsharpHelper.dll /codebase
至此dll的准备工作完成了,下面在sql里试试看,打开查询分析器(用sa或具有相应权限的账户登录),选中master数据库,书写sql语句如下:
Code
declare @object int
declare @hr int
declare @property varchar(255)
declare @return varchar(255)
declare @src varchar(255), @desc varchar(255)
-- 创建对象实例。
exec @hr = sp_OACreate 'SQLInterop.CsharpHelper', @object out
if @hr <> 0
begin
exec sp_OAGetErrorInfo @object, @src out, @desc out
select hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
return
end
-- 调用对象方法。
exec @hr = sp_OAMethod @object, 'Hello', @return out
if @hr <> 0
begin
exec sp_OAGetErrorInfo @object, @src out, @desc out
select hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
return
end
print @return
-- 销毁对象实例。
exec @hr = sp_OADestroy @object
if @hr <> 0
begin
exec sp_OAGetErrorInfo @object, @src out, @desc out
select hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
return
end
看看,是不是输出了"Hello from Csharp"呢?
可能遇到的错误:
a、提示“阻止了对组件 'Ole Automation Procedures' 的 过程'sys.sp_OACreate' 的访问”,此时可用"exec sp_configure 'Ole Automation Procedures', 1 reconfigure"语句来开启权限,或是在SQLServer功能的外围应用配置器中选中“Ole自动化”。
b、返回0x80131700号错误信息。这个我也没解决。目前只发现在装有sql2000的机器上没碰到此问题。
c、其它错误,请参照sqlserver联机丛书。
或许你会说,仅仅返回一个字符串,有啥用啊?返回个数据集给我看看?没问题,let's go!
1、再新建一个文件,名为DataHelper.cs,代码如下:
DataHelper Code
namespace HisHelper
{
public interface IDataHelper
{
DataTable GetData(string name);
}
[ClassInterface(ClassInterfaceType.AutoDual)]
public class DataHelper : IDataHelper
{
private static string _connstr = "Password={0};Persist Security Info=True;User ID={1};Initial Catalog={2};Data Source={3};Connect Timeout=10;";
private static string _testsql = "select * from [user] where [name] like '%@name%'";
private SqlConnection CreateConnection(string server, string logid, string logpass)
{
return new SqlConnection(string.Format(_connstr, logpass, logid, "his_nh", server));
}
public DataTable GetData(string name)
{
SqlParameter parm = new SqlParameter("@name", SqlDbType.VarChar, 12);
parm.Value = name;
DataTable dt = SqlHelper.ExecuteDataset(CreateConnection(".", "sa", string.Empty), CommandType.Text, _testsql, parm).Tables[0];
return dt;
}
}
}
namespace HisHelper
{
public interface IDataHelper
{
DataTable GetData(string name);
}
[ClassInterface(ClassInterfaceType.AutoDual)]
public class DataHelper : IDataHelper
{
private static string _connstr = "Password={0};Persist Security Info=True;User ID={1};Initial Catalog={2};Data Source={3};Connect Timeout=10;";
private static string _testsql = "select * from [user] where [name] like '%@name%'";
private SqlConnection CreateConnection(string server, string logid, string logpass)
{
return new SqlConnection(string.Format(_connstr, logpass, logid, "his_nh", server));
}
public DataTable GetData(string name)
{
SqlParameter parm = new SqlParameter("@name", SqlDbType.VarChar, 12);
parm.Value = name;
DataTable dt = SqlHelper.ExecuteDataset(CreateConnection(".", "sa", string.Empty), CommandType.Text, _testsql, parm).Tables[0];
}
}
}
2、再按照之前说的那几个步骤,生成snk文件,编译,注册。在查询分析器里调用:
DataHelper Sql Code
exec @hr = sp_OACreate 'HisHelper.DataHelper', @object out
exec @hr = sp_OAMethod @object, 'GetData', @return out, '张三'
--省略了部分代码
执行之后发现什么?oh my god,是不是出错了?提示“该环境不支持。。。”?没关系,仔细想想,GetData方法返回的是datatable,sql里认不认得这个东西啊?对了,问题就出在这,把datatable转成字符串数组吧!再添加一个方法:
Format1 Code
private string[,] FormatDataTable(DataTable dt)
{
if (dt == null || dt.Rows.Count == 0)
return null;
string[,] dataArr = new string[dt.Rows.Count, dt.Columns.Count];
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
dataArr[i, j] = dt.Rows[i][j].ToString();
}
}
return dataArr;
}
把GetData里的return语句修改一下:return FormatDataTable(dt); 重新生成并注册,在sql里调用下看看。是不是成功了?可是。。。好像还有点不对,怎么行列似乎被对调了啊?哈哈,再修改下FormatDataTable方法:
Format2 Code
private string[,] FormatDataTable(DataTable dt)
{
if (dt == null || dt.Rows.Count == 0)
return null;
string[,] dataArr = new string[dt.Columns.Count, dt.Rows.Count];
for (int i = 0; i < dt.Columns.Count; i++)
{
for (int j = 0; j < dt.Rows.Count; j++)
{
dataArr[i, j] = dt.Rows[j][i].ToString();
}
}
return dataArr;
}
再编译,注册,调用,这回没问题了。Over