public void OracleManagedClient()
{
//tested using
//<configuration>
// <system.data>
// <DbProviderFactories>
// <remove invariant="Oracle.ManagedDataAccess.Client" />
// <add name="ODP.NET, Managed Driver"
// invariant="Oracle.ManagedDataAccess.Client"
// description="Oracle Data Provider for .NET, Managed Driver"
// type="Oracle.ManagedDataAccess.Client.OracleClientFactory, Oracle.ManagedDataAccess, Version=4.121.1.0, Culture=neutral, PublicKeyToken=89b483f429c47342" />
// </DbProviderFactories>
// </system.data>
//</configuration>
const string providername = "Oracle.ManagedDataAccess.Client";
const string connectionString = ConnectionStrings.OracleHr;
ProviderChecker.Check(providername, connectionString);
var dbReader = new DatabaseReader(connectionString, providername);
dbReader.Owner = "HR";
var schema = dbReader.ReadAll();
var employees = schema.FindTableByName("EMPLOYEES");
Assert.AreEqual(11, employees.Columns.Count);
var table = dbReader.Table("EMPLOYEES");
Assert.AreEqual(11, table.Columns.Count);
}
private DatabaseTable LoadCountryFromSakila()
{
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("country"); //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);
dbReader.DataTypes(); //ensure we have datatypes (this doesn't hit the database)
_table = dbReader.Table("STAFF"); //this hits database for columns and constraints
return _table;
}
private DatabaseTable LoadTable()
{
if (_table != null) return _table;
ProviderChecker.Check(ProviderName, _connectionString);
var dbReader = new DatabaseReader(_connectionString, ProviderName);
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 MySqlTableTest()
{
const string providername = "MySql.Data.MySqlClient";
var connectionString = ConnectionStrings.MySql;
ProviderChecker.Check(providername, connectionString);
var dbReader = new DatabaseReader(connectionString, providername);
var country = dbReader.Table("country");
Assert.AreEqual(3, country.Columns.Count);
Assert.IsNotNull(country.PrimaryKeyColumn);
Assert.IsTrue(country.FindColumn("country_id").IsPrimaryKey);
}
private DatabaseTable LoadRegionsFromHr()
{
if (_regionsTable != null) return _regionsTable;
ProviderChecker.Check(ProviderName, ConnectionString);
var dbReader = new DatabaseReader(ConnectionString, ProviderName);
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;
}
public void DevartSqlServer()
{
const string providername = "Devart.Data.SqlServer";
const string connectionString = @"Data Source=localhost\SQLEXPRESS;Integrated Security=true;Initial Catalog=AdventureWorks";
ProviderChecker.Check(providername, connectionString);
var dbReader = new DatabaseReader(connectionString, providername);
var schema = dbReader.ReadAll();
var product = schema.FindTableByName("Product");
Assert.AreEqual(25, product.Columns.Count);
var table = dbReader.Table("Product");
Assert.AreEqual(25, table.Columns.Count);
}
public void SqlServerAdventureWorks()
{
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);
var schema = dbReader.ReadAll();
var product = schema.FindTableByName("Product");
Assert.IsTrue(product.Columns.Count >= 17); //different versions of AdventureWorks have different # columns
var table = dbReader.Table("Product");
Assert.IsTrue(table.Columns.Count >= 17);
}
public void DataDirectOracle()
{
const string providername = "DDTek.Oracle";
const string connectionString = "Host=localhost;Service Name=XE;User Id=HR;Password=HR;";
ProviderChecker.Check(providername, connectionString);
var dbReader = new DatabaseReader(connectionString, providername);
dbReader.Owner = "HR";
var schema = dbReader.ReadAll();
var employees = schema.FindTableByName("EMPLOYEES");
Assert.AreEqual(11, employees.Columns.Count);
var table = dbReader.Table("EMPLOYEES");
Assert.AreEqual(11, table.Columns.Count);
}
public void MySqlTest()
{
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 country = schema.FindTableByName("country");
Assert.AreEqual(3, country.Columns.Count);
Assert.IsNotNull(country.PrimaryKeyColumn);
var table = dbReader.Table("city");
Assert.AreEqual(4, table.Columns.Count);
}
public void DevartOracle()
{
const string providername = "Devart.Data.Oracle";
const string connectionString = "Server=localhost;Sid=XE;Port=1521;Direct=true;User Id=hr;Password=hr;";
ProviderChecker.Check(providername, connectionString);
var dbReader = new DatabaseReader(connectionString, providername);
dbReader.Owner = "HR";
var schema = dbReader.ReadAll();
var employees = schema.FindTableByName("EMPLOYEES");
Assert.AreEqual(11, employees.Columns.Count);
var table = dbReader.Table("EMPLOYEES");
Assert.AreEqual(11, table.Columns.Count);
}
public void OracleOdp()
{
const string providername = "Oracle.DataAccess.Client";
const string connectionString = ConnectionStrings.OracleHr;
ProviderChecker.Check(providername, connectionString);
var dbReader = new DatabaseReader(connectionString, providername);
dbReader.Owner = "HR";
var schema = dbReader.ReadAll();
var employees = schema.FindTableByName("EMPLOYEES");
Assert.AreEqual(11, employees.Columns.Count);
var table = dbReader.Table("EMPLOYEES");
Assert.AreEqual(11, table.Columns.Count);
}
public void DataDirectSqlServer()
{
//not sql express
const string providername = "DDTek.SQLServer";
const string connectionString = @"Server=localhost;AuthenticationMethod=NTLM;DatabaseName=AdventureWorks";
ProviderChecker.Check(providername, connectionString);
var dbReader = new DatabaseReader(connectionString, providername);
var schema = dbReader.ReadAll();
var product = schema.FindTableByName("Product");
Assert.AreEqual(25, product.Columns.Count);
var table = dbReader.Table("Product");
Assert.AreEqual(25, table.Columns.Count);
}
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;
}
private DatabaseTable LoadCategoriesFromNorthwind()
{
if (_categoriesTable != null) return _categoriesTable;
if (!File.Exists(FilePath))
{
Assert.Inconclusive("Cannot test SqlServerCe.4.0 as no database file " + FilePath);
}
_connectionString = string.Format(CultureInfo.InvariantCulture, "DataSource=\"{0}\";", FilePath);
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;
}
//[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 OleDb()
{
const string providername = "System.Data.OleDb";
const string connectionString = "Provider=msdaora;Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SID=XE)));User Id=hr;Password=hr;";
ProviderChecker.Check(providername, connectionString);
var dbReader = new DatabaseReader(connectionString, providername);
//exclude a lot of system sprocs that get picked up. This speeds us up massively.
dbReader.Exclusions.StoredProcedureFilter = new PrefixFilter("APEX", "ANY", "AGGR", "AQ$", "BLAST",
"CTX", "DBMS", "DM_", "DBU", "DEVELOP", "DICT", "DIV", "DIU", "DRI", "DRV", "HTF",
"FTP", "FUNCSTATS", "HS$", "HH", "HTML", "HTP", "HTTP", "KUP", "LCR", "MVAGG",
"ODC", "ODM", "OGC", "OLAP", "ORA", "OUTLN", "OWA", "PLIT", "PRIVAT", "PRVT",
"RE$", "SCHEDULER$", "SDO", "SERVER_", "SQL_", "ST_", "STANDARD", "SYS_", "TBLAST", "TFM", "TRANSFORM_",
"URI", "UTL_", "WPG_", "WPIUTL", "WRI$", "WWV", "XDB", "XML");
dbReader.Owner = "HR";
var schema = dbReader.ReadAll();
var employees = schema.FindTableByName("EMPLOYEES");
Assert.AreEqual(11, employees.Columns.Count);
var table = dbReader.Table("EMPLOYEES");
Assert.AreEqual(11, table.Columns.Count);
}
public void MySqlViaDevartTest()
{
const string providername = "Devart.Data.MySql";
var connectionString = ConnectionStrings.MySqlDevart;
ProviderChecker.Check(providername, connectionString);
DiscoverProviderFactory.Discover(connectionString, providername);
var dbReader = new DatabaseReader(connectionString, providername);
dbReader.Owner = "sakila";
var schema = dbReader.ReadAll();
var country = schema.FindTableByName("country");
Assert.AreEqual(3, country.Columns.Count);
Assert.IsNotNull(country.PrimaryKeyColumn);
var table = dbReader.Table("city");
Assert.AreEqual(4, table.Columns.Count);
}
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;
}
