Friday 21 September 2012

Bulk Insert into SQL Server Using SqlBulkCopy In Asp.Net


Hi All, we are using excel sheet to store the data but nowdays we want to store the excel sheet data into database. therefore in asp.net there is SqlBulkCopy to store excel sheet data into table of database directly . Folllowing Are Step to BulkInsert In database through Excel Sheet.
Step 1 : Create Table in database
create table Test
(
id int,
name varchar(50),
address varchar(100),
CreatedBy varchar(100)
)

Step 2 : In Asp page add file uploader control and Upload button in aspx page to upload excel file. now add following code on upload button click
OleDbConnection conn = new OleDbConnection();
OleDbCommand cmd = new OleDbCommand();
OleDbDataAdapter da = new OleDbDataAdapter();
DataSet ds = new DataSet();
string strFileName = "Test";
string strFileType = Path.GetExtension(fuOptionDebit.FileName).ToString().ToLower();
string strNewPath = Server.MapPath("~/temp/" + strFileName);
if (strFileType == ".xls" || strFileType == ".xlsx")
 {
     fuOptionDebit.SaveAs(Server.MapPath("~/temp/" + strFileName + strFileType));
 }

//This Connection String to Read Excel Sheet

 if (strFileType.Trim() == ".xls")
 {
 connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strNewPath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
 }
 else if (strFileType.Trim() == ".xlsx")
 {
 connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strNewPath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
 }
 query = "SELECT * FROM [Sheet1$]";
 conn = new OleDbConnection(connString);
 try
 {
  if (conn.State == ConnectionState.Closed) conn.Open();
//Create the command object
  cmd = new OleDbCommand(query, conn);
  da = new OleDbDataAdapter(cmd);
  ds = new DataSet();
  da.Fill(ds);
  if (ds.Tables.Count > 0)
   {
     DataTable dt = ds.Tables[0];
     if (dt.Rows.Count > 0)
      {
string conString="server=ServerName;database=DatabaseName;uid=UserId;pwd=Password;Min Pool Size=10; Max Pool Size=100;Connect Timeout=50" providerName="System.Data.SqlClient";


BulkInsert(dt, table, conString);



Following method used for bulk insert into database but our excel sheet contain following column Id at 0 index Name at 1Index CreatedBY at 2index i told that briefly becoz when u upload the excel sheet it will map column of table with excel sheet column and then it insert the data to table of database.
 private void BulkInsert(DataTable dt, string table, string con)
    {

        // get your connection string
        string connString = con;

        // connect to SQL
        using (SqlConnection connection =
                new SqlConnection(connString))
        {
            // make sure to enable triggers
            // more on triggers in next post
            SqlBulkCopy bulkCopy =
                new SqlBulkCopy
                (
                connection,
                SqlBulkCopyOptions.TableLock |
                SqlBulkCopyOptions.FireTriggers |
                SqlBulkCopyOptions.UseInternalTransaction,
                null
                );

            // set the destination table name
            bulkCopy.DestinationTableName = table;
            connection.Open();
//COLUMN MAPPING BEFORE INSERTING
//first 0 for index of dataset and 2nd 0 for table column index which is in database
            bulkCopy.ColumnMappings.Add(0, 0);  
            bulkCopy.ColumnMappings.Add(1, 1);
            bulkCopy.ColumnMappings.Add(2, 3);
            
// write the data in the "dataTable"
            bulkCopy.WriteToServer(dt);
            connection.Close();
        }

    }

this is all about SqlBulkCopy used In asp.net to upload excel data into sql server.
Thank You Chetan Virkar.

3 comments:

  1. Hello Chetan V,
    I am happy to comment here. Thanks for offer solution on codeproject. This is really a good article. It is obviousely that you are almost an expert on database.

    Not long ago, I found a free dataexport component Spire.free DataExport, it can export data from database,listview,command to word,excel,pdf,access,sql scriptlsylk,csv,text,dbf clipboard etc. I personally I think it is really a good software. especially for data exporting. I want to suggest to you, and know what is your idea about it. Thanks!

    Please write it here!


    ReplyDelete
  2. Hi Mukesh,
    fuOptionDebit is ID if File Uploader Control where You Can Browse the file.
    Thanks
    Chetan V.

    ReplyDelete