csharp(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     /// 获取项目文件编号 geovindu@163.com 涂聚文
 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     }

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34

/// <summary> /// 通过部门ID自定义函数 得到子部门ID /// 涂聚文Geovin Du /// sql server 2012 /// </summary> /// <returns></returns> public string GetDeptChildrenGroupId(int depid) {     string dep = string.Empty;     try     {                   SqlParameter[] par = new SqlParameter[]{         new SqlParameter("@DeptID",SqlDbType.Int,10),         new SqlParameter("@RValue",SqlDbType.NVarChar,1000),//      返回參参数2012； 2005,2000不需要         };         par[0].Direction = ParameterDirection.Input;         par[1].Direction = ParameterDirection.ReturnValue;  //返回參参数 涂聚文 2016-07-25         par[0].Value = depid;         //object reader = DBHelper.GetObject("select "+DBHelper.GetRole()+".GetDeptChildrenGroup("+depid.ToString()+")", CommandType.Text, par);   //sql server 2012         //object reader = DBHelper.GetObject("GetDeptChildrenGroup", CommandType.StoredProcedure, par);//sql server 2005,2000         object reader = DBHelper.GetObject(DBHelper.GetRole() + ".GetDeptChildrenGroup", CommandType.StoredProcedure, par);                        //dep = reader.ToString();   //2005，2000 用                      dep = par[1].Value.ToString(); //2012         }     catch (SqlException ex)     {         throw ex;     }     return dep; }