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 = 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);
}
//[TestMethod]
public void TestGeneratedSqlForInsert()
{
//arrange
ProviderChecker.Check(ProviderName, ConnectionString);
var dbReader = new DatabaseReader(ConnectionString, ProviderName, 0);
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 = 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;
foreach (var column in table.Columns)
{
if (column.IsIdentity)
{
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 = 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.IsIdentity)
{
//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 = 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 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.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.IsIdentity)
{
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);
}