With Oracle and the DAAB, it seems that you have to prefix parameters with a semicoln “:“, whereas with SQL Server you have to prefix params with an “@“ symbol.  This is a bit of a pain when you are trying to write a data access tier that you can just switch between Oracle and SQL Server with a setting in the config file.  I’d like to know how people are handling this out there in the real world?  I guess you could put a ParameterPrefix setting in a config file, but there must be a better way?


 


Example:


 


Oracle Syntax:


      Public Function GetProjects(ByVal authUser As Integer) As DataTable


            Dim dt As New DataTable


 


            Dim db As Database = DatabaseFactory.CreateDatabase(“bla“)


 


            Dim sqlCommand As String = “select p.project as project,p.projectno as projectno,p.name as name,p.busent as busent “ & _


             “from project p,projectpermission pp where pp.project = p.project and “ & _


            “pp.isauthorised = 1 And pp.authuser = :AuthUser “ & _


             “order by projectno”


            Dim dbCommand As DbCommand = db.GetSqlStringCommand(sqlCommand)


 


            db.AddInParameter(dbCommand, “:AuthUser”, DbType.Int32, authUser)


 


            Using dataReader As IDataReader = db.ExecuteReader(dbCommand)


                  dt.Load(dataReader)


            End Using


 


            Return dt


      End Function


 


SQL Server Syntax:


      Public Function GetProjects(ByVal authUser As Integer) As DataTable


            Dim dt As New DataTable


 


            Dim db As Database = DatabaseFactory.CreateDatabase(“bla“


 


            Dim sqlCommand As String = “select p.project as project,p.projectno as projectno,p.name as name,p.busent as busent “ & _


             “from project p,projectpermission pp where pp.project = p.project and “ & _


            “pp.isauthorised = 1 And pp.authuser = @AuthUser “ & _


             “order by projectno”


            Dim dbCommand As DbCommand = db.GetSqlStringCommand(sqlCommand)


 


            db.AddInParameter(dbCommand, “@AuthUser”, DbType.Int32, authUser)


 


            Using dataReader As IDataReader = db.ExecuteReader(dbCommand)


                  dt.Load(dataReader)


            End Using


 


            Return dt


      End Function