【分享】SQL Parameter方法獲取數據源類

Question

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Collections;/// <summary>
/// clsDataBase 的摘要描述
/// </summary>
public class clsDataBase
{ 
    Auth.Auth Auth = new Auth.Auth();
    GP.GP Gp = new GP.GP();
    clsWebConfig webconfig = new clsWebConfig(); public clsDataBase()
{
    }    /// <summary>
    /// 從數據庫獲取DataTable（含SqlParameter）
    /// </summary>
    /// <param name="sql">指定查詢SQL語句</param>
    /// <param name="conn">數據庫連接</param> 
    /// <param name="ALParameter">SqlParameter對象集合（無參指定Null）</param>
    /// <returns></returns>
    public DataTable getDt(string sql, SqlConnection conn, ArrayList ALParameter)
    {
        DataSet ds = new DataSet();
        if (conn.State == ConnectionState.Closed)
        {
            conn.Open();
        }
        SqlCommand cmd = new SqlCommand(sql, conn);
        try
        {
            if (ALParameter != null && ALParameter.Count > 0)
            {
                foreach (SqlParameter para in ALParameter)
                {
                    cmd.Parameters.Add(para);
                }
            }            SqlDataAdapter da = new SqlDataAdapter(cmd);            
            da.Fill(ds);
        }
        catch
        {
            System.Web.HttpContext.Current.Response.Write("<script>window.alert('connect database error!');</script>");
            throw; //拋出一個錯誤,讓外部函數能夠捕捉到錯誤
        }
        finally
        {
            cmd.Dispose();
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
        }       
        return ds.Tables[0];
    }    /// <summary>
    /// 從數據庫獲取DataSet（含SqlParameter）
    /// </summary>
    /// <param name="sql">指定查詢SQL語句</param>
    /// <param name="conn">數據庫連接</param> 
    /// <param name="ALParameter">SqlParameter對象集合（無參指定Null）</param>
    /// <returns></returns>
    public DataSet GetDs(string sql, SqlConnection conn, ArrayList ALParameter)
    {
        if (conn.State == ConnectionState.Closed)
        {
            conn.Open();
        }
        SqlCommand cmd = new SqlCommand(sql, conn);
        DataSet ds = new DataSet();
        try
        {            if (ALParameter != null && ALParameter.Count > 0)
            {
                foreach (SqlParameter para in ALParameter)
                {
                    cmd.Parameters.Add(para);
                }
            }            SqlDataAdapter da = new SqlDataAdapter(cmd);            da.Fill(ds);
        }
        catch
        {
            System.Web.HttpContext.Current.Response.Write("<script>window.alert('connect database error!');</script>");
            throw; //拋出一個錯誤,讓外部函數能夠捕捉到錯誤
        }
        finally
        {
            cmd.Dispose();
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
        }
        return ds;
    }    /// <summary>
    /// 執行指定SQL Script （含事務）（含SqlParameter）
    /// </summary>
    /// <param name="sql">指定SQL語句（增改刪）</param>
    /// <param name="conn">數據庫連接</param>
    /// <param name="ALParameter">SqlParameter對象集合（無參指定Null）</param>
    public void GetCommand(string sql, SqlConnection conn,ArrayList ALParameter)
    {
        if (conn.State == ConnectionState.Closed)
        {
            conn.Open();             
        }
        SqlCommand cmd = conn.CreateCommand();
        try
        {
            cmd.Transaction = conn.BeginTransaction();
            cmd.CommandText = sql;
            if (ALParameter != null && ALParameter.Count > 0)
            {
                foreach (SqlParameter para in ALParameter)
                {
                    cmd.Parameters.Add(para);
                }
            }
            cmd.ExecuteNonQuery();
            cmd.Transaction.Commit();
        }
        catch
        {
            cmd.Transaction.Rollback();
            System.Web.HttpContext.Current.Response.Write("<script>window.alert('connect database error!');</script>");
            throw; //拋出一個錯誤,讓外部函數能夠捕捉到錯誤
        }
        finally
        {
            cmd.Dispose();
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
        }
    }    /// <summary>
    /// 執行指定SQL Script （不含事務）（含SqlParameter）
    /// </summary>
    /// <param name="sql">指定SQL語句（增改刪）</param>
    /// <param name="conn">數據庫連接</param>
    /// <param name="ALParameter">SqlParameter對象集合（無參指定Null）</param> 
    public void GetExecu(string sql, SqlConnection conn, ArrayList ALParameter)
    {
        if (conn.State == ConnectionState.Closed)
        {
            conn.Open();
        }
        SqlCommand cmd = new SqlCommand(sql, conn); 
        try
        {
            if (ALParameter != null && ALParameter.Count > 0)
            {
                foreach (SqlParameter para in ALParameter)
                {
                    cmd.Parameters.Add(para);
                }
            }
            cmd.ExecuteNonQuery();
        }
        catch
        { 
          System.Web.HttpContext.Current.Response.Write("<script>window.alert('connect database error!');</script>");
          throw; //拋出一個錯誤,讓外部函數能夠捕捉到錯誤
        }
        finally
        {
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
        }
    }        /// <summary>
    /// 執行指定SQL Script所影響的資料行（含事務）（含SqlParameter）
    /// </summary>
    /// <param name="sql">指定查詢SQL語句</param>
    /// <param name="conn">數據庫連接</param>
    /// <param name="ALParameter">SqlParameter對象集合（無參指定Null）</param> 
    public int Execute(string strSql, SqlConnection conn,ArrayList ALParameter)
    {
        if (conn.State == ConnectionState.Closed)
        {
            conn.Open();
        }
        SqlTransaction tran = conn.BeginTransaction();
        SqlCommand comm = conn.CreateCommand();
        if (ALParameter != null && ALParameter.Count > 0)
        {
            foreach (SqlParameter para in ALParameter)
            {
                comm.Parameters.Add(para);
            }
        }
        comm.Transaction = tran;
        comm.CommandText = strSql;
        int exe;
        try
        {            
            exe = comm.ExecuteNonQuery();
            tran.Commit();
        }
        catch (Exception ex)
        {
            try
            {
                tran.Rollback();
            }
            catch (SqlException sqlex)
            {
                if (tran.Connection != null)
                {
                    throw sqlex;
                }
            }
            throw ex;
        }
        finally
        {
            comm.Dispose();
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
        }
        return exe;
    }
    /// <summary>
    /// 獲取第一資料列的第一資料行（字符串）（含SqlParameter）
    /// </summary>
    /// <param name="strCmd">指定查詢SQL語句</param>
    /// <param name="myConn">數據庫連接</param>
    /// <param name="ALParameter">SqlParameter對象集合（無參指定Null）</param> 
    /// <returns></returns>
    public string GetSingle(string strCmd, SqlConnection myConn,ArrayList ALParameter)
    {
        if (myConn.State == ConnectionState.Closed)
        {
            myConn.Open();
        }
        string strSingle = string.Empty;
        SqlCommand myCmd = new SqlCommand(strCmd, myConn);
        try
        {
            if (ALParameter != null && ALParameter.Count > 0)
            {
                foreach (SqlParameter para in ALParameter)
                {
                    myCmd.Parameters.Add(para);
                }
            }            if (myCmd.ExecuteScalar() != null)
            {
                strSingle = myCmd.ExecuteScalar().ToString().Trim();
            }
        }
        catch
        {
            System.Web.HttpContext.Current.Response.Write("<script>window.alert('connect database error!');</script>");
            throw; //拋出一個錯誤,讓外部函數能夠捕捉到錯誤
        }
        finally
        {
            myCmd.Dispose();
            if (myConn.State == ConnectionState.Open)
            {
                myConn.Close();
            }
        }
        return strSingle;
    } 
}如有好的方法或者建議，一經證實，必有高分奉送！

Answer 1

   （一）查詢數據
string strCmd = "select * from employee where badge=@badge and comp_code=@comp_code"; //“@符號”是必須的
        ArrayList ALParameter = new ArrayList();
        ALParameter.Add(new SqlParameter("@badge", "80017152")); // 第一參數要不要“@符號”都可以，第二參數不可帶單引號
        ALParameter.Add(new SqlParameter("@comp_code","8000")); //第一參數要不要“@符號”都可以，第二參數不可帶單引號
        DataTable dt = this.getDt(strCmd, clsWebConfig.EmpConn, ALParameter);
        this.GridView1.DataSource = dt;
    this.GridView1.DataBind();

/// <summary>
    /// 從數據庫獲取DataTable（含SqlParameter）
    /// </summary>
    /// <param name="sql">指定查詢SQL語句</param>
    /// <param name="conn">數據庫連接</param> 
    /// <param name="ALParameter">SqlParameter對象集合（無參指定Null）</param>
    /// <returns></returns>
    public DataTable getDt(string sql, SqlConnection conn, ArrayList ALParameter)
    {
        DataSet ds = new DataSet();
        if (conn.State == ConnectionState.Closed)
        {
            conn.Open();
        }
        SqlCommand cmd = new SqlCommand(sql, conn);
        try
        {
            if (ALParameter != null && ALParameter.Count > 0)
            {
                foreach (SqlParameter para in ALParameter)
                {
                    cmd.Parameters.Add(para);
                }
            }            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds);
        }
        catch
        {
            System.Web.HttpContext.Current.Response.Write("<script>window.alert('connect database error!');</script>");
            throw; //拋出一個錯誤,讓外部函數能夠捕捉到錯誤
        }
        finally
        {
            cmd.Dispose();
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
        }
        return ds.Tables[0];
    }
（二）新增數據：
string strCmd = "insert into appraisal_config select '200501' as appraisalno,decline,datefrom,dateto,pastpage,pastprint,active,class,company from appraisal_config where appraisalno=@appraisalno and company=@company"; //“@符號”是必須的
        ArrayList ALParameter = new ArrayList();
        ALParameter.Add(new SqlParameter("@appraisalno", "200601")); //第一參數要不要“@符號”都可以，第二參數不可帶單引號
        ALParameter.Add(new SqlParameter("@company", "HK")); //第一參數要不要“@符號”都可以，第二參數不可帶單引號
        this.GetCommand(strCmd, clsWebConfig.DataConn, ALParameter);
（三）更新數據：
string strCmd = "update appraisal_config set class=@class where appraisalno=@appraisalno and company=@company";//“@符號”是必須的
        ArrayList ALParameter = new ArrayList();
        ALParameter.Add(new SqlParameter("@class", "SA")); //第一參數要不要“@符號”都可以，第二參數不可帶單引號
        ALParameter.Add(new SqlParameter("@appraisalno", "200601")); //第一參數要不要“@符號”都可以，第二參數不可帶單引號
        ALParameter.Add(new SqlParameter("@company", "HK")); //第一參數要不要“@符號”都可以，第二參數不可帶單引號
        this.GetCommand(strCmd, clsWebConfig.DataConn, ALParameter);
（四）刪除數據：
  string strCmd = "delete appraisal_config where appraisalno=@appraisalno and company=@company";//“@符號”是必須的
        ArrayList ALParameter = new ArrayList();
        ALParameter.Add(new SqlParameter("@appraisalno", "200501")); //第一參數要不要“@符號”都可以，第二參數不可帶單引號
        ALParameter.Add(new SqlParameter("@company", "HK")); //第一參數要不要“@符號”都可以，第二參數不可帶單引號
        this.GetCommand(strCmd, clsWebConfig.DataConn, ALParameter);    /// <summary>
    /// 執行指定SQL Script （含事務）（含SqlParameter）
    /// </summary>
    /// <param name="sql">指定SQL語句（增改刪）</param>
    /// <param name="conn">數據庫連接</param>
    /// <param name="ALParameter">SqlParameter對象集合（無參指定Null）</param>
    public void GetCommand(string sql, SqlConnection conn, ArrayList ALParameter)
    {
        if (conn.State == ConnectionState.Closed)
        {
            conn.Open();
        }
        SqlCommand cmd = conn.CreateCommand();
        try
        {
            cmd.Transaction = conn.BeginTransaction();
            cmd.CommandText = sql;
            if (ALParameter != null && ALParameter.Count > 0)
            {
                foreach (SqlParameter para in ALParameter)
                {
                    cmd.Parameters.Add(para);
                }
            }
            cmd.ExecuteNonQuery();
            cmd.Transaction.Commit();
        }
        catch
        {
            cmd.Transaction.Rollback();
            System.Web.HttpContext.Current.Response.Write("<script>window.alert('connect database error!');</script>");
            throw; //拋出一個錯誤,讓外部函數能夠捕捉到錯誤
        }
        finally
        {
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
            conn.Close();
        }
    }

Answer 2

using System;
using System.Collections;
using System.Data.SqlClient;
using System.Data;
using System.Text;namespace pm.Classes
{
/// <summary>
/// clsContractorStaff 的摘要描述。
/// </summary>
public class clsContractorStaff
{
public clsContractorStaff()
{
//
// TODO: 在此加入建構函式的程式碼
//
}
/// <summary>
/// 承建商員工某資料項目是否已在數據庫中存在
/// </summary>
/// <param name="strField">指定承建商員工資料項目（數據庫字段）</param>
/// <param name="strFieldValue">待判斷項目值</param>
/// <returns></returns>
public bool IsHasExistedInDataBase(string strField,string strFieldValue)
{
pm.Classes.clsSQLDB db = new pm.Classes.clsSQLDB(pm.Classes.clsSQLDB.ConnectionToData);
ArrayList ALParameter = new ArrayList();
ALParameter.Clear();
ALParameter.Add(new SqlParameter("@FieldValue",strFieldValue));
string strCmd = "select "+strField+" from pm_contractorstaff where "+strField+" = @FieldValue and (logout_date is null or  logout_date ='')";
DataSet ds = db.GetDataSet(strCmd,ALParameter);
return (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) ? true : false;
} /// <summary>
/// 獲取所有有效的承建商公司名稱及其字母簡稱（集合）
/// </summary>
/// <returns></returns>
public DataTable DTContractorName()
{
pm.Classes.clsSQLDB db = new pm.Classes.clsSQLDB(pm.Classes.clsSQLDB.ConnectionToData);
string strCmd = "select companyname,ab_company_edh from pm_contractor where valid='1'  order by ab_company_edh";
DataSet ds = db.GetDataSet(strCmd,null);
return ds.Tables[0];
} /// <summary>
/// 獲取指定承建商下所有未被注銷的所有員工姓名（集合）
/// </summary>
/// <returns></returns>
public DataTable DTContractorStaffCName(string strCompanyName)
{
pm.Classes.clsSQLDB db = new pm.Classes.clsSQLDB(pm.Classes.clsSQLDB.ConnectionToData);
string strCmd = "select staffbadge,staffcname from pm_contractorstaff where companyname=@companyname and (logout_date is null or logout_date ='') order by staffbadge ";
ArrayList ALParameter = new ArrayList();
ALParameter.Add(new SqlParameter("@companyname",strCompanyName));
DataSet ds = db.GetDataSet(strCmd,ALParameter);
return ds.Tables[0];
}
/// <summary>
/// 獲取指定承建商員工指定字段的數據值
/// </summary>
/// <param name="strStaffBadge">指定承建商員工工號</param>
/// <param name="strField">指定資料字段</param>
/// <returns></returns>
public string SpecialFieldValueInSQL(string strStaffBadge,string strField)
{
pm.Classes.clsSQLDB db = new pm.Classes.clsSQLDB(pm.Classes.clsSQLDB.ConnectionToData);
ArrayList ALParameter = new ArrayList();
ALParameter.Clear();
ALParameter.Add(new SqlParameter("@staffbadge",strStaffBadge));
string strCmd = "select "+strField+" from pm_contractorstaff where staffbadge = @staffbadge and (logout_date is null or logout_date = '') ";
DataSet ds = db.GetDataSet(strCmd,ALParameter);
return (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) ? ds.Tables[0].Rows[0][0].ToString().Trim() : string.Empty;
} /// <summary>
/// 新增指定承建商員工，獲取自動生成的員工工號
/// </summary>
/// <param name="strCompanyName">指定承建商公司名稱字母縮寫</param>
/// <returns>新增員工工號</returns>
public string StaffBadgeByCompanyName(string strAb_Company_Edh)
{
string strStaffBadge = string.Empty;
string strCmd = "select top 1 staffbadge from pm_contractorstaff where ab_company_edh=@ab_company_edh order by staffbadge desc";
ArrayList ALParameter = new ArrayList();
ALParameter.Add(new SqlParameter("@ab_company_edh",strAb_Company_Edh.Trim()));
pm.Classes.clsSQLDB db = new pm.Classes.clsSQLDB(pm.Classes.clsSQLDB.ConnectionToData);
DataSet ds = db.GetDataSet(strCmd,ALParameter);
if(ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
string strLastStaffBadge = ds.Tables[0].Rows[0]["staffbadge"].ToString().Trim();
string[] arrayStaffBadge = strLastStaffBadge.Split('-');
if(arrayStaffBadge.Length == 2)
{
int intCurrentSuffixNumber = Int16.Parse(arrayStaffBadge[1].Trim())+1;
switch(intCurrentSuffixNumber.ToString().Trim().Length)
{
case 1:
strStaffBadge = strAb_Company_Edh.Trim() + "-000" + intCurrentSuffixNumber.ToString().Trim();
break;
case 2:
strStaffBadge = strAb_Company_Edh.Trim() + "-00" + intCurrentSuffixNumber.ToString().Trim();
break;
case 3:
strStaffBadge = strAb_Company_Edh.Trim() + "-0" + intCurrentSuffixNumber.ToString().Trim();
break;
case 4:
strStaffBadge = strAb_Company_Edh.Trim() + "-" + intCurrentSuffixNumber.ToString().Trim();
break;
default:
break;
}
}
}
else
{
strStaffBadge = strAb_Company_Edh.Trim().ToUpper() + "-0001";
}
return strStaffBadge;
} /// <summary>
/// 獲取指定承建商員工信息（集合）
/// </summary>
/// <param name="strStaffBadge">指定承建商員工工號</param>
/// <returns>數據表</returns>
public DataTable DTContractorStaffDocument(string strStaffBadge)
{
StringBuilder sbCmd = new StringBuilder();
sbCmd.Append("select ");
sbCmd.Append("companyname,");
sbCmd.Append("ab_company_edh,");
sbCmd.Append("staffBadge,");
sbCmd.Append("staffCName,");
sbCmd.Append("IDnumber,");
sbCmd.Append("origin,");
sbCmd.Append("[8000floor],");
sbCmd.Append("A000floor,");
sbCmd.Append("staffre,");
sbCmd.Append("photopath,");
sbCmd.Append("trainexperience,");
sbCmd.Append("workexperience,");
sbCmd.Append("badrecord,");
sbCmd.Append("convert(char(10),train_date,101) as train_date,");
sbCmd.Append("trainhour,");
sbCmd.Append("traingrade,");
sbCmd.Append("trainre,");
sbCmd.Append("convert(char(10),register_date,101) as register_date,");
sbCmd.Append("convert(char(10),paper_validdate,101) as paper_validdate,");
sbCmd.Append("worktype,");
sbCmd.Append("guardglasses,");
sbCmd.Append("workclothes,");
sbCmd.Append("Laborensuregrade,");
sbCmd.Append("Laborensurere,");
sbCmd.Append("convert(char(10),logout_date,101) as logout_date,");
sbCmd.Append("logoutreason ");
sbCmd.Append("from pm_contractorstaff ");
sbCmd.Append("where staffbadge=@staffbadge ");
sbCmd.Append("and (logout_date is null or logout_date = '') ");
ArrayList ALParameter = new ArrayList();
ALParameter.Add(new SqlParameter("@staffbadge",strStaffBadge.Trim()));
pm.Classes.clsSQLDB db = new pm.Classes.clsSQLDB(pm.Classes.clsSQLDB.ConnectionToData);
DataSet ds = db.GetDataSet(sbCmd.ToString().Trim(),ALParameter);
return (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) ? ds.Tables[0] : null;
}

Answer 3

/// <summary>
/// 在數據庫中插入新增承建商員工信息
/// </summary>
/// <param name="htContractorStaff">新增承建商員工工號</param>
public void InsertNewContractorStaffDocument(Hashtable htContractorStaffDocument)
{
StringBuilder sbCmd = new StringBuilder();
sbCmd.Append("select staffbadge from pm_contractorstaff where staffbadge=@staffbadge ");
sbCmd.Append("if(@@rowcount = 0) ");
sbCmd.Append("insert pm_contractorstaff ");
sbCmd.Append("(");
sbCmd.Append("companyname,");
sbCmd.Append("ab_company_edh,");
sbCmd.Append("staffBadge,");
sbCmd.Append("staffCName,");
sbCmd.Append("IDnumber,");
sbCmd.Append("origin,");
sbCmd.Append("[8000floor],");
sbCmd.Append("A000floor,");
sbCmd.Append("staffre,");
sbCmd.Append("photopath,");
sbCmd.Append("trainexperience,");
sbCmd.Append("workexperience,");
sbCmd.Append("badrecord,");
sbCmd.Append("train_date,");
sbCmd.Append("trainhour,");
sbCmd.Append("traingrade,");
sbCmd.Append("trainre,");
sbCmd.Append("register_date,");
sbCmd.Append("paper_validdate,");
sbCmd.Append("worktype,");
sbCmd.Append("guardglasses,");
sbCmd.Append("workclothes,");
sbCmd.Append("Laborensuregrade,");
sbCmd.Append("Laborensurere,");
sbCmd.Append("operationtime ");
sbCmd.Append(") ");
sbCmd.Append("values(");
sbCmd.Append("@companyname,");
sbCmd.Append("@ab_company_edh,");
sbCmd.Append("@staffBadge,");
sbCmd.Append("@staffCName,");
sbCmd.Append("@IDnumber,");
sbCmd.Append("@origin,");
sbCmd.Append("@8000floor,");
sbCmd.Append("@A000floor,");
sbCmd.Append("@staffre,");
sbCmd.Append("@photopath,");
sbCmd.Append("@trainexperience,");
sbCmd.Append("@workexperience,");
sbCmd.Append("@badrecord,");
sbCmd.Append("@train_date,");
sbCmd.Append("@trainhour,");
sbCmd.Append("@traingrade,");
sbCmd.Append("@trainre,");
sbCmd.Append("@register_date,");
sbCmd.Append("@paper_validdate,");
sbCmd.Append("@worktype,");
sbCmd.Append("@guardglasses,");
sbCmd.Append("@workclothes,");
sbCmd.Append("@Laborensuregrade,");
sbCmd.Append("@Laborensurere,");
sbCmd.Append("getdate() ");
sbCmd.Append(") ");
sbCmd.Append("else ");
sbCmd.Append("update pm_contractorstaff ");
sbCmd.Append("set ");
sbCmd.Append("companyname=@companyname,");
sbCmd.Append("ab_company_edh=@ab_company_edh,");
sbCmd.Append("staffBadge=@staffBadge,");
sbCmd.Append("staffCName=@staffCName,");
sbCmd.Append("IDnumber=@IDnumber,");
sbCmd.Append("origin=@origin,");
sbCmd.Append("[8000floor]=@8000floor,");
sbCmd.Append("A000floor=@A000floor,");
sbCmd.Append("staffre=@staffre,");
sbCmd.Append("photopath=@photopath,");
sbCmd.Append("trainexperience=@trainexperience,");
sbCmd.Append("workexperience=@workexperience,");
sbCmd.Append("badrecord=@badrecord,");
sbCmd.Append("train_date=@train_date,");
sbCmd.Append("trainhour=@trainhour,");
sbCmd.Append("traingrade=@traingrade,");
sbCmd.Append("trainre=@trainre,");
sbCmd.Append("register_date=@register_date,");
sbCmd.Append("paper_validdate=@paper_validdate,");
sbCmd.Append("worktype=@worktype,");
sbCmd.Append("guardglasses=@guardglasses,");
sbCmd.Append("workclothes=@workclothes,");
sbCmd.Append("Laborensuregrade=@Laborensuregrade,");
sbCmd.Append("Laborensurere=@Laborensurere,");
sbCmd.Append("operationtime=getdate() ");
sbCmd.Append("where staffbadge=@staffbadge ");
sbCmd.Append("and (logout_date is null or logout_date = '') "); ArrayList ALParameter = new ArrayList();
ALParameter.Add(new SqlParameter("@companyname",htContractorStaffDocument["companyname"].ToString().Trim()));
ALParameter.Add(new SqlParameter("@ab_company_edh",htContractorStaffDocument["ab_company_edh"].ToString().Trim()));
ALParameter.Add(new SqlParameter("@staffBadge",htContractorStaffDocument["staffBadge"].ToString().Trim()));
ALParameter.Add(new SqlParameter("@staffCName",htContractorStaffDocument["staffCName"].ToString().Trim()));
ALParameter.Add(new SqlParameter("@IDnumber",htContractorStaffDocument["IDnumber"].ToString().Trim()));
ALParameter.Add(new SqlParameter("@origin",htContractorStaffDocument["origin"].ToString().Trim()));
ALParameter.Add(new SqlParameter("@8000floor",htContractorStaffDocument["8000floor"].ToString().Trim()));
ALParameter.Add(new SqlParameter("@A000floor",htContractorStaffDocument["A000floor"].ToString().Trim()));
ALParameter.Add(new SqlParameter("@staffre",htContractorStaffDocument["staffre"].ToString().Trim()));
ALParameter.Add(new SqlParameter("@photopath",htContractorStaffDocument["photopath"].ToString().Trim()));
ALParameter.Add(new SqlParameter("@trainexperience",htContractorStaffDocument["trainexperience"].ToString().Trim()));
ALParameter.Add(new SqlParameter("@workexperience",htContractorStaffDocument["workexperience"].ToString().Trim()));
ALParameter.Add(new SqlParameter("@badrecord",htContractorStaffDocument["badrecord"].ToString().Trim()));
ALParameter.Add(new SqlParameter("@train_date",htContractorStaffDocument["train_date"].ToString().Trim()));
ALParameter.Add(new SqlParameter("@trainhour",htContractorStaffDocument["trainhour"].ToString().Trim()));
ALParameter.Add(new SqlParameter("@traingrade",htContractorStaffDocument["traingrade"].ToString().Trim()));
ALParameter.Add(new SqlParameter("@trainre",htContractorStaffDocument["trainre"].ToString().Trim()));
ALParameter.Add(new SqlParameter("@register_date",htContractorStaffDocument["register_date"].ToString().Trim()));
ALParameter.Add(new SqlParameter("@paper_validdate",htContractorStaffDocument["paper_validdate"].ToString().Trim()));
ALParameter.Add(new SqlParameter("@worktype",htContractorStaffDocument["worktype"].ToString().Trim()));
ALParameter.Add(new SqlParameter("@guardglasses",htContractorStaffDocument["guardglasses"].ToString().Trim()));
ALParameter.Add(new SqlParameter("@workclothes",htContractorStaffDocument["workclothes"].ToString().Trim()));
ALParameter.Add(new SqlParameter("@Laborensuregrade",htContractorStaffDocument["laborensuregrade"].ToString().Trim()));
ALParameter.Add(new SqlParameter("@Laborensurere",htContractorStaffDocument["laborensurere"].ToString().Trim()));
pm.Classes.clsSQLDB db = new pm.Classes.clsSQLDB(pm.Classes.clsSQLDB.ConnectionToData);
db.ExcuteSQL(sbCmd.ToString().Trim(),ALParameter);
}

Answer 4

/// <summary>
/// 在數據庫中注銷承建商員工信息
/// </summary>
/// <param name="htContractorStaff">待注銷信息的承建商員工工號</param>
public void LogoutContractorStaffDocument(Hashtable htContractorStaffDocument)
{
StringBuilder sbCmd = new StringBuilder();
sbCmd.Append("update pm_contractorstaff ");
sbCmd.Append("set ");
sbCmd.Append("logout_date=@logout_date,");
sbCmd.Append("logoutreason=@logoutreason,");
sbCmd.Append("operationtime=getdate() ");
sbCmd.Append("where staffbadge=@staffbadge ");
sbCmd.Append("and (logout_date is null or logout_date = '') "); ArrayList ALParameter = new ArrayList();
ALParameter.Add(new SqlParameter("@staffbadge",htContractorStaffDocument["staffBadge"].ToString().Trim()));
ALParameter.Add(new SqlParameter("@logout_date",htContractorStaffDocument["logout_date"].ToString().Trim()));
ALParameter.Add(new SqlParameter("@logoutreason",htContractorStaffDocument["logoutreason"].ToString().Trim()));
pm.Classes.clsSQLDB db = new pm.Classes.clsSQLDB(pm.Classes.clsSQLDB.ConnectionToData);
db.ExcuteSQL(sbCmd.ToString().Trim(),ALParameter);
}

/// <summary>
/// 獲取滿足指定檢索條件的承建商員工信息（集合）
/// </summary>
/// <param name="strCompanyName">指定承建商公司名稱</param>
/// <param name="strSearchField">指定檢索字段</param>
/// <param name="strSearchValue">指定檢索值</param>
/// <returns>DataTable</returns>
public DataTable DTContractorStaffDocumentBySearchField(string strCompanyName,string strSearchField,string strSearchValue)
{
StringBuilder sbCmd = new StringBuilder();
sbCmd.Append("select ");
sbCmd.Append("companyname,");
sbCmd.Append("ab_company_edh,");
sbCmd.Append("staffBadge,");
sbCmd.Append("staffCName,");
sbCmd.Append("IDnumber,");
sbCmd.Append("origin,");
sbCmd.Append("[8000floor],");
sbCmd.Append("A000floor,");
sbCmd.Append("staffre,");
sbCmd.Append("photopath,");
sbCmd.Append("trainexperience,");
sbCmd.Append("workexperience,");
sbCmd.Append("badrecord,");
sbCmd.Append("convert(char(10),train_date,101) as train_date,");
sbCmd.Append("trainhour,");
sbCmd.Append("traingrade,");
sbCmd.Append("trainre,");
sbCmd.Append("convert(char(10),register_date,101) as register_date,");
sbCmd.Append("convert(char(10),paper_validdate,101) as paper_validdate,");
sbCmd.Append("worktype,");
sbCmd.Append("case when (guardglasses='1' ) then  N'使用' else N'歸還' end as guardglasses,");
sbCmd.Append("case when (workclothes='1' ) then  N'使用' else N'歸還' end as workclothes,");
sbCmd.Append("Laborensuregrade,");
sbCmd.Append("Laborensurere,");
sbCmd.Append("convert(char(10),logout_date,101) as logout_date,");
sbCmd.Append("logoutreason,");
sbCmd.Append("case when (logout_date is null or logout_date='')  then  N'有效' else N'注銷' end as valid,");
sbCmd.Append("case when (logout_date is null or logout_date='')  then  1 else 0 end as validsort ");
sbCmd.Append("from pm_contractorstaff ");
sbCmd.Append("where companyname like N'%'+@companyname+'%' and "+strSearchField+" like '%'+@searchvalue+'%' ");
sbCmd.Append("order by validsort desc,staffbadge ");
ArrayList ALParameter = new ArrayList();
ALParameter.Add(new SqlParameter("@companyname",strCompanyName.Trim()));
ALParameter.Add(new SqlParameter("@searchvalue",strSearchValue.Trim()));
pm.Classes.clsSQLDB db = new pm.Classes.clsSQLDB(pm.Classes.clsSQLDB.ConnectionToData);
DataSet ds = db.GetDataSet(sbCmd.ToString().Trim(),ALParameter);
return (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) ? ds.Tables[0] : null;
}
public DataTable DTContractorStaffDocumentByBatchStaffBadge(string strBatchStaffBadge)
{
StringBuilder sbCmd = new StringBuilder();
sbCmd.Append("select ");
sbCmd.Append("row_number() over(order by staffbadge)-1 rowindex,");
sbCmd.Append("companyname,");
sbCmd.Append("ab_company_edh,");
sbCmd.Append("staffBadge,");
sbCmd.Append("staffCName,");
sbCmd.Append("IDnumber,");
sbCmd.Append("origin,");
sbCmd.Append("[8000floor] as floor,");
sbCmd.Append("A000floor,");
sbCmd.Append("staffre,");
sbCmd.Append("photopath=(case when(photopath is not null and photopath <>'') then photopath else '../../Image/NoPhotoOnBrand.JPG' end),");
sbCmd.Append("trainexperience,");
sbCmd.Append("workexperience,");
sbCmd.Append("badrecord,");
sbCmd.Append("convert(char(10),train_date,101) as train_date,");
sbCmd.Append("trainhour,");
sbCmd.Append("traingrade,");
sbCmd.Append("trainre,");
sbCmd.Append("convert(char(10),register_date,101) as register_date,");
sbCmd.Append("convert(char(10),paper_validdate,101) as paper_validdate,");
sbCmd.Append("worktype,");
sbCmd.Append("case when (guardglasses='1' ) then  N'使用' else N'歸還' end as guardglasses,");
sbCmd.Append("case when (workclothes='1' ) then  N'使用' else N'歸還' end as workclothes,");
sbCmd.Append("Laborensuregrade,");
sbCmd.Append("Laborensurere,");
sbCmd.Append("convert(char(10),logout_date,101) as logout_date,");
sbCmd.Append("logoutreason,");
sbCmd.Append("case when (logout_date is null or logout_date='')  then  N'有效' else N'注銷' end as valid,");
sbCmd.Append("case when (logout_date is null or logout_date='')  then  1 else 0 end as validsort ");
sbCmd.Append("from pm_contractorstaff ");
sbCmd.Append("where staffbadge in("+strBatchStaffBadge.Trim()+") ");
sbCmd.Append("order by validsort desc,staffbadge ");

pm.Classes.clsSQLDB db = new pm.Classes.clsSQLDB(pm.Classes.clsSQLDB.ConnectionToData);
DataSet ds = db.GetDataSet(sbCmd.ToString().Trim(),null);
return (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) ? ds.Tables[0] : null;
} }
}