/// <summary>
/// Load table schema names into CheckBoxList control in the format: [schema].[name]
/// </summary>
private void LoadTables()
{
// Use datasource specified in app.config to query SQL Server database for list of table schemas
using (DataAccess data = new DataAccess(ConfigurationManager.ConnectionStrings["datasource"].ConnectionString))
{
using (SqlCommand cmd = data.GetCommand(Resources.select_tables_query))
{
using (SqlDataReader reader = cmd.ExecuteReader())
{
// Refresh CheckBoxList control with latest table schemas
ckbxTableNameList.Items.Clear();
while (reader.Read())
{
ckbxTableNameList.Items.Add(reader["SchemaTable"]);
}
}
}
}
// Update table selected
UpdateNumberOfCheckedItems();
}
/// <summary>
/// Thread-safe call to export table schemas and is used by the background worker to start the exporting process.
/// </summary>
private void DoExport()
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
using (DataAccess data = new DataAccess(ConfigurationManager.ConnectionStrings["datasource"].ConnectionString))
{
int count = 0;
int items = ckbxTableNameList.CheckedItems.Count;
// for each item in the checklist....
foreach (object table in ckbxTableNameList.CheckedItems)
{
// Update progress for progressbar...
int progress = (int)(((double)(count + 1) / (double)items) * 100);
string tableName = table.ToString();
using (SqlCommand cmd = data.GetCommand(Resources.select_table_schema_query))
{
// Add table name parameter
cmd.Parameters.AddWithValue("@TableName", tableName);
using (SqlDataAdapter reader = new SqlDataAdapter(cmd))
{
// Remove [] characters around the schema & table names
string fileName = Path.Combine(Program.ExportPath,
Regex.Replace(tableName, @"[\[\]]", string.Empty) + ".xls");
DataSet ds = new DataSet();
reader.Fill(ds);
ExportTableDataToExcel(ds.Tables[0], fileName, excel);
}
}
count++;
dataExtractWorker.ReportProgress(progress);
Thread.Sleep(100);
}
}
// Quit and release instance of MS Excel
excel.Quit();
NAR(excel);
}