public IDataReader ExecDataReader(string strSQL, CommandType cmdType, ParamCollection parameterArray) { SqlCommand cmd = new SqlCommand(); SqlConnection conn = new SqlConnection(); try { PrepareAll(ref cmd, ref conn, strSQL, cmdType, parameterArray); return(cmd.ExecuteReader()); } catch (Exception ex) { if ((!IsInTransaction()) && (conn != null)) { if (conn.State == ConnectionState.Open) { conn.Close(); } conn.Dispose(); } GenericExceptionHandler(ex, strSQL, parameterArray); } return(null); }
private void ODPExceptionHandler(SqlException ex, string SQLCommandText, ParamCollection Params) { //SqlInfoMessageEventArgs sqler = default(SqlInfoMessageEventArgs); StringBuilder sb = new StringBuilder(); throw new Exception(ex.ToString()); }
public DataAccess(string ConnectionStringItem) { _isolationLevel = IsolationLevel.ReadCommitted; _connString = GetConnectionString(ConnectionStringItem); _commandText = string.Empty; _commandType = CommandType.Text; _parameters = new ParamCollection(); }
public Int32 ExecNonQuery(string strSQL, CommandType cmdType, ParamCollection parameterArray) { SqlCommand cmd = new SqlCommand(); SqlConnection conn = new SqlConnection(); Int32 intRowAff = 0; Int32 intCounter; try { PrepareAll(ref cmd, ref conn, strSQL, cmdType, parameterArray); intRowAff = cmd.ExecuteNonQuery(); if (intRowAff == -1) { intRowAff = 1; } intCounter = -1; foreach (ParamStruct objParam in parameterArray) { intCounter += 1; if (objParam.Direction == ParameterDirection.InputOutput || objParam.Direction == ParameterDirection.Output || objParam.Direction == ParameterDirection.ReturnValue) { parameterArray[intCounter].Value = ((IDataParameter)cmd.Parameters[objParam.ParamName]).Value; } } return(intRowAff); } catch (Exception ex) { GenericExceptionHandler(ex, strSQL, parameterArray); } finally { if ((!IsInTransaction()) && (conn != null)) { if (conn.State == ConnectionState.Open) { conn.Close(); } conn.Dispose(); } if (cmd != null) { cmd.Dispose(); } } return(-1); }
public DataTable ExecDataTable(string strSQL, CommandType cmdType, ParamCollection parameterArray) { DataTable tb = new DataTable(); DataSet ds = new DataSet(); SqlDataAdapter da = new SqlDataAdapter(); SqlConnection conn = new SqlConnection(); SqlCommand cmd = new SqlCommand(); try { PrepareAll(ref cmd, ref conn, strSQL, cmdType, parameterArray); //Fill_DataSet(ref cmd, ref ds); da.SelectCommand = cmd; da.Fill(ds); if (ds != null && ds.Tables.Count > 0) { return(ds.Tables[0]); } else { ds = null; } } catch (Exception ex) { GenericExceptionHandler(ex, strSQL, parameterArray); } finally { if ((!IsInTransaction()) && (conn != null)) { if (conn.State == ConnectionState.Open) { conn.Close(); } conn.Dispose(); } if (cmd != null) { cmd.Dispose(); } da.Dispose(); if (ds != null) { ds.Dispose(); } } return(null); }
private void GenericExceptionHandler(Exception ex, string SQLCommandText, ParamCollection Params) { ODPExceptionHandler((SqlException)ex, SQLCommandText, Params); }
protected SqlCommand GetCommand(string strSQL, CommandType cmdType, ParamCollection ParameterArray) { SqlCommand cmd = new SqlCommand(); Int32 i = default(Int32); Int32 j = default(Int32); SqlParameter[] para_SP = null; if (cmdType == CommandType.StoredProcedure) { para_SP = Get_SPParameterSet(strSQL); } if (para_SP != null) { //Duyệt từng tham số từ store procedure //Duyệt tham số từ code //Kiểm tra tham số giữa store và code có trùng tên không //Nếu có gán dữ liệu cho tham số store là dữ liệu tham số code //Ngược lại default nothing for (i = 0; i <= para_SP.Length - 2; i++) { SqlParameter ps_SP = para_SP[i]; for (j = 0; j <= ParameterArray.Count - 1; j++) { ParamStruct ps_Code = ParameterArray[j]; if (ps_SP.ParameterName.ToUpper() == ps_Code.ParamName.ToUpper()) { if (System.DBNull.Value == ps_Code.Value) { ps_SP.Value = null; } else { ps_SP.Value = ps_Code.Value; } break; } else { ps_SP.Value = null; } } IDbDataParameter pm = GetParameter(ps_SP.ParameterName, ps_SP.Direction, ps_SP.Value, ps_SP.DbType, ps_SP.Size); cmd.Parameters.Add(pm); } } else { if (((ParameterArray != null)) && ParameterArray.Count > 0) { for (i = 0; i <= ParameterArray.Count - 1; i++) { ParamStruct ps = ParameterArray[i]; IDbDataParameter pm = GetParameter(ps.ParamName, ps.Direction, ps.Value, ps.DataType, ps.Size); cmd.Parameters.Add(pm); } } } cmd.CommandType = cmdType; cmd.CommandText = strSQL; return(cmd); }
public void PrepareAll(ref SqlCommand cmd, ref SqlConnection conn, string strSQL, CommandType cmdType, ParamCollection parameterArray) { if (!IsInTransaction()) { conn = GetConnection(ConnectionString); cmd = GetCommand(strSQL, cmdType, parameterArray); cmd.Connection = conn; conn.Open(); } else { cmd = GetCommand(strSQL, cmdType, parameterArray); cmd.Connection = _conn; cmd.Transaction = _trans; } }