public virtual object ExecuteCommand(IDbCommand command, DbExecutionType executionType)
{
try {
var conn = command.Connection;
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
switch (executionType)
{
case DbExecutionType.Reader:
return(command.ExecuteReader());
case DbExecutionType.NonQuery:
return(command.ExecuteNonQuery());
case DbExecutionType.Scalar:
return(command.ExecuteScalar());
}
return(null); //never happens
} catch (System.Data.Common.DbException dbExc) {
// Important: in some cases exception on invalid SQL is not thrown immediately but is thrown later when we try to read the results
// ex (MS SQL): WHERE "Name" LIKE 'ABC%' ESCAPE '' - with empty ESCAPE arg string.
Debug.WriteLine("Failed SQL: \r\n" + command.CommandText);
var dex = ConvertToDataAccessException(dbExc, command);
throw dex;
}
}
public DataCommand CreateCommand(DataConnection connection,
DbExecutionType executionType, IDataCommandResultProcessor resultProcessor)
{
_dbCommand.CommandText = string.Join(string.Empty, _sqlStrings);
var cmd = new DataCommand(connection, _dbCommand, executionType, resultProcessor, _records);
_dbCommand = null;
return(cmd);
}
public override void CommandExecuted(DataConnection connection, IDbCommand command, DbExecutionType executionType)
{
//If there is transaction started only for this command (see ExecuteCommand method above), then commit it
if(command.CommandType == CommandType.StoredProcedure && executionType == DbExecutionType.Reader &&
command.Transaction != null && command.Transaction != connection.DbTransaction) {
command.Transaction.Commit();
command.Transaction = null;
}
}
public override object ExecuteCommand(IDbCommand command, DbExecutionType executionType)
{
// Oracle does not allow ending semicolon in single statement.
// But... batches start with BEGIN; and end with "END;"
if (!command.CommandText.StartsWith("BEGIN "))
{
command.CommandText = command.CommandText.TrimEnd(' ', ';', '\r', '\n');
}
return(base.ExecuteCommand(command, executionType));
}
public DataCommand(DataConnection connection, IDbCommand dbCommand, DbExecutionType executionType,
IDataCommandResultProcessor resultsProcessor, IList <EntityRecord> records,
IList <BatchParamCopy> paramCopyList = null)
{
Connection = connection;
DbCommand = dbCommand;
ExecutionType = executionType;
ResultProcessor = resultsProcessor;
Records = records;
ParamCopyList = paramCopyList;
}
public SqlStatement(SqlKind kind, SqlFragment sql, SqlPlaceHolderList placeHolders, DbExecutionType executionType,
SqlPrecedenceHandler precedenceHandler = null, QueryOptions options = QueryOptions.None)
{
Kind = kind;
ExecutionType = executionType;
Options = options;
Append(sql, placeHolders, precedenceHandler);
if (placeHolders == null)
{
DiscoverPlaceholders();
}
}
public override DbCommandInfo CreateDbCommandInfo(EntityCommand entityCommand, string name, DbTableInfo mainTable, DbExecutionType executionType, string sql)
{
var cmdInfo = base.CreateDbCommandInfo(entityCommand, name, mainTable, executionType, sql);
var ent = entityCommand.TargetEntityInfo;
if (cmdInfo.Kind == EntityCommandKind.Insert && ent.Flags.IsSet(EntityFlags.HasIdentity)) {
//Add actions to read identity value
var idPrm = cmdInfo.Parameters.FirstOrDefault(p => p.SourceColumn.Flags.IsSet(DbColumnFlags.Identity));
if (idPrm != null)
cmdInfo.PostUpdateActions.Add(GetLastRowId);
}
return cmdInfo;
}
public override object ExecuteCommand(IDbCommand command, DbExecutionType executionType)
{
try {
return(base.ExecuteCommand(command, executionType));
} catch (Exception) {
//Strange behavior in MySql - sometimes, right exception thrown from db, the next (insert?) statement is not executed properly,
// and in effect ignored. To see this behavior, comment the following line and run TestUniqueKey test in UnitTests.Basic assembly.
// The second part of the test fails, because the first insert statement for driver Mindy is ignored, Mindy is not inserted,
// and then next insert of Molly driver succeeds and does not throw 'Unique index violation' exception.
// Did not find any clues why it happens.
ExecuteDummyPostErrorSelect(command.Connection);
throw;
}
}
public object ExecuteDbCommand(IDbCommand command, DataConnection connection, DbExecutionType executionType,
Func<IDataReader, int> resultsReader = null)
{
object result;
IDataReader reader = null;
connection.Session.SetLastCommand(command);
try {
command.Connection = connection.DbConnection;
command.Transaction = connection.DbTransaction;
var start = CurrentTickCount;
int recordCount = -1;
result = _driver.ExecuteCommand(command, executionType);
if(executionType == DbExecutionType.Reader) {
reader = (IDataReader)result;
if(resultsReader != null)
recordCount = resultsReader(reader);
reader.Close();
}
_driver.CommandExecuted(connection, command, executionType);
var end = CurrentTickCount;
var timeMs = (int)(end - start);
LogCommand(connection.Session, command, timeMs, recordCount);
return result;
} catch(Exception ex) {
// Important: in some cases exception on invalid SQL is not thrown immediately but is thrown later when we try to read the results
// ex (MS SQL): WHERE "Name" LIKE 'ABC%' ESCAPE '' - with empty ESCAPE arg string.
// So driver.ExecuteDbCommand does NOT catch it, it is thrown only here in call to resultsReader
var dex = ex as DataAccessException;
if(dex == null)
dex = _driver.ConvertToDataAccessException(ex, command);
if (connection.DbTransaction != null) {
connection.Session.LogMessage(" -- Aborting transaction on error");
connection.Abort();
}
connection.Session.LogMessage(" -- Failed command text: ");
LogCommand(connection.Session, command, 0);
ReviewExceptionAndAddInfo(dex);
LogException(connection.Session, dex);
if(reader != null)
reader.Close();
throw dex;
} finally {
command.Transaction = null;
command.Connection = null;
}
}
public override object ExecuteCommand(IDbCommand command, DbExecutionType executionType)
{
// this is one strange thing about PostGres - 'select-like' stored proc returns cursor,
// which is immediately disposed after trans is committed.
// As any standalone SQL command is auto-wrapped into transation, then simply calling stored proc (function) returns a deallocated cursor.
// You have to wrap the call in explicit transation, finish reading the data and then commit transaction
// Here we only begin transaction, it will be committed after reading the data
if (command.CommandType == CommandType.StoredProcedure && executionType == DbExecutionType.Reader && command.Transaction == null)
{
var conn = command.Connection;
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
command.Transaction = conn.BeginTransaction();
}
return(base.ExecuteCommand(command, executionType));
}
public override DbCommandInfo CreateDbCommandInfo(EntityCommand entityCommand, string name, DbTableInfo mainTable, DbExecutionType executionType, string sql)
{
var cmdInfo = base.CreateDbCommandInfo(entityCommand, name, mainTable, executionType, sql);
var ent = entityCommand.TargetEntityInfo;
if (cmdInfo.Kind == EntityCommandKind.Insert && ent.Flags.IsSet(EntityFlags.HasIdentity)) {
//Add actions to read identity value
var idPrm = cmdInfo.Parameters.FirstOrDefault(p => p.SourceColumn.Flags.IsSet(DbColumnFlags.Identity));
if (idPrm != null) {
cmdInfo.PostUpdateActions.Add((conn, cmd, rec) => {
var idCmd = conn.DbConnection.CreateCommand();
idCmd.CommandText = "Select @@IDENTITY;";
idCmd.Transaction = conn.DbTransaction;
var id = conn.Database.ExecuteDbCommand(idCmd, conn, DbExecutionType.Scalar); //it is decimal
var intId = Convert.ChangeType(id, idPrm.SourceColumn.Member.DataType);
rec.SetValueDirect(idPrm.SourceColumn.Member, intId);
});
}//if IdPrm ...
}
return cmdInfo;
}
public DbCommandInfo(EntityCommand entityCommand, string commandName, DbTableInfo table, DbExecutionType executionType, string sql, string tag)
: base(table.DbModel, table.Schema, DbObjectType.Command, entityCommand)
{
EntityCommand = entityCommand;
CommandName = commandName;
Table = table;
ExecutionType = executionType;
Sql = sql;
Description = EntityCommand.Description;
DescriptiveTag = tag;
//derived entities
FullCommandName = Table.DbModel.Driver.FormatFullName(Schema, CommandName);
Kind = entityCommand.Kind;
var dbModel = table.DbModel;
dbModel.AddCommand(this);
if (Table != null)
{
Table.CrudCommands.Add(this);
}
base.GlobalName = DbModelHelper.GetGlobalName(Schema, commandName);
}
public virtual DbCommandInfo CreateDbCommandInfo(EntityCommand entityCommand, string name, DbTableInfo mainTable, DbExecutionType executionType, string sql)
{
var descrTag = GetDbCommandDescriptiveTag(entityCommand);
var cmdInfo = new DbCommandInfo(entityCommand, name, mainTable, executionType, sql, descrTag);
//Create parameters from entity command parameters
var policy = DbModel.Config.NamingPolicy;
var prmPrefix = GetParameterPrefix();
for (int i = 0; i < entityCommand.Parameters.Count; i++)
{
var entParam = entityCommand.Parameters[i];
var paramName = prmPrefix + policy.GetDbParameterName(entParam.Name);
DbParamInfo prmInfo;
if (entParam.SourceMember != null)
{
var col = mainTable.Columns.FirstOrDefault(c => c.Member == entParam.SourceMember);
Util.Check(col != null, "Failed to find Db column for member {0}, entity {1}.", entParam.SourceMember.MemberName, mainTable.Entity.Name);
prmInfo = cmdInfo.AddParameter(entParam, paramName, col, i);
}
else
{
var typeInfo = GetDbTypeInfo(entParam.DataType, entParam.Size);
prmInfo = cmdInfo.AddParameter(entParam, paramName, typeInfo, i);
}
// SQL CE does not support output parameters
if (!this.DbModel.Driver.Supports(DbFeatures.OutputParameters))
{
prmInfo.Direction = ParameterDirection.Input;
}
if (prmInfo.Direction == ParameterDirection.Output || prmInfo.Direction == ParameterDirection.InputOutput)
{
cmdInfo.PostUpdateActions.Add((con, cmd, rec) => {
var prm = (IDbDataParameter)cmd.Parameters[prmInfo.Name];
rec.SetValueDirect(prmInfo.SourceColumn.Member, prm.Value);
});
}
}//foreach entParam
return(cmdInfo);
}
public virtual void CommandExecuted(DataConnection connection, IDbCommand command, DbExecutionType executionType)
{
}
public override object ExecuteCommand(IDbCommand command, DbExecutionType executionType)
{
try {
return base.ExecuteCommand(command, executionType);
} catch(Exception) {
//Strange behavior in MySql - sometimes, right exception thrown from db, the next (insert?) statement is not executed properly,
// and in effect ignored. To see this behavior, comment the following line and run TestUniqueKey test in UnitTests.Basic assembly.
// The second part of the test fails, because the first insert statement for driver Mindy is ignored, Mindy is not inserted,
// and then next insert of Molly driver succeeds and does not throw 'Unique index violation' exception.
// Did not find any clues why it happens.
ExecuteDummyPostErrorSelect(command.Connection);
throw;
}
}
public object ExecuteDbCommand(IDbCommand command, DataConnection connection, DbExecutionType executionType,
Func <IDataReader, int> resultsReader = null)
{
object result;
IDataReader reader = null;
connection.Session.SetLastCommand(command);
try {
command.Connection = connection.DbConnection;
command.Transaction = connection.DbTransaction;
var start = CurrentTickCount;
int recordCount = -1;
result = _driver.ExecuteCommand(command, executionType);
if (executionType == DbExecutionType.Reader)
{
reader = (IDataReader)result;
if (resultsReader != null)
{
recordCount = resultsReader(reader);
}
reader.Close();
}
_driver.CommandExecuted(connection, command, executionType);
var end = CurrentTickCount;
var timeMs = (int)(end - start);
LogCommand(connection.Session, command, timeMs, recordCount);
return(result);
} catch (Exception ex) {
// Important: in some cases exception on invalid SQL is not thrown immediately but is thrown later when we try to read the results
// ex (MS SQL): WHERE "Name" LIKE 'ABC%' ESCAPE '' - with empty ESCAPE arg string.
// So driver.ExecuteDbCommand does NOT catch it, it is thrown only here in call to resultsReader
var dex = ex as DataAccessException;
if (dex == null)
{
dex = _driver.ConvertToDataAccessException(ex, command);
}
if (connection.DbTransaction != null)
{
connection.Session.LogMessage(" -- Aborting transaction on error");
connection.Abort();
}
connection.Session.LogMessage(" -- Failed command text: ");
LogCommand(connection.Session, command, 0);
ReviewExceptionAndAddInfo(dex);
LogException(connection.Session, dex);
if (reader != null)
{
reader.Close();
}
throw dex;
} finally {
command.Transaction = null;
command.Connection = null;
}
}//method
public object ExecuteDirectDbCommand(IDbCommand command, DataConnection connection, DbExecutionType execType) {
object result;
connection.Session.SetLastCommand(command);
try {
command.Connection = connection.DbConnection;
command.Transaction = connection.DbTransaction;
var start = _timeService.ElapsedMilliseconds;
int recordCount = -1;
result = _driver.ExecuteCommand(command, execType);
// if (execType == DbExecutionType.Reader)
connection.ActiveReader = result as IDataReader; // if it is reader, save it in connection
_driver.CommandExecuted(connection, command, execType);
var end = _timeService.ElapsedMilliseconds;
var timeMs = (int)(end - start);
LogCommand(connection.Session, command, timeMs, recordCount);
return result;
} catch (Exception ex) {
// Important: in some cases exception on invalid SQL is not thrown immediately but is thrown later when we try to read the results
// ex (MS SQL): WHERE "Name" LIKE 'ABC%' ESCAPE '' - with empty ESCAPE arg string.
// So driver.ExecuteDbCommand does NOT catch it, it is thrown only here in call to resultsReader
var dex = ex as DataAccessException;
if (dex == null)
dex = _driver.ConvertToDataAccessException(ex, command);
if (connection.DbTransaction != null) {
connection.Session.LogMessage(" -- Aborting transaction on error");
connection.Abort();
}
connection.Session.LogMessage(" -- Failed command text: ");
LogCommand(connection.Session, command, 0);
ReviewExceptionAndAddInfo(dex);
LogException(connection.Session, dex);
if (connection.ActiveReader != null)
connection.ActiveReader.Dispose();
throw dex;
} finally {
/* //SQLite does not like this
command.Transaction = null;
command.Connection = null;
connection.ActiveReader = null;
*/
}
}//method
object IDirectDbConnector.ExecuteDbCommand(IDbCommand command, DbExecutionType executionType, Func<IDataReader, int> resultsReader)
{
return this.Database.ExecuteDbCommand(command, this, executionType, resultsReader);
}
public DbCommandInfo(EntityCommand entityCommand, string commandName, DbTableInfo table, DbExecutionType executionType, string sql, string tag)
: base(table.DbModel, table.Schema, DbObjectType.Command, entityCommand)
{
EntityCommand = entityCommand;
CommandName = commandName;
Table = table;
ExecutionType = executionType;
Sql = sql;
Description = EntityCommand.Description;
DescriptiveTag = tag;
//derived entities
FullCommandName = Table.DbModel.Driver.GetFullName(Schema, CommandName);
Kind = entityCommand.Kind;
var dbModel = table.DbModel;
dbModel.AddCommand(this);
if(Table != null)
Table.CrudCommands.Add(this);
base.GlobalName = DbModelHelper.GetGlobalName(Schema, commandName);
}
public override void CommandExecuted(DataConnection connection, IDbCommand command, DbExecutionType executionType)
{
//If there is transaction started only for this command (see ExecuteCommand method above), then commit it
if (command.CommandType == CommandType.StoredProcedure && executionType == DbExecutionType.Reader &&
command.Transaction != null && command.Transaction != connection.DbTransaction)
{
command.Transaction.Commit();
command.Transaction = null;
}
}
public virtual object ExecuteCommand(IDbCommand command, DbExecutionType executionType)
{
try {
var conn = command.Connection;
if(conn.State != ConnectionState.Open)
conn.Open();
switch (executionType) {
case DbExecutionType.Reader:
return command.ExecuteReader();
case DbExecutionType.NonQuery:
return command.ExecuteNonQuery();
case DbExecutionType.Scalar:
return command.ExecuteScalar();
}
return null; //never happens
} catch (System.Data.Common.DbException dbExc) {
// Important: in some cases exception on invalid SQL is not thrown immediately but is thrown later when we try to read the results
// ex (MS SQL): WHERE "Name" LIKE 'ABC%' ESCAPE '' - with empty ESCAPE arg string.
var dex = ConvertToDataAccessException(dbExc, command);
throw dex;
}
}
public override DbCommandInfo CreateDbCommandInfo(EntityCommand entityCommand, string name, DbTableInfo mainTable, DbExecutionType executionType, string sql)
{
var cmdInfo = base.CreateDbCommandInfo(entityCommand, name, mainTable, executionType, sql);
var ent = entityCommand.TargetEntityInfo;
if (cmdInfo.Kind == EntityCommandKind.Insert && ent.Flags.IsSet(EntityFlags.HasIdentity))
{
//Add actions to read identity value
var idPrm = cmdInfo.Parameters.FirstOrDefault(p => p.SourceColumn.Flags.IsSet(DbColumnFlags.Identity));
if (idPrm != null)
{
cmdInfo.PostUpdateActions.Add(GetLastRowId);
}
}
return(cmdInfo);
}
public override object ExecuteCommand(IDbCommand command, DbExecutionType executionType)
{
// this is one strange thing about PostGres - 'select-like' stored proc returns cursor,
// which is immediately disposed after trans is committed.
// As any standalone SQL command is auto-wrapped into transation, then simply calling stored proc (function) returns a deallocated cursor.
// You have to wrap the call in explicit transation, finish reading the data and then commit transaction
// Here we only begin transaction, it will be committed after reading the data
if(command.CommandType == CommandType.StoredProcedure && executionType == DbExecutionType.Reader && command.Transaction == null) {
var conn = command.Connection;
if(conn.State != ConnectionState.Open)
conn.Open();
command.Transaction = conn.BeginTransaction();
}
return base.ExecuteCommand(command, executionType);
}
public override DbCommandInfo CreateDbCommandInfo(EntityCommand entityCommand, string name, DbTableInfo mainTable, DbExecutionType executionType, string sql)
{
var cmdInfo = base.CreateDbCommandInfo(entityCommand, name, mainTable, executionType, sql);
var ent = entityCommand.TargetEntityInfo;
if (cmdInfo.Kind == EntityCommandKind.Insert && ent.Flags.IsSet(EntityFlags.HasIdentity))
{
//Add actions to read identity value
var idPrm = cmdInfo.Parameters.FirstOrDefault(p => p.SourceColumn.Flags.IsSet(DbColumnFlags.Identity));
if (idPrm != null)
{
cmdInfo.PostUpdateActions.Add((conn, cmd, rec) => {
var idCmd = conn.DbConnection.CreateCommand();
idCmd.CommandText = "Select @@IDENTITY;";
idCmd.Transaction = conn.DbTransaction;
var id = conn.Database.ExecuteDbCommand(idCmd, conn, DbExecutionType.Scalar); //it is decimal
var intId = Convert.ChangeType(id, idPrm.SourceColumn.Member.DataType);
rec.SetValueDirect(idPrm.SourceColumn.Member, intId);
});
}//if IdPrm ...
}
return(cmdInfo);
} //method
object IDirectDbConnector.ExecuteDbCommand(IDbCommand command, DbExecutionType executionType, Func <IDataReader, int> resultsReader)
{
return(this.Database.ExecuteDbCommand(command, this, executionType, resultsReader));
}
public virtual void CommandExecuted(DataConnection connection, IDbCommand command, DbExecutionType executionType)
{
}
public virtual DbCommandInfo CreateDbCommandInfo(EntityCommand entityCommand, string name, DbTableInfo mainTable, DbExecutionType executionType, string sql)
{
var descrTag = GetDbCommandDescriptiveTag(entityCommand);
var cmdInfo = new DbCommandInfo(entityCommand, name, mainTable, executionType, sql, descrTag);
//Create parameters from entity command parameters
var policy = DbModel.Config.NamingPolicy;
var prmPrefix = GetParameterPrefix();
for(int i=0; i< entityCommand.Parameters.Count; i++) {
var entParam = entityCommand.Parameters[i];
var paramName = prmPrefix + policy.ConstructDbParameterName(entParam.Name);
DbParamInfo prmInfo;
if (entParam.SourceMember != null) {
var col = mainTable.Columns.FirstOrDefault(c => c.Member == entParam.SourceMember);
Util.Check(col != null, "Failed to find Db column for member {0}, entity {1}.", entParam.SourceMember.MemberName, mainTable.Entity.Name);
prmInfo = cmdInfo.AddParameter(entParam, paramName, col, i);
} else {
var typeInfo = GetDbTypeInfo(entParam.DataType, entParam.Size);
prmInfo = cmdInfo.AddParameter(entParam, paramName, typeInfo, i);
}
// SQL CE does not support output parameters
if (!this.DbModel.Driver.Supports(DbFeatures.OutputParameters))
prmInfo.Direction = ParameterDirection.Input;
if (prmInfo.Direction == ParameterDirection.Output || prmInfo.Direction == ParameterDirection.InputOutput) {
cmdInfo.PostUpdateActions.Add((con, cmd, rec) => {
var prm = (IDbDataParameter) cmd.Parameters[prmInfo.Name];
rec.SetValueDirect(prmInfo.SourceColumn.Member, prm.Value);
});
}
}//foreach entParam
return cmdInfo;
}
