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>
/// Creates a new IQueryCriteriaGenerator
/// </summary>
/// <param name="database">Database server type</param>
/// <returns>Returns a new IQueryCriteriaGenerator</returns>
public IQueryCriteriaGenerator InitializeQueryCriteriaGenerator(DatabaseServer database)
{
IQueryCriteriaGenerator iql = null;
switch (database)
{
case DatabaseServer.Access:
iql = new AccessQueryCriteriaGenerator();
break;
case DatabaseServer.MySQL:
iql = new MySqlQueryCriteriaGenerator();
break;
case DatabaseServer.SqlServer:
iql = new SqlServerQueryCriteriaGenerator();
break;
case DatabaseServer.PostgreSql:
iql = new PostgreSqlQueryCriteriaGenerator();
break;
case DatabaseServer.Oracle:
iql = new OracleQueryCriteriaGenerator();
break;
default:
throw new ArgumentException("Invalid database type");
}
return(iql);
}
/// <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);
}
/// <summary>
/// Deletes the row by the specified primary key value.
/// </summary>
/// <param name="primaryKeyValue">The primary key value.</param>
/// <returns>Number of affected rows</returns>
public int Delete(object primaryKeyValue)
{
IQueryCriteriaGenerator queryCriteriaGenerator = null;
DataFactory factory = new DataFactory();
int affectedRows = 0;
ExecutionEngine exec = null;
try
{
queryCriteriaGenerator = factory.InitializeQueryCriteriaGenerator(database);
DatabaseField field = mappedObject.GetPrimaryKeyField();
field.fieldValue = primaryKeyValue;
QueryCriteria criteria = new QueryCriteria(mappedObject.TableName);
criteria.Add(CriteriaOperator.Equality, field, primaryKeyValue);
ExecutionQuery query = queryCriteriaGenerator.GenerateDelete(criteria);
if (contextSession != null)
{
if (contextSession.IsInTransaction)
{
contextSession.Queries.Add(query);
}
else
{
affectedRows = execEngine.ExecuteNonQuery(query);
}
}
else
{
exec = new ExecutionEngine();
affectedRows = exec.ExecuteNonQuery(query);
}
return(affectedRows);
}
finally
{
if (exec != null)
{
exec.Dispose();
}
}
}
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 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);
}
/// <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 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);
}
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);
}
private void Form1_Load(object sender, EventArgs e)
{
try
{
CustomerTableMetadata c = new CustomerTableMetadata();
QueryCriteria qc = new QueryCriteria(c);
qc.Add(CriteriaOperator.Distinct, c.TableFields[0]);
IQueryCriteriaGenerator ss = DataFactory.InitializeQueryCriteriaGenerator(EDatabase.SqlServer);
// CategoriesTableMetadata aom = new CategoriesTableMetadata();
// CategoriesPersistentObject ctg = new CategoriesPersistentObject(EDatabase.SqlServer, Program.sqlserver, aom);
// ExtenderTerritories.TerritoriesTableMetadata etp = new ExtenderTerritories.TerritoriesTableMetadata();
// CategoriesTableMetadata[] ct = (CategoriesTableMetadata[])ctg.GetTableMetadata();
// CategoriesTableMetadata c = (CategoriesTableMetadata)ctg.GetTableMetadata(2);
//
// CategoriesTableMetadata[] cc = new CategoriesTableMetadata[1];
// CategoriesTableMetadata[] ctm = (CategoriesTableMetadata[])ctg.GetTableMetadata();
// cc[0] = c;
// this.dataGridView1.DataSource = cc;
// EmployeesTableMetadata eom = new EmployeesTableMetadata();
// EmployeesPersistentObject emp = new EmployeesPersistentObject(EDatabase.SqlServer, Program.sqlserver, eom);
// EmployeesTableMetadata[] ex = (EmployeesTableMetadata[]) emp.GetTableMetadata();
//
// EmployeeTerritoriesTableMetadata[] ao = (EmployeeTerritoriesTableMetadata[]) emp.GetTableMetadata("FK", "ExtenderEmployees.EmployeeTerritoriesTableMetadata", 1);
//
// this.dataGridView1.DataSource = ao;
}
catch (Exception)
{
throw;
}
finally
{
}
}
/// <summary>
/// Delete multiple rows from the table using the specified criteria from a QueryCriteria
/// </summary>
/// <param name="criteria">QueryCriteria based on which data is deleted</param>
public int Delete(QueryCriteria criteria)
{
int affectedRows = 0;
DataFactory factory = new DataFactory();
ExecutionEngine exec = null;
try
{
IQueryCriteriaGenerator iql = factory.InitializeQueryCriteriaGenerator(database);
ExecutionQuery query = iql.GenerateDelete(criteria);
if (contextSession != null)
{
if (contextSession.IsInTransaction)
{
contextSession.Queries.Add(query);
}
else
{
affectedRows = execEngine.ExecuteNonQuery(query);
}
}
else
{
exec = new ExecutionEngine();
affectedRows = exec.ExecuteNonQuery(query);
}
return(affectedRows);
}
finally
{
if (exec != null)
{
exec.Dispose();
}
}
}
/// <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();
}
}
}
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();
}
}
}
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);
}
/// <summary>
/// Updates the specified entities using the specified QueryCriteria
/// </summary>
/// <param name="criteria">QueryCriteria based upon which data is updated</param>
/// <returns>Number of affected rows</returns>
public int Update(QueryCriteria criteria)
{
DataFactory factory = new DataFactory();
ExecutionEngine exec = null;
try
{
IQueryCriteriaGenerator iql = factory.InitializeQueryCriteriaGenerator(database);
ExecutionQuery query = iql.GenerateUpdate(criteria);
if (contextSession != null)
{
if (contextSession.IsInTransaction)
{
contextSession.Queries.Add(query);
return(0);
}
return(execEngine.ExecuteNonQuery(query));
}
exec = new ExecutionEngine();
return(exec.ExecuteNonQuery(query));
}
finally
{
if (exec != null)
{
exec.Dispose();
}
}
}
