Perform Insert Update after SqlBulkCopy Excel import is completed using C#

Last Reply on Oct 18, 2016 04:58 AM By dharmendr

Posted on Oct 17, 2016 04:40 AM

Now i want to insert "Date_Load" on my table once the transaction was completed.

Condition:

On Excel Column :

1.Id

2.Name

3.Salary

On Table tblPersons :

1.PersonId

2.Name

3.Salary

4.BankName

protected void Submit_Click1(object sender, EventArgs e)
{
    //Upload and save the file
    string excelPath = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName);
    FileUpload1.SaveAs(excelPath);

    string conString = string.Empty;
    string extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
    switch (extension)
    {
        case ".xls": //Excel 97-03
            conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
            break;
        case ".xlsx": //Excel 07 or higher
            conString = ConfigurationManager.ConnectionStrings["Excel07+ConString"].ConnectionString;
            break;

    }
    conString = string.Format(conString, excelPath);
    using (OleDbConnection excel_con = new OleDbConnection(conString))
    {
        excel_con.Open();
        string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
        DataTable dtExcelData = new DataTable();

        //[OPTIONAL]: It is recommended as otherwise the data will be considered as String by default.
        dtExcelData.Columns.AddRange(new DataColumn[7] {new DataColumn("TranDate", typeof(DateTime)),
        new DataColumn("ValueDate",typeof(DateTime)),
        new DataColumn("ChekNo", typeof(string)),
        new DataColumn("Remark", typeof(string)),
        new DataColumn("Amount", typeof(decimal)),
        new DataColumn("Type", typeof(string)),
        new DataColumn("BranchName", typeof(string)),});

        using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + sheet1 + "]", excel_con))
        {
            oda.Fill(dtExcelData);
        }
        excel_con.Close();

        string consString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (SqlConnection con = new SqlConnection(consString))
        {
            using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
            {
                //Set the database table name
                sqlBulkCopy.DestinationTableName = "dbo.tbl_BankTransaction";

                //[OPTIONAL]: Map the Excel columns with that of the database table

                sqlBulkCopy.ColumnMappings.Add("BankName", ddlCategory.Text).ToString();
                sqlBulkCopy.ColumnMappings.Add("Tran Date", "TranDate");
                sqlBulkCopy.ColumnMappings.Add("Value Date", "ValueDate");
                sqlBulkCopy.ColumnMappings.Add("CHQNO", "ChekNo");
                sqlBulkCopy.ColumnMappings.Add("Transaction Particulars", "Remark");
                sqlBulkCopy.ColumnMappings.Add("Amount", "Amount");
                sqlBulkCopy.ColumnMappings.Add("DR|CR", "Type");
                sqlBulkCopy.ColumnMappings.Add("Branch Name", "BranchName");
                con.Open();
                sqlBulkCopy.WriteToServer(dtExcelData);

                con.Close();
                Label1.Visible = false;
                Label1.Text = " DETAILS ARE UPDATED SUCCESSFULLY";
            }
        }
    }
}

 

Posted on Oct 18, 2016 04:17 AM Modified on one year ago

Hi smuthu,

I have modified your code. Refer the below code.

protected void upload_Click(object sender, EventArgs e)
{
    //Upload and save the file
    string excelPath = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName);
    FileUpload1.SaveAs(excelPath);
    string conString = string.Empty;
    string extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
    switch (extension)
    {
        case ".xls": //Excel 97-03
            conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
            break;
        case ".xlsx": //Excel 07 or higher
            conString = ConfigurationManager.ConnectionStrings["Excel07+ConString"].ConnectionString;
            break;

    }
    conString = string.Format(conString, excelPath);
    using (OleDbConnection excel_con = new OleDbConnection(conString))
    {
        excel_con.Open();
        string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
        DataTable dtExcelData = new DataTable();

        //[OPTIONAL]: It is recommended as otherwise the data will be considered as String by default.
        dtExcelData.Columns.AddRange(new DataColumn[8]
        { 
            new DataColumn("Tran Date", typeof(DateTime)),
            new DataColumn("Value Date", typeof(DateTime)),
            new DataColumn("CHQNO",typeof(string)),
            new DataColumn("Transaction Particulars",typeof(string)),
            new DataColumn("Amount",typeof(decimal)),
            new DataColumn("DR|CR",typeof(string)),
            new DataColumn("Branch Name",typeof(string)),
            new DataColumn("Bank Name",typeof(string))
        });

        dtExcelData.Columns["Bank Name"].DefaultValue = TextBox1.Text.Trim();
        using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + sheet1 + "]", excel_con))
        {
            oda.Fill(dtExcelData);

        }
        excel_con.Close();

        string consString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (SqlConnection con = new SqlConnection(consString))
        {
            using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
            {
                //Set the database table name
                sqlBulkCopy.DestinationTableName = "dbo.tbl_BankTransaction";
                //[OPTIONAL]: Map the Excel columns with that of the database table
                sqlBulkCopy.ColumnMappings.Add("Tran Date", "TranDate");
                sqlBulkCopy.ColumnMappings.Add("Value Date", "ValueDate");
                sqlBulkCopy.ColumnMappings.Add("CHQNO", "ChekNo");
                sqlBulkCopy.ColumnMappings.Add("Transaction Particulars", "Remark");
                sqlBulkCopy.ColumnMappings.Add("Amount", "Amount");
                sqlBulkCopy.ColumnMappings.Add("DR|CR", "Type");
                sqlBulkCopy.ColumnMappings.Add("Branch Name", "BranchName");
                sqlBulkCopy.ColumnMappings.Add("Bank Name", "BankName");
                con.Open();
                sqlBulkCopy.WriteToServer(dtExcelData);
                con.Close();
            }
        }
    }
}

Screenshot