public IEnumerable <long> GetQualified(long qualifier)
{
List <long> qualifiedRelations = new List <long>();
using (VistaDBConnection conn = new VistaDBConnection(this.db))
{
VistaDBCommand cmd;
// get qualified root relations
cmd = new VistaDBCommand("select id from RootRelations where qualifierId=@qualifierId", conn);
cmd.Parameters.AddWithValue("@qualifierId", qualifier);
using (VistaDBDataReader rd = cmd.ExecuteReader())
{
while (rd.Read())
{
qualifiedRelations.Add(rd.GetInt64(0));
}
}
// get qualified inner relations
cmd.CommandText = "select id from InnerRelations where qualifierId=@qualifierId";
using (VistaDBDataReader rd = cmd.ExecuteReader())
{
while (rd.Read())
{
qualifiedRelations.Add(rd.GetInt64(0));
}
}
}
return(qualifiedRelations);
}
public static DataTable executeReader(this API_VistaDB vistaDB, string command)
{
var sqlConnection = new VistaDBConnection(vistaDB.ConnectionString);
sqlConnection.Open();
try
{
var sqlCommand = new VistaDBCommand();
sqlCommand.Connection = sqlConnection;
sqlCommand.CommandText = command;
sqlCommand.CommandType = CommandType.Text;
var reader = sqlCommand.ExecuteReader();
var dataTable = new DataTable();
dataTable.Load(reader);
return(dataTable);
}
catch (Exception ex)
{
vistaDB.LastError = ex.Message;
"[executeNonQuery] {0}".error(ex.Message);
//ex.log();
}
finally
{
if (sqlConnection.notNull())
{
sqlConnection.Close();
}
}
return(null);
}
public int FindId(User user)
{
int getId = 0;
using (VistaDBConnection connection = new VistaDBConnection(new Connection().ConnectionString))
{
try
{
connection.Open();
using (VistaDBCommand command = new VistaDBCommand())
{
command.Connection = connection;
command.CommandText = $"SELECT Id FROM dbo.Users where Name='{user.Name}'";
var reader = command.ExecuteReader();
while (reader.Read())
{
getId = reader.GetInt32(0);
}
connection.Close();
}
}
catch (VistaDBException exception)
{
MessageBox.Show(exception.Message);
}
}
return(getId);
}
public double StandingOrder()
{
double Value = 0;
using (VistaDBConnection connection = new VistaDBConnection(new Connection().ConnectionString))
{
try
{
connection.Open();
using (VistaDBCommand command = new VistaDBCommand())
{
command.Connection = connection;
command.CommandText = $"SELECT dbo.StandingOrder('{SchoolName}')";
var reader = command.ExecuteReader();
while (reader.Read())
{
Value = reader.GetDouble(0);
}
connection.Close();
}
}
catch (VistaDBException exception)
{
MessageBox.Show("Something went wrong");
Log.Error(exception);
}
}
return(Value);
}
public static List <string> Names()
{
List <string> names = new List <string>();
using (VistaDBConnection connection = Connection.Connexion)
{
try
{
connection.Open();
using (VistaDBCommand command = new VistaDBCommand())
{
command.Connection = connection;
command.CommandText = $"SELECT Name FROM dbo.Users";
var reader = command.ExecuteReader();
while (reader.Read())
{
names.Add(reader.GetString(0));
}
connection.Close();
if (names.Count < 1)
{
names.Add("No Data Available");
}
}
}
catch (VistaDBException exception)
{
MessageBox.Show("Something went wrong");
Log.Error(exception);
}
}
return(names);
}
public double Amount()
{
using (VistaDBConnection connection = Connection.Connexion)
{
try
{
connection.Open();
using (VistaDBCommand command = new VistaDBCommand())
{
command.Connection = connection;
command.CommandText = $"SELECT Amount FROM dbo.SchoolType WHERE Id={TypeId}";
var reader = command.ExecuteReader();
while (reader.Read())
{
amount = reader.GetInt32(0);
}
connection.Close();
}
}
catch (VistaDBException exception)
{
MessageBox.Show(exception.Message);
}
}
return(amount);
}
public double Recieved()
{
double Value = 0;
using (VistaDBConnection connection = new VistaDBConnection(new Connection().ConnectionString))
{
try
{
connection.Open();
using (VistaDBCommand command = new VistaDBCommand())
{
command.Connection = connection;
command.CommandText = $"SELECT dbo.Recieved('{SchoolName}')";
var reader = command.ExecuteReader();
while (reader.Read())
{
Value = reader.GetDouble(0);
}
connection.Close();
}
}
catch (VistaDBException exception)
{
MessageBox.Show(exception.Message);
}
}
recieved = Value;
return(recieved);
}
public School(string schoolName)
{
SchoolName = schoolName;
using (VistaDBConnection connection = Connection.Connexion)
{
try
{
connection.Open();
using (VistaDBCommand command = new VistaDBCommand())
{
command.Connection = connection;
command.CommandText = $"SELECT Id,TypeId FROM dbo.School WHERE SchoolName='{schoolName}'";
var reader = command.ExecuteReader();
while (reader.Read())
{
Id = reader.GetInt32(0);
TypeId = reader.GetInt32(1);
}
connection.Close();
}
}
catch (VistaDBException exception)
{
MessageBox.Show(exception.Message);
}
}
}
public int Role()
{
using (VistaDBConnection connection = Connection.Connexion)
{
try
{
connection.Open();
using (VistaDBCommand command = new VistaDBCommand())
{
command.Connection = connection;
command.CommandText = $"SELECT RoleId FROM dbo.Users WHERE Id={UserId}";
var reader = command.ExecuteReader();
while (reader.Read())
{
RoleId = reader.GetInt32(0);
}
connection.Close();
}
}
catch (VistaDBException exception)
{
MessageBox.Show("Something went wrong");
Log.Error(exception);
}
}
return(RoleId);
}
public IEnumerable <long> GetChildren(long parent, ParentModes mode, long qualifier)
{
List <long> children = new List <long>();
using (VistaDBConnection conn = new VistaDBConnection(this.db))
{
VistaDBCommand cmd;
cmd = new VistaDBCommand("select id from InnerRelations where nParentId=@parentId", conn);
cmd.Parameters.AddWithValue("@parentId", parent);
if (qualifier != 0)
{
cmd.CommandText += " and qualifierId=@qualifierId";
cmd.Parameters.AddWithValue("@qualifierId", qualifier);
}
if (mode == ParentModes.normative || mode == ParentModes.both)
{
using (VistaDBDataReader rd = cmd.ExecuteReader())
{
while (rd.Read())
{
children.Add(rd.GetInt64(0));
}
}
}
if (mode == ParentModes.associative || mode == ParentModes.both)
{
cmd.CommandText = cmd.CommandText.Replace("nParentId", "aParentId");
using (VistaDBDataReader rd = cmd.ExecuteReader())
{
while (rd.Read())
{
children.Add(rd.GetInt64(0));
}
}
}
}
return(children);
}
esDataResponse IDataProvider.ExecuteReader(esDataRequest request)
{
esDataResponse response = new esDataResponse();
VistaDBCommand cmd = null;
try
{
cmd = new VistaDBCommand();
if (request.CommandTimeout != null)
{
cmd.CommandTimeout = request.CommandTimeout.Value;
}
if (request.Parameters != null)
{
Shared.AddParameters(cmd, request);
}
switch (request.QueryType)
{
case esQueryType.TableDirect:
cmd.CommandType = CommandType.TableDirect;
cmd.CommandText = request.QueryText;
break;
case esQueryType.StoredProcedure:
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = Shared.CreateFullName(request);
break;
case esQueryType.Text:
cmd.CommandType = CommandType.Text;
cmd.CommandText = request.QueryText;
break;
case esQueryType.DynamicQuery:
cmd = QueryBuilder.PrepareCommand(request);
break;
}
cmd.Connection = new VistaDBConnection(request.ConnectionString);
cmd.Connection.Open();
response.DataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
CleanupCommand(cmd);
response.Exception = ex;
}
return(response);
}
public static bool Valid(string username, string password)
{
int user = 0;
bool valid = false;
using (VistaDBConnection connection = Connection.Connexion)
{
try
{
connection.Open();
using (VistaDBCommand command = new VistaDBCommand())
{
command.Connection = connection;
command.CommandText = $"SELECT UserId FROM dbo.Login WHERE UserName='******' AND Password='******'";
var reader = command.ExecuteReader();
while (reader.Read())
{
user++;
UserId = reader.GetInt32(0);
}
connection.Close();
if (user == 1)
{
valid = true;
}
}
}
catch (VistaDBException exception)
{
MessageBox.Show("Something went wrong");
Log.Error(exception);
}
}
return(valid);
}
tgDataResponse IDataProvider.ExecuteReader(tgDataRequest request)
{
tgDataResponse response = new tgDataResponse();
VistaDBCommand cmd = null;
try
{
cmd = new VistaDBCommand();
if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;
if (request.Parameters != null) Shared.AddParameters(cmd, request);
switch (request.QueryType)
{
case tgQueryType.TableDirect:
cmd.CommandType = CommandType.TableDirect;
cmd.CommandText = request.QueryText;
break;
case tgQueryType.StoredProcedure:
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = Shared.CreateFullName(request);
break;
case tgQueryType.Text:
cmd.CommandType = CommandType.Text;
cmd.CommandText = request.QueryText;
break;
case tgQueryType.DynamicQuery:
cmd = QueryBuilder.PrepareCommand(request);
break;
}
cmd.Connection = new VistaDBConnection(request.ConnectionString);
cmd.Connection.Open();
#region Profiling
if (sTraceHandler != null)
{
using (esTraceArguments esTrace = new esTraceArguments(request, cmd, "ExecuteReader", System.Environment.StackTrace))
{
try
{
response.DataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
esTrace.Exception = ex.Message;
throw;
}
}
}
else
#endregion Profiling
{
response.DataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
}
catch (Exception ex)
{
CleanupCommand(cmd);
response.Exception = ex;
}
return response;
}
public void ParseVistaDB(string vistaTableName, string qualifiedTableName, DataTable dataTable)
{
Program objProgram = new Program();
//Create a connection to VistaDB
string connectionString = @"Data Source=C:\superpay\Superpay4.vdb6;Open Mode=SingleProcessReadWrite";
//Create string connection to Oracle
string conString = "User Id=dwrrhh; password=milenaok;" +
//How to connect to an Oracle DB without SQL*Net configuration file
//also known as tnsnames.ora.
"Data Source=cocgsa016.cupagroup.com:1521/CUPIRE; Pooling=false;";
try
{
//Read data from VistaDb and create variable with Values
using (VistaDBConnection dbConn = new VistaDBConnection(connectionString))
{
dbConn.Open();
VistaDBCommand command = dbConn.CreateCommand();
command.CommandText = "SELECT * FROM " + vistaTableName;
using (VistaDBDataReader dr = command.ExecuteReader())
{
int count = 0;
while (dr.Read())
{
count++;
DataRow dataRow = dataTable.NewRow();
for (int i = 0; dataTable.Columns.Count > i; i++)
{
string colName = dataTable.Columns[i].ColumnName.ToString();
int colLength = dr[colName].ToString().Length;
if (colLength == 0)
{
dataRow[colName] = 0.0;
}
else
{
string s1 = dr[colName].ToString();
string s2 = s1.Replace(",", ".");
dataRow[colName] = s2;
}
//Console.WriteLine("Columna Nº: " + count + "-- Nombre COL: " + colName + " -- Valor: " + dataRow[colName]);
}
dataTable.Rows.Add(dataRow);
}
// Send parse data to Oracle
objProgram.WriteToOracle(qualifiedTableName, conString, dataTable);
}
// Just a little pause for viewing
//Console.ReadLine();
}
}
catch (Exception e)
{
Console.WriteLine("Error encountered during parsed data from VistaDB table name "
+ vistaTableName + " con error " + e.Message);
throw;
}
finally
{
Console.WriteLine("Parse data from Table " + vistaTableName + " of VistaDb was successfully");
}
}
protected static void OnRowUpdated(object sender, VistaDBRowUpdatedEventArgs e)
{
try
{
PropertyCollection props = e.Row.Table.ExtendedProperties;
if (props.ContainsKey("props"))
{
props = (PropertyCollection)props["props"];
}
if (e.Status == UpdateStatus.Continue && (e.StatementType == StatementType.Insert || e.StatementType == StatementType.Update))
{
esDataRequest request = props["esDataRequest"] as esDataRequest;
esEntitySavePacket packet = (esEntitySavePacket)props["esEntityData"];
string source = props["Source"] as string;
if (e.StatementType == StatementType.Insert)
{
if (props.Contains("AutoInc"))
{
string autoInc = props["AutoInc"] as string;
VistaDBCommand cmd = new VistaDBCommand();
cmd.Connection = e.Command.Connection;
cmd.Transaction = e.Command.Transaction;
cmd.CommandText = "SELECT LastIdentity([" + autoInc + "]) FROM [" + source + "]";
object o = null;
o = cmd.ExecuteScalar();
if (o != null)
{
e.Row[autoInc] = o;
e.Command.Parameters["@" + autoInc].Value = o;
}
}
if (props.Contains("EntitySpacesConcurrency"))
{
string esConcurrencyColumn = props["EntitySpacesConcurrency"] as string;
packet.CurrentValues[esConcurrencyColumn] = 1;
}
}
if (props.Contains("Timestamp"))
{
string column = props["Timestamp"] as string;
VistaDBCommand cmd = new VistaDBCommand();
cmd.Connection = e.Command.Connection;
cmd.Transaction = e.Command.Transaction;
cmd.CommandText = "SELECT LastTimestamp('" + source + "');";
object o = null;
o = cmd.ExecuteScalar();
if (o != null)
{
e.Command.Parameters["@" + column].Value = o;
}
}
//-------------------------------------------------------------------------------------------------
// Fetch any defaults, SQLite doesn't support output parameters so we gotta do this the hard way
//-------------------------------------------------------------------------------------------------
if (props.Contains("Defaults"))
{
// Build the Where parameter and parameters
VistaDBCommand cmd = new VistaDBCommand();
cmd.Connection = e.Command.Connection;
cmd.Transaction = e.Command.Transaction;
string select = (string)props["Defaults"];
string[] whereParameters = ((string)props["Where"]).Split(',');
string comma = String.Empty;
string where = String.Empty;
int i = 1;
foreach (string parameter in whereParameters)
{
VistaDBParameter p = new VistaDBParameter("@p" + i++.ToString(), e.Row[parameter]);
cmd.Parameters.Add(p);
where += comma + "[" + parameter + "]=" + p.ParameterName;
comma = " AND ";
}
// Okay, now we can execute the sql and get any values that have defaults that were
// null at the time of the insert and/or our timestamp
cmd.CommandText = "SELECT " + select + " FROM [" + request.ProviderMetadata.Source + "] WHERE " + where + ";";
VistaDBDataReader rdr = null;
try
{
rdr = cmd.ExecuteReader(CommandBehavior.SingleResult);
if (rdr.Read())
{
select = select.Replace("[", String.Empty).Replace("]", String.Empty);
string[] selectCols = select.Split(',');
for (int k = 0; k < selectCols.Length; k++)
{
packet.CurrentValues[selectCols[k]] = rdr.GetValue(k);
}
}
}
finally
{
// Make sure we close the reader no matter what
if (rdr != null)
{
rdr.Close();
}
}
}
if (e.StatementType == StatementType.Update)
{
string colName = props["EntitySpacesConcurrency"] as string;
object o = e.Row[colName];
VistaDBParameter p = e.Command.Parameters["@" + colName];
object v = null;
switch (Type.GetTypeCode(o.GetType()))
{
case TypeCode.Int16: v = ((System.Int16)o) + 1; break;
case TypeCode.Int32: v = ((System.Int32)o) + 1; break;
case TypeCode.Int64: v = ((System.Int64)o) + 1; break;
case TypeCode.UInt16: v = ((System.UInt16)o) + 1; break;
case TypeCode.UInt32: v = ((System.UInt32)o) + 1; break;
case TypeCode.UInt64: v = ((System.UInt64)o) + 1; break;
}
p.Value = v;
}
}
}
catch { }
}
/// <summary>
/// Returns the specified error from the database, or null
/// if it does not exist.
/// </summary>
public override ErrorLogEntry GetError(string id)
{
if (id == null)
{
throw new ArgumentNullException("id");
}
if (id.Length == 0)
{
throw new ArgumentException(null, "id");
}
int errorId;
try
{
errorId = int.Parse(id, CultureInfo.InvariantCulture);
}
catch (FormatException e)
{
throw new ArgumentException(e.Message, "id", e);
}
catch (OverflowException e)
{
throw new ArgumentException(e.Message, "id", e);
}
string errorXml;
using (VistaDBConnection connection = new VistaDBConnection(this.ConnectionString))
using (VistaDBCommand command = connection.CreateCommand())
{
command.CommandText = @"SELECT AllXml
FROM ELMAH_Error
WHERE ErrorId = @ErrorId";
command.CommandType = CommandType.Text;
VistaDBParameterCollection parameters = command.Parameters;
parameters.Add("@ErrorId", VistaDBType.Int).Value = errorId;
connection.Open();
// NB this has been deliberately done like this as command.ExecuteScalar
// is not exhibiting the expected behaviour in VistaDB at the moment
using (VistaDBDataReader dr = command.ExecuteReader())
{
if (dr.Read())
{
errorXml = dr[0] as string;
}
else
{
errorXml = null;
}
}
}
if (errorXml == null)
{
return(null);
}
Error error = ErrorXml.DecodeString(errorXml);
return(new ErrorLogEntry(this, id, error));
}
esDataResponse IDataProvider.ExecuteReader(esDataRequest request)
{
esDataResponse response = new esDataResponse();
VistaDBCommand cmd = null;
try
{
cmd = new VistaDBCommand();
if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;
if (request.Parameters != null) Shared.AddParameters(cmd, request);
switch (request.QueryType)
{
case esQueryType.TableDirect:
cmd.CommandType = CommandType.TableDirect;
cmd.CommandText = request.QueryText;
break;
case esQueryType.StoredProcedure:
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = Shared.CreateFullName(request);
break;
case esQueryType.Text:
cmd.CommandType = CommandType.Text;
cmd.CommandText = request.QueryText;
break;
case esQueryType.DynamicQuery:
cmd = QueryBuilder.PrepareCommand(request);
break;
}
cmd.Connection = new VistaDBConnection(request.ConnectionString);
cmd.Connection.Open();
response.DataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
CleanupCommand(cmd);
response.Exception = ex;
}
return response;
}
protected static void OnRowUpdated(object sender, VistaDBRowUpdatedEventArgs e)
{
try
{
PropertyCollection props = e.Row.Table.ExtendedProperties;
if (props.ContainsKey("props"))
{
props = (PropertyCollection)props["props"];
}
if (e.Status == UpdateStatus.Continue && (e.StatementType == StatementType.Insert || e.StatementType == StatementType.Update))
{
tgDataRequest request = props["tgDataRequest"] as tgDataRequest;
tgEntitySavePacket packet = (tgEntitySavePacket)props["esEntityData"];
string source = props["Source"] as string;
if (e.StatementType == StatementType.Insert)
{
if (props.Contains("AutoInc"))
{
string autoInc = props["AutoInc"] as string;
VistaDBCommand cmd = new VistaDBCommand();
cmd.Connection = e.Command.Connection;
cmd.Transaction = e.Command.Transaction;
cmd.CommandText = "SELECT LastIdentity([" + autoInc + "]) FROM [" + source + "]";
object o = null;
#region Profiling
if (sTraceHandler != null)
{
using (esTraceArguments esTrace = new esTraceArguments(request, cmd, "OnRowUpdated", System.Environment.StackTrace))
{
try
{
o = cmd.ExecuteScalar();
}
catch (Exception ex)
{
esTrace.Exception = ex.Message;
throw;
}
}
}
else
#endregion Profiling
{
o = cmd.ExecuteScalar();
}
if (o != null)
{
e.Row[autoInc] = o;
e.Command.Parameters["@" + autoInc].Value = o;
}
}
if (props.Contains("EntitySpacesConcurrency"))
{
string esConcurrencyColumn = props["EntitySpacesConcurrency"] as string;
packet.CurrentValues[esConcurrencyColumn] = 1;
}
}
if (props.Contains("Timestamp"))
{
string column = props["Timestamp"] as string;
VistaDBCommand cmd = new VistaDBCommand();
cmd.Connection = e.Command.Connection;
cmd.Transaction = e.Command.Transaction;
cmd.CommandText = "SELECT LastTimestamp('" + source + "');";
object o = null;
#region Profiling
if (sTraceHandler != null)
{
using (esTraceArguments esTrace = new esTraceArguments(request, cmd, "OnRowUpdated", System.Environment.StackTrace))
{
try
{
o = cmd.ExecuteScalar();
}
catch (Exception ex)
{
esTrace.Exception = ex.Message;
throw;
}
}
}
else
#endregion Profiling
{
o = cmd.ExecuteScalar();
}
if (o != null)
{
e.Command.Parameters["@" + column].Value = o;
}
}
//-------------------------------------------------------------------------------------------------
// Fetch any defaults, SQLite doesn't support output parameters so we gotta do this the hard way
//-------------------------------------------------------------------------------------------------
if (props.Contains("Defaults"))
{
// Build the Where parameter and parameters
VistaDBCommand cmd = new VistaDBCommand();
cmd.Connection = e.Command.Connection;
cmd.Transaction = e.Command.Transaction;
string select = (string)props["Defaults"];
string[] whereParameters = ((string)props["Where"]).Split(',');
string comma = String.Empty;
string where = String.Empty;
int i = 1;
foreach (string parameter in whereParameters)
{
VistaDBParameter p = new VistaDBParameter("@p" + i++.ToString(), e.Row[parameter]);
cmd.Parameters.Add(p);
where += comma + "[" + parameter + "]=" + p.ParameterName;
comma = " AND ";
}
// Okay, now we can execute the sql and get any values that have defaults that were
// null at the time of the insert and/or our timestamp
cmd.CommandText = "SELECT " + select + " FROM [" + request.ProviderMetadata.Source + "] WHERE " + where + ";";
VistaDBDataReader rdr = null;
try
{
#region Profiling
if (sTraceHandler != null)
{
using (esTraceArguments esTrace = new esTraceArguments(request, cmd, "OnRowUpdated", System.Environment.StackTrace))
{
try
{
rdr = cmd.ExecuteReader(CommandBehavior.SingleResult);
}
catch (Exception ex)
{
esTrace.Exception = ex.Message;
throw;
}
}
}
else
#endregion Profiling
{
rdr = cmd.ExecuteReader(CommandBehavior.SingleResult);
}
if (rdr.Read())
{
select = select.Replace("[", String.Empty).Replace("]", String.Empty);
string[] selectCols = select.Split(',');
for (int k = 0; k < selectCols.Length; k++)
{
packet.CurrentValues[selectCols[k]] = rdr.GetValue(k);
}
}
}
finally
{
// Make sure we close the reader no matter what
if (rdr != null) rdr.Close();
}
}
if (e.StatementType == StatementType.Update)
{
string colName = props["EntitySpacesConcurrency"] as string;
object o = e.Row[colName];
VistaDBParameter p = e.Command.Parameters["@" + colName];
object v = null;
switch (Type.GetTypeCode(o.GetType()))
{
case TypeCode.Int16: v = ((System.Int16)o) + 1; break;
case TypeCode.Int32: v = ((System.Int32)o) + 1; break;
case TypeCode.Int64: v = ((System.Int64)o) + 1; break;
case TypeCode.UInt16: v = ((System.UInt16)o) + 1; break;
case TypeCode.UInt32: v = ((System.UInt32)o) + 1; break;
case TypeCode.UInt64: v = ((System.UInt64)o) + 1; break;
}
p.Value = v;
}
}
}
catch { }
}
/// <summary>
/// Create and prepare a VistaDBCommand, and call ExecuteReader with the appropriate CommandBehavior.
/// </summary>
/// <remarks>
/// If we created and opened the connection, we want the connection to be closed when the DataReader is closed.
///
/// If the caller provided the connection, we want to leave it to them to manage.
/// </remarks>
/// <param name="connection">A valid VistaDBConnection, on which to execute this command</param>
/// <param name="transaction">A valid VistaDBTransaction, or 'null'</param>
/// <param name="commandType">The CommandType (TableDirect, Text)</param>
/// <param name="commandText">The T-SQL command</param>
/// <param name="commandParameters">An array of VistaDBParameters to be associated with the command or 'null' if no parameters are required</param>
/// <param name="connectionOwnership">Indicates whether the connection parameter was provided by the caller, or created by VistaDBHelper</param>
/// <returns>VistaDBDataReader containing the results of the command</returns>
private static VistaDBDataReader ExecuteReader(VistaDBConnection connection, VistaDBTransaction transaction, CommandType commandType, string commandText, VistaDBParameter[] commandParameters, VistaDBConnectionOwnership connectionOwnership)
{
if( connection == null ) throw new ArgumentNullException( "connection" );
bool mustCloseConnection = false;
// Create a command and prepare it for execution
VistaDBCommand cmd = new VistaDBCommand();
try
{
PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection );
// Create a reader
VistaDBDataReader dataReader;
// Call ExecuteReader with the appropriate CommandBehavior
if (connectionOwnership == VistaDBConnectionOwnership.External)
{
dataReader = cmd.ExecuteReader();
}
else
{
dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
// Detach the VistaDBParameters from the command object, so they can be used again.
// HACK: There is a problem here, the output parameter values are fletched
// when the reader is closed, so if the parameters are detached from the command
// then the VistaDBReader can´t set its values.
// When this happen, the parameters can´t be used again in other command.
bool canClear = true;
foreach(VistaDBParameter commandParameter in cmd.Parameters)
{
if (commandParameter.Direction != ParameterDirection.Input)
canClear = false;
}
if (canClear)
{
cmd.Parameters.Clear();
}
return dataReader;
}
catch
{
if( mustCloseConnection )
connection.Close();
throw;
}
}