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); }
// Method will return bool based on whether there is already data in teh Sales Department table public bool DoesSalesDeptDataExist(int salesDate) { bool dataExists = false; DBAccess dbAccess = new DBAccess(); SqlConnection sqlConn = dbAccess.CreateFuelDBConnection(); string spName = "uspGetDepartmentSalesData"; SqlCommand sqlComm = dbAccess.SetFuelDBCommandTypeStoredProcedure(sqlConn, spName); try { sqlConn.Open(); sqlComm.Parameters.Add("@SalesDateID"); int sqlReader = sqlComm.ExecuteNonQuery(); // executenonquery returns an integer if (sqlReader > 0) { dataExists = true; } } catch (SqlException ex) { Message = ex.Message; } catch (Exception ex) { Message = ex.Message; } finally { dbAccess.CloseConnection(ref sqlConn); } return(dataExists); }
public bool UpdateGasSalesChanges(ArrayList arrFuelData, int salesDateId) { bool isSuccess = false; //string sql = ""; DBAccess fueldb = new DBAccess(); SqlConnection sqlConn = new SqlConnection(); sqlConn = fueldb.CreateFuelDBConnection(); SqlCommand sqlCommand = fueldb.SetFuelDBCommandTypeStoredProcedure(sqlConn, "uspInsertGasSalesData"); try { sqlConn.Open(); foreach (FuelData fd in arrFuelData) { sqlCommand.Parameters.AddWithValue("@GasTypeId", SqlDbType.Int); sqlCommand.Parameters["@GasTypeId"].Value = fd.FuelType; sqlCommand.Parameters.AddWithValue("@GallonsSold", SqlDbType.Decimal); sqlCommand.Parameters["@GallonsSold"].Value = fd.SoldAmount; sqlCommand.Parameters.AddWithValue("@Price", SqlDbType.Decimal); sqlCommand.Parameters["@Price"].Value = fd.Price; sqlCommand.Parameters.AddWithValue("TotalDollarAmount", SqlDbType.Decimal); sqlCommand.Parameters["@TotalDollarAmount"].Value = fd.SoldAmount; sqlCommand.Parameters.AddWithValue("@CostOfGas", SqlDbType.Decimal); sqlCommand.Parameters["@CostOfGas"].Value = fd.CostOfGas; sqlCommand.Parameters.AddWithValue("@MobilPrice", SqlDbType.Decimal); sqlCommand.Parameters["@MobilPrice"].Value = 0.00m; sqlCommand.Parameters.AddWithValue("@QTPrice", SqlDbType.Decimal); sqlCommand.Parameters["@QTPrice"].Value = 0.00m; sqlCommand.Parameters.AddWithValue("@ChevronPrice", SqlDbType.Decimal); sqlCommand.Parameters["@ChevronPrice"].Value = 0.00m; sqlCommand.ExecuteNonQuery(); } } catch (Exception ex) { ExceptionsMessages = new FuelCenterDB.Exceptions(ex); } finally { fueldb.CloseConnection(ref sqlConn); } return(isSuccess); }
public int GetSalesDateIDBySalesDate(DateTime businessdate) { // will retrieve sales date id from the given business date. // however, it will not be a method available to an instantiated class. // the stored procedure in this will create a new sales date and sales date id if one does not exist. int salesdateid = 0; string sql = "uspGetOrInsertSalesDateID"; DBAccess fuelDB = new DBAccess(); SqlConnection sqlConn = fuelDB.CreateFuelDBConnection(); SqlCommand sqlComm = fuelDB.SetFuelDBCommandTypeStoredProcedure(sqlConn, sql); sqlComm.Parameters.AddWithValue("@SalesDate", SqlDbType.DateTime); sqlComm.Parameters["@SalesDate"].Value = businessdate; // when having an OUTPUT in the stored procedure, you still need to specify the OUTPUT parameter // Since we are using executescalar because we are not returning a recordset, we need to specify the OUTPUT parameter // So we create an output parameter. This parameter can be accesssed after the executenonquery has occured. SqlParameter returnSalesDateId = new SqlParameter("@SalesDateId", SqlDbType.Int); returnSalesDateId.Direction = ParameterDirection.Output; sqlComm.Parameters.Add(returnSalesDateId); try { sqlConn.Open(); sqlComm.ExecuteScalar(); //int counter = 0; // the stored procedure should always return a sales date because it will either select or create a new salesdate record. salesdateid = (int)returnSalesDateId.Value; } catch (SqlException ex) { salesdateid = -1; // error exists MessageErrorDB = ex.Message; } catch (Exception ex) { salesdateid = -1; // error exists MessageError = ex.Message; } finally { fuelDB.CloseConnection(ref sqlConn); } return(salesdateid); }
// InsertsalesDataListDB: will either update modified data, or insert new data. // Requires a List<SalesDeptData> // returns total number of rows affected. public int InsertSalesDataListDB(List <SalesDepartmentData> listSalesData) { // this will update a list of SalesData objects from teh salesdata datagridview. Instead of using a data adapter, a list will be sent to this // method, and will iterate through the list and update each SalesData object seperately. int counter = 0; // counter- will count how many records were updated. DBAccess db = new DBAccess(); SqlConnection sqlConn = db.CreateFuelDBConnection(); //SqlCommand sqlComm = db.SetFuelDBCommandTypeStoredProcedure(sqlConn, "uspInsertDepartmentSales"); string spName = ""; try { sqlConn.Open(); foreach (SalesDepartmentData sales in listSalesData) { if (DoesSalesDeptDataExist(sales.SalesDateId)) {// if there are records in the db then just do an update of those recoreds spName = "uspUpdateDepartmentsales"; } else {// if there are no records then do an insert. spName = "uspInsertDepartmentSales"; } SqlCommand sqlComm = db.SetFuelDBCommandTypeStoredProcedure(sqlConn, spName); sqlComm.Parameters.Add(new SqlParameter("@DeptID", sales.DeptID)); sqlComm.Parameters.Add(new SqlParameter("@Quantity", sales.Quantity)); sqlComm.Parameters.Add(new SqlParameter("@Amount", sales.TotalDollars)); sqlComm.Parameters.Add(new SqlParameter("@SalesDateID", sales.SalesDateId)); counter++; } } catch (SqlException ex) { Message = ex.Message; } catch (Exception ex) { Message = ex.Message; } finally { db.CloseConnection(ref sqlConn); } return(counter); }
public DateTime?GetSalesDateByDateID(int salesdateid) { // will retrieve sales date from the given business date id. // however, it will not be a method available to an instantiated class. DateTime?salesdate = null; string sql = @"Select Top 1 SalesDate from [Bookkeeping].[SalesDate] where SalesDateID = " + salesdateid + ";"; DBAccess fuelDB = new DBAccess(); SqlConnection sqlConn = fuelDB.CreateFuelDBConnection(); SqlCommand sqlComm = fuelDB.SetFuelDBCommandTypeText(sqlConn, sql); try { sqlConn.Open(); SqlDataReader sqlReader = sqlComm.ExecuteReader(); //int counter = 0; if (sqlReader.HasRows == true) { while (sqlReader.Read()) { salesdate = (DateTime)sqlReader["SalesDate"]; } sqlReader.Close(); } else { return(salesdate); } } catch (SqlException ex) { //salesdateid = -1; // error exists MessageErrorDB = ex.Message; } catch (Exception ex) { // error exists MessageError = ex.Message; } finally { fuelDB.CloseConnection(ref sqlConn); } return(salesdate); }
public ArrayList GetGasSalesByDateId(int salesDateId) { ArrayList gasRecordsArray = new ArrayList(); string sqlText = @"Select GasTypeID, GallonsSold, Price, TotalDollarAmount, CostOfGas from Bookkeeping.GasSales where DateID = " + salesDateId + ";"; //ArrayList gasRecordsArray = new ArrayList(); DBAccess sqlGasSales = new DBAccess(); SqlConnection sqlConn = sqlGasSales.CreateFuelDBConnection(); SqlCommand sqlCommand = sqlGasSales.SetFuelDBCommandTypeText(sqlConn, sqlText); try { sqlConn.Open(); SqlDataReader sqlreader = sqlCommand.ExecuteReader(); FuelData gasSalesValues = new FuelData(); if (sqlreader.HasRows == true) { while (sqlreader.Read()) { //sqlvalues += sqlreader["GallonsSold"].ToString() + ", "; gasRecordsArray.Add(new FuelData() { FuelType = (int)sqlreader["GasTypeId"], SoldAmount = (decimal)sqlreader["GallonsSold"], Price = (decimal)sqlreader["Price"], DollarAmount = (decimal)sqlreader["TotalDollarAmount"], CostOfGas = (decimal)sqlreader["CostOfGas"] }); } } } catch (Exception ex) { ExceptionsMessages = new FuelCenterDB.Exceptions(ex); } finally { sqlGasSales.CloseConnection(ref sqlConn); } return(gasRecordsArray); }
//GetTenderType - Returns dict for how a customer paid public Dictionary <int, string> GetTenderType() { Dictionary <int, string> dictTenderType = new Dictionary <int, string>(); DBAccess db = new DBAccess(); SqlConnection sqlConnect = db.CreateFuelDBConnection(); SqlCommand sqlCommand = db.SetFuelDBCommandTypeStoredProcedure(sqlConnect, "uspGetTenderTypeList"); try { using (sqlConnect) { sqlConnect.Open(); SqlDataReader sqlReader = sqlCommand.ExecuteReader(); if (sqlReader.HasRows == true) { while (sqlReader.Read()) { dictTenderType.Add((int)sqlReader["TenderTypeId"], (string)sqlReader["TenderType"]); } } else { dictTenderType.Add(0, ""); } sqlReader.Close(); } } catch (SqlException ex) { Message = ex.Message; } catch (Exception ex) { Message = ex.Message; } finally { db.CloseConnection(ref sqlConnect); } return(dictTenderType); }