What is the fastest way to export dataGridView rows to Excel or into an SQL Server database

Question

What is the fastest way to export DataGridView rows in the range of 460328 - 800328 to Excel or into an SQL Server database table with out using Microsoft office interop as interop is quite slow and heavy on system resources?

Answer 1

For exporting to Excel, if you aren't using the XML based 2007 or 2010, Interop is pretty much the only way to go. It's not as bad as it's reputation though. I'll list a few solutions.

1 To Excel

First add a Microsoft.Office.Interop.Excel component reference to your project. This should be under the .NET tab in Project -> Add Reference. add the using statement to your form:

using Excel = Microsoft.Office.Interop.Excel;

add a button control, and add this code to it's body:

    private void btnExport_Click(object sender, EventArgs e)
    {

        Excel.Application app = new Excel.Application();
        app.Visible = true;
        Excel.Workbook wb = app.Workbooks.Add(1);
        Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets[1];
        // changing the name of active sheet
        ws.Name = "Exported from gridview";

        ws.Rows.HorizontalAlignment = HorizontalAlignment.Center;
        // storing header part in Excel
        for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
        {
            ws.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;
        }


        // storing Each row and column value to excel sheet
        for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
        {
            for (int j = 0; j < dataGridView1.Columns.Count; j++)
            {
                ws.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
            }
        }

        // sizing the columns
        ws.Cells.EntireColumn.AutoFit();

        // save the application
        wb.SaveAs("c:\\output.xls",Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive , Type.Missing, Type.Missing, Type.Missing, Type.Missing);

        // Exit from the application
       app.Quit();
    }
}

2 - to SQL Server

This requires no interop. For ease of use, pass your List object to the event executing the inserts. If you have tables set up to correspond to your grid view column, it's easy. Here, I use a sproc.

    private void btnToSQL_Click(object sender, EventArgs e)
    {
        string connStr = @"Data Source=(local)\sqlexpress;Initial Catalog=rTALIS;Integrated Security=True";
        var cn = new SqlConnection(connStr);
        var cm = new SqlCommand("exec usp_InsertRecord", cn);
        cm.CommandType = System.Data.CommandType.StoredProcedure;
        try
        {
            cn.Open();
            foreach (Row r in rows)
            {
                cm.Parameters.Clear();
                cm.Parameters.AddWithValue("@Number1", r.Number1);
                cm.Parameters.AddWithValue("@Number2", r.Number2);
                cm.Parameters.AddWithValue("@Number3", r.Number3);
                cm.Parameters.AddWithValue("@Number4", r.Number4);
                cm.Parameters.AddWithValue("@Number5", r.Number5);
                cm.Parameters.AddWithValue("@Number6", r.Number6);
                cm.Parameters.AddWithValue("@Number7", r.Number7);
                cm.Parameters.AddWithValue("@Date1", r.Date1);
                cm.ExecuteNonQuery();
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
        finally
        {
            cn.Close();
        }
    }

Let me know if I need to tweak this for you. In the original example, I had List rows = new List(); declared in the form_Load method. This worked for that solution, but it's scope is now too limited. I have moved it up/out into the class, so that in can be called anywhere on the form (specifically btnToSQL_Click). I have commented it out below:

    List<Row> rows = new List<Row>();

    private void Form1_Load(object sender, EventArgs e)
    {
        //var rows = new List<Row>();  //limited scope
        var sr = new StreamReader(@"C:\so_test.txt");
        while (!sr.EndOfStream)
        {
            string s = sr.ReadLine();
            if (!String.IsNullOrEmpty(s.Trim()))
            {
                rows.Add(new Row(s));
            }
        }
        sr.Close();
        dataGridView1.DataSource = rows;
    }