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