Making your database code less dependent on the database system

[kevininstructor]

(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.

      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

      Dim Identifier As String = "22222"
      Dim DemoCommand = _
          <SQL>
          SELECT APPRAISERID As Identifier, 
                 NAME, 
                 SWITCH 
          FROM APPRAISERREF
          WHERE Identifier = <&#37;= Identifier %>
          </SQL>.Value

For normal param queries we follow the following flow

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