Bulk Import And Export

Gautam Parmar

8y1k

I have more than 90k Record in my table, I want to Import it to excel and give to client when they click download button from website, I have tried some code which takes too much time or more than 30 min.

I have tried to export record from directly SQL using Stored Procedure but it gives different errors which I have no idea what it is actually because I tried to search a lot.

Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

Basically, I want to know what is the best way to import and export from SQL to excel when data is more than 1lc Record.

I am Using C# Web API With Angular And Sql 2012

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sandy8y ago
HI Gautam ,
can you share the code of your storeProduceure that you are executing while inserting records in database.

That will help us to solve problem if you can like you can use Bulk Insertion or any thing other.

Thanks,
Sandy
Gautam Parmar8y ago
Hi Nilesh,

It has to be from code only
Gautam Parmar8y ago
Hi Sandy we are having 90k plus records and is not good at point of perfomance to loop 90k records while inserting,

I am looking for bulk insert.
Sandy8y ago
Hi Gautam,
Try below code
SqlConnection conn = new SqlConnection(prop.sqlConnString);
    string sqlQuery = "select * from TableName " ;
    SqlCommand command = new SqlCommand(sqlQuery, conn);
    conn.Open();

    SqlDataReader dr = command.ExecuteReader();
    DataTable dtSchema = dr.GetSchemaTable();
    StreamWriter sw = new StreamWriter(fileOut, false, this.encodingCSV);
    string strRow; // represents a full row
    if (this.chkFirstRowColumnNames.Checked)
    {
    sw.WriteLine(columnNames(dtSchema, this.separator));
    }

    while (dr.Read())
    {
    strRow = "";
    for (int i = 0; i < dr.FieldCount; i++)
    {
        strRow += dr.GetString(i);
        if (i < dr.FieldCount - 1)
        {
        strRow += this.separator;
        }
    }
    sw.WriteLine(strRow);
    }

    sw.Close();
    conn.Close();
if the issue not resolved than kindly share more infomation or your code which you write
Thanks
Sandy
Nilesh Patil8y ago
Hi,

Refer this link

https://solutioncenter.apexsql.com/how-to-import-and-export-sql-server-data-to-an-excel-file/