C# Relational Database
Child Forms
Struct Array
Serialized
DataGridView
This is an advanced Child Forms MDI Accounting project. Here I use a struct array which in effect has tables. For example, the Sales transaction picks a customer from the Customers table.
This avoids duplication as the customer is only once in the database. The Sales and Purchases are now presented in DataGridViews.
The struct array file is saved Serialized. This makes it easy to save and load the array. Once loaded into the array when the application starts, the data has to be copied from the array into the various forms.
This project will be similar to the Child Forms project and a copy of that project could be adapted. Let's make a start to the MAD Accounting Project which is a More Advanced Database.
The first form is named frmParent. Set its IsMdiContainer property to True and add a MenuStrip with the menus shown.
Child Forms
Struct Array
Serialized
DataGridView
This is an advanced Child Forms MDI Accounting project. Here I use a struct array which in effect has tables. For example, the Sales transaction picks a customer from the Customers table.
This avoids duplication as the customer is only once in the database. The Sales and Purchases are now presented in DataGridViews.
The struct array file is saved Serialized. This makes it easy to save and load the array. Once loaded into the array when the application starts, the data has to be copied from the array into the various forms.
This project will be similar to the Child Forms project and a copy of that project could be adapted. Let's make a start to the MAD Accounting Project which is a More Advanced Database.
The first form is named frmParent. Set its IsMdiContainer property to True and add a MenuStrip with the menus shown.
|
|
Add six forms.
Click frmParent and press F7 to start coding.
In the using area add:
using System.IO;.
using System.Runtime.Serialization;
using System.Runtime.Serialization.Formatters.Binary;
After { InitializeComponent() }; add:
[Serializable]
Next we'll declare the Structure, which is like database columns or fields once we declare an array of it, and 2 variables to store totals.
public struct Accounting
{
//For Sales
public string Customers;
public string SaleAccounts;
//Each Sale
public String SaleDate;
public int Customer; //Customer[0] records the number of customers
public int SaleAccount; //SaleAccount[0] records the number of accounts
public string SaleDescription;
public float SaleAmount; //SaleAmount[0] records the number of Sales
//For Purchases
public string Vendors;
public string PurchaseAccounts;
//Each Purchase
public String PurchaseDate;
public int Vendor; //Vendor[0] records the number of Vendors
public int PurchaseAccount; //PurchaseAccount[0] records the number of Accounts
public string PurchaseDescription;
public float PurchaseAmount; //PurchaseAmount[0] records the number of Purchases
}
//The Acc struct array creates the rows, or records, for 100,000 Sales and Purchases
//The Sales, Purchases,Customers, Vendors and Accounts start at [1]
//The zero row stores numbers of Customers, Vendors, Accounts, Sales and Purchases
public static Accounting[] Acc = new Accounting[100000];
//Add up Sales and Purchases totals
public static Single sngTotalSales = new Single();
public static Single sngTotalPurchases = new Single();
//Instantiate Child forms
frmMenu BigMenu = new frmMenu();
public static frmAddSale Sale = new frmAddSale();
public static frmListSales ListSales = new frmListSales();
public static frmAddPurchase Purchase = new frmAddPurchase();
public static frmListPurchases ListPurchases = new frmListPurchases();
public static frmBalance Balance = new frmBalance();
//Enable bulk closing
public static bool ParentClosing = new bool();
//More to come
Design the expanded Menu form.
In the using area add:
using System.IO;.
using System.Runtime.Serialization;
using System.Runtime.Serialization.Formatters.Binary;
After { InitializeComponent() }; add:
[Serializable]
Next we'll declare the Structure, which is like database columns or fields once we declare an array of it, and 2 variables to store totals.
public struct Accounting
{
//For Sales
public string Customers;
public string SaleAccounts;
//Each Sale
public String SaleDate;
public int Customer; //Customer[0] records the number of customers
public int SaleAccount; //SaleAccount[0] records the number of accounts
public string SaleDescription;
public float SaleAmount; //SaleAmount[0] records the number of Sales
//For Purchases
public string Vendors;
public string PurchaseAccounts;
//Each Purchase
public String PurchaseDate;
public int Vendor; //Vendor[0] records the number of Vendors
public int PurchaseAccount; //PurchaseAccount[0] records the number of Accounts
public string PurchaseDescription;
public float PurchaseAmount; //PurchaseAmount[0] records the number of Purchases
}
//The Acc struct array creates the rows, or records, for 100,000 Sales and Purchases
//The Sales, Purchases,Customers, Vendors and Accounts start at [1]
//The zero row stores numbers of Customers, Vendors, Accounts, Sales and Purchases
public static Accounting[] Acc = new Accounting[100000];
//Add up Sales and Purchases totals
public static Single sngTotalSales = new Single();
public static Single sngTotalPurchases = new Single();
//Instantiate Child forms
frmMenu BigMenu = new frmMenu();
public static frmAddSale Sale = new frmAddSale();
public static frmListSales ListSales = new frmListSales();
public static frmAddPurchase Purchase = new frmAddPurchase();
public static frmListPurchases ListPurchases = new frmListPurchases();
public static frmBalance Balance = new frmBalance();
//Enable bulk closing
public static bool ParentClosing = new bool();
//More to come
Design the expanded Menu form.
Set the ControlBox to False to hide the Minimize/Maximize/Close buttons. This prevents this form from being closed.
As you can see, there are buttons to add Customers, Vendors and Accounts to their tables. First thing to do when the project is running is to make some entries into those tables being careful not to enter an item more than once such as M Smith then Matt Smith and Matthew Smith all being the same customer or vendor.
Then in the Sale, or Purchase, we'll pick one of them from a ComboBox and only a SelectedIndex number is recorded for the transaction.
Design the Sale form. It has two ComboBoxes. Set the Modifiers property to Public for the top 3 boxes.
txtDescription has a MaxLength of 25 characters and txtAmount allows 10. Do the same for the Purchase form or copy and paste the controls which is, of course, easier.
As you can see, there are buttons to add Customers, Vendors and Accounts to their tables. First thing to do when the project is running is to make some entries into those tables being careful not to enter an item more than once such as M Smith then Matt Smith and Matthew Smith all being the same customer or vendor.
Then in the Sale, or Purchase, we'll pick one of them from a ComboBox and only a SelectedIndex number is recorded for the transaction.
Design the Sale form. It has two ComboBoxes. Set the Modifiers property to Public for the top 3 boxes.
txtDescription has a MaxLength of 25 characters and txtAmount allows 10. Do the same for the Purchase form or copy and paste the controls which is, of course, easier.
Double click the Parent form to start the Load method.
private void frmParent_Load(object sender, EventArgs e)
{
this.Size = new Size(1160, 730);
BigMenu.MdiParent = this;
BigMenu.Show();
Sale.MdiParent = this;
DateTime TodaysDate = DateTime.Today;
Sale.txtDate.Text = TodaysDate.ToShortDateString();
Sale.Show();
Purchase.MdiParent = this;
Purchase.txtDate.Text = TodaysDate.ToShortDateString();
Purchase.Show();
ListSales.MdiParent = this;
ListSales.Show();
ListPurchases.MdiParent = this;
ListPurchases.Show();
Balance.MdiParent = this;
Balance.Show();
//More to come
Run this for this result. They'll be cascaded to start with as per the Child Forms project.
private void frmParent_Load(object sender, EventArgs e)
{
this.Size = new Size(1160, 730);
BigMenu.MdiParent = this;
BigMenu.Show();
Sale.MdiParent = this;
DateTime TodaysDate = DateTime.Today;
Sale.txtDate.Text = TodaysDate.ToShortDateString();
Sale.Show();
Purchase.MdiParent = this;
Purchase.txtDate.Text = TodaysDate.ToShortDateString();
Purchase.Show();
ListSales.MdiParent = this;
ListSales.Show();
ListPurchases.MdiParent = this;
ListPurchases.Show();
Balance.MdiParent = this;
Balance.Show();
//More to come
Run this for this result. They'll be cascaded to start with as per the Child Forms project.
Double click the Tasks Sale menu to bring back the form if it was closed by the user.
Private void saleToolStripMenuItem_Click(object sender, EventArgs e)
{
Sale.Show();
Sale.Activate();
}
Repeat this for the Purchase menu.
Purchase.Show();
Purchase.Activate();
The other three menus do not need to activate the particular form as the user does not enter data on them.
ListSales.Show();
ListPurchases.Show();
Balance.Show();
This will not bring these forms back as yet. To achieve this, we’ll cancel Closing and just Hide a form when the user closes it.
This will also preserve the data on the forms. Add the following to the FormClosing method of all the child forms except the menu.
To start a FormClosing method click the form, switch to the events flash in the Properties box and double click on FormClosing.
private void frmAddSale_FormClosing(object sender, FormClosingEventArgs e)
{
if (!frmParent.ParentClosing)
{
e.Cancel = true;
this.Hide();
}
}
This, however, means that clicking the project close button only closes one child at a time. To enable the application to close with one click, code the following for the Exit menu.
private void exitToolStripMenuItem_Click(object sender, EventArgs e)
{
ParentClosing = true;
Application.Exit();
}
The last menu item is to Cascade the forms.
private void cascadeToolStripMenuItem_Click(object sender, EventArgs e)
{
this.LayoutMdi(System.Windows.Forms.MdiLayout.Cascade);
}
Double click the Make and Show buttons on the Menu form and add the following codes for each button. The codes are like the parent menus but have to refer to the forms via the parent to access them.
frmParent.Sale.Show();
frmParent.Sale.Activate();
//Ready to enter data
frmParent.Purchase.Show();
frmParent.Purchase.Activate();
//Ready to enter data
frmParent.ListSales.Show();
frmParent.ListPurchases.Show();
frmParent.Balance.Show();
//Exit button
frmParent.ParentClosing = true
Application.Exit();
Just like the parent, the Exit button will close all forms. Add code to the Activated method for each form to space them out on the parent form.
//frmAddSale_Activated
this.Top = 0;
this.Left = 330;
//frmAddPurchase_Activated
this.Top = 0;
this.Left = 600;
//frmBalance_Activated
this.Top = 0;
this.Left = 870;
//frmListSales_Activated
this.Top = 300;
this.Left = 0;
//frmListPurchases_Activated
this.Top = 300;
this.Left = 560;
Add a Visual Basic reference via Project > Add Reference...
Private void saleToolStripMenuItem_Click(object sender, EventArgs e)
{
Sale.Show();
Sale.Activate();
}
Repeat this for the Purchase menu.
Purchase.Show();
Purchase.Activate();
The other three menus do not need to activate the particular form as the user does not enter data on them.
ListSales.Show();
ListPurchases.Show();
Balance.Show();
This will not bring these forms back as yet. To achieve this, we’ll cancel Closing and just Hide a form when the user closes it.
This will also preserve the data on the forms. Add the following to the FormClosing method of all the child forms except the menu.
To start a FormClosing method click the form, switch to the events flash in the Properties box and double click on FormClosing.
private void frmAddSale_FormClosing(object sender, FormClosingEventArgs e)
{
if (!frmParent.ParentClosing)
{
e.Cancel = true;
this.Hide();
}
}
This, however, means that clicking the project close button only closes one child at a time. To enable the application to close with one click, code the following for the Exit menu.
private void exitToolStripMenuItem_Click(object sender, EventArgs e)
{
ParentClosing = true;
Application.Exit();
}
The last menu item is to Cascade the forms.
private void cascadeToolStripMenuItem_Click(object sender, EventArgs e)
{
this.LayoutMdi(System.Windows.Forms.MdiLayout.Cascade);
}
Double click the Make and Show buttons on the Menu form and add the following codes for each button. The codes are like the parent menus but have to refer to the forms via the parent to access them.
frmParent.Sale.Show();
frmParent.Sale.Activate();
//Ready to enter data
frmParent.Purchase.Show();
frmParent.Purchase.Activate();
//Ready to enter data
frmParent.ListSales.Show();
frmParent.ListPurchases.Show();
frmParent.Balance.Show();
//Exit button
frmParent.ParentClosing = true
Application.Exit();
Just like the parent, the Exit button will close all forms. Add code to the Activated method for each form to space them out on the parent form.
//frmAddSale_Activated
this.Top = 0;
this.Left = 330;
//frmAddPurchase_Activated
this.Top = 0;
this.Left = 600;
//frmBalance_Activated
this.Top = 0;
this.Left = 870;
//frmListSales_Activated
this.Top = 300;
this.Left = 0;
//frmListPurchases_Activated
this.Top = 300;
this.Left = 560;
Add a Visual Basic reference via Project > Add Reference...
Back on the Menu form double click on Add Customer.
public void btnAddCustomer_Click(object sender, EventArgs e)
{
//Get name from the user
string strName = Microsoft.VisualBasic.Interaction.InputBox("Enter Customer:", "Customer Entry").Trim();
//Check for presence of text
if (string.Compare(strName, "") == 0)
{
MessageBox.Show
(
"Customer not added!",
"Customer not added",
MessageBoxButtons.OK,
MessageBoxIcon.Stop
);
return;
}
else
//Check length of text
if (strName.Length > 25)
{
MessageBox.Show
(
"Text is longer than 25 characters!",
"Customer not added",
MessageBoxButtons.OK,
MessageBoxIcon.Stop
);
return;
}
//Increase the number of customers in the array
frmParent.Acc[0].Customer += 1;
//Add the customer to the array
frmParent.Acc[frmParent.Acc[0].Customer].Customers = strName;
//and to the ComboBox on the form
//*Change Sale to Purchase form for Vendor and PurchaseAccount
//*Change Name to Account for the Accounts
frmParent.Sale.cboName.Items.Add(strName);
MessageBox.Show
(
strName + " is added",
"Customer added",
MessageBoxButtons.OK,
MessageBoxIcon.Information
);
}
The code is the same for adding a Vendor*, SaleAccount and PurchaseAccount*. Replace all the Customer texts with one of those.
*Also change to the Purchase form for The Vendor and PurchaseAccount and cboAccount for Accounts.
Let's check the date entered.
private void txtDate_Leave(object sender, EventArgs e)
{
DateTime T;
if (!DateTime.TryParse(txtDate.Text, out T) )
{
MessageBox.Show
(
"Please enter valid date",
"Not a Date",
MessageBoxButtons.OK,
MessageBoxIcon.Stop
);
txtDate.Focus();
}
}
Design the ListSales form.
Set the Borderstyle of lblTotal to FixedSingle, the BackColor to White, TextAlign to MiddleRight, and AutoSize to False.
Set the Modifiers property to Public for the DataGridView and lblTotal to enable access from other child forms.
Click the Columns property of the DataGridview and add the columns shown.
I set the widths to:
Date: 60
Name: 100
Account: 80
Description: 150
Amount: 80
The DefaultCellStyle for the Amount column is set to MiddleRight in the Alignment property.
Set the Modifiers property to Public for the DataGridView and lblTotal to enable access from other child forms.
Click the Columns property of the DataGridview and add the columns shown.
I set the widths to:
Date: 60
Name: 100
Account: 80
Description: 150
Amount: 80
The DefaultCellStyle for the Amount column is set to MiddleRight in the Alignment property.
I set an alternating row BackColor in the AlternatingRowsDefaultCellStyle property which opens the CellStyle Builder.
Copy all controls and paste them to the Purchases form.
Design the Balance form. Copy lblTotal off the ListSales form and paste 3 copies, then rename them. The Modifiers property should already be set to Public.
Design the Balance form. Copy lblTotal off the ListSales form and paste 3 copies, then rename them. The Modifiers property should already be set to Public.
Double click the Add Sale button.
private void btnAdd_Click(object sender, EventArgs e)
{
if (cboName.SelectedItem == null)
{
MessageBox.Show("Please select a Name",
"No Name selected",
MessageBoxButtons.OK,
MessageBoxIcon.Stop);
cboName.Focus();
return;
}
if (cboAccount.SelectedItem == null)
{
MessageBox.Show("Please select an Account",
"No Account selectet",
MessageBoxButtons.OK,
MessageBoxIcon.Stop);
cboAccount.Focus();
return;
}
if (string.IsNullOrWhiteSpace(txtDescription.Text))
{
MessageBox.Show("Please enter Description ",
"No Description entered",
MessageBoxButtons.OK,
MessageBoxIcon.Stop);
txtDescription.Focus();
return;
}
Single i;
if (!Single.TryParse(txtAmount.Text, out i))
{
MessageBox.Show("Please enter an Amount ",
"No Amount entered",
MessageBoxButtons.OK,
MessageBoxIcon.Stop);
txtAmount.Focus();
return;
}
//Add the data to the dataGridView
frmParent.ListSales.dataGridView1.Rows.Add
(
txtDate.Text,
cboName.SelectedItem.ToString(),
cboAccount.SelectedItem.ToString(),
txtDescription.Text,
Convert.ToSingle(txtAmount.Text).ToString("C")
);
//Add transaction to the array
//Increment the Sale Number in the array
frmParent.Acc[0].SaleAmount += 1;
//Get the new Sale number
int x = Convert.ToInt32(frmParent.Acc[0].SaleAmount);
//Put the data into the array
frmParent.Acc[x].SaleDate = txtDate.Text;
frmParent.Acc[x].Customer = cboName.SelectedIndex + 1;
frmParent.Acc[x].SaleAccount = cboAccount.SelectedIndex + 1;
frmParent.Acc[x].SaleDescription = txtDescription.Text;
frmParent.Acc[x].SaleAmount = Convert.ToSingle(txtAmount.Text);
//Add to total of transactions
frmParent.sngTotalSales += Convert.ToSingle(txtAmount.Text);
//Update the forms
frmParent.ListSales.lblTotal.Text = frmParent.sngTotalSales.ToString("C");
frmParent.Balance.lblSales.Text = frmParent.sngTotalSales.ToString("C");
/*Copy all to 'Add Purchase' but
change every Sale to Purchase and Customer to Vendor up to here*/
//Calculate and display the Balance
frmParent.Balance.lblBalance.Text =
(frmParent.sngTotalSales - frmParent.sngTotalPurchases).ToString("C");
txtDate.Focus();
}
When the application closes we'll of course want to save the data.
private void frmParent_FormClosing(object sender, FormClosingEventArgs e)
{
IFormatter bf = new BinaryFormatter();
Stream fs = new FileStream("Accounting File", FileMode.Create, FileAccess.Write);
bf.Serialize(fs, Acc);
fs.Close();
}
The saved file is not as readable as the one with the Child Forms project. The array data place holders continue a long way down.
private void frmParent_FormClosing(object sender, FormClosingEventArgs e)
{
IFormatter bf = new BinaryFormatter();
Stream fs = new FileStream("Accounting File", FileMode.Create, FileAccess.Write);
bf.Serialize(fs, Acc);
fs.Close();
}
The saved file is not as readable as the one with the Child Forms project. The array data place holders continue a long way down.
That brings us to the finale which is to load the data and feed it to the various forms and controls. Add it to the Form_Load method.
//More to come (You can delete this line :)
if (File.Exists("Accounting File"))
{
FileStream fs = new FileStream("Accounting File", FileMode.Open, FileAccess.Read);
BinaryFormatter bf = new BinaryFormatter();
Acc = (Accounting[])bf.Deserialize(fs);
fs.Close();
int x;
//Add Customers to the Sale combo
for (x = 1; x <= Acc[0].Customer; x += 1)
{
Sale.cboName.Items.Add(Acc[x].Customers);
}
//Add Accounts to the Sale combo
for (x = 1; x <= Acc[0].SaleAccount; x += 1)
{
Sale.cboAccount.Items.Add(Acc[x].SaleAccounts);
}
//Add Vendors to the Purchase combo
for (x = 1; x <= Acc[0].Vendor; x += 1)
{
Purchase.cboName.Items.Add(Acc[x].Vendors);
}
//Add Accounts to the Purchase combo
for (x = 1; x <= Acc[0].PurchaseAccount; x += 1)
{
Purchase.cboAccount.Items.Add(Acc[x].PurchaseAccounts);
}
//Get Sales
for (x = 1; x <= Acc[0].SaleAmount; x += 1)
{
//Add string to Sales DataGridView
ListSales.dataGridView1.Rows.Add
(
Acc[x].SaleDate,
Acc[Acc[x].Customer].Customers,
Acc[Acc[x].SaleAccount].SaleAccounts,
Acc[x].SaleDescription,
Acc[x].SaleAmount.ToString("C")
);
//Add to total of Sales
sngTotalSales += Acc[x].SaleAmount;
}
//Update the forms
ListSales.lblTotal.Text = sngTotalSales.ToString("C");
Balance.lblSales.Text = sngTotalSales.ToString("C");
//Get Purchases
for (x = 1; x <= Acc[0].PurchaseAmount; x += 1)
{
//Add string to Purchases DataGridView
ListPurchases.dataGridView1.Rows.Add
(
Acc[x].PurchaseDate,
Acc[Acc[x].Vendor].Vendors,
Acc[Acc[x].PurchaseAccount].PurchaseAccounts,
Acc[x].PurchaseDescription,
Acc[x].PurchaseAmount.ToString("C")
);
//Add to total of Purchases
sngTotalPurchases += Acc[x].PurchaseAmount;
}
//Update the forms
ListPurchases.lblTotal.Text = sngTotalPurchases.ToString("C");
Balance.lblPurchases.Text = sngTotalPurchases.ToString("C");
//Calculate and show the Balance
Balance.lblBalance.Text = (sngTotalSales - sngTotalPurchases).ToString("C");
}
}
I hope you find something useful and instructive out of this.
Cheers,
Rudi
//More to come (You can delete this line :)
if (File.Exists("Accounting File"))
{
FileStream fs = new FileStream("Accounting File", FileMode.Open, FileAccess.Read);
BinaryFormatter bf = new BinaryFormatter();
Acc = (Accounting[])bf.Deserialize(fs);
fs.Close();
int x;
//Add Customers to the Sale combo
for (x = 1; x <= Acc[0].Customer; x += 1)
{
Sale.cboName.Items.Add(Acc[x].Customers);
}
//Add Accounts to the Sale combo
for (x = 1; x <= Acc[0].SaleAccount; x += 1)
{
Sale.cboAccount.Items.Add(Acc[x].SaleAccounts);
}
//Add Vendors to the Purchase combo
for (x = 1; x <= Acc[0].Vendor; x += 1)
{
Purchase.cboName.Items.Add(Acc[x].Vendors);
}
//Add Accounts to the Purchase combo
for (x = 1; x <= Acc[0].PurchaseAccount; x += 1)
{
Purchase.cboAccount.Items.Add(Acc[x].PurchaseAccounts);
}
//Get Sales
for (x = 1; x <= Acc[0].SaleAmount; x += 1)
{
//Add string to Sales DataGridView
ListSales.dataGridView1.Rows.Add
(
Acc[x].SaleDate,
Acc[Acc[x].Customer].Customers,
Acc[Acc[x].SaleAccount].SaleAccounts,
Acc[x].SaleDescription,
Acc[x].SaleAmount.ToString("C")
);
//Add to total of Sales
sngTotalSales += Acc[x].SaleAmount;
}
//Update the forms
ListSales.lblTotal.Text = sngTotalSales.ToString("C");
Balance.lblSales.Text = sngTotalSales.ToString("C");
//Get Purchases
for (x = 1; x <= Acc[0].PurchaseAmount; x += 1)
{
//Add string to Purchases DataGridView
ListPurchases.dataGridView1.Rows.Add
(
Acc[x].PurchaseDate,
Acc[Acc[x].Vendor].Vendors,
Acc[Acc[x].PurchaseAccount].PurchaseAccounts,
Acc[x].PurchaseDescription,
Acc[x].PurchaseAmount.ToString("C")
);
//Add to total of Purchases
sngTotalPurchases += Acc[x].PurchaseAmount;
}
//Update the forms
ListPurchases.lblTotal.Text = sngTotalPurchases.ToString("C");
Balance.lblPurchases.Text = sngTotalPurchases.ToString("C");
//Calculate and show the Balance
Balance.lblBalance.Text = (sngTotalSales - sngTotalPurchases).ToString("C");
}
}
I hope you find something useful and instructive out of this.
Cheers,
Rudi