In the last post I discuss how we insert bulk data by using SQL Server you can find
here but there is an issue with that approach it only if your comma separated file exist on that SQL Server machine.
Let suppose I have a requirement in which user upload a comma separated text file from website and I need to insert that file in to SQL table what I am facing right now my IIS Server and my Database server both are separate machine so in this case my previous post was not useful in this case .
I have a same file format which is
Coin_ID | Customer_ID | Coin_Address | BitCoins | Status_ID | Is_Active | Created_By | Updated_By | Updated_Date | Transaction_ID | Transfer_Type7635 | 209285 | 1HqrLzm2jUqcNGi4YQtMstUcadNBzfuz39 | 0.019 | 4 | 1 | Finance | Finance | 06/08/2018 02:31:54 PM | 334fb8d949d4f2783060cac4ac238e13bb12gfgf3ce3c4943d9f8f5639e88 | 1
there are thirty thousand record if one record at a time using a SqlCommand along with a INSERT INTO statement is very costly and slow. You need an efficient solution to insert large number of records quickly thank God in .net SqlBulkCopy we can use this to fix this issue .
This is the main method that will insert in to table
Using con As New SqlConnection("connectionstring")
con.Open()
Using sqlTransaction As SqlTransaction = con.BeginTransaction()
Using sqlBulkCopy As New SqlBulkCopy(con, SqlBulkCopyOptions.Default, sqlTransaction)
sqlBulkCopy.DestinationTableName = "test_auto"
Try
sqlBulkCopy.WriteToServer(dt1) ' Pass datatable in to that
sqlTransaction.Commit()
Catch
sqlTransaction.Rollback()
End Try
End Using
End Using
con.Close()
End Using
Now I am sharing my whole code .
Dim sValues As String()
Dim lines As String()
' Read a textfile from that path
lines = System.IO.File.ReadAllLines("C:\testinglargedate.txt")
' declare all my column that I previously share my table format
Dim dt1 = New DataTable()
dt1.Columns.Add("Coin_ID")
dt1.Columns.Add("Customer_ID")
dt1.Columns.Add("Coin_Address")
dt1.Columns.Add("BitCoins")
dt1.Columns.Add("Status_ID")
dt1.Columns.Add("Is_Active")
dt1.Columns.Add("Created_By")
dt1.Columns.Add("Updated_By")
dt1.Columns.Add("Updated_Date")
dt1.Columns.Add("Transaction_ID")
dt1.Columns.Add("Transfer_Type")
Dim Coin_ID As Integer
Dim Customer_ID As Integer
Dim Coin_Address As String
Dim BitCoins As Decimal
Dim Status_ID As Integer
Dim Is_Active As Integer
Dim Created_By As String
Dim Updated_By As String
Dim Updated_Date As DateTime
Dim Transaction_ID As String
Dim Transfer_Type As String
'Looping of each row and skipping the header the fill the values accordingly i Just add one for sample
For Each sLine As String In lines
sLine = Trim(sLine.TrimEnd())
sValues = sLine.Split(" | ")
If sValues.Length >= 11 AndAlso (sValues(0) <> "Coin_ID " And sValues(0) <> "Coin_ID") Then 'Checking NULL and Skipping the Header Row
If Not String.IsNullOrEmpty(Convert.ToString(sValues(0))) Then
Coin_ID = Convert.ToInt32(sValues(0))
Else
Coin_ID = Convert.ToInt32(0)
End If
dt1.Rows.Add(Coin_ID, Customer_ID, Coin_Address, BitCoins, Status_ID, Is_Active, Created_By, Updated_By,Updated_Date , Transaction_ID, Transfer_Type)
End If
Next
Using con As New SqlConnection("connectionstring")
con.Open()
Using sqlTransaction As SqlTransaction = con.BeginTransaction()
Using sqlBulkCopy As New SqlBulkCopy(con, SqlBulkCopyOptions.Default, sqlTransaction)
sqlBulkCopy.DestinationTableName = "test_auto"
Try
sqlBulkCopy.WriteToServer(dt1) ' Pass datatable in to that
sqlTransaction.Commit()
Catch
sqlTransaction.Rollback()
End Try
End Using
End Using
con.Close()
End Using
Refer link :
Great Help
