public void Close()
{
try
{
Conn.Close();
}
catch (Exception)
{
throw;
}
}
public RecievePay(List <PaymentOrder> list)
{
using (VistaDBConnection connection = new VistaDBConnection(new Connection().ConnectionString))
{
try
{
foreach (var pay in list)
{
connection.Open();
using (VistaDBCommand command = new VistaDBCommand())
{
command.Connection = connection;
command.CommandText = $"UPDATE dbo.PaymentOrder SET Status = 'Recieved',RecievedBy ={LoginUser.UserId},RecievedDate = GETDATE() WHERE Id = {pay.Id}";
command.ExecuteNonQuery();
connection.Close();
}
}
MessageBox.Show(@"Selected payments confirmed successfully");
}
catch (VistaDBException exception)
{
Log.Error(exception);
}
}
}
public static void Test()
{
FileInfo fi = new FileInfo(AppDomain.CurrentDomain.BaseDirectory + "\\Cases\\InitialProject.cs");
VistaDBConnection conn = new VistaDBConnection("Data Source=" + AppDomain.CurrentDomain.BaseDirectory + "\\App_Data\\FmqStore.vdb3");
conn.Open();
string sql = "insert into [FileSystem](FileName,FileDir,FileSize,HashCode,BinData,FileVersion,CreateDate,LastChangeDate) values("
+ "@FileName,@FileDir,@FileSize,@HashCode,@BinData,1, @CreateDate, @LastChangeDate"
+ ")";
VistaDBCommand cmd = new VistaDBCommand(sql, conn);
cmd.Parameters.AddWithValue("@FileName", fi.Name);
cmd.Parameters.AddWithValue("@FileDir", "/cases");
cmd.Parameters.AddWithValue("@FileSize", fi.Length);
byte[] fBin = GetFileBytes(fi.FullName);
cmd.Parameters.AddWithValue("@HashCode", GetMD5Hash(fBin));
cmd.Parameters.AddWithValue("@BinData", fBin);
cmd.Parameters.AddWithValue("@CreateDate", fi.CreationTimeUtc);
cmd.Parameters.AddWithValue("@LastChangeDate", fi.LastWriteTimeUtc);
cmd.ExecuteNonQuery();
conn.Close();
conn.Dispose();
}
public DataTable ViewTransactions(string schoolname)
{
var data = new DataTable();
using (VistaDBConnection connection = new VistaDBConnection(new Connection().ConnectionString))
{
try
{
connection.Open();
using (VistaDBCommand command = new VistaDBCommand())
{
command.Connection = connection;
var query = command.CommandText = $"SELECT po.Id AS 'Transaction Id',po.Beneficiary,po.Payee,po.Amount,po.Date,po.Status FROM PaymentOrder po WHERE po.SchoolId=(SELECT s.Id FROM School s WHERE s.SchoolName='{schoolname}') and po.RecievedDate BETWEEN GETDATE()-30 AND GETDATE()";
var adapter = new VistaDBDataAdapter(command.CommandText, command.Connection);
adapter.Fill(data);
connection.Close();
}
}
catch (VistaDBException exception)
{
MessageBox.Show("Something went wrong");
Log.Error(exception);
}
}
return(data);
}
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 DataTable ViewPending(string serch)
{
var data = new DataTable();
using (VistaDBConnection connection = new VistaDBConnection(new Connection().ConnectionString))
{
try
{
connection.Open();
using (VistaDBCommand command = new VistaDBCommand())
{
command.Connection = connection;
var query = command.CommandText = $"SELECT PoNumber as PONumber,UserId,Date FROM dbo.PaymentOrder where status='Pending'";
var adapter = new VistaDBDataAdapter(command.CommandText, command.Connection);
adapter.Fill(data);
connection.Close();
}
}
catch (VistaDBException exception)
{
MessageBox.Show("Something went wrong");
Log.Error(exception);
}
}
return(data);
}
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 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 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 Table[] GetMetaData(string server, string database, string login, string password, string fn)
{
List<Table> tables = new List<Table>();
IVistaDBDatabase vistaDB = VistaDBEngine.Connections.OpenDDA().OpenDatabase(fn, VistaDBDatabaseOpenMode.NonexclusiveReadWrite, null);
string[] tableNames = (string[])vistaDB.EnumTables().ToArray(typeof(string));
vistaDB.Close();
var conn = new VistaDBConnection("Data Source=" + fn);
foreach (string tableName in tableNames)
{
string className = tableName;
if (tableName.StartsWith("tbl"))
className = tableName.Substring(3);
tables.Add(GetTable(conn, tableName, className));
}
conn.Close();
return tables.ToArray();
}
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 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 DataTable ViewPending()
{
var data = new DataTable();
using (VistaDBConnection connection = new VistaDBConnection(new Connection().ConnectionString))
{
try
{
connection.Open();
using (VistaDBCommand command = new VistaDBCommand())
{
command.Connection = connection;
var query = command.CommandText = $"SELECT Id,PoNumber,Beneficiary,Amount,(SELECT SchoolName FROM School s WHERE s.Id=p.SchoolId) AS 'School' FROM dbo.PaymentOrder p where status='Pending'";
var adapter = new VistaDBDataAdapter(command.CommandText, command.Connection);
adapter.Fill(data);
connection.Close();
}
}
catch (VistaDBException exception)
{
MessageBox.Show("Something went wrong");
Log.Error(exception);
}
}
return(data);
}
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 static object executeScalar(this API_VistaDB vistaDB, string command)
{
"[API_VistaDB] Executing Scalar: {0}".info(command);
VistaDBConnection sqlConnection = null;
try
{
sqlConnection = new VistaDBConnection(vistaDB.ConnectionString);
sqlConnection.Open();
var sqlCommand = new VistaDBCommand();
sqlCommand.Connection = sqlConnection;
sqlCommand.CommandText = command;
sqlCommand.CommandType = CommandType.Text;
return(sqlCommand.ExecuteScalar());
}
catch (Exception ex)
{
vistaDB.LastError = ex.Message;
"[executeNonQuery] {0}".error(ex.Message);
//ex.log();
}
finally
{
sqlConnection.Close();
}
return(null);
}
public void Dispose()
{
if (conn != null)
{
conn.Close();
conn.Dispose();
}
}
public static VistaDBConnection closeConnection(this API_VistaDB vistaDB, VistaDBConnection sqlConnection)
{
"[API_VistaDB] Closing Connection".info();
try
{
sqlConnection.Close();
return(sqlConnection);
}
catch (Exception ex)
{
vistaDB.LastError = ex.Message;
"[executeNonQuery] {0}".error(ex.Message);
//ex.log();
}
return(null);
}
private void testButton_Click(object sender, EventArgs e)
{
using (VistaDBConnection connection = new VistaDBConnection(ConnectionString))
{
try
{
connection.Open();
MessageBox.Show(this, "Connection Test Was Successful.",
"Connection Test", MessageBoxButtons.OK, MessageBoxIcon.Information);
connection.Close();
}
catch (Exception ex)
{
MessageBox.Show(this, "Connection Test Failed." + Environment.NewLine + Environment.NewLine + ex.GetBaseException().Message,
"Connection Test", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
public void Save(User user)
{
using (VistaDBConnection connection = new VistaDBConnection(new Connection().ConnectionString))
{
try
{
connection.Open();
using (VistaDBCommand command = new VistaDBCommand())
{
command.Connection = connection;
command.CommandText = $"INSERT INTO dbo.Users(RoleId,Name)VALUES({user.Type},N'{user.Name}')";
command.ExecuteNonQuery();
connection.Close();
MessageBox.Show($"successfully Added {user.Name}");
}
}
catch (VistaDBException exception)
{
MessageBox.Show(exception.Message);
}
}
}
public void CreateLogin(User user)
{
using (VistaDBConnection connection = new VistaDBConnection(new Connection().ConnectionString))
{
try
{
connection.Open();
using (VistaDBCommand command = new VistaDBCommand())
{
command.Connection = connection;
command.CommandText = $"INSERT INTO dbo.Login(UserName ,UserId ,Password)VALUES('{user.userName}',{user.Id},'{user.Password}')";
command.ExecuteNonQuery();
connection.Close();
MessageBox.Show($"successfully Added {user.userName}");
}
}
catch (VistaDBException exception)
{
MessageBox.Show(exception.Message);
}
}
}
public void User(User user)
{
using (VistaDBConnection connection = Connection.Connexion)
{
try
{
connection.Open();
using (VistaDBCommand command = new VistaDBCommand())
{
command.Connection = connection;
command.CommandText = $"INSERT INTO dbo.Users(Name,RoleId)VALUES('{user.Name}',2)";
command.ExecuteNonQuery();
connection.Close();
MessageBox.Show($"successfully added {user.Name}");
}
}
catch (VistaDBException exception)
{
MessageBox.Show("Something went wrong");
Log.Error(exception);
}
}
}
public void Payment(PaymentOrder payment, School school, User user)
{
using (VistaDBConnection connection = Connection.Connexion)
{
try
{
connection.Open();
using (VistaDBCommand command = new VistaDBCommand())
{
command.Connection = connection;
command.CommandText = $"INSERT INTO dbo.PaymentOrder(PoNumber,Beneficiary,Payee,Phone,Amount,Interest,SchoolId,UserId)VALUES('{payment.PoNumber}','{payment.Beneficiary}','{payment.Payee}','{payment.Phone}',{payment.Amount},{payment.Interest},{school.Id},{user.Id},)";
command.ExecuteNonQuery();
connection.Close();
MessageBox.Show($"Successfully added Payment for {school.SchoolName}");
}
}
catch (VistaDBException exception)
{
MessageBox.Show("Something went wrong");
Log.Error(exception);
}
}
}
public void CategoryInterest(int id, double amount, string categoryName)
{
using (VistaDBConnection connection = Connection.Connexion)
{
try
{
connection.Open();
using (VistaDBCommand command = new VistaDBCommand())
{
command.Connection = connection;
command.CommandText = $"UPDATE dbo.SchoolType SET Amount ={amount} WHERE Id = {id}";
command.ExecuteNonQuery();
connection.Close();
MessageBox.Show($"Successfully updated Commission for {categoryName} Category");
}
}
catch (VistaDBException exception)
{
MessageBox.Show("Something went wrong");
Log.Error(exception);
}
}
}
public void Save(PaymentOrder paymentOrder)
{
using (VistaDBConnection connection = new VistaDBConnection(new Connection().ConnectionString))
{
try
{
connection.Open();
using (VistaDBCommand command = new VistaDBCommand())
{
command.Connection = connection;
command.CommandText = $"INSERT INTO dbo.PaymentOrder(PoNumber,Beneficiary,Payee,Phone,Amount,Interest,SchoolId,UserId)VALUES('{paymentOrder.PoNumber}','{paymentOrder.Beneficiary}','{paymentOrder.Payee}','{paymentOrder.Phone}',{paymentOrder.Amount},{paymentOrder.Interest},{SchoolId},{UserId})";
command.ExecuteNonQuery();
connection.Close();
MessageBox.Show(@"successfully Saved Payment");
}
}
catch (VistaDBException exception)
{
MessageBox.Show("Something went wrong");
Log.Error(exception);
}
}
}
public void School(School school)
{
using (VistaDBConnection connection = Connection.Connexion)
{
try
{
connection.Open();
using (VistaDBCommand command = new VistaDBCommand())
{
command.Connection = connection;
//command.CommandText = $"INSERT INTO dbo.School(SchoolName)VALUES('{school.SchoolName}')";
command.CommandText = $"INSERT INTO dbo.School(TypeId,SchoolName)VALUES({school.TypeId},'{school.SchoolName}')";
command.ExecuteNonQuery();
connection.Close();
MessageBox.Show($"Successfully added {school.SchoolName}");
}
}
catch (VistaDBException exception)
{
MessageBox.Show("Something went wrong");
Log.Error(exception);
}
}
}
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);
}
/// <summary>
/// Private helper method that execute a VistaDBCommand (that returns a resultset) against the specified VistaDBTransaction and VistaDBConnection
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// FillDataset(conn, trans, CommandType.Text, "Select * from TableTransaction where ProdId=?", ds, new string[] {"orders"}, new VistaDBParameter("@prodid", 24));
/// </remarks>
/// <param name="connection">A valid VistaDBConnection</param>
/// <param name="transaction">A valid VistaDBTransaction</param>
/// <param name="commandType">The CommandType (TableDirect, Text)</param>
/// <param name="commandText">The T-SQL command</param>
/// <param name="dataSet">A dataset wich will contain the resultset generated by the command</param>
/// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced
/// by a user defined name (probably the actual table name)
/// </param>
/// <param name="commandParameters">An array of VistaDBParamters used to execute the command</param>
private static void FillDataset(VistaDBConnection connection, VistaDBTransaction transaction, CommandType commandType,
string commandText, DataSet dataSet, string[] tableNames,
params VistaDBParameter[] commandParameters)
{
if( connection == null ) throw new ArgumentNullException( "connection" );
if( dataSet == null ) throw new ArgumentNullException( "dataSet" );
// Create a command and prepare it for execution
VistaDBCommand command = new VistaDBCommand();
bool mustCloseConnection = false;
PrepareCommand(command, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection );
// Create the DataAdapter & DataSet
VistaDBDataAdapter dataAdapter = new VistaDBDataAdapter(command);
try
{
// Add the table mappings specified by the user
if (tableNames != null && tableNames.Length > 0)
{
string tableName = "Table";
for (int index=0; index < tableNames.Length; index++)
{
if( tableNames[index] == null || tableNames[index].Length == 0 ) throw new ArgumentException( "The tableNames parameter must contain a list of tables, a value was provided as null or empty string.", "tableNames" );
dataAdapter.TableMappings.Add(tableName, tableNames[index]);
tableName += (index + 1).ToString();
}
}
// Fill the DataSet using default values for DataTable names, etc
dataAdapter.Fill(dataSet);
// Detach the VistaDBParameters from the command object, so they can be used again
command.Parameters.Clear();
if( mustCloseConnection )
connection.Close();
}
finally
{
dataAdapter.Dispose();
}
}
/// <summary>
/// Execute a VistaDBCommand (that returns a resultset) against the database specified in the connection string
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// VistaDBDataReader dr = ExecuteReader(connString, CommandType.Text, "Select Orderid from TableTransaction where ProdId=?", new VistaDBParameter("@prodid", 24));
/// </remarks>
/// <param name="connectionString">A valid connection string for a VistaDBConnection</param>
/// <param name="commandType">The CommandType (TableDirect, Text)</param>
/// <param name="commandText">The T-SQL command</param>
/// <param name="commandParameters">An array of VistaDBParamters used to execute the command</param>
/// <returns>A VistaDBDataReader containing the resultset generated by the command</returns>
public static VistaDBDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText, params VistaDBParameter[] commandParameters)
{
if( connectionString == null || connectionString.Length == 0 ) throw new ArgumentNullException( "connectionString" );
VistaDBConnection connection = null;
try
{
connection = new VistaDBConnection(connectionString);
// Call the private overload that takes an internally owned connection in place of the connection string
return ExecuteReader(connection, null, commandType, commandText, commandParameters,VistaDBConnectionOwnership.Internal);
}
catch
{
// If we fail to return the VistaDBDatReader, we need to close the connection ourselves
if( connection != null ) connection.Close();
throw;
}
}
/// <summary>
/// Execute a VistaDBCommand (that returns a 1x1 resultset) against the specified VistaDBConnection
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int orderCount = (int)ExecuteScalar(conn, CommandType.Text, "Select count(Order) from TableTransaction where ProdId=?", new VistaDBParameter("@prodid", 24));
/// </remarks>
/// <param name="connection">A valid VistaDBConnection</param>
/// <param name="commandType">The CommandType (TableDirect, Text)</param>
/// <param name="commandText">The T-SQL command</param>
/// <param name="commandParameters">An array of VistaDBParamters used to execute the command</param>
/// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
public static object ExecuteScalar(VistaDBConnection connection, CommandType commandType, string commandText, params VistaDBParameter[] commandParameters)
{
if( connection == null ) throw new ArgumentNullException( "connection" );
// Create a command and prepare it for execution
VistaDBCommand cmd = new VistaDBCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, connection, (VistaDBTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection );
// Execute the command & return the results
object retval = cmd.ExecuteScalar();
// Detach the VistaDBParameters from the command object, so they can be used again
cmd.Parameters.Clear();
if( mustCloseConnection )
connection.Close();
return retval;
}
/// <summary>
/// Execute a VistaDBCommand (that returns a resultset) against the specified VistaDBConnection
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// string r = ExecuteXml(conn, CommandType.Text, "Select * from TableTransaction where ProdId=?", new VistaDBParameter("@prodid", 24));
/// </remarks>
/// <param name="connection">A valid VistaDBConnection</param>
/// <param name="commandType">The CommandType (TableDirect, Text)</param>
/// <param name="commandText">The T-SQL command using "FOR XML AUTO"</param>
/// <param name="commandParameters">An array of VistaDBParamters used to execute the command</param>
/// <returns>An string containing the resultset generated by the command</returns>
public static string ExecuteXml(VistaDBConnection connection, CommandType commandType, string commandText, params VistaDBParameter[] commandParameters)
{
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, (VistaDBTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection );
// Create the DataAdapter & DataSet
VistaDBDataAdapter obj_Adapter =new VistaDBDataAdapter (cmd);
DataSet ds=new DataSet();
ds.Locale =CultureInfo.InvariantCulture;
obj_Adapter.Fill(ds);
// Detach the VistaDBParameters from the command object, so they can be used again
cmd.Parameters.Clear();
string retval= ds.GetXml();
ds.Clear();
obj_Adapter.Dispose ();
return retval;
}
catch
{
if( mustCloseConnection )
connection.Close();
throw;
}
}
/// <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;
}
}
public void Close()
{
co.Close();
}
/// <summary>
/// Execute a VistaDBCommand (that returns a resultset) against the specified VistaDBConnection
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// DataSet ds = ExecuteDataset(conn, CommandType.Text, "Select * from TableTransaction where ProdId=?", new VistaDBParameter("@prodid", 24));
/// </remarks>
/// <param name="connection">A valid VistaDBConnection</param>
/// <param name="commandType">The CommandType (TableDirect, Text)</param>
/// <param name="commandText">The T-SQL command</param>
/// <param name="commandParameters">An array of VistaDBParamters used to execute the command</param>
/// <returns>A dataset containing the resultset generated by the command</returns>
public static DataSet ExecuteDataset(VistaDBConnection connection, CommandType commandType, string commandText, params VistaDBParameter[] commandParameters)
{
if( connection == null ) throw new ArgumentNullException( "connection" );
// Create a command and prepare it for execution
VistaDBCommand cmd = new VistaDBCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, connection, (VistaDBTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection );
// Create the DataAdapter & DataSet
VistaDBDataAdapter da = new VistaDBDataAdapter(cmd);
DataSet ds = new DataSet();
ds.Locale =CultureInfo.InvariantCulture;
// Fill the DataSet using default values for DataTable names, etc
da.Fill(ds);
// Detach the VistaDBParameters from the command object, so they can be used again
cmd.Parameters.Clear();
if( mustCloseConnection )
connection.Close();
// Return the dataset
return ds;
}