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";
            }
        }
    }
}

 

Results 1 - 5 of 9 12
Posted on Oct 17, 2016 04:40 AM
Hi @smuthu,
Please try the following

Perform Insert Update after SqlBulkCopy Excel import is completed in C# and VB.Net

It might help you.

Cheers Andrea.

Posted on Oct 17, 2016 05:37 AM

sir ..,

i will try this article not working .

 

 i want one colunm add in sql server  but not add in escel sheet , i was saved  that colunm values  show excel sheet all rows this value save


Posted on Oct 17, 2016 07:57 AM

Hi smuthu,

Write more detailed explanation. Unless we understand we can't help.


Posted on Oct 18, 2016 12:10 AM

EXCEl :

1.Tran Date

2.VAlueDate

3.CHQNO

4.Transaction Particulars

5.Amount

6.DR|CR

7.Branch Name

SQL:

1.Tran Date

2.VAlueDate

3.CHQNO

4.Transaction Particulars

5.Amount

6.DR|CR

7.Branch Name

8.BankName

DESIGN:

<div>
    <table align="center">
        <tr>
            <td>
                BANKNAME
            </td>
            <td>
                <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td>
                UPLOAD
            </td>
            <td>
                <asp:FileUpload ID="FileUpload1" runat="server" />
            </td>
        </tr>
        <tr>
            <td>
            </td>
            <td>
            </td>
        </tr>
        <tr>
            <td>
            </td>
            <td>
                <asp:Button ID="upload" runat="server" Text="SUBMIT" OnClick="upload_Click" />
            </td>
        </tr>
    </table>
</div>

SOURCE:

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[7] { 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(string)),
            new DataColumn("DR|CR",typeof(string)),
            new DataColumn("Branch Name",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("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();
                SqlCommand cmd = new SqlCommand("insert into tbl_BankTransaction (BankName) values ('" + TextBox1.Text + "')", con);
                cmd.ExecuteNonQuery();
                sqlBulkCopy.WriteToServer(dtExcelData);
                con.Close();
            }
        }
    }
}

ACTUAL RESULT:

14.10.2016 14.10.2016  12354 NEFT  2500 DR  AXIS(KERALA) NULL

14.10.2016 14.10.2016  12354 NEFT  2500 DR  AXIS(KERALA) NULL

14.10.2016 14.10.2016  12354 NEFT  2500 DR  AXIS(KERALA) NULL

14.10.2016 14.10.2016  12354 NEFT  2500 DR  AXIS(KERALA) NULL

NULL         NULL           NULL NULL  NULL NULL   NULL         AXIS

EXCEPTING RESULT:

14.10.2016 14.10.2016  12354 NEFT  2500 DR  AXIS(KERALA) AXIS

14.10.2016 14.10.2016  12354 NEFT  2500 DR  AXIS(KERALA) AXIS

14.10.2016 14.10.2016  12354 NEFT  2500 DR  AXIS(KERALA) AXIS

14.10.2016 14.10.2016  12354 NEFT  2500 DR  AXIS(KERALA) AXIS

 

I WANT TO  ABOVE RESULT , WHAT TO DO HELP SIR ...


Posted on Oct 18, 2016 02:27 AM

Hi smuthu,

Will you please share the excel sheet data.