public void RunSelectAllSql() { //arrange var writer = new SqlWriter(_table, _sqlType); var sql = writer.SelectAllSql(); var dataTable = new DataTable(); //run generated sql using (var con = _factory.CreateConnection()) { con.ConnectionString = _connectionString; using (var cmd = con.CreateCommand()) { cmd.CommandText = sql; var da = _factory.CreateDataAdapter(); da.SelectCommand = cmd; da.Fill(dataTable); } } //assert Assert.IsTrue(dataTable.Rows.Count > 0, "There should be some data rows (this test may fail if database table is empty)"); foreach (var column in _table.Columns) { var name = column.Name; Assert.IsTrue(dataTable.Columns.Contains(name), "Should retrieve column " + name); } }
public void TestGeneratedSqlForSelectAll() { //arrange var schema = new DatabaseSchema(null, SqlType.SqlServer); schema.AddTable("Category").AddColumn<int>("Id").AddPrimaryKey() .AddColumn<string>("FirstName").AddLength(10) .Table.SchemaOwner = "first"; schema.AddTable("Category").AddColumn<int>("Id").AddPrimaryKey() .AddColumn<string>("SecondName").AddLength(20) .Table.SchemaOwner = "second"; var table = schema.FindTableByName("Category"); //this will find one of them var writer = new SqlWriter(table, SqlType.SqlServer); //act var sql = writer.SelectAllSql(); //assert }
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; }
public void BuildTableSelect(DatabaseTable databaseTable) { var sqlWriter = new SqlWriter(databaseTable, _sqlType); try { var txt = sqlWriter.SelectAllSql(); Clipboard.SetText(txt, TextDataFormat.UnicodeText); } catch (Exception exception) { Debug.WriteLine(exception.Message); } }