I'm currently trying to use OleDb/JET to export SQL Server data to an Excel 2003 file. I initially coded the export in a console application, and it worked fast enough. However, running the same code during an ASP.Net request takes roughly three times longer. Specifically, it's slowing down during a call to OleDbCommand.ExecuteQuery, which is inserting the record to the Excel file.
The only difference between the two is literally that one is running in a console app and the other is running in IIS. Other than that, both applications are:
- running on my development machine
- running the same code as below from a shared dll that both applications reference
- connecting to the same database with the same connection string
- using the same exact select statement with the same exact parameter values
- creating a brand new file in the same location on my development machine and writing to it
- generating byte-for-byte identical files
Is there something inherently slow when using OleDb in ASP.Net that I'm unaware of?
UPDATE: This is the code in question. The import command used in both the console application and ASP.Net sites are identical. They are both connecting to a database that is local to my machine, and both applications are running on my machine.
public void Convert(IDbCommand importCommand, string savePath, string sheetName)
{
var excelConnString = new OleDbConnectionStringBuilder();
excelConnString.Provider = "Microsoft.ACE.OLEDB.12.0";
excelConnString.DataSource = savePath;
excelConnString["Extended Properties"] = "Excel 8.0;HDR=Yes";
using (var dr = importCommand.ExecuteReader())
{
var columnCount = dr.FieldCount;
using (var oleConn = new OleDbConnection(excelConnString.ToString()))
{
oleConn.Open();
var headers = new string[columnCount];
var formattedHeaders = new List<string>();
var qs = new List<string>();
var insertCmd = oleConn.CreateCommand();
for (var curCol = 0; curCol < dr.FieldCount; curCol++)
{
var name = dr.GetName(curCol);
headers[curCol] = name;
formattedHeaders.Add("[" + name + "]");
qs.Add("?");
insertCmd.Parameters.Add(name, OleDbType.LongVarChar, 20000);
}
using (var cmd = oleConn.CreateCommand())
{
cmd.CommandText = string.Format("create table {0}({1})", sheetName,
string.Join(",", formattedHeaders.Select(x => x + " longtext")));
cmd.ExecuteNonQuery();
}
insertCmd.CommandText = string.Format("insert into {0} ({1}) values ({2})", sheetName, string.Join(",", formattedHeaders), string.Join(",", qs));
insertCmd.Prepare();
var values = new object[columnCount];
while (dr.Read())
{
dr.GetValues(values);
for (var i = 0; i < columnCount; i++)
{
insertCmd.Parameters[headers[i]].Value = values[i];
}
insertCmd.ExecuteNonQuery();
}
}
}
}
COMmethod with the same parameters to create and write to an Excel file? – Sokil