Getting error creating Excel through C# : Transition into COM context 0x56b098 -
i trying create excel file through c# code , scenario have stored procedure returns 15000 records , reading data through sqldataadapter , data populated in datatable , filling data excel file after while application throws below error.
error:
transition com context 0x56b098 runtimecallablewrapper failed following error: system call failed. (exception hresult: 0x80010100 (rpc_e_sys_call_failed)). typically because com context 0x56b098 runtimecallablewrapper created has been disconnected or busy doing else. releasing interfaces current com context (com context 0x56af28). may cause corruption or data loss. avoid problem, please ensure com contexts/apartments/threads stay alive , available context transition, until application done runtimecallablewrappers represents com components live inside them.
below code using
public datatable getdata(string query,string year,string month) { sqldataadapter da = new sqldataadapter(); datatable dt = new datatable(); sqlcommand cmd = new sqlcommand(query,con); cmd.parameters.addwithvalue("@yr", year); cmd.parameters.addwithvalue("@mn", month); cmd.commandtype = commandtype.storedprocedure; cmd.commandtimeout = 0; da.selectcommand = cmd; da.fill(dt); return dt; } main method
private void btn_mrrretention_click(object sender, eventargs e) { // working creating mrr retention excel file datatable dt_mrr; string yr, mn; int tot_rows; yr = cmb_yr.selecteditem.tostring(); mn = cmb_mnth.selecteditem.tostring(); if (xlapp == null) { messagebox.show("excel not installed!"); return; } excel.workbook xlworkbook; excel.worksheet xlworksheet; object misvalue = system.reflection.missing.value; xlworkbook = xlapp.workbooks.add(misvalue); xlworksheet = (excel.worksheet)xlworkbook.worksheets.get_item(1); // data reader code start collecting data db , pasting excel picturebox2.visible = true; dt_mrr = func.getdata("sp_mrr_retention_apac", yr, mn); //string text = "exec sp_mrr_retention" + "'" + yr + "','" + mn + "'"; tot_rows = dt_mrr.rows.count; int row = 2; int i=0; xlworksheet.cells[1, 1] = "month"; xlworksheet.cells[1, 2] = "parent name"; xlworksheet.cells[1, 3] = "customer_name"; xlworksheet.cells[1, 4] = "customer_account_no"; xlworksheet.cells[1, 5] = "item_category"; xlworksheet.cells[1, 6] = "item_description_summary"; xlworksheet.cells[1, 7] = "item_number"; xlworksheet.cells[1, 8] = "date_range"; xlworksheet.cells[1, 9] = "activity_type"; xlworksheet.cells[1, 10] = "line_type"; xlworksheet.cells[1, 11] = "ibx_code"; xlworksheet.cells[1, 12] = "ibx_country"; xlworksheet.cells[1, 13] = "ibx_region"; xlworksheet.cells[1, 14] = "primary_sales_rep"; xlworksheet.cells[1, 15] = "mrc_amount_usd_budget_rate"; xlworksheet.cells[1, 16] = "entered_currency_code"; xlworksheet.cells[1, 17] = "mrc_amount_lc"; xlworksheet.cells[1, 18] = "ucm id"; xlworksheet.cells[1, 19] = "gam_tag"; xlworksheet.cells[1, 20] = "client services manager"; xlworksheet.cells[1, 21] = "sales program type"; xlworksheet.cells[1, 22] = "sfdc account id"; xlworksheet.cells[1, 23] = "account owner"; //rs = func.getreader("sp_mrr_retention '" + yr + "','" + mn + "'"); while (tot_rows>i) { xlworksheet.cells[row, 1] = dt_mrr.rows[i]["month"]; xlworksheet.cells[row, 2] = dt_mrr.rows[i]["parent name"]; xlworksheet.cells[row, 3] = dt_mrr.rows[i]["customer_name"]; xlworksheet.cells[row, 4] = dt_mrr.rows[i]["customer_account_no"]; xlworksheet.cells[row, 5] = dt_mrr.rows[i]["item_category"]; xlworksheet.cells[row, 6] = dt_mrr.rows[i]["item_description_summary"]; xlworksheet.cells[row, 7] = dt_mrr.rows[i]["item_number"]; xlworksheet.cells[row, 8] = dt_mrr.rows[i]["date_range"]; xlworksheet.cells[row, 9] = dt_mrr.rows[i]["activity_type"]; xlworksheet.cells[row, 10] = dt_mrr.rows[i]["line_type"]; xlworksheet.cells[row, 11] = dt_mrr.rows[i]["ibx_code"]; xlworksheet.cells[row, 12] = dt_mrr.rows[i]["ibx_country"]; xlworksheet.cells[row, 13] = dt_mrr.rows[i]["ibx_region"]; xlworksheet.cells[row, 14] = dt_mrr.rows[i]["primary_sales_rep"]; xlworksheet.cells[row, 15] = dt_mrr.rows[i]["mrc_amount_usd_budget_rate"]; xlworksheet.cells[row, 16] = dt_mrr.rows[i]["entered_currency_code"]; xlworksheet.cells[row, 17] = dt_mrr.rows[i]["mrc_amount_lc"]; xlworksheet.cells[row, 18] = dt_mrr.rows[i]["ucm id"]; xlworksheet.cells[row, 19] = dt_mrr.rows[i]["gam_tag"]; xlworksheet.cells[row, 20] = dt_mrr.rows[i]["client services manager"]; xlworksheet.cells[row, 21] = dt_mrr.rows[i]["sales program type"]; xlworksheet.cells[row, 22] = dt_mrr.rows[i]["sfdc account id"]; xlworksheet.cells[row, 23] = dt_mrr.rows[i]["account owner"]; row++; i++; //for checking purpose! //if (i == 1000) //{ // break; //} } // data reader code ends here xlworkbook.saveas("d:\\mrr_retention_auto.xls", excel.xlfileformat.xlworkbooknormal, misvalue, misvalue, misvalue, misvalue, excel.xlsaveasaccessmode.xlexclusive, misvalue, misvalue, misvalue, misvalue, misvalue); xlworkbook.close(true, misvalue, misvalue); xlapp.quit(); releaseobject(xlworksheet); releaseobject(xlworkbook); releaseobject(xlapp); picturebox2.visible = false; messagebox.show("excel file created , can find file d:\\mrr_retention_auto.xls"); } i need on problem , looking forward it.
it seems me might gain lot of efficiencies dropping datatable completely. datatables great, have overhead, , wonder if overhead of populating 15,000 rows in datatable impacting com communication.
here solution avoids datatable. presupposes stored procedure dump columns in same order want see them in excel.
first, have getdata method return sqlcommand object instead of datatable:
public sqlcommand getdata(string query, string year, string month) { sqlcommand cmd = new sqlcommand(query, con); cmd.parameters.addwithvalue("@yr", year); cmd.parameters.addwithvalue("@mn", month); cmd.commandtype = commandtype.storedprocedure; cmd.commandtimeout = 0; return cmd; } from here, function calls , output excel can this:
sqlcommand cmd = func.getdata("sp_mrr_retention_apac", yr, mn); sqldatareader reader = cmd.executereader(); (int col = 0; col < reader.fieldcount; col++) xlworksheet.cells[col + 1, 1].value2 = reader.getname(col); while (reader.read()) { (int col = 0; col < reader.fieldcount; col++) if (!reader.isdbnull(col)) xlworksheet.cells[row, col + 1] = reader.getvalue(col); row++; } reader.close(); you can wrap in single method excel inputs well:
public void getdata(string query, string year, string month, excel.worksheet ws) { sqlcommand cmd = new sqlcommand(query, con); cmd.parameters.addwithvalue("@yr", year); cmd.parameters.addwithvalue("@mn", month); cmd.commandtype = commandtype.storedprocedure; cmd.commandtimeout = 0; int row = 1; sqldatareader reader = cmd.executereader(); (int col = 0; col < reader.fieldcount; col++) ws.cells[col + 1, 1].value2 = reader.getname(col); while (reader.read()) { (int col = 0; col < reader.fieldcount; col++) if (!reader.isdbnull(col)) ws.cells[row, col + 1] = reader.getvalue(col); row++; } reader.close(); } which simplify of code in main method to:
func.getdata("sp_mrr_retention_apac", yr, mn, xlworksheet); here's real kicker... ms query, built excel, of you. use odbc, ms sql server can hit server directly without odbc -- perk of microsoft-to-microsoft cohabitation. honestly, i've never tried stored proc, query work splendidly. have no reason doubt ms query function procedure.
the ms query call within c# this:
excel.listobject lo = sheet.listobjects.addex(excel.xllistobjectsourcetype.xlsrcquery, connectionstring, true, excel.xlyesnoguess.xlguess, range); lo.querytable.commandtext = querytext; lo.refresh(); and find it's extremely fast -- dare rival can write hand.
Comments
Post a Comment