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