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
posted on Monday, February 12, 2007 1:22 PM