Retrieving the Parameter Marker Format When Using Generic Database Factories
With .NET 2, Microsoft introduced something called DbProviderFactories that allows you to work with databases in a generic fashion. That is, you don't create strongly typed objects like a SqlConnection or an OleDbConnection at design-time, but defer the decision what object to create till run-time. This way, you can write code that works against a number of different databases, like SQL Server, Oracle and OleDb databases like Microsoft Access.
One of the big differences between how these databases operate is the way you must name the parameters you send to a stored procedure or query. For example, SQL Server uses the @ symbol (@userName) and even allows you to leave out a prefix altogether. Microsoft Access in turn uses a question mark without a specific name while Oracle uses a colon as the prefix. Now, how do you deal with these differences when you don't know what database you're working with till run-time?
Introduction
Let's briefly look at how these generic database factories work. Take a look at the following example:
ConnectionStringSettings mySettings = ConfigurationManager.ConnectionStrings[0]; string myProviderName = mySettings.ProviderName; DbProviderFactory myFactory = DbProviderFactories.GetFactory(myProviderName); DbConnection myConnection = myFactory.CreateConnection(); myConnection.ConnectionString = mySettings.ConnectionString; DbCommand myCommand = myConnection.CreateCommand(); myCommand.CommandText = "SELECT Id, Description FROM MyTable"; myCommand.CommandType = CommandType.Text; myConnection.Open(); using (DbDataReader myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)) { while (myReader.Read()) { MessageBox.Show(myReader[0].ToString()); } myReader.Close(); } myConnection.Close(); myConnection.Dispose();
This code first retrieves a ConnectionStringSettings object based on the first available connection string in the config file. I am using the first connectionstring for demo purposes only. In your own code, you could access the connectionstring by its name instead. From this object, it uses the ProviderName property to determine the type of the provider. Possible values for this property are System.Data.SqlClient, System.Data.OleDb and so on. It then creates a DbProviderFactory object that, as a real-world factory, is used to create instances of other objects, like DbConnections and DbCommands. The code then builds up a DbCommand object, executes a SQL statement and loops through the results from the query.
In my latest book, ASP.NET 2.0 Instant Results I devoted an entire chapter to working with database factories. Chapter 6 - The Wrox Blog has a generic data access layer that works with SQL Server and an Access database. You'll find a lot more details and explanation about database factories in that chapter.
One problem that this chapter didn't solve very well was the parameter problem. The Blog uses a number of stored procedures / queries that are parameter driven. So, I needed a way to determine the right parameter name and constructed a simple Select Case statement that returned the proper format, like this:
Public Shared Function ReturnCommandParamName( _ ByVal paramName As String) As String Dim returnValue As String = String.Empty Select Case AppConfiguration.ConnectionStringSettings.ProviderName.ToLower() Case "system.data.sqlclient" returnValue = "@" & paramName Case "system.data.oledb" returnValue = "?" Case Else Throw New NotSupportedException("The provider " & _ AppConfiguration.ConnectionStringSettings.ProviderName & _ " is not supported") End Select Return returnValue End Function
While this code certainly works, it has one big drawback: you'll need to change it whenever you need to support another data provider, like Oracle.
Fortunately, all the information you need to retrieve the parameter info at run-time is already available for you on your system. Information like the parameter format marker can be queried by executing GetSchema against an open DbConnection object. The following code snippet shows how you can do this:
ConnectionStringSettings mySettings = ConfigurationManager.ConnectionStrings[0]; string myProviderName = mySettings.ProviderName; DbProviderFactory myFactory = DbProviderFactories.GetFactory(myProviderName); DbConnection myConnection = myFactory.CreateConnection(); myConnection.ConnectionString = mySettings.ConnectionString; myConnection.Open(); string parameterMarker = myConnection.GetSchema("DataSourceInformation") .Rows[0]["ParameterMarkerFormat"].ToString(); myConnection.Close(); myConnection.Dispose();
This code is similar to the previous snippet: it uses a DbProviderFactory to create and open a generic DbConnection object. The GetSchema method is then used to retrieve the parameter marker format for the current connection type. For SQL Server, this returns {0}, while for a Microsoft Access database this returns ?. You can use this format in combination with the String.Format method to get the right parameter name. For example:
string paramName = String.Format(parameterMarker, "firstName");
For SQL Server, this will return firstName. For an Access database, you'll get ? and for an Oracle database you get :firstName.
You shouldn't call this code every time you need a parameter name because it takes quite some resources. Instead, you should cache the value somewhere, or store it in a static variable accessible by your DAL code.
Download Files
Download the Source Code for this Article
The download contains a simple Windows Forms application with the code shown above to retrieve the parameter marker format. When the application is loaded, a drop down list is filled with all the available connection strings from the web.config file. If you want to add another connection string, don't forget to add the providerName attribute that should point to a valid data provider type.
Where to Next?
Wonder where to go next? You can post a comment on this article.
Links in this Document
Doc ID | 405 |
Full URL | https://imar.spaanjaars.com/405/retrieving-the-parameter-marker-format-when-using-generic-database-factories |
Short cut | https://imar.spaanjaars.com/405/ |
Written by | Imar Spaanjaars |
Date Posted | 09/19/2006 21:57 |
Listened to when writing | Warsaw by Joy Division (Track 1 from the album: Heart And Soul (CD 3 - Studio Rarities & Unreleased)) |
Comments
Talk Back! Comment on Imar.Spaanjaars.Com
I am interested in what you have to say about this article. Feel free to post any comments, remarks or questions you may have about this article. The Talk Back feature is not meant for technical questions that are not directly related to this article. So, a post like "Hey, can you tell me how I can upload files to a MySQL database in PHP?" is likely to be removed. Also spam and unrealistic job offers will be deleted immediately.
When you post a comment, you have to provide your name and the comment. Your e-mail address is optional and you only need to provide it if you want me to contact you. It will not be displayed along with your comment. I got sick and tired of the comment spam I was receiving, so I have protected this page with a simple calculation exercise. This means that if you want to leave a comment, you'll need to complete the calculation before you hit the Post Comment button.
If you want to object to a comment made by another visitor, be sure to contact me and I'll look into it ASAP. Don't forget to mention the page link, or the Doc ID of the document.