none
Universal Data Access RRS feed

  • Question

  • I want to build a system where data base access is Centralised, and be able to access multiple database servers using the same Code so I can avoid to manually change/configure every application and report I have, if I want to customise my applications to connect to mysql or sql server or oracle, etc...

    "dotConnect Universal" seems to be one option as I can avoid writing conditional code for different data access providers.

    Are there any similar third party components? 

    Thanks

     

     

    Sunday, March 13, 2011 1:45 PM

Answers

All replies

  • You can use Entity Framework to Abstract Database from application code. Just pass the different connection string as parameter to EntityDataContext.

     

    string oracleconstr = "connection string for oracle";

    string mysqlconstr = "connection string for mysql";

     

    var db = new EntityModel1(oracleconstr);

    //Linq code to query oracle database.

    var db2 = new EntityModel1(mysqlconstr );

    //Linq code to query mysql database.


    -- Sriram.S getsrirams.blogspot.com
    • Proposed as answer by getsrirams Monday, March 14, 2011 12:18 PM
    Monday, March 14, 2011 12:18 PM
  • This option is good to setup the whole process by code and have more control.

    however what i want to do is to enable my application to either have it's main database as oracle OR mysql etc without having to change any of the code, from client to client. what "dotConnect Universal" seems to provide is a centralised solution and i would like to know if anyone uses this kind of solution or any other similar component.

    also "dotConnect Universal" provides direct access to oracle, mysql, (without any installation drivers) whereas with the suggested solution i would have to install odbc drivers right? 

    Monday, March 14, 2011 12:32 PM
  • OpenLink is another option. Keep in mind that any product such as this which has a unified programming interface, will work with a database specific provider (or middleware), which could be .NET, OLEDB, ODBC, JDBC, etc., for each database management system.

    Also, once you start using SQL in your app the chances increase that there will be support compatibility issues amongst different database systems. This is one reason for using stored procedures, since most current database management systems support them to some extent, and they allow you to hide the database implementation from the application code.


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Monday, March 14, 2011 5:52 PM
  • anyway in order to use dotConnect Universal. you need to install it on client machine. Moreover there are lots of licensing fees involved in dotconnect universal. ODBC is not officially supported by Entity Framework. you need to have Entity Framework enabled ADO.NET provider for your Database.In case of entity framework you need to just put the required DLLs in Bin directory of your application. 

    Or

    You can use built in IDbConnection,IDbCommand interfaces for your data access. but  at the cost of loosing your provider specific features. 

    if your application fits the above requirement. you can go ahead and use it. here is the sample code.

     

    DbProviderFactory dbf = DbProviderFactories.GetFactory(provider);
    IDbConnection con = dbf.CreateConnection();
    con.ConnectionString = constr;
    con.Open();
    IDbCommand cmd = con.CreateCommand();
    cmd.CommandText = "SELECT * FROM T1";
    IDataReader rdr = cmd.ExecuteReader();
    while(rdr.Read()) {
    int f1 = (int)rdr[0];
    string f2 = (string)rdr[1];
    Console.WriteLine(f1 + " " + f2);
    }
    con.Close();


    -- Sriram.S getsrirams.blogspot.com
    Monday, March 14, 2011 5:53 PM
  • so dotConnect Universal does not get included/compiled within the exe itself?
    Monday, March 14, 2011 6:00 PM
  • Thanks for the link. It seems they miss SQLite support. Sounds strange...
    Monday, March 14, 2011 6:03 PM
  • It looks like Devart's dotConnect Universal works either with middleware providers they have developed (in their Pro version), or existing middleware providers. That means you would need to install the provider for any database that they have not included in the product, or that is not in the .NET Framework or not included with the OS. It looks like they have built-in .NET providers for Oracle, MySQL, PostgreSQL and SQLite.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Monday, March 14, 2011 6:11 PM
  • Thanks for confirming. Oracle, MySQL, PostgreSQL, SQLite + MS SQL are the ones I need.
    Monday, March 14, 2011 6:35 PM
  • Nothing buit in, but it would still work with .NET, OLEDB or ODBC SQLite providers.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Monday, March 14, 2011 6:39 PM
  • I have also come across this opensource solution in my search: http://habanerolabs.com
    Monday, March 14, 2011 7:18 PM