Question for the OO Gurus

[flu]

I recently had to modify an application that had originally been written for
SQL Server to work with MySQL. I found myself replacing all of the
declarations for the connection and command objects with their MySQL class
equivalents.

In hindsight it is clear my code should have been written originally for
some basic or super SQL class that SQLServer, MySQL, or ODBC, implements.
Ideally this could be determined even at run time.

This is a problem I'm sure others in this group have run into.

I am interested in how you have approached this problem.

TIA

[James Curran]

Well, you originally would have had:

SqlConnection conn = new SqlConnection();

Using a higher level class you would have had:

IDbConnection conn = new SqlConnection();

Which means that you would have had to change one spot instead of two --- or
click "Replace" in the "Find/Replace" dialog half as many times --- or click
"Replace All" exactly the same number of time.

The bottom line is that the Data Access Layer of your code & the Database
itself are intimately tied, and trying abstract it just shifts the problem
somewhere else.

If, for some reason, you absolutely need multiple database support, you
could do something like:

IDbConnection NewConnection()
{
string dbType = GetDbTypeForAppConfig();
switch (dbType)
{
case 'sqlserver':
return SqlConnection();
case 'mysql':
return MySqlConnection();
case 'oracle':
// etc
}
}
}

"flu" <fl***********@nospam.yahoo.com> wrote in message
news:7D*******************@newssvr13.news.prodigy. com...

I recently had to modify an application that had originally been written for SQL Server to work with MySQL. I found myself replacing all of the
declarations for the connection and command objects with their MySQL class
equivalents.

In hindsight it is clear my code should have been written originally for
some basic or super SQL class that SQLServer, MySQL, or ODBC, implements.
Ideally this could be determined even at run time.

This is a problem I'm sure others in this group have run into.

I am interested in how you have approached this problem.

TIA

[Alvin Bruney [MVP]]

Curiously, what is the reason for choosing mysql over sql?

--
Regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
The Microsoft Office Web Components Black Book with .NET
Now Available @ http://tinyurl.com/27cok
----------------------------------------------------------
"James Curran" <ja*********@mvps.org> wrote in message
news:%2******************@TK2MSFTNGP10.phx.gbl...

Well, you originally would have had:

SqlConnection conn = new SqlConnection();

Using a higher level class you would have had:

IDbConnection conn = new SqlConnection();

Which means that you would have had to change one spot instead of two ---
or
click "Replace" in the "Find/Replace" dialog half as many times --- or
click
"Replace All" exactly the same number of time.

The bottom line is that the Data Access Layer of your code & the Database
itself are intimately tied, and trying abstract it just shifts the problem
somewhere else.

If, for some reason, you absolutely need multiple database support, you
could do something like:

IDbConnection NewConnection()
{
string dbType = GetDbTypeForAppConfig();
switch (dbType)
{
case 'sqlserver':
return SqlConnection();
case 'mysql':
return MySqlConnection();
case 'oracle':
// etc
}
}
}

"flu" <fl***********@nospam.yahoo.com> wrote in message
news:7D*******************@newssvr13.news.prodigy. com...

I recently had to modify an application that had originally been written

for

SQL Server to work with MySQL. I found myself replacing all of the
declarations for the connection and command objects with their MySQL
class
equivalents.

In hindsight it is clear my code should have been written originally for
some basic or super SQL class that SQLServer, MySQL, or ODBC, implements.
Ideally this could be determined even at run time.

This is a problem I'm sure others in this group have run into.

I am interested in how you have approached this problem.

TIA

[Lionel LASKE]

If you want an abstraction layer for data access, encapsulate all your data
processing (not only Connection or Command creation) in a assembly
dynamicelly loaded by you application.

A really fun example of that sort of architecture is in DotNetNuke
(http://www.dotnetnuke.com). See
http://www.dotnetnuke.com/LinkClick....id=478&mid=857
for more.

Lionel.

"James Curran" <ja*********@mvps.org> a écrit dans le message de news:
%2******************@TK2MSFTNGP10.phx.gbl...

Well, you originally would have had:

SqlConnection conn = new SqlConnection();

Using a higher level class you would have had:

IDbConnection conn = new SqlConnection();

Which means that you would have had to change one spot instead of two ---
or
click "Replace" in the "Find/Replace" dialog half as many times --- or
click
"Replace All" exactly the same number of time.

The bottom line is that the Data Access Layer of your code & the Database
itself are intimately tied, and trying abstract it just shifts the problem
somewhere else.

If, for some reason, you absolutely need multiple database support, you
could do something like:

IDbConnection NewConnection()
{
string dbType = GetDbTypeForAppConfig();
switch (dbType)
{
case 'sqlserver':
return SqlConnection();
case 'mysql':
return MySqlConnection();
case 'oracle':
// etc
}
}
}

"flu" <fl***********@nospam.yahoo.com> wrote in message
news:7D*******************@newssvr13.news.prodigy. com...

I recently had to modify an application that had originally been written

for

SQL Server to work with MySQL. I found myself replacing all of the
declarations for the connection and command objects with their MySQL
class
equivalents.

In hindsight it is clear my code should have been written originally for
some basic or super SQL class that SQLServer, MySQL, or ODBC, implements.
Ideally this could be determined even at run time.

This is a problem I'm sure others in this group have run into.

I am interested in how you have approached this problem.

TIA

[stork]

> Curiously, what is the reason for choosing mysql over sql?
runs on a unix box.

[flu]

Not my choice, but the client's. It is free and runs on unix as well as
windows.

flu
"Alvin Bruney [MVP]" <vapor at steaming post office> wrote in message
news:OK**************@tk2msftngp13.phx.gbl...

Curiously, what is the reason for choosing mysql over sql?

--
Regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
The Microsoft Office Web Components Black Book with .NET
Now Available @ http://tinyurl.com/27cok
----------------------------------------------------------
"James Curran" <ja*********@mvps.org> wrote in message
news:%2******************@TK2MSFTNGP10.phx.gbl...

Well, you originally would have had:

SqlConnection conn = new SqlConnection();

Using a higher level class you would have had:

IDbConnection conn = new SqlConnection();

Which means that you would have had to change one spot instead of two ---
or
click "Replace" in the "Find/Replace" dialog half as many times --- or
click
"Replace All" exactly the same number of time.

The bottom line is that the Data Access Layer of your code & the Database
itself are intimately tied, and trying abstract it just shifts the
problem
somewhere else.

If, for some reason, you absolutely need multiple database support, you
could do something like:

IDbConnection NewConnection()
{
string dbType = GetDbTypeForAppConfig();
switch (dbType)
{
case 'sqlserver':
return SqlConnection();
case 'mysql':
return MySqlConnection();
case 'oracle':
// etc
}
}
}

"flu" <fl***********@nospam.yahoo.com> wrote in message
news:7D*******************@newssvr13.news.prodigy. com...

I recently had to modify an application that had originally been written

for

SQL Server to work with MySQL. I found myself replacing all of the
declarations for the connection and command objects with their MySQL
class
equivalents.

In hindsight it is clear my code should have been written originally for
some basic or super SQL class that SQLServer, MySQL, or ODBC,
implements.
Ideally this could be determined even at run time.

This is a problem I'm sure others in this group have run into.

I am interested in how you have approached this problem.

TIA

[Alvin Bruney [MVP]]

did you find a performance gain with mysql over sql?

we moved from informix to mysql for our data analysis solutions because
mysql showed at least a 65% performance gain for light to moderate
concurrent load. And it don't cost 20grand per processor. Some of the
advanced features are lacking, but all in all, i'd say it was a sound
investment.

--
Regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
The Microsoft Office Web Components Black Book with .NET
Now Available @ http://tinyurl.com/27cok
----------------------------------------------------------
"flu" <fl***********@nospam.yahoo.com> wrote in message
news:9U*******************@newssvr13.news.prodigy. com...

Not my choice, but the client's. It is free and runs on unix as well as
windows.

flu
"Alvin Bruney [MVP]" <vapor at steaming post office> wrote in message
news:OK**************@tk2msftngp13.phx.gbl...

Curiously, what is the reason for choosing mysql over sql?

--
Regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
The Microsoft Office Web Components Black Book with .NET
Now Available @ http://tinyurl.com/27cok
----------------------------------------------------------
"James Curran" <ja*********@mvps.org> wrote in message
news:%2******************@TK2MSFTNGP10.phx.gbl...

Well, you originally would have had:

SqlConnection conn = new SqlConnection();

Using a higher level class you would have had:

IDbConnection conn = new SqlConnection();

Which means that you would have had to change one spot instead of
two --- or
click "Replace" in the "Find/Replace" dialog half as many times --- or
click
"Replace All" exactly the same number of time.

The bottom line is that the Data Access Layer of your code & the
Database
itself are intimately tied, and trying abstract it just shifts the
problem
somewhere else.

If, for some reason, you absolutely need multiple database support, you
could do something like:

IDbConnection NewConnection()
{
string dbType = GetDbTypeForAppConfig();
switch (dbType)
{
case 'sqlserver':
return SqlConnection();
case 'mysql':
return MySqlConnection();
case 'oracle':
// etc
}
}
}

"flu" <fl***********@nospam.yahoo.com> wrote in message
news:7D*******************@newssvr13.news.prodigy. com...
I recently had to modify an application that had originally been
written
for
SQL Server to work with MySQL. I found myself replacing all of the
declarations for the connection and command objects with their MySQL
class
equivalents.

In hindsight it is clear my code should have been written originally
for
some basic or super SQL class that SQLServer, MySQL, or ODBC,
implements.
Ideally this could be determined even at run time.

This is a problem I'm sure others in this group have run into.

I am interested in how you have approached this problem.

TIA

[flu]

I haven't had the opportunity to test it under load but it seems to perform
just fine under under light conditions. The tools are also adequate. The
..NET support works seamlessly.

I've got used to Enterprise Manager and was glad to see MySQL had a version.

I still prefer SQL Server but if price is a concern, you can't beat free.

flu

"Alvin Bruney [MVP]" <vapor at steaming post office> wrote in message
news:eS**************@TK2MSFTNGP09.phx.gbl...

did you find a performance gain with mysql over sql?

we moved from informix to mysql for our data analysis solutions because
mysql showed at least a 65% performance gain for light to moderate
concurrent load. And it don't cost 20grand per processor. Some of the
advanced features are lacking, but all in all, i'd say it was a sound
investment.

--
Regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
The Microsoft Office Web Components Black Book with .NET
Now Available @ http://tinyurl.com/27cok
----------------------------------------------------------
"flu" <fl***********@nospam.yahoo.com> wrote in message
news:9U*******************@newssvr13.news.prodigy. com...

Not my choice, but the client's. It is free and runs on unix as well as
windows.

flu
"Alvin Bruney [MVP]" <vapor at steaming post office> wrote in message
news:OK**************@tk2msftngp13.phx.gbl...

Curiously, what is the reason for choosing mysql over sql?

--
Regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
The Microsoft Office Web Components Black Book with .NET
Now Available @ http://tinyurl.com/27cok
----------------------------------------------------------
"James Curran" <ja*********@mvps.org> wrote in message
news:%2******************@TK2MSFTNGP10.phx.gbl...
Well, you originally would have had:

SqlConnection conn = new SqlConnection();

Using a higher level class you would have had:

IDbConnection conn = new SqlConnection();

Which means that you would have had to change one spot instead of
two --- or
click "Replace" in the "Find/Replace" dialog half as many times --- or
click
"Replace All" exactly the same number of time.

The bottom line is that the Data Access Layer of your code & the
Database
itself are intimately tied, and trying abstract it just shifts the
problem
somewhere else.

If, for some reason, you absolutely need multiple database support, you
could do something like:

IDbConnection NewConnection()
{
string dbType = GetDbTypeForAppConfig();
switch (dbType)
{
case 'sqlserver':
return SqlConnection();
case 'mysql':
return MySqlConnection();
case 'oracle':
// etc
}
}
}

"flu" <fl***********@nospam.yahoo.com> wrote in message
news:7D*******************@newssvr13.news.prodigy. com...
> I recently had to modify an application that had originally been
> written
for
> SQL Server to work with MySQL. I found myself replacing all of the
> declarations for the connection and command objects with their MySQL
> class
> equivalents.
>
> In hindsight it is clear my code should have been written originally
> for
> some basic or super SQL class that SQLServer, MySQL, or ODBC,
> implements.
> Ideally this could be determined even at run time.
>
> This is a problem I'm sure others in this group have run into.
>
> I am interested in how you have approached this problem.
>
> TIA
>
>

[Alvin Bruney [MVP]]

was just curious, i've been using it for two years and i would definitely
recommend it for moderate concurrent loads

--
Regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
The Microsoft Office Web Components Black Book with .NET
Now Available @ http://tinyurl.com/27cok
----------------------------------------------------------
"flu" <fl***********@nospam.yahoo.com> wrote in message
news:WO**************@newssvr24.news.prodigy.net.. .

I haven't had the opportunity to test it under load but it seems to perform
just fine under under light conditions. The tools are also adequate. The
.NET support works seamlessly.

I've got used to Enterprise Manager and was glad to see MySQL had a
version.

I still prefer SQL Server but if price is a concern, you can't beat free.

flu

"Alvin Bruney [MVP]" <vapor at steaming post office> wrote in message
news:eS**************@TK2MSFTNGP09.phx.gbl...

did you find a performance gain with mysql over sql?

we moved from informix to mysql for our data analysis solutions because
mysql showed at least a 65% performance gain for light to moderate
concurrent load. And it don't cost 20grand per processor. Some of the
advanced features are lacking, but all in all, i'd say it was a sound
investment.

--
Regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
The Microsoft Office Web Components Black Book with .NET
Now Available @ http://tinyurl.com/27cok
----------------------------------------------------------
"flu" <fl***********@nospam.yahoo.com> wrote in message
news:9U*******************@newssvr13.news.prodigy. com...

Not my choice, but the client's. It is free and runs on unix as well as
windows.

flu
"Alvin Bruney [MVP]" <vapor at steaming post office> wrote in message
news:OK**************@tk2msftngp13.phx.gbl...
Curiously, what is the reason for choosing mysql over sql?

--
Regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
The Microsoft Office Web Components Black Book with .NET
Now Available @ http://tinyurl.com/27cok
----------------------------------------------------------
"James Curran" <ja*********@mvps.org> wrote in message
news:%2******************@TK2MSFTNGP10.phx.gbl...
> Well, you originally would have had:
>
> SqlConnection conn = new SqlConnection();
>
> Using a higher level class you would have had:
>
> IDbConnection conn = new SqlConnection();
>
> Which means that you would have had to change one spot instead of
> two --- or
> click "Replace" in the "Find/Replace" dialog half as many times --- or
> click
> "Replace All" exactly the same number of time.
>
> The bottom line is that the Data Access Layer of your code & the
> Database
> itself are intimately tied, and trying abstract it just shifts the
> problem
> somewhere else.
>
> If, for some reason, you absolutely need multiple database support,
> you
> could do something like:
>
> IDbConnection NewConnection()
> {
> string dbType = GetDbTypeForAppConfig();
> switch (dbType)
> {
> case 'sqlserver':
> return SqlConnection();
> case 'mysql':
> return MySqlConnection();
> case 'oracle':
> // etc
> }
> }
> }
>
>
>
> "flu" <fl***********@nospam.yahoo.com> wrote in message
> news:7D*******************@newssvr13.news.prodigy. com...
>> I recently had to modify an application that had originally been
>> written
> for
>> SQL Server to work with MySQL. I found myself replacing all of the
>> declarations for the connection and command objects with their MySQL
>> class
>> equivalents.
>>
>> In hindsight it is clear my code should have been written originally
>> for
>> some basic or super SQL class that SQLServer, MySQL, or ODBC,
>> implements.
>> Ideally this could be determined even at run time.
>>
>> This is a problem I'm sure others in this group have run into.
>>
>> I am interested in how you have approached this problem.
>>
>> TIA
>>
>>
>
>