public void RunCountSql()
{
//arrange
var writer = new SqlWriter(_table, _sqlType);
var sql = writer.CountSql();
int count;
//run generated sql
using (var con = _factory.CreateConnection())
{
con.ConnectionString = _connectionString;
using (var cmd = con.CreateCommand())
{
cmd.CommandText = sql;
con.Open();
//this returns an int in SqlServer and MySQL, a long in SQLite and a decimal(!) in Oracle
count = Convert.ToInt32(cmd.ExecuteScalar());
}
}
//assert
Assert.IsTrue(count > 0, "There should be some categories (this test may fail if database table is empty)");
}
public PaginadoGenerico ODataGet(string Sistema, string Assunto, ODataQueryOptions queryOptions)
{
Dictionary<string, object> parametros = new Dictionary<string, object>();
Dictionary<string, string> parametrosNimbus = new Dictionary<string, string>();
string Connection = string.Empty;
string Query = string.Empty;
string QueryCount = string.Empty;
string DbConnection = string.Empty;
string TipQuery = string.Empty;
parametrosNimbus.Add("@Sistema", Sistema);
PaginadoGenerico entidadeNimbus = _repository.ListaPaginada(0, 2, connectionApplication, "Select * from sistema where Nome = @Sistema LIMIT 1", "", parametrosNimbus, dataBaseType, "Text");
foreach (Dictionary<string, object> itemQueryNimbus in entidadeNimbus._Lista)
{
DbConnection = itemQueryNimbus["connectionstring"].ToString();
}
var dbReader = new DatabaseReader(DbConnection, DatabaseSchemaReader.DataSchema.SqlType.SqlServer);
var schema = dbReader.Table(Assunto);
var sqlWrite = new SqlWriter(schema, DatabaseSchemaReader.DataSchema.SqlType.SqlServer);
PaginadoGenerico entidade = null;
if (queryOptions.Skip != null && queryOptions.Top != null)
{
int page = 1;
if (queryOptions.Skip.Value >= queryOptions.Top.Value)
page = (queryOptions.Skip.Value / queryOptions.Top.Value) + 1;
string replaceOver = string.Empty;
string query = sqlWrite.SelectPageSql();
BuildSelect(queryOptions, schema, ref replaceOver, ref query);
BuildOrderBy(queryOptions, schema, ref replaceOver, ref query);
BuildFilter(queryOptions, schema, ref replaceOver, ref query);
entidade = _repository.ListaPaginadaNew(page, queryOptions.Top.Value, DbConnection, query, sqlWrite.CountSql(), parametros, "SQLServer", "Select");
}
else
{
string query = sqlWrite.SelectAllSql();
if (queryOptions.Select != null)
{
foreach (var item in schema.Columns)
{
var linqVerifica = (from queryOption in queryOptions.Select.Properties
where queryOption.ToLower().Equals(item.Name.ToLower())
select queryOption).SingleOrDefault();
if (string.IsNullOrEmpty(linqVerifica))
{
query = query.Replace("[" + item.Name + "],", "");
query = query.Replace("[" + item.Name + "]", "");
}
}
}
entidade = _repository.ListaPaginadaNew(0, 0, DbConnection, sqlWrite.SelectAllSql(), sqlWrite.CountSql(), parametros, "SQLServer", "Select");
}
return entidade;
}