Getting error creating Excel through C# : Transition into COM context 0x56b098

I am trying to create an Excel file through C# code and scenario is that I have a stored procedure which returns 15000 records and I am reading the data through a SqlDataAdapter and data is then populated in the DataTable and then I am filling the data into excel file but after a while the application throws below error.

Error:

Transition into COM context 0x56b098 for this RuntimeCallableWrapper failed with the following error: System call failed. (Exception from HRESULT: 0x80010100 (RPC_E_SYS_CALL_FAILED)). This is typically because the COM context 0x56b098 where this RuntimeCallableWrapper was created has been disconnected or it is busy doing something else. Releasing the interfaces from the current COM context (COM context 0x56af28). This may cause corruption or data loss. To avoid this problem, please ensure that all COM contexts/apartments/threads stay alive and are available for context transition, until the application is completely done with the RuntimeCallableWrappers that represents COM components that live inside them.

Below is the code I am 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 for 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 is not properly 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 for collecting data from DB and pasting into 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 , you can find the file D:\\MRR_Retention_Auto.xls");

    }

I need help on this problem and looking forward to it.

It seems to me you might gain a lot of efficiencies by dropping the datatable completely. Datatables are great, but they do have overhead, and I wonder if the overhead of populating 15,000 rows in the datatable is impacting your COM communication.

Here is a solution that avoids a datatable. It presupposes the stored procedure will dump the columns in the same order you want to see them in Excel.

First, have your getData method return a SqlCommand Object instead of a 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, your function calls and output to Excel can simply look like this:

SqlCommand cmd = func.getData("sp_MRR_Retention_APAC", Yr, mn);

SqlDataReader reader = cmd.ExecuteReader();
for (int col = 0; col < reader.FieldCount; col++)
    xlWorkSheet.Cells[col + 1, 1].Value2 = reader.GetName(col);

while (reader.Read())
{
    for (int col = 0; col < reader.FieldCount; col++)
        if (!reader.IsDBNull(col))
            xlWorkSheet.Cells[row, col + 1] = reader.GetValue(col);
    row++;
}

reader.Close();

You can even wrap that in a single method to do the Excel inputs as 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();
    for (int col = 0; col < reader.FieldCount; col++)
        Ws.Cells[col + 1, 1].Value2 = reader.GetName(col);

    while (reader.Read())
    {
        for (int col = 0; col < reader.FieldCount; col++)
            if (!reader.IsDBNull(col))
                Ws.Cells[row, col + 1] = reader.GetValue(col);
        row++;
    }

    reader.Close();
}

Which would simplify all of that code in your main method to:

func.getData("sp_MRR_Retention_APAC", Yr, mn, xlWorkSheet);

Here's the real kicker... MS Query, which is built into Excel, actually does all of this for you. Normally you use ODBC, but with MS SQL Server you can hit the server directly without ODBC -- a perk of Microsoft-to-Microsoft cohabitation. Honestly, I've never tried it with a stored proc, but with a query it would work splendidly. I have no reason to doubt MS Query will function with a procedure.

The MS Query call within C# would look something like this:

excel.ListObject lo = sheet.ListObjects.AddEx(excel.XlListObjectSourceType.xlSrcQuery,
    connectionString, true, Excel.XlYesNoGuess.xlGuess, range);
lo.QueryTable.CommandText = queryText;
lo.Refresh();

And you will find it's extremely fast -- I dare say it would rival anything you can write by hand.