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     }

        /// <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;
        }