public static int WritePayable(Payable payable) { try { connection = PayablesDB.GetConnection(); connection.Open(); payableTran = connection.BeginTransaction(); payableCommand = new SqlCommand(); payableCommand.Connection = connection; payableCommand.Transaction = payableTran; int invoiceID = InsertInvoice(payable); int invoiceSequence = 0; foreach (LineItem li in payable.LineItems) { li.InvoiceID = invoiceID; invoiceSequence += 1; li.InvoiceSequence = invoiceSequence; InsertLineItem(li); } payableTran.Commit(); return(invoiceID); } catch (SqlException ex) { payableTran.Rollback(); throw ex; } finally { connection.Close(); } }
private static int InsertInvoice(Payable payable) { payableCommand.CommandText = "INSERT INTO Invoices " + "(VendorID, InvoiceNumber, InvoiceDate, InvoiceTotal, " + "TermsID, DueDate) " + "VALUES (@VendorID, @InvoiceNumber, @InvoiceDate, " + "@InvoiceTotal, @TermsID, @DueDate)"; payableCommand.Parameters.Clear(); payableCommand.Parameters.AddWithValue("@VendorID", payable.Invoice.VendorID); payableCommand.Parameters.AddWithValue("@InvoiceNumber", payable.Invoice.InvoiceNumber); payableCommand.Parameters.AddWithValue("@InvoiceDate", payable.Invoice.InvoiceDate); payableCommand.Parameters.AddWithValue("@InvoiceTotal", payable.Invoice.InvoiceTotal); payableCommand.Parameters.AddWithValue("@TermsID", payable.Invoice.TermsID); payableCommand.Parameters.AddWithValue("@DueDate", payable.Invoice.DueDate); payableCommand.ExecuteNonQuery(); payableCommand.CommandText = "SELECT IDENT_CURRENT('Invoices') FROM Invoices"; int invoiceID = Convert.ToInt32(payableCommand.ExecuteScalar()); return(invoiceID); }
private static int InsertInvoice(Payable payable) { SqlCommand insertCommand = new SqlCommand(); insertCommand.Connection = connection; insertCommand.CommandText = "INSERT INTO Invoices " + "(VendorID, InvoiceNumber, InvoiceDate, InvoiceTotal, " + "TermsID, DueDate) " + "VALUES (@VendorID, @InvoiceNumber, @InvoiceDate, " + "@InvoiceTotal, @TermsID, @DueDate)"; insertCommand.Transaction = payableTran; insertCommand.Parameters.Clear(); insertCommand.Parameters.AddWithValue("@VendorID", payable.Invoice.VendorID); insertCommand.Parameters.AddWithValue("@InvoiceNumber", payable.Invoice.InvoiceNumber); insertCommand.Parameters.AddWithValue("@InvoiceDate", payable.Invoice.InvoiceDate); insertCommand.Parameters.AddWithValue("@InvoiceTotal", payable.Invoice.InvoiceTotal); insertCommand.Parameters.AddWithValue("@TermsID", payable.Invoice.TermsID); insertCommand.Parameters.AddWithValue("@DueDate", payable.Invoice.DueDate); insertCommand.ExecuteNonQuery(); SqlCommand identCommand = new SqlCommand("SELECT IDENT_CURRENT('Invoices') FROM Invoices", connection); identCommand.Transaction = payableTran; int invoiceID = Convert.ToInt32(identCommand.ExecuteScalar()); return(invoiceID); }