void SaveInvoice()
{
string sql;
//create new object from form data
Invoice newInvoice = new Invoice();
newInvoice.InvoiceDateTime = Convert.ToDateTime(v.Text);
newInvoice.Shipping = ShippedChckbox.IsChecked.Value;
newInvoice.CustomerName = CustNameTxbox.Text;
newInvoice.CustomerEmail = CustEmailTxtbox.Text;
newInvoice.CustomerAddress = CustAddressTxbox.Text;
using (SqlConnection connection = new SqlConnection())
{
connection.ConnectionString = @"Data Source = (LocalDB)\MSSQLLocalDB; AttachDbFilename =|DataDirectory|\Invoices.mdf; Integrated Security = True";
connection.Open();
//to find the last primary key used (to know the maximum primary key)
sql = $"Select MAX(InvoiceID) FROM Invoices;";
int NewInvoiceID;
//to create new primary key
using (SqlCommand Selection = new SqlCommand(sql, connection))
{
NewInvoiceID = Convert.ToInt32(Selection.ExecuteScalar()) + 1;
newInvoice.InvoiceID = NewInvoiceID;
}
sql = $"INSERT INTO Invoices " +
"(InvoiceID,InvoiceDate,Shipped,CustomerName, CustomerAddress, CustomerEmail) " +
"VALUES " +
$"('{newInvoice.InvoiceID}'," +
$"'{newInvoice.InvoiceDateTime}'," +
$"'{newInvoice.Shipping}'," +
$"'{newInvoice.CustomerName}'," +
$"'{newInvoice.CustomerAddress}'," +
$"'{newInvoice.CustomerEmail}')";
using (SqlCommand InsertCommand = new SqlCommand(sql, connection))
{
InsertCommand.ExecuteNonQuery();
}
//clear all data displayed and update data
InvoiceRecListbox.Items.Clear();
LoadInvoice();
int NewInvoiceIndex = InvoiceRecListbox.Items.IndexOf(newInvoice);
//select the new index
InvoiceRecListbox.SelectedIndex = NewInvoiceIndex;
InvoiceItemListbox.ScrollIntoView(newInvoice);
}
//not to save data users put unless clicking addnew buttons
isNewRecord = false;
}
void SaveItems()
{
string sql;
//create a new object from form data
InvoiceItems newItems = new InvoiceItems();
//get changed data from form
newItems.ItemName = ItemNameTxtbox.Text;
newItems.ItemDescription = ItemDescTxtbox.Text;
newItems.ItemPrice = Convert.ToDecimal(ItemPriceTxtbox.Text);
newItems.ItemQuantity = Convert.ToInt32(ItemQuantTxtbox.Text);
using (SqlConnection connection = new SqlConnection())
{
connection.ConnectionString = @"Data Source = (LocalDB)\MSSQLLocalDB; AttachDbFilename =|DataDirectory|\Invoices.mdf; Integrated Security = True";
//open connection
connection.Open();
//find the last primary key used
sql = $"Select MAX(ItemID) FROM InvoiceItems;";
int NewItemID;
using (SqlCommand Selection = new SqlCommand(sql, connection))
{
NewItemID = Convert.ToInt32(Selection.ExecuteScalar()) + 1;
newItems.ItemID = NewItemID;
}
sql = $"INSERT INTO InvoiceItems " +
"(ItemID,InvoiceID,ItemName,ItemDescription, ItemPrice, ItemQuantity) " +
"VALUES " +
$"('{NewItemID}'," +
$"'{CurrentSelectedInvoice.InvoiceID}'," +
$"'{newItems.ItemName}'," +
$"'{newItems.ItemDescription}'," +
$"'{newItems.ItemPrice.ToString()}'," +
$"'{newItems.ItemQuantity.ToString()}')";
using (SqlCommand InsertCommand = new SqlCommand(sql, connection))
{
InsertCommand.ExecuteNonQuery();
}
LoadItems();
int NewItemIndex = InvoiceItemListbox.Items.IndexOf(newItems);
InvoiceItemListbox.SelectedIndex = NewItemIndex;
InvoiceItemListbox.ScrollIntoView(newItems);
}
isNewRecord = false;
}
void SaveItems()
{
string sql;
//create a new object from form data
InvoiceItems newItems = new InvoiceItems();
//get changed data from form
newItems.ItemName = ItemNameTxtbox.Text;
newItems.ItemDescription = ItemDescTxtbox.Text;
newItems.ItemPrice = Convert.ToDecimal(ItemPriceTxtbox.Text);
newItems.ItemQuantity = Convert.ToInt32(ItemQuantTxtbox.Text);
using (SqlConnection connection = new SqlConnection())
{
connection.ConnectionString = @"Data Source = (LocalDB)\MSSQLLocalDB; AttachDbFilename =|DataDirectory|\Invoices.mdf; Integrated Security = True";
//open connection
connection.Open();
//find the last primary key used
sql = $"Select MAX(ItemID) FROM InvoiceItems;";
//assing new item ID
int NewItemID;
//to create new primary key(item ID)
using (SqlCommand Selection = new SqlCommand(sql, connection))
{
NewItemID = Convert.ToInt32(Selection.ExecuteScalar()) + 1;
newItems.ItemID = NewItemID;
}
//insert new data into database
sql = $"INSERT INTO InvoiceItems " +
"(ItemID,InvoiceID,ItemName,ItemDescription, ItemPrice, ItemQuantity) " +
"VALUES " +
$"('{NewItemID}'," +
$"'{CurrentSelectedInvoice.InvoiceID}'," +
$"'{newItems.ItemName}'," +
$"'{newItems.ItemDescription}'," +
$"'{newItems.ItemPrice.ToString()}'," +
$"'{newItems.ItemQuantity.ToString()}')";
using (SqlCommand InsertCommand = new SqlCommand(sql, connection))
{
InsertCommand.ExecuteNonQuery();
}
//Update List<InvoiceItemsList>
InvoiceItemsList.Add(newItems);
//clear all data displayed and update data
InvoiceItemListbox.Items.Clear();
LoadItems();
//finde index of new item
int NewItemIndex = InvoiceItemListbox.Items.IndexOf(newItems);
//selec the new item
InvoiceItemListbox.SelectedIndex = NewItemIndex;
InvoiceItemListbox.ScrollIntoView(newItems);
}
//close saving function until users click add button
isNewRecord = false;
}