C#.NET Native SQL Data Provider vs. ODBC DSN Connection - Differences?

> potentially against any database
you'll need to have some kind of parsing or db checking to create a correct query
for example: sysdate for oracle, getdate() for mssql, ...

performance reasons are what usually cause someone to program for 1 specific database and use a native connection

I hate to be the bearer of bad news but database independent code is pretty much a fairy tale.

Yes, you can do basic CRUD in just about any database but the app will likely not perform well using generic SQL statements.

Sorry but there is no cross database stored procedure language.

What I've seen in the past is you create generic funtions in all the main code then you have a specific database module that does all the specific database calls.

Then all you need to do is relink the app with the specific database module for the specific install.

If you want stored procedures, it can be a simple database script that can also be executed as part of the install.

Oracle now has a 12c .Net Managed driver that does not need a full client install.  It doesn't support ALL database objects but handles about all the basic ones.

Guys,

thanks for your help.  Just to make it clear, I'm not in anyway looking to have platform independent code.  I stated that I would not use code that would cause a problem (I'll test it againstall of the DBs I would use) so the getdate() issue for example would never occur.  I know that wouldn't work on a SQL Server piece of code so I wouldn't use that.

"none of the proprietary SQL code differences between DBs will cause a problem"

I just don't want to get lost on that point when that's not the question.

An example would be:

Select * from Table1

or

sp_RunThisCode

in the SQL which would work for ither SQL Server or Oracle (obviously I wouldn't use that code for a MS Access database as it can't run stored procedures).

To elaborate more, I'm actually developing a toolkit to get data out of any database.  The basic premise being that if I go into a company and they have a DB2 database and a DB2 driver, I can extract data or set up queries for a basic user to prepare data.

The key here is that if the user has database access and can create a working ODBC DSN to that database (in exactly the same way that use could do this in Excel, for example), I can create a new script and fire it through my application and know that it will do whatever it is that I type in (it's my own fault if I can't code in that language).  Assuming that I know what to code, would there be any inherant differences between the native provider (for which I'd have to recode my project, find the driver (bear in mind there might be many versions of the driver for the database that I might use), add it in and recompile it.  That's a lot of work to do each time.

The benefit is that my export functions will be the same each time, meaning I don't have to code a new app everytime I build something new to do with data extraction. I simply am reliant upon the limitations of the ODBC driver which will cover the vast majority of what I want to do.

I guess my main concern might be around the lack of security using a basic SQL injection prone piece of code because it's not wrapped a command builder or that ODBC might not work with a datagrid in a particular scenario that someone had come across.

The timing is not an issue as the volumes of data processed won';t cause a big difference - I'm not expecting to extract full databases.  Most of the queries would be under a few minutes at most.

Sorry for the comarative vagueness of my initial question but it's hard to know how other people will interpret it.  I wanted it to be a broad brush question in case there was something unusual I hadn't considered.

I suppose a functional comparison matrix is what I want between the two types but I can't find anything like it on the net so far.

Many thanks.


Craig.

not sure if you did a typo there ...
"the getdate() issue for example would never occur.  I know that wouldn't work on a SQL Server piece of code so I wouldn't use that."
>> the getdate() works by default on mssql, but not on oracle.

i gave you the most basic of comparisons the getdate() for mssql against sysdate for oracle
getdate() retrieves the current date and time on mssql
sysdate retrieves the current date and time on oracle

sample on mssql
insert into tableX (item, time) select 'X', getdate()

same sample on oracle
insert into tablex (item, time) select 'X', sysdate from dual

you see ?

the statements are completely different, but do exactly the same ...
besides the point that getdate() is precise up to 100 nanoseconds and sysdate is precise up to complete seconds

slightwv indicated a fairy tale ... it's more of a nightmare actually

Yeah, it was a typo, sorry.

There will be absolutely no untested architecture unspecific code in my application.  I will never try to run getddate() on Oracle.  If I did, it will fail and I'll now about it immediately and change it.

This part is a total red herring.

Consider:

select * from table1

as a better example.

Regardless, if I did want to use getdate() on a SQL Server script, I would and I'd only use it against SQL Server.  Really is isn't the issue at all.

The point of my multiplatform application is to implement a very simple way for basic users to extract and information from any SQL compliant database that they have access to.  They won't be writing the SQL - I will.  They won't even see it.

Assuming I know how to code in the relevant language, do you think there are any differences between the two connection types?

Thanks,


Craig.

>> Assuming I know how to code in the relevant language
i don't assume anything

basic users are the most difficult ones
ever saw the first star trek film ?
this scene with Scotty is a definitive sample of how basic a user can be:
https://www.youtube.com/watch?v=hShY6xZWVGE

I don't know what to say to that.

be careful when going to a customer and claiming your app will work on any database
the first week it will be run on interbase
and your app may work

then they'll call you because they have a problem
without informing you they migrated the data to mssql
so you fix that

then they have at it again and migrate to oracle
and call you again, because of the same problem

later on, and because it's become too expensive, then move to mysql
and you have to go back

mind you, since you stated your app works on any database,
they will refuse to pay your 3 last visits ...because
you stated ... and not they ... that your app works on any database

i hope you see the pitfalls ?

i'd start with 1 database being compliant
and gradually add a database as you go along ...

I am a contractor of 15 years application development experience.  I use ODBC connections sometimes, I use native connections sometimes.

All I can do is ask the question that I need to know the answer to.  

My app will work for any database that I develop it for, not any database in the world ever.  It still requires an awful lot of though in each particular case to do this development.  I fully support these applications going forward.

I appreciate your concern but please, I can't rebuff every challenge because you don't assume anything.

The ODBC Connection must surely have been created for a reason.  I'm pretty sure it does a good job.  I'm simply asking if there ar any major restrictions on it as compared to the native drivers for my future development.

All of my code *is* thorougly tested.  You simply have to believe me on that otherwise, can we please not have this conversation?  No one is more aware of the fall out from that than me.  Doing a background check on every person's ability in her before answering a question can't be helpful.

Regardless, pretend I'm doing this for home use if it makes this question more focussed.

The problem with adding one database at a time is the time factor.  That's my point and that's where i have been for the last 6 or 7 years in C# on this and hence my question.  This isn't something I have dreamt up over night.  It's been an ongoing question for years so it is pertinent and it is going to save me a lot of time.  That's the name of the game here isn't it?  There is always a base assumption that people will test their code.  I get that.

I'm guessing that not many people switch between the two types of driver so I appreciate why you may not have used both extensively.  I have.  I'm just belt and braces asking for outside help just in case I have missed something.  I'd be negligent not to do so and I'd happily take a useful answer from a first time programmer to an expert if it helped.

Appreciated.


Craig.

>>The ODBC Connection must surely have been created for a reason.  

It was an attempt at being data store independent.  Just like Java was supposed to be platform independent (code once, run anywhere)...

IMHO, ODBC is typically more trouble than it is worth.

For example:
What happens when one of the Oracle tables has a CLOB and 1 Gig of data in the column and you do a 'select * from table'?  Don't think ODBC will handle it...

Since you are going to write/test against your 'supported' databases, I would use native drivers and the app specific database module like I explained above.

Your main code uses functions you name like gettable1()

Then ALL the specific database modules have a gettable1() function coded specifically for the target database.  To change database platforms, just relink the app with the one module and no other code should have to change.  You have an Oracle.DLL, SQLServer.dll and DB2.dll.

There will always be extremes on this, I guess.  If my application falls over because of an extremity, it is likely that it would have died a death on the datagridview or because of a lack of RAM or any number of things that could also go wrong that's hard to cater for.  I'd never run it for that size of data anyway - I can always keep the parameters of this to within a reasonable capability.  After all, I have to support this so I'll gauge that case-by-case.

My select statement was to show they type of statement.  In reality, it would only be:

select col1, col2, col3
from Table_1

I'd never actually do a 'select *' because, as you say, I wouldn't have tested the column properly and understood its data type and content (I'd have profiled the data anyway).  It's hard to get across when something should be generally or specifically meant in here.  Apologies for that.

My app simply runs SQL for basic users who typically need access to the base system because of a lack of knowledge on how to create the native reports or because they just prefer using it in Excel or because they are a simple user and want to shorten a task that previously took them half a day into one Excel spreadsheet.  That's where this really comes in.  Maybe 100k rows and a few table joins of basic data types.  Typically, this is all businesses seem to require in terms of operational improvements.

Thanks,


Craig.

I appreciate this was always a hard question to answer if there wasn't a great deal of difference between the two, which in my case, seems to be the case.  I was asking a question that potentially had no answer.

I'm going to take a lot of heart that all of the comments have been about these 'extreme' cases and that no one said, "ODBC doesn't work when updating from a stored procedure with parameters," or some such comment.

In that case, thank you all for your help on this.



Craig.