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

asrul
 
on Oct 01, 2015 02:53 AM
5492 Views

Hi Friends,

FYI, I was successfully done to transfer data from excel file to SQL server by using below link. Its work!!! Thanks bro.

http://www.aspsnippets.com/Articles/Using-SqlBulkCopy-to-import-Excel-SpreadSheet-data-into-SQL-Server-in-ASPNet-using-C-and-VBNet.aspx

But, 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.Date_Load

 

May I know, how to auto insert "Date_Load" on table tblPersons when user upload the data. 

I attach together with code that I try modified, but got error.

error msg = "The given ColumnName '2015-10-01 16:49:45' does not match up with any column in data source."

Thanks..I hope u got my point.

Protected Sub Upload(sender As Object, e As EventArgs) Handles Button1.Click
        'Upload and save the file
        Dim excelPath As String = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName)
        FileUpload1.SaveAs(excelPath)

        Dim connString As String = String.Empty
        Dim extension As String = Path.GetExtension(FileUpload1.PostedFile.FileName)
        Select Case extension
            Case ".xls"
                'Excel 97-03
                connString = ConfigurationManager.ConnectionStrings("Excel03ConStringload").ConnectionString
                Exit Select
            Case ".xlsx"
                'Excel 07 or higher
                connString = ConfigurationManager.ConnectionStrings("Excel07+ConStringload").ConnectionString
                Exit Select

        End Select
        connString = String.Format(connString, excelPath)
        Using excel_con As New OleDbConnection(connString)
            excel_con.Open()
            Dim sheet1 As String = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing).Rows(0)("TABLE_NAME").ToString()
            Dim dtExcelData As New DataTable()

            '[OPTIONAL]: It is recommended as otherwise the data will be considered as String by default.
            dtExcelData.Columns.AddRange(New DataColumn(2) {New DataColumn("Id", GetType(Integer)), _
                                                            New DataColumn("Name", GetType(String)), _
                                                            New DataColumn("Salary", GetType(Decimal))})

            Using oda As New OleDbDataAdapter((Convert.ToString("SELECT * FROM [") & sheet1) + "]", excel_con)
                oda.Fill(dtExcelData)
            End Using
            excel_con.Close()

            Dim conString As String = ConfigurationManager.ConnectionStrings("APPHRMS_CS").ConnectionString
            Using con As New SqlConnection(conString)
                Using sqlBulkCopy As New SqlBulkCopy(con)
                    'Set the database table name
                    sqlBulkCopy.DestinationTableName = "dbo.tblPersons"

                    '[OPTIONAL]: Map the Excel columns with that of the database table
                    sqlBulkCopy.ColumnMappings.Add("Id", "PersonId")
                    sqlBulkCopy.ColumnMappings.Add("Name", "Name")
                    sqlBulkCopy.ColumnMappings.Add("Salary", "Salary")
                    sqlBulkCopy.ColumnMappings.Add(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), "Date_Load")
                    con.Open()
                    sqlBulkCopy.WriteToServer(dtExcelData)
                    con.Close()
                End Using
            End Using
        End Using
    End Sub

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
Mudassar
 
on Oct 01, 2015 02:54 AM

You need to write a separate Update statement and execute it after the work done.

asrul
 
on Oct 01, 2015 03:08 AM

Hi Mudassar,

 

Thanks for your fast reply.

May I know which correct place should i put on my code?

It's OK if i put here?

Dim conString As String = ConfigurationManager.ConnectionStrings("APPHRMS_CS").ConnectionString
            Using con As New SqlConnection(conString)
                Using sqlBulkCopy As New SqlBulkCopy(con)
                    'Set the database table name
                    sqlBulkCopy.DestinationTableName = "dbo.tblPersons"

                    '[OPTIONAL]: Map the Excel columns with that of the database table
                    sqlBulkCopy.ColumnMappings.Add("Id", "PersonId")
                    sqlBulkCopy.ColumnMappings.Add("Name", "Name")
                    sqlBulkCopy.ColumnMappings.Add("Salary", "Salary")
                    'sqlBulkCopy.ColumnMappings.Add(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), "Date_Load")
                    con.Open()
                    sqlBulkCopy.WriteToServer(dtExcelData)
                    con.Close()
                End Using
                '****** Insert Date Load
                Call InsertDateLoad()
            End Using