public DataTable SelectData(Dictionary <string, ValuePair> dic, string sp) { DataTable dt = new DataTable(); try { command = new SqlCommand(sp, GetConnection()) { CommandType = CommandType.StoredProcedure, }; foreach (KeyValuePair <string, ValuePair> pair in dic) { ValuePair vp = pair.Value; AddParam(command, pair.Key, vp.value1, vp.value2); } command.CommandTimeout = string.IsNullOrWhiteSpace(iniEntity.TimeoutValues) ? 0 : Convert.ToInt32(iniEntity.TimeoutValues); adapter = new SqlDataAdapter(command); adapter.Fill(dt); } catch (Exception ex) { //var msg = ex.Message; throw ex; } return(dt); }
/// <summary> /// ktp 2019-05-29 to select easily /// </summary> /// <param name="fields">selectしたい項目</param> /// <param name="tableName">テーブルめい</param> /// <param name="condition">条件</param> /// <returns></returns> //public DataTable SimpleSelect(string fields, string tableName, string condition) //{ // Dictionary<string, ValuePair> dic = new Dictionary<string, ValuePair> // { // { "@Fields", new ValuePair { value1 = SqlDbType.VarChar, value2 = fields } }, // { "@Table", new ValuePair { value1 = SqlDbType.VarChar, value2 = tableName } }, // { "@Condition", new ValuePair { value1 = SqlDbType.VarChar, value2 = condition } } // }; // DataTable dt = new DataTable(); // command = new SqlCommand("Simple_Select", GetConnection()) // { // CommandType = CommandType.StoredProcedure, // }; // foreach (KeyValuePair<string, ValuePair> pair in dic) // { // ValuePair vp = pair.Value; // AddParam(command, pair.Key, vp.value1, vp.value2); // } // adapter = new SqlDataAdapter(command); // adapter.Fill(dt); // return dt; //} public bool InsertUpdateDeleteData(Dictionary <string, ValuePair> dic, string sp) { try { if (UseTransaction) { StartTransaction(); command = new SqlCommand(sp, GetConnection(), transaction); } else { command = new SqlCommand(sp, GetConnection()); } command.CommandType = CommandType.StoredProcedure; foreach (KeyValuePair <string, ValuePair> pair in dic) { ValuePair vp = pair.Value; AddParam(command, pair.Key, vp.value1, vp.value2); } if (!UseTransaction) { command.Connection.Open(); } command.ExecuteNonQuery(); if (UseTransaction) { CommitTransaction(); } return(true); } catch (Exception e) { if (UseTransaction) { RollBackTransaction(); } //return false; 2019.6.12 chg throw e; } finally { command.Connection.Close(); } }
public DataTable Select_SearchName(Dictionary <string, ValuePair> dic, string sp) { DataTable dt = new DataTable(); command = new SqlCommand("Select_SearchName", GetConnection()) { CommandType = CommandType.StoredProcedure, }; foreach (KeyValuePair <string, ValuePair> pair in dic) { ValuePair vp = pair.Value; AddParam(command, pair.Key, vp.value1, vp.value2); } adapter = new SqlDataAdapter(command); adapter.Fill(dt); return(dt); }
/// <summary> /// /// </summary> /// <param name="operationMode"></param> /// <param name="operatorID"></param> /// <param name="pc"></param> /// <param name="storeCD"></param> /// <param name="staffCD"></param> /// <param name="hacchuuDate"></param> /// <param name="dtTIkkatuHacchuuNyuuryoku"></param> /// <returns></returns> public bool PRC_IkkatuHacchuuNyuuryoku_Register(int operationMode, string operatorID, string pc, string storeCD, string staffCD, string orderDate, string orderNO, string orderProcessNO, DataTable dtTIkkatuHacchuuNyuuryoku) { Dictionary <string, ValuePair> dic = new Dictionary <string, ValuePair> { { "@p_OperateMode", new ValuePair { value1 = SqlDbType.Int, value2 = operationMode.ToString() } }, { "@p_Operator", new ValuePair { value1 = SqlDbType.VarChar, value2 = operatorID } }, { "@p_PC", new ValuePair { value1 = SqlDbType.VarChar, value2 = pc } }, { "@p_StoreCD", new ValuePair { value1 = SqlDbType.VarChar, value2 = storeCD } }, { "@p_StaffCD", new ValuePair { value1 = SqlDbType.VarChar, value2 = staffCD } }, { "@p_OrderDate", new ValuePair { value1 = SqlDbType.Date, value2 = orderDate } }, { "@p_OrderNO", new ValuePair { value1 = SqlDbType.VarChar, value2 = orderNO } }, { "@p_OrderProcessNO", new ValuePair { value1 = SqlDbType.VarChar, value2 = orderProcessNO } }, }; bool ret = true; string sp = "PRC_IkkatuHacchuuNyuuryoku_Register"; try { StartTransaction(); command = new SqlCommand(sp, GetConnection(), transaction); command.CommandType = CommandType.StoredProcedure; foreach (KeyValuePair <string, ValuePair> pair in dic) { ValuePair vp = pair.Value; AddParam(command, pair.Key, vp.value1, vp.value2); } SqlParameter p = command.Parameters.AddWithValue("@p_TIkkatuHacchuuNyuuryoku", dtTIkkatuHacchuuNyuuryoku); p.SqlDbType = SqlDbType.Structured; command.ExecuteNonQuery(); CommitTransaction(); ret = true; } catch (Exception e) { RollBackTransaction(); ret = false; throw e; } finally { command.Connection.Close(); } return(ret); }
/// <summary> /// 未出力分の処理 /// </summary> /// <param name="doe"></param> /// <param name="dee"></param> /// <returns></returns> public bool PRC_EDIOrder_Insert(D_Order_Entity doe, D_EDIOrder_Entity dee) { DateTime dtNow = DateTime.Now; try { StartTransaction(); //EDI発注追加 Dictionary <string, ValuePair> dic = new Dictionary <string, ValuePair> { { "@StoreCD", new ValuePair { value1 = SqlDbType.VarChar, value2 = doe.StoreCD } }, { "@OrderDateFrom", new ValuePair { value1 = SqlDbType.VarChar, value2 = doe.OrderDateFrom } }, { "@OrderDateTo", new ValuePair { value1 = SqlDbType.VarChar, value2 = doe.OrderDateTo } }, { "@StaffCD", new ValuePair { value1 = SqlDbType.VarChar, value2 = doe.StaffCD } }, { "@OrderCD", new ValuePair { value1 = SqlDbType.VarChar, value2 = doe.OrderCD } }, { "@OrderNO", new ValuePair { value1 = SqlDbType.VarChar, value2 = doe.OrderNO } }, { "@ChkMisyonin", new ValuePair { value1 = SqlDbType.VarChar, value2 = doe.ChkMisyonin.ToString() } }, { "@Operator", new ValuePair { value1 = SqlDbType.VarChar, value2 = doe.Operator } }, { "@PC", new ValuePair { value1 = SqlDbType.VarChar, value2 = doe.PC } }, { "@SYSDATETIME", new ValuePair { value1 = SqlDbType.VarChar, value2 = dtNow.ToString("yyyy/MM/dd HH:mm:ss") } }, }; command = new SqlCommand("PRC_EDIOrder_Insert", GetConnection(), transaction); command.CommandType = CommandType.StoredProcedure; foreach (KeyValuePair <string, ValuePair> pair in dic) { ValuePair vp = pair.Value; AddParam(command, pair.Key, vp.value1, vp.value2); } command.ExecuteNonQuery(); //CSV出力 dic = new Dictionary <string, ValuePair> { { "@EDIOrderNO", new ValuePair { value1 = SqlDbType.VarChar, value2 = dee.EDIOrderNO } }, }; DataTable dt = new DataTable(); command = new SqlCommand("D_EDIOrder_SelectForCSV", GetConnection(), transaction); command.CommandType = CommandType.StoredProcedure; foreach (KeyValuePair <string, ValuePair> pair in dic) { ValuePair vp = pair.Value; AddParam(command, pair.Key, vp.value1, vp.value2); } adapter = new SqlDataAdapter(command); adapter.Fill(dt); ExportCsv(dt, dtNow); //CSV出力日時更新 if (dee.EDIOrderNO == null) { dic = new Dictionary <string, ValuePair> { { "@SYSDATETIME", new ValuePair { value1 = SqlDbType.VarChar, value2 = dtNow.ToString("yyyy/MM/dd HH:mm:ss") } }, }; command = new SqlCommand("D_EDIOrder_UpdateOutputDateTime", GetConnection(), transaction); command.CommandType = CommandType.StoredProcedure; foreach (KeyValuePair <string, ValuePair> pair in dic) { ValuePair vp = pair.Value; AddParam(command, pair.Key, vp.value1, vp.value2); } command.ExecuteNonQuery(); } CommitTransaction(); return(true); } catch (Exception e) { RollBackTransaction(); throw e; } finally { command.Connection.Close(); } }