private static DatabaseReader GetNortwindReader()
{
const string providername = "System.Data.SqlClient";
const string connectionString = ConnectionStrings.Northwind;
ProviderChecker.Check(providername, connectionString);
return(new DatabaseReader(connectionString, providername));
}
/// <summary>
/// Gets the SqlServer NorthWind reader.
/// </summary>
/// <param name="additionalParameters"></param>
/// <returns></returns>
public static DatabaseReader GetNorthwindReader(IAdditionalProperties additionalParameters = null)
{
const string providername = "System.Data.SqlClient";
var connectionString = ConnectionStrings.Northwind;
ProviderChecker.Check(providername, connectionString);
var northwindReader = new DatabaseReader(connectionString, providername, 15, additionalParameters);
northwindReader.Owner = "dbo";
return(northwindReader);
}
public void DetectsSqlAzure()
{
const string providername = "System.Data.SqlClient";
const string connectionString = @"Server=tcp:SERVERNAME.database.windows.net,1433;Database=DBNAME;User ID=USERNAME@SERVERNAME;Password=PASSWORD;Trusted_Connection=False;Encrypt=True;";
ProviderChecker.Check(providername, connectionString);
var target = new SqlAzureOrSqlServerSchemaReader(connectionString, "System.Data.SqlClient");
var actual = target.IsAzureSqlDatabase;
Assert.IsTrue(actual);
}
public void TestProductsQuery()
{
const string providername = "System.Data.SqlClient";
var connectionString = ConnectionStrings.Northwind;
ProviderChecker.Check(providername, connectionString);
var reader = new QueryReader();
var columns = reader.GetQueryColumns(connectionString, providername, "Select ProductID, ProductName FROM Products");
Assert.AreEqual(2, columns.Count);
}
private static DatabaseTable LoadCategoriesFromNorthwind()
{
const string providername = "System.Data.SqlClient";
var connectionString = ConnectionStrings.Northwind;
ProviderChecker.Check(providername, connectionString);
var dbReader = new DatabaseReader(connectionString, providername);
var schema = dbReader.ReadAll();
return(schema.FindTableByName("Categories"));
}
/// <summary>
/// Gets the SqlServer NorthWind reader.
/// </summary>
/// <returns></returns>
public static DatabaseReader GetNorthwindReader()
{
const string providername = "System.Data.SqlClient";
var connectionString = ConnectionStrings.Northwind;
ProviderChecker.Check(providername, connectionString);
var northwindReader = new DatabaseReader(connectionString, providername);
northwindReader.Owner = "dbo";
return(northwindReader);
}
public void DetectsSqlServer()
{
const string providername = "System.Data.SqlClient";
var connectionString = ConnectionStrings.Northwind;
ProviderChecker.Check(providername, connectionString);
var target = new SqlAzureOrSqlServerSchemaReader(connectionString, providername);
var actual = target.IsAzureSqlDatabase;
Assert.IsFalse(actual);
}
//[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 TestInsertNoDirectDatabaseReaderIntegration()
{
//arrange
ProviderChecker.Check(Providername, ConnectionString);
var rdr = new ScriptWriter();
//act
var txt = rdr.ReadTable("Customers", ConnectionString, Providername);
//assert
Assert.IsTrue(txt.Contains("INSERT INTO [Customers]"), "Insert statments created");
Assert.IsTrue(txt.Contains("[CustomerID], [CompanyName]"), "Insert names the columns");
Assert.IsTrue(txt.Contains("'ALFKI'"), "Data includes Alfreds Futterkiste");
}
public void TestInsertNoDirectDatabaseReaderIntegration()
{
//arrange
ProviderChecker.Check(Providername, _connectionString);
var rdr = new ScriptWriter();
//act
var txt = rdr.ReadTable("Categories", _connectionString, Providername, 0);
//assert
Assert.IsTrue(txt.Contains("INSERT INTO [Categories]"), "Insert statments created: [" + txt + "]");
Assert.IsTrue(txt.Contains("[CategoryName], [Description]"), "Insert names the columns: [" + txt + "]");
Assert.IsTrue(txt.Contains("'Beverages'"), "Data includes Beverages: [" + txt + "]");
}
private DatabaseTable LoadCategoriesFromNorthwind()
{
if (_categoriesTable != null)
{
return(_categoriesTable);
}
ProviderChecker.Check(ProviderName, ConnectionString);
var dbReader = new DatabaseReader(ConnectionString, ProviderName);
dbReader.DataTypes(); //ensure we have datatypes (this doesn't hit the database)
_categoriesTable = dbReader.Table("Categories"); //this hits database for columns and constraints
return(_categoriesTable);
}
private DatabaseTable LoadRegionsFromHr()
{
if (_regionsTable != null)
{
return(_regionsTable);
}
ProviderChecker.Check(ProviderName, ConnectionString);
var dbReader = new DatabaseReader(ConnectionString, ProviderName, 0);
dbReader.Owner = "HR";
dbReader.DataTypes(); //ensure we have datatypes (this doesn't hit the database)
_regionsTable = dbReader.Table("REGIONS"); //this hits database for columns and constraints
return(_regionsTable);
}
private DatabaseTable LoadCategoriesFromNorthwind()
{
if (_categoriesTable != null) return _categoriesTable;
if (!File.Exists(ConnectionStrings.SqlServerCeFilePath))
{
Assert.Inconclusive("Cannot test SqlServerCe.4.0 as no database file " + ConnectionStrings.SqlServerCeFilePath);
}
_connectionString = string.Format(CultureInfo.InvariantCulture, "DataSource=\"{0}\";", ConnectionStrings.SqlServerCeFilePath);
ProviderChecker.Check(ProviderName, _connectionString);
var dbReader = new DatabaseReader(_connectionString, ProviderName);
dbReader.DataTypes(); //ensure we have datatypes (this doesn't hit the database)
_categoriesTable = dbReader.Table("Categories"); //this hits database for columns and constraints
return _categoriesTable;
}
private DatabaseTable LoadTable()
{
if (_table != null)
{
return(_table);
}
ProviderChecker.Check(ProviderName, _connectionString);
var dbReader = new DatabaseReader(_connectionString, ProviderName, 0);
dbReader.Owner = "public"; //otherwise you have "postgres" owned tables and views
dbReader.DataTypes(); //ensure we have datatypes (this doesn't hit the database)
_table = dbReader.Table("country"); //this hits database for columns and constraints
return(_table);
}
public void OracleHrTest()
{
const string providername = "System.Data.OracleClient";
const string connectionString = ConnectionStrings.OracleHr;
ProviderChecker.Check(providername, connectionString);
var dbReader = new DatabaseReader(connectionString, providername);
dbReader.Owner = "HR";
DatabaseSchema schema = null;
try
{
schema = dbReader.ReadAll();
}
catch (DbException exception)
{
Assert.Inconclusive("Cannot access database " + exception.Message);
}
var directory = TestHelper.CreateDirectory("Hr");
const string @namespace = "Hr.Domain";
var settings = new CodeWriterSettings {
Namespace = @namespace, CodeTarget = CodeTarget.PocoNHibernateHbm
};
var codeWriter = new CodeWriter(schema, settings);
codeWriter.Execute(directory);
var mapping = directory.GetDirectories("mapping").FirstOrDefault();
if (mapping == null)
{
Assert.Fail("Could not find Mapping subdirectory");
}
var files = mapping.GetFiles("*.xml");
var employeeMap = files.First(f => f.Name == "Employee.hbm.xml");
var doc = XDocument.Load(employeeMap.FullName);
var classElement = doc.Descendants("{urn:nhibernate-mapping-2.2}class").First();
Assert.AreEqual("Employee", (string)classElement.Attribute("name"));
Assert.AreEqual("`EMPLOYEES`", (string)classElement.Attribute("table"));
}
public static string FindFreeTableName(string providerName, string connectionString)
{
ProviderChecker.Check(providerName, connectionString);
var dbReader = new DatabaseReader(connectionString, providerName);
var tables = dbReader.TableList();
//find an unused table name.
const string tableName = "TESTDSR";
var suffix = string.Empty;
var i = 0;
while (tables.Any(t => t.Name.Equals(tableName + suffix, StringComparison.OrdinalIgnoreCase)))
{
i++;
suffix = i.ToString(CultureInfo.InvariantCulture);
}
return(tableName + suffix);
}
public void AdventureWorksTest()
{
const string providername = "System.Data.SqlClient";
const string connectionString = @"Data Source=.\SQLEXPRESS;Integrated Security=true;Initial Catalog=AdventureWorks";
ProviderChecker.Check(providername, connectionString);
var dbReader = new DatabaseReader(connectionString, providername);
DatabaseSchema schema = null;
try
{
schema = dbReader.ReadAll();
}
catch (SqlException exception)
{
Assert.Inconclusive("Cannot access database " + exception.Message);
}
var directory = CreateDirectory("AdventureWorks");
const string @namespace = "AdventureWorks.Domain";
var settings = new CodeWriterSettings {
Namespace = @namespace, CodeTarget = CodeTarget.Poco, WriteStoredProcedures = true
};
var codeWriter = new CodeWriter(schema, settings);
codeWriter.Execute(directory);
var procedures = directory.GetDirectories("Procedures").FirstOrDefault();
if (procedures == null)
{
Assert.Fail("Could not find Procedures subdirectory for stored procedures");
}
var files = procedures.GetFiles("*.cs");
var category = files.First(f => f.Name == "uspLogError.cs");
var cs = File.ReadAllText(category.FullName);
var ok = cs.Contains("public virtual DbCommand CreateCommand(int? errorLogId)");
Assert.IsTrue(ok, "Should contain the uspLogError stored procedure (in standard AdventureWorks db)");
}
public void FactoryToolsTest()
{
const string providername = "System.Data.SqlClient";
//this is normally used
var provider = FactoryTools.GetFactory(providername);
Assert.AreEqual("System.Data.SqlClient.SqlClientFactory", provider.GetType().FullName, "No override, returns SqlClient");
//override with a repository
FactoryTools.ProviderRepository = new DbProviderFactoryRepository();
var manualDescription = new DbProviderFactoryDescription
{
Description = ".NET Framework Data Provider for SuperDuperDatabase",
InvariantName = "SuperDuperDatabase",
Name = "SuperDuperDatabase Data Provider",
AssemblyQualifiedName = typeof(SuperDuperProviderFactory).AssemblyQualifiedName,
};
FactoryTools.ProviderRepository.Add(manualDescription);
provider = FactoryTools.GetFactory(providername);
Assert.AreEqual("System.Data.SqlClient.SqlClientFactory", provider.GetType().FullName, "Overridden, but returns underlying SqlClient");
provider = FactoryTools.GetFactory("SuperDuperDatabase");
Assert.AreEqual(typeof(SuperDuperProviderFactory), provider.GetType(), "Overridden, returns manually added provider");
//override with a single provider
FactoryTools.SingleProviderFactory = SqlClientFactory.Instance;
provider = FactoryTools.GetFactory("Xxxx");
Assert.AreEqual("System.Data.SqlClient.SqlClientFactory", provider.GetType().FullName, "Overridden, always returns SqlClient");
ProviderChecker.Check(providername, ConnectionStrings.Northwind);
var dr = new DatabaseReader(ConnectionStrings.Northwind, "Xxxxx", 0);
var tables = dr.TableList();
Assert.IsTrue(tables.Count > 0, "We called the reader with a bogus provider type, but we got the overridden type");
}
private DatabaseTable LoadCategoriesFromNorthwind()
{
if (_categoriesTable != null)
{
return(_categoriesTable);
}
if (!File.Exists(DatabaseFile))
{
Assert.Inconclusive("SQLite database file not found: " + DatabaseFile);
}
ProviderChecker.Check(ProviderName, ConnectionString);
var dbReader = new DatabaseReader(ConnectionString, ProviderName);
dbReader.DataTypes(); //ensure we have datatypes (this doesn't hit the database)
_categoriesTable = dbReader.Table("Categories"); //this hits database for columns and constraints
if (_categoriesTable == null)
{
Assert.Inconclusive("Could not load Categories table from SQLite file");
}
return(_categoriesTable);
}
public void TestMigration()
{
//arrange
DbProviderFactory factory = null;
try
{
factory = DbProviderFactories.GetFactory(ProviderName);
}
catch (ArgumentException)
{
Assert.Inconclusive("Unable to find System.Data.SqlServerCe.4.0 Data Provider. It may not be installed.");
}
if (!File.Exists(FilePath))
{
Assert.Inconclusive("SqlServerCe4 test requires database file " + FilePath);
}
const string connectionString = "Data Source=\"" + FilePath + "\"";
ProviderChecker.Check(ProviderName, connectionString);
var tableName = MigrationCommon.FindFreeTableName(ProviderName, connectionString);
var migration = new DdlGeneratorFactory(SqlType.SqlServerCe).MigrationGenerator();
var table = MigrationCommon.CreateTestTable(tableName);
var newColumn = MigrationCommon.CreateNewColumn();
var unqiueConstraint = MigrationCommon.CreateUniqueConstraint(newColumn);
var fk = MigrationCommon.CreateForeignKey(table);
var index = MigrationCommon.CreateUniqueIndex(newColumn, tableName);
var createTable = migration.AddTable(table);
var addColumn = migration.AddColumn(table, newColumn);
var addUniqueConstraint = migration.AddConstraint(table, unqiueConstraint);
var addForeignKey = migration.AddConstraint(table, fk);
var addUniqueIndex = migration.AddIndex(table, index);
var dropUniqueIndex = migration.DropIndex(table, index);
var dropForeignKey = migration.DropConstraint(table, fk);
var dropUniqueConstraint = migration.DropConstraint(table, unqiueConstraint);
var dropColumn = migration.DropColumn(table, newColumn);
var dropTable = migration.DropTable(table);
using (new TransactionScope())
{
using (var con = factory.CreateConnection())
{
con.ConnectionString = connectionString;
using (var cmd = con.CreateCommand())
{
con.Open();
Execute(cmd, createTable);
Execute(cmd, addColumn);
Execute(cmd, addUniqueConstraint);
Execute(cmd, addForeignKey);
Execute(cmd, dropForeignKey);
Execute(cmd, dropUniqueConstraint);
Execute(cmd, addUniqueIndex);
Execute(cmd, dropUniqueIndex);
Execute(cmd, dropColumn);
Execute(cmd, dropTable);
}
}
}
}
public void MySqlProcedureTest()
{
const string providername = "MySql.Data.MySqlClient";
var connectionString = ConnectionStrings.MySql;
ProviderChecker.Check(providername, connectionString);
var dbReader = new DatabaseReader(connectionString, providername);
var schema = dbReader.ReadAll();
var procedure = schema.StoredProcedures
.Find(x => string.Equals(x.Name, "film_not_in_stock", StringComparison.OrdinalIgnoreCase));
if (procedure == null)
{
Assert.Inconclusive("No sproc film_not_in_stock in Sakila");
}
//getting the procedure resultsets is a special call
var runner = new ResultSetReader(schema);
runner.Execute();
var directory = TestHelper.CreateDirectory("MySqlSproc");
const string @namespace = "MySqlSproc.Domain";
var settings = new CodeWriterSettings
{
Namespace = @namespace,
CodeTarget = CodeTarget.Poco,
WriteStoredProcedures = true
};
//act
var codeWriter = new CodeWriter(schema, settings);
codeWriter.Execute(directory);
//assert
//This procedure produces a table of inventory ID numbers for the copies of the film not in stock,
//and returns (in the p_film_count parameter) a count that indicates the number of rows in that table.
//using (var connection = new MySqlConnection("Server=...;Database=sakila;Allow User Variables=True;"))
//{
// var sproc = new FilmNotInStock(connection);
// var result = sproc.Execute(2, 1);
// Console.WriteLine(result.PFilmCount);
// Console.WriteLine(string.Join(",", result.FilmNotInStockResult0.Select(x => x.InventoryId)));
//}
var procedures = directory.GetDirectories("Procedures").FirstOrDefault();
if (procedures == null)
{
Assert.Fail("Could not find Procedures subdirectory");
}
var files = procedures.GetFiles("*.cs");
var filmNotInStock = files.FirstOrDefault(f => f.Name == "FilmNotInStock.cs");
Assert.IsNotNull(filmNotInStock,
"Should have written FilmNotInStock class for film_not_in_stock procedure");
var cs = File.ReadAllText(filmNotInStock.FullName);
//there are 2 input parameters, an OUT parameter and a resultset.
Assert.IsTrue(cs.Contains("public virtual FilmNotInStockResult Execute(int? pFilmId, int? pStoreId)"),
"Generated input signature");
var result = files.FirstOrDefault(f => f.Name == "FilmNotInStockResult.cs");
Assert.IsNotNull(result, "Should have written FilmNotInStockResult class");
cs = File.ReadAllText(result.FullName);
Assert.IsTrue(cs.Contains("public virtual int? PFilmCount { get; set; }"),
"Generated property for OUT parameter");
Assert.IsTrue(cs.Contains("public virtual IList<FilmNotInStockResult0> FilmNotInStockResult0 { get; private set; }"),
"Generated collection property for resultset");
}
private static DatabaseReader GetNortwindReader()
{
ProviderChecker.Check(Providername, ConnectionString);
return(new DatabaseReader(ConnectionString, Providername));
}
