-
Mar 17th, 2013, 05:21 PM
#1
Thread Starter
Frenzied Member
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
-
Mar 18th, 2013, 09:33 AM
#2
Re: Insert Multiple Records in SQL DataBase
hope this kevininstructors post may prove very good & simple
-
Mar 19th, 2013, 09:52 AM
#3
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.
Everything that has a computer in will fail. Everything in your life, from a watch to a car to, you know, a radio, to an iPhone, it will fail if it has a computer in it. They should kill the people who made those things.- 'Woz'
save a blobFileStreamDataTable To Text Filemy blog
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|