1 Attachment(s)
Making your database code less dependent on the database system
(this post has been moved from the FAQ article Database - Why should I use Parameters instead of putting values into my SQL string?)
In regards to
Your code is much more specific to the database system you are currently using (eg: Access or SQL Server)
Suppose you have Oledb as your database engine then upgrade to SQL Server. If you code specifically for one database engine then you need to change the code to the other database engine.
If you foresee this happening consider using IDb interface when working with databases when you may change the database engine. Having a class (see attachment) which handles the database engine can control changing providers easily.
Conceptually the following code works for all known database engines supported by the Framework, we have done SQL Server, Oracle, DB2, OleDb and ODBC.
Code:
Dim Table As New DataTable
Dim cn As IDbConnection = Nothing
Dim dc As DatabaseConnections = DatabaseConnections.GetInstance()
cn = dc.MainConnection(DataBaseEnvironment.DefaultEnvironment)
Using cn
Dim cmd As IDbCommand
cmd = cn.CreateCommand
cmd.CommandText = _
<SQL>
SELECT ID As Identifier,
NAME,
SWITCH
FROM APPRAISERREF
</SQL>.Value
cmd.Transaction = Nothing
cmd.CommandTimeout = 6
cmd.Connection = cn
Dim dr As System.Data.IDataReader = cmd.ExecuteReader
Table.Load(dr)
bsSource.DataSource = Table
bsSource.Sort = "APPRAISERNAME"
End Using
Using an XElement value (a string) means no concatenation, double quotes or stringbuilders. This is not the recommended method for param queries but can easily be done if you want.
Simple example with simple parameter
Code:
Dim Identifier As String = "22222"
Dim DemoCommand = _
<SQL>
SELECT APPRAISERID As Identifier,
NAME,
SWITCH
FROM APPRAISERREF
WHERE Identifier = <%= Identifier %>
</SQL>.Value
For normal param queries we follow the following flow
Code:
Dim cn As IDbConnection = Nothing
Dim dc As DatabaseConnections = DatabaseConnections.GetInstance()
cn = dc.MainConnection(DataBaseEnvironment.DefaultEnvironment)
Using cn
Dim cmd As IDbCommand
cmd = cn.CreateCommand
Dim EquipmentParam As IDbDataParameter = cmd.CreateParameter
EquipmentParam.DbType = DbType.String
EquipmentParam.ParameterName = "EQUIP"
EquipmentParam.Value = Me.Equipment
cmd.Parameters.Add(EquipmentParam)
Dim ManufacturerIdentifierParam As IDbDataParameter = cmd.CreateParameter
ManufacturerIdentifierParam.DbType = DbType.Int32
ManufacturerIdentifierParam.ParameterName = "MANUF"
ManufacturerIdentifierParam.Value = Me.ManufacturerIdentifier
cmd.Parameters.Add(ManufacturerIdentifierParam)
Dim WhereParam As IDbDataParameter = cmd.CreateParameter
WhereParam.DbType = DbType.Int32
WhereParam.ParameterName = "MDL_ID"
WhereParam.Value = Me.Identifier
cmd.Parameters.Add(WhereParam)
cmd.CommandText = "UpdateEquipment.sql".ResourceContents
cmd.Transaction = Nothing
cmd.Connection = cn
Dim Rows As Integer = cmd.ExecuteNonQuery
End Using
UPDATE tbl_MDL
SET
EQUIP= @EQUIP,
MANUF=@MANUF,
WHERE
MDL_ID = @MDL_ID
In short, more work, more flexible.
I will build a simple demo application that allows you to see the above ideas work between SQL-Server and MS-Access using thier native provider before New Years eve 2010