Re: VS2003 transfer data between two data sources

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
    ... 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: 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)
  • Re: Ole Automation of Project 2000
    ... > Project and read/save data to Excel for analysis. ... Reading a blank line causes my application to ... > an exception but I have not been able to track down the source of the ... I have experimented with the windows function VarIsEmpty, ...
    (microsoft.public.project.vba)