Database strategy using .Net, ADO.net ans MS SQL

[CedricB]

Hi,

I'm trying to put together the architecture for a new application. I'm using .net 1.1, ado.net and ms sql server, but I guess my questions also apply to other environments.

A sketch of my situation:

1. I have a database with tables
2. For most tables, I have a class in a code library to control access to it
3. I'm currently building an asp.net application, but I might need to access the same data later on from other environments, e.g. windows application.

What is the best strategy for my database connections? Some thoughts already

1. I won't have my classes read the connection string from the web.config since this will limit them to a web environment only. Hence, I guess I will pass them as a parameter in my constructor method.

2. What to do with transactions that may stretch different classes? Pass an opened connection to the class through a constructor? Of course this violates the open late and close early guideline for database connections. Any alternatives?

3. I'm not currently taking support for other databases in my requirements, but what if I do? How does this affect my strategy?

4. What is the best way to get a list of items, e.g. all users when I have a user class? Passing a dataset is the easiest and quickest, but exposes the database structure outside the user class. Passing a collection of user objects means that I have to create a user object for each row in the dataset before it is returned from the class.

I'm looking forward to hear your thoughts, experiences, strategies, ...

Cedric

[egurdarshan]

Dear CedricB,

Please see my comments below for your question and FYI, in most of my project development I have always following this strategy:

1. I won't have my classes read the connection string from the web.config since this will limit them to a web environment only. Hence, I guess I will pass them as a parameter in my constructor method.

This problem only happen i.e. using connection string from config file when you not using Dot net as your base for application development. If you want to use your classes with Window Based application then also you can access the connection string from config file which is normally created by developer in his project and its name is always start with your application exe file name e.g. if your application name is MyApp and its EXE file name is MyApp.exe then you have to create a config file with name MyApp.exe.config and store in your application EXE folder.

And code for accessing connection string is still be same i.e.
Its a C# code.
System.Configuration.ConfigurationSettings.AppSettings["yourConnectionString"].ToString();


2. What to do with transactions that may stretch different classes? Pass an opened connection to the class through a constructor? Of course this violates the open late and close early guideline for database connections. Any alternatives?

This problem can be solved in following ways:
A. Create a instance of your Database connection in your main class method from which you want to start the database update process and after creating connection object, start a transaction and then in each class method which need to be called from main class method pass connection and transaction object and use them with your database operation.

B. Use COM+ services i.e. EnterpriseServices and mark your class with attribute [Transaction(TransactionOption.Required)] and mark your method as [AutoComplete(true)]. This method is required following extra activity:
i). Knowledge of COM+ Services
ii). Installation and Management of COM+
iii) Its a unmanaged code

From above two option, i am always prefer first one because it is more easy to implement and need not have unmanaged code handling.


3. I'm not currently taking support for other databases in my requirements, but what if I do? How does this affect my strategy?

If you want to go for multi database then plan your architecture from inital phase otherwise you will face problem during conversion from one database to other database.


4. What is the best way to get a list of items, e.g. all users when I have a user class? Passing a dataset is the easiest and quickest, but exposes the database structure outside the user class. Passing a collection of user objects means that I have to create a user object for each row in the dataset before it is returned from the class.

I am not clear with your requirement, please elaborte more..

[CedricB]

Hi Gurdarshan,

Thank you for your reaction. Good to hear somebody else's opinion.

What I mean with my last point is the following. Suppose I have a class called Book. It has properties and a load and save method to manage a book. But what with a method like GetAllBooks? Should it return a collection of book objects, or a dataset? In the first case, I don't have to know what the database looks like because I'm still working with my book class, but it takes time to convert my dataset into book objects. In the second case, I don't have this overhead but by passing the dataset, the user of the method must also know the database structure since he will get a dataset.

I hope this makes it clear?

Cedric

[egurdarshan]

Dear CedricB,

In this scenerio i will always prefer Dataset because it is easy to maintain and require lesser work on extra functionality like filter, fetch particular detail and easily bind with datagrid.

But if you think you can work with class object which is MFC model then that will provide more control on your data instead of dataset which can be easily tempered by the user of the dataset.

So, before going ahead with your application coding it will be good for your project health if your first decide your architecture and then design your base classes & interfaces through which you implement the logic and provide access to your data to outside world or main application.