Re: VS2003 transfer data between two data sources
- From: "William \(Bill\) Vaughn" <billvaRemoveThis@xxxxxxxxxx>
- Date: Mon, 10 Apr 2006 09:11:48 -0700
While you can deliver coal with a bicycle, I would not recommend it as a
Check out the SqlBulkCopy class. It's designed to do specifically what
you're asking to do. It permits you to open a DataReader against the source
data and blast the rows to a SQL Server table. Once the "temporary" table is
imported, you use a SP to filter/validate/refine the rows that are added to
the production table.
William (Bill) Vaughn
Author, Mentor, Consultant
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
"AbeR" <AbeR@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
Thanks. That actually turned out to be my implimentation. Open up the
firehose reading the excel file with a datareader and firing off a stored
procedure on the Server for each read(). It was a little tedious, but
right, 500 records inserted takes well under a second to append. The only
issue with it is that it sort of thrashes the DB server for that split
with an insert per record. I just thought that in the "modern" OO world
would be a model for basically transfering the "dataset" between data
providers and do it in one insert operation. I've been yelled at by folks
like Joe Celko who have berated my "procedural" solutions (according to
"so 70's"), so I was trying to get with the program ;-)
Anyway, thanks so much for the feedback. Take care.
"Otis Mukinfus" wrote:
On Sun, 9 Apr 2006 14:34:01 -0700, AbeR <AbeR@xxxxxxxxxxxxxxxxxxxxxxxxx>
Actually this is a monthly event. It's really not that big, probably
500 records, but I thought since I had a data set/ data table,
there might be a way to attach it to another data adapter. Oops on the
datasource it is an Excel file (old habits are tough to break ;) ). A
package might be a good solution except that the column names the SQL
sees will be inconsistent, thanks. When I originally wrote this applet
in MS Access where it's really simple to link data. I want to write a
windows app in .Net so I can work with the roles I designed within
The problem with reading an excel table without a header row is that the
some of the field names that are arbitraly assigned are almost random
was an issue when I did an attach to the server or tried a dts package)
columns are coming up as F1, F2... others are picking up the content of
cells in the first row as column names. At least with a data reader I
column values by ordinal.
Maybe I'm making too much out of this, but I thought there must be a
elegant solution - especially if you think of potentially scallable
Another not so elegant solution might be to get the UI to read the Excel
file, write an XML, have a predefined XSD with field names, attach the
and do a set appent?
Thank you for the feedback.
These comments are opinions, so take them with a grain of salt if you
I wouldn't do it with XML unless you are getting the data from outside
organization. My experience is that XML is over-used. It's bloated and
unnecessary unless your moving data across the web. It seems to me that a
in place where people are so enamored with it, they use it at every
I wouldn't do it with a DataSet. Reading the Excel document is going to
consuming enough. Putting the data in a DataSet while reading it is just
another added step. If one does that, they have to now parse the DataSet
at a time.
I think opening a DataReader in the Excel document is the way to go. One
per row, one insert per row.
I've been doing backend data manipulation for more than twenty years and
seen a lot of changes in this biz for the better, but when applying those
and better technologies, a good rule to follow is still, and will
be; Keep it simple, you may have to maintain it some day.
Good luck with your project,
- Prev by Date: Re: Efficient data retrieval techniques
- Next by Date: Re: VS2003 transfer data between two data sources
- Previous by thread: Re: VS2003 transfer data between two data sources
- Next by thread: Re: VS2003 transfer data between two data sources