Sql Sevrer not updated after copying DataRows

microsoft.public.dotnet.framework.adonet

10 Jan 2006 4:12 PM

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

10 Jan 2006 5:10 PM

Kerry Moorman

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

Show quote

"Brandon Olson" wrote:

> 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
>
>
>
>

10 Jan 2006 5:51 PM

Brandon Olson

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
Show quote

"Kerry Moorman" <KerryMoor***@discussions.microsoft.com> wrote in message
news:1A4A5E1C-C79B-4117-B820-EED82BA478DB@microsoft.com...
> 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" wrote:
>
>> 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
>>
>>
>>
>>

10 Jan 2006 7:16 PM

Kerry Moorman

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

Show quote

"Brandon Olson" wrote:

> 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" <KerryMoor***@discussions.microsoft.com> wrote in message
> news:1A4A5E1C-C79B-4117-B820-EED82BA478DB@microsoft.com...
> > 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" wrote:
> >
> >> 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
> >>
> >>
> >>
> >>
>
>
>

10 Jan 2006 6:04 PM

Jesús López

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

Show quote

"Brandon Olson" <bol***@lindaclarkrealtors.com> escribió en el mensaje
news:%23kpbeCgFGHA.2912@tk2msftngp13.phx.gbl...
> 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
>
>
>