Need a better way

[Ruslan Shlain]

I am pulling data from flat file and putting it in to the DB using dataset.
Below i am including code how i did it. I am adding a row to a new table in
the dataset and adding collumns to it one by one. The reson i did it this
way is because i need to have column changed in order for Update to work. Is
there a better way to do it? I am looking to cut proccesing time.

Thank you all very much.

Dim FilePath As String = "C:\ftproot\TOG\"

Dim Conn As New
System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" & FilePath & ";Extended Properties=""text;HDR=NO;FMT=Delimited""")

Dim Comm As New System.Data.OleDb.OleDbDataAdapter("select * from
c126927_1_sc_011504_001.txt", Conn)

Dim DS As New DataSet

Try

Comm.Fill(DS)

Dim dr As DataRow

Dim dt As DataTable

dt = DS.Tables(0).Clone

dt.TableName = "scs_ds_sold_customer"

Dim newdr As DataRow

Dim dc As DataColumn

DS.Tables.Add(dt)

For Each dr In DS.Tables(0).Rows

newdr = DS.Tables(1).NewRow

For Each dc In DS.Tables(0).Columns

newdr.Item(dc.ColumnName) = dr.Item(dc.ColumnName)

Next

DS.Tables(1).Rows.Add(newdr)

Next



Dim objCn As New
SqlConnection("Server=NTCH27;Database=scs_auto_menu;Trusted_Connection=True;
")

Dim NwindDA As New SqlDataAdapter("select * from scs_ds_sold_customer",
objCn)

Dim x As SqlCommandBuilder = New SqlCommandBuilder(NwindDA)

objCn.Open()

NwindDA.Update(DS, "scs_ds_sold_customer")

Catch e As Exception

End Try

 

[Miha Markic]

Hi Ruslan,

Set Comm.AcceptChangesDuringFill = false and all rows in original table will
remain as Added.
You might use Update directly on this table.

 

[Ruslan Shlain]

Thank you Miha.

This works. Its awsome.

Hi Ruslan,

Set Comm.AcceptChangesDuringFill = false and all rows in original table will
remain as Added.
You might use Update directly on this table.

--
Miha Markic - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

work.
SqlConnection("Server=NTCH27;Database=scs_auto_menu;Trusted_Connection=True;

 

[William \(Bill\) Vaughn]

The "best" way to move data from place to place is to use a tool designed
for this purpose. Moving data with ADO.NET is like moving straw with a
kitchen spoon--it works if you have enough patience. Most folks gravitate
toward DTS or BCP which are designed specifically to move data--quickly,
quietly and efficiently from and to a variety of data sources.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Plausible Indirection]

Don't know about SQL Server, but just about every other DBMS system I
can think of has a tool that comes with the server that, given a flat
file and some formatting directions, can load the table data much
faster and easier than anything else. I would look for such a tool
before I wrote my own code; especially if I was worried about
throughput. I'd have little confidence that I was going to be able to
quickly come up with something faster and more reliable than some tool
which had already been used and abused by thousands of other
developers/users. If I had a limited data set and just needed it
once, that might be a different story.

Luck,
Chris

 

[William \(Bill\) Vaughn]

This is why I suggested DTS or BCP. These are tools designed for and shipped
with SQL Server. They take any file (flat or otherwise) and import the data
into SQL Server.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________