private void Form1_Shown(object sender, EventArgs e)
{
var ops = new SqlServerOperations();
/*
* Read only active customers and active contacts
*/
_bsCustomers.DataSource = ops.Customers(true);
bindingNavigator1.BindingSource = _bsCustomers;
dataGridView1.DataSource = _bsCustomers;
/*
* Configure how columns are shown, not shown
*/
var configItems = ops.CustomerConfigurationItems();
foreach (DataGridViewColumnDefinition item in configItems)
{
if (!dataGridView1.Columns.Contains(item.Name))
{
continue;
}
dataGridView1.Columns[item.Name].DisplayIndex = item.Position;
dataGridView1.Columns[item.Name].Visible = item.Visible;
dataGridView1.Columns[item.Name].HeaderText = item.DisplayText;
}
dataGridView1.ExpandColumns();
cmdGetInactiveCustomers.Enabled = ops.InactiveCustomers().Count > 0;
cmdActivateCustomer.DataBindings.Add("Enabled", cmdGetInactiveCustomers, "Enabled");
}
private void selectByCountryButton_Click(object sender, EventArgs e)
{
if (countriesCombox.Text == "Select")
{
MessageBox.Show("Please select a country");
return;
}
var ops = new SqlServerOperations();
var item = (CountryItem)countriesCombox.SelectedItem;
var destinationFileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, $"{item.Compact}.xlsx");
if (CreateNewExcelFileCheckBox.Checked)
{
if (!(ops.CopyToApplicationFolder(_companyFileName, destinationFileName)))
{
MessageBox.Show("Copy failed");
return;
}
}
var rowCount = 0;
if (ops.ExportByCountryNameCustomersToExcel(destinationFileName, countriesCombox.Text, ref rowCount))
{
MessageBox.Show($"Exported {rowCount} rows.");
}
else
{
MessageBox.Show(ops.ExceptionMessage);
}
}
private void Form1_Load(object sender, EventArgs e)
{
var ops = new SqlServerOperations();
countriesCombox.DataSource = ops.CountryList();
countriesCombox.DisplayMember = "Name";
}
/// <summary>
/// Export from SQL-Server Customers table selected fields.
/// The same can be done by writing a method for exporting to Excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void cmdExportFromSqlServerToMsAccessDynamic_Click(object sender, EventArgs e)
{
var columnList = clbColumnNames.CheckedIColumnDetailsList();
if (columnList.Count > 0)
{
/*
* if chkBracketed is checked wrap field names with [], otherwise
* use field names without brackets.
*/
var fields = chkBracketed.Checked
? string.Join(",", columnList.Select(col => col.NameBracketed))
: string.Join(",", columnList.Select(col => col.Name));
var ops = new SqlServerOperations();
if (!(ops.CopyToApplicationFolder(_accessCompanyFileName)))
{
MessageBox.Show(ops.LastExceptionMessage);
return;
}
int rowCount = 0;
MessageBox.Show(
ops.ExportAllCustomersToAccess(_accessCompanyFileName, countriesComboxAccess2.Text, fields, ref rowCount)
? $"Exported {rowCount} rows."
: ops.LastExceptionMessage);
}
else
{
MessageBox.Show("Please select one or more fields and try again");
}
}
/// <summary>
/// Reset all Customers to active status, requires a restart. Normally
/// would never been done in an application but this is to show it's easily done.
///
/// Under normal conditions this would not be an available option, it's here for
/// demonstrative purposed of this code sample.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void cmdActivateAllCustomers_Click(object sender, EventArgs e)
{
if (Question("This will set all customers to active status, continue"))
{
var ops = new SqlServerOperations();
MessageBox.Show(ops.ActivateAllCustomer() ? "Finished, Press OK to restart." : ops.LastExceptionMessage);
Application.Restart();
}
}
private async void GenderForm_Shown(object sender, EventArgs e)
{
var ops = new SqlServerOperations();
var genderList = await ops.GenderList();
listBox1.DataSource = genderList;
IdentifierLabel.DataBindings.Add("Text", genderList, "Id");
}
/// <summary>
/// Demonstrates writing customers table to a xml file.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void cmdExportToXml_Click(object sender, EventArgs e)
{
var ops = new SqlServerOperations();
// ReSharper disable once ConvertIfStatementToConditionalTernaryExpression
if (ops.WriteCustomerTableToXmlFile(_xmlCompanyFileName))
{
MessageBox.Show("Customers data written successfully");
}
else
{
MessageBox.Show(ops.LastExceptionMessage);
}
}
/// <summary>
/// Used to activate the currently selected inactive customer in the
/// ComboBox to the left.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void cmdActivateCustomer_Click(object sender, EventArgs e)
{
if (cboInactiveCustomers.SelectedIndex > -1)
{
var ops = new SqlServerOperations();
if (ops.ActivateCustomer((int)cboInactiveCustomers.SelectedValue))
{
MessageBox.Show($"'{cboInactiveCustomers.Text}' will be available when starting this application again.");
}
else
{
MessageBox.Show("Failed to activate customer");
}
}
}
private async void Form1_Shown(object sender, EventArgs e)
{
var ops = new SqlServerOperations();
_bindingListOriginalData =
new BindingList <Customer>(await ops.CustomersList());
listBox1.DataSource = _bindingListOriginalData;
listBox2.DataSource = _bindingListRight;
listBox2.DisplayMember = "CompanyName";
listBox1.DoubleClick += ListBox1_DoubleClick;
listBox2.DoubleClick += ListBox2_DoubleClick;
}
/// <summary>
/// Export all customer records or by country
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void cmdExportFromSqlServerToMsAccess_Click(object sender, EventArgs e)
{
var ops = new SqlServerOperations();
if (!(ops.CopyToApplicationFolder(_accessCompanyFileName)))
{
MessageBox.Show(ops.LastExceptionMessage);
return;
}
int rowCount = 0;
MessageBox.Show(
ops.ExportAllCustomersToAccess(_accessCompanyFileName, countriesComboxAccess1.Text, ref rowCount)
? $"Exported {rowCount} rows."
: ops.LastExceptionMessage);
}
/// <summary>
/// Get all inactive customers to use for reactivating one or more
/// customers
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void cmdGetInactiveCustomers_Click(object sender, EventArgs e)
{
var ops = new SqlServerOperations();
var customerList = ops.InactiveCustomers();
if (customerList.Count > 0)
{
cboInactiveCustomers.AutoCompleteMode = AutoCompleteMode.Suggest;
cboInactiveCustomers.AutoCompleteSource = AutoCompleteSource.ListItems;
cboInactiveCustomers.DataSource = customerList;
cboInactiveCustomers.DisplayMember = "Name";
cboInactiveCustomers.ValueMember = "id";
}
else
{
MessageBox.Show("Currently there are no inactive customers.");
}
}
/// <summary>
/// Export all customers table records to Excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void selectAllButton_Click(object sender, EventArgs e)
{
var ops = new SqlServerOperations();
if (CreateNewExcelFileCheckBox.Checked)
{
if (!(ops.CopyToApplicationFolder(_excelCompanyFileName)))
{
MessageBox.Show(ops.LastExceptionMessage);
return;
}
}
int rowCount = 0;
MessageBox.Show(ops.ExportAllCustomersToExcel(_excelCompanyFileName, ref rowCount)
? $"Exported {rowCount} rows."
: ops.LastExceptionMessage);
}
/// <summary>
/// Promot user to deactivate the currently selected customer record.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void bindingNavigatorMakeInActive_Click(object sender, EventArgs e)
{
if (_bsCustomers.CurrentIsValid())
{
if (Question($"Remove '{_bsCustomers.CurrentRow().Field<string>("CompanyName")}'"))
{
var ops = new SqlServerOperations();
if (ops.InactivateCustomer(_bsCustomers.CurrentRow().Field <int>("CustomerIdentifier")))
{
_bsCustomers.RemoveCurrent();
cmdGetInactiveCustomers.Enabled = true;
}
else
{
MessageBox.Show(ops.LastExceptionMessage);
}
}
}
}
/// <summary>
/// Get in active customers
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void cmdInActiveCustomers_Click(object sender, EventArgs e)
{
var ops = new SqlServerOperations();
var customerList = ops.InactiveCustomers();
if (customerList.Count > 0)
{
var f = new InactiveCustomersForm(customerList);
try
{
f.ShowDialog();
}
finally
{
f.Dispose();
}
}
else
{
MessageBox.Show("There are no inactive customers at this time");
}
}
/// <summary>
/// Load reference table countries into two ComboBox controls
/// for allowing selections for exporting to either ms-access
/// or ms-excel from sql-server.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void Form1_Load(object sender, EventArgs e)
{
var ops = new SqlServerOperations();
countriesComboxExcel.DataSource = ops.CountryList();
countriesComboxAccess1.DataSource = ops.CountryList("*");
countriesComboxAccess2.DataSource = new List <CountryItem>((List <CountryItem>)countriesComboxAccess1.DataSource);
clbColumnNames.DataSource = ops.GetColumnsForTable("Customers");
if (ops.HasException)
{
// disable buttons as they are of no use
Controls.OfType <Button>().ToList().ForEach(but => but.Enabled = false);
MessageBox.Show(ops.LastExceptionMessage);
return;
}
countriesComboxExcel.DisplayMember = "Name";
countriesComboxAccess1.DisplayMember = "Name";
countriesComboxAccess2.DisplayMember = "Name";
}
