public DataSet GetExpensesListByDate(int dateId)
{
DataSet dsExpenses = new DataSet();
DBAccess db = new FuelCenterDB.DBAccess();
SqlConnection sqlConnect = db.CreateFuelDBConnection();
string sql = "uspGetExpensesListByDateId";
SqlCommand sqlCommand = db.SetFuelDBCommandTypeStoredProcedure(sqlConnect, sql);
try
{
sqlConnect.Open();
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = sqlCommand;
da.SelectCommand.Parameters.AddWithValue("@DateID", (int)dateId);
da.Fill(dsExpenses);
dsExpenses.Tables[0].TableName = "ExpensesDataList";
}
catch (SqlException ex)
{
Message = ex.Message;
}
finally
{
db.CloseConnection(ref sqlConnect);
}
return(dsExpenses);
}
// Create overloaded methods to return expenses data by expenseid
// *** When entering an expenseId it will only return and populate one class
public ExpensesData(int expensesId)
{ //*********************UNFINISHED CLASS****************
// this will populate an Expenses Class
DBAccess db = new FuelCenterDB.DBAccess();
SqlConnection sqlConnect = db.CreateFuelDBConnection();
string sqlSelect = "";
SqlCommand sqlCommand = db.SetFuelDBCommandTypeStoredProcedure(sqlConnect, sqlSelect);
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = sqlCommand;
}
//GetUserData - Returns list of Users' Info
public DataSet GetExpensesListByDateId(int dateId)
{
DataSet dsExpenses = new DataSet();
dsExpenses.Tables[0].TableName = "ExpensesList";
DBAccess db = new FuelCenterDB.DBAccess();
SqlConnection sqlConnect = db.CreateFuelDBConnection();
SqlCommand sqlCommand = db.SetFuelDBCommandTypeStoredProcedure(sqlConnect, "uspGetExpensesListByDateID");
sqlCommand.Parameters.AddWithValue("@DateId", null).Value = dateId;
SqlDataAdapter sqlDA = new SqlDataAdapter();
sqlDA.SelectCommand = sqlCommand;
sqlDA.Fill(dsExpenses.Tables["ExpensesList"]);
return(dsExpenses);
}
public int UpdateExpenseDataGridList(DataTable expensesDataSet)
{
int rowsAffected = 0;
DBAccess db = new DBAccess();
SqlConnection sqlConnect = db.CreateFuelDBConnection();
SqlCommand sqlCommand = db.SetFuelDBCommandTypeStoredProcedure(sqlConnect, "");
try
{
SqlDataAdapter sqlDA = new SqlDataAdapter();
sqlCommand = db.SetFuelDBCommandTypeStoredProcedure(sqlConnect, "uspDeleteExpenseRecordsByExpenseID");
sqlCommand.Parameters.AddWithValue("@ExpensesId", SqlDbType.Int);
sqlCommand.Parameters["@ExpensesId"].SourceColumn = "ExpensesId";
sqlDA.DeleteCommand = sqlCommand;
sqlCommand = db.SetFuelDBCommandTypeStoredProcedure(sqlConnect, "uspUpdateExpensesList");
sqlCommand.Parameters.AddWithValue("@ExpensesId", SqlDbType.Int);
sqlCommand.Parameters["@ExpensesId"].SourceColumn = "ExpensesId";
sqlCommand.Parameters.AddWithValue("@VendorId", SqlDbType.Int);
sqlCommand.Parameters["@VendorId"].SourceColumn = "VendorId";
sqlCommand.Parameters.AddWithValue("@ExpenseTypeId", SqlDbType.Int);
sqlCommand.Parameters["@ExpenseTypeId"].SourceColumn = "ExpenseTypeId";
sqlCommand.Parameters.AddWithValue("@Description", SqlDbType.VarChar);
sqlCommand.Parameters["@Description"].SourceColumn = "Description";
sqlCommand.Parameters.AddWithValue("@Amount", SqlDbType.Decimal);
sqlCommand.Parameters["@Amount"].SourceColumn = "Amount";
sqlCommand.Parameters.AddWithValue("@UserId", SqlDbType.Int);
sqlCommand.Parameters["@UserId"].SourceColumn = "UserId";
sqlCommand.Parameters.AddWithValue("@Cleared", SqlDbType.Bit);
sqlCommand.Parameters["@Cleared"].SourceColumn = "Cleared";
sqlCommand.Parameters.AddWithValue("@CheckNo", SqlDbType.VarChar);
sqlCommand.Parameters["@CheckNo"].SourceColumn = "CheckNo";
sqlCommand.Parameters.AddWithValue("@CheckNotes", SqlDbType.VarChar);
sqlCommand.Parameters["@CheckNotes"].SourceColumn = "CheckNotes";
sqlCommand.Parameters.AddWithValue("@DateId", SqlDbType.Int);
sqlCommand.Parameters["@DateId"].SourceColumn = "DateId";
sqlDA.UpdateCommand = sqlCommand;
sqlCommand = db.SetFuelDBCommandTypeStoredProcedure(sqlConnect, "uspInsertExpensesData");
//sqlCommand.Parameters.AddWithValue("@ExpensesId", SqlDbType.Int);
//sqlCommand.Parameters["@ExpensesId"].SourceColumn = expensesDataSet.Tables["ExpensesDataList"].Columns["ExpensesId"].ColumnName;
sqlCommand.Parameters.AddWithValue("@VendorId", SqlDbType.Int);
sqlCommand.Parameters["@VendorId"].SourceColumn = expensesDataSet.Columns["VendorId"].ColumnName;
sqlCommand.Parameters.AddWithValue("@ExpenseTypeId", SqlDbType.Int);
sqlCommand.Parameters["@ExpenseTypeId"].SourceColumn = expensesDataSet.Columns["ExpenseTypeId"].ColumnName;
sqlCommand.Parameters.AddWithValue("@TenderTypeId", SqlDbType.VarChar);
sqlCommand.Parameters["@TenderTypeId"].SourceColumn = expensesDataSet.Columns["TenderTypeId"].ColumnName;
sqlCommand.Parameters.AddWithValue("@Description", SqlDbType.VarChar);
sqlCommand.Parameters["@Description"].SourceColumn = expensesDataSet.Columns["Description"].ColumnName;
sqlCommand.Parameters.AddWithValue("@Amount", SqlDbType.Decimal);
sqlCommand.Parameters["@Amount"].SourceColumn = expensesDataSet.Columns["Amount"].ColumnName;
sqlCommand.Parameters.AddWithValue("@UserId", SqlDbType.Int);
sqlCommand.Parameters["@UserId"].SourceColumn = expensesDataSet.Columns["UserId"].ColumnName;
sqlCommand.Parameters.AddWithValue("@Cleared", SqlDbType.Bit);
sqlCommand.Parameters["@Cleared"].SourceColumn = expensesDataSet.Columns["Cleared"].ColumnName;
sqlCommand.Parameters.AddWithValue("@CheckNo", SqlDbType.VarChar);
sqlCommand.Parameters["@CheckNo"].SourceColumn = expensesDataSet.Columns["CheckNo"].ColumnName;
sqlCommand.Parameters.AddWithValue("@CheckNotes", SqlDbType.VarChar);
sqlCommand.Parameters["@CheckNotes"].SourceColumn = expensesDataSet.Columns["CheckNotes"].ColumnName;
sqlCommand.Parameters.AddWithValue("@DateId", SqlDbType.Int);
sqlCommand.Parameters["@DateId"].SourceColumn = expensesDataSet.Columns["DateId"].ColumnName;
sqlDA.InsertCommand = sqlCommand;
if (expensesDataSet == null)
{
}
//MessageBox.Show("IT IS NULL");
else
{
sqlDA.Update(expensesDataSet);
}
}
catch (SqlException ex)
{
//MessageBox.Show(ex.Message);
// ErrorMessage.ErrorMessage = ex.ErrorMessage;
//ErrorMessage.ClassName = this.ToString();
}
catch (ArgumentNullException ex)
{
ErrorMessage.Message = ex.Message;
ErrorMessage.ClassName = this.ToString();
}
catch (Exception ex)
{
ErrorMessage.Message = ex.Message;
ErrorMessage.ClassName = this.ToString();
}
return(rowsAffected);
}