/// <summary>
/// Parameters를 설정하여 SQL 문을 실행합니다.
/// </summary>
/// <param name="strSql">SQL statement</param>
/// <param name="param">Parameter</param>
/// <returns></returns>
public SqlDataReader ExecuteReader(StringBuilder strSql, ParameterMember param)
{
SqlCommand sqlCmd = new SqlCommand(strSql.ToString(), gConn);
AttachParameters(sqlCmd, param);
return(sqlCmd.ExecuteReader());
}
private void AssignParameterValues(SqlParameter[] commandParameters, ParameterMember parameterValues)
{
if ((commandParameters == null) || (parameterValues == null))
{
return;
}
// we must have the same number of values as we pave parameters to put them in
if (commandParameters.Length != parameterValues.Count)
{
throw new ArgumentException("Parameter count does not match Parameter Value count.");
}
int i = 0;
foreach (SqlParameter p in commandParameters)
{
if (p.ParameterName.ToUpper() != parameterValues.GetName(i++).ToUpper())
{
throw new ArgumentException("ParameterName does not match ParamterMember Name");
}
}
//iterate through the SqlParameters, assigning the values from the corresponding position in the
//value array
i = 0;
for (int j = commandParameters.Length; i < j; i++)
{
commandParameters[i].Value = parameterValues.GetValue(i);
}
}
private string AttachParametersErr(string CommandText, ParameterMember param, ref string Query)
{
if (param == null)
{
return(CommandText);
}
CommandText = CommandText.Replace("\t", "");
CommandText = CommandText.Replace(",", " ,") + " ";
string ParmData = "";
ParmData = "================================================================================================\n";
ParmData += CommandText + "\n";
ParmData += "================================================================================================\n";
for (int i = 0; i < param.Count; i++)
{
ParmData += "\t" + param.GetName(i) + "\t='" + param.GetValue(i) + "' \n";
CommandText = CommandText.Replace(param.GetName(i) + " ", "'" + param.GetValue(i) + "'");
}
CommandText = CommandText.Replace(" ", "");
ParmData += "================================================================================================\n";
Query = CommandText;
CommandText = ParmData + CommandText;
return(CommandText);
//Console.WriteLine(command.CommandText.ToString());
}
public string ExecuteStoreProcs(SqlTransaction transaction, string spName, ParameterMember parameterValues)
{
SqlCommand command = new SqlCommand();
command.Connection = gConn;
command.CommandText = spName;
command.CommandType = CommandType.StoredProcedure;
if (transaction != null)
{
command.Transaction = transaction;
}
if ((parameterValues != null) && (parameterValues.Count > 0))
{
SqlParameter[] commandParameters = ParameterCache.GetSpParameterSet(gConn, spName);
AssignParameterValues(commandParameters, parameterValues);
foreach (SqlParameter p in commandParameters)
{
if ((p.Direction == ParameterDirection.InputOutput) && (p.Value == null))
{
p.Value = DBNull.Value;
}
command.Parameters.Add(p);
}
string retval = command.ExecuteNonQuery().ToString();
int i = 0;
foreach (SqlParameter p in commandParameters)
{
if (p.Direction == ParameterDirection.Output || p.Direction == ParameterDirection.InputOutput)
{
parameterValues.SetValue(i, p.Value);
}
else if (p.Direction == ParameterDirection.ReturnValue)
{
parameterValues.Add(p.SqlDbType, @"RC", p.Value);
}
i++;
}
//command.Parameters.Clear();
return(retval);
}
else
{
string retval = command.ExecuteNonQuery().ToString();
//command.Parameters.Clear();
return(retval);
}
}
/// <summary>
/// Query를 실행한다.
/// </summary>
/// <param name="transaction"></param>
/// <param name="strSql">Query문</param>
/// <param name="param">ParameterMember</param>
/// <returns>영향받은 레코드 수</returns>
public int ExecuteNonQuery(SqlTransaction transaction, StringBuilder strSql, ParameterMember param)
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, transaction, strSql, param);
//finally, execute the command.
int retval = cmd.ExecuteNonQuery();
// detach the SqlParameters from the command object, so they can be used again.
cmd.Parameters.Clear();
return(retval);
}
/// <summary>
/// Command객체에 Parameter를 추가한다.
/// </summary>
/// <param name="command"></param>
/// <param name="param"></param>
private void AttachParameters(SqlCommand command, ParameterMember param)
{
if (param == null)
{
return;
}
for (int i = 0; i < param.Count; i++)
{
SqlParameter cmdPara = new SqlParameter(param.GetName(i), param.GetType(i));
cmdPara.Value = param.GetValue(i);
command.Parameters.Add(cmdPara);
}
}
public bool ExecuteDataSet(SqlTransaction transaction, StringBuilder strSql, ParameterMember param, ref DataSet ds)
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, transaction, strSql, param);
SqlDataAdapter da = new SqlDataAdapter(cmd);
ds = new DataSet();
da.Fill(ds);
cmd.Parameters.Clear();
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// Command객체을 설정한다.
/// </summary>
/// <param name="command">SqlCommand</param>
/// <param name="transaction">SqlTransaction</param>
/// <param name="strSql">Query문</param>
/// <param name="commandParameters">ParameterMember</param>
private void PrepareCommand(SqlCommand command, SqlTransaction transaction, StringBuilder strSql,
ParameterMember commandParameters)
{
if (gConn.State != ConnectionState.Open)
{
gConn.Open();
}
command.Connection = gConn;
command.CommandText = strSql.ToString();
command.CommandType = CommandType.Text;
if (transaction != null)
{
command.Transaction = transaction;
}
if (commandParameters != null)
{
AttachParameters(command, commandParameters);
}
}
/// <summary>
/// Command객체에 Parameter를 추가한다.
/// </summary>
/// <param name="command"></param>
/// <param name="param"></param>
private void AttachParameters(OleDbCommand command, ParameterMember param)
{
if (param == null)
{
return;
}
for (int i = 0; i < param.Count; i++)
{
OleDbParameter cmdPara = new OleDbParameter(param.GetName(i), param.GetType(i));
cmdPara.Value = param.GetValue(i);
if (command.CommandType != CommandType.StoredProcedure)
{
command.CommandText = command.CommandText.Replace(param.GetName(i) + " ", "? ");
}
else
{
command.CommandText = command.CommandText.Replace(param.GetName(i), "");
}
command.Parameters.Add(cmdPara);
}
//Console.WriteLine(command.CommandText.ToString());
}
//========================================테스트============================================
#region [Method] ExecuteStoreProc
/// <summary>
/// Stored Procedure를 실행하고 결과를 ParameterMember에 담아 리턴한다.
/// Procedure자체의 리턴은 PrameterMember의 마지막에 'RC'라는 이름으로 추가되어 리턴된다.
/// thissor형태의 Output은 지원되지 않는다.
/// </summary>
/// <param name="transaction"></param>
/// <param name="spName"></param>
/// <param name="parameterValues"></param>
/// <returns></returns>
public string ExecuteStoreProcs(string spName, ParameterMember parameterValues)
{
return(ExecuteStoreProcs((SqlTransaction)null, spName, parameterValues));
}
/// <summary>
/// Query를 실행한다
/// </summary>
/// <param name="strSql">Query문</param>
/// <param name="param">ParameterMember</param>
/// <returns>영향받은 레코드 수</returns>
public int ExecuteNonQuery(StringBuilder strSql, ParameterMember param)
{
return(ExecuteNonQuery((SqlTransaction)null, strSql, param));
}
/// <summary>
/// Command객체을 설정한다.
/// </summary>
/// <param name="command">OleDbCommand</param>
/// <param name="transaction">OleDbTransaction</param>
/// <param name="strSql">Query문</param>
/// <param name="commandParameters">ParameterMember</param>
private void PrepareCommand(OleDbCommand command, OleDbTransaction transaction, StringBuilder strSql, ParameterMember commandParameters)
{
if (gConn.State != ConnectionState.Open)
{
gConn.Open();
}
string CommandTextdata = strSql.ToString().Replace(",", " ,") + " ";
CommandTextdata = CommandTextdata.Replace(")", " )");
command.Connection = gConn;
command.CommandText = CommandTextdata;
string CommandTypeGubu = command.CommandText.ToUpper().Substring(0, 8);
if (CommandTypeGubu.IndexOf("SELECT") == -1 & CommandTypeGubu.IndexOf("INSERT") == -1 & CommandTypeGubu.IndexOf("UPDATE") == -1 & CommandTypeGubu.IndexOf("DELETE") == -1)
{
command.CommandText = CommandTextdata.Replace(",", "");
command.CommandType = CommandType.StoredProcedure;
}
else
{
command.CommandType = CommandType.Text;
}
if (transaction != null)
{
command.Transaction = transaction;
}
if (commandParameters != null)
{
AttachParameters(command, commandParameters);
}
if (ivizConn.DB_LOG && CommandTextdata.IndexOf("COMM") == -1)
{
string GetQuery = "";
if (MessageBox.Show(AttachParametersErr(CommandTextdata, commandParameters, ref GetQuery), "Qeury Log", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
Clipboard.Clear();
Clipboard.SetText(GetQuery.ToString());
}
}
}
public string ExecuteScalar(StringBuilder strSql, ParameterMember param)
{
return(ExecuteScalar((SqlTransaction)null, strSql, param));
}
public bool ExecuteDataSet(OleDbTransaction transaction, StringBuilder strSql, ParameterMember param, ref DataSet ds)
{
try
{
OleDbCommand cmd = new OleDbCommand();
PrepareCommand(cmd, transaction, strSql, param);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
ds = new DataSet();
da.Fill(ds);
cmd.Parameters.Clear();
if (ds != null && ds.Tables[0].Rows.Count >= 0)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return(false);
}
}
/// <summary>
/// Query를 실행한다.
/// </summary>
/// <param name="transaction"></param>
/// <param name="strSql">Query문</param>
/// <param name="param">ParameterMember</param>
/// <returns>영향받은 레코드 수</returns>
public bool ExecuteNonQuery(OleDbTransaction transaction, StringBuilder strSql, ParameterMember param)
{
try
{
OleDbCommand cmd = new OleDbCommand();
PrepareCommand(cmd, transaction, strSql, param);
//finally, execute the command.
int retval = cmd.ExecuteNonQuery();
// detach the OleDbParameters from the command object, so they can be used again.
cmd.Parameters.Clear();
if (retval >= 1)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception)
{
//Console.WriteLine(ex.Message.ToString() + strSql.ToString () );
return(false);
}
}
public static bool Excute(StringBuilder strSql, ParameterMember param)
{
return(cDBOleDb.ExecuteNonQuery((OleDbTransaction)null, strSql, param));
}
public static bool ExcuteDS(StringBuilder strSql, ParameterMember param, ref DataSet ds)
{
return(cDBOleDb.ExecuteDataSet((OleDbTransaction)null, strSql, param, ref ds));
}
public string ExecuteScalar(SqlTransaction transaction, StringBuilder strSql, ParameterMember param)
{
SqlCommand sqlCmd = new SqlCommand(strSql.ToString(), gConn);
PrepareCommand(sqlCmd, transaction, strSql, param);
return(sqlCmd.ExecuteScalar().ToString());
}
