vendredi 16 mars 2007

DbCommand - SqlCommand : Best Practice

DON'T :

string connectionString="..."

SqlConnection sqlCon = new SqlConnection(connectionString);
SqlCommand sqlCmd = sqlCon.CreateCommand();
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = "...";
SqlParameter sqlPar = sqlCmd.CreateParameter();
sqlPar.ParameterName = "...";

DO :

string connectionString="..."
DbConnection dbCon = new SqlConnection(connectionString);
IDbConnection dbCon = new SqlConnection(connectionString);
DbCommand dbCmd = dbCon.CreateCommand();
IDbCommand dbCmd = dbCon.CreateCommand();
dbCmd.CommandType = CommandType.StoredProcedure;
dbCmd.CommandText = "...";
DbParameter dbPar = dbCmd.CreateParameter();
IDataParameter dbPar = dbCmd.CreateParameter();
dbPar.ParameterName = "...";

The goal of this is to limit the amount of provider-specific code.
If you want to adapt this code for any other provider (OleDb, ODBC, Oracle,...) , you only have to change it in 1 place.

Mmm. This is what M$ recommends.
Maybe I should do some test with it to see whether there are any perfs impact...
Maybe more on that someday...

2 commentaires:

Julien a dit…

Why don't you use the IDbConnection interface
instead of the DbConnection or the IDbCommand instead of the DbCommand?

JMG a dit…

Yup.

Got it right. Guess the SelfPaced Training Kit wasn't quite right...

Try to compile de "DBConnection" stuff...

The best is :
string connectionString="...";
IDbConnection dbCon = new SqlConnection(connectionString);
IDbCommand dbCmd = dbCon.CreateCommand();
dbCmd.CommandType = CommandType.StoredProcedure;
dbCmd.CommandText = "...";
IDbDataParameter dbPar = dbCmd.CreateParameter();
dbPar.ParameterName = "...";