LikeIfYouCaredAboutMyName LikeIfYouCaredAboutMyName - 4 months ago 31
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 =
DbProviderFactories.GetFactory(Provider);
DbConnection conn = factory.CreateConnection();
conn.ConnectionString = constr;
conn.Open();
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;
try
{
linhas = cmd.ExecuteNonQuery();
return false;
}
catch (DbException exp)
{
Logg("Erro a executar o comando: " + comando, log);
Logg("Descrição do erro: " + exp.ToString(), log);
cmd.Dispose();
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

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.

Comments