代码
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
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 }
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;
}