We have a code snippet that downloads data to Excel. it is writing row by
row. This causes a performance issue. Any ideas on how to speed this up will
be appreciated.
Please find below an excerpt of the code:
#region Method:WriteToExcel
/// <summary>
/// <para>Description : Method is used to Write the records into
excel</para>
public static void WriteToExcel(string excelFilePath, DataSet
dsPrConditions)
{
try
{
XYZLogManager.WriteToLog("Inside WriteToExcel of
dsPrCondition",
LogCategory.Information, typeof(DSPCondition).FullName);
string connectionString =
"Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelFilePath + ";" +
"Extended Properties=\"Excel 8.0;MAXSCANROWS=1;HDR=YES;IMEX=2\"";
DbProviderFactory factory =
DbProviderFactories.GetFactory("System.Data.OleDb" );
using (DbConnection connection = factory.CreateConnection())
{
connection.ConnectionString = connectionString;
//CREATE SHEET IN THE EXCEL FILE
using (DbCommand CreateTableCommand =
connection.CreateCommand())
{
connection.Open();
CreateTableCommand.CommandText = CreateTableQuery();
CreateTableCommand.ExecuteNonQuery();
connection.Close();
}
//INSERT VALUES INTO THE NEWLY CREATED SHEET
using (DbCommand command = connection.CreateCommand())
{
CreateCommandParameters(command);
connection.Open();
command.CommandText = GenerateInsertQuery();
//Exclude Locked Records and write it to Excel
foreach (DataRow dr in
dsPrConditions.Tables[0].Select("CRITERIA_1 = 'N'"))
{
CreateInsertCommand(command, dr);
command.ExecuteNonQuery();
}
connection.Close();
}
//DROP THE BLANK SHEET FROM THE TEMPLATE
using (DbCommand DropTableCommand =
connection.CreateCommand())
{
connection.Open();
DropTableCommand.CommandText = "DROP TABLE [TEMP$]";
DropTableCommand.ExecuteNonQuery();
connection.Close();
}
}
}
catch (Exception ex)
{
XYZExceptionHandler.Process(ex,
ExceptionPolicies.WrapPolicy, Severity.Error,
"Error Code:DL0136, Description : Error writing values
to excel");
}
}
#endregion
--
fhillipo
