protected override SQLCursor InternalOpen(SQLCursorType cursorType, SQLIsolationLevel isolationLevel)
{
PrepareCommand(false, isolationLevel);
SetParameters();
IDataReader cursor = _command.ExecuteReader(SQLCommandBehaviorToCommandBehavior(CommandBehavior));
GetParameters();
return(new DotNetCursor(this, cursor));
}
protected override void InternalBeginTransaction(SQLIsolationLevel isolationLevel)
{
if (isolationLevel == SQLIsolationLevel.ReadUncommitted)
{
_inReadUncommittedTransaction = true;
}
else
{
base.InternalBeginTransaction(isolationLevel);
}
}
public SQLCursor Open(SQLCursorType cursorType, SQLIsolationLevel cursorIsolationLevel)
{
Prepare();
try
{
return(InternalOpen(cursorType, cursorIsolationLevel));
}
catch (Exception exception)
{
_connection.WrapException(exception, Statement, true);
throw;
}
}
public SQLCursor Open(SQLCursorType ACursorType, SQLIsolationLevel ACursorIsolationLevel)
{
Prepare();
try
{
return(InternalOpen(ACursorType, ACursorIsolationLevel));
}
catch (Exception LException)
{
FConnection.WrapException(LException, Statement, true);
throw;
}
}
protected override void InternalBeginTransaction(SQLIsolationLevel isolationLevel)
{
_isolationLevel = System.Data.IsolationLevel.Unspecified;
switch (isolationLevel)
{
case SQLIsolationLevel.ReadUncommitted: // all three will map to committed in this Optimisitc system
case SQLIsolationLevel.RepeatableRead:
case SQLIsolationLevel.ReadCommitted: _isolationLevel = System.Data.IsolationLevel.ReadCommitted; break;
case SQLIsolationLevel.Serializable: _isolationLevel = System.Data.IsolationLevel.Serializable; break;
}
_transaction = _connection.BeginTransaction(_isolationLevel);
}
protected void PrepareCommand(SQLIsolationLevel isolationLevel)
{
_command.CommandText = PrepareStatement(Statement);
_command.CommandTimeout = CommandTimeout;
if (CommandType == SQLCommandType.Statement)
{
_command.CommandType = CommandTypeEnum.adCmdText;
}
if (UseParameters)
{
PrepareParameters();
}
//FCommand.Prepared = true; // this causes a 'syntax error or access violation' in some cases ???
}
protected override void InternalBeginTransaction(SQLIsolationLevel AIsolationLevel)
{
FIsolationLevel = System.Data.IsolationLevel.Unspecified;
switch (AIsolationLevel)
{
case SQLIsolationLevel.ReadUncommitted: FIsolationLevel = System.Data.IsolationLevel.ReadUncommitted; break;
case SQLIsolationLevel.ReadCommitted: FIsolationLevel = System.Data.IsolationLevel.ReadCommitted; break;
case SQLIsolationLevel.RepeatableRead: FIsolationLevel = System.Data.IsolationLevel.RepeatableRead; break;
case SQLIsolationLevel.Serializable: FIsolationLevel = System.Data.IsolationLevel.Serializable; break;
}
FTransaction = FConnection.BeginTransaction(FIsolationLevel);
}
public void BeginTransaction(SQLIsolationLevel AIsolationLevel)
{
CheckConnectionValid();
CheckNotInTransaction();
try
{
InternalBeginTransaction(AIsolationLevel);
}
catch (Exception LException)
{
WrapException(LException, "begin transaction", false);
}
FInTransaction = true;
FTransactionFailure = false;
}
protected void PrepareCommand(bool AExecute, SQLIsolationLevel AIsolationLevel)
{
FCommand.CommandText = PrepareStatement(Statement);
switch (CommandType)
{
case SQLCommandType.Statement: FCommand.CommandType = System.Data.CommandType.Text; break;
case SQLCommandType.Table: FCommand.CommandType = System.Data.CommandType.TableDirect; break;
}
if (UseParameters)
{
PrepareParameters();
}
//FCommand.Prepare();//commented out because the MS Oracle Provider gets an iteration exception when prepared
// Todo: put back in when MS Oracle can prepare
}
protected override void InternalBeginTransaction(SQLIsolationLevel isolationLevel)
{
switch (isolationLevel)
{
case SQLIsolationLevel.ReadUncommitted: _connection.IsolationLevel = ADODB.IsolationLevelEnum.adXactReadUncommitted; break;
case SQLIsolationLevel.ReadCommitted: _connection.IsolationLevel = ADODB.IsolationLevelEnum.adXactReadCommitted; break;
case SQLIsolationLevel.RepeatableRead: _connection.IsolationLevel = ADODB.IsolationLevelEnum.adXactRepeatableRead; break;
case SQLIsolationLevel.Serializable: _connection.IsolationLevel = ADODB.IsolationLevelEnum.adXactSerializable; break;
default: _connection.IsolationLevel = ADODB.IsolationLevelEnum.adXactUnspecified; break;
}
_nestingLevel = _connection.BeginTrans();
}
// BeginTransaction
public virtual void BeginTransaction(SQLIsolationLevel isolationLevel)
{
if (_transactionCount == 0)
{
#if SQLSTORETIMING
long startTicks = TimingUtility.CurrentTicks;
#endif
DisposeExecuteCommand();
_connection.BeginTransaction(isolationLevel);
#if SQLSTORETIMING
Store.Counters.Add(new SQLStoreCounter("BeginTransaction", "", "", false, false, false, TimingUtility.TimeSpanFromTicks(startTicks)));
#endif
}
_transactionCount++;
_operationLog.Add(new BeginTransactionOperation());
}
/// <summary> Prepares for a server cursor open. </summary>
protected void PrepareServerCursorCommand(SQLCursorType ACursorType, SQLIsolationLevel AIsolationLevel)
{
// All commands will be stored procedures when using server-side cursors
FCommand.CommandType = System.Data.CommandType.StoredProcedure; // always use stored procedure because queries are wrapped in a server-side cursor stored procedure call
// Call PrepareStatement first to determine the set of non-internal parameters and normalize the statement
string LStatement = PrepareStatement(Statement);
// Call PrepareCursorStatement before creating the non-internal parameters because sp_cursoropen is fickle about the order of the arguments (even though they are passed by name)
PrepareCursorStatement(LStatement, ACursorType, AIsolationLevel);
if (ParametersActive())
{
// Prepare non-internal parameters
PrepareParameters();
// One of the already created internal parameters is a list of the non-internal parameters; set it now that we have the non-internal parameters configured.
SetParameterDefinition();
//FCommand.Prepare(); // Do not prepare against SQL Server. It seems that it is perhaps never faster to do so.
}
}
/*
* NOTE: ADO WORKAROUND: The adOpenForwardOnly and adLockReadOnly combination will always discard the recordset on a commit or abort, so it cannot be used.
* NOTE: ADO WORKAROUND only required when the isolation level > read uncommitted because the browse Cursors are all opened on a separate Cursor connection which runs its own transaction.
* NOTE: ADO WORKAROUND not required at all because the only cursors that span transactions are browse Cursors
*/
protected override SQLCursor InternalOpen(SQLCursorType cursorType, SQLIsolationLevel isolationLevel)
{
PrepareCommand(isolationLevel);
SetParameters();
ADODB.Recordset recordset = new ADODB.Recordset();
recordset.Source = _command;
recordset.CacheSize = 20;
//LRecordset.Properties["Preserve on Commit"].Value = true;
//LRecordset.Properties["Preserve on Abort"].Value = true;
recordset.Open
(
Missing.Value,
Missing.Value,
(
(cursorType == SQLCursorType.Static) ?
ADODB.CursorTypeEnum.adOpenStatic :
(
(LockType == SQLLockType.ReadOnly) ?
ADODB.CursorTypeEnum.adOpenForwardOnly :
ADODB.CursorTypeEnum.adOpenDynamic
)
),
(LockType == SQLLockType.Optimistic) ?
ADODB.LockTypeEnum.adLockOptimistic :
(
(LockType == SQLLockType.Pessimistic) ?
ADODB.LockTypeEnum.adLockPessimistic :
ADODB.LockTypeEnum.adLockReadOnly
),
CommandType == SQLCommandType.Table ? (int)CommandTypeEnum.adCmdTableDirect : -1
);
SQLCursor cursor = new ADOCursor(this, recordset);
GetParameters();
return(cursor);
}
public SQLCursor Open(string AStatement, SQLParameters AParameters, SQLCursorType ACursorType, SQLIsolationLevel ACursorIsolationLevel, SQLCommandBehavior ABehavior)
{
CheckConnectionValid();
SQLCommand LCommand = CreateCommand();
try
{
LCommand.Statement = AStatement;
LCommand.Parameters.AddRange(AParameters);
LCommand.CommandBehavior = ABehavior;
return(LCommand.Open(ACursorType, ACursorIsolationLevel));
}
catch
{
LCommand.Dispose();
throw;
}
}
protected virtual void InternalBeginTransaction(SQLIsolationLevel isolationLevel)
{
_connection.BeginTransaction(isolationLevel);
}
/// <summary> Prepares for invocation of a server-side cursor. </summary>
private void PrepareCursorStatement(string statement, SQLCursorType cursorType, SQLIsolationLevel isolationLevel)
{
// Cannot use the provider's command type behavior because we are always enclosing the expression in sp_cursor calls
if (CommandType == SQLCommandType.Table)
{
statement = "select * from " + statement;
}
InternalParameterCreate("@retval", SqlDbType.Int, ParameterDirection.ReturnValue);
InternalParameterCreate("@cursor", SqlDbType.Int, ParameterDirection.Output).Value = 0;
InternalParameterCreate("@stmt", SqlDbType.NVarChar, ParameterDirection.Input).Value = statement;
InternalParameterCreate("@scrollopt", SqlDbType.Int, ParameterDirection.Input).Value = GetScrollOptions(cursorType, isolationLevel);
InternalParameterCreate("@ccopt", SqlDbType.Int, ParameterDirection.Input).Value = GetConcurrencyOptions();
InternalParameterCreate("@rowcount", SqlDbType.Int, ParameterDirection.InputOutput).Value = FetchCount; // This must be an output parameter or an error will occur
// Create the parameter definition parameter here (before setting its value) to ensure that it comes in the right order (otherwise sp_cursoropen complains)
if (ParametersActive())
{
InternalParameterCreate("@paramdef", SqlDbType.NVarChar, ParameterDirection.Input).Value = "";
}
_command.CommandText = "sp_cursoropen";
}
/// <summary> Gets the SQL Server server side cursor scroll options. </summary>
private SQLServerCursorScrollOptions GetScrollOptions(SQLCursorType cursorType, SQLIsolationLevel isolationLevel)
{
SQLServerCursorScrollOptions results = SQLServerCursorScrollOptions.AutoFetch | SQLServerCursorScrollOptions.AutoClose;
if (ParametersActive())
{
results |= SQLServerCursorScrollOptions.Parameterized;
}
if (LockType == SQLLockType.ReadOnly)
{
results |= SQLServerCursorScrollOptions.FastForward;
}
else
{
results |= SQLServerCursorScrollOptions.ForwardOnly;
}
/*
* Setting these is not agreeable to the server
* if (ACursorType == SQLCursorType.Dynamic)
* LResults |= SQLServerCursorScrollOptions.Dynamic;
* else
* LResults |= SQLServerCursorScrollOptions.Static;
*/
// TODO: Set additional "acceptible" options
return(results);
}
public SQLCursor Open(string statement, SQLParameters parameters, SQLCursorType cursorType, SQLIsolationLevel cursorIsolationLevel, SQLCommandBehavior behavior)
{
CheckConnectionValid();
SQLCommand command = CreateCommand(true);
try
{
command.Statement = statement;
command.Parameters.AddRange(parameters);
command.CommandBehavior = behavior;
return(command.Open(cursorType, cursorIsolationLevel));
}
catch
{
command.Dispose();
throw;
}
}
protected abstract SQLCursor InternalOpen(SQLCursorType ACursorType, SQLIsolationLevel ACursorIsolationLevel);
protected abstract void InternalBeginTransaction(SQLIsolationLevel AIsolationLevel);
