public void RunPagingSql() { //arrange var writer = new SqlWriter(_table, _sqlType); var sql = writer.SelectPageSql(); var dataTable = new DataTable(); Console.WriteLine(sql); //run generated sql using (var con = _factory.CreateConnection()) { con.ConnectionString = _connectionString; using (var cmd = con.CreateCommand()) { cmd.CommandText = sql; var pageSize = cmd.CreateParameter(); pageSize.ParameterName = writer.ParameterName("pageSize"); pageSize.Value = 4; cmd.Parameters.Add(pageSize); var currentPage = cmd.CreateParameter(); currentPage.ParameterName = writer.ParameterName("currentPage"); currentPage.Value = 1; cmd.Parameters.Add(currentPage); var da = _factory.CreateDataAdapter(); da.SelectCommand = cmd; da.Fill(dataTable); } } //assert Assert.IsTrue(dataTable.Rows.Count > 0, "There should be some categories (this test may fail if database table is empty)"); Assert.IsTrue(dataTable.Rows.Count <= 4, "Should only return the page size (or less)"); foreach (var column in _table.Columns) { var name = column.Name; Assert.IsTrue(dataTable.Columns.Contains(name), "Should retrieve column " + name); } }
public void ReadNorthwindProductsWithCodeGen() { var dbReader = TestHelper.GetNorthwindReader(); dbReader.DataTypes(); //load the datatypes var table = dbReader.Table("Products"); Debug.WriteLine("Table " + table.Name); foreach (var column in table.Columns) { //Cs properties (the column name could be made .Net friendly too) Debug.WriteLine("\tpublic " + column.DataType.NetCodeName(column) + " " + column.Name + " { get; set; }"); } // public int ProductID { get; set; } // public string ProductName { get; set; } // public int SupplierID { get; set; } // public int CategoryID { get; set; } // public string QuantityPerUnit { get; set; } // public decimal UnitPrice { get; set; } // public short UnitsInStock { get; set; } // public short UnitsOnOrder { get; set; } // public short ReorderLevel { get; set; } // public bool Discontinued { get; set; } //get the sql var sqlWriter = new SqlWriter(table, DatabaseSchemaReader.DataSchema.SqlType.SqlServer); var sql = sqlWriter.SelectPageSql(); //paging sql sql = SqlWriter.SimpleFormat(sql); //remove line breaks Debug.WriteLine(sql); //SELECT [ProductID], [ProductName], ...etc... //FROM //(SELECT ROW_NUMBER() OVER( ORDER BY [ProductID]) AS //rowNumber, [ProductID], [ProductName], ...etc.. //FROM [Products]) AS countedTable //WHERE rowNumber >= (@pageSize * (@currentPage - 1)) //AND rowNumber <= (@pageSize * @currentPage) }
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 TestGeneratedSqlForPaging() { var table = LoadCategoriesFromNorthwind(); //arrange var writer = new SqlWriter(table, SqlType.SqlServerCe); var sql = writer.SelectPageSql(); //sane as writer.SelectPageStartToEndRowSql() //parameters are offset and pageSize, not the standard ones (limitations of OFFSET/FETCH in sqlserverCe) var dataTable = new DataTable(); //run generated sql using (var con = _factory.CreateConnection()) { con.ConnectionString = _connectionString; using (var cmd = con.CreateCommand()) { cmd.CommandText = sql; var pageSize = cmd.CreateParameter(); pageSize.ParameterName = writer.ParameterName("pageSize"); pageSize.Value = 2; cmd.Parameters.Add(pageSize); var currentPage = cmd.CreateParameter(); currentPage.ParameterName = writer.ParameterName("offset"); currentPage.Value = 2; cmd.Parameters.Add(currentPage); var da = _factory.CreateDataAdapter(); da.SelectCommand = cmd; da.Fill(dataTable); } } //assert Assert.IsTrue(dataTable.Rows.Count > 0, "There should be some categories (this test may fail if database table is empty)"); Assert.IsTrue(dataTable.Rows.Count <= 4, "Should only return the page size (or less)"); foreach (var column in table.Columns) { var name = column.Name; Assert.IsTrue(dataTable.Columns.Contains(name), "Should retrieve column " + name); } }
public void BuildTableSelectPaged(DatabaseTable databaseTable) { var sqlWriter = new SqlWriter(databaseTable, _sqlType); try { var txt = sqlWriter.SelectPageSql(); Clipboard.SetText(txt, TextDataFormat.UnicodeText); } catch (Exception exception) { Debug.WriteLine(exception.Message); } }