public object ExecuteScaler(string commandText, CommandType commandType, DalParameterList dalParameterList) { IsValidCommandText(commandText); DataSet TempDataSet = new DataSet(); SqlDataAdapter _sqlDataAdapter = null; int OutputParametersCount = 0; // count Output parameters sent to stored procedure try { // get new sql data adapter _sqlDataAdapter = GetSqlDataAdapter(commandText); using (_sqlDataAdapter) { // prepare procedure _sqlDataAdapter.SelectCommand.CommandType = commandType; _sqlDataAdapter.SelectCommand.CommandTimeout = CommandTimeout; // add sql parameters to procedure if (dalParameterList != null) { foreach (DalParameter DalParam in dalParameterList) { if (DalParam.ParameterDirection == ParameterDirection.Output) { OutputParametersCount++; } SqlParameter SqlParam = new SqlParameter() { ParameterName = DalParam.ParameterName, SqlDbType = DalParam.ParameterType, Size = DalParam.ParameterSize, Direction = DalParam.ParameterDirection, SqlValue = DalParam.ParameterValue }; _sqlDataAdapter.SelectCommand.Parameters.Add(SqlParam); } } // retrieve data into datasets from stored procedure _sqlDataAdapter.Fill(TempDataSet); // handle output parameters if (OutputParametersCount > 0) { foreach (SqlParameter SqlParam in _sqlDataAdapter.SelectCommand.Parameters) { if (SqlParam.Direction == ParameterDirection.Output) { dalParameterList.Find((x) => x.ParameterName == SqlParam.ParameterName && x.ParameterDirection == ParameterDirection.Output) .ParameterValue = SqlParam.Value; } } }//if(iOutputParametersCount > 0) } object ReturnValue = null; if (TempDataSet != null && TempDataSet.Tables.Count > 0 && TempDataSet.Tables[0].Rows.Count > 0) { ReturnValue = TempDataSet.Tables[0].Rows[0][0]; } return(ReturnValue); } catch (SqlException SqlEx) { throw SqlEx; } catch (Exception ex) { throw ex; } finally { if (_sqlDataAdapter != null) { _sqlDataAdapter.Dispose(); } if (_sqlConnection != null) { _sqlConnection.Dispose(); } } }
public DbDataReader ExecuteDataReader(string commandText, CommandType commandType, DalParameterList dalParameterList) { IsValidCommandText(commandText); DbDataReader dbDataReader = null; SqlCommand _sqlCommand = null; int OutputParametersCount = 0; // count Output parameters sent to stored procedure try { // get new sql command _sqlCommand = GetSqlCommand(commandText); using (_sqlCommand) { // prepare procedure _sqlCommand.CommandType = commandType; _sqlCommand.CommandTimeout = CommandTimeout; // add sql parameters to procedure if (dalParameterList != null) { foreach (DalParameter DalParam in dalParameterList) { if (DalParam.ParameterDirection == ParameterDirection.Output) { OutputParametersCount++; } SqlParameter SqlParam = new SqlParameter() { ParameterName = DalParam.ParameterName, SqlDbType = DalParam.ParameterType, Direction = DalParam.ParameterDirection, SqlValue = DalParam.ParameterValue }; _sqlCommand.Parameters.Add(SqlParam); } } // take care of transaction business _sqlCommand.Connection.Open(); // executes procedure to insert/update/delete data dbDataReader = _sqlCommand.ExecuteReader(CommandBehavior.CloseConnection); // handle output parameters if (OutputParametersCount > 0) { foreach (SqlParameter SqlParam in _sqlCommand.Parameters) { if (SqlParam.Direction == ParameterDirection.Output) { dalParameterList.Find((x) => x.ParameterName == SqlParam.ParameterName && x.ParameterDirection == ParameterDirection.Output) .ParameterValue = SqlParam.Value; } } } }//using SureScoreCommand } catch (SqlException SqlEx) { throw SqlEx; } catch (Exception ex) { throw ex; } finally { //if (_sqlCommand.Connection.State != ConnectionState.Closed) // _sqlCommand.Connection.Close(); //if (SureScoreCommand != null) // SureScoreCommand.Dispose(); //if (SureScoreConnection != null) // SureScoreConnection.Dispose(); } return(dbDataReader); }
public DataSet ExecuteDataSet(string commandText, CommandType commandType, DalParameterList dalParameterList, string[] TableNames = null) { IsValidCommandText(commandText); DataSet TempDataSet = new DataSet(); SqlDataAdapter _sqlDataAdapter = null; int OutputParametersCount = 0; // count Output parameters sent to stored procedure try { // get new sql data adapter _sqlDataAdapter = GetSqlDataAdapter(commandText); using (_sqlDataAdapter) { // prepare procedure _sqlDataAdapter.SelectCommand.CommandType = commandType; _sqlDataAdapter.SelectCommand.CommandTimeout = CommandTimeout; // add sql parameters to procedure if (dalParameterList != null) { foreach (DalParameter DalParam in dalParameterList) { if (DalParam.ParameterDirection == ParameterDirection.Output) { OutputParametersCount++; } SqlParameter SqlParam = new SqlParameter() { ParameterName = DalParam.ParameterName, SqlDbType = DalParam.ParameterType, Size = DalParam.ParameterSize, Direction = DalParam.ParameterDirection, SqlValue = DalParam.ParameterValue }; _sqlDataAdapter.SelectCommand.Parameters.Add(SqlParam); } } // retrieve data into datasets from stored procedure _sqlDataAdapter.Fill(TempDataSet); // handle output parameters if (OutputParametersCount > 0) { foreach (SqlParameter SqlParam in _sqlDataAdapter.SelectCommand.Parameters) { if (SqlParam.Direction == ParameterDirection.Output) { dalParameterList.Find((x) => x.ParameterName == SqlParam.ParameterName && x.ParameterDirection == ParameterDirection.Output) .ParameterValue = SqlParam.Value; } } }//if(iOutputParametersCount > 0) } if (TempDataSet != null && TempDataSet.Tables.Count > 0) { int counter = 0; int tableNamesCount = TableNames != null ? TableNames.Length : 0; foreach (DataTable table in TempDataSet.Tables) { if (counter < tableNamesCount) { table.TableName = TableNames[counter].Trim() == "" ? table.TableName : TableNames[counter]; counter++; } } } return(TempDataSet); } catch (SqlException SqlEx) { throw SqlEx; } catch (Exception ex) { throw ex; } finally { if (_sqlDataAdapter != null) { _sqlDataAdapter.Dispose(); } if (_sqlConnection != null) { _sqlConnection.Dispose(); } } }
public int ExecuteNonQuery(string commandText, CommandType commandType, DalParameterList dalParameterList) { IsValidCommandText(commandText); int RecordsAffected = -1; SqlCommand _sqlCommand = null; int OutputParametersCount = 0; // count Output parameters sent to stored procedure bool HasTransactionBegan = false; try { // get new sql command _sqlCommand = GetSqlCommand(commandText); using (_sqlCommand) { // prepare procedure _sqlCommand.CommandType = commandType; _sqlCommand.CommandTimeout = CommandTimeout; // add sql parameters to procedure if (dalParameterList != null) { foreach (DalParameter DalParam in dalParameterList) { if (DalParam.ParameterDirection == ParameterDirection.Output) { OutputParametersCount++; } SqlParameter SqlParam = new SqlParameter() { ParameterName = DalParam.ParameterName, SqlDbType = DalParam.ParameterType, Direction = DalParam.ParameterDirection, SqlValue = DalParam.ParameterValue }; _sqlCommand.Parameters.Add(SqlParam); } } // take care of transaction business _sqlCommand.Connection.Open(); _sqlCommand.Transaction = _sqlCommand.Connection.BeginTransaction(); HasTransactionBegan = true; // executes procedure to insert/update/delete data RecordsAffected = _sqlCommand.ExecuteNonQuery(); _sqlCommand.Transaction.Commit(); // handle output parameters if (OutputParametersCount > 0) { foreach (SqlParameter SqlParam in _sqlCommand.Parameters) { if (SqlParam.Direction == ParameterDirection.Output) { dalParameterList.Find((x) => x.ParameterName == SqlParam.ParameterName && x.ParameterDirection == ParameterDirection.Output) .ParameterValue = SqlParam.Value; } } } }//using SureScoreCommand } catch (SqlException SqlEx) { throw SqlEx; } catch (Exception ex) { throw ex; } finally { if (_sqlCommand != null && _sqlCommand.Transaction != null && HasTransactionBegan) { _sqlCommand.Transaction.Rollback(); } if (_sqlCommand.Connection.State != ConnectionState.Closed) { _sqlCommand.Connection.Close(); } //if (SureScoreCommand != null) // SureScoreCommand.Dispose(); //if (SureScoreConnection != null) // SureScoreConnection.Dispose(); } return(RecordsAffected); }
public object ExecuteScaler(string commandText, CommandType commandType, DalParameterList dalParameterList) { throw new NotImplementedException(); }
public int ExecuteNonQuery(string commandText, CommandType commandType, DalParameterList dalParameterList) { throw new NotImplementedException(); }
public DataTable ExecuteDataTable(string commandText, CommandType commandType, DalParameterList dalParameterList, string TableName = null) { throw new NotImplementedException(); }
public DbDataReader ExecuteDataReader(string commandText, CommandType commandType, DalParameterList dalParameterList) { throw new NotImplementedException(); }