int ODP()
{
Oracle.ManagedDataAccess.Client.OracleConnectionStringBuilder connBuilder = new Oracle.ManagedDataAccess.Client.OracleConnectionStringBuilder();
connBuilder.DataSource = txtDataSource.Text.Trim();
connBuilder.UserID = txtUserId.Text.Trim();
connBuilder.Password = txtPwd.Text.Trim();
connBuilder.ConnectionTimeout = 300;
connBuilder.ConnectionLifeTime = 60;
connBuilder.MinPoolSize = 0;
int rows = 0;
using (IDbConnection conn = ODPClientFactory.CreateConnection())
{
conn.ConnectionString = connBuilder.ConnectionString;
IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = txtSql.Text.Trim();
cmd.CommandTimeout = 300;
conn.Open();
using (IDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
object[] objs = new object[500];
dr.GetValues(objs);
rows++;
}
}
return(rows);
}
}
//bool TestMySQL(string server, string port, string database, string userID, string password, int connectionTimeout)
//{
//MySql.Data.MySqlClient.MySqlConnectionStringBuilder builder = new MySql.Data.MySqlClient.MySqlConnectionStringBuilder();
//builder.Server = server;
// uint portMySQL = Convert.ToUInt32(port);
//builder.Port = portMySQL;
// builder.Database = database;
// uint connectionTimeoutMySQL = Convert.ToUInt32(connectionTimeout);
//builder.ConnectionTimeout = connectionTimeoutMySQL;
// if (!string.IsNullOrWhiteSpace(userID))
// {
// builder.UserID = userID;
// builder.Password = password;
// }
// else
// {
// builder.IntegratedSecurity = true;
// }
// using (var conn = new MySql.Data.MySqlClient.MySqlConnection(builder.ToString()))
// {
// return TestConnection(conn);
// }
//}
bool TestOracle(string server, string port, string database, string userID, string password, int connectionTimeout)
{
Oracle.ManagedDataAccess.Client.OracleConnectionStringBuilder builder = new Oracle.ManagedDataAccess.Client.OracleConnectionStringBuilder();
//If userID is set to "/", password is not needed
if (string.IsNullOrWhiteSpace(password) && (userID == "/"))
{
builder.UserID = userID;
}
if (!string.IsNullOrWhiteSpace(password) && (!string.IsNullOrWhiteSpace(userID)))
{
builder.UserID = userID;
builder.Password = password;
}
//If Port is filled in
if (!string.IsNullOrWhiteSpace(port))
{
builder.ConnectionString = string.Format("Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST={0})(PORT={1}))(CONNECT_DATA=(SERVICE_NAME={2})));User ID={3};Password={4};", server, port, database, userID, password);
}
//if port is not filled in, set default port to 1521
if (string.IsNullOrWhiteSpace(port))
{
builder.ConnectionString = string.Format("Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST={0})(PORT={1}))(CONNECT_DATA=(SERVICE_NAME={2})));User ID={3};Password={4};", server, 1521, database, userID, password);
}
Oracle.ManagedDataAccess.Client.OracleConnection conn = new Oracle.ManagedDataAccess.Client.OracleConnection(builder.ConnectionString);
using (conn)
{
return(TestConnection(conn));
}
}
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
Oracle.ManagedDataAccess.Client.OracleConnectionStringBuilder con = new Oracle.ManagedDataAccess.Client.OracleConnectionStringBuilder(connectionStr);
string userName = con.UserID;
modelBuilder.HasDefaultSchema(userName.ToUpper());
}
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
var schema = new Oracle.ManagedDataAccess.Client.OracleConnectionStringBuilder(this.Database.Connection.ConnectionString).UserID;
modelBuilder.HasDefaultSchema(schema);
}
public static string change_db(string data_source = "localhost:1521/testowa", string user = "******", string password = "******")
{
const string providerName = "Oracle.ManagedDataAccess.Client";
string metaData = "res://*/model_Samochod.csdl|res://*/model_Samochod.ssdl|res://*/model_Samochod.msl";
Oracle.ManagedDataAccess.Client.OracleConnectionStringBuilder oracleBuilder = new Oracle.ManagedDataAccess.Client.OracleConnectionStringBuilder();
oracleBuilder.DataSource = data_source;
oracleBuilder.UserID = user;
oracleBuilder.Password = password;
System.Data.Entity.Core.EntityClient.EntityConnectionStringBuilder efBuilder = new System.Data.Entity.Core.EntityClient.EntityConnectionStringBuilder();
efBuilder.Metadata = metaData;
efBuilder.Provider = providerName;
efBuilder.ProviderConnectionString = oracleBuilder.ConnectionString;
return(efBuilder.ConnectionString);
}
public static string BuildConnectionString(IDictionary <string, object> settings, log4net.ILog logger)
{
string server = settings.GetAsString("Server", "");
string port = settings.GetAsString("Port", "");
string userId = settings.GetAsString("UserID", "");
string password = settings.GetAsString("Password", "");
string database = settings.GetAsString("Database", "");
string dataSourceName = settings.GetAsString("DataSourceName", "");
string connectionTimeout = settings.GetAsString("ConnectionTimeout", "15");
string commandTimeout = settings.GetAsString("CommandTimeout", "120");
string dataSource = settings.GetAsString("Data Source", "");
string encrypted = settings.GetAsString("Encrypt", "False");
logger.Debug("Connection timeout: " + connectionTimeout + ", Command timeout: " + commandTimeout);
if (!settings.ContainsKey("DataProvider"))
{
throw new Exception(CommonMessages.Exception_MissingDataProviderType);
}
string connectionString = string.Empty;
switch ((Model.Settings.SQLProvider)Enum.Parse(typeof(Model.Settings.SQLProvider), settings.GetAsString("DataProvider", ""), true))
{
case Model.Settings.SQLProvider.ODBC:
if (string.IsNullOrEmpty(dataSourceName))
{
throw new Exception(CommonMessages.Exception_MissingODBCDatasourceName);
}
connectionString = string.Format("DSN={0}", dataSourceName);
break;
case Model.Settings.SQLProvider.PostgreSQL:
if (string.IsNullOrEmpty(server))
{
throw new Exception(CommonMessages.Exception_MissingDatabaseServer);
}
if (string.IsNullOrEmpty(database))
{
throw new Exception(CommonMessages.Exception_MissingDatabaseName);
}
if (!string.IsNullOrEmpty(userId) && string.IsNullOrEmpty(password))
{
throw new Exception(CommonMessages.Exception_MissingDatabasePassword);
}
if (port == null || port == string.Empty)
{
port = "5432";
}
connectionString = String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4};Timeout={5};CommandTimeout={6};SSL Mode={7};Trust Server Certificate={8}", server, port, userId, password, database, connectionTimeout, commandTimeout, encrypted.ToUpper() == "TRUE" ? "Require" : "Prefer", encrypted);
break;
case Model.Settings.SQLProvider.SQLServer:
if (string.IsNullOrEmpty(server))
{
throw new Exception(CommonMessages.Exception_MissingDatabaseServer);
}
if (string.IsNullOrEmpty(database))
{
throw new Exception(CommonMessages.Exception_MissingDatabaseName);
}
if (!string.IsNullOrEmpty(userId) && string.IsNullOrEmpty(password))
{
throw new Exception(CommonMessages.Exception_MissingDatabasePassword);
}
if (port != null && port != string.Empty)
{
server += ", " + port;
}
connectionString = userId != null && userId != string.Empty ? String.Format("server={0};User ID={1};Password={2};Database={3}; Connection Timeout={4}; Encrypt={5}; TrustServerCertificate={5};", server, userId, password, database, connectionTimeout, encrypted) : String.Format("server={0};integrated security=True;Database={1}; Connection Timeout={2}; Encrypt={3}; TrustServerCertificate={3};", server, database, connectionTimeout, encrypted);
break;
case Model.Settings.SQLProvider.MySQL:
if (string.IsNullOrEmpty(server))
{
throw new Exception(CommonMessages.Exception_MissingDatabaseServer);
}
if (string.IsNullOrEmpty(database))
{
throw new Exception(CommonMessages.Exception_MissingDatabaseName);
}
if (!string.IsNullOrEmpty(userId) && string.IsNullOrEmpty(password))
{
throw new Exception(CommonMessages.Exception_MissingDatabasePassword);
}
if (port == null || port == string.Empty)
{
port = "3306";
}
connectionString = String.Format("Server={0};Port={1};Database={2};User Id={3};Password={4}", server, port, database, userId, password);
break;
case Model.Settings.SQLProvider.Oracle:
if (string.IsNullOrEmpty(server))
{
throw new Exception(CommonMessages.Exception_MissingDatabaseServer);
}
if (string.IsNullOrEmpty(database))
{
throw new Exception(CommonMessages.Exception_MissingDatabaseName);
}
if (!string.IsNullOrEmpty(userId) && string.IsNullOrEmpty(password))
{
throw new Exception(CommonMessages.Exception_MissingDatabasePassword);
}
Oracle.ManagedDataAccess.Client.OracleConnectionStringBuilder builder = new Oracle.ManagedDataAccess.Client.OracleConnectionStringBuilder();
//If userID is set to "/", password is not needed
if (string.IsNullOrWhiteSpace(password) && (userId == "/"))
{
builder.UserID = userId;
}
if (!string.IsNullOrWhiteSpace(password) && (!string.IsNullOrWhiteSpace(userId)))
{
builder.UserID = userId;
builder.Password = password;
}
//If Port is filled in
if (!string.IsNullOrWhiteSpace(port))
{
builder.ConnectionString = string.Format("Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST={0})(PORT={1}))(CONNECT_DATA=(SERVICE_NAME={2})));User ID={3};Password={4};", server, port, database, userId, password);
}
//if port is not filled in, set default port to 1521
if (string.IsNullOrWhiteSpace(port))
{
builder.ConnectionString = string.Format("Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST={0})(PORT={1}))(CONNECT_DATA=(SERVICE_NAME={2})));User ID={3};Password={4};", server, 1521, database, userId, password);
}
connectionString = builder.ConnectionString;
break;
default:
throw new Exception(CommonMessages.Exception_InvalidDataProviderType);
}
return(connectionString);
}
public static QueryComposer.Adapters.PCORI.PCORIModelAdapter CreatePCORIModelAdapterAdapter(string connectionString, Lpp.Dns.DataMart.Model.Settings.SQLProvider sqlProvider, string schema = null)
{
Dictionary <string, object> adapterSettings;
if (sqlProvider == Settings.SQLProvider.SQLServer)
{
var connectionStringBuilder = new System.Data.SqlClient.SqlConnectionStringBuilder(connectionString);
adapterSettings = new Dictionary <string, object>()
{
{ "Server", connectionStringBuilder.DataSource },
{ "UserID", connectionStringBuilder.UserID },
{ "Password", connectionStringBuilder.Password },
{ "Database", connectionStringBuilder.InitialCatalog },
{ "DataProvider", sqlProvider.ToString() }
};
}
else if (sqlProvider == Settings.SQLProvider.PostgreSQL)
{
var postgresConnectionStringBuilder = new Npgsql.NpgsqlConnectionStringBuilder(connectionString);
adapterSettings = new Dictionary <string, object>()
{
{ "Server", postgresConnectionStringBuilder.Host },
{ "Port", postgresConnectionStringBuilder.Port.ToString() },
{ "UserID", postgresConnectionStringBuilder.Username },
//{"Password", System.Text.Encoding.UTF8.GetString(postgresConnectionStringBuilder.PasswordAsByteArray) },
{ "Password", postgresConnectionStringBuilder.Password },
{ "Database", postgresConnectionStringBuilder.Database },
{ "ConnectionTimeout", postgresConnectionStringBuilder.Timeout.ToString() },
{ "CommandTimeout", postgresConnectionStringBuilder.CommandTimeout.ToString() },
{ "DatabaseSchema", schema },
{ "DataProvider", sqlProvider.ToString() }
};
}
else if (sqlProvider == Settings.SQLProvider.Oracle)
{
var oracleConnectionStringBuilder = new Oracle.ManagedDataAccess.Client.OracleConnectionStringBuilder(connectionString);
adapterSettings = new Dictionary <string, object>()
{
{ "Server", "" },
{ "Port", "" },
{ "Database", "" },
{ "UserID", oracleConnectionStringBuilder.UserID },
{ "Password", oracleConnectionStringBuilder.Password },
{ "DataProvider", sqlProvider.ToString() },
{ "DatabaseSchema", schema }
};
//(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST={server address})(PORT=1521))(CONNECT_DATA=(SERVICE_NAME={service name})))
System.Text.RegularExpressions.Regex regex = new System.Text.RegularExpressions.Regex(@"\((?:[\w|\=|\.]+)\)");
var matches = regex.Matches(oracleConnectionStringBuilder.DataSource);
foreach (var m in matches)
{
string capture = m.ToString();
string[] split = capture.Substring(1, capture.Length - 2).Split(new[] { '=' });
if (string.Equals("HOST", split[0], StringComparison.OrdinalIgnoreCase))
{
adapterSettings["Server"] = split[1];
}
else if (string.Equals("PORT", split[0], StringComparison.OrdinalIgnoreCase))
{
adapterSettings["Port"] = split[1];
}
else if (string.Equals("SERVICE_NAME", split[0], StringComparison.OrdinalIgnoreCase))
{
adapterSettings["Database"] = split[1];
}
}
}
else
{
throw new NotImplementedException("Support for parsing configuration string into adapter settings not completed yet.");
}
var adapter = new QueryComposer.Adapters.PCORI.PCORIModelAdapter(new RequestMetadata
{
CreatedOn = DateTime.UtcNow,
MSRequestID = "Unit Test Request"
});
adapter.Initialize(adapterSettings, Guid.NewGuid().ToString("D"));
return(adapter);
}
