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 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 = 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 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 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); }
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); }
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); }
//[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); }