[RESOLVED] Generic DB importer service

[wakawaka]

Hello again!

I am looking into make a generic DB importing tool. As of now it is only targeting CSV's.

Now I don't have much experience on this front but to make it useable with multiple files, I have pretty much set it up so it is reliant on a XML configuration file. I don't have any actual VB code as I am working on the XML format, but I just want to know what you guys think and if there is a better way to go about it.

<?xml version=1.0?>
<Configuration>
	<Service interval="15" newThread="Yes" />
	
	<Database connectionString="" invariantName="System.Data.Sql" />
	<ImportSettings>
		<File name="TEST 1" fetchPath="" dumpPath="" errorPath="" verificationKey="" />
	</ImportSettings>
</Configuration>

This really is banking on the "verificationKey" attribute being set correctly. The point of it is to verify that the record being imported is in-alignment with what is expected.

Example: [0=NULL|1=NOT NULL][s=String|I=Int|DA=Date|etc...]

So if the record to be import was suppose to be a entry date, name, and Food prefrence it would look like this: 1DA, 1S, 0S. This means there has to be a value other than null or a null string and then it would verify that it can be converted to a date, and so on and so forth.

Sound like a horrible idea?

[kareninstructor]

I assume when you say “generic DB importing tool” which will support multiple source types; CSV, MDB, Excel etc. along with unknown table structures. Seems you need something like a wizard interface similar to MS-Access. That is something I have been doing for some time now and will say it is not a trivial task. Below are some thoughts.

If you are working with databases take a look at my database connection class coupled up with working with get schema methods such as GetOleDbSchema. You can do the same with Excel workbooks; get sheet names as you would with database tables. When it comes to column definitions sometimes it is best to get schemas from a DataReader while other times using GetOleDbSchema. When getting schema information on some files you may run up against password protected files so account for this too.

I would have import classes for each known file type and a common user interface which guides a user thru the import process. I cannot imagine code that is smart enough to import data automatically.

In regards to using a configuration file, that all depends on your code logic.


Get schemas
http://msdn.microsoft.com/en-us/libr...hematable.aspx

Attached is a file with some common get schema logic

[wakawaka]

For this perticular task, it has to be automated as the files will automatically be dumped from several data sources and then dropped into a SQL database.

[kareninstructor]

For this perticular task, it has to be automated as the files will automatically be dumped from several data sources and then dropped into a SQL database.

If automated then your logic becomes more complex then.

[wakawaka]

Do you think the approach I am taking is incorrect?

For this, let us assume, that all import files will be CSV files. I would like to have it so the XML file and the DB are the only things that need to be changed to support another datasource. I don't want to have to worry about updating source code of the service.

My idea about the "verivicationKey" attribute would allow the records to be checked before the DB is involved. Is that the best approach or should I just attempt to add it and if it fails, catch it? Is it too much extra work to check each line of the file to make sure it is inline with what is expected to be imported in the SP that will be executed to add it?

[techgnome]

I'd probably go a different route... something a little less cryptic, much, much more verbose:
(given - entry date, name, and Food prefrence)

<File name="TEST 1" fetchPath="" dumpPath="" errorPath="">
    <Field name="EntryDate" type="date" required="true" />
    <Field name="Name" type="string" maxlength="100" required="true" />
    <Field name="FoodPreference" type="string" maxlength="1000" required="false" />
</File>

The required element would control the nullability of the field... if it's required, it can't be null.. if it isn't required then it can be null.

I like this approach because if a field gets added to the middle of the file (it happens) ... it's much easier to update than 1DA 1S 0S....

-tg

[wakawaka]

I see your point, I thought that too but I guess I was being lazy because some of the tables have as many as 25 fields...

[techgnome]

been there, done that... it's not as big of a deal as you think... and in 6 months from now, when you have to go maintain those tables and add or take something out... you'll be thankful you took the time now to be bit more clear.

-tg

and if you really want to be lazy... built a utility where you can feed it a connection string, a table name, and it generates the xml for you...

[wakawaka]

lol, I would, but then it would be MORE work to figure out that XML file >_<

[techgnome]

no, no not the file... just the one snip for that one file.... dump it into a text box... copy-paste.

-tg

[wakawaka]

I got one last question for you. How would YOU set it up for the stored procedure to import the file? You think it would be bad to add the stored procedure to the XML file as an attribute to make it completely independant of the service?

It seems wrong, but like the best way to go about it without having to re-code the service.

[techgnome]

Yes and no... it depends really. if you want to use a stored proc to handle the info you could do this:

<File name="TEST 1" fetchPath="" dumpPath="" errorPath="" dbhandler="usp_mysproc">
    <Field name="EntryDate" type="date" required="true" dbparameter="@EntryDate" />
    <Field name="Name" type="string" maxlength="100" required="true" dbparameter="@Name" />
    <Field name="FoodPreference" type="string" maxlength="1000" required="false" dbparameter="@FoodPref" />
</File>

Or you could simply specify the name of the sproc, using the name of the field to find the parameter

Or you do neither...

If the files are small and performance isn't a major issue, then I'd go the verbose route and specify the sproc name and the parameters as in the example...

The other alternative is the SQLBulkCopy namespace... which is probably where I'd go with it.

<File name="TEST 1" fetchPath="" dumpPath="" errorPath="" target="tblStagingData">
    <Field name="EntryDate" type="date" required="true" />
    <Field name="Name" type="string" maxlength="100" required="true" />
    <Field name="FoodPreference" type="string" maxlength="1000" required="false" />
</File>

tblStagingData would mirror the data I'm importing... I could then use SQLBulkCopy to mass pull the data into the database... from there another process could take over and handle the data itself.

-tg

[wakawaka]

I did look at the SQLBulkCopy because I heard it's speed was quite impressive, but my idea was to use the dbfactories to provide database independency. I don't think any other bulk copy methods exist except for the SQL Server.

Prehaps I am being too picky about this and should just say it is SQL Server or it is nothing!

[techgnome]

well, you already limited yourself when you introduced stored procs...

Now, you could have a SQL Server importer... an Access Importer, and Oracle importer ... they could all use the same xml config file... but the code to handle the db and inserts really should be specific to the target database.

-tg

[wakawaka]

Hmmm, I suppose that would be a better alternative.

While I say SP, that is just the SQL Server name. They exist in Access and MySQL as well so I didn't think that was really limiting me, but I guess executing a single statement for a single row each time is horribly ineffecient huh.

[techgnome]

perhaps... but if it's all you can do, there's not much else you can do about it....

-tg