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