LikeIfYouCaredAboutMyName LikeIfYouCaredAboutMyName - 1 year ago 104
SQL Question

Executing commands using DbCommand in c#

ok I used the DbClass so I could use both Oracle and sql without having to have 2 different dills, I just pass the connection and the provider and that's it.
This is how I am opening the connection:

public class CdpsiUpdateSql : IDisposable
private DbTransaction _myTransaction;
bool disposed = false;
SafeHandle handle = new SafeFileHandle(IntPtr.Zero, true);
public DbConnection OraConnection { get; set; }

public CdpsiUpdateSql(string Provider, string connectionString)
//this.OraConnection = OpenDbConnection(connectionString);
string constr = connectionString;

DbProviderFactory factory =
DbConnection conn = factory.CreateConnection();
conn.ConnectionString = constr;
this.OraConnection = conn;


but now I need to execute several commands using that connection (OraConnection), I have another DLL that executes the commands, and I have this function:

private bool ExecComando(string comando, CdpsiUpdateSql Updater, string log)
Database db = DatabaseFactory.CreateDatabase();
string sql = comando;
DbCommand cmd = db.GetSqlStringCommand(sql);
cmd.Connection = Updater.OraConnection;
linhas = cmd.ExecuteNonQuery();
return false;
catch (DbException exp)
Logg("Erro a executar o comando: " + comando, log);
Logg("Descrição do erro: " + exp.ToString(), log);
return true;

it throws an exception asking to set a DatabaseProviderFactory which needs a config file.
I can't found anything conclusive on this, because info is really scarse when it comes to this class.
If it's any relevant when testing I used the provider >"Oracle.ManagedDataAccess.Client" and the connection using the first method works just fine, it connects successfully.
What do I need to use to execute the commands?
Any help is appreciated, thank you very much

Answer Source

You don't need the DatabaseFactory class at all, in my opinion.

I think you can use the CreateCommand method of the DbConnection class. Something like:

var cmd = Updater.OraConnection.CreateCommand();
cmd.CommandText = sql;

I think it's even better to declare the connection with a private setter.

public DbConnection OraConnection { get; private set; }

Much more elegant and safe.