Question

C#怎么调用sqlserver的自定义函数

下面是这个函数，要具体的代码。

alter function AutoNum(@prefix varchar(3))
returns varchar(20)
as
begin
DECLARE @str VARCHAR(20), @dateStr VARCHAR(6),@num VARCHAR(5),@oldStr VARCHAR(20),@dt datetime
select @dt=today from numdate
SET @dateStr=RIGHT(YEAR(@dt),2)+RIGHT('0'+Convert(VARCHAR(2),MONTH(@dt)),2)
+RIGHT('0'+Convert(VARCHAR(2),DAY(@dt)),2)
SELECT @oldStr=MAX(pigID) FROM pig

IF @oldStr is not null
begin
declare @timer varchar(6),@amount varchar(5)--存放日期和流水号
set @timer=substring(@oldStr,4,9)--截取时间6位
set @amount=right(@oldStr,5)--截取流水号5位
if @timer=@dateStr--如果数据库表中的日期和当前日期相等，则在流水号上加1
set @str=@prefix+@timer+right('00000'+Convert(varchar(5),(@amount+1)),5)
else
set @str=@prefix+@dateStr+'00001'
end
IF @oldStr is null--如果数据表为空，则新生成一个编号
begin
set @str=@prefix+@dateStr+'00001'
end
return @str
end
go

insert into pig(pigID) values(dbo.AutoNum('HQ-'))

Answer 1

　　sql server 自定义函数的使用
　　自定义函数
　　
　　用户定义自定义函数像内置函数一样返回标量值，也可以将结果集用表格变量返回
　　用户自定义函数的类型:
　　标量函数:返回一个标量值
　　表格值函数{内联表格值函数、多表格值函数}:返回行集（即返回多个值）
　　
　　1、标量函数
　　Create function 函数名（参数）
　　Returns 返回值数据类型
　　[with {Encryption | Schemabinding }]
　　[as]
　　begin
　　SQL语句(必须有return 变量或值)
　　
　　End
　　
　　Schemabinding :将函数绑定到它引用的对象上（注：函数一旦绑定，则不能删除、修改，除非删除绑定）
　　
　　
　　Create function AvgResult(@scode varchar(10))
　　Returns real
　　As
　　Begin
　　Declare @avg real
　　Declare @code varchar(11)
　　Set @code=@scode + ‘%’
　　Select @avg=avg(result) from LearnResult_baijiali
　　Where scode like @code
　　Return @avg
　　End
　　
　　执行用户自定义函数
　　select 用户名。函数名 as 字段别名
　　select dbo.AvgResult(‘s0002’) as result
　　
　　用户自定义函数返回值可放到局部变量中，用set ,select,exec赋值
　　declare @avg1 real ,@avg2 real ,@avg3 real
　　select @avg1= dbo.AvgResult(‘s0002’)
　　set @avg2= dbo.AvgResult(‘s0002’)
　　exec @avg3= dbo.AvgResult ‘s0002’
　　select @avg1 as avg1 ,@avg2 as avg2 ,@avg3 as avg3
　　
　　函数引用
　　
　　create function code(@scode varchar(10))
　　returns varchar(10)
　　as
　　begin
　　declare @ccode varchar(10)
　　set @scode = @scode + ‘%’
　　select @ccode=ccode from cmessage
　　where ccode like @scode
　　return @ccode
　　end
　　
　　select name from class where ccode = dbo.code(‘c001’)
　　
　　2、表格值函数
　　a、 内联表格值函数
　　格式：
　　create function 函数名（参数）
　　returns table
　　[with {Encryption | Schemabinding }]
　　as
　　return(一条SQL语句)
　　
　　create function tabcmess(@code varchar(10))
　　returns table
　　as
　　return(select ccode,scode from cmessage where ccode like @ccode)
　　
　　b、 多句表格值函数
　　create function 函数名（参数）
　　returns 表格变量名table (表格变量定义)
　　[with {Encryption | Schemabinding }]
　　as
　　begin
　　SQL语句
　　end
　　
　　多句表格值函数包含多条SQL语句，至少有一条在表格变量中填上数据值
　　表格变量格式
　　returns @变量名 table (column 定义| 约束定义 [,…])
　　对表格变量中的行可执行select,insert,update,delete ， 但select into 和 insert 语句的结果集是从存储过程插入。
　　
　　Create function tabcmessalot (@code varchar(10))
　　Returns @ctable table(code varchar(10) null,cname varchar(100) null)
　　As
　　Begin
　　Insert @ctable
　　Select ccode,explain from cmessage
　　Where scode like @code
　　return
　　End
　　
　　Select * from tabcmessalot(‘s0003’)
　　
　　来自：http://hi.baidu.com/datachina/blog/item/801def0366c4e7ea09fa9344.html

　　

C#调用SQL自定义函数返回值

代码
　　1 --SQL自定义函数：
　　2
　　3 CREATE FUNCTION [GetProjectID] (@headStr nvarchar(10),@date datetime)
　　4 )
　　5
　　6 RETURNS NVARCHAR(200)
　　7
　　8 AS
　　9
　　10 BEGIN
　　11
　　12 --不能在自定义函数中用INSERT INTO
　　13
　　14 --insert into emos_cust(cust_name,dates)values(
　　15
　　16 --@headStr,@date
　　17
　　18 --)
　　19
　　20 return 'TEST BY HANSHU'
　　21 END
　　代码
　　1 /// <summary>
　　2 /// 获取项目文件编号 涂聚文
　　3 /// </summary>
　　4 private void FileNo()
　　5 {
　　6
　　7 SqlConnection conn = new SqlConnection(connectionString);
　　8 string strSql = "GetProjectID"; //自定SQL函数
　　9 SqlCommand cmd = new SqlCommand(strSql, conn);
　　10 cmd.CommandType = CommandType.StoredProcedure;
　　11 cmd.Parameters.Add("@headStr", SqlDbType.NVarChar).Value = "ZQ3"; //输入参数
　　12 cmd.Parameters.Add("@date", SqlDbType.DateTime).Value = System.DateTime.Now.ToShortDateString(); //输入参数
　　13 cmd.Parameters.Add("@returnString", SqlDbType.NVarChar);
　　14 cmd.Parameters["@returnString"].Direction = ParameterDirection.ReturnValue; //返回参数
　　15 try
　　16 {
　　17 conn.Open();
　　18 object o= cmd.ExecuteScalar();
　　19
　　20 this.txtAFileNO.Text = cmd.Parameters["@returnString"].Value.ToString();
　　21
　　22 //Response.Write("");
　　23
　　24 }
　　25 catch (Exception ex)
　　26 {
　　27
　　28 this.txtAFileNO.Text = ex.Message;
　　29
　　30 }
　　31 finally
　　32 {
　　33
　　34 if (!(conn.State == ConnectionState.Closed))
　　35 {
　　36
　　37 conn.Close();
　　38
　　39
　　40 }
　　41
　　42 }
　　43
　　44
　　45 }

Answer 2

这里我们用C#做一个计算滑轮系统近似距离的函数，并把该函数部署到SqlServer2005上去，这里使用的方法是原始的，全手动方式的部署方式，主要的目的是为了理解部署原理，实际上直接开SqlServer数据库项目可能更容易些。
首先让我们看一下滑轮传输带系统图示，如下图所示：
用C#编写SQLServer自定义函数
一、首先在VS2005里编写一个计算传输带距离的函数，源代码如下所示：
//自定义计算传输带距离的函数
using System;
using System.Collections.Generic;
using System.Text;

namespace SqlServerClr
{
public class pulley
{
public static double PulleyDistance(double Pulley1Diameter, double Pulley2Diameter, double BeltLength)
{
double length = 0, a = 2.0;
double b = BeltLength - 1.57 * (Pulley1Diameter + Pulley2Diameter);
double c = System.Math.Pow(Pulley1Diameter - Pulley2Diameter, 2.0);
//如果只是
double b1 = (b * b) - (4 * a * c);
if (b1 > 0)
{
length = (b + Math.Sqrt(b1)) / (2 * a);
//检查传输带是否合适
if (length < ((Pulley1Diameter + Pulley2Diameter) / 2))
{
//返回0，如果传输带不合适；
length = 0;
}
}

//精度只允许一位小数
return System.Math.Round(length, 1);
}

}
}

把以上CS文件编译为动态连接库， 在Visual Studio 2005 命令行模式下使用命令：
csc /t:library /out:pulleylib.dll pulley.cs
编译成功，得到了一个pulleylib.dll

二、在SQL Sever 2005中部署CS的自定义函数
--编译PulleyDistance函数
Drop Assembly Mechanics --如果编译程序出错可以用此命令删除程序集

Create Assembly Mechanics --Mechanics是编译程序集名称
from 'E:\pulleyLib.dll'
Go

Create function PulleyDistance
(
@diameter1 float,
@diameter2 float,
@beltLength float
)
--注意这里的三个参数名称可以和CS编写的静态函数不同，但类型要兼容一致
returns float
As External name
[Mechanics].[SqlServerClr.pulley].[PulleyDistance]
--CS中定义的静态函数全称一般为【命名空间.类名】.【函数名】，注意这里是区分大小写的，对应 [SqlServerClr.pulley].[PulleyDistance]
GO

--检查是否已经部署上了
select dbo.PulleyDistance(3,2,100)

--检查部署结果
select routine_name,routine_body from information_schema.routines

通过以上两步，一个.NetCLR的函数就部署到了SqlServer上了

Answer 3

sql.exec("insert into pig(pigID) values(dbo.AutoNum('HQ-'))");

直接写，或者写储存过程，在储存过程里面调用函数。

Answer 4

//定义
SqlConnection conn = new SqlConnection(DbTool.getSQLConnString());
string strSql = "AutoNum"; //" and user_role='Administrator'";
SqlCommand cmd = new SqlCommand(strSql, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@cust_name",SqlDbType.NVarChar).Value="";
cmd.Parameters.Add("@returnString", SqlDbType.NVarChar);
cmd.Parameters["@returnString"].Direction = ParameterDirection.ReturnValue;
string strRTN = "";
//方法
try
{
conn.Open();
object o= cmd.ExecuteScalar();

strRTN = cmd.Parameters["@returnString"].Value.ToString();

}
catch (Exception ex)
{

LabelTestMSG.Text = ex.Message;

}
finally
{

if (!(conn.State == ConnectionState.Closed))
{

conn.Close();

}

}

}

--------------
其实有个更简单的方法,你把函数封装到存储过程里,用exec的语句直接跟select语句一样的调用.