R
RSH
Hi,
I have a situation where I have created a little application that makes an
Access database from a SQL Database for reporting purposes. it does the job
well, but it seems a bit slow. Is there anything that i can do to speed the
app up a bit?
namespace SQLToAccessBackup
{
class SqlToAccessConvertor
{
// RSH 3/23/2006
// This module creates an Access database from a SQL database
// It requires blank database (will not be destroyed) that has the
// same schema as it's SQL counterpart
// The word 'Blank' should be added to the Access Template databases name
Example: GlobalBlank.mdb
public SqlConnection cnSQL;
public OleDbConnection cnAccess;
public String strError = "";
public long lCounter;
public void RunDataInsert(String SourceServer, String Database, String
Template, String DataPath)
{
int iTotalRecords = 0;
String strDatabasePath = "";
String strSQL = "";
String dStartTime = "";
String dFinishTime = "";
dStartTime = DateTime.Now.Month + "/" + DateTime.Now.Day + "/" +
DateTime.Now.Year + " " + DateTime.Now.Hour + ":" + DateTime.Now.Minute;
Console.WriteLine("Process Begun : " + dStartTime);
if (File.Exists(DataPath + Database + ".mdb"))
{
File.Copy(DataPath + Database + ".mdb", DataPath + Database + "-BAK-" +
DateTime.Now.Month + DateTime.Now.Day + DateTime.Now.Year +
DateTime.Now.Hour + DateTime.Now.Minute + ".mdb");
File.Delete(DataPath + Database + ".mdb");
}
File.Copy(DataPath + Template + ".mdb", DataPath + Database + ".mdb");
strDatabasePath = DataPath + Database + ".mdb";
cnAccess = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" + "Data
Source=" + strDatabasePath + ";Persist Security Info=False");
cnAccess.Open();
cnSQL = new SqlConnection("Data Source=" + SourceServer + "; Integrated
Security=SSPI; Initial Catalog=" + Database);
cnSQL.Open();
// START LOOP OF ALL SQL TABLES HERE
strSQL = "USE [" + Database + "]";
SqlCommand myCommand = new SqlCommand(strSQL, cnSQL);
myCommand.ExecuteNonQuery();
strSQL = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES";
SqlDataAdapter daSQLTables = new SqlDataAdapter(strSQL, cnSQL);
DataSet dsSQLTables = new DataSet();
daSQLTables.Fill(dsSQLTables);
int iTotalTables = dsSQLTables.Tables[0].Rows.Count;
int iCurTableIdx = 0;
lCounter = 0;
string strCurTable = "";
Console.WriteLine("Processing Table: " + strCurTable + " Table " +
(iCurTableIdx + 1) + " of " + dsSQLTables.Tables[0].Rows.Count.ToString());
foreach (DataRow drSQLTables in dsSQLTables.Tables[0].Rows)
{
strCurTable = drSQLTables[0].ToString();
iCurTableIdx++;
// ACCESS
String strAccessSQL = "SELECT * FROM " + strCurTable;
OleDbDataAdapter daAccess = new OleDbDataAdapter(strAccessSQL, cnAccess);
DataSet dsAccess = new DataSet();
daAccess.Fill(dsAccess, "DT");
OleDbCommandBuilder cbDevelopment = new OleDbCommandBuilder(daAccess);
cbDevelopment.QuotePrefix = "[";
cbDevelopment.QuoteSuffix = "]";
// SQL
String strSQLServer = "SELECT * FROM [" + strCurTable + "] WITH (NOLOCK)";
SqlDataAdapter daSQLServer = new SqlDataAdapter(strSQLServer, cnSQL);
DataSet dsSQLServer = new DataSet();
daSQLServer.Fill(dsSQLServer);
int j = 1;
int maxRecords = dsSQLServer.Tables[0].Rows.Count;
foreach (DataRow dr in dsSQLServer.Tables[0].Rows)
{
DataRow drAccessAdd;
drAccessAdd = dsAccess.Tables["DT"].NewRow();
j++;
if (lCounter == 1000)
{
Console.WriteLine("Processing Table: " + strCurTable + "(" + (iCurTableIdx)
+ ")" + " Row " + (j - 2) + " of " + maxRecords);
lCounter = 0;
}
lCounter++;
for (Int16 i = 0; i < dr.ItemArray.Length; i++)
{
iTotalRecords++;
if (dsSQLServer.Tables[0].Columns.ColumnName.ToString() != "ID")
{
drAccessAdd[dsSQLServer.Tables[0].Columns.ColumnName.ToString()] =
dr[dsSQLServer.Tables[0].Columns.ColumnName.ToString()];
}
}
dsAccess.Tables["DT"].Rows.Add(drAccessAdd);
if (drAccessAdd.HasErrors == false)
{
daAccess.Update(dsAccess, "DT");
}
else
{
// Write to error log
strError = drAccessAdd.RowError.ToString() + "\\n";
}
}
}
dFinishTime = DateTime.Now.Month + "/" + DateTime.Now.Day + "/" +
DateTime.Now.Year + " " + DateTime.Now.Hour + ":" + DateTime.Now.Minute;
MailMessage objMsg = new MailMessage();
objMsg.To = "(e-mail address removed)";
objMsg.From = "(e-mail address removed)";
objMsg.Subject = DateTime.Now.Month + DateTime.Now.Day + DateTime.Now.Year +
" -- Sql to Access backup conversion job results.";
objMsg.Body = "The SQL to Access conversion job scheduled to run at: " +
dStartTime + " ran to completion at: " + dFinishTime + ".\n\n" +
String.Format("{0:###,###,###,###,###}", iTotalRecords - 1) + " Records were
written to: " + DataPath + Database + ".mdb \n\n The following errors
occurred during processing:\n" + strError;
SmtpMail.SmtpServer = "SMTPSERVER1";
SmtpMail.Send(objMsg);
cnAccess.Close();
cnAccess.Dispose();
cnSQL.Close();
cnSQL.Dispose();
if (strError.Length > 1)
{
String strFilename = AppDomain.CurrentDomain.BaseDirectory +
"DataImportErrorLog-" + ".txt";
TextWriter tw = new StreamWriter(strFilename);
tw.WriteLine(strError);
tw.Close();
}
}
}
}
Thanks for any help you might be able to offer!
RSH
I have a situation where I have created a little application that makes an
Access database from a SQL Database for reporting purposes. it does the job
well, but it seems a bit slow. Is there anything that i can do to speed the
app up a bit?
namespace SQLToAccessBackup
{
class SqlToAccessConvertor
{
// RSH 3/23/2006
// This module creates an Access database from a SQL database
// It requires blank database (will not be destroyed) that has the
// same schema as it's SQL counterpart
// The word 'Blank' should be added to the Access Template databases name
Example: GlobalBlank.mdb
public SqlConnection cnSQL;
public OleDbConnection cnAccess;
public String strError = "";
public long lCounter;
public void RunDataInsert(String SourceServer, String Database, String
Template, String DataPath)
{
int iTotalRecords = 0;
String strDatabasePath = "";
String strSQL = "";
String dStartTime = "";
String dFinishTime = "";
dStartTime = DateTime.Now.Month + "/" + DateTime.Now.Day + "/" +
DateTime.Now.Year + " " + DateTime.Now.Hour + ":" + DateTime.Now.Minute;
Console.WriteLine("Process Begun : " + dStartTime);
if (File.Exists(DataPath + Database + ".mdb"))
{
File.Copy(DataPath + Database + ".mdb", DataPath + Database + "-BAK-" +
DateTime.Now.Month + DateTime.Now.Day + DateTime.Now.Year +
DateTime.Now.Hour + DateTime.Now.Minute + ".mdb");
File.Delete(DataPath + Database + ".mdb");
}
File.Copy(DataPath + Template + ".mdb", DataPath + Database + ".mdb");
strDatabasePath = DataPath + Database + ".mdb";
cnAccess = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" + "Data
Source=" + strDatabasePath + ";Persist Security Info=False");
cnAccess.Open();
cnSQL = new SqlConnection("Data Source=" + SourceServer + "; Integrated
Security=SSPI; Initial Catalog=" + Database);
cnSQL.Open();
// START LOOP OF ALL SQL TABLES HERE
strSQL = "USE [" + Database + "]";
SqlCommand myCommand = new SqlCommand(strSQL, cnSQL);
myCommand.ExecuteNonQuery();
strSQL = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES";
SqlDataAdapter daSQLTables = new SqlDataAdapter(strSQL, cnSQL);
DataSet dsSQLTables = new DataSet();
daSQLTables.Fill(dsSQLTables);
int iTotalTables = dsSQLTables.Tables[0].Rows.Count;
int iCurTableIdx = 0;
lCounter = 0;
string strCurTable = "";
Console.WriteLine("Processing Table: " + strCurTable + " Table " +
(iCurTableIdx + 1) + " of " + dsSQLTables.Tables[0].Rows.Count.ToString());
foreach (DataRow drSQLTables in dsSQLTables.Tables[0].Rows)
{
strCurTable = drSQLTables[0].ToString();
iCurTableIdx++;
// ACCESS
String strAccessSQL = "SELECT * FROM " + strCurTable;
OleDbDataAdapter daAccess = new OleDbDataAdapter(strAccessSQL, cnAccess);
DataSet dsAccess = new DataSet();
daAccess.Fill(dsAccess, "DT");
OleDbCommandBuilder cbDevelopment = new OleDbCommandBuilder(daAccess);
cbDevelopment.QuotePrefix = "[";
cbDevelopment.QuoteSuffix = "]";
// SQL
String strSQLServer = "SELECT * FROM [" + strCurTable + "] WITH (NOLOCK)";
SqlDataAdapter daSQLServer = new SqlDataAdapter(strSQLServer, cnSQL);
DataSet dsSQLServer = new DataSet();
daSQLServer.Fill(dsSQLServer);
int j = 1;
int maxRecords = dsSQLServer.Tables[0].Rows.Count;
foreach (DataRow dr in dsSQLServer.Tables[0].Rows)
{
DataRow drAccessAdd;
drAccessAdd = dsAccess.Tables["DT"].NewRow();
j++;
if (lCounter == 1000)
{
Console.WriteLine("Processing Table: " + strCurTable + "(" + (iCurTableIdx)
+ ")" + " Row " + (j - 2) + " of " + maxRecords);
lCounter = 0;
}
lCounter++;
for (Int16 i = 0; i < dr.ItemArray.Length; i++)
{
iTotalRecords++;
if (dsSQLServer.Tables[0].Columns.ColumnName.ToString() != "ID")
{
drAccessAdd[dsSQLServer.Tables[0].Columns.ColumnName.ToString()] =
dr[dsSQLServer.Tables[0].Columns.ColumnName.ToString()];
}
}
dsAccess.Tables["DT"].Rows.Add(drAccessAdd);
if (drAccessAdd.HasErrors == false)
{
daAccess.Update(dsAccess, "DT");
}
else
{
// Write to error log
strError = drAccessAdd.RowError.ToString() + "\\n";
}
}
}
dFinishTime = DateTime.Now.Month + "/" + DateTime.Now.Day + "/" +
DateTime.Now.Year + " " + DateTime.Now.Hour + ":" + DateTime.Now.Minute;
MailMessage objMsg = new MailMessage();
objMsg.To = "(e-mail address removed)";
objMsg.From = "(e-mail address removed)";
objMsg.Subject = DateTime.Now.Month + DateTime.Now.Day + DateTime.Now.Year +
" -- Sql to Access backup conversion job results.";
objMsg.Body = "The SQL to Access conversion job scheduled to run at: " +
dStartTime + " ran to completion at: " + dFinishTime + ".\n\n" +
String.Format("{0:###,###,###,###,###}", iTotalRecords - 1) + " Records were
written to: " + DataPath + Database + ".mdb \n\n The following errors
occurred during processing:\n" + strError;
SmtpMail.SmtpServer = "SMTPSERVER1";
SmtpMail.Send(objMsg);
cnAccess.Close();
cnAccess.Dispose();
cnSQL.Close();
cnSQL.Dispose();
if (strError.Length > 1)
{
String strFilename = AppDomain.CurrentDomain.BaseDirectory +
"DataImportErrorLog-" + ".txt";
TextWriter tw = new StreamWriter(strFilename);
tw.WriteLine(strError);
tw.Close();
}
}
}
}
Thanks for any help you might be able to offer!
RSH