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; }