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 = <&#37;= 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