摘要：
代码1--SQL自定义函数：23CREATEFUNCTION[GetProjectID](@headStrnvarchar(10),@datedatetime)4)56RETURNSNVARCHAR(200)78AS910BEGIN1112--不能在自定义函数中用INSERTINTO1314--insertintoemos_cust(cust_name,dates)values(1516--@he
代码1--SQL自定义函数：23CREATEFUNCTION[GetProjectID](@headStrnvarchar(10),@datedatetime)
4)
56RETURNSNVARCHAR(200)
78AS910BEGIN1112--不能在自定义函数中用INSERTINTO1314--insertintoemos_cust(cust_name,dates)values(1516--@headStr,@date1718--)1920return'TESTBYHANSHU'21END
4)
56RETURNSNVARCHAR(200)
78AS910BEGIN1112--不能在自定义函数中用INSERTINTO1314--insertintoemos_cust(cust_name,dates)values(1516--@headStr,@date1718--)1920return'TESTBYHANSHU'21END
代码1///<summary>2///获取项目文件编号 geovindu@163.com 涂聚文
3///</summary>4privatevoidFileNo()
5{
67SqlConnectionconn=newSqlConnection(connectionString);
8stringstrSql="GetProjectID";//自定SQL函數9SqlCommandcmd=newSqlCommand(strSql,conn);
10cmd.CommandType=CommandType.StoredProcedure;
11cmd.Parameters.Add("@headStr",SqlDbType.NVarChar).Value="ZQ3";//輸入參數12cmd.Parameters.Add("@date",SqlDbType.DateTime).Value=System.DateTime.Now.ToShortDateString();//輸入參數13cmd.Parameters.Add("@returnString",SqlDbType.NVarChar);
14cmd.Parameters["@returnString"].Direction=ParameterDirection.ReturnValue;//返回參數15try16{
17conn.Open();
18objecto=cmd.ExecuteScalar();
1920this.txtAFileNO.Text=cmd.Parameters["@returnString"].Value.ToString();
2122//Response.Write("");2324}
25catch(Exceptionex)
26{
2728this.txtAFileNO.Text=ex.Message;
2930}
31finally32{
3334if(!(conn.State==ConnectionState.Closed))
35{
3637conn.Close();
383940}
4142}
434445}
3///</summary>4privatevoidFileNo()
5{
67SqlConnectionconn=newSqlConnection(connectionString);
8stringstrSql="GetProjectID";//自定SQL函數9SqlCommandcmd=newSqlCommand(strSql,conn);
10cmd.CommandType=CommandType.StoredProcedure;
11cmd.Parameters.Add("@headStr",SqlDbType.NVarChar).Value="ZQ3";//輸入參數12cmd.Parameters.Add("@date",SqlDbType.DateTime).Value=System.DateTime.Now.ToShortDateString();//輸入參數13cmd.Parameters.Add("@returnString",SqlDbType.NVarChar);
14cmd.Parameters["@returnString"].Direction=ParameterDirection.ReturnValue;//返回參數15try16{
17conn.Open();
18objecto=cmd.ExecuteScalar();
1920this.txtAFileNO.Text=cmd.Parameters["@returnString"].Value.ToString();
2122//Response.Write("");2324}
25catch(Exceptionex)
26{
2728this.txtAFileNO.Text=ex.Message;
2930}
31finally32{
3334if(!(conn.State==ConnectionState.Closed))
35{
3637conn.Close();
383940}
4142}
434445}
/// <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;
}