SQL custom functions and calls in C #

1. Calling SQL custom functions in C #

1.1 Scalar-valued functions

The SQL statement calls select dbo. Getclassidwithname (1)

String strSQL = String. Format ("SELECT dbo. Getclassidwithname (' {0} ') ", dttime);
DataTable dt = DB_Contrast.DB.OleDbHelper.GetDataTable (strSQL);

1.2 Table-Valued functions

SQL statement calls select * from Getanalysis (' 2015-1-15 ', 1)

String strSQL = String. Format ("SELECT * FROM dbo. Getanalysis (' {0} ', {1}) where Department = ' {2} ' ", Dttime, ClassID," development ");
DataSet ds = DB_Contrast.DB.OleDbHelper.GetDataSet (strSQL);

2, table-valued function,

Inner Select Gets a record that is not duplicated

The outer layers are grouped by department

 Use [Bw_contrast]GO/** * * * object:userdefinedfunction [dbo].    [Getanalysis] Script date:01/15/2015 13:09:17 * * * * **/IF  EXISTS(SELECT *  fromSys.objectsWHERE object_id = object_id(N'[dbo]. [Getanalysis]') andTypeinch(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 * * * * **/SETAnsi_nulls onGOSETQuoted_identifier onGO   /*-----------------------------------------------------------------------------"Get the corresponding shift ID according to the time of entry"-------------         ----------------------------------------------------------------parameters: 1, @classdate shift date 2, @classid shift ID Return value: 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. Department)=1) Then 'Total' ElseA. DepartmentEnd  asDepartment, (Select Top 1Id fromDbo.v_deptwhereDepartment Name=A. Department) asDeptID,Sum( Case  whenDt_realtime is  not NULL  andMyclassid=@classid   Then 1 Else 0 End) asnumber of assigned classes,Sum( Case  whenDt_gettime is  not NULL  andMyclassid=@classid   Then 1 Else 0 End) asnumber of LED lights,Sum( Case  whenDtinwelltime is  not NULL  andMyclassid=@classid   Then 1 Else 0 End) asnumber of down wells,Sum( Case  whenDt_outwelltime is  not NULL  andMyclassid=@classid   Then 1 Else 0 End) asthe number of wells,Sum( Case  whenDt_returntime is  not NULL  andMyclassid=@classid   Then 1 Else 0 End) asNumber of Lights from(    Selectdepartment, Deptid,myclassdate,myclassid,mypersonid,min(Dt_realtime) asDt_realtime,min(Dt_gettime) asDt_gettime,min(Dtinwelltime) asDtinwelltime,min(Dt_outwelltime) asDt_outwelltime,min(Dt_returntime) asDt_returntime fromv_all_newwhereMyclassdate=@classdate  andMyclassid=@classid    Group  byDepartment, Myclassdate,myclassid,mypersonid,deptid) awhereMyclassdate=@classdate  andMyclassid=@classid  andDepartment is  not NULL Group  byDepartment withrollup)GO

3. Scalar value function

 Use [Bw_contrast]GO/** * * * object:userdefinedfunction [dbo].    [Getclassidwithname] Script date:01/15/2015 14:31:39 * * * * **/IF  EXISTS(SELECT *  fromSys.objectsWHERE object_id = object_id(N'[dbo]. [Getclassidwithname]') andTypeinch(N'FN'N'IF'N'TF'N'FS'N'FT'))DROP FUNCTION [dbo].[Getclassidwithname]GO Use [Bw_contrast]GO/** * * * object:userdefinedfunction [dbo].    [Getclassidwithname] Script date:01/15/2015 14:31:39 * * * * **/SETAnsi_nulls onGOSETQuoted_identifier onGO/*-----------------------------------------------------------------------------"according to the time to get the appropriate   Shift ID-----------------------------------------------------------------------------Parameters: [email protected]                                 Enter time return value: INT type shift ID----------------------------------------------------------------------------- Written by-----------------------------------------------------------------------------*/Create    function [dbo].[Getclassidwithname](@InWellTime varchar( -))returns int  asbegin  Declare @returnValue int  Set @returnValue=0    Select @returnValue=ClassID fromV_classwhereTime period Name=@InWellTime  return @returnValueEnd GO

SQL custom functions and calls in C #