dumb previous question - repost

  • Thread starter Thread starter Rich
  • Start date Start date
R

Rich

After thinking about it a bit, my previous question was
kinda lame (can I fill an Access table with sql adapter -
of course not). I meant to ask if I could bulk copy data
from a sql adapter table to an oledbAdapter table.

Where I am not clear however is if one dataset can contain
a sql table and also an oledb table since I can drag the
tables from the Server tab (in visual studio 2003) If
not, is there a way to bulk copy data from a sql table to
an oledb table so I could do:
OleDbDataAdapter.Update(dataset, "AccessTbl")

Thanks,
Rich
 
Hi Rich,

As a dataset is made it is a dataset it does as far as I know not know where
it came from.

So it can be inserted in every database where columns of the database have
the same schema as the dataset. However the rowstate has to be "Inserted"

That you can do doing this.

http://msdn.microsoft.com/library/d...aadapterclassacceptchangesduringfilltopic.asp

I never did it, however I thought that the only thing you needed than was to
write your insert command for the dataadapter update.

I hope this helps,

Cor
 
Yes, I have a link doing it from one sql server table to another here
http://www.knowdotnet.com/articles/datasetmerge.html

But basically, fill the first dataadapter fill AcceptChangesDuringFill set
to true. Then, use that SAME datatable with the second dataadapter and just
call fill. It's possible to do in just a few lines of code.


Let me know if you have any questions.

HTH,

Bill
 
FYI, all you need to do is substitute your SqlDataAdapter in my example for
daSource and daDestination to Access. Just make sure the OleDbDataAdapter
is configured correctly and that AcceptChangesDuringFill is true.

Cheers,

Bill
 
Thanks all for getting back to me. It seems like the
trick is in the

DataAdapter.AcceptChangesDuringFill Property

I will have to digest this for a moment (kinda new to
Ado.Net). Here is what I do right now with com ado from
an MS Access code module. I set an ado recorset to
cmd.Execute (com ado command object) of an sp in sql
server and I loop through this result recordset to
populate a table in Access. I was hoping that with
Ado.Net I could eliminate having to do looping. But in
Bill's article at

http://www.knowdotnet.com/articles/datasetmerge.html
transfer tool. This is a very slow and inefficient way to
transfer data if you are going from server to server<<

With my com ado loop above I can transfer 5000 records
from sql server to Access in a few seconds. I was hoping
that would be a few milliseconds with Ado.net
(disconnected recordsets and all). My com ado loop is
almost as fast as using DTS (well, for less than 50,000
records from sqlsrv to Access). So if I have a dataset in
my vb.net app with 2 tables, tblsql and tblAccess is this
what I do?

sqlAdap.Fill(dataset1, "tblsql")
Dim cmd As OleDBCommand 'set cmd to OleAdap
....
cmd.Text = "Insert Into tblAccess Select * From tblsql"
OleAdap.Fill(dataset1, "tblAccess")

can I do this? or do I have to reference tblAccess and
tblsql tables differently?

Thanks,
Rich
 
Rich:

You only need to call once fill if you are trying to move one table from
SqlServer to access. So lets say you have a table with records 1-1000 in
sql server and Access has records 1001- 2000. You want to move everything
from Sql to Access. All you do is call SqlDataAdapter.Fill(dataSet,
"TableName")

Now, set the AcceptChangesDuringFill to false beforehand. Now, Use the SAME
dataset and table name to fire the Update. By setting
AcceptChangesDuringFill to false, the rowstate of eahch row will be marked
Inserted so any dataadapter can call it and see it.

So now you just call the AccessDataAdapter (or any other one) and call
update on the exact same dataset and table name. Basically, whatever is in
the parenthesis in the fill command should appear identically in Update
command, the only difference will be the DataAdapterName and the methodname.
ie SqlDataAdapter.Fill("WhateverDataSet", "whateverTable")
OleDbDataAdapter.Fill("This part should match the stuff in the fill
parameters exactly")

HTH,

Bill
 
BTW, ADO.NET isn't going to be as fast as DTS b/c as it stands now, it's a
dataaccess technology not a datatransfer technology although it can be used
as both. ADO.NET 2.0 is adding some improved functionality for this, but
what we are doing now is essentially fooling ADO.NET into working as a
transfer mechanism.

HTH,

Bill
 
Hi Bill,

I went through your article during my lunch break. Very
insightfull. Now I understand about
AcceptChangesDuringFill. This is exactly what I am hoping
to do. I will look forward for Ado.Net2. In the
meantime, I hope that Ado.Net has at least more
performance than com ado (it does, doesn't it? :).

Thank you very much for your help. I will try this right
now.

Rich
 
Hi Cor,

Thanks for your reply. I think I am starting to get it.
But I also read a reply you sent for someone else about
images. Very interesting. I tried out the code that you
had in your reply. It worked great except for one of the
subs. I had a problem with this one:

Private Sub Button2_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles Button2.Click
'writing a picture from a bytearray
If sf.ShowDialog = DialogResult.OK Then
Dim fs As New IO.FileStream(sf.FileName,
IO.FileMode.CreateNew)
Dim bw As New IO.BinaryWriter(fs)
bw.Write(abyt)
bw.Close()
End If
End Sub

I put a try/catch on it and kept getting a message that
the file already exists when I selected a jpg from a
directory. Never got past the fs... on this sub. Is
there a way to rename the jpg in this sub? Very cool code.

Rich
 
Thanks, and let me know if you have any trouble.
Rich said:
Hi Bill,

I went through your article during my lunch break. Very
insightfull. Now I understand about
AcceptChangesDuringFill. This is exactly what I am hoping
to do. I will look forward for Ado.Net2. In the
meantime, I hope that Ado.Net has at least more
performance than com ado (it does, doesn't it? :).

Thank you very much for your help. I will try this right
now.

Rich
Click to expand...
 
This worked just as advertised! I am very happy :). I
wonder if I could ask your opinion on the following:

I have been working on this one project for a few years
now where I read about 1 million+ records a night to sql
server from a Lotus Notes DB (lotus notes not relational -
document based - no ODBC driver - cant use DTS). I am
using com ado. takes about 3 hours to read/write the data
row by row (the big people won't have it any other way -
it's their creation - big company - I could take it or
leave it). Connection to Lotus Notes open for about 3
hours). My hope was to switch to Ado.Net. Read
everything to memory on my server (16 gigs right now
win2kserver (not advanced server although I asked for
that) 2 processors - box only holds 16 gigs memory). I
want to read (just read) the data directly to memory to a
bunch of datatables in my vb.net app. Close conn to Lotus
Notes and then write to sql server (or update, etc -
buncha different tables). I am sure the update will take
maybe as long, but the idea was to shorten the connection
time to Lotus Notes - read everything to memory on the
server, close conn, and then start writing/updating
locally as opposed to across the wire row by row. Any
thoughts or wisdom on my plan is appreciated.

Thanks again for your help. Oh, and on the rest of this
project, analysts query the data like no tommorrow. I
have written hundreds of SPs for them to use all invoked
with com ado (good'ole com - looks like John Henry is
going to have to make way for the Steam Shovel again -
ado.Net :).

Rich
 
Rich:

Let me give a few misc comments and hopefully I'm not dancing around the
issue.
I would seriously consider changing the way I handled it. You could write
the Lotus data to a csv and use Sql Server's BCP to load your data. My
original background is in database administration so I have a bias in this
direction versus programmatic approaches. If you can hit the data in the
first place, you can get it in some format that can be gotten to with BCP or
DTS.

Assuming that you can't politically use either of the above approaches, your
approach sounds pretty good if the goal is minimizing connection time to the
notes DB. I don't know if this is possible or not, but if you could write
an app that polls the notes db periodically throughout the day, grabbing
chunks of the monolith, loading them into a dataset and then doing your
updates periodically instead of all at once, I think it might be a little
cleaner but I also know this isn't always possible.

One way or the other, doing mega inserts is going to have a cost but the
first methods are going to probably be the cheapest. Absent that, I think
you may want to expriment depending on the overall needs of your app.

could you tell me a little more about how long the actual updates take, if
the db is being used while this is happening etc? If not, then dropping any
indexes, doing the load then rebuilding them is often a decent approach, but
the devil is really in the details in many regards and I don't want to steer
you down the wrong road. Let me know and I'll see if I can answer it a
little more precisely.

Thanks again,

Bill
 
Hi Rich,

You probably want what I have changed below, keep in mind this is a sample.

There is no error detection at all in this sample.

When there is a writting error let say because a full disk, than your image
is gone.

Therefore you should in my opinion put it in a try, catch block, first move
the original file to a tempary file, write the newfile and than delete the
tempary.

That I did not include in the sample, however for that you can use very easy
that io.file class I showed you in this sample.

However, I think this part of the sample is not a subject for the adonet
newsgroup.

(When you make the temp file you have to check if that already exist and
create a new tempname first, when I write that in this sample this sample
becomes unclear in my opinion).

When you have further problems with this, than ask it in the language.vb
newsgroup.
When I see it there and nobody else has answered it than I will do it there.

I hope this helps?

Cor
\\\
'writing a picture from a bytearray
If sf.ShowDialog = DialogResult.OK Then
If IO.File.Exists(sf.FileName) Then
IO.File.Delete(sf.FileName)
End If
Dim fs As New IO.FileStream(sf.FileName, _
IO.FileMode.CreateNew)
Dim bw As New IO.BinaryWriter(fs)
bw.Write(abyt)
bw.Close
End If
///
 
Back
Top