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();
}
}