C#中執行SQL的幾種方法講解

private InfoLightDBTools dbTools;       //#ORACLE#
this.dbTools = new InfoLightDBTools(clientInfo, dbName);

private InfoLightDBTXTools dbTXTools;  //#ORACLE# 要結合事務處理使用：BeginTransaction(),Commit(),Rollback(),EndTransaction()

this.dbTXTools = new InfoLightDBTXTools(clientInfo, dbName);

private InfoLightMSTools mdbtool;    //#SQL SERVER#

public DataTable GetManPowerData(string vLineName, string vShift)
        {
            DataTable dt = new DataTable();
            ExecutionResult exeRes = new ExecutionResult();
            List<SqlParameter> mParams;
            SqlParameter mLine_NameParam, mDNSParam;
            string sql = @"select *
  from manpower
 where lineid = (select ID
                   from lines
                  where line = @LINENAME
                    AND (SMTCS IS NULL OR SMTCS = 'C'))
   and dns = @dns
 order by time1_start
";


            mParams = new List<SqlParameter>();
            mLine_NameParam = new SqlParameter("@LINENAME", SqlDbType.VarChar, 20);
            mLine_NameParam.Value = vLineName;
            mParams.Add(mLine_NameParam);

            mDNSParam = new SqlParameter("@dns", SqlDbType.VarChar, 20);
            mDNSParam.Value = 0;
            mParams.Add(mDNSParam);


            exeRes = this.rMSDBTools.ExecuteQueryDS(sql, mParams);
            if (exeRes.Status)
                dt = ((DataSet)exeRes.Anything).Tables[0];

            return dt;
        }

public DataTable getModel(string line_name)
        {
            DataTable dt = new DataTable();
            ExecutionResult exeRes = new ExecutionResult();
            DBParameter dbParam = new DBParameter();
            string sql = @"   SELECT DISTINCT T.MODEL_NAME
    FROM sfism4.R_LINE_MODEL_STATUS_T t
   WHERE T.LINE_NAME = :line_name
";
            dbParam.Clear();
            dbParam.Add("line_name", OracleType.VarChar, line_name);
            exeRes = this.mdbtools.ExecuteQueryDS(sql, dbParam.GetParameters());
            if (exeRes.Status)
                dt = ((DataSet)exeRes.Anything).Tables[0];
            return dt;
        }

public ExecutionResult DoDelete(string sap_plant, string wip_sn, string up_data1)
        {
            ExecutionResult exeRes = new ExecutionResult();
            exeRes.Message = "";
            DBParameter dbParam = new DBParameter();
            #region sql
            string sql = @"  DELETE SFISM4.U_UP2INTERFACE_TODOLIST_T A
 WHERE A.CUST_NO = :CUST_NO
   AND A.TYPE = 'IMS_STOP'
   AND A.JOB_STATUS = 'OK'
   AND A.WIP_SN = :WIP_SN
   AND A.UP_DATA1 = :UP_DATA1
";
            #endregion

            #region Param
            dbParam.Clear();
            dbParam.Add("CUST_NO", OracleType.NVarChar, sap_plant);
            dbParam.Add("WIP_SN", OracleType.NVarChar, wip_sn);
            dbParam.Add("UP_DATA1", OracleType.NVarChar, up_data1);
            #endregion
            try
            {
                exeRes = this.mdbtools.ExecuteUpdate(sql, dbParam.GetParameters());
            }
            catch (Exception ex)
            {
                exeRes.Message += ex.Message;
                exeRes.Status = false;
            }
            return exeRes;
        }