sql自訂函數及C#中調用

標籤：

1、在C#中調用sql自訂函數

1.1 純量值函式

sql語句調用 select  dbo.GetClassIDWithName(1)  

string strSql = string.Format("select dbo.GetClassIDWithName(‘{0}‘)",dtTime);
DataTable dt = DB_Contrast.DB.OleDbHelper.GetDataTable(strSql);

1.2 資料表值函式

sql語句調用 select * from GetAnalysis(‘2015-1-15‘,1) 

string strSql = string.Format("select * from dbo.GetAnalysis(‘{0}‘,{1}) where 部門=‘{2}‘ ",dtTime, classid,"開發");
DataSet ds = DB_Contrast.DB.OleDbHelper.GetDataSet(strSql);

2、資料表值函式，

內層select擷取不重複的記錄

外層按照部門進行分組

USE [BW_Contrast]GO/****** Object:  UserDefinedFunction [dbo].[GetAnalysis]    Script Date: 01/15/2015 13:09:17 ******/IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N‘[dbo].[GetAnalysis]‘) AND type in (N‘FN‘, N‘IF‘, N‘TF‘, N‘FS‘, N‘FT‘))DROP FUNCTION [dbo].[GetAnalysis]GO/****** Object:  UserDefinedFunction [dbo].[GetAnalysis]    Script Date: 01/15/2015 13:09:17 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO   /*   -----------------------------------------------------------------------------  《根據入井時間擷取相應的班次ID》   -----------------------------------------------------------------------------   參數：　　　1、@classdate   班次日期        2、@classid 班次ID         傳回值：table   -----------------------------------------------------------------------------   Written by    -----------------------------------------------------------------------------   */CREATE function [dbo].[GetAnalysis](@classdate datetime,@classid int)returns table as   return (--declare @classdate datetime,@classid int--set @classid=2--set @classdate=‘2015-1-14‘select     case when(grouping(a.部門)=1) then ‘合計‘ else a.部門 end as 部門   ,(select top 1 ID from dbo.v_Dept where 部門名稱=a.部門) as deptid   ,Sum(case when dt_RealTime is not null and myclassid=@classid  then 1 else 0 end ) as  派班人數   ,Sum(case when dt_GetTime is not null and myclassid=@classid  then 1 else 0 end ) as  領燈人數   ,Sum(case when dtInWellTime is not null and myclassid=@classid  then 1 else 0 end ) as  下井人數   ,Sum(case when dt_OutWellTime is not null and myclassid=@classid  then 1 else 0 end ) as  上井人數   ,Sum(case when dt_ReturnTime is not null and myclassid=@classid  then 1 else 0 end ) as  還燈人數 from(    select         部門,deptid,myclassdate,myclassid,mypersonid        ,min(dt_RealTime) as  dt_RealTime        ,min(dt_GetTime) as dt_GetTime        ,min(dtInWellTime) as dtInWellTime        ,min(dt_OutWellTime) as dt_OutWellTime        ,min(dt_ReturnTime) as dt_ReturnTime    from v_ALL_NEW    where myclassdate=@classdate and myclassid=@classid    group by 部門,myclassdate,myclassid,mypersonid,deptid)a    where  myclassdate=@classdate and myclassid=@classid and 部門 is not null group by 部門with rollup)GO

 

3、純量值函式

USE [BW_Contrast]GO/****** Object:  UserDefinedFunction [dbo].[GetClassIDWithName]    Script Date: 01/15/2015 14:31:39 ******/IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N‘[dbo].[GetClassIDWithName]‘) AND type in (N‘FN‘, N‘IF‘, N‘TF‘, N‘FS‘, N‘FT‘))DROP FUNCTION [dbo].[GetClassIDWithName]GOUSE [BW_Contrast]GO/****** Object:  UserDefinedFunction [dbo].[GetClassIDWithName]    Script Date: 01/15/2015 14:31:39 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO/*-----------------------------------------------------------------------------                              《根據入井時間擷取相應的班次ID》-----------------------------------------------------------------------------參數：　　　[email protected]   入井時間傳回值：int型 班次ID-----------------------------------------------------------------------------                                 Written by -----------------------------------------------------------------------------*/create    function [dbo].[GetClassIDWithName](@InWellTime varchar(50))returns int asbegin  declare @returnValue int  set @returnValue=0    select @returnValue=classID from v_Class where 時間段名稱 =@InWellTime  return @returnValueend GO

 

sql自訂函數及C#中調用