ADO.NET, Why Is it so darn different and difficult?

  • Thread starter Thread starter Mythran
  • Start date Start date
M

Mythran

Yes, I know how to use it, but darn...I could do what I want to do in ADODB
(non-dotnet) with just a few statements, ADO.NET (which is supposed to be better)
requires more than 1000 statements to do the same task! There is no easier and
quicker way in ADO.NET.

I've got 33 tables, all these tables have relationships with one another in one
way or another. I'm reading through a HIPAA compliant file which, if any of you
know hipaa, is no easy task. Then to place ALL values from this file into a
typed dataset, then inserting this data from the data into the database is a
pain. Not only a pain, I'm totally lost in doing it :(

ALL tables have a RecordId field which is an autogenerated uniqueidentifier
field. These fields are autogenerated on the server (SQL Server). That is my
first problem, I am not allowed to use ANY stored procedures on the server. Not
allowed meaning NO IF'S ANDS OR BuTs.

So, any suggestions on that part would be helpful.

Next question would be, I've filled the entire dataset. Remember, 33 tables in
the dataset. Each table is NOT the same...building the dataset was just dropping
the table listing from the SQL Server to the DataSet screen in the IDE.

Now that I'm here, how do I, after the dataset has all the values I need in it,
save them to the database. Ok, another question arises, the datasets don't have
the unique identifiers in them, so they can't contain or hold the
relationships...how the heck could it?!?

I'm lost, I'm forgotten, I'm upset, I'm ... just tired today heh..

Thanks in advance,

Mythran
 
When you say HIPAA, are you referring to HL7 feeds? If so, then that would
certainly make some things easier.
There are tons of tools to extract data like SQL Server DTS which could
probably get this done for you relatively easily. Moreover, if your goal is
to simply get data out of files and into the DB, you don't even need to use
DataSets at all.

Let's say your document is a Typical HL7 ADT feed or a delimmited file. You
can use file IO to read through it, and place each respective field in an
object. The objects could be stored in a collection and you could
iteratively walk through it, sending ExecuteNonquery statement to the DB.

If you have to use DataSets, you could use the TableMappings to get you
there, Last Month's Visual Studio magazine has a pretty good example of
using them when your source data doesn't match the back end db table. on a
scale of one to 10, this would probably be about a 3-5 on the complicated
side b/c you can do it visually. For simpler mappings, it's about a 1.

ADO.NET isn't primarily made to be a transformation mechanism although it
certainly is capable of it. In your case, I'd be running for DTS b/c it's
easy to use, fast as heck, and can be automated. As far as the unique
identifiers, remember, you can add them to the datacolumn programatically
regardless of if they exist on teh db or not..

If you really want to, you can even wrap old ADO code and use it in .NET
although it's probably a little late for that approach since you have 33
tables already in a DataSet.

Just out of curiosity, what is the reason your boss or DBA is so anti-Stored
procedure? That's a rather curious position to hold, and since it is
limiting in so many regards, that's probably a fight worth fighting in the
future, although there probably isn't much you can do it about now.

Also, by any chance do you have access to Biztalk Server? If so, you could
map those fields to XML and use BTS for the transformation, then read them
directly into ADO.NET or map them to SQL Server. I'm guessing that's
probably not an option or you would have mentioned it, but I figured I'd
bring it up just in case.

DTS is probably your best option, but TableMappings should get you there
without taking you off track.

Stick in there, ADO.NET is a dream come true in so many regards, and by the
time you are done with this project, you'll surely be a whiz with it.

If you have any questions, I've done this sort of thing a bunch and will be
glad to help.

HTH,

Bill
 
Mythran,

On the point of dataset relationships, you can create autonumber columns in
DataTables, so that a new unique (throughout the table) id is allocated
whenever you add a new record. ADO.Net also supports creation of foreign
key constraints which can reference this id column. Now of course this
means that we can use a relationship to navigate from the parent row to
child rows and vice versa, within the dataset.

However, what happens when the changes are saved to the database? Well, if
the id column is defined as an identity column in the database then a new,
potentially different, id will be allocated when the new row is written from
the parent datatable. This is where things get clever... VS.Net's
DataAdapter wizard generates code to requery the inserted row to find the
allocated identity and update it in the parent DataTable. Changes are then
automatically cascaded from the parent row to the child rows, so that they
can then be correctly written to the database.

So while it may seem that you can't use unique identifiers in the dataset,
in fact this works very well. The downside is that you're limited to
identity fields (you can't use GUID fields which are more unique).

Neil.
 
Back
Top