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);
}
