public void TestSqlServerAliases()
{
CustomerTableMetadata c = new CustomerTableMetadata();
QueryCriteria qc = new QueryCriteria(c);
qc.Add(CriteriaOperator.Equality, c.TableFields[0], 47);
qc.Add(CriteriaOperator.Or, c.TableFields[1]);
qc.Add(CriteriaOperator.Equality, c.TableFields[0], 48);
qc.Add(CriteriaOperator.Between, c.TableFields[0], 48, 70);
qc.Add(CriteriaOperator.Like, c.TableFields[1], "blah");
qc.Add(CriteriaOperator.Not, c.TableFields[0], 48);
qc.Add(CriteriaOperator.IsNull, c.TableFields[0]);
qc.Add(CriteriaOperator.OrderBy, c.TableFields[1], "asc");
qc.Add(CriteriaOperator.OrderBy, c.TableFields[0], "desc");
//aliases
qc.AddAlias("Age", "AAAAAAAg");
qc.AddAlias("Name", "Numele_bahhhhhhhhhhhhhh");
IQueryCriteriaGenerator ss = DataFactory.InitializeQueryCriteriaGenerator(DatabaseServer.SqlServer);
ExecutionQuery s = ss.GenerateSelect(qc);
Console.WriteLine(s.Query);
DataSet ds = psqlserver.GetDataSet(qc);
Assert.IsTrue(ds != null);
}
/// <summary>
/// Returns a dataset which contains data specified in the criteria query
/// </summary>
/// <param name="criteria">QueryCriteria based upon which the data is selected</param>
/// <returns>DataSet containing the selected data</returns>
public DataSet GetDataSet(QueryCriteria criteria)
{
IQueryCriteriaGenerator iql = null;
DataFactory factory = new DataFactory();
iql = factory.InitializeQueryCriteriaGenerator(database);
DataSet dsTemp = null;
ExecutionQuery selectQuery = iql.GenerateSelect(criteria);
//check for session
if (contextSession != null)
{
dsTemp = execEngine.ExecuteDataSet(selectQuery);
}
else
{
using (ExecutionEngine e = new ExecutionEngine())
{
dsTemp = e.ExecuteDataSet(database, connectionString, selectQuery);
}
}
return(dsTemp);
}
/// <summary>
/// Gets a DataTable by running the specified criteria
/// </summary>
/// <param name="criteria">Specified criteria</param>
/// <returns>Resulting DataTable</returns>
public DataTable GetDataTable(QueryCriteria criteria)
{
IQueryCriteriaGenerator iql = null;
DataFactory factory = new DataFactory();
iql = factory.InitializeQueryCriteriaGenerator(database);
DataTable table = null;
ExecutionQuery selectQuery = iql.GenerateSelect(criteria);
//check for session
if (contextSession != null)
{
table = execEngine.ExecuteDataTable(selectQuery);
}
else
{
using (ExecutionEngine e = new ExecutionEngine())
{
//no session
table = e.ExecuteDataTable(selectQuery);
}
}
return(table);
}
/// <summary>
/// Returns a ArraList with the data from the specified field using the specified QueryCriteria
/// </summary>
/// <param name="criteria">QueryCriteria based upon which data is selected</param>
/// <returns>ArrayList which contains the selected result</returns>
public virtual ArrayList GetFieldList(QueryCriteria criteria)
{
IQueryCriteriaGenerator iql = null;
IDataReader iread = null;
ArrayList scData = null;
ExecutionEngine exec = null;
try
{
if (criteria.Fields.Length > 1)
{
throw new ArgumentException("Invalid field length. Must have only one field ");
}
scData = new ArrayList();
DataFactory factory = new DataFactory();
iql = factory.InitializeQueryCriteriaGenerator(database);
ExecutionQuery selectQuery = iql.GenerateSelect(criteria);
if (contextSession != null)
{
iread = execEngine.ExecuteReader(selectQuery);
}
else
{
exec = new ExecutionEngine(database, connectionString);
iread = exec.ExecuteReader(selectQuery);
}
while (iread.Read())
{
scData.Add(iread.GetValue(0));
}
iread.Close();
return(scData);
}
finally
{
if (iread != null)
{
iread.Close();
}
if (exec != null)
{
exec.Dispose();
}
}
}
public void TestPostgreSqlMax()
{
QueryCriteria qc = new QueryCriteria(first.TableName, first.TableFields[0]);
qc.Add(CriteriaOperator.Max, first.TableFields[0]);
IQueryCriteriaGenerator iq = DataFactory.InitializeQueryCriteriaGenerator(DatabaseServer.PostgreSql);
ExecutionQuery s = iq.GenerateSelect(qc);
Console.WriteLine(s.Query);
Assert.IsTrue(s.Query != null);
}
public void TestPostgreSqlEquality()
{
CustomerTableMetadata c = new CustomerTableMetadata();
QueryCriteria qc = new QueryCriteria(c);
qc.Add(CriteriaOperator.Equality, c.TableFields[0], 1);
IQueryCriteriaGenerator ss = DataFactory.InitializeQueryCriteriaGenerator(DatabaseServer.PostgreSql);
ExecutionQuery s = ss.GenerateSelect(qc);
Console.WriteLine(s.Query);
Assert.IsTrue(s.Query != string.Empty);
}
public void TestMySQLSmallerOrEqual()
{
CustomerTableMetadata c = new CustomerTableMetadata();
QueryCriteria qc = new QueryCriteria(c);
qc.Add(CriteriaOperator.SmallerOrEqual, c.TableFields[0], 47);
IQueryCriteriaGenerator ss = DataFactory.InitializeQueryCriteriaGenerator(DatabaseServer.MySQL);
ExecutionQuery s = ss.GenerateSelect(qc);
Console.WriteLine(s.Query);
Assert.IsTrue(s.Query != string.Empty);
}
public void TestAccessSmaller()
{
CustomerTableMetadata c = new CustomerTableMetadata();
QueryCriteria qc = new QueryCriteria(c);
qc.Add(CriteriaOperator.Smaller, c.TableFields[0], 47);
IQueryCriteriaGenerator ss = DataFactory.InitializeQueryCriteriaGenerator(DatabaseServer.Access);
ExecutionQuery s = ss.GenerateSelect(qc);
Console.WriteLine(s.Query);
DataSet ds = pAccess.GetDataSet(qc);
Assert.IsTrue(ds != null);
}
/// <summary>
/// Get data as a TableMetadata array
/// </summary>
/// <param name="criteria">QueryCriteria based upon which data is selected</param>
/// <returns>A TableMetadata array containing all the data</returns>
public Array GetTableMetadata(QueryCriteria criteria)
{
if (criteria.TableName != mappedObject.TableName)
{
throw new ArgumentException("Invalid criteria query. Must be the same as current table metadata");
}
DataFactory factory = new DataFactory();
IQueryCriteriaGenerator iql = factory.InitializeQueryCriteriaGenerator(database);
ExecutionQuery selectQuery = iql.GenerateSelect(criteria);
return(GetTableMetadata(selectQuery));
}
public void TestSqlServerOrderBy()
{
CustomerTableMetadata c = new CustomerTableMetadata();
QueryCriteria qc = new QueryCriteria(c);
qc.Add(CriteriaOperator.OrderBy, c.TableFields[0], "asc");
qc.Add(CriteriaOperator.OrderBy, c.TableFields[1], "desc");
IQueryCriteriaGenerator ss = DataFactory.InitializeQueryCriteriaGenerator(DatabaseServer.SqlServer);
ExecutionQuery s = ss.GenerateSelect(qc);
Console.WriteLine(s.Query);
DataSet ds = psqlserver.GetDataSet(qc);
Assert.IsTrue(ds != null);
}
/// <summary>
/// Returns a single value from the database using the specified QueryCriteria
/// </summary>
/// <param name="criteria">QueryCriteria based upon which data is selected</param>
/// <returns>The selected fieldValue</returns>
public object GetValue(QueryCriteria criteria)
{
IQueryCriteriaGenerator iql = null;
object result = null;
ExecutionEngine exec = null;
try
{
if (criteria.Fields.Length > 1)
{
throw new ArgumentException("Invalid fields length. Must have only one field");
}
DataFactory factory = new DataFactory();
iql = factory.InitializeQueryCriteriaGenerator(database);
ExecutionQuery selectQuery = iql.GenerateSelect(criteria);
if (contextSession != null)
{
result = execEngine.ExecuteScalar(selectQuery);
}
else
{
exec = new ExecutionEngine();
result = exec.ExecuteScalar(selectQuery);
}
return(result);
}
finally
{
if (exec != null)
{
exec.Dispose();
}
}
}
public void TestMySQLMultiple()
{
CustomerTableMetadata c = new CustomerTableMetadata();
QueryCriteria qc = new QueryCriteria(c);
qc.Add(CriteriaOperator.Equality, c.TableFields[0], 47);
qc.Add(CriteriaOperator.Or, c.TableFields[1]);
qc.Add(CriteriaOperator.Equality, c.TableFields[0], 48);
qc.Add(CriteriaOperator.Between, c.TableFields[0], 48, 70);
qc.Add(CriteriaOperator.Like, c.TableFields[1], "blah");
qc.Add(CriteriaOperator.Not, c.TableFields[0], 48);
qc.Add(CriteriaOperator.IsNull, c.TableFields[0]);
qc.Add(CriteriaOperator.OrderBy, c.TableFields[1], "asc");
qc.Add(CriteriaOperator.OrderBy, c.TableFields[0], "desc");
IQueryCriteriaGenerator ss = DataFactory.InitializeQueryCriteriaGenerator(DatabaseServer.MySQL);
ExecutionQuery s = ss.GenerateSelect(qc);
Console.WriteLine(s.Query);
Assert.IsTrue(s.Query != string.Empty);
}
private object RunIntrinsecFunction(CriteriaOperator criteria, DatabaseField field)
{
IQueryCriteriaGenerator iql = null;
object result;
ExecutionEngine exec = null;
try
{
QueryCriteria qc = new QueryCriteria(mappedObject.TableName, field);
qc.Add(criteria, field);
DataFactory factory = new DataFactory();
iql = factory.InitializeQueryCriteriaGenerator(database);
ExecutionQuery query = iql.GenerateSelect(qc);
if (contextSession != null)
{
result = execEngine.ExecuteScalar(query);
}
else
{
exec = new ExecutionEngine();
result = exec.ExecuteScalar(database, connectionString, query);
}
return(result);
}
finally
{
if (exec != null)
{
exec.Dispose();
}
}
}