Question Need solution for application using both SQL Server and MS Access ???

[kmn104]

Hi all,
I'm writing an application which allows user choose database of SQL Server or MS Access.
My problem is that I must check the choice of user each time I manual with database, if SQL Server, i must create three objects of SQLConnection, SQLDataAdapter, SQLCommandBuilder, else I must create three objects of OLEDB...

I need your solution or your opinion to help me to give a idea to check create them once and use through the solution.

Please ask me if you don't understand my question ?

Thanks !

 

[jmcilhinney]

Two choices:

1. Use OleDb only and just change the Provider in the connection string depending on the data source.

2. Use a factory to create all your data access objects. Follow the Blog link in my signature and find my post on DB-Independent DAL for more info.

 

[kmn104]

Thank you very much !
I have just used your first option and i'm doing follow the second.
Also, I have an other way to do that, would you mind giving your opinions about that for me. My solution is that :
- I create three global variables with the data type of System.Data.Common.Db...
- Then when user choose the DBMS, I convert them to SQLClient or Oledb data type
- When I manual with database, I just call the global function to work with dataset, such as : loadDataSet, updateDataset... so my Dataset variable will work through the project and doesn't depend on the Connection, Adapter and CommandBuilder.

Thanks for your consider !

 

[jmcilhinney]

What you suggest there is exactly what you'd do if you followed my second option. There's no "conversion" though. In code you would declare a DbConnection variable and you would always use it as a DbConnection. You'd created an object by calling DbFactoryProvider.CreateConnection. The difference would be what factory you used, such that your variable referred to an OleDbConnection or a SqlConnection. Your code wouldn't have to care though, because it would just treat it as a DbConnection all the time. My blog shows you how to select a factory.

John McIlhinney's .NET Developer Blog: Database-independent Data Access Layer

 

[kmn104]

Yeah, I did it, DbFactory is so great.
Glad to learning from you. Thanks again

 

[Tom]

Is there any difference in efficiency between a generic provider such as this compared to coding for the specific database providers?

 

[kmn104]

Hi Tom,

In my opinion, the benefit of using a generic provider help me to create and use the data object (connection, dataAdapter, commandbuilder) flexibly that only set the provider just one time (set the name of Provider and connection string) when the user choose the Database Management System (SQL Server, Access...). So you can code your project and don't care about what kind of your data object except the querries.

What do you think about it ?

 

[Tom]

Oh I agree about the flexibility it is a great advantage. I was just wondering if it was worth using if the need for that flexibility was unlikely and/or whether there were any disadvantages.

 

[jmcilhinney]

Is there any difference in efficiency between a generic provider such as this compared to coding for the specific database providers?

There's no difference in efficiency because you ARE using a specific provider. For instance, all the variables may be of types from System.Data.Common but, if you use a SqlClientFactory, every object is going to be of a type from System.Data.SqlClient. You're NOT using generic objects, just generic code. That said, you obviously can't make use of features that are specific to one provider in that generic code, e.g. SqlClient's asynchronous methods. You can only use functionality directly inherited from the common base classes.

The reason for creating this generic code in the first place is so that you can write your DAL once and once only, then use it every project you ever create from that point on, no matter what data source you happen to be using. Code reuse is the point and the only point of using a DbFactoryProvider.

 

[Tom]

Thanks JM, its definitely interesting and I look forward to digging into it.

 

[cjard]

Polymorphism is so misunderstood..

 

[jmcilhinney]

Polymorphism is so misunderstood..

Like so many OO concepts it works in exactly the same way as the real-world process it was modelled on that we use every day without thinking, but people don't get the connection because their programming objects aren't tangible. It's funny that OOP should be completely intuitive because it is based on real-world object behaviour but so many people try to treat it differently and end up their own worst enemy when it comes to understanding OOP. I lament along with you.