/// <summary> /// Provides the information for the InventoryWindow from the database /// </summary> /// <returns></returns> public ObservableCollection <clsItemsLogic> theInventory() { ObservableCollection <clsItemsLogic> inv = new ObservableCollection <clsItemsLogic>(); try { int num = 0; ds = db.ExecuteSQLStatement(SQLQueries.SelectAllInventory(), ref num); for (int i = 0; i < num; i++) { clsItemsLogic inventory = new clsItemsLogic(); inventory.InventoryLetter = ds.Tables[0].Rows[i][0].ToString(); inventory.ItemDesc = ds.Tables[0].Rows[i][1].ToString(); inventory.ItemCost = Convert.ToDecimal(ds.Tables[0].Rows[i][2].ToString()); inv.Add(inventory); } } catch (Exception ex) { //Just throw the exception throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message); } return(inv); }
/// <summary> /// Gets all items. /// </summary> /// <returns>A list of item objects.</returns> public ObservableCollection <Items.clsItem> GetItems() { try { int retVal = 0; string itemsSQL = sql.SelectItems(); ds = data.ExecuteSQLStatement(itemsSQL, ref retVal); itemsList = new ObservableCollection <Items.clsItem>(); Items.clsItem item; // fills itemsList with queried items for (int i = 0; i < retVal; i++) { item = new Items.clsItem(ds.Tables[0].Rows[i][0].ToString(), ds.Tables[0].Rows[i][2].ToString(), ds.Tables[0].Rows[i][1].ToString()); itemsList.Add(item); } return(itemsList); } catch (Exception ex) { //Just throw the exception throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message); } }
}//constructor public ObservableCollection <Invoice> getInvoices() { ObservableCollection <Invoice> invoices = new ObservableCollection <Invoice>(); try { //set up for query DataSet ds; int iRef = 0; string query = "SELECT InvoiceNum, InvoiceDate, TotalCost FROM Invoices"; //execute query ds = db.ExecuteSQLStatement(query, ref iRef); //create and store objects for (int i = 0; i < iRef; i++) { string invoiceNum = ds.Tables[0].Rows[i]["InvoiceNum"].ToString(); string invoiceDate = ds.Tables[0].Rows[i]["InvoiceDate"].ToString(); string totalCost = ds.Tables[0].Rows[i]["TotalCost"].ToString(); Invoice invoice = new Invoice(Int32.Parse(invoiceNum), invoiceDate, Convert.ToDouble(totalCost)); invoices.Add(invoice); } } catch (Exception ex) { System.IO.File.AppendAllText("C:\\Error.txt", Environment.NewLine + "HandleError Exception: " + ex.Message); } return(invoices); }
//will have a method that queries the DB for the flights, loops through them, and for each flight creates an object of clsFlight and adds it to a generic list of clsFlight objects. This list will then be returned by this method. /// <summary> /// Method for generating the flights class and storing them in their own flightdetails class /// </summary> /// <returns></returns> public List <clsFlight> getFlights() { List <clsFlight> flights = new List <clsFlight>(); try { //Create a DataSet to hold the data DataSet ds = new DataSet(); //Number of return values int iRet = 0; //Get all the values from the Authors table ds = db.ExecuteSQLStatement("SELECT * FROM flight", ref iRet); for (int i = 0; i < iRet; i++) { FlightDetails = new clsFlight(); FlightDetails.getFlightNumber = ds.Tables[0].Rows[i][0].ToString(); FlightDetails.getFlightNumber = ds.Tables[0].Rows[i][1].ToString(); FlightDetails.getAircraftType = ds.Tables[0].Rows[i]["Aircraft_Type"].ToString(); flights.Add(FlightDetails); } } catch (Exception ex) { MessageBox.Show(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message); //return false; } return(flights.Cast <clsFlight>().ToList()); }
/// <summary> /// Grab all rows from items desc database table. /// </summary> /// <returns></returns> public List <clsItemDescObj> SelectItemDescData() { try { int rowsReturned = 0; string sSql = "SELECT * FROM ItemDesc ORDER BY ItemCode"; DataSet ds = da.ExecuteSQLStatement(sSql, ref rowsReturned); List <clsItemDescObj> itemList = new List <clsItemDescObj> { }; if (rowsReturned == 0) { throw new Exception("No itmes were found."); } int row = 0; while (row < rowsReturned) { clsItemDescObj item = new clsItemDescObj(); item.sItemCode = ds.Tables[0].Rows[row]["ItemCode"].ToString(); item.sItemDesc = ds.Tables[0].Rows[row]["ItemDesc"].ToString(); item.sCost = ds.Tables[0].Rows[row]["Cost"].ToString(); itemList.Add(item); row++; } return(itemList); } catch (Exception ex) { System.IO.File.AppendAllText("C:\\Error.txt", Environment.NewLine + "HandleError Exception: " + ex.Message); // return empty string because something is wrong return(null); } }
/// <summary> /// returns dataset of the invoice number searched for /// </summary> /// <param name="InvoiceNumber"></param> /// <returns></returns> public DataSet SelectInvoice(int InvoiceNumber) { int iRet = 0; ds = db.ExecuteSQLStatement($"SELECT InvoiceNum, InvoiceDate, TotalCost FROM Invoices WHERE InvoiceNum = {InvoiceNumber}", ref iRet); return(ds); }
/// <summary> /// Runs the provided SQL string and fills the invoice variable with the results. /// </summary> /// <param name="sSQL"></param> /// <returns></returns> public List <clsItems> getItems(string sSQL) { try { DataSet ds = new DataSet(); items = new List <clsItems>(); clsItems item; int numRows = 0; ds = da.ExecuteSQLStatement(sSQL, ref numRows); for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { item = new clsItems(); item.ItemCode = ds.Tables[0].Rows[i][0].ToString(); item.ItemDesc = ds.Tables[0].Rows[i][1].ToString(); item.ItemCost = Convert.ToDouble(ds.Tables[0].Rows[i][2]); items.Add(item); } return(items); } catch (Exception ex) { throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message); } }
/// <summary> /// Method for generating the drop down list of equipment /// </summary> /// <returns></returns> public List <clsMusicEquipment> getItems() { List <clsMusicEquipment> musicEquipment = new List <clsMusicEquipment>(); try { DataSet ds = new DataSet(); int iRet = 0; ds = db.ExecuteSQLStatement(mainSQL.SelectItemsToBeAddedToInvoice(), ref iRet); for (int i = 0; i < iRet; i++) { Equipment = new Main.clsMusicEquipment(); Equipment.getEqipmentID = ds.Tables[0].Rows[i][0].ToString(); Equipment.getEquipmentName = ds.Tables[0].Rows[i][1].ToString(); Equipment.getEquipmentDescription = ds.Tables[0].Rows[i][2].ToString(); Equipment.getEquipmentCost = ds.Tables[0].Rows[i][3].ToString(); musicEquipment.Add(Equipment); } } catch (Exception ex) { MessageBox.Show(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message); } return(musicEquipment.Cast <clsMusicEquipment>().ToList()); }
/// <summary> /// This function returns a list of clsItem object /// </summary> /// <returns></returns> public BindingList <clsItem> populateChooseItem() { try { DataSet ds = new DataSet(); int iRet = 0; ds = db.ExecuteSQLStatement(SQLQueries.SelectAllItems(), ref iRet); BindingList <clsItem> ItemList = new BindingList <clsItem>(); for (int i = 0; i < iRet; i++) { clsItem Item = new clsItem(); Item.ItemCode = ds.Tables[0].Rows[i][0].ToString(); Item.ItemDesc = ds.Tables[0].Rows[i][1].ToString(); Item.Cost = Convert.ToDecimal(ds.Tables[0].Rows[i][2].ToString()); ItemList.Add(Item); } return(ItemList); } catch (Exception ex) { //Just throw the exception throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message); } }
/// <summary> /// runs the method to get the invoice numbers from the database /// </summary> /// <returns></returns> public void getInvoiceNums() { try { invoiceNums = new ObservableCollection <InvoiceInfo>(); DataSet ds; int res = 0; ds = db.ExecuteSQLStatement(searchSQL.getInvoiceNums(), ref res); for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { invoiceNums.Add(new InvoiceInfo { InvoiceNumber = ds.Tables[0].Rows[i]["InvoiceNum"].ToString() }); } } catch (Exception ex) { //Just throw the exception throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message); } }
/// <summary> /// Returns a list of all of the invoices in the database /// </summary> /// <returns></returns> public ObservableCollection <clsMainLogic> GetInvoices() { ObservableCollection <clsMainLogic> result = new ObservableCollection <clsMainLogic>(); try { int iRet = 0; DataSet ds = dataAccess.ExecuteSQLStatement(sql.SelectAllInvoices(), ref iRet); for (int i = 0; i < iRet; ++i) { clsMainLogic invoice = new clsMainLogic(); invoice.InvoiceNum = Convert.ToInt32(ds.Tables[0].Rows[i][0].ToString()); invoice.InvoiceDate = Convert.ToDateTime(ds.Tables[0].Rows[i][1].ToString()); invoice.TotalCharge = Convert.ToDecimal(ds.Tables[0].Rows[i][2].ToString()); result.Add(invoice); } } catch (Exception ex) { //Just throw the exception throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message); } return(result); }
/// <summary> /// gets invoices /// </summary> /// <returns></returns> public List <clsLineItems> ListItems() { try { List <clsLineItems> listItems = new List <clsLineItems>(); int rows = 0; DataSet rawdata = db.ExecuteSQLStatement(Query.GetItems(), ref rows); for (int x = 0; x < rows; x++) { Double.TryParse(rawdata.Tables[0].Rows[x][2].ToString(), out double total); listItems.Add(new clsLineItems { ItemCode = rawdata.Tables[0].Rows[x][0].ToString(), ItemDesc = rawdata.Tables[0].Rows[x][1].ToString(), Cost = total }); } return(listItems); } catch (Exception e) { throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + e.Message); } }
/// <summary> /// Gets the invoice based on an invoice ID /// </summary> /// <param name="invoiceID">Invoice ID to load</param> /// <returns>The invoice as a clsInvoiceObj</returns> public clsInvoiceObj GetInvoice(string invoiceID) { try { int rowsReturned = 0; string sql = sqlProvider.GetInvoiceQuery(invoiceID); clsInvoiceObj invoice; DataSet ds = da.ExecuteSQLStatement(sql, ref rowsReturned); if (rowsReturned > 1) { throw new Exception("More than one invoice was found for the invoice ID, expected one."); } else if (rowsReturned == 0) { throw new Exception("No invoice was found for the ID, expected one."); } else { invoice = new clsInvoiceObj(); invoice.sInvoiceNum = ds.Tables[0].Rows[0]["InvoiceNum"].ToString(); invoice.sInvoiceDate = ds.Tables[0].Rows[0]["InvoiceDate"].ToString(); invoice.sTotalCost = ds.Tables[0].Rows[0]["TotalCost"].ToString(); } return(invoice); } catch (Exception ex) { System.IO.File.AppendAllText("C:\\Error.txt", Environment.NewLine + "HandleError Exception: " + ex.Message); return(null); } }
/// <summary> /// This is the SQL statement to get all invoices. /// </summary> /// <returns>List of invoices</returns> public List <Invoice> GetAllInvoices() { try { string sSQL = "SELECT * FROM Items"; int iRet = 0; DataSet ds = new DataSet(); List <Invoice> invoices = new List <Invoice>(); ds = db.ExecuteSQLStatement(sSQL, ref iRet); for (int i = 0; i < iRet; i++) { Invoice invoiceItem = new Invoice { invoiceId = ds.Tables[0].Rows[i][0].ToString(), invoiceDate = ds.Tables[0].Rows[i][1].ToString(), invoiceTotal = ds.Tables[0].Rows[i][2].ToString() }; invoices.Add(invoiceItem); } return(invoices); } catch (Exception ex) { throw new Exception("Unable to get all items." + ex.ToString()); } }
/// <summary> /// Query to retrieve all the Items from Database /// </summary> /// <returns>A list of all items</returns> public List <Item> GetAllItems() { try { List <Item> items = new List <Item>(); //Sql Statement string Sql = "SELECT * FROM ItemDesc ORDER BY ItemCode"; int retVal = 0; DS = DataAccess.ExecuteSQLStatement(Sql, ref retVal); //Loop through DS and add Items foreach (DataRow dr in DS.Tables[0].Rows) { items.Add(new Item(Convert.ToChar(dr[0]), dr[1].ToString(), (float)Convert.ToDouble(dr[2]))); } return(items); } catch (Exception ex) { throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " --> " + ex.Message); } }
/// <summary> /// Method for getting all Items in ItemsDesc table /// </summary> /// <returns>Observable Collection of Item objects</returns> public ObservableCollection <clsItem> getItems() { try { string sSQL = clsSQL.GetAllItems(); int iRet = 0; ds = clsDA.ExecuteSQLStatement(sSQL, ref iRet); ObservableCollection <clsItem> ItemList = new ObservableCollection <clsItem>(); foreach (DataRow dr in ds.Tables[0].Rows) { ItemList.Add(new clsItem { sItemCode = dr[0].ToString(), sItemDesc = dr[1].ToString(), sCost = dr[2].ToString() }); } return(ItemList); } catch (Exception ex) { throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " ->" + ex.Message); } }
/// <summary> /// Gets sorted dataset of invoice costs /// </summary> /// <returns></returns> public DataSet GetSortedCosts() { try { int numInvoices = 0; // get ordered costs dataSet = dataAccess.ExecuteSQLStatement("SELECT TotalCost FROM Invoices ORDER BY TotalCost", ref numInvoices); return(dataSet); } catch (Exception ex) { throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + " " + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message); } }
/// <summary> /// Method to get all items. /// </summary> public DataSet getAllItems() { try { ds = db.ExecuteSQLStatement(clsItemsSQL.GetAllItems(), ref iRet); } catch (Exception ex) { throw new Exception("Unable to get all items. " + ex.ToString()); } return(ds); }
/// <summary> /// deletes an item in the database if that item is not in an invoice /// </summary> /// <param name="code">the items code</param> /// <returns>if something was deleted</returns> public bool DeleteItem(string code) { try { int iRet = 0; DataSet ds = new DataSet(); try { ds = data.ExecuteSQLStatement(SQL.InAInvoice(code), ref iRet); } catch (Exception ex) { throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message); } if (iRet == 0) { try { int del = data.ExecuteNonQuery(SQL.DeleteItem(code)); } catch (Exception ex) { throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message); } wnd.ShowError("Deleted"); return(true); } else { string sError = "This Item is in Invoice "; bool bFirst = true; for (int i = 0; i < iRet; i++) { if (!bFirst) { sError += ", "; } sError += ds.Tables[0].Rows[i][0].ToString(); } wnd.ShowError(sError); return(false); } } catch (Exception ex) { throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message); } }
/// <summary> /// Gets all the invoices in the database /// Creates an Invoice class object and adds to a list /// </summary> /// <returns>List of all the invoices objects in the database</returns> public static List <Invoice> GetAllInvoices() { try { int i = 0; // Temp ref integer, unused but required DataSet ds = _data.ExecuteSQLStatement(clsMainSQL.GetAllInvoices, ref i); // The DataSet of all the invoices List <Invoice> invoices = new List <Invoice>(); // The list of invoices to be returned foreach (DataRow row in ds.Tables[0].Rows) // Each row (each individual invoice) in the Invoices table { var invoice = new Invoice() // Temp invoice object to be added to the list { InvoiceNum = row[0].ToString(), InvoiceDate = row[1].ToString().Substring(0, row[1].ToString().IndexOf(' ')), TotalCost = Convert.ToDecimal(row[2]).ToString("c"), Items = "" }; DataSet ds1 = _data.ExecuteSQLStatement(clsMainSQL.GetItemsByInvoiceNum(row[0].ToString()), ref i); // The DataSet of all the items associated with an invoice foreach (DataRow row1 in ds1.Tables[0].Rows) // Each row (each individual item) in the LineItems table { invoice.Items += row1[0].ToString() + ", "; } invoice.Items = invoice.Items.TrimEnd(' ', ','); invoices.Add(invoice); } return(invoices); } catch { throw new Exception("Could not get all invoices"); } }
/// <summary> /// Runs the provided SQL string and fills the items variable with the results; /// </summary> /// <param name="sSQL"></param> /// <returns></returns> public List <clsItems> getItems() { try { DataSet ds = new DataSet(); int iRef = 0; itemsResult = new List <clsItems>(); var query = sql.SelectItems(); clsItems items; ds = db.ExecuteSQLStatement(query, ref iRef); for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { items = new clsItems(); items.ItemCode = ds.Tables[0].Rows[i][0].ToString(); items.ItemDesc = ds.Tables[0].Rows[i][1].ToString(); items.ItemCost = Convert.ToDouble(ds.Tables[0].Rows[i][2].ToString()); itemsResult.Add(items); } } catch (Exception ex) { throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message); } return(itemsResult); }
/// <summary> /// Loads the flight information from the database and stores it in the flight dataset /// </summary> private void LoadFlights() { // SQL Query Statement string sSQL = "SELECT Flight_ID, Flight_Number + ' - ' + Aircraft_Type AS Flight FROM FLIGHT"; // Execute SQL Query Flights = db.ExecuteSQLStatement(sSQL, ref iRet); // Update Flight selection combobox cbFlight.DisplayMember = "Flight"; // Sets combobox datasource cbFlight.DataSource = Flights.Tables[0]; // Sets selected index to initial position cbFlight.SelectedIndex = -1; }
/// <summary> /// Select all items from database /// </summary> /// <returns>all items</returns> public List <clsItem> Items() { try { // Initialize variablse int iRetVal = 0; List <clsItem> lstItems = new List <clsItem>(); // SQL query string string sSQLQuery = clsItemsSQL.SelectAllItems(); // query the database dsItems = db.ExecuteSQLStatement(sSQLQuery, ref iRetVal); // temp variables string sName; string sDescription; int iCost; bool bNumValid; foreach (DataRow drRow in dsItems.Tables[0].Rows) { sName = drRow.ItemArray[0].ToString(); sDescription = drRow.ItemArray[1].ToString(); iCost = 0; bNumValid = Int32.TryParse(drRow.ItemArray[2].ToString(), out iCost); // if parsing of string to int valid if (bNumValid) { lstItems.Add(new clsItem { sItemName = sName, sItemDesc = sDescription, iItemCost = iCost }); } } // comparer object for sorting the item code clsItemComparer itemComparer = new clsItemComparer(); lstItems.Sort(itemComparer); // return the items list return(lstItems); } catch (Exception ex) { throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message); } }
/// <summary> /// get our invoice IDS for our Search Window combo-box /// </summary> /// <returns>items</returns> public List <Invoice> getInvoices() { try { DataSet ds; int iRef = 0; //SQL Query to extract the Invoice ID from the INvoices table string query = "SELECT InvoiceNum, InvoiceDate, TotalCost FROM Invoices"; ds = db.ExecuteSQLStatement(query, ref iRef); //iterate through all the rows for (int i = 0; i < iRef; i++) { //get the invoice ID as a string string sinvoiceID = ds.Tables[0].Rows[i]["InvoiceNum"].ToString(); //get the invoice date as a string string sinvoiceDate = ds.Tables[0].Rows[i]["InvoiceDate"].ToString(); //get the invoice cost as a string string sinvoiceCost = (ds.Tables[0].Rows[i]["TotalCost"]).ToString(); //conver the invoice ID to an int int invoiceID = Convert.ToInt32(sinvoiceID); //convert the total cost to a double double totalInvoiceCost = Convert.ToDouble(sinvoiceCost); //create an Invoice object //Invoice inv = new Invoice(invoiceID, invoiceDate, totalCost); //add to our invoiceID list InvoiceList.Add(new Invoice { InvoiceNumber = invoiceID, InvoiceDate = sinvoiceDate, TotalCost = totalInvoiceCost }); } //return our list return(InvoiceList); } catch (Exception ex) { throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message); } }
/// <summary> /// Returns the full list of invoices from the database /// </summary> /// <returns></returns> public ObservableCollection<clsInvoice> invoicesCollection() { try { string sSQL; //Holds an SQL statement int iRet = 0; //Number of return values ds = new DataSet(); db = new clsDataAccess(); ObservableCollection<clsInvoice> col_Invoices = new ObservableCollection<clsInvoice>(); clsInvoice invoice; sSQL = "SELECT InvoiceNum, InvoiceDate, TotalCharge " + "FROM Invoices"; ds = db.ExecuteSQLStatement(sSQL, ref iRet); for (int i = 0; i < iRet; i++) { invoice = new clsInvoice(); invoice.InvoiceNum = ds.Tables[0].Rows[i][0].ToString(); invoice.InvoiceDate = ds.Tables[0].Rows[i]["InvoiceDate"].ToString(); invoice.TotalCharge = ds.Tables[0].Rows[i]["TotalCharge"].ToString(); col_Invoices.Add(invoice); } return col_Invoices; } catch (Exception ex) { throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message); } }
DataSet ds; // Dataset to hold the returned data from queries /// <summary> /// Returns the full list of jewelry items from the database /// </summary> /// <returns></returns> public ObservableCollection<clsItem> itemsCollection() { try { db = new clsDataAccess(); ObservableCollection<clsItem> col_Items = new ObservableCollection<clsItem>(); string sSQL; //Holds an SQL statement int iRet = 0; //Number of return values ds = new DataSet(); //Holds the return values clsItem items; //Used to load the return values into the combo box sSQL = "SELECT ItemCode, ItemDesc, Cost " + "FROM ItemDesc"; ds = db.ExecuteSQLStatement(sSQL, ref iRet); //Creates item objects based on the data pulled from the query than adds the object to a list for (int i = 0; i < iRet; i++) { items = new clsItem(); items.ItemCode = ds.Tables[0].Rows[i][0].ToString(); items.ItemDesc = ds.Tables[0].Rows[i]["ItemDesc"].ToString(); items.Cost = ds.Tables[0].Rows[i]["Cost"].ToString(); col_Items.Add(items); } return col_Items; } catch (Exception ex) { throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message); } }
public ObservableCollection<clsItem> itemsCollection() { clsDataAccess db; ObservableCollection<clsItem> col_Items; db = new clsDataAccess(); string sSQL; //Holds an SQL statement int iRet = 0; //Number of return values DataSet ds = new DataSet(); //Holds the return values clsItem items; //Used to load the return values into the combo box sSQL = "SELECT ItemCode, ItemDesc, Cost " + "FROM ItemDesc"; col_Items = new ObservableCollection<clsItem>(); ds = db.ExecuteSQLStatement(sSQL, ref iRet); //Creates Flight objects based on the data pulled from the query than adds the object to a list for (int i = 0; i < iRet; i++) { items = new clsItem(); items.ItemCode = ds.Tables[0].Rows[i][0].ToString(); items.ItemDesc = ds.Tables[0].Rows[i]["ItemDesc"].ToString(); items.Cost = ds.Tables[0].Rows[i]["Cost"].ToString(); col_Items.Add(items); } return col_Items; }
/// <summary> /// List to get GetLineItems() data /// </summary> /// <returns></returns> public List <clsLineItems> GetLineItems(int n) { List <clsLineItems> lstLineItems = new List <clsLineItems>(); db = new clsDataAccess(); ds = db.ExecuteSQLStatement(MainSQL.LoadLineItems(n), ref iRet); for (int i = 0; i < iRet; i++) { lstLineItems.Add(new clsLineItems { sInvoiceNum = ds.Tables[0].Rows[i]["InvoiceNum"].ToString(), sLineItemNum = ds.Tables[0].Rows[i]["LineItemNum"].ToString(), sItemCode = ds.Tables[0].Rows[i]["ItemCode"].ToString() }); /* * foreach (DataRow dr in ds.Tables[0].Rows) * { * another way to loop * } */ } return(lstLineItems); }
/// <summary> /// Get invoices function builds a query string and executes it and returns a list of invoices. /// </summary> /// <param name="dateTime"></param> /// <param name="totalCharges"></param> /// <param name="invoiceId"></param> /// <returns></returns> public List <clsInvoice> GetInvoices(DateTime?dateTime, double?totalCharges, int?invoiceId) { try { string queryString = clsSearchSQL.GetInvoices(dateTime, totalCharges, invoiceId); int numReturned = 0; DataSet invoiceDataSet = clsDataAccess.ExecuteSQLStatement(queryString, ref numReturned); List <clsInvoice> clsInvoicesList = new List <clsInvoice>(); for (int i = 0; i < numReturned; i++) { var invoice = new clsInvoice(); invoice.Id = int.Parse(invoiceDataSet.Tables[0].Rows[i][0].ToString()); invoice.InvoiceDate = (DateTime)invoiceDataSet.Tables[0].Rows[i][1]; invoice.TotalCost = double.Parse(invoiceDataSet.Tables[0].Rows[i][2].ToString()); clsInvoicesList.Add(invoice); } return(clsInvoicesList); } catch (Exception ex) { throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message); } }
/// <summary> /// method to get a List of Items from the database /// </summary> /// <param name="db"></param> /// <returns></returns> public List <clsItem> GetItems(clsDataAccess db) { try { ItemsList = new List <clsItem>(); sSQL = "select ItemCode, ItemDesc, Cost from ItemDesc"; ds = db.ExecuteSQLStatement(sSQL, ref iRetVal); for (int i = 0; i < iRetVal; i++) { clsItem Item = new clsItem(); Item.Code = (string)ds.Tables[0].Rows[i][0]; Item.Description = (string)ds.Tables[0].Rows[i][1]; Item.Cost = float.Parse(ds.Tables[0].Rows[i][2].ToString()); ItemsList.Add(Item); } return(ItemsList); } catch (Exception ex) { throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message); } }
/// <summary> /// Load All the Equipment /// </summary> public void getTable() { try { DataSet ds; string sSQL; sSQL = "Select * FROM EQUIPMENT"; ds = db.ExecuteSQLStatement(sSQL, ref iReturn_Value); } catch (Exception ex) { throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message); } }
/// <summary> /// Get list of LineItems /// </summary> /// <returns></returns> internal List <Item> GetLineItems() { List <Item> items = new List <Item>(); //try //{ DataSet ds = new DataSet(); string sSQL = String.Format(clsMainSQL.GetLineItems); int iRet = 0; clsData = new clsDataAccess(); ds = clsData.ExecuteSQLStatement(sSQL, ref iRet); for (int i = 0; i < iRet; i++) { items.Add(new Item() { Code = ds.Tables[0].Rows[i][0].ToString(), Description = ds.Tables[0].Rows[i][1].ToString(), Cost = int.Parse(ds.Tables[0].Rows[i][2].ToString()) }); } //} //catch (Exception ex) //{ // throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message); //} return(items); }
/// <summary> /// Method to check if an item is contained in any invoice /// </summary> /// <param name="db"></param> /// <param name="code"></param> /// <returns></returns> public string ItemInvoice(clsDataAccess db, string code) { try { string sInvoices = ""; sSQL = "select distinct(InvoiceNum) from LineItems where ItemCode = '" + code + "'"; ds = db.ExecuteSQLStatement(sSQL, ref iRetVal); for (int i = 0; i < iRetVal; i++) { sInvoices += string.Concat(ds.Tables[0].Rows[i][0].ToString()); if ((i + 1) != iRetVal) { sInvoices = string.Concat(", "); } } return(sInvoices); } catch (Exception ex) { throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message); } }
/// <summary> /// Returns the collection of items from a certain invoice /// </summary> /// <param name="invoiceNumber"></param> /// <returns></returns> public ObservableCollection<clsItem> invoiceItems(string invoiceNumber) { try { db = new clsDataAccess(); ObservableCollection<clsItem> col_Items = new ObservableCollection<clsItem>(); List<string> itemCode = new List<string>(); string sSQL; //Holds an SQL statement int iRet = 0; //Number of return values ds = new DataSet(); //Holds the return values clsItem items; //Used to load the return values into the combo box sSQL = "SELECT ItemCode FROM LineItems " + "WHERE InvoiceNum = " + invoiceNumber; ds = db.ExecuteSQLStatement(sSQL, ref iRet); // adds all of the different item codes on the invoice to a list for (int i = 0; i < iRet; i++) { itemCode.Add(ds.Tables[0].Rows[i]["ItemCode"].ToString()); } if (itemCode.Count > 0) { foreach (string code in itemCode) { sSQL = "SELECT ItemCode, ItemDesc, Cost FROM ItemDesc " + "WHERE ItemCode = '" + code + "'"; ds = db.ExecuteSQLStatement(sSQL, ref iRet); items = new clsItem(); items.ItemCode = ds.Tables[0].Rows[0][0].ToString(); items.ItemDesc = ds.Tables[0].Rows[0]["ItemDesc"].ToString(); items.Cost = ds.Tables[0].Rows[0]["Cost"].ToString(); col_Items.Add(items); } } return col_Items; } catch (Exception ex) { throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message); } }
/// <summary> /// Adds a new invoice to the database /// </summary> /// <param name="date"></param> /// <param name="totalCharge"></param> /// <param name="items"></param> public void addInvoice(string date, string totalCharge, ObservableCollection<clsItem> items) { try { db = new clsDataAccess(); int iRet = 0; string sSQL = "INSERT INTO Invoices( InvoiceDate, TotalCharge) VALUES(#" + date + "#, " + totalCharge + ")"; db.ExecuteNonQuery(sSQL); string sSQLInvoiceNumber = "SELECT MAX(InvoiceNum) FROM Invoices"; DataSet newInvoice = db.ExecuteSQLStatement(sSQLInvoiceNumber, ref iRet); string invoiceNumber = newInvoice.Tables[0].Rows[0][0].ToString(); int i = 1; foreach (clsItem item in items) { string sSQLLineItem = "INSERT INTO LineItems(InvoiceNum, LineItemNum, ItemCode) " + "VALUES('" + invoiceNumber + "', '" + i + "' , '" + item.ItemCode + "')"; db.ExecuteNonQuery(sSQLLineItem); i += 1; } } catch (Exception ex) { throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message); } }