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);
}
static public VistaDBCommand BuildStoredProcDeleteCommand(tgDataRequest request)
{
VistaDBCommand cmd = new VistaDBCommand();
if (request.CommandTimeout != null)
{
cmd.CommandTimeout = request.CommandTimeout.Value;
}
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = Delimiters.StoredProcNameOpen + request.ProviderMetadata.spDelete + Delimiters.StoredProcNameClose;
Dictionary <string, VistaDBParameter> types = Cache.GetParameters(request);
VistaDBParameter p;
foreach (tgColumnMetadata col in request.Columns)
{
if (col.IsInPrimaryKey)
{
p = types[col.Name];
p = CloneParameter(p);
p.SourceVersion = DataRowVersion.Current;
cmd.Parameters.Add(p);
}
}
return(cmd);
}
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 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> 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);
}
protected static string GetFromStatement(esDataRequest request, esDynamicQuerySerializable query, VistaDBCommand cmd, ref int pindex)
{
IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal;
string sql = String.Empty;
if (iQuery.InternalFromQuery == null)
{
sql = Shared.CreateFullName(query);
if (iQuery.JoinAlias != " ")
{
sql += " " + iQuery.JoinAlias;
}
}
else
{
IDynamicQuerySerializableInternal iSubQuery = iQuery.InternalFromQuery as IDynamicQuerySerializableInternal;
iSubQuery.IsInSubQuery = true;
sql += "(";
sql += BuildQuery(request, iQuery.InternalFromQuery, cmd, ref pindex);
sql += ")";
if (iSubQuery.SubQueryAlias != " ")
{
sql += " AS " + iSubQuery.SubQueryAlias;
}
iSubQuery.IsInSubQuery = false;
}
return sql;
}
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 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);
}
DataTable IPlugin.GetViews(string database)
{
DataTable metaData = new DataTable();
try
{
metaData = context.CreateViewsDataTable();
using (VistaDBConnection conn = new VistaDBConnection(context.ConnectionString))
{
using (VistaDBCommand cmd = new VistaDBCommand("SELECT * FROM GetViews()", conn))
{
using (VistaDBDataAdapter da = new VistaDBDataAdapter(cmd))
{
DataTable views = new DataTable();
da.Fill(views);
foreach (DataRow vistaRow in views.Rows)
{
DataRow row = metaData.NewRow();
metaData.Rows.Add(row);
row["TABLE_NAME"] = vistaRow["VIEW_NAME"];
row["DESCRIPTION"] = vistaRow["DESCRIPTION"];
row["VIEW_TEXT"] = vistaRow["VIEW_DEFINITION"];
row["IS_UPDATABLE"] = vistaRow["IS_UPDATABLE"];
}
}
}
}
}
catch {}
return(metaData);
}
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 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 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);
}
}
}
protected override IDbCommand CreateCommand(string sqlQuery, CSParameterCollection parameters)
{
VistaDBCommand sqlCommand = (VistaDBCommand)Connection.CreateCommand();
sqlCommand.Transaction = (VistaDBTransaction)CurrentTransaction;
if (sqlQuery.StartsWith("!"))
{
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = sqlQuery.Substring(1);
}
else
{
sqlCommand.CommandType = CommandType.Text;
sqlCommand.CommandText = sqlQuery;
}
if (parameters != null && !parameters.IsEmpty)
{
foreach (CSParameter csParameter in parameters)
{
IDbDataParameter dataParameter = sqlCommand.CreateParameter();
dataParameter.ParameterName = csParameter.Name;
dataParameter.Direction = ParameterDirection.Input;
dataParameter.Value = ConvertParameter(csParameter.Value);
sqlCommand.Parameters.Add(dataParameter);
}
}
return(sqlCommand);
}
override internal IDbCommand _LoadFromRawSql(string rawSql, params object[] parameters)
{
int i = 0;
string token = "";
string sIndex = "";
string param = "";
VistaDBCommand cmd = new VistaDBCommand();
foreach (object o in parameters)
{
sIndex = i.ToString();
token = '{' + sIndex + '}';
param = "@p" + sIndex;
rawSql = rawSql.Replace(token, param);
VistaDBParameter p = new VistaDBParameter(param, o);
cmd.Parameters.Add(p);
i++;
}
cmd.CommandText = rawSql;
return(cmd);
}
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);
}
static public VistaDBCommand BuildStoredProcUpdateCommand(tgDataRequest request)
{
VistaDBCommand cmd = new VistaDBCommand();
if (request.CommandTimeout != null)
{
cmd.CommandTimeout = request.CommandTimeout.Value;
}
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = Delimiters.StoredProcNameOpen + request.ProviderMetadata.spUpdate + Delimiters.StoredProcNameClose;
PopulateStoredProcParameters(cmd, request);
foreach (tgColumnMetadata col in request.Columns)
{
if (col.IsComputed)
{
VistaDBParameter p = cmd.Parameters[Delimiters.Param + col.PropertyName];
p.Direction = ParameterDirection.InputOutput;
}
}
return(cmd);
}
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);
}
static public VistaDBCommand BuildStoredProcInsertCommand(tgDataRequest request)
{
VistaDBCommand cmd = new VistaDBCommand();
if (request.CommandTimeout != null)
{
cmd.CommandTimeout = request.CommandTimeout.Value;
}
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = Delimiters.StoredProcNameOpen + request.ProviderMetadata.spInsert + Delimiters.StoredProcNameClose;
PopulateStoredProcParameters(cmd, request);
foreach (tgColumnMetadata col in request.Columns)
{
if (col.HasDefault && col.Default.ToLower() == "GUID()")
{
VistaDBParameter p = cmd.Parameters[Delimiters.Param + col.PropertyName];
p.Direction = ParameterDirection.InputOutput;
}
else if (col.IsComputed || col.IsAutoIncrement)
{
VistaDBParameter p = cmd.Parameters[Delimiters.Param + col.PropertyName];
p.Direction = ParameterDirection.Output;
}
}
return(cmd);
}
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();
}
static public VistaDBCommand BuildDynamicDeleteCommand(tgDataRequest request, List <string> modifiedColumns)
{
Dictionary <string, VistaDBParameter> types = Cache.GetParameters(request);
VistaDBCommand cmd = new VistaDBCommand();
if (request.CommandTimeout != null)
{
cmd.CommandTimeout = request.CommandTimeout.Value;
}
string sql = "DELETE FROM " + CreateFullName(request) + " ";
string comma = String.Empty;
comma = String.Empty;
sql += " WHERE ";
foreach (tgColumnMetadata col in request.Columns)
{
if (col.IsInPrimaryKey || col.IsTiraggoConcurrency || col.IsConcurrency)
{
VistaDBParameter p = types[col.Name];
cmd.Parameters.Add(CloneParameter(p));
sql += comma;
sql += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + p.ParameterName;
comma = " AND ";
}
}
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
return(cmd);
}
/// <summary>
/// Call the export schema and data sql function to write out the xml file
/// </summary>
/// <param name="outputFilename">Name of the file to write to disk</param>
public static void CallExportSchemaAndDataSQL(string outputFilename)
{
Console.WriteLine("Attempting to execute CLR Proc ExportSchemaAndData");
using (VistaDBConnection connection = new VistaDBConnection())
{
connection.ConnectionString = SampleRunner.ConnectionString;
connection.Open();
try
{
using (VistaDBCommand command = new VistaDBCommand())
{
// Straight forward way to call a function is just using SELECT
// You cannot EXEC a SqlFunction, and you cannot set the command here to be a stored proc
// Setting this command to a stored proc is a common error, the two are not the same
// SqlFunction = SELECT to call
// SqlProcdure = EXEC or direct call using StoredProcedure command type
command.Connection = connection;
command.CommandText = string.Format("SELECT ExportSchemaAndData('{0}');", outputFilename);
// This command does not return anything in the rowset, so just execute non query
command.ExecuteNonQuery();
}
Console.WriteLine(string.Format("Schema and Data export to {0}\\{1}.xml", Directory.GetCurrentDirectory(), outputFilename));
}
catch (Exception e)
{
Console.WriteLine("Failed to execute CLR-Proc ExportSchemaAndData, Reason: " + e.Message);
}
}
}
// If it's an Insert we fetch the @@Identity value and stuff it in the proper column
protected void OnRowUpdated(object sender, VistaDBRowUpdatedEventArgs e)
{
try
{
if (e.Status == UpdateStatus.Continue && e.StatementType == StatementType.Insert)
{
TransactionMgr txMgr = TransactionMgr.ThreadTransactionMgr();
string[] identityCols = this.GetAutoKeyColumns().Split(';');
VistaDBCommand cmd = new VistaDBCommand();
foreach (string col in identityCols)
{
cmd.CommandText = "SELECT LastIdentity([" + col + "]) FROM [" + this.QuerySource + "]";
// We make sure we enlist in the ongoing transaction, otherwise, we
// would most likely deadlock
txMgr.Enlist(cmd, this);
object o = cmd.ExecuteScalar(); // Get the Identity Value
txMgr.DeEnlist(cmd, this);
if (o != null)
{
e.Row[col] = o;
}
}
e.Row.AcceptChanges();
}
}
catch {}
}
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 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);
}
protected override IDbCommand GetInsertCommand()
{
VistaDBCommand cmd = new VistaDBCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText =
@"INSERT INTO [AggregateTest]
(
[DepartmentID],
[FirstName],
[LastName],
[Age],
[HireDate],
[Salary],
[IsActive]
)
VALUES
(
@DepartmentID,
@FirstName,
@LastName,
@Age,
@HireDate,
@Salary,
@IsActive
)" ;
CreateParameters(cmd);
return(cmd);
}
protected override IDbCommand GetUpdateCommand()
{
VistaDBCommand cmd = new VistaDBCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText =
@"UPDATE [Employees] SET
[LastName]=@LastName,
[FirstName]=@FirstName,
[Title]=@Title,
[TitleOfCourtesy]=@TitleOfCourtesy,
[BirthDate]=@BirthDate,
[HireDate]=@HireDate,
[Address]=@Address,
[City]=@City,
[Region]=@Region,
[PostalCode]=@PostalCode,
[Country]=@Country,
[HomePhone]=@HomePhone,
[Extension]=@Extension,
[Photo]=@Photo,
[Notes]=@Notes,
[ReportsTo]=@ReportsTo
WHERE
[EmployeeID]=@EmployeeID" ;
CreateParameters(cmd);
return(cmd);
}
esDataResponse IDataProvider.ExecuteNonQuery(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;
}
try
{
esTransactionScope.Enlist(cmd, request.ConnectionString, CreateIDbConnectionDelegate);
response.RowsEffected = cmd.ExecuteNonQuery();
}
finally
{
esTransactionScope.DeEnlist(cmd);
}
if (request.Parameters != null)
{
Shared.GatherReturnParameters(cmd, request, response);
}
}
catch (Exception ex)
{
CleanupCommand(cmd);
response.Exception = ex;
}
return(response);
}
static private void CleanupCommand(VistaDBCommand cmd)
{
if (cmd != null && cmd.Connection != null)
{
if (cmd.Connection.State == ConnectionState.Open)
{
cmd.Connection.Close();
}
}
}
private void tabControl1_Selected(object sender, TabControlEventArgs e)
{
// Move the input focus to the query builder.
// This will fire Leave event in the text box and update the query builder
// with modified query text.
queryBuilder1.Focus();
Application.DoEvents();
// Try to execute the query using current database connection:
if (e.TabPage == tabPageData)
{
dataGridView1.DataSource = null;
if (queryBuilder1.MetadataProvider != null && queryBuilder1.MetadataProvider.Connected)
{
VistaDBCommand command = (VistaDBCommand)queryBuilder1.MetadataProvider.Connection.CreateCommand();
command.CommandText = queryBuilder1.SQL;
// handle the query parameters
if (queryBuilder1.Parameters.Count > 0)
{
for (int i = 0; i < queryBuilder1.Parameters.Count; i++)
{
if (!command.Parameters.Contains(queryBuilder1.Parameters[i].FullName))
{
VistaDBParameter parameter = new VistaDBParameter();
parameter.ParameterName = queryBuilder1.Parameters[i].FullName;
parameter.DbType = queryBuilder1.Parameters[i].DataType;
command.Parameters.Add(parameter);
}
}
using (QueryParametersForm qpf = new QueryParametersForm(command))
{
qpf.ShowDialog();
}
}
VistaDBDataAdapter adapter = new VistaDBDataAdapter(command);
DataSet dataset = new DataSet();
try
{
adapter.Fill(dataset, "QueryResult");
dataGridView1.DataSource = dataset.Tables["QueryResult"];
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "SQL query error");
}
}
}
}
static private esDataResponse LoadDataSetFromStoredProcedure(esDataRequest request)
{
esDataResponse response = new esDataResponse();
VistaDBCommand cmd = null;
try
{
DataSet dataSet = new DataSet();
cmd = new VistaDBCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = Shared.CreateFullName(request);
if (request.CommandTimeout != null)
{
cmd.CommandTimeout = request.CommandTimeout.Value;
}
if (request.Parameters != null)
{
Shared.AddParameters(cmd, request);
}
VistaDBDataAdapter da = new VistaDBDataAdapter();
da.SelectCommand = cmd;
try
{
esTransactionScope.Enlist(da.SelectCommand, request.ConnectionString, CreateIDbConnectionDelegate);
da.Fill(dataSet);
}
finally
{
esTransactionScope.DeEnlist(da.SelectCommand);
}
response.DataSet = dataSet;
if (request.Parameters != null)
{
Shared.GatherReturnParameters(cmd, request, response);
}
}
catch (Exception)
{
CleanupCommand(cmd);
throw;
}
finally
{
}
return(response);
}
static private DataTable SaveDynamicCollection_Deletes(esDataRequest request)
{
VistaDBCommand cmd = null;
DataTable dataTable = CreateDataTable(request);
using (esTransactionScope scope = new esTransactionScope())
{
using (VistaDBDataAdapter da = new VistaDBDataAdapter())
{
da.AcceptChangesDuringUpdate = false;
da.ContinueUpdateOnError = request.ContinueUpdateOnError;
try
{
cmd = da.DeleteCommand = Shared.BuildDynamicDeleteCommand(request, request.CollectionSavePacket[0].ModifiedColumns);
esTransactionScope.Enlist(cmd, request.ConnectionString, CreateIDbConnectionDelegate);
DataRow[] singleRow = new DataRow[1];
// Delete each record
foreach (esEntitySavePacket packet in request.CollectionSavePacket)
{
DataRow row = dataTable.NewRow();
dataTable.Rows.Add(row);
SetOriginalValues(request, packet, row, true);
row.AcceptChanges();
row.Delete();
singleRow[0] = row;
da.Update(singleRow);
if (row.HasErrors)
{
request.FireOnError(packet, row.RowError);
}
dataTable.Rows.Clear(); // ADO.NET won't let us reuse the same DataRow
}
}
finally
{
esTransactionScope.DeEnlist(cmd);
cmd.Dispose();
}
}
scope.Complete();
}
return(request.Table);
}
public static VistaDBCommand PrepareCommand(esDataRequest request)
{
esDynamicQuerySerializable query = request.DynamicQuery;
VistaDBCommand cmd = new VistaDBCommand();
int pindex = NextParamIndex(cmd);
string sql = BuildQuery(request, query, cmd, ref pindex);
cmd.CommandText = sql;
return cmd;
}
public static void AddParameters(VistaDBCommand cmd, tgDataRequest request)
{
if (request.QueryType == tgQueryType.Text && request.QueryText != null && request.QueryText.Contains("{0}"))
{
int i = 0;
string token = String.Empty;
string sIndex = String.Empty;
string param = String.Empty;
foreach (tgParameter esParam in request.Parameters)
{
sIndex = i.ToString();
token = '{' + sIndex + '}';
param = Delimiters.Param + "p" + sIndex;
request.QueryText = request.QueryText.Replace(token, param);
i++;
cmd.Parameters.Add(Delimiters.Param + esParam.Name, esParam.Value);
}
}
else
{
VistaDBParameter param;
foreach (tgParameter esParam in request.Parameters)
{
param = cmd.Parameters.Add(Delimiters.Param + esParam.Name, esParam.Value);
switch (esParam.Direction)
{
case tgParameterDirection.InputOutput:
param.Direction = ParameterDirection.InputOutput;
break;
case tgParameterDirection.Output:
param.Direction = ParameterDirection.Output;
param.DbType = esParam.DbType;
param.Size = esParam.Size;
break;
case tgParameterDirection.ReturnValue:
param.Direction = ParameterDirection.ReturnValue;
break;
// The default is ParameterDirection.Input;
}
}
}
}
/// <summary>
/// Simplify the creation of a VistaDB command object by allowing
/// a CommandType and Command Text to be provided
/// </summary>
/// <remarks>
/// e.g.:
/// VistaDBCommand command = CreateCommand(conn, CommandType.Text, "Select * from Customers");
/// </remarks>
/// <param name="connection">A valid VistaDBConnection object</param>
/// <param name="commandType">CommandType (TableDirect, Text)</param>
/// <param name="commandText">CommandText</param>
/// <returns>A valid VistaDBCommand object</returns>
public static VistaDBCommand CreateCommand(VistaDBConnection connection, CommandType commandType, string commandText )
{
if( connection == null ) throw new ArgumentNullException( "connection" );
if( commandType == CommandType.StoredProcedure ) throw new ArgumentException("Stored Procedures are not supported.");
// If we receive parameter values, we need to figure out where they go
if ((commandText == null) && (commandText.Length<= 0)) throw new ArgumentNullException( "Command Text" );
// Create a VistaDBCommand
VistaDBCommand cmd = new VistaDBCommand(commandText, connection );
cmd.CommandType = CommandType.Text ;
return cmd;
}
protected static string BuildQuery(esDataRequest request, esDynamicQuerySerializable query, VistaDBCommand cmd, ref int pindex)
{
IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal;
string select = GetSelectStatement(request, query, cmd, ref pindex);
string from = GetFromStatement(request, query, cmd, ref pindex);
string join = GetJoinStatement(request, query, cmd, ref pindex);
string where = GetComparisonStatement(request, query, iQuery.InternalWhereItems, " WHERE ", cmd, ref pindex);
string groupBy = GetGroupByStatement(request, query, cmd, ref pindex);
string having = GetComparisonStatement(request, query, iQuery.InternalHavingItems, " HAVING ", cmd, ref pindex);
string orderBy = GetOrderByStatement(request, query, cmd, ref pindex);
string setOperation = GetSetOperationStatement(request, query, cmd, ref pindex);
string sql = String.Empty;
sql += "SELECT " + select + " FROM " + from + join + where + setOperation + groupBy + having + orderBy;
return sql;
}
protected override IDbCommand GetInsertCommand()
{
VistaDBCommand cmd = new VistaDBCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText =
@"INSERT INTO [Employees]
(
[LastName],
[FirstName],
[Title],
[TitleOfCourtesy],
[BirthDate],
[HireDate],
[Address],
[City],
[Region],
[PostalCode],
[Country],
[HomePhone],
[Extension],
[Photo],
[Notes],
[ReportsTo]
)
VALUES
(
@LastName,
@FirstName,
@Title,
@TitleOfCourtesy,
@BirthDate,
@HireDate,
@Address,
@City,
@Region,
@PostalCode,
@Country,
@HomePhone,
@Extension,
@Photo,
@Notes,
@ReportsTo
)";
CreateParameters(cmd);
return cmd;
}
// This is used only to execute the Dynamic Query API
private static void LoadDataTableFromDynamicQuery(tgDataRequest request, tgDataResponse response, VistaDBCommand cmd)
{
try
{
response.LastQuery = cmd.CommandText;
if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;
DataTable dataTable = new DataTable(request.ProviderMetadata.Destination);
VistaDBDataAdapter da = new VistaDBDataAdapter();
da.SelectCommand = cmd;
try
{
tgTransactionScope.Enlist(da.SelectCommand, request.ConnectionString, CreateIDbConnectionDelegate);
#region Profiling
if (sTraceHandler != null)
{
using (esTraceArguments esTrace = new esTraceArguments(request, cmd, "LoadFromDynamicQuery", System.Environment.StackTrace))
{
try
{
da.Fill(dataTable);
}
catch (Exception ex)
{
esTrace.Exception = ex.Message;
throw;
}
}
}
else
#endregion Profiling
{
da.Fill(dataTable);
}
}
finally
{
tgTransactionScope.DeEnlist(da.SelectCommand);
}
response.Table = dataTable;
}
catch (Exception)
{
CleanupCommand(cmd);
throw;
}
finally
{
}
}
private static void CleanupCommand(VistaDBCommand cmd)
{
if (cmd != null && cmd.Connection != null)
{
if (cmd.Connection.State == ConnectionState.Open)
{
cmd.Connection.Close();
}
}
}
private IDbCommand CreateParameters(VistaDBCommand cmd)
{
VistaDBParameter p;
p = cmd.Parameters.Add(Parameters.ID);
p.SourceColumn = ColumnNames.ID;
p.SourceVersion = DataRowVersion.Current;
p = cmd.Parameters.Add(Parameters.DepartmentID);
p.SourceColumn = ColumnNames.DepartmentID;
p.SourceVersion = DataRowVersion.Current;
p = cmd.Parameters.Add(Parameters.FirstName);
p.SourceColumn = ColumnNames.FirstName;
p.SourceVersion = DataRowVersion.Current;
p = cmd.Parameters.Add(Parameters.LastName);
p.SourceColumn = ColumnNames.LastName;
p.SourceVersion = DataRowVersion.Current;
p = cmd.Parameters.Add(Parameters.Age);
p.SourceColumn = ColumnNames.Age;
p.SourceVersion = DataRowVersion.Current;
p = cmd.Parameters.Add(Parameters.HireDate);
p.SourceColumn = ColumnNames.HireDate;
p.SourceVersion = DataRowVersion.Current;
p = cmd.Parameters.Add(Parameters.Salary);
p.SourceColumn = ColumnNames.Salary;
p.SourceVersion = DataRowVersion.Current;
p = cmd.Parameters.Add(Parameters.IsActive);
p.SourceColumn = ColumnNames.IsActive;
p.SourceVersion = DataRowVersion.Current;
return cmd;
}
DataTable IMyMetaPlugin.GetViewColumns(string database, string view)
{
DataTable metaData = new DataTable();
//IVistaDBDatabase db = null;
try
{
metaData = context.CreateColumnsDataTable();
using (VistaDBConnection conn = new VistaDBConnection())
{
conn.ConnectionString = context.ConnectionString;
conn.Open();
string sql = "SELECT * FROM GetViewColumns('" + view + "')";
using (VistaDBCommand cmd = new VistaDBCommand(sql, conn))
{
using (VistaDBDataAdapter da = new VistaDBDataAdapter(cmd))
{
DataTable views = new DataTable();
da.Fill(views);
foreach(DataRow vistaRow in views.Rows)
{
DataRow row = metaData.NewRow();
metaData.Rows.Add(row);
int width = Convert.ToInt32(vistaRow["COLUMN_SIZE"]);
int dec = 0;
int length = 0;
int octLength = width;
bool timestamp = false;
string type = vistaRow["DATA_TYPE_NAME"] as string;
switch(type)
{
case "Char":
case "NChar":
case "NText":
case "NVarchar":
case "Text":
case "Varchar":
length = width;
width = 0;
dec = 0;
break;
case "Currency":
case "Double":
case "Decimal":
case "Single":
break;
case "Timestamp":
timestamp = true;
break;
default:
width = 0;
dec = 0;
break;
}
string def = Convert.ToString(vistaRow["DEFAULT_VALUE"]);
row["TABLE_NAME"] = view;
row["COLUMN_NAME"] = vistaRow["COLUMN_NAME"];
row["ORDINAL_POSITION"] = vistaRow["COLUMN_ORDINAL"];
row["IS_NULLABLE"] = vistaRow["ALLOW_NULL"];
row["COLUMN_HASDEFAULT"] = def == string.Empty ? false : true;
row["COLUMN_DEFAULT"] = def;
row["IS_AUTO_KEY"] = vistaRow["IDENTITY_VALUE"];
row["AUTO_KEY_SEED"] = vistaRow["IDENTITY_SEED"];
row["AUTO_KEY_INCREMENT"] = vistaRow["IDENTITY_STEP"];
row["TYPE_NAME"] = type;
row["NUMERIC_PRECISION"] = width;
row["NUMERIC_SCALE"] = dec;
row["CHARACTER_MAXIMUM_LENGTH"] = length;
row["CHARACTER_OCTET_LENGTH"] = octLength;
row["DESCRIPTION"] = vistaRow["COLUMN_DESCRIPTION"];
if (timestamp)
{
row["IS_COMPUTED"] = true;
}
}
}
}
}
}
catch{}
return metaData;
}
override protected IDbCommand _Load(string conjuction)
{
bool hasColumn = false;
bool selectAll = true;
string query;
query = "SELECT ";
if( this._distinct) query += " DISTINCT ";
if( this._top >= 0) query += " TOP " + this._top.ToString() + " ";
if(this._resultColumns.Length > 0)
{
query += this._resultColumns;
hasColumn = true;
selectAll = false;
}
if(this._countAll)
{
if(hasColumn)
{
query += ", ";
}
query += "COUNT(*)";
if(this._countAllAlias != string.Empty)
{
// Need DBMS string delimiter here
query += " AS [" + this._countAllAlias + "]";
}
hasColumn = true;
selectAll = false;
}
if(_aggregateParameters != null && _aggregateParameters.Count > 0)
{
bool isFirst = true;
if(hasColumn)
{
query += ", ";
}
AggregateParameter wItem;
foreach(object obj in _aggregateParameters)
{
wItem = obj as AggregateParameter;
if(wItem.IsDirty)
{
if(isFirst)
{
query += GetAggregate(wItem, true);
isFirst = false;
}
else
{
query += ", " + GetAggregate(wItem, true);
}
}
}
selectAll = false;
}
if(selectAll)
{
query += "*";
}
query += " FROM [" + this._entity.QuerySource + "]";
VistaDBCommand cmd = new VistaDBCommand();
if(_whereParameters != null && _whereParameters.Count > 0)
{
query += " WHERE ";
bool first = true;
bool requiresParam;
WhereParameter wItem;
bool skipConjuction = false;
string paramName;
string columnName;
foreach(object obj in _whereParameters)
{
// Maybe we injected text or a WhereParameter
if(obj.GetType().ToString() == "System.String")
{
string text = obj as string;
query += text;
if(text == "(")
{
skipConjuction = true;
}
}
else
{
wItem = obj as WhereParameter;
if(wItem.IsDirty)
{
if(!first && !skipConjuction)
{
if(wItem.Conjuction != WhereParameter.Conj.UseDefault)
{
if(wItem.Conjuction == WhereParameter.Conj.And)
query += " AND ";
else
query += " OR ";
}
else
{
query += " " + conjuction + " ";
}
}
requiresParam = true;
columnName = "[" + wItem.Column + "]";
paramName = "@" + wItem.Column + (++inc).ToString();
wItem.Param.ParameterName = paramName;
switch(wItem.Operator)
{
case WhereParameter.Operand.Equal:
query += columnName + " = " + paramName + " ";
break;
case WhereParameter.Operand.NotEqual:
query += columnName + " <> " + paramName + " ";
break;
case WhereParameter.Operand.GreaterThan:
query += columnName + " > " + paramName + " ";
break;
case WhereParameter.Operand.LessThan:
query += columnName + " < " + paramName + " ";
break;
case WhereParameter.Operand.LessThanOrEqual:
query += columnName + " <= " + paramName + " ";
break;
case WhereParameter.Operand.GreaterThanOrEqual:
query += columnName + " >= " + paramName + " ";
break;
case WhereParameter.Operand.Like:
query += columnName + " LIKE " + paramName + " ";
break;
case WhereParameter.Operand.NotLike:
query += columnName + " NOT LIKE " + paramName + " ";
break;
case WhereParameter.Operand.IsNull:
query += columnName + " IS NULL ";
requiresParam = false;
break;
case WhereParameter.Operand.IsNotNull:
query += columnName + " IS NOT NULL ";
requiresParam = false;
break;
case WhereParameter.Operand.In:
query += columnName + " IN (" + wItem.Value + ") ";
requiresParam = false;
break;
case WhereParameter.Operand.NotIn:
query += columnName + " NOT IN (" + wItem.Value + ") ";
requiresParam = false;
break;
case WhereParameter.Operand.Between:
query += columnName + " BETWEEN " + paramName;
cmd.Parameters.Add(paramName, wItem.BetweenBeginValue);
paramName = "@" + wItem.Column + (++inc).ToString();
query += " AND " + paramName;
cmd.Parameters.Add(paramName, wItem.BetweenEndValue);
requiresParam = false;
break;
}
if(requiresParam)
{
IDbCommand dbCmd = cmd as IDbCommand;
dbCmd.Parameters.Add(wItem.Param);
wItem.Param.Value = wItem.Value;
}
first = false;
skipConjuction = false;
}
}
}
}
if(_groupBy.Length > 0)
{
query += " GROUP BY " + _groupBy;
if(this._withRollup)
{
query += " WITH ROLLUP";
}
}
if(_orderBy.Length > 0)
{
query += " ORDER BY " + _orderBy;
}
cmd.CommandText = query;
return cmd;
}
protected override IDbCommand GetUpdateCommand()
{
VistaDBCommand cmd = new VistaDBCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText =
@"UPDATE [AggregateTest] SET
[DepartmentID]=@DepartmentID,
[FirstName]=@FirstName,
[LastName]=@LastName,
[Age]=@Age,
[HireDate]=@HireDate,
[Salary]=@Salary,
[IsActive]=@IsActive
WHERE
[ID]=@ID";
CreateParameters(cmd);
return cmd;
}
private IDbCommand CreateParameters(VistaDBCommand cmd)
{
VistaDBParameter p;
p = cmd.Parameters.Add(Parameters.EmployeeID);
p.SourceColumn = ColumnNames.EmployeeID;
p.SourceVersion = DataRowVersion.Current;
p = cmd.Parameters.Add(Parameters.LastName);
p.SourceColumn = ColumnNames.LastName;
p.SourceVersion = DataRowVersion.Current;
p = cmd.Parameters.Add(Parameters.FirstName);
p.SourceColumn = ColumnNames.FirstName;
p.SourceVersion = DataRowVersion.Current;
p = cmd.Parameters.Add(Parameters.Title);
p.SourceColumn = ColumnNames.Title;
p.SourceVersion = DataRowVersion.Current;
p = cmd.Parameters.Add(Parameters.TitleOfCourtesy);
p.SourceColumn = ColumnNames.TitleOfCourtesy;
p.SourceVersion = DataRowVersion.Current;
p = cmd.Parameters.Add(Parameters.BirthDate);
p.SourceColumn = ColumnNames.BirthDate;
p.SourceVersion = DataRowVersion.Current;
p = cmd.Parameters.Add(Parameters.HireDate);
p.SourceColumn = ColumnNames.HireDate;
p.SourceVersion = DataRowVersion.Current;
p = cmd.Parameters.Add(Parameters.Address);
p.SourceColumn = ColumnNames.Address;
p.SourceVersion = DataRowVersion.Current;
p = cmd.Parameters.Add(Parameters.City);
p.SourceColumn = ColumnNames.City;
p.SourceVersion = DataRowVersion.Current;
p = cmd.Parameters.Add(Parameters.Region);
p.SourceColumn = ColumnNames.Region;
p.SourceVersion = DataRowVersion.Current;
p = cmd.Parameters.Add(Parameters.PostalCode);
p.SourceColumn = ColumnNames.PostalCode;
p.SourceVersion = DataRowVersion.Current;
p = cmd.Parameters.Add(Parameters.Country);
p.SourceColumn = ColumnNames.Country;
p.SourceVersion = DataRowVersion.Current;
p = cmd.Parameters.Add(Parameters.HomePhone);
p.SourceColumn = ColumnNames.HomePhone;
p.SourceVersion = DataRowVersion.Current;
p = cmd.Parameters.Add(Parameters.Extension);
p.SourceColumn = ColumnNames.Extension;
p.SourceVersion = DataRowVersion.Current;
p = cmd.Parameters.Add(Parameters.Photo);
p.SourceColumn = ColumnNames.Photo;
p.SourceVersion = DataRowVersion.Current;
p = cmd.Parameters.Add(Parameters.Notes);
p.SourceColumn = ColumnNames.Notes;
p.SourceVersion = DataRowVersion.Current;
p = cmd.Parameters.Add(Parameters.ReportsTo);
p.SourceColumn = ColumnNames.ReportsTo;
p.SourceVersion = DataRowVersion.Current;
return cmd;
}
tgDataResponse IDataProvider.ExecuteScalar(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;
}
try
{
tgTransactionScope.Enlist(cmd, request.ConnectionString, CreateIDbConnectionDelegate);
#region Profiling
if (sTraceHandler != null)
{
using (esTraceArguments esTrace = new esTraceArguments(request, cmd, "ExecuteScalar", System.Environment.StackTrace))
{
try
{
response.Scalar = cmd.ExecuteScalar();
}
catch (Exception ex)
{
esTrace.Exception = ex.Message;
throw;
}
}
}
else
#endregion Profiling
{
response.Scalar = cmd.ExecuteScalar();
}
}
finally
{
tgTransactionScope.DeEnlist(cmd);
}
if (request.Parameters != null)
{
Shared.GatherReturnParameters(cmd, request, response);
}
}
catch (Exception ex)
{
CleanupCommand(cmd);
response.Exception = ex;
}
return response;
}
protected override IDbCommand GetUpdateCommand()
{
VistaDBCommand cmd = new VistaDBCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText =
@"UPDATE [Employees] SET
[LastName]=@LastName,
[FirstName]=@FirstName,
[Title]=@Title,
[TitleOfCourtesy]=@TitleOfCourtesy,
[BirthDate]=@BirthDate,
[HireDate]=@HireDate,
[Address]=@Address,
[City]=@City,
[Region]=@Region,
[PostalCode]=@PostalCode,
[Country]=@Country,
[HomePhone]=@HomePhone,
[Extension]=@Extension,
[Photo]=@Photo,
[Notes]=@Notes,
[ReportsTo]=@ReportsTo
WHERE
[EmployeeID]=@EmployeeID";
CreateParameters(cmd);
return cmd;
}
protected static string GetOrderByStatement(esDataRequest request, esDynamicQuerySerializable query, VistaDBCommand cmd, ref int pindex)
{
string sql = String.Empty;
string comma = String.Empty;
IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal;
if (iQuery.InternalOrderByItems != null)
{
sql += " ORDER BY ";
foreach (esOrderByItem orderByItem in iQuery.InternalOrderByItems)
{
bool literal = false;
sql += comma;
string columnName = orderByItem.Expression.Column.Name;
if (columnName != null && columnName[0] == '<')
{
sql += columnName.Substring(1, columnName.Length - 2);
if (orderByItem.Direction == esOrderByDirection.Unassigned)
{
literal = true; // They must provide the DESC/ASC in the literal string
}
}
else
{
sql += GetExpressionColumn(orderByItem.Expression, false, false);
}
if (!literal)
{
if (orderByItem.Direction == esOrderByDirection.Ascending)
sql += " ASC";
else
sql += " DESC";
}
comma = ",";
}
}
return sql;
}
protected static string GetSelectStatement(esDataRequest request, esDynamicQuerySerializable query, VistaDBCommand cmd, ref int pindex)
{
string sql = String.Empty;
string comma = String.Empty;
bool selectAll = true;
IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal;
if (query.es.Distinct) sql += " DISTINCT ";
if (query.es.Top >= 0) sql += " TOP " + query.es.Top.ToString() + " ";
if (iQuery.InternalSelectColumns != null)
{
selectAll = false;
foreach (esExpression expressionItem in iQuery.InternalSelectColumns)
{
if (expressionItem.Query != null)
{
IDynamicQuerySerializableInternal iSubQuery = expressionItem.Query as IDynamicQuerySerializableInternal;
sql += comma;
if (iSubQuery.SubQueryAlias == string.Empty)
{
sql += iSubQuery.JoinAlias + ".*";
}
else
{
iSubQuery.IsInSubQuery = true;
sql += " (" + BuildQuery(request, expressionItem.Query as esDynamicQuerySerializable, cmd, ref pindex) + ") AS " + iSubQuery.SubQueryAlias;
iSubQuery.IsInSubQuery = false;
}
comma = ",";
}
else
{
sql += comma;
string columnName = expressionItem.Column.Name;
if (columnName != null && columnName[0] == '<')
sql += columnName.Substring(1, columnName.Length - 2);
else
sql += GetExpressionColumn(expressionItem, false, true);
comma = ",";
}
}
sql += " ";
}
if (query.es.CountAll)
{
selectAll = false;
sql += comma;
sql += "COUNT(*)";
if (query.es.CountAllAlias != null)
{
// Need DBMS string delimiter here
sql += " AS " + Delimiters.StringOpen + query.es.CountAllAlias + Delimiters.StringClose;
}
}
if (selectAll)
{
sql += "*";
}
return sql;
}
protected static string GetComparisonStatement(esDataRequest request, esDynamicQuerySerializable query, List<esComparison> items, string prefix, VistaDBCommand cmd, ref int pindex)
{
string sql = String.Empty;
string comma = String.Empty;
IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal;
//=======================================
// WHERE
//=======================================
if (items != null)
{
sql += prefix;
string compareTo = String.Empty;
foreach (esComparison comparisonItem in items)
{
esComparison.esComparisonData comparisonData = (esComparison.esComparisonData)comparisonItem;
esDynamicQuerySerializable subQuery = null;
bool requiresParam = true;
bool needsStringParameter = false;
if (comparisonData.IsParenthesis)
{
if (comparisonData.Parenthesis == esParenthesis.Open)
sql += "(";
else
sql += ")";
continue;
}
if (comparisonData.IsConjunction)
{
switch (comparisonData.Conjunction)
{
case esConjunction.And: sql += " AND "; break;
case esConjunction.Or: sql += " OR "; break;
case esConjunction.AndNot: sql += " AND NOT "; break;
case esConjunction.OrNot: sql += " OR NOT "; break;
}
continue;
}
Dictionary<string, VistaDBParameter> types = null;
if (comparisonData.Column.Query != null)
{
IDynamicQuerySerializableInternal iLocalQuery = comparisonData.Column.Query as IDynamicQuerySerializableInternal;
types = Cache.GetParameters(iLocalQuery.DataID, (esProviderSpecificMetadata)iLocalQuery.ProviderMetadata, (esColumnMetadataCollection)iLocalQuery.Columns);
}
if (comparisonData.IsLiteral)
{
if (comparisonData.Column.Name[0] == '<')
{
sql += comparisonData.Column.Name.Substring(1, comparisonData.Column.Name.Length - 2);
}
else
{
sql += comparisonData.Column.Name;
}
continue;
}
if (comparisonData.ComparisonColumn.Name == null)
{
subQuery = comparisonData.Value as esDynamicQuerySerializable;
if (subQuery == null)
{
if (comparisonData.Column.Name != null)
{
IDynamicQuerySerializableInternal iColQuery = comparisonData.Column.Query as IDynamicQuerySerializableInternal;
esColumnMetadataCollection columns = (esColumnMetadataCollection)iColQuery.Columns;
compareTo = Delimiters.Param + columns[comparisonData.Column.Name].PropertyName + (++pindex).ToString();
}
else
{
compareTo = Delimiters.Param + "Expr" + (++pindex).ToString();
}
}
else
{
// It's a sub query
compareTo = GetSubquerySearchCondition(subQuery) + " (" + BuildQuery(request, subQuery, cmd, ref pindex) + ") ";
requiresParam = false;
}
}
else
{
compareTo = GetColumnName(comparisonData.ComparisonColumn);
requiresParam = false;
}
switch (comparisonData.Operand)
{
case esComparisonOperand.Exists:
sql += " EXISTS" + compareTo;
break;
case esComparisonOperand.NotExists:
sql += " NOT EXISTS" + compareTo;
break;
//-----------------------------------------------------------
// Comparison operators, left side vs right side
//-----------------------------------------------------------
case esComparisonOperand.Equal:
if (comparisonData.ItemFirst)
sql += ApplyWhereSubOperations(comparisonData) + " = " + compareTo;
else
sql += compareTo + " = " + ApplyWhereSubOperations(comparisonData);
break;
case esComparisonOperand.NotEqual:
if (comparisonData.ItemFirst)
sql += ApplyWhereSubOperations(comparisonData) + " <> " + compareTo;
else
sql += compareTo + " <> " + ApplyWhereSubOperations(comparisonData);
break;
case esComparisonOperand.GreaterThan:
if (comparisonData.ItemFirst)
sql += ApplyWhereSubOperations(comparisonData) + " > " + compareTo;
else
sql += compareTo + " > " + ApplyWhereSubOperations(comparisonData);
break;
case esComparisonOperand.LessThan:
if (comparisonData.ItemFirst)
sql += ApplyWhereSubOperations(comparisonData) + " < " + compareTo;
else
sql += compareTo + " < " + ApplyWhereSubOperations(comparisonData);
break;
case esComparisonOperand.LessThanOrEqual:
if (comparisonData.ItemFirst)
sql += ApplyWhereSubOperations(comparisonData) + " <= " + compareTo;
else
sql += compareTo + " <= " + ApplyWhereSubOperations(comparisonData);
break;
case esComparisonOperand.GreaterThanOrEqual:
if (comparisonData.ItemFirst)
sql += ApplyWhereSubOperations(comparisonData) + " >= " + compareTo;
else
sql += compareTo + " >= " + ApplyWhereSubOperations(comparisonData);
break;
case esComparisonOperand.Like:
string esc = comparisonData.LikeEscape.ToString();
if (String.IsNullOrEmpty(esc) || esc == "\0")
{
sql += ApplyWhereSubOperations(comparisonData) + " LIKE " + compareTo;
needsStringParameter = true;
}
else
{
sql += ApplyWhereSubOperations(comparisonData) + " LIKE " + compareTo;
sql += " ESCAPE '" + esc + "'";
needsStringParameter = true;
}
break;
case esComparisonOperand.NotLike:
esc = comparisonData.LikeEscape.ToString();
if (String.IsNullOrEmpty(esc) || esc == "\0")
{
sql += ApplyWhereSubOperations(comparisonData) + " NOT LIKE " + compareTo;
needsStringParameter = true;
}
else
{
sql += ApplyWhereSubOperations(comparisonData) + " NOT LIKE " + compareTo;
sql += " ESCAPE '" + esc + "'";
needsStringParameter = true;
}
break;
case esComparisonOperand.Contains:
sql += " CONTAINS(" + GetColumnName(comparisonData.Column) +
", " + compareTo + ")";
needsStringParameter = true;
break;
case esComparisonOperand.IsNull:
sql += ApplyWhereSubOperations(comparisonData) + " IS NULL";
requiresParam = false;
break;
case esComparisonOperand.IsNotNull:
sql += ApplyWhereSubOperations(comparisonData) + " IS NOT NULL";
requiresParam = false;
break;
case esComparisonOperand.In:
case esComparisonOperand.NotIn:
{
if (subQuery != null)
{
// They used a subquery for In or Not
sql += ApplyWhereSubOperations(comparisonData);
sql += (comparisonData.Operand == esComparisonOperand.In) ? " IN" : " NOT IN";
sql += compareTo;
}
else
{
comma = String.Empty;
if (comparisonData.Operand == esComparisonOperand.In)
{
sql += ApplyWhereSubOperations(comparisonData) + " IN (";
}
else
{
sql += ApplyWhereSubOperations(comparisonData) + " NOT IN (";
}
foreach (object oin in comparisonData.Values)
{
string str = oin as string;
if (str != null)
{
// STRING
sql += comma + Delimiters.StringOpen + str + Delimiters.StringClose;
comma = ",";
}
else if (null != oin as System.Collections.IEnumerable)
{
// LIST OR COLLECTION OF SOME SORT
System.Collections.IEnumerable enumer = oin as System.Collections.IEnumerable;
if (enumer != null)
{
System.Collections.IEnumerator iter = enumer.GetEnumerator();
while (iter.MoveNext())
{
object o = iter.Current;
string soin = o as string;
if (soin != null)
sql += comma + Delimiters.StringOpen + soin + Delimiters.StringClose;
else
sql += comma + Convert.ToString(o);
comma = ",";
}
}
}
else
{
// NON STRING OR LIST
sql += comma + Convert.ToString(oin);
comma = ",";
}
}
sql += ")";
requiresParam = false;
}
}
break;
case esComparisonOperand.Between:
sql += ApplyWhereSubOperations(comparisonData) + " BETWEEN ";
sql += compareTo;
if (comparisonData.ComparisonColumn.Name == null)
{
cmd.Parameters.Add(compareTo, comparisonData.BetweenBegin);
}
if (comparisonData.ComparisonColumn2.Name == null)
{
IDynamicQuerySerializableInternal iColQuery = comparisonData.Column.Query as IDynamicQuerySerializableInternal;
esColumnMetadataCollection columns = (esColumnMetadataCollection)iColQuery.Columns;
compareTo = Delimiters.Param + columns[comparisonData.Column.Name].PropertyName + (++pindex).ToString();
sql += " AND " + compareTo;
cmd.Parameters.AddWithValue(compareTo, comparisonData.BetweenEnd);
}
else
{
sql += " AND " + Delimiters.ColumnOpen + comparisonData.ComparisonColumn2 + Delimiters.ColumnClose;
}
requiresParam = false;
break;
}
if (requiresParam)
{
VistaDBParameter p;
if (comparisonData.Column.Name != null)
{
p = types[comparisonData.Column.Name];
p = Cache.CloneParameter(p);
p.ParameterName = compareTo;
p.Value = comparisonData.Value;
if (needsStringParameter)
{
p.DbType = DbType.String;
}
}
else
{
p = new VistaDBParameter(compareTo, comparisonData.Value);
}
cmd.Parameters.Add(p);
}
}
}
return sql;
}
protected static string GetJoinStatement(esDataRequest request, esDynamicQuerySerializable query, VistaDBCommand cmd, ref int pindex)
{
string sql = String.Empty;
IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal;
if (iQuery.InternalJoinItems != null)
{
foreach (esJoinItem joinItem in iQuery.InternalJoinItems)
{
esJoinItem.esJoinItemData joinData = (esJoinItem.esJoinItemData)joinItem;
switch (joinData.JoinType)
{
case esJoinType.InnerJoin:
sql += " INNER JOIN ";
break;
case esJoinType.LeftJoin:
sql += " LEFT JOIN ";
break;
case esJoinType.RightJoin:
sql += " RIGHT JOIN ";
break;
case esJoinType.FullJoin:
sql += " FULL JOIN ";
break;
}
IDynamicQuerySerializableInternal iSubQuery = joinData.Query as IDynamicQuerySerializableInternal;
sql += Shared.CreateFullName((esProviderSpecificMetadata)iSubQuery.ProviderMetadata);
sql += " " + iSubQuery.JoinAlias + " ON ";
sql += GetComparisonStatement(request, query, joinData.WhereItems, String.Empty, cmd, ref pindex);
}
}
return sql;
}
protected override IDbCommand GetDeleteCommand()
{
VistaDBCommand cmd = new VistaDBCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText =
@"DELETE FROM [Employees]
WHERE
[EmployeeID]=@EmployeeID";
VistaDBParameter p;
p = cmd.Parameters.Add(Parameters.EmployeeID);
p.SourceColumn = ColumnNames.EmployeeID;
p.SourceVersion = DataRowVersion.Current;
return cmd;
}
protected static string GetGroupByStatement(esDataRequest request, esDynamicQuerySerializable query, VistaDBCommand cmd, ref int pindex)
{
string sql = String.Empty;
string comma = String.Empty;
IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal;
if (iQuery.InternalGroupByItems != null)
{
sql += " GROUP BY ";
foreach (esGroupByItem groupBy in iQuery.InternalGroupByItems)
{
sql += comma;
string columnName = groupBy.Expression.Column.Name;
if (columnName != null && columnName[0] == '<')
sql += columnName.Substring(1, columnName.Length - 2);
else
sql += GetExpressionColumn(groupBy.Expression, false, false);
comma = ",";
}
if (query.es.WithRollup)
{
sql += " WITH ROLLUP";
}
}
return sql;
}
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 { }
}
protected static string GetSetOperationStatement(esDataRequest request, esDynamicQuerySerializable query, VistaDBCommand cmd, ref int pindex)
{
string sql = String.Empty;
IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal;
if (iQuery.InternalSetOperations != null)
{
foreach (esSetOperation setOperation in iQuery.InternalSetOperations)
{
switch (setOperation.SetOperationType)
{
case esSetOperationType.Union: sql += " UNION "; break;
case esSetOperationType.UnionAll: sql += " UNION ALL "; break;
case esSetOperationType.Intersect: sql += " INTERSECT "; break;
case esSetOperationType.Except: sql += " EXCEPT "; break;
}
sql += BuildQuery(request, setOperation.Query, cmd, ref pindex);
}
}
return sql;
}
private static tgDataResponse LoadManyToMany(tgDataRequest request)
{
tgDataResponse response = new tgDataResponse();
VistaDBCommand cmd = null;
try
{
DataTable dataTable = new DataTable(request.ProviderMetadata.Destination);
cmd = new VistaDBCommand();
cmd.CommandType = CommandType.Text;
if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;
string mmQuery = request.QueryText;
string[] sections = mmQuery.Split('|');
string[] tables = sections[0].Split(',');
string[] columns = sections[1].Split(',');
// We build the query, we don't use Delimiters to avoid tons of extra concatentation
string sql = "SELECT * FROM [" + tables[0];
sql += "] JOIN [" + tables[1] + "] ON [" + tables[0] + "].[" + columns[0] + "] = [";
sql += tables[1] + "].[" + columns[1];
sql += "] WHERE [" + tables[1] + "].[" + sections[2] + "] = @";
if (request.Parameters != null)
{
foreach (tgParameter esParam in request.Parameters)
{
sql += esParam.Name;
}
Shared.AddParameters(cmd, request);
}
VistaDBDataAdapter da = new VistaDBDataAdapter();
cmd.CommandText = sql;
da.SelectCommand = cmd;
try
{
tgTransactionScope.Enlist(da.SelectCommand, request.ConnectionString, CreateIDbConnectionDelegate);
#region Profiling
if (sTraceHandler != null)
{
using (esTraceArguments esTrace = new esTraceArguments(request, cmd, "LoadManyToMany", System.Environment.StackTrace))
{
try
{
da.Fill(dataTable);
}
catch (Exception ex)
{
esTrace.Exception = ex.Message;
throw;
}
}
}
else
#endregion Profiling
{
da.Fill(dataTable);
}
}
finally
{
tgTransactionScope.DeEnlist(da.SelectCommand);
}
response.Table = dataTable;
}
catch (Exception)
{
CleanupCommand(cmd);
throw;
}
finally
{
}
return response;
}
private static tgDataResponse LoadDataTableFromStoredProcedure(tgDataRequest request)
{
tgDataResponse response = new tgDataResponse();
VistaDBCommand cmd = null;
try
{
DataTable dataTable = new DataTable(request.ProviderMetadata.Destination);
cmd = new VistaDBCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = Shared.CreateFullName(request);
if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;
if (request.Parameters != null) Shared.AddParameters(cmd, request);
VistaDBDataAdapter da = new VistaDBDataAdapter();
da.SelectCommand = cmd;
try
{
tgTransactionScope.Enlist(da.SelectCommand, request.ConnectionString, CreateIDbConnectionDelegate);
#region Profiling
if (sTraceHandler != null)
{
using (esTraceArguments esTrace = new esTraceArguments(request, cmd, "LoadFromStoredProcedure", System.Environment.StackTrace))
{
try
{
da.Fill(dataTable);
}
catch (Exception ex)
{
esTrace.Exception = ex.Message;
throw;
}
}
}
else
#endregion Profiling
{
da.Fill(dataTable);
}
}
finally
{
tgTransactionScope.DeEnlist(da.SelectCommand);
}
response.Table = dataTable;
if (request.Parameters != null)
{
Shared.GatherReturnParameters(cmd, request, response);
}
}
catch (Exception)
{
CleanupCommand(cmd);
throw;
}
finally
{
}
return response;
}
internal override IDbCommand _LoadFromRawSql(string rawSql, params object[] parameters)
{
int i = 0;
string token = "";
string sIndex = "";
string param = "";
VistaDBCommand cmd = new VistaDBCommand();
foreach(object o in parameters)
{
sIndex = i.ToString();
token = '{' + sIndex + '}';
param = "@p" + sIndex;
rawSql = rawSql.Replace(token, param);
VistaDBParameter p = new VistaDBParameter(param, o);
cmd.Parameters.Add(p);
i++;
}
cmd.CommandText = rawSql;
return cmd;
}
private static int NextParamIndex(VistaDBCommand cmd)
{
return cmd.Parameters.Count;
}
DataTable IMyMetaPlugin.GetViews(string database)
{
DataTable metaData = new DataTable();
//IVistaDBDatabase db = null;
try
{
metaData = context.CreateViewsDataTable();
using (VistaDBConnection conn = new VistaDBConnection())
{
conn.ConnectionString = context.ConnectionString;
conn.Open();
using (VistaDBCommand cmd = new VistaDBCommand("SELECT * FROM GetViews()", conn))
{
using (VistaDBDataAdapter da = new VistaDBDataAdapter(cmd))
{
DataTable views = new DataTable();
da.Fill(views);
foreach(DataRow vistaRow in views.Rows)
{
DataRow row = metaData.NewRow();
metaData.Rows.Add(row);
row["TABLE_NAME"] = vistaRow["VIEW_NAME"];
row["DESCRIPTION"] = vistaRow["DESCRIPTION"];
row["VIEW_TEXT"] = vistaRow["VIEW_DEFINITION"];
row["IS_UPDATABLE"] = vistaRow["IS_UPDATABLE"];
}
}
}
}
}
catch{}
return metaData;
}
protected override IDbCommand GetInsertCommand()
{
VistaDBCommand cmd = new VistaDBCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText =
@"INSERT INTO [AggregateTest]
(
[DepartmentID],
[FirstName],
[LastName],
[Age],
[HireDate],
[Salary],
[IsActive]
)
VALUES
(
@DepartmentID,
@FirstName,
@LastName,
@Age,
@HireDate,
@Salary,
@IsActive
)";
CreateParameters(cmd);
return cmd;
}
