How to generate "plain" SQL with OleDb or Odbc providers, in db in

[Guest]

How to generate "plain" SQL with OleDb or Odbc providers, in database
independent sintax ?

----
In VS2005 all generated SQLs in new dataset designer are database dependent,
even if we use OleDb provider, for example, if we prepare TableAdapter over
Microsoft.Jet.OLEDB.4.0
we get next SQL :

Me._adapter.InsertCommand = New System.Data.OleDb.OleDbCommand
Me._adapter.InsertCommand.Connection = Me.Connection
Me._adapter.InsertCommand.CommandText = "INSERT INTO `TBSIMPLE` (`SIMPLEID`,
`CODE`, `DESCRIPTION`) VALUES (?, ?, ?)"

Generated SQL doesn't work on Sql server for example because of this
"identifiers" in the names.
If we use MSSQL providers for example, we get "dbo." in the name of tables
and another identifiers etc. This will not work on Access .....

Is there any chance to generate database independent SQL code?

Is there any chance to override this behaviour ?
I discover that this character (identifier) could be defined (redefined?) in
the registry under the key:
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\VisualStudio\8.0\DataProviders\

under OleDb I saw another sub-branch named specialization for each driver :
Specialization\ Microsoft.Jet.OLEDB.4.0\ DataSourceInformation

Probably I should add this specific values :
DataSourceInformation\SupportsQuotedIdentifierParts = False
DataSourceInformation\IdentifierOpenQuote =
DataSourceInformation\IdentifierOpenQuote =

But I didn't find way to make it work.

The question is very simple. If I use OleDb without sql generator (manualy)
I am able to make complete solution with plain SQL statements (basic SQL) ,
is there any chance to use powerful SQL generator with typed dataset or not ?

For example, if we look at the generated statements from access or mssql
(oledb)
for "select" we see plain sql statement:

"SELECT SIMPLEID, CODE, DESCRIPTION FROM dbo.TBSIMPLE"

i am trying to forget "dbo."

If we look at select statement from access is plain simple:

"SELECT SIMPLEID, CODE, DESCRIPTION FROM TBSIMPLE"

but if we look at "insert" statement in MSSQL then what ?

"INSERT INTO [TESTOLEDB].[dbo].[TBSIMPLE] ([SIMPLEID],

,
[DESCRIPTION]) VALUES (?, ?, ?)"

What is that  "[TESTOLEDB].[dbo]." ??


This is OleDb and the same is with Odbc guys, is that really ok ?
Or what ?
Why we use OleDb or ODBC in the first place ? To connect to one and only
database ?


thx
Matej

 

[Miha Markic [MVP C#]]

Hi Matej,

Either use factory approach with ado.net (you would still have to code
differences) or go with an ORM product, such as XPO, LLBLGen, etc that
generates sql statements for supported databases.

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

How to generate "plain" SQL with OleDb or Odbc providers, in database
independent sintax ?

----
In VS2005 all generated SQLs in new dataset designer are database
dependent,
even if we use OleDb provider, for example, if we prepare TableAdapter
over
Microsoft.Jet.OLEDB.4.0
we get next SQL :

Me._adapter.InsertCommand = New System.Data.OleDb.OleDbCommand
Me._adapter.InsertCommand.Connection = Me.Connection
Me._adapter.InsertCommand.CommandText = "INSERT INTO `TBSIMPLE`
(`SIMPLEID`,
`CODE`, `DESCRIPTION`) VALUES (?, ?, ?)"

Generated SQL doesn't work on Sql server for example because of this
"identifiers" in the names.
If we use MSSQL providers for example, we get "dbo." in the name of tables
and another identifiers etc. This will not work on Access .....

Is there any chance to generate database independent SQL code?

Is there any chance to override this behaviour ?
I discover that this character (identifier) could be defined (redefined?)
in
the registry under the key:
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\VisualStudio\8.0\DataProviders\

under OleDb I saw another sub-branch named specialization for each driver
:
Specialization\ Microsoft.Jet.OLEDB.4.0\ DataSourceInformation

Probably I should add this specific values :
DataSourceInformation\SupportsQuotedIdentifierParts = False
DataSourceInformation\IdentifierOpenQuote =
DataSourceInformation\IdentifierOpenQuote =

But I didn't find way to make it work.

The question is very simple. If I use OleDb without sql generator
(manualy)
I am able to make complete solution with plain SQL statements (basic SQL)
,
is there any chance to use powerful SQL generator with typed dataset or
not ?

For example, if we look at the generated statements from access or mssql
(oledb)
for "select" we see plain sql statement:

"SELECT SIMPLEID, CODE, DESCRIPTION FROM dbo.TBSIMPLE"

i am trying to forget "dbo."

If we look at select statement from access is plain simple:

"SELECT SIMPLEID, CODE, DESCRIPTION FROM TBSIMPLE"

but if we look at "insert" statement in MSSQL then what ?

"INSERT INTO [TESTOLEDB].[dbo].[TBSIMPLE] ([SIMPLEID],

,
[DESCRIPTION]) VALUES (?, ?, ?)"

What is that  "[TESTOLEDB].[dbo]." ??


This is OleDb and the same is with Odbc guys, is that really ok ?
Or what ?
Why we use OleDb or ODBC in the first place ? To connect to one and only
database ?


thx
Matej[/QUOTE]