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 TestGeneratedSqlForInsert() { //arrange var table = LoadCategoriesFromNorthwind(); var writer = new SqlWriter(table, SqlType.SqlServerCe); //can't insert identity, and no output parameters var sql = writer.InsertSqlWithoutOutputParameter(); Console.WriteLine(sql); int identity; //run generated sql using (var con = _factory.CreateConnection()) { con.ConnectionString = _connectionString; con.Open(); using (var transaction = con.BeginTransaction()) { using (var cmd = con.CreateCommand()) { cmd.CommandText = sql; cmd.Transaction = transaction; foreach (var column in table.Columns) { if (column.IsAutoNumber) continue; var par = cmd.CreateParameter(); par.ParameterName = writer.ParameterName(column.Name); object value = DummyDataCreator.CreateData(column); par.Value = value ?? DBNull.Value; cmd.Parameters.Add(par); } cmd.ExecuteNonQuery(); } using (var cmd = con.CreateCommand()) { //can't use SCOPE_IDENTITY in SqlServerCE cmd.CommandText = "SELECT @@IDENTITY;"; cmd.Transaction = transaction; //comes back as decimal, but we know it's always an int identity = Convert.ToInt32(cmd.ExecuteScalar()); } //explicit rollback. If we errored, implicit rollback. transaction.Rollback(); } } //assert Assert.AreNotEqual(0, identity); }
public void TestGeneratedSqlForInsert() { //arrange var table = LoadCategoriesFromNorthwind(); var writer = new SqlWriter(table, SqlType.SqlServer); var sql = writer.InsertSql(); int identity; //run generated sql using (var con = _factory.CreateConnection()) { con.ConnectionString = _connectionString; con.Open(); using (var transaction = con.BeginTransaction()) { using (var cmd = con.CreateCommand()) { cmd.CommandText = sql; cmd.Transaction = transaction; string identityParameterName = "Id"; foreach (var column in table.Columns) { var par = cmd.CreateParameter(); par.ParameterName = writer.ParameterName(column.Name); if (column.IsAutoNumber) { //get the name of the identity parameter identityParameterName = par.ParameterName; par.Direction = ParameterDirection.Output; par.DbType = DbType.Int32; } else { object value = DummyDataCreator.CreateData(column); par.Value = value ?? DBNull.Value; } cmd.Parameters.Add(par); } cmd.ExecuteNonQuery(); identity = (int)cmd.Parameters[identityParameterName].Value; } //explicit rollback. If we errored, implicit rollback. transaction.Rollback(); } } //assert Assert.AreNotEqual(0, identity); }
public void TestGeneratedSqlForInsert() { //arrange var table = LoadTable(); var writer = new SqlWriter(table, SqlType.PostgreSql); var sql = writer.InsertSqlWithoutOutputParameter(); Console.WriteLine(sql); //int identity; //run generated sql using (var con = _factory.CreateConnection()) { con.ConnectionString = _connectionString; con.Open(); using (var transaction = con.BeginTransaction()) { using (var cmd = con.CreateCommand()) { cmd.CommandText = sql; cmd.Transaction = transaction; //need to set cmd.UnpreparedExecute =true as Protocol 3 doesn't support multiple commands var unpreparedExecute = cmd.GetType().GetProperty("UnpreparedExecute"); if (unpreparedExecute != null) unpreparedExecute.SetValue(cmd, true, null); foreach (var column in table.Columns) { if (column.IsAutoNumber) continue; var par = cmd.CreateParameter(); par.ParameterName = writer.ParameterName(column.Name); object value = DummyDataCreator.CreateData(column); if (column.Name == "id") value = 9999; //hardcoded for city par.Value = value ?? DBNull.Value; cmd.Parameters.Add(par); } cmd.ExecuteNonQuery(); //identity = Convert.ToInt32(cmd.ExecuteScalar()); } //explicit rollback. If we errored, implicit rollback. transaction.Rollback(); } } //assert //Assert.AreNotEqual(0, identity); }
//[TestMethod] public void TestGeneratedSqlForInsert() { //arrange ProviderChecker.Check(ProviderName, ConnectionString); var dbReader = new DatabaseReader(ConnectionString, ProviderName); dbReader.DataTypes(); //ensure we have datatypes (this doesn't hit the database) var table = dbReader.Table("TABWITHIDENTITY"); //this hits database for columns and constraints var writer = new SqlWriter(table, SqlType.Db2); var sql = writer.InsertSqlWithoutOutputParameter(); Console.WriteLine(sql); int identity; //run generated sql using (var con = _factory.CreateConnection()) { con.ConnectionString = ConnectionString; con.Open(); using (var transaction = con.BeginTransaction()) { using (var cmd = con.CreateCommand()) { cmd.CommandText = sql; cmd.Transaction = transaction; foreach (var column in table.Columns) { if (column.IsAutoNumber) continue; var par = cmd.CreateParameter(); par.ParameterName = writer.ParameterName(column.Name); object value = DummyDataCreator.CreateData(column); par.Value = value ?? DBNull.Value; cmd.Parameters.Add(par); } identity = Convert.ToInt32(cmd.ExecuteScalar()); } //explicit rollback. If we errored, implicit rollback. transaction.Rollback(); } } //assert Assert.AreNotEqual(0, identity); }
public void TestGeneratedSqlForInsert() { //arrange var table = LoadRegionsFromHr(); var writer = new SqlWriter(table, SqlType.Oracle); var sql = writer.InsertSql(); //run generated sql using (var con = _factory.CreateConnection()) { con.ConnectionString = ConnectionString; con.Open(); using (var transaction = con.BeginTransaction()) { using (var cmd = con.CreateCommand()) { cmd.CommandText = sql; cmd.Transaction = transaction; foreach (var column in table.Columns) { var par = cmd.CreateParameter(); par.ParameterName = writer.ParameterName(column.Name); if (column.IsAutoNumber) { //we could be using sequences here par.Direction = ParameterDirection.Output; par.DbType = DbType.Int32; } else { object value = DummyDataCreator.CreateData(column); par.Value = value ?? DBNull.Value; } cmd.Parameters.Add(par); } cmd.ExecuteNonQuery(); } //explicit rollback. If we errored, implicit rollback. transaction.Rollback(); } } //assert }
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 void TestGeneratedSqlForInsert() { //arrange var table = LoadCountryFromSakila(); var writer = new SqlWriter(table, SqlType.MySql); //MySQL can only use output parameters with sprocs. var sql = writer.InsertSqlWithoutOutputParameter(); Console.WriteLine(sql); int identity; //run generated sql using (var con = _factory.CreateConnection()) { con.ConnectionString = _connectionString; con.Open(); using (var transaction = con.BeginTransaction()) { using (var cmd = con.CreateCommand()) { cmd.CommandText = sql; cmd.Transaction = transaction; foreach (var column in table.Columns) { if (column.IsAutoNumber) continue; var par = cmd.CreateParameter(); par.ParameterName = writer.ParameterName(column.Name); object value = DummyDataCreator.CreateData(column); par.Value = value ?? DBNull.Value; cmd.Parameters.Add(par); } identity = Convert.ToInt32(cmd.ExecuteScalar()); //if using a sproc //identity = (int)cmd.Parameters[identityParameterName].Value; } //explicit rollback. If we errored, implicit rollback. transaction.Rollback(); } } //assert Assert.AreNotEqual(0, identity); }
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 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 void DeleteAllData(DatabaseSchema databaseSchema) { try { var orderedTables = SchemaTablesSorter.TopologicalSort(databaseSchema).Reverse(); var sb = new StringBuilder(); sb.AppendLine("-- delete data from all tables in safe order"); foreach (var databaseTable in orderedTables) { if (databaseTable.ForeignKeyChildren.Contains(databaseTable)) { sb.AppendLine("-- WARNING: " + databaseTable.Name + " has SELF-JOIN"); } var sqlWriter = new SqlWriter(databaseTable, _sqlType); sb.AppendLine("DELETE FROM " + sqlWriter.EscapedTableName); } Clipboard.SetText(sb.ToString(), TextDataFormat.UnicodeText); } catch (Exception exception) { Debug.WriteLine(exception.Message); } }
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 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 void BuildTableInsert(DatabaseTable databaseTable) { var sqlWriter = new SqlWriter(databaseTable, _sqlType); try { var txt = sqlWriter.InsertSqlWithoutOutputParameter(); Clipboard.SetText(txt, TextDataFormat.UnicodeText); } catch (Exception exception) { Debug.WriteLine(exception.Message); } }
public void BuildTableUpdate(DatabaseTable databaseTable) { var sqlWriter = new SqlWriter(databaseTable, _sqlType); try { var txt = sqlWriter.UpdateSql(); Clipboard.SetText(txt, TextDataFormat.UnicodeText); } catch (Exception exception) { Debug.WriteLine(exception.Message); } }
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 TestGeneratedSqlForInsert() { //arrange var table = LoadCategoriesFromNorthwind(); var writer = new SqlWriter(table, SqlType.SQLite); var sql = writer.InsertSql(); int identity; //run generated sql using (var con = _factory.CreateConnection()) { con.ConnectionString = _connectionString; con.Open(); using (var transaction = con.BeginTransaction()) { using (var cmd = con.CreateCommand()) { cmd.CommandText = sql; cmd.Transaction = transaction; foreach (var column in table.Columns) { if (column.IsAutoNumber) continue; var par = cmd.CreateParameter(); par.ParameterName = writer.ParameterName(column.Name); object value = DummyDataCreator.CreateData(column); par.Value = value ?? DBNull.Value; cmd.Parameters.Add(par); } identity = Convert.ToInt32(cmd.ExecuteScalar()); } //explicit rollback. If we errored, implicit rollback. transaction.Rollback(); } } //assert Assert.AreNotEqual(0, identity); }