private static int AddNewQuote(_Quote quote) { SqlCommand cmdQuote = new SqlCommand("INSERT INTO Quote (TotalServiceTime, CustomerID, ServiceDescription, AdditionalLabourPrice) " + "VALUES (@totalServiceTime, @customerID, @serviceDescription, @additionalLabourPrice)", connection); cmdQuote.Parameters.Add("@totalServiceTime", SqlDbType.Int).Value = quote.TotalServiceTime; cmdQuote.Parameters.Add("@customerID", SqlDbType.Int).Value = quote.CustomerID; cmdQuote.Parameters.Add("@serviceDescription", SqlDbType.Text).Value = quote.ServiceDescription; cmdQuote.Parameters.Add("@additionalLabourPrice", SqlDbType.Int).Value = quote.AdditionalLabourPrice; try { connection.Open(); quote.ID = Convert.ToInt32(cmdQuote.ExecuteScalar()); /* * foreach (Item i in quote.Items) * { * SqlCommand cmdQP = new SqlCommand("INSERT INTO QuotePart VALUES(" + quote.ID + "," + i.ID + "," + i.Quantity + ")", connection); * cmdQP.ExecuteNonQuery(); * }*/ } finally { connection.Close(); } return(quote.ID); }
private static void sqlGetQuotes() { //SqlConnection connection = new SqlConnection("Data Source=fastapps04.qut.edu.au;Initial Catalog=n5499879;User ID=n5499879;Password=ohmedix"); List <_Quote> quotes = new List <_Quote>(); SqlCommand cmd = new SqlCommand("SELECT * FROM Quote", connection); try { connection.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { _Quote q = new _Quote(); q.TotalServiceTime = reader.GetInt32(1); q.PartsETA = reader.GetDateTime(3); q.IsApproved = reader.GetBoolean(4); q.PurchaseOrderID = reader.GetInt32(5); q.ServiceDescription = reader.GetString(7); q.AdditionalLabourPrice = reader.GetInt32(8); quotes.Add(q); } reader.Close(); foreach (_Quote q in quotes) { cmd.CommandText = "SELECT * FROM QuotePart qp INNER JOIN WarehouseStock whs ON qp.PartID = whs.PartID WHERE qp.QuoteID=" + q.ID; SqlDataReader r = cmd.ExecuteReader(); while (r.Read()) { _Item i = new _Item(); i.ID = r.GetInt32(1); i.Quantity = r.GetInt32(2); i.Description = r.GetString(4); i.Price = r.GetInt32(5); i.ServiceTime = r.GetInt32(7); q.AddItem(i); } r.Close(); } } finally { connection.Close(); } }