Add a member to Command object

[hardieca]

Good morning,

I am creating a CMS which I would like to support multiple DBs for its
back-end. I have taken the approach of using DBProviderFactories to
keep my code flexible enough so that I can switch backends simply by
changing the provider and connection strings stored in my App.config
file.

However, there are a few methods I would like to add to the Command
object to wrap up a few inconsistencies of how DBs implement certain
functionality. For instance, I would like the command object to return
the ID of the last inserted record.

I would like to add something like the following to the Command object:

public int GetLastInserted(){
string oldSQL = this.CommandText;
if (provider = "System.Data.SqlClient){
this.CommandText = @"SELECT Cast(scope_identity() AS int;";
}
else if (provider = "MySQL.Data.MySqlClient"){
this.CommandText = @"SELECT currval();";
}
this.CommandText = oldSQL;
return (int)this.ExecuteScalar();

So when I create my Command object like so:

DbProviderFactory factory = DBProviderFactories.GetFactory(provider);
DbConnection conn = factory.CreateConnection();
conn.ConnectionString = conStr;
DbCommand cmd = conn.CreateCommand();

Now that I have the Command object cmd, can I somehow cast/convert it
into a custom type that includes the desired members?

Kind regards,

Chris

 

[Guest]

I suppose you could certainly create a command object that derives from
IDBCommand and "Soup it up" if you like, but a problem that may crop up here
is that once your original command has been executed, @@IDENTITY and
SCOPE_IDENTITY are already gone, because the database has already completed
its work.
Typically you would do this with stored procedures, and you would get the
@@IDENTITY value either as a scalar return value, or as an output parameter
to the sproc.
Peter

 

[PS]

Good morning,

I am creating a CMS which I would like to support multiple DBs for its
back-end. I have taken the approach of using DBProviderFactories to
keep my code flexible enough so that I can switch backends simply by
changing the provider and connection strings stored in my App.config
file.

However, there are a few methods I would like to add to the Command
object to wrap up a few inconsistencies of how DBs implement certain
functionality. For instance, I would like the command object to return
the ID of the last inserted record.

I would like to add something like the following to the Command object:

public int GetLastInserted(){
string oldSQL = this.CommandText;
if (provider = "System.Data.SqlClient){
this.CommandText = @"SELECT Cast(scope_identity() AS int;";
}
else if (provider = "MySQL.Data.MySqlClient"){
this.CommandText = @"SELECT currval();";
}
this.CommandText = oldSQL;
return (int)this.ExecuteScalar();

So when I create my Command object like so:

DbProviderFactory factory = DBProviderFactories.GetFactory(provider);
DbConnection conn = factory.CreateConnection();
conn.ConnectionString = conStr;
DbCommand cmd = conn.CreateCommand();

Now that I have the Command object cmd, can I somehow cast/convert it
into a custom type that includes the desired members?

You are doing something wrong. The if / else statements and the need to cast
are both code smells. Also the CreateCommand() method is not going to be
able to return your object derived from DbCommand. You should re-think this
idea. To me the GetLastInserted method would be part of a derived data layer
class (one class per provider) not part of the DbCommand object.

PS