.net WinForms application required to support Oracle & MS SQL Server RRS feed

  • Question

  • Hello,


    We are developing a new .net WinForms application, which can use either Oracle or MS SQL Server 2000 as its database. Application is small and will be used by few users, so performance is not the main issue, but ease of enhancement and maintainability is.


    Possible data access designs:
    1. ODBC .NET Data Provider
    2. OLE DB .NET Data Provider
    3. Oracle .NET Data Provider AND SQL Server .NET Data Provider: and build your own abstraction ?
    4. MS Enterprise Library - Data Access Application Block
    5. O/R Mapping e.g nHibernate
    6. OTHER Options ? LINQ ?


    ODBC and OLE DB are old technologies and would not support new features. (http://support.microsoft.com/kb/244661/)

    Oracle/SQL Server .NET Data Provider have best performance, but performance is not an issue here.


    MS Enterprise Library: Does it truly abstract out underlying database provider ?
    Are there issues with it while using Oracle .NET Data Provider ?
    How high is learning curve for developers ?


    O/R Mapping: Is use of an external O/R framework justified for a small application, just to support Oracle & MS SQL Server ? If so, which is best O/R Mapping framework for this application ?


    Thank You.

    Sunday, December 2, 2007 3:50 PM

All replies

  • Hi,

    I would stay far from option 1 and 2. In my opinion the enterprise library is overly complicated, takes too much time to learn and does not offer much benefit compared to alternatives (but it has no issues with oracle provider as far as I know). 

    For option 5, NHibernate would take probably too much time to learn for a small project, but you could consider it an investment for future projects.  Also consider llblgen pro and the codesmith DAL generator.

    Option 6: Linq to SQL currently only support sql server. Linq to entities which will ship in the sql server 2008 timeframe will support any database although initial support for other db will probably be sketchy. 

    Personally I would go with option 3 because the .net framework already has all the abstraction necessary to do provider independant code (ie the DbProviderFactories).

    Another option would be to use dataset and manually code a 2nd table adapter that accesses an Oracle provider.  That isn't difficult to do, but it's a maintenance problem since every change in the designer must be manually repeated.

    Hope this helps,
    Sunday, December 2, 2007 5:50 PM
  • Thanks.

    I explored both enterprise library and option 3 (custom abstraction layer). With two set of stored procedures one for each db, we can accomplish goal.


    About .NET Enterprise Library, I wrote a method using Parameterized Queries to run against MSSqlserver & Oracle.
    Both db have identical table 'tbl_test' with two colums test_id (int) & test_data (varchar).


        Public sub printDbData() 
            Dim db As Database
            db = DatabaseFactory.CreateDatabase()
            Dim sqlCommand As String = "Select * From  tbl_test where test_id=:testId"

            Dim dbCommand As DbCommand = db.GetSqlStringCommand(sqlCommand)
            db.AddInParameter(dbCommand, "testId", DbType.Int32, 2)
            Dim dataReader As IDataReader = db.ExecuteReader(dbCommand)
            While (dataReader.Read())
            End While
        End sub


    The above code ran successfully against ORACLE, but gave error against MSSqlserver.
    For MSSqlserver I have to change SQL query to :
     "Select * From  tbl_test where test_id=@testId"


    How do I write database independent Parameterized Queries in Enterprise Library AND custom abstraction layer. (For custom AL, parse sql ? better way ?)


    Thank You.


    Wednesday, December 5, 2007 3:10 PM
  • I don't think that the entlib provides something like this.  The spring.net data access components do however.  What you could do is Place {0} at the place of the parameter and use String.Format to insert the correct parameter...



    Thursday, December 6, 2007 12:19 AM