Re: VS2003 transfer data between two data sources

While you can deliver coal with a bicycle, I would not recommend it as a
"best practice".
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.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
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.
__________________________________

"AbeR" <AbeR@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:890E1B2D-B39E-4CD4-B806-0033F0874F8C@xxxxxxxxxxxxxxxx
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
you're
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
second
with an insert per record. I just thought that in the "modern" OO world
there
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
them
"so 70's"), so I was trying to get with the program ;-)

Anyway, thanks so much for the feedback. Take care.

Abe Rosner

"Otis Mukinfus" wrote:

On Sun, 9 Apr 2006 14:34:01 -0700, AbeR <AbeR@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote:

Hi Otis,

Actually this is a monthly event. It's really not that big, probably
about
500 records, but I thought since I had a data set/ data table,
theoretically
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
DTS
package might be a good solution except that the column names the SQL
Server
sees will be inconsistent, thanks. When I originally wrote this applet
it was
in MS Access where it's really simple to link data. I want to write a
simple
windows app in .Net so I can work with the roles I designed within
SqlServer.

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
(this
was an issue when I did an attach to the server or tried a dts package)
Some
columns are coming up as F1, F2... others are picking up the content of
the
cells in the first row as column names. At least with a data reader I
can get
column values by ordinal.

Maybe I'm making too much out of this, but I thought there must be a
more
elegant solution - especially if you think of potentially scallable
problems.
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
XLM
and do a set appent?

Thank you for the feedback.

- Abe

[snip]

These comments are opinions, so take them with a grain of salt if you
wish.

I wouldn't do it with XML unless you are getting the data from outside
your
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
fad is
in place where people are so enamored with it, they use it at every
opportunity.

I wouldn't do it with a DataSet. Reading the Excel document is going to
be time
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
a row
at a time.

I think opening a DataReader in the Excel document is the way to go. One
read
per row, one insert per row.

I've been doing backend data manipulation for more than twenty years and
have
seen a lot of changes in this biz for the better, but when applying those
new
and better technologies, a good rule to follow is still, and will
continue to
be; Keep it simple, you may have to maintain it some day.

Good luck with your project,

Otis Mukinfus
http://www.arltex.com
http://www.tomchilders.com



.

Relevant Pages

  • Re: VS2003 transfer data between two data sources
    ... data and blast the rows to a SQL Server table. ... The problem with reading an excel table without a header row is that the ... Another not so elegant solution might be to get the UI to read the Excel ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: VS2003 transfer data between two data sources
    ... data and blast the rows to a SQL Server table. ... The problem with reading an excel table without a header row is that the ... Another not so elegant solution might be to get the UI to read the Excel ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: VS2003 transfer data between two data sources
    ... procedure on the Server for each read. ... The problem with reading an excel table without a header row is that the ... Another not so elegant solution might be to get the UI to read the Excel ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: workbooks.open function fails to open an existing excel file when used in ASP, but works in VB.
    ... Just reading the script there doesn't seem to be anything wrong with it, ... excel is definitely not recommended as a server side application. ...
    (microsoft.public.excel.programming)
  • CEICW Fails at RegisterMSBOExchangeBP
    ... Ethernet adapter Server Local Area Connection: ... Call to Reading hardware selection returned ok. ... calling CNetCommit::ValidateRouterConnectionProperties. ... Call to Reading preferred DNS server IP returned ok. ...
    (microsoft.public.windows.server.sbs)