how to develop dbms independent application using Microsoft tech?

Question

I need to write an app in such a way that it should not worry about the incoming DBMS. It can be Oracle, PostgreSQL, MySql etc..

The local dbms will be using SQL Server 2008 into which I will be loading data.

What is needed/planning to do...

The user has textbox(s) where he is able to create/save the connection string vales in the local database/config file for each possible dbms.
The application then should use this to form the connection string and connect to the incoming database one at a time or concurrently if possible.
Retrieve the values and put them in the local database.
No need to write data to the incoming database.Only 1-way.

In short a database independent application to only read values based on a set query and put them in the specified database.

Is it possible to do this or sounding magical?

Any solutions/sample/technique I can develop on?

Application is based on Silverlight + ASP.NET tech.

score 3 · Accepted Answer · edited May 23 '17 at 12:01

You should search and study about design patterns. check out this link. Also check out LLBLGen
If you want to opt for your own solution then you have to worked in layers. Define your Data Access Layer (DAL) loosely coupled. One way of doing it could be using interfaces. Define an Interface for database connection and then let each class for DBMS to implement it. Then you can get the database connection string on something on the following lines.
Source:

public static IDbConnection GetConnection(string connectionName)
    {
      ConnectionStringSettings ConnectString = ConfigurationManager.ConnectionStrings[connectionName];
//Use a Factory class, to which you pass the ProviderName and 
//it will return you object for that particular provider, you will have to implement it
      DbProviderFactory Factory = DbProviderFactories.GetFactory(ConnectString.ProviderName);
      IDbConnection Connection = Factory.CreateConnection();
      Connection.ConnectionString = ConnectString.ConnectionString;
      return Connection;
    }

Then you can use it like:

public static DataTable GetData()
{
  using (IDbConnection Connection = GetConnection("SiteSqlServer"))
  {
    IDbCommand Command = Connection.CreateCommand();
    Command.CommandText = "DummyCommand";
    Command.CommandType = CommandType.StoredProcedure;

    Connection.Open();

    using (IDataReader reader = Command.ExecuteReader())
    {
      DataTable Result = new DataTable();
      Result.Load(reader);
      return Result;
    }
  }
}

+1: If you'd prefer not to have the overhead of an ORM, of you'd rather write your own, then this would be a good place to get started. — dwerner, May 06 '12 at 20:16
Thanks every1. I'm looking into the book now and also thanks for the sample pattern.seems good.. Hectic schedule with assignments..:D..late reply — Searching, May 22 '12 at 19:24
@dwerner if you are still writing your own orm/data access code today, you're not really clever: you'll waste a lot of time, running into the same mistakes everyone else has already solved, and you have to support your own code with updates. — Frans Bouma, Jan 12 '13 at 09:06
@Frans Bourna - I do agree in this case, because why reinvent the wheel, right? In platforms like .NET, this has been done well with NHibernate. In some other, newer platforms, like Node.js, for instance, it hasn't been solved to my satisfaction, so I struck up a project to do that there. In the end, yes I agree that you shouldn't reinvent the wheel, unless all the other wheels don't turn to your satisfaction. — dwerner, Jan 16 '13 at 01:47

score 2 · Answer 2 · answered May 06 '12 at 19:20

2

This is rather basic requirement and you should be able to easily fulfill it using one of the Object Relational Mapping frameworks.

If I am to recommend one - use NHibernate.

Silverlight + ASP.NET has nothing to do with the way you persist your data as long as you persist it at the server side. For the Silverlight application, you'll also need some WCF web services to communicate between the client and the server.

answered May 06 '12 at 19:20

Wiktor Zychla

47,367
6
74
106

+1: ORMs are largely what you'll want to implement yourself in a lot of cases anyway, so this could be a huge win and time saver. On the other hand, ORMs can be complex for beginners. I can also recommend NHibernate myself. – dwerner May 06 '12 at 20:17

score 0 · Answer 3 · edited May 23 '17 at 12:16

0

Along the same line with other answers, I would strongly recommend you look at the following question https://stackoverflow.com/questions/132676/which-orm-for-net-would-you-recommend it should give you very good break down of ORM out there.

edited May 23 '17 at 12:16

Community

1
1

answered May 06 '12 at 19:43

Nair

7,438
10
41
69

score 0 · Answer 4 · answered May 06 '12 at 20:29

To build on what the other answers have said. Think about your problem in this way.

You have "front end (asp.net/silver light)" <-> "data storage"

What you want is a step in the middle

"front end" <-> "database ignorant layer" <-> "data storage"

This middle layer may be an ORM, but all an ORM really is (besides the 100's of thousands of lines of code and years of refined experience) is an implementation of the repository and unit of work patterns.

how to develop dbms independent application using Microsoft tech?

4 Answers4