August 7, 2008 04:47pm pdt

03.04.2008 at 07:51AM PST, ID: 23212946

	[x] Attachment Details

Switching between database providers in web.config / Data Access Layer

Asked by gjok in .NET, Microsoft Visual C#.Net

Tags: c#, asp.net

Hi.
I've been looking at a basic example of a web application that allows you to specify between MS Access or SQL Server in the web.config. (see snippet part 1 below)

Now, as both these providers use ifferent sql syntax, I understand that I will need to parse my sql statements accordingly as shown in snippet part.3.

However, how do I handle things like "getdate()" which is not supported by Access? Am I to have separate code blocks for handling Access and sql server? If so isnt the following line kind of pointless?:

param.ParameterName = DalHelpers.ReturnCommandParamName("startDate")

-or- should I just NOT use "getdate()" and build the date manually?

Also, am I correct in assuming that the code in snippet part.2 will handle the dates (sql uses a quote and access uses a '#' symbol)
Start Free Trial

         
           
             1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:

           
           
             Part.1 - Web.config:
 
  <appSettings>
    <add key="DefaultConnectionString" value="SqlServerConString" />
  </appSettings>
 
  <connectionStrings>
    <add name="SqlServerConString" connectionString="server=(local)\SqlExpress etc, etc, " providerName="System.Data.SqlClient" />
    <add name="AccessConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Inetpub\wwwrootI etc, etc ;" providerName="System.Data.OleDb" />
  </connectionStrings>
 
 
 
Part.2 - Code
      param = myCommand.CreateParameter()
      param.ParameterName = DalHelpers.ReturnCommandParamName("startDate")
      param.DbType = DbType.DateTime
      param.Value = startDate
      myCommand.Parameters.Add(param)
 
 
 
Part.3 - Class:
 
  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
           
         

Keywords: Switching between database provider…

	Title
1	How can I hash the password in a we…
2	Web.config...
3	GetDate??
4	WebConfig file
5	problems with web.config
6	WebConfig File

Loading Advertisement...

20080716-EE-VQP-32 / EE_QW_2_20070628

Switching between database providers in web.config / Data Access Layer

Asked by gjok in .NET, Microsoft Visual C#.Net

Tags: c#, asp.net

About this solution