I am currently developing a console application with C# and Entity Framework 5, where I insert data from Excel into database (SQL Server) then process it in the database (actually in a stored procedure) then update the excel file again. The problem I encountered was that it took a long time to insert ... but this has been resolved using SQLBulk.
The next problem is that it takes a lot longer to write (update) a column (actually 3 columns). where the value of the column comes from a database query. The number of rows generated from the query is 16000 rows.
Here is my code:
private static void writetoExcel2(T_UploadEvent objParam, DateTime dtstartingTime)
{
try
{
M_PriceMapper m_PriceMapper = new M_PriceMapper();
Application excelApp = new Application();
if (excelApp == null)
{
Console.WriteLine("Excel is not installed!!");
return;
}
Workbook excelBook = excelApp.Workbooks.Open(@objParam.FilePath);
_Worksheet excelSheet = excelBook.Sheets[1];
Range excelRange = excelSheet.UsedRange;
Range excelRangeRow = excelSheet.UsedRange.Rows;
int rows = excelRange.Rows.Count;
int cols = excelRange.Columns.Count;
List<T_HeaderOrder> t_HeaderOrders = new List<T_HeaderOrder>();
T_HeaderOrderMapper t_HeaderOrderMapper = new T_HeaderOrderMapper();
t_HeaderOrderLists = t_HeaderOrderMapper.getListData(objParam, "1");
T_HeaderOrder objOL = new T_HeaderOrder();
if (t_HeaderOrderLists != null)
{
for (int y = 2; y <= rows; y++)
{
for (int x = 1; x <= cols; x++)
{
try
{
switch (excelRange.Cells[1, x].value2)
{
case "UNIQCODE":
for (int c = 0; c <= t_HeaderOrderLists.Count - 1; c++)
{
T_HeaderOrder obj = t_HeaderOrderLists[c];
if (string.IsNullOrEmpty(obj.ErrorMessage))
{
M_Price m_Price = new M_Price();
m_Price = m_PriceMapper.getData(obj.MasterPriceID);
if (m_Price.UniqCode == excelRange.Cells[y, x].value2.ToString())
{
objOL = obj;
c = t_HeaderOrderLists.Count;
Range ora1 = excelSheet.Rows.Cells[y, cols + 1];
ora1.Value2 = objOL.NewPrice;
Range ora2 = excelSheet.Rows.Cells[y, cols + 2];
ora2.Value2 = objOL.Manual_VS_MPrise.ToUpper();
if (objOL.Manual_VS_MPrise == "false")
{
Range rowFormat = excelSheet.Rows[y];
rowFormat.Interior.Color = System.Drawing.Color.Red;
rowFormat.Font.Color = System.Drawing.Color.White;
}
Range ora3 = excelSheet.Rows.Cells[y, 33];
ora3.Value2 = objOL.SourcePrice;
}
}
else
{
Range msgERR = excelSheet.Rows.Cells[y, cols + 4];
msgERR.Value2 = objOL.ErrorMessage.ToUpper();
}
}
break;
}
}
catch (Exception eb)
{
Console.WriteLine("Error while write to excel, Row number : " + y);
Range msgERR = excelSheet.Rows.Cells[y, cols + 4];
msgERR.Value2 = eb.Message;
}
}
Console.WriteLine("Updateing row : " + y);
}
Range oraHEADER = excelSheet.Rows.Cells[1, cols + 1];
oraHEADER.Value2 = "New PRICE";
Range oravs = excelSheet.Rows.Cells[1, cols + 2];
oravs.Value2 = "PRICE Comparasion";
Range orasOURCE = excelSheet.Rows.Cells[1, cols + 3];
orasOURCE.Value2 = "SOURCE PRICE";
Range oramsgerr = excelSheet.Rows.Cells[1, cols + 4];
orasOURCE.Value2 = "Error Message";
}
excelApp.ActiveWorkbook.Save();
excelApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
}
catch (Exception ex)
{
Console.WriteLine("Error While Sending Emails");
}
}
public List<T_HeaderOrder> getListData(T_UploadEvent objParam, string param)
{
try
{
List<T_HeaderOrder> t_HeaderOrders= new List<T_HeaderOrder>();
using (Entities entities = new Entities ())
{
var idParam = new SqlParameter
{
ParameterName = "T_UploadEventID",
Value = objParam.ID
};
var list = entities.Database.SqlQuery<T_HeaderOrder>
("exec T_HeaderOrder_Comparasion @T_UploadEventID ", idParam).ToList<T_HeaderOrder>();
t_HeaderOrders = list;
}
return t_HeaderOrders;
}
catch (DbEntityValidationException e)
{
foreach (var eve in e.EntityValidationErrors)
{
Console.WriteLine("Entity of type \"{0}\" in state \"{1}\" has the following validation errors:",
eve.Entry.Entity.GetType().Name, eve.Entry.State);
foreach (var ve in eve.ValidationErrors)
{
Console.WriteLine("- Property: \"{0}\", Error: \"{1}\"",
ve.PropertyName, ve.ErrorMessage);
}
}
throw;
return new List<T_HeaderOrder>();
}
}
Thanks for your help
