Sql Sevrer not updated after copying DataRows

  • Thread starter Thread starter Brandon Olson
  • Start date Start date
B

Brandon Olson

Hello all,

I am trying to import a tab delimited text file with 80 columns and 1700
records into a SQLExpress database using vb.net. Here's how I am doing it:

Dim i as Integer = 0 ' Counter
Dim da as New OleDbDataAdapter("SELECT * FROM [format1.txt]",
textFileConnectionString)
dim dt as new DataTable("FromTable")
dim sqlDt as new dataTable("ToTable")
Dim myDataRow as DataRow
da.Fill(dt)
dim sqlDa as new SqlDataAdapter("SELECT * FROM toTable"),
sqlConnectionString)
sqlDa.Fill(sqlDT)
For each myDataRow in dt.Rows
sqlDT.Rows.Add(dt.Rows(i).ItemArray)
'I use this to see if the data was actually written to the new Sql table
' This actually works
Console.WriteLine("fromTable Column(0) - " &
dt.Rows(i).Item(0).ToString)
Console.Writeline("toTable - & Column(0) - " &
sqldt.Rows(i).Item(0).ToString)
' I have tried sticking a sqlDT.AcceptChanges() here - doesn't work
' I have tried sticking a sqlDA.Update(sqlDT) here - doesn't work
i+=1
Next

....etc.

This code appears to working, the sqlDT has the new data in it and is
printing to the console, however, when I look in the Server Manager, no data
appears.

What am I missing?

Thanks in advance,

Brandon
 
Brandon,

You need to use the data adapter to update the database.

The data adapter's Update method is used to update the database with changes
to the in-memory data table.

Kerry Moorman
 
Kerry,

Thanks for your reply.

Does this mean that I cannot use a DataTable and that I have to create an
'Update' stored proc in sql? I hope there is another way, because this
table has 80 fields and that would be a P.I.T.A.

Thanks again,

Brandon
Kerry Moorman said:
Brandon,

You need to use the data adapter to update the database.

The data adapter's Update method is used to update the database with
changes
to the in-memory data table.

Kerry Moorman


Brandon Olson said:
Hello all,

I am trying to import a tab delimited text file with 80 columns and 1700
records into a SQLExpress database using vb.net. Here's how I am doing
it:

Dim i as Integer = 0 ' Counter
Dim da as New OleDbDataAdapter("SELECT * FROM [format1.txt]",
textFileConnectionString)
dim dt as new DataTable("FromTable")
dim sqlDt as new dataTable("ToTable")
Dim myDataRow as DataRow
da.Fill(dt)
dim sqlDa as new SqlDataAdapter("SELECT * FROM toTable"),
sqlConnectionString)
sqlDa.Fill(sqlDT)
For each myDataRow in dt.Rows
sqlDT.Rows.Add(dt.Rows(i).ItemArray)
'I use this to see if the data was actually written to the new Sql
table
' This actually works
Console.WriteLine("fromTable Column(0) - " &
dt.Rows(i).Item(0).ToString)
Console.Writeline("toTable - & Column(0) - " &
sqldt.Rows(i).Item(0).ToString)
' I have tried sticking a sqlDT.AcceptChanges() here - doesn't work
' I have tried sticking a sqlDA.Update(sqlDT) here - doesn't work
i+=1
Next

....etc.

This code appears to working, the sqlDT has the new data in it and is
printing to the console, however, when I look in the Server Manager, no
data
appears.

What am I missing?

Thanks in advance,

Brandon
Click to expand...
Click to expand...
 
Hi Brandon,

This task can be done much easier and much more efficiently using
SqlBulkCopy Class.

Let me give you an example.

Given a tab delimited text file "Contacts.txt" and "Schema.ini" in c:\data:

Schema.ini:

[Contacts.txt]
Format=TabDelimited
Col1=ContactID Long
Col2=FirstName Text Width 50
Col3=LastName Text Width 50

And the following table in SQL Server:

create table Contacts
(
ContactID int primary key,
FirstName varchar(50),
LastName varchar(50)
)

You could use the following code:

Sub ImportData()
Dim cnSource As New
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=c:\data;Extended Properties=Text")
Dim cmdSource As New OleDbCommand("SELECT ContactID, FirstName,
LastName FROM Contacts#txt", cnSource)
cnSource.Open()
Dim reader As OleDbDataReader = cmdSource.ExecuteReader()
Dim cnDestination As New SqlConnection("Data
Source=(local);Integrated Security=SSPI;Initial Catalog=Pruebas")
Dim bulkCopy As New SqlBulkCopy(cnDestination)
bulkCopy.DestinationTableName = "Contacts"
cnDestination.Open()
bulkCopy.WriteToServer(reader)
bulkCopy.Close()
reader.Close()
cnSource.Close()
cnDestination.Close()
End Sub


This code is several dozens faster than loading the data into a datatable
and using a SqlDataAdapter to insert the data into SQL Server

Regards from Madrid (Spain)

Jesús López
VB MVP
 
Brandon,

The data adapter's Update method takes the data table as input and applies
the data table changes to the database.

You have to supply valid sql update, insert and delete statements to the
data adapter. One option is to use stored procedures, but you don't have to.

Also, the command builder object may be able to construct the sql update,
insert and delete statements for you.

Kerry Moorman


Brandon Olson said:
Kerry,

Thanks for your reply.

Does this mean that I cannot use a DataTable and that I have to create an
'Update' stored proc in sql? I hope there is another way, because this
table has 80 fields and that would be a P.I.T.A.

Thanks again,

Brandon
Kerry Moorman said:
Brandon,

You need to use the data adapter to update the database.

The data adapter's Update method is used to update the database with
changes
to the in-memory data table.

Kerry Moorman


Brandon Olson said:
Hello all,

I am trying to import a tab delimited text file with 80 columns and 1700
records into a SQLExpress database using vb.net. Here's how I am doing
it:

Dim i as Integer = 0 ' Counter
Dim da as New OleDbDataAdapter("SELECT * FROM [format1.txt]",
textFileConnectionString)
dim dt as new DataTable("FromTable")
dim sqlDt as new dataTable("ToTable")
Dim myDataRow as DataRow
da.Fill(dt)
dim sqlDa as new SqlDataAdapter("SELECT * FROM toTable"),
sqlConnectionString)
sqlDa.Fill(sqlDT)
For each myDataRow in dt.Rows
sqlDT.Rows.Add(dt.Rows(i).ItemArray)
'I use this to see if the data was actually written to the new Sql
table
' This actually works
Console.WriteLine("fromTable Column(0) - " &
dt.Rows(i).Item(0).ToString)
Console.Writeline("toTable - & Column(0) - " &
sqldt.Rows(i).Item(0).ToString)
' I have tried sticking a sqlDT.AcceptChanges() here - doesn't work
' I have tried sticking a sqlDA.Update(sqlDT) here - doesn't work
i+=1
Next

....etc.

This code appears to working, the sqlDT has the new data in it and is
printing to the console, however, when I look in the Server Manager, no
data
appears.

What am I missing?

Thanks in advance,

Brandon
Click to expand...
Click to expand...
Click to expand...
 
Back
Top