Insert Multiple Records in SQL DataBase
I am hoping someone can explain a bit of sql to me as I am having a difficult time getting my head around some of the concepts and reading seems to be more confusing
I am hoping a real world example might help me. I have a sql database with an existing table that is populated from another table and inserted using sqlBulkCopy
I would like now like to be able to add records to the Table perferably multiple records that come from a DataTable that is created from a csv file using the TextFieldParser class. I was hoping to add these at one time or with one statement, sub or function etc...
I am trying to follow the this code I found in the codebank and I honestly don't understand it so I cant determine what to do, this is what I have so far
Code:
Dim str As String = ("F:\email\emailtest.txt")
ReadTable2(str)
Dim col0 As String = Table2.Columns(0).ColumnName
Dim col1 As String = Table2.Columns(1).ColumnName
Dim col2 As String = Table2.Columns(2).ColumnName
Dim col3 As String = Table2.Columns(3).ColumnName
Using connection As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Bill\Documents\Visual Studio 2008\WebSites\Emailer\App_Data\Database.mdf;Integrated Security=True;User Instance=True")
Dim insert As New SqlCommand("INSERT INTO Contacts (LicenseNumber, FirstName, LastName, Email) VALUES (@LicenseNumber, @FirstName, @LastName, @Email)", connection)
Dim Adapter As New SqlDataAdapter("SELECT * FROM Contacts", connection)
insert.Parameters.AddWithValue("@LicenseNumber", col0)
insert.Parameters.AddWithValue("@FirstName", col1)
insert.Parameters.AddWithValue("@LastName", col2)
insert.Parameters.AddWithValue("@Email", col3)
insert.Parameters.AddWithValue("OptOut", 0)
Adapter.InsertCommand = insert
Adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
Dim updatetable As New DataTable
'Retrieve the data.
Adapter.FillSchema(updatetable, SchemaType.Source)
'Add the new rows to the DataTable, e.g.
Dim row As DataRow = updatetable.NewRow()
row("LicenseNumber") = (row(1)(col0))
row("FirstName") = (row(col1))
row("LastName") = (row(col2))
row("Email") = (row(col3))
row("OptOut") = 0
Table.Rows.Add(row)
'Save the changes.
Adapter.Update(updatetable)
End Using
Again I do NOT know what I am doing I seem to be competely lost with sql and particuarly parameters. So of course this code is no where near accurate for what I am trying to achieve, I am hoping someone would be kind enough to give me a break down of what it is supposed to be doing or what I am supposed to be doing in order to achieve my end result of
Re: Insert Multiple Records in SQL DataBase
hope this kevininstructors post may prove very good & simple
Re: Insert Multiple Records in SQL DataBase
The .NET provider System.Data has classes for BulkCopying. Refer to this article written for Oracle and modify the code to suit your database.