You are hereDatabase-independent .NET programs

Database-independent .NET programs


By Mel - Posted on 14 March 2010

A few years ago in one of the companies I worked for, we had an ADO.NET 1.0 application that used MS Access as its database system. We needed to port it to MS SQL Server and so we had to modify the source code. What we did was to write data provider specific code each for MS Access and MS SQL Server, similar to the following sample code. It worked but it was not an elegant solution.

  1. If strDBMS = "MS SQL Server" Then
  2. Dim sqlCmd As SqlClient.SqlCommand
  3. sqlCmd = sqlCn.CreateCommand()
  4. sqlCmd.CommandText = pSQL
  5. Dim dr As SqlClient.SqlDataReader = sqlCmd.ExecuteReader()
  6. Else
  7. Dim oleDbCmd As OleDb.OleDbCommand
  8. oleDbCmd = oleCn.CreateCommand()
  9. oleDbCmd.CommandText = pSQL
  10. Dim dr As OleDb.OleDbDataReader = oleDbCmd.ExecuteReader()
  11. End If

Now, in my current company, I need again to port an application from one database system to another, this time from MS SQL Server to Oracle Database. Fortunately, there is now an easy way to write data provider independent code in ADO.NET 2.0 using the Provider Factory Model.

Provider Factory Model Overview

You can think of the provider factory as a generic namespace for different data-related objects such as the Connection, Command, and DataReader objects. Instead of declaring your objects using types under provider-specific namespaces such as System.Data.SqlClient or System.Data.OleDb, you will use the abstract base classes under the common namespace System.Data.Common.

The classes under System.Data.Common are however abstract so you cannot instantiate new objects from them. You will have to create a factory object that will provide concrete database objects. The DbProviderFactories class, also declared under System.Data.Common, provides a static method for creating these factory objects.

Sample Code

Here’s a VB.NET sample code for issuing a simple Select statement using the Provider Factory Model. In a real world application, you would have to specify the provider name and the connection string from a configuration or an ini file.

To run the sample code, create a Windows Forms Application project in Visual Studio, add a button to a form and associate the following code. Don’t forget to change the connection string to an appropriate one.

I have included comments to explain what the code does. If you have any questions about the sample code, please leave a comment to this article.

  1. Imports System.Data
  2. Imports System.Data.Common
  3. Imports System.Collections.Generic
  4.  
  5. Public Class Form1
  6. Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
  7.  
  8. 'Provider name for Oracle
  9. Dim myDataProviderName As String = "System.Data.OracleClient"
  10. 'Connection string
  11. Dim myConnectionString As String = "Data Source=testdb;User Id=testuser;Password=testpassword;"
  12.  
  13. Try
  14. 'Create a DB Object Factory
  15. Dim gDBFactory As DbProviderFactory = DbProviderFactories.GetFactory(myDataProviderName)
  16.  
  17. 'Get a connection object from the factory
  18. Dim sqlCn As DbConnection = gDBFactory.CreateConnection
  19.  
  20. 'Open a DB connection
  21. sqlCn.ConnectionString = myConnectionString
  22. sqlCn.Open()
  23.  
  24. 'Retrieve a row of data from a table
  25. Dim sqlCmd As DbCommand = sqlCn.CreateCommand()
  26. sqlCmd.CommandText = "SELECT field1 FROM table1 where field2 = 'abc'"
  27. Dim dr As DbDataReader = sqlCmd.ExecuteReader()
  28. dr.Read()
  29.  
  30. 'Display the value of a field in the row
  31. MessageBox.Show(dr.Item("field1"))
  32.  
  33. 'Close the connection
  34. sqlCn.Close()
  35. sqlCn.Dispose()
  36.  
  37. Catch ex As Exception ' catches any error
  38. MessageBox.Show(ex.Message.ToString())
  39. Finally
  40. 'put clean-up code here in a real application
  41. End Try
  42. End Sub
  43. End Class

Hi,
I would like to know that if Nhibernate could be used in any way to acheive data independance?
I have joined an organization for my final year internship and the have database in postgres and SqlServer and now the want to transform it into oracle with data independance. and they have asked me to use Hibernate which i guess is known as Nhibernet in .NET,
waiting for you reply,
Thank you.

Bojagar, thanks for visiting this site. Sorry, I'm not really familiar with Nhibernate, but judging from its description in Wikipedia, it seems that it doesn't really directly address the problem of dependency on a specific RDBMS implementation. It seems that it is more of a solution to the object-relational mapping problem.

Post new comment

The content of this field is kept private and will not be shown publicly.
Image CAPTCHA
Enter the characters shown in the image.