private static ISessionFactory GetSQLServerSessionFactory(DBEnvironment _environment)
{
ISessionFactory factory = null;
String connectionString = DatabaseUtil.GetConnectionStringByEnvironment(_environment);
Assembly mapping = GetAssemblyWithMapping();
MsSqlConfiguration configurer = GetMSMsSqlConfigurationByEnvironment(_environment);
FluentConfiguration config = Fluently.Configure()
.Database(configurer)
.CurrentSessionContext("context")
.Cache(
c => c.UseQueryCache().UseSecondLevelCache()
)
.Mappings(m => m.FluentMappings.AddFromAssembly(mapping)
);
Assembly secondMapping = GetSecondAssemblyWithMapping();
Assembly thirdMapping = GetThirdAssemblyWithMapping();
factory = config.BuildSessionFactory();
return(factory);
}
private static MsSqlConfiguration GetMSMsSqlConfigurationByEnvironment(DBEnvironment _environment)
{
var connectionString = DatabaseUtil.GetConnectionStringByEnvironment(_environment);
MsSqlConfiguration config = null;
var version = (DBMSSQLServerVersion)_environment.DBMSVersion;
switch (version)
{
case DBMSSQLServerVersion.Versao2005:
config = MsSqlConfiguration.MsSql2005.ConnectionString(connectionString);
break;
case DBMSSQLServerVersion.Versao2008:
config = MsSqlConfiguration.MsSql2008.ConnectionString(connectionString);
break;
case DBMSSQLServerVersion.Versao2012:
config = MsSqlConfiguration.MsSql2012.ConnectionString(connectionString);
break;
default:
config = MsSqlConfiguration.MsSql2008.ConnectionString(connectionString);
break;
}
return(config);
}
public void ShowView()
{
Debug.WriteLine("ShowEmployeeView");
// buttons
parent.AddButton.Show();
parent.EditButton.Show();
parent.DeleteButton.Show();
// other
parent.dataGridView1.Show();
// setup dataGridView
DBEnvironment.SetEmployees();
parent.employees = DBEnvironment.GetEmployees();
parent.dataGridView1.DataSource = parent.employees;
// refactor to specify names
parent.dataGridView1.Columns.Remove("Address");
parent.dataGridView1.Columns.Remove("Name");
parent.dataGridView1.Columns.Remove("ContactInformation");
if (parent.user.EmployeePosition != Employee.Position.Manager)
{
parent.dataGridView1.Columns["wage"].Visible = false;
}
parent.dataGridView1.Columns.Remove("Credentials");
parent.Refresh();
}
private static SqlConnection GetSQLServerConnection(DBEnvironment _environment)
{
var connectionString = GetSQLServerConnectionStringByEnvironment(_environment);
try
{
return(new SqlConnection(connectionString));
}
catch (Exception)
{
return(null);
}
}
private static MySql.Data.MySqlClient.MySqlConnection GetMySQLConnection(DBEnvironment _environment)
{
var connectionString = GetMySQLConnectionStringByEnvironment(_environment);
try
{
return(new MySql.Data.MySqlClient.MySqlConnection(connectionString));
}
catch (Exception)
{
return(null);
}
}
private static Npgsql.NpgsqlConnection GetPostgreSQLConnection(DBEnvironment _environment)
{
var connectionString = GetPostgreSQLConnectionStringByEnvironment(_environment);
try
{
return(new Npgsql.NpgsqlConnection(connectionString));
}
catch (Exception)
{
return(null);
}
}
public void ShowView()
{
Debug.WriteLine("Show OrderView");
// buttons
parent.FulfillOrderButton.Show();
// other
parent.dataGridView1.Show();
// setup dataGridView
parent.orders = DBEnvironment.RetrieveUnfulfilledOrders();
parent.dataGridView1.DataSource = parent.orders;
parent.Refresh();
}
public void RxClaimDev_ConnectionString()
{
// arrange
string database = "RXCLAIM";
DBEnvironment environment = DBEnvironment.DEV;
string expected = "PERSIST SECURITY INFO=FALSE;INTEGRATED SECURITY=SSPI;DATA SOURCE=SQLDEV1;INITIAL CATALOG=RXCLAIM;CONNECT TIMEOUT=6000";
// act
GHCDataAccessLayer dataAccess = GHCDataAccessLayerFactory.GetDataAccessLayer(environment, DataProviderType.Sql, database);
// assert
string actual = dataAccess.ConnectionString;
Assert.AreEqual(expected, actual, "RXCLAIMDEV connection string does not match");
}
public void BadgerCareProd_ConnectionString()
{
// arrange
string database = "BADGERCARE";
DBEnvironment environment = DBEnvironment.PROD;
string expected = "PERSIST SECURITY INFO=FALSE;INTEGRATED SECURITY=SSPI;DATA SOURCE=ASCLARITY;INITIAL CATALOG=BADGERCARE;CONNECT TIMEOUT=6000";
// act
GHCDataAccessLayer dataAccess = GHCDataAccessLayerFactory.GetDataAccessLayer(environment, DataProviderType.Sql, database);
// assert
string actual = dataAccess.ConnectionString;
Assert.AreEqual(expected, actual, "BADGERCAREPROD connection string does not match");
}
public static Boolean ConnectToSQLServer(DBEnvironment _environment)
{
try
{
var connectionString = GetSQLServerConnectionStringByEnvironment(_environment);
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
return(true);
}
}
catch (Exception)
{
throw;
}
}
public static Boolean ConnectToFirebird(DBEnvironment _environment)
{
try
{
var connectionString = GetFirebirdConnectionStringByEnvironment(_environment);
using (var connection = new FirebirdSql.Data.FirebirdClient.FbConnection(connectionString))
{
connection.Open();
return(true);
}
}
catch (Exception)
{
throw;
}
}
public static Boolean ConnectToOracle(DBEnvironment _environment)
{
try
{
var connectionString = GetOracleConnectionStringByEnvironment(_environment);
using (var connection = new Oracle.ManagedDataAccess.Client.OracleConnection(connectionString))
{
connection.Open();
return(true);
}
}
catch (Exception)
{
throw;
}
}
public static Boolean ConnectToPostgreSQL(DBEnvironment _environment)
{
try
{
var connectionString = GetPostgreSQLConnectionStringByEnvironment(_environment);
using (var connection = new Npgsql.NpgsqlConnection(connectionString))
{
connection.Open();
return(true);
}
}
catch (Exception)
{
throw;
}
}
public static Boolean ConnectToMySQL(DBEnvironment _environment)
{
try
{
var connectionString = GetMySQLConnectionStringByEnvironment(_environment);
using (var connection = new MySql.Data.MySqlClient.MySqlConnection(connectionString))
{
connection.Open();
return(true);
}
}
catch (Exception)
{
throw;
}
}
public void ShowView()
{
Debug.WriteLine("ShowMovieView");
// buttons
parent.AddButton.Show();
parent.EditButton.Show();
parent.DeleteButton.Show();
// other
parent.dataGridView1.Show();
// setup dataGridView
DBEnvironment.SetMovies();
parent.movies = DBEnvironment.GetMovies();
parent.dataGridView1.DataSource = parent.movies;
parent.dataGridView1.Columns["customerRating"].Visible = false;
parent.Refresh();
}
public static String GetConnectionStringByEnvironment(DBEnvironment _environment)
{
String connectionString = String.Empty;
var dbms = (DBMSTypeEnum)_environment.DBMS;
try
{
switch (dbms)
{
case DBMSTypeEnum.Firebird:
connectionString = GetFirebirdConnectionStringByEnvironment(_environment);
break;
case DBMSTypeEnum.MySQL:
connectionString = GetMySQLConnectionStringByEnvironment(_environment);
break;
case DBMSTypeEnum.Oracle:
connectionString = GetOracleConnectionStringByEnvironment(_environment);
break;
case DBMSTypeEnum.PostgreSQL:
connectionString = GetPostgreSQLConnectionStringByEnvironment(_environment);
break;
case DBMSTypeEnum.SqlServer:
connectionString = GetSQLServerConnectionStringByEnvironment(_environment);
break;
default:
connectionString = String.Empty;
break;
}
return(connectionString);
}
catch (Exception ex)
{
String log = ex.Message;
throw;
}
}
public static ISessionFactory GetContextForConnection(DBEnvironment environment)
{
ISessionFactory factory = null;
var dbms = (DBMSTypeEnum)environment.DBMS;
try
{
switch (dbms)
{
case DBMSTypeEnum.Firebird:
factory = GetFirebirdSessionFactory();
break;
case DBMSTypeEnum.MySQL:
factory = GetMySQLSessionFactory(environment);
break;
case DBMSTypeEnum.Oracle:
factory = GetOracleSessionFactory(environment);
break;
case DBMSTypeEnum.PostgreSQL:
factory = GetPostgreSQLSessionFactory(environment);
break;
case DBMSTypeEnum.SqlServer:
factory = GetSQLServerSessionFactory(environment);
break;
default:
factory = null;
break;
}
return(factory);
}
catch (Exception ex)
{
String log = ex.Message;
throw;
}
}
private static ISessionFactory GetMySQLSessionFactory(DBEnvironment _environment)
{
ISessionFactory factory = null;
String connectionString = DatabaseUtil.GetConnectionStringByEnvironment(_environment);
FluentConfiguration config = Fluently.Configure()
.Database(
//MySQLConfiguration.Standard.ConnectionString(connectionString).ShowSql())
MySQLConfiguration.Standard.ConnectionString(connectionString))
.CurrentSessionContext("context")
.Cache(
c => c.UseQueryCache().UseSecondLevelCache()
);
//Mapeia os assemblies externos primeiro, geralmente o GetAssemblyWithMapping vai ser o assembly de mapeamento
//do projeto principal
Assembly secondMapping = GetSecondAssemblyWithMapping();
if (secondMapping != null)
{
config.Mappings(m => m.FluentMappings.AddFromAssembly(secondMapping));
}
Assembly thirdMapping = GetThirdAssemblyWithMapping();
if (thirdMapping != null)
{
config.Mappings(m => m.FluentMappings.AddFromAssembly(thirdMapping));
}
Assembly mapping = GetAssemblyWithMapping();
config.Mappings(m => m.FluentMappings.AddFromAssembly(mapping));
factory = config.BuildSessionFactory();
return(factory);
}
public static List <DBEnvironment> ConvertXMLToEnvironments(String xml, List <DBEnvironment> environments = null)
{
List <DBEnvironment> envs = new List <DBEnvironment>();
if (String.IsNullOrEmpty(xml))
{
return(null);
}
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.LoadXml(xml);
string xpath = "configuracaoambientes/configuracaoambiente";
var nodes = xmlDoc.SelectNodes(xpath);
for (int i = 0; i < nodes.Count; i++)
{
DBEnvironment amb = new DBEnvironment();
amb.Name = nodes[i].ChildNodes[0].InnerXml;
amb.DBMS = Convert.ToInt32(nodes[i].ChildNodes[1].InnerXml);
amb.Hostname = nodes[i].ChildNodes[2].InnerXml;
amb.Port = nodes[i].ChildNodes[3].InnerXml;
amb.Instance = nodes[i].ChildNodes[4].InnerXml;
amb.User = nodes[i].ChildNodes[5].InnerXml;
amb.Pass = nodes[i].ChildNodes[6].InnerXml;
amb.EnvironmentType = Convert.ToInt32(nodes[i].ChildNodes[7].InnerXml);
if (environments == null)
{
envs.Add(amb);
}
else
{
environments.Add(amb);
}
}
return(envs);
}
public void ShowView()
{
Debug.WriteLine("ShowCustomerView");
// buttons
parent.AddButton.Show();
parent.EditButton.Show();
parent.DeleteButton.Show();
parent.UpdateRatingsButton.Show();
// other
parent.dataGridView1.Show();
// setup dataGridView
DBEnvironment.SetCustomers();
parent.customers = DBEnvironment.GetCustomers();
parent.dataGridView1.DataSource = parent.customers;
// refactor to specify all columns
parent.dataGridView1.Columns.Remove("Address");
parent.dataGridView1.Columns.Remove("Name");
parent.dataGridView1.Columns.Remove("ContactInformation");
parent.dataGridView1.Columns.Remove("Credentials");
parent.Refresh();
}
private static Oracle.ManagedDataAccess.Client.OracleConnection GetOracleConnection(DBEnvironment _environment)
{
var connectionString = GetOracleConnectionStringByEnvironment(_environment);
try
{
return(new Oracle.ManagedDataAccess.Client.OracleConnection(connectionString));
}
catch (Exception)
{
return(null);
}
}
private static String GetFirebirdConnectionStringByEnvironment(DBEnvironment _environment)
{
return(String.Format(@"User={0};Password={1};Database={2};DataSource={3};
Port={4};Dialect=3;Charset=NONE;Role=;Connection lifetime=15;Pooling=true;
MinPoolSize=0;MaxPoolSize=50;ServerType=0;", _environment.User, _environment.Pass, _environment.Instance, _environment.Hostname, _environment.Port));
}
private static String GetOracleConnectionStringByEnvironment(DBEnvironment _environment)
{
return(String.Format(@"Data Source={0};User ID={1};Password={2}", _environment.Hostname + ":" + _environment.Port + "/" + _environment.Instance, _environment.User, _environment.Pass));
}
/// <summary>
/// Get the data access layer for the specified database. The environment (Prod vs Dev) is specified overrides
/// the environment inferred from the machine context.
/// </summary>
/// <param name="environment">Environment</param>
/// <param name="dataProviderType">DataProviderType</param>
/// <param name="database">string</param>
/// <returns>GHCDataAccessLayer</returns>
public static GHCDataAccessLayer GetDataAccessLayer(DBEnvironment environment, DataProviderType dataProviderType, string database)
{
return(GetDataAccessLayer(GHCConnectionStringBuilder.GetConnectionString(database, dataProviderType, environment), dataProviderType));
}
public CustomUnitOfWork(DBEnvironment environment) : base(environment)
{
}
/// <summary>
/// Retrieves the actual string from the repository. This could be Active Directory,
/// app.config, registry, or something else entirely. The current implementation is
/// using active directory through the GHCSecurity object, retrieving the connection
/// string from the description of an AD account created specifically to store the
/// connection strings
/// </summary>
/// <param name="database">string</param>
/// <param name="environment">Environment</param>
/// <returns>string</returns>
private static string GetStringFromRepository(string database, DataProviderType dataProviderType, DBEnvironment environment)
{
string connectionString = "";
// connection strings are stored in active directory as the description for a user.
// the username is 'database + environment', so CLARITYDEV would return the dev
// connection string.
connectionString = GHCActiveDirectory.GetUserDescription(database + environment);
#region "ResourceManager Repository"
//System.Resources.ResourceManager rm = GHCDataAccess.Properties.Resources.ResourceManager;
//connectionString = rm.GetString(database + environment);
//if(connectionString == null)
//{
// throw new System.ArgumentOutOfRangeException("ConnectionName", "Invalid connection name. The database and environment requested does not exist as a resource. (" + database + " :: " + environment + ")");
//}
#endregion "ResourceManager Repository"
return(connectionString);
}
private static String GetPostgreSQLConnectionStringByEnvironment(DBEnvironment _environment)
{
return(String.Format(@"User ID={0};Password={1};Host={2};Port={3};Database={4};
Pooling=true;Connection Lifetime=0;" , _environment.User, _environment.Pass, _environment.Hostname, _environment.Port, _environment.Instance));
}
private KeyValuePair<string, string> CreateReplicationStatement(DBEnvironment env, string CreateStatement)
{
Regex rgx = new Regex("`(.*?)`");
Match tableName = rgx.Match(CreateStatement);
string ReplacementTable = tableName.Groups[1].ToString();
switch (env)
{
case DBEnvironment.Production:
ReplacementTable = "`" + ReplacementTable + PROD_ID + "`";
break;
case DBEnvironment.Staging:
ReplacementTable = "`" + ReplacementTable + STAGE_ID + "`";
break;
default:
throw new Exception("Invalid Env");
}
return new KeyValuePair<string, string>(ReplacementTable, rgx.Replace(CreateStatement, ReplacementTable, 1));
}
private static String GetSQLServerConnectionStringByEnvironment(DBEnvironment _environment)
{
return(String.Format(@"Server={0};Database={1};User Id={2};
Password={3};", _environment.Hostname, _environment.Instance, _environment.User, _environment.Pass));
}
protected NHibernateUnitOfWork(DBEnvironment environment)
{
_environment = environment;
}
/// <summary>
/// Gets the connection string for the database, provider, and environment.
/// </summary>
/// <param name="database">string</param>
/// <param name="dataProviderType">DataProviderType</param>
/// <param name="environment">Environment</param>
/// <returns>string</returns>
public static string GetConnectionString(string database, DataProviderType dataProviderType, DBEnvironment environment)
{
return(GetStringFromRepository(database, dataProviderType, environment));
}
