貓~程式碼集散區

 

 #region =====執行SQLCommand(含Insert/Update/Delete)=====

      /// 

    /// 執行SQLCommand(含Insert/Update/Delete)
    /// 1.傳入
    /// strConn：資料庫連線字串
    /// strCommand：SQL指令字串
    /// cmdType:SqlCommand執行型態(1:StoreProcedure/2:Text)
    /// lstParamVariable：SQL參數名稱字串陣列
    /// lstParamValue：SQL參數值字串陣列
    /// 2.傳出 ：null
    /// 
    ///資料庫連線字串
    ///SQL指令字串
    ///SqlCommand執行型態(1:StoreProcedure/2:Text)
    ///SQL參數名稱字串陣列
    ///SQL參數值字串陣列
    public void ExecuteCommand(string strConn, string strCommand, string cmdType, ref string[] lstParamVariable, ref string[] lstParamValue)
    {
        using (SqlConnection conn = new SqlConnection())
        {
            conn.ConnectionString = strConn;//ConfigurationManager.ConnectionStrings["ConnStr"].ToString();

            using (SqlCommand cmd = new SqlCommand())
            {

                cmd.CommandText = strCommand;
                cmd.CommandType = (cmdType == "1" ? CommandType.StoredProcedure : CommandType.Text);
                cmd.Connection = conn;

                try
                {
                    cmd.Connection.Open();
                    for (int i = 0; i < lstParamVariable.Length; i++)
                    {
                         cmd.Parameters.AddWithValue(lstParamVariable[i], lstParamValue[i]);
                    }
                    cmd.ExecuteNonQuery();
                    cmd.Connection.Close();

                    //MessageBox.Show("存檔成功!");
                }
                catch (Exception e1)
                {
                    //MessageBox.Show("存檔失敗!" + e1.Message.ToString());
                }
                finally
                {

                }
            }
        }
    }
    #endregion

 #region =====取得資料集(DataTable)=====

   /// 

    ///  取得資料集(DataSet)
    /// 1.傳入 
    ///  strConn：資料庫連線字串
    ///  strCommand：SQL指令字串
    /// lstParamVariable：SQL參數名稱字串陣列
    ///  lstParamValue：SQL參數值字串陣列
    ///  2.傳出 ：DataSet
    /// 
    ///資料庫連線字串
    ///SQL指令字串
    ///SQL參數名稱字串陣列
    ///SQL參數值字串陣列
    public DataSet GetFreshData(string strConn, string strCommand, ref string[] lstParamVariable, ref string[] lstParamValue)
    {

        using (SqlConnection conn = new SqlConnection())
        {
            conn.ConnectionString = strConn;//ConfigurationManager.ConnectionStrings["ConnStr"].ToString();

            using (SqlDataAdapter da = new SqlDataAdapter())
            {
                da.SelectCommand = new SqlCommand();
                da.SelectCommand.CommandText = strCommand;
                da.SelectCommand.CommandType = CommandType.StoredProcedure;
                da.SelectCommand.Connection = conn;

                DataSet table = new DataSet();

                try
                {
                    for (int i = 0; i < lstParamVariable.Length; i++)
                    {
                        da.SelectCommand.Parameters.AddWithValue(lstParamVariable[i], lstParamValue[i]);
                    }
                    da.SelectCommand.Connection.Open();
                    table.Locale = System.Globalization.CultureInfo.InvariantCulture;
                    da.Fill(table);
                    da.SelectCommand.Connection.Close();
                }
                catch (Exception ex)
                {
                    return null;
                }
                finally
                {
                    // do other things...calling Close() or Dispose() 
                    // for SqlConnection or SqlDataAdapter objects not necessary
                    // as its taken care of in the nested "using" statements
                }

                return table;
            }
        }


    }
    #endregion

使用方式

        protected ConnectionStringSettings connstr = ConfigurationManager.ConnectionStrings["WEBConnectionString"];

    public DataSet STP_SMS_ECARD_01(string vCod)
    {
        string[] lstParamVariable = new string[] { "@vCod"};
        string[] lstParamValue = new string[] { vCod };
        DataSet ds = SDS.GetFreshData(connstr.ConnectionString, "proc name", ref lstParamVariable, ref lstParamValue);
        return ds;
    }