Article:		Transparent Database Access with ADO.NET
Subject:		Parameters
Date:		2003-02-05 19:41:27
From:		anonymous2
Oracle and SQLServer data providers have a different way to specify parameter markers in sentences: select * from customer where customerid = @customerid vs select * from customer where customerid = :customerid Do you have a good way to abstract those differences? Thanks

Showing messages 1 through 2 of 2.

• Parameters
  2003-06-09 20:00:04  anonymous2 [Reply | View]
  
  
  One way a manage this problem is using String.Format() function. For example:
  
  private const string SQL_GET_RECORD = "SELECT * FROM UserTbl WHERE id = {0} AND name='{1}'";
  
  You can format your string like this:
  String.Format(SQL_GET_RECORD_BY_ID,123,'jesse');
  
  This will produce the following string:
  SELECT * FROM UserTbl WHERE id = 123 AND name='jesse'
  
  Hope it helps,
  
  Jesse

• Parameters
  2003-02-06 10:25:06  neh123us@yahoo.com [Reply | View]
  
  
  A good way to hide such differences in the SQL syntax is to use stored procedures and bind parameters using the IDbDataParameter interface. Another method would be to store the parameter prefix character in a config file, but this would not abstract the differences in join syntax, set operations, and other vendor specific syntax.
  
  I hope this helps.