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