static OracleConnectionStringBuilder GetBuilder()
{
var builder = new OracleConnectionStringBuilder();
builder.UserID = "scott";
builder.Password = "******";
builder.DataSource = "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.170)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl.Router)))";
builder.Pooling = true;
builder.ConnectionTimeout = 5;
//Console.WriteLine(builder.ToString());
return builder;
}
public override void DropAndRecreate()
{
var connectionString = ConfigurationManager.ConnectionStrings[Name].ConnectionString;
var connectionStringBuilder = new OracleConnectionStringBuilder
{
ConnectionString = connectionString
};
var databaseName = connectionStringBuilder.UserID;
var ddl = string.Format("DECLARE\r\n" +
"\r\n" +
" c INTEGER := 0;\r\n" +
"\r\n" +
"BEGIN\r\n" +
" SELECT count(*) INTO c FROM sys.dba_users WHERE USERNAME = \'{0}\';\r\n" +
" IF c = 1 THEN\r\n" +
" execute immediate (\'drop user {0} cascade\');\r\n" +
" execute immediate (\'drop tablespace {0}_TS\');\r\n" +
" execute immediate (\'drop tablespace {0}_TS_TMP\');\r\n" +
" END IF;\r\n" +
" \r\n" +
" execute immediate (\'create tablespace {0}_TS datafile \'\'{0}.dat\'\' size 10M reuse autoextend on\');\r\n" +
" execute immediate (\'create temporary tablespace {0}_TS_TMP tempfile \'\'{0}_TMP.dat\'\' size 10M reuse autoextend on\');\r\n" +
" execute immediate (\'create user {0} identified by pass default tablespace {0}_TS temporary tablespace {0}_TS_TMP\');\r\n" +
" execute immediate (\'grant create session to {0}\');\r\n" +
" execute immediate (\'grant create table to {0}\');\r\n" +
" execute immediate (\'GRANT UNLIMITED TABLESPACE TO {0}\');\r\n" +
"\r\n" +
"END;", databaseName);
using (var db = Db.FromConfig(MasterName))
{
db.Execute(ddl);
}
}
public override void SetUpFixture()
{
OracleInsightDbProvider.RegisterProvider();
_connectionStringBuilder = new OracleConnectionStringBuilder();
_connectionStringBuilder.ConnectionString = "Data Source = (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=testserver)(PORT=1521))); User Id = system; Password = Password1";
_connection = _connectionStringBuilder.Open();
}
public OracleRepository(string connectionString)
{
OracleInsightDbProvider.RegisterProvider();
db = new OracleConnectionStringBuilder(connectionString);
searchRepository = new SearchRepository(db);
historyRepository = new AccessHistoryRepository(db);
dashboardRepository = new DashboardRepository(db);
}
public OracleRepository()
{
OracleConnectionStringBuilder oraCSB = new OracleConnectionStringBuilder();
oraCSB.Direct = true;
oraCSB.Server = "45.55.22.169";
oraCSB.Port = 1521;
oraCSB.Sid = "xe";
oraCSB.UserId = "cresa_prod";
oraCSB.Password = "******";
ConnectionString = oraCSB.ConnectionString;
}
public static void Clean(params string[] tableNames)
{
var connectionString = ConfigurationManager.ConnectionStrings["TestDb"].ConnectionString;
var connectionBuilder = new OracleConnectionStringBuilder(connectionString);
var schemaName = connectionBuilder.UserID;
using (var conn = new OracleConnection(connectionString))
{
conn.Open();
DropTables(conn, schemaName, tableNames);
}
}
protected override IDbConnection MakeUnderlyingConnection()
{
string dataSource = string.Format("{0}:{1}/{2}", ServerName, Port, SidOrServiceName);
OracleConnectionStringBuilder bld = new OracleConnectionStringBuilder
{
DataSource = dataSource,
UserID = UserName,
Password = Password
};
return new OracleConnection(bld.ConnectionString);
}
public static bool CheckIfTableExists(string tableName)
{
var connectionString = ConfigurationManager.ConnectionStrings["TestDb"].ConnectionString;
var connectionBuilder = new OracleConnectionStringBuilder(connectionString);
var databaseName = connectionBuilder.UserID;
using (var conn = new OracleConnection(connectionString))
{
conn.Open();
return CheckIfTableExists(conn, databaseName, tableName);
}
}
public Repository()
{
OracleInsightDbProvider.RegisterProvider();
string connectionString = "User Id=RMS_GUEST;Password=RMS_GUEST;Data Source=lxrmsdevdb1.beckman.com:1521/RMSDVA";
this.db = new OracleConnectionStringBuilder(connectionString);
this.instrumentInfoRepository = new InstrumentInfoRepository(this.db);
this.instrumentRepository = new InstrumentRepository(this.db);
this.accessHistoryRepository = new AccessHistoryRepository(this.db);
this.eventRepository = new EventRepository(this.db);
this.performanceDetailsRepository = new PerformanceDetailsRepository(this.db);
this.systemStatusRepository = new SystemStatusRepository(this.db);
this.instrumentAccessHistoryRepository = new InstrumentAccessHistoryRepository(this.db);
}
public string GetConnectionStringOracle()
{
//return @"Provider=OraOLEDB.Oracle"
// + @"; host=" + server + @":" + port
// + @"; Data Source="+ dbName
// + @"; User Id=" + userName
// + @"; Password="******"; OLEDB.NET=True;";
OracleConnectionStringBuilder csb = new OracleConnectionStringBuilder();
csb.DataSource = dbName;
csb.UserID = userName;
csb.Password = password;
return(csb.ConnectionString);
}
private static string ComposeConnectionString(IConfiguration configuration)
{
var builder = new OracleConnectionStringBuilder
{
Server = configuration["DatabaseServer"],
UserId = configuration["UserId"],
Password = configuration["Password"],
ServiceName = configuration["ServiceName"],
Port = int.Parse(configuration["Port"]),
Direct = true,
Pooling = true,
LicenseKey = configuration["DevartLicenseKey"]
};
return(builder.ToString());
}
protected override IDbConnection CreateConnection(DBObject dbObject, out string dsn)
{
var dataSourceName = dbObject.IP ?? dbObject.Node;
var connStrBuilder = new OracleConnectionStringBuilder();
if (dbObject.ConnectedMode == DBObject.Mode.Direct)
{
connStrBuilder.DataSource = string.Format(@"(DESCRIPTION = (CONNECT_DATA = (SERVER=DEDICATED)(SID = {0}))(ADDRESS_LIST = (ADDRESS = (COMMUNITY = tcp.world)(PROTOCOL = TCP)(Host = {1})(Port = 1521))(ADDRESS = (COMMUNITY = tcp.world)(PROTOCOL = TCP)(Host = {1})(Port = 1526))))", dbObject.Node, dataSourceName);
}
else
{
connStrBuilder.DataSource = dbObject.Node;
}
connStrBuilder.UserID = dbObject.ID;
if (LazyPassword.GetLazyPasswordEnabled(dbObject))
{
dbObject.Password = LazyPassword.GetPassword(new OracleConnection(), dbObject.ID, dbObject.LazyPasswordSecretKey, dbObject.LazyPasswordSecretKeyNode);
}
connStrBuilder.Password = dbObject.Password;
var extra = dbObject.ConnectionString as OracleConnectionString ?? new OracleConnectionString();
var ps = typeof(OracleConnectionString).GetProperties().Select(r =>
{
var attrs = r.GetCustomAttributes(false).OfType <Attribute>();
return(new
{
Value = r.GetValue(extra, null),
DisplayName = InternalHelper.GetValue <DisplayNameAttribute, string>(attr => attr.DisplayName, attrs),
DefaultValue = InternalHelper.GetValue <DefaultValueAttribute, object>(attr => attr.Value, attrs),
});
});
foreach (var p in ps)
{
var value = p.Value ?? p.DefaultValue;
if (value != null)
{
typeof(OracleConnectionStringBuilder).GetProperty(p.DisplayName).SetValue(connStrBuilder, value, null);
}
}
dsn = dbObject.Node;
return(new OracleConnection(connStrBuilder.ConnectionString));
}
public void OracleDatabaseIsReadFromConfigFileWhenSetAsDefault()
{
var oracleBuilder = new OracleConnectionStringBuilder()
{
DataSource = OracleDataSource,
UserID = OracleUserID,
Password = OraclePassword
};
configurationStart.ForDatabaseNamed(OracleDatabaseName)
.AsDefault()
.ThatIs
.AnOracleDatabase()
.WithConnectionString(oracleBuilder)
.WithPackageNamed(PackageName)
.AndPrefix(Prefix);
var settings = GetSettings <DatabaseSettings>();
var oracleSettings = GetSettings <OracleConnectionSettings>();
Assert.IsNotNull(oracleSettings);
var package = oracleSettings.OracleConnectionsData.Get(OracleDatabaseName).Packages.Get(PackageName);
Assert.IsNotNull(package);
Assert.AreEqual(Prefix, package.Prefix);
Assert.AreEqual(OracleDatabaseName, settings.DefaultDatabase);
var connectionStringSettings = GetConnectionStringSettings();
var connectionStringElement = connectionStringSettings.ConnectionStrings[OracleDatabaseName];
Assert.IsNotNull(connectionStringElement);
var connectionString = connectionStringSettings.ConnectionStrings[OracleDatabaseName].ConnectionString;
var providerName = connectionStringSettings.ConnectionStrings[OracleDatabaseName].ProviderName;
var connectionBuilder = new OracleConnectionStringBuilder(connectionString);
Assert.AreEqual(OracleDataSource, connectionBuilder.DataSource);
Assert.AreEqual(OracleUserID, connectionBuilder.UserID);
Assert.AreEqual(OraclePassword, connectionBuilder.Password);
Assert.AreEqual(DbProviderMapping.DefaultOracleProviderName, providerName);
}
public static OracleConnectionSettings GetSettings(string connectionString)
{
var csb = new OracleConnectionStringBuilder(connectionString);
var settingsCsb = _settingsCsb(csb);
return(Settings.GetOrAdd(settingsCsb.ConnectionString, key =>
{
string serverVersion;
using (var schemalessConnection = new OracleConnection(csb.ConnectionString))
{
schemalessConnection.Open();
serverVersion = schemalessConnection.ServerVersion;
}
var version = new OracleVersion(serverVersion);
return new OracleConnectionSettings(settingsCsb, version);
}));
}
static void Main(string[] args)
{
// create connection
OracleConnection con = new OracleConnection();
// create connection string using builder
OracleConnectionStringBuilder ocsb = new OracleConnectionStringBuilder();
ocsb.Password = "******";
ocsb.UserID = "john";
ocsb.DataSource = "database.url:port/databasename";
// connect
con.ConnectionString = ocsb.ConnectionString;
con.Open();
Console.WriteLine("Connection established (" + con.ServerVersion + ")");
}
static void Main()
{
OracleConnectionStringBuilder builder =
new OracleConnectionStringBuilder();
builder["Data Source"] = "localhost";
builder["integrated security"] = true;
builder["Unicode"] = true;
// Overwrite the existing value for the Data Source value.
builder["Data Source"] = "NewOracleDemo";
Console.WriteLine(builder.ConnectionString);
Console.WriteLine();
Console.WriteLine("Press Enter to continue.");
Console.ReadLine();
}
static void Main()
{
OracleConnectionStringBuilder builder =
new OracleConnectionStringBuilder(
"Server=OracleDemo;Integrated Security=True");
// Display the connection string, which should now
// contains the "Data Source" key, as opposed to the
// supplied "Server".
Console.WriteLine(builder.ConnectionString);
// Retrieve the DataSource property.
Console.WriteLine("DataSource = " + builder.DataSource);
Console.WriteLine("Press any key to continue.");
Console.ReadLine();
}
private static void InitConnection()
{
// create connection
connection = new OracleConnection();
// fill connection params
OracleConnectionStringBuilder ocsb = new OracleConnectionStringBuilder();
ocsb.Password = "******";
ocsb.UserID = "c##test";
ocsb.DataSource = "SampleDataSource";
// establishing connection
connection.ConnectionString = ocsb.ConnectionString;
connection.Open();
Console.WriteLine("Connection established (" + connection.ServerVersion + ")");
}
private void SaveDataBaseTab(Configuration config)
{
OracleConnectionStringBuilder csb = new OracleConnectionStringBuilder();
csb.DataSource = tbDataSource.Text;
csb.UserID = tbUserId.Text;
csb.Password = tbPassword.Text;
if (config.ConnectionStrings.ConnectionStrings["Cron"] == null)
{
config.ConnectionStrings.ConnectionStrings.Add(new ConnectionStringSettings("Cron", csb.ConnectionString));
}
else
{
config.ConnectionStrings.ConnectionStrings["Cron"].ConnectionString = csb.ConnectionString;
}
}
static void Main()
{
OracleConnectionStringBuilder builder = new OracleConnectionStringBuilder();
builder.ConnectionString = GetConnectionString();
// Call TryGetValue method for multiple
// key names. Note that these keys are converted
// to well-known synonynms for data retrieval.
DisplayValue(builder, "Data Source");
DisplayValue(builder, "Trusted_Connection");
DisplayValue(builder, "InvalidKey");
DisplayValue(builder, null);
Console.WriteLine("Press any key to continue.");
Console.ReadLine();
}
/// <summary>
/// Overrides the native Didspose method
/// </summary>
/// <param name="disposing">True or False</param>
protected virtual void Dispose(bool disposing)
{
if (!disposed)
{
if (disposing)
{
// Clear all property values that maybe have been set
// when the class was instantiated
Clear();
connectionString.Clear();
connectionString = null;
}
// Indicate that the instance has been disposed.
disposed = true;
}
}
public virtual IOracleConnection CreateMasterConnection()
{
var connectionStringBuilder
= new OracleConnectionStringBuilder(ConnectionString)
{
UserID = EFPDBAdminUser,
Password = EFPDBAdminUser
};
var contextOptions = new DbContextOptionsBuilder()
.UseOracle(
connectionStringBuilder.ConnectionString,
b => b.CommandTimeout(CommandTimeout ?? DefaultMasterConnectionCommandTimeout))
.Options;
return(new OracleRelationalConnection(Dependencies.With(contextOptions)));
}
public DBConnection(Database database)
{
string url = database.connectionString;
string username = database.username;
string password = database.password;
this.type = database.type;
SecureString secureStr = null;
if (username != null && password != null)
{
secureStr = new SecureString();
for (int i = 0; i < password.Length; i++)
{
secureStr.AppendChar(password[i]);
}
secureStr.MakeReadOnly();
}
switch (this.type)
{
case "ORACLE":
OracleConnectionStringBuilder ocsb = new OracleConnectionStringBuilder
{
DataSource = url
};
this.conOracle = (secureStr == null) ? new OracleConnection(ocsb.ConnectionString) : new OracleConnection(ocsb.ConnectionString, new OracleCredential(username, secureStr));
this.conOracle.Open();
break;
case "OLEDB":
this.conOleDb = new OleDbConnection(url);
break;
case "ODBC":
this.conOdbc = new OdbcConnection(url);
break;
default:
this.con = (secureStr == null) ? new SqlConnection(url) : new SqlConnection(url, new SqlCredential(username, secureStr));
this.con.Open();
break;
}
}
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
var builder = new OracleConnectionStringBuilder(Database.Connection.ConnectionString);
modelBuilder.HasDefaultSchema(builder.UserID);
modelBuilder.Properties().Configure(c => c.HasColumnName(c.ClrPropertyInfo.Name.ToUpper()));
modelBuilder.Entity <Document>().Property(x => x.Number).HasColumnName("Number");
modelBuilder.Entity <Document>().Property(x => x.Comment).HasColumnName("Comment");
modelBuilder.Entity <Employee>()
.HasMany(e => e.Documents)
.WithRequired(e => e.Author)
.HasForeignKey(e => e.AuthorId)
.WillCascadeOnDelete(false);
modelBuilder.Entity <Employee>()
.HasMany(e => e.Documents1)
.WithOptional(e => e.Manager)
.HasForeignKey(e => e.ManagerId);
modelBuilder.Entity <Employee>()
.HasMany(e => e.EmployeeRoles)
.WithRequired(r => r.Employee)
.HasForeignKey(r => r.EmployeeId);
modelBuilder.Entity <Role>()
.HasMany(e => e.EmployeeRoles)
.WithRequired(r => r.Role)
.HasForeignKey(r => r.RoleId);
modelBuilder.Entity <StructDivision>()
.HasMany(e => e.StructDivision1)
.WithOptional(e => e.StructDivision2)
.HasForeignKey(e => e.ParentId)
.WillCascadeOnDelete();
modelBuilder.Entity <EmployeeRole>()
.HasKey(x => new { x.RoleId, x.EmployeeId })
.HasRequired(x => x.Role).WithMany(x => x.EmployeeRoles)
;
}
public static IDbConnection GetConnection()
{
if (instance == null || instance.State == System.Data.ConnectionState.Closed)
{
OracleConnectionStringBuilder ocsb = new OracleConnectionStringBuilder();
ocsb.DataSource = "//localhost:1521/xe";
ocsb.UserID = "blagoje";
ocsb.Password = "******";
ocsb.Pooling = true;
ocsb.MinPoolSize = 1;
ocsb.MaxPoolSize = 10;
ocsb.IncrPoolSize = 3;
ocsb.ConnectionLifeTime = 5;
ocsb.ConnectionTimeout = 30;
instance = new OracleConnection(ocsb.ConnectionString);
}
return(instance);
}
public static object NewConnection(IList args)
{
object connStr = args[0];
object nPoolSize = args[1];
var cs = Convert.ToString(connStr);
var parts = cs.Split('/', '\\', '@', ':');
if (parts.Length != 5)
{
new ArgumentException("Ora.NewConnection: connStr must be in format 'username/password@host:port/sid' instead of '" + cs + "'");
}
var username = parts[0];
var password = parts[1];
var host = parts[2];
var port = parts[3];
var sid = parts[4];
var ocsb = new OracleConnectionStringBuilder();
ocsb.DataSource = $"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST={host})(PORT={port}))(CONNECT_DATA=(SERVICE_NAME={sid})))";
ocsb.UserID = username;
ocsb.Password = password;
ocsb.Pooling = false;
ocsb.LoadBalancing = false;
ocsb.HAEvents = false;
string[] initCmds;
if (args.Count > 2)
{
var lst = args[2] as IList;
if (lst == null)
{
initCmds = new string[] { Convert.ToString(args[2]) }
}
;
else
{
initCmds = lst.Cast <object>().Select(x => Convert.ToString(x)).ToArray();
}
}
else
{
initCmds = new string[0];
}
return(new DbConnPool(DbmsSpecificOracle.Instance, Convert.ToInt32(nPoolSize), ocsb.ConnectionString, TimeSpan.FromSeconds(10), initCmds));
}
/// <summary>
/// Build connection string
/// </summary>
/// <param name="oracleProvider"></param>
/// <returns></returns>
public string BuildConnectionString(string dataSource, string user, string pass)
{
try
{
string conString = "Data Source = " + dataSource;
OracleConnectionStringBuilder oracleConnectionStringBuilder = new OracleConnectionStringBuilder();
oracleConnectionStringBuilder.ConnectionString = conString;
oracleConnectionStringBuilder.UserID = user;
oracleConnectionStringBuilder.Password = pass;
return(oracleConnectionStringBuilder.ToString());
}
catch (Exception e)
{
Logger.ShowMsg(e.Message);
Logger.Logging(e.Message, DateTime.Now.ToString());
return(null);
}
}
public static OracleConnection getConnection()
{
if (oracleConnection1 == null)
{
OracleConnectionStringBuilder myCStringB = new OracleConnectionStringBuilder();
//myCStringB.UserID = "pirian";//put in username
//myCStringB.Password = "******";//put in password
//myCStringB.DataSource = "labdbwin";//use this for connecting to
myCStringB.UserID = "system"; //put in username
myCStringB.Password = "******"; //put in password
myCStringB.DataSource = "XE"; //use this for connecting to
oracleConnection1 = new OracleConnection(myCStringB.ConnectionString);
}
return(oracleConnection1);
}
public static string CreateOracleConnectingString(DbConnectionStringModel model)
{
if (!model.Port.HasValue)
{
model.Port = 1521;
}
var builder = new OracleConnectionStringBuilder();
builder.DataSource = $"{model.Server}/{model.ServerName}:{model.Port}";
builder.UserID = model.UserId;
builder.Password = model.Pwd;
//builder.ConnectionTimeout = 15;
//builder.Pooling = true;
//builder.MinPoolSize = 1;
//builder.MaxPoolSize = 20;
return(builder.ConnectionString);
}
private static string CreateConnectionString()
{
var conBuilder = new OracleConnectionStringBuilder
{
Server = _dbname,
UserId = _username,
Password = _password,
Unicode = _isUnicode
};
var builder = new EntityConnectionStringBuilder
{
Provider = "Devart.Data.Oracle",
ProviderConnectionString = conBuilder.ToString(),
Metadata = "res://*/Data.MzModel.csdl|res://*/Data.MzModel.ssdl|res://*/Data.MzModel.msl"
};
return(builder.ToString());
}
//private string _infoGettingQuery;
public OracleAccessor(string dataSource, string userId, string password, string tablespace = null)
{
_loader = new StringLoader( );
_avaliableTables = null;
_avaliableTypes = null;
_userId = userId;
_tablespace = tablespace;
var builder = new OracleConnectionStringBuilder( );
builder.DataSource = dataSource;
builder.Password = password;
builder.UserID = userId;
builder.ValidateConnection = true;
_connection = new OracleConnection(builder.ToString( ));
_connection.Open( );
}
protected override DbConnectionStringBuilder GetConnectionStringBuilderImpl(string server, string database, string username, string password)
{
var toReturn = new OracleConnectionStringBuilder()
{
DataSource = server
};
if (string.IsNullOrWhiteSpace(username))
{
toReturn.UserID = "/";
}
else
{
toReturn.UserID = username;
toReturn.Password = password;
}
return(toReturn);
}
public void Consulta()
{
OracleConnection con = new OracleConnection();
OracleConnectionStringBuilder ocsb = new OracleConnectionStringBuilder();
ocsb.Password = "******";
ocsb.UserID = "sgcred";
ocsb.DataSource = db;
con.ConnectionString = ocsb.ConnectionString;
string queryString =
"select sol.id from SOLICITACOES_WEB sol, USUARIOS_WEB usu where sol.USR_ID = usu.id and cpf =:cpf";
OracleCommand command = con.CreateCommand();
command.CommandText = queryString;
command.Parameters.Add(new OracleParameter("cpf", CpfFixo));
try
{
con.Open();
Console.WriteLine("Connection established (" + con.ServerVersion + ")");
OracleDataReader reader = command.ExecuteReader();
if (reader.Read())
{
id = reader[0].ToString();
Console.WriteLine(id);
UpdatePDC(id);
}
reader.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
con.Dispose();
}
public string ObtenerConexion()
{
#region Conexion
var oracnsBuilder = new OracleConnectionStringBuilder();
var connectionString = string.Empty;
var appSettings = ConfigurationManager.AppSettings;
oracnsBuilder.UserID = appSettings["SICEPUSUARIO"].ToUpper();
oracnsBuilder.Password = appSettings["SICEPPASS"].ToUpper();
oracnsBuilder.PersistSecurityInfo = false;
oracnsBuilder.Pooling = false;
oracnsBuilder.DataSource = appSettings["SICEPNODO"].ToUpper();
connectionString = oracnsBuilder.ToString();
return(connectionString);
#endregion
}
static void Main()
{
OracleConnectionStringBuilder builder =
new OracleConnectionStringBuilder();
builder.DataSource = "OracleSample";
builder.IntegratedSecurity = true;
// Loop through the collection of keys, displaying
// the key and value for each item.
foreach (string key in builder.Keys)
{
Console.WriteLine("{0}={1}", key, builder[key]);
}
Console.WriteLine();
Console.WriteLine("Press Enter to continue.");
Console.ReadLine();
}
protected bool Connect()
{
try
{
var cstr = new OracleConnectionStringBuilder();
cstr.Server = loginData.Server;
cstr.UserId = loginData.Login;
cstr.Password = loginData.Password;
oracleConnection = new OracleConnection(cstr.ConnectionString);
oracleConnection.Open();
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
conErrorMsg = ex.Message;
return(false);
}
return(true);
}
public static DbContextOptionsBuilder UseOracle(
[NotNull] this DbContextOptionsBuilder optionsBuilder,
[NotNull] DbConnection connection,
[CanBeNull] Action <OracleDbContextOptionsBuilder> OracleOptionsAction = null)
{
Check.NotNull(optionsBuilder, nameof(optionsBuilder));
Check.NotNull(connection, nameof(connection));
var csb = new OracleConnectionStringBuilder(connection.ConnectionString);
connection.ConnectionString = csb.ConnectionString;
var extension = GetOrCreateExtension(optionsBuilder).WithConnection(connection);
((IDbContextOptionsBuilderInfrastructure)optionsBuilder).AddOrUpdateExtension(extension);
ConfigureWarnings(optionsBuilder);
OracleOptionsAction?.Invoke(new OracleDbContextOptionsBuilder(optionsBuilder));
return(optionsBuilder);
}
private bool disposed = false; //used for the Dispose method
#endregion Fields
#region Constructors
/// <summary>
/// Main constructor
/// </summary>
/// <param name="dataSource">Server name</param>
/// <param name="initialCatalog">Data base name</param>
/// <param name="userId">User Id</param>
/// <param name="passWord">Password</param>
/// <param name="persistSecurityInfo">Whether to keep password in memory or not</param>
/// <param name="integratedSecurity">Use Windows Authentication</param>
/// <param name="connectionTimeOut">Time in seconds before a connection timeout occurs</param>
public DBConnectorOracle(string dataSource, string initialCatalog, string userId, string passWord,
bool persistSecurityInfo, bool integratedSecurity, int connectionTimeOut)
{
connectionString = new OracleConnectionStringBuilder();
connectionString.DataSource = dataSource;
if (integratedSecurity)
{
connectionString.UserID = "/";
//connectionString.Password = "";
}
else
{
connectionString.UserID = userId;
connectionString.Password = passWord;
}
connectionString.PersistSecurityInfo = persistSecurityInfo;
connectionString.ConnectionTimeout = connectionTimeOut;
Parameters = new List<OracleParameter>();
ErrorList = new List<Error>();
}
public static OracleConnection Build(bool disableMetadataPooling)
{
var connection = Environment.GetEnvironmentVariable("OracleConnectionString");
if (string.IsNullOrWhiteSpace(connection))
{
throw new Exception("OracleConnectionString environment variable is empty");
}
if (disableMetadataPooling)
{
var builder = new OracleConnectionStringBuilder(connection)
{
MetadataPooling = false
};
connection = builder.ConnectionString;
}
return(new OracleConnection(connection));
}
private IEnumerable <MigrationCommand> CreateDropCommands()
{
var userName = new OracleConnectionStringBuilder(_connection.DbConnection.ConnectionString).UserID;
if (string.IsNullOrEmpty(userName))
{
throw new InvalidOperationException(OracleStrings.NoUserId);
}
var operations = new MigrationOperation[]
{
new OracleDropUserOperation {
UserName = userName
}
};
var masterCommands = Dependencies.MigrationsSqlGenerator.Generate(operations);
return(masterCommands);
}
public void ConnectionStringBuilding() {
var builder = new OracleConnectionStringBuilder
{
Direct = true,
Server = "127.0.0.1",
Port = 1521,
Sid = "xe",
UserId = "NSoft",
Password = "******",
MaxPoolSize = 150,
ConnectionTimeout = 30
};
var connStr = builder.ConnectionString;
Assert.IsNotEmpty(connStr);
Assert.IsTrue(connStr.Contains("Max Pool Size"));
Assert.IsTrue(connStr.Contains("User Id=NSoft"));
Console.WriteLine("ConnectionString=[{0}]", connStr);
}
public IDatabaseMeta ReadMetaInfo()
{
AbstractDatabaseMeta meta = new DatabaseMeta(new TypeMap());
var b = new OracleConnectionStringBuilder(_exe.Connection.ConnectionString);
var dtTable = _exe.ExecuteQuery(string.Format("SELECT * FROM ALL_TABLES WHERE OWNER = '{0}'", b.UserID.ToUpper()));
foreach (DataRow dr in dtTable.Rows)
{
string name = (string)dr["TABLE_NAME"];
meta.AddTable(name);
}
DataTable dtColumn = _exe.ExecuteQuery(string.Format("SELECT * FROM ALL_TAB_COLUMNS WHERE OWNER = '{0}'", b.UserID.ToUpper()));
string sqlPK = "SELECT * FROM user_cons_columns WHERE constraint_name IN (SELECT constraint_name FROM user_constraints WHERE Constraint_type='P') AND owner='{0}'";
DataTable dtPK = _exe.ExecuteQuery(string.Format(sqlPK, b.UserID.ToUpper()));
foreach (DataRow dr in dtColumn.Rows)
{
string tblName = (string)dr["TABLE_NAME"];
string colName = (string)dr["COLUMN_NAME"];
bool nullable = (string)dr["NULLABLE"] != "N";
string dbType = (string)dr["DATA_TYPE"];
bool isPK = false;
foreach (DataRow row in dtPK.Rows)
{
if ((string)row["COLUMN_NAME"] == colName)
{
isPK = true;
break;
}
}
meta.AddColumn(tblName, colName, dbType, isPK, nullable, 0);//Extend: length
}
meta.Build();
return meta;
}
public void SetUpFixture()
{
_connectionStringBuilder = new OracleConnectionStringBuilder();
_connectionStringBuilder.ConnectionString = "Data Source = (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))); User Id = system; Password = password";
_connection = _connectionStringBuilder.Open();
}
public DashboardRepository(OracleConnectionStringBuilder db)
{
this.db = db;
}
/// <summary>
/// Overrides the native Didspose method
/// </summary>
/// <param name="disposing">True or False</param>
protected virtual void Dispose(bool disposing)
{
if (!disposed)
{
if (disposing)
{
// Clear all property values that maybe have been set
// when the class was instantiated
Clear();
connectionString.Clear();
connectionString = null;
}
// Indicate that the instance has been disposed.
disposed = true;
}
}
/// <summary>
/// Main constructor
/// </summary>
/// <param name="connString">A connection string to the database</param>
public DBConnectorOracle(string connString)
{
connectionString = new OracleConnectionStringBuilder(connString);
Parameters = new List<OracleParameter>();
ErrorList = new List<Error>();
}
private static bool TryConnect(string username, string password, string database)
{
OracleConnectionStringBuilder conStr = new OracleConnectionStringBuilder()
{
UserID = username,
Password = password,
DataSource = database
};
using (var con = new OracleConnection(conStr.ToString()))
{
try
{
con.Open();
}
catch (OracleException e)
{
Console.WriteLine("{0} was caught\n HResult={1}\n Source={2}\n ErrorCode={3}\n DataSource={4}\n Number={5}\n Procedure={6}\n StackTrace={7}\n InnerException: {8}",
e.GetType(),
e.HResult,
e.Source ?? "",
e.ErrorCode,
e.DataSource ?? "",
e.Number,
e.Procedure ?? "",
e.StackTrace,
e.InnerException
);
return false;
}
var si = con.GetSessionInfo();
si.Language = "POLISH";
con.SetSessionInfo(si);
try
{
return true;
}
finally
{
con.Close();
}
}
}
public SystemStatusRepository(OracleConnectionStringBuilder db)
{
this.db = db;
}
static void Main(string[] args)
{
if (args.Length != 3)
{
Console.WriteLine("Usage: program.exe <username> <password> <db>");
Environment.Exit(1);
}
string username = args[0];
string password = args[1];
string database = args[2];
OracleConnectionStringBuilder conStr = new OracleConnectionStringBuilder()
{
UserID = username,
Password = password,
DataSource = database
};
using (var con = new OracleConnection(conStr.ToString()))
{
con.Open();
var si = con.GetSessionInfo();
si.Language = "POLISH";
con.SetSessionInfo(si);
try
{
bool exists = UserExists(ProblematicUsername, con);
Console.WriteLine("User exists? = {0}", exists);
if (!exists)
{
CreateUser(ProblematicUsername, ProblematicUserPass, con);
}
GrantConnect(ProblematicUsername, con);
PrintDebug(con);
}
finally
{
con.Close();
}
}
if (TryConnect(ProblematicUsername, ProblematicUserPass, database))
{
Console.WriteLine("OK, connected!");
}
using (var con = new OracleConnection(conStr.ToString()))
{
con.Open();
var si = con.GetSessionInfo();
si.Language = "POLISH";
con.SetSessionInfo(si);
DropUser(ProblematicUsername, con);
}
/*Console.WriteLine("Press enter...");
Console.ReadLine();*/
}
public void TestReliableConnection()
{
int retries = 0;
var retryStrategy = new RetryStrategy();
retryStrategy.MaxRetryCount = 1;
retryStrategy.Retrying += (sender, re) => { Console.WriteLine("Retrying. Attempt {0}", re.Attempt); retries++; };
try
{
var builder = new OracleConnectionStringBuilder(_connectionStringBuilder.ConnectionString);
builder.DataSource = "testserver:9999";
using (var reliable = new ReliableConnection<OracleConnection>(builder.ConnectionString, retryStrategy))
{
reliable.Open();
}
}
catch
{
}
Assert.AreEqual(1, retries);
}
public InstrumentAccessHistoryRepository(OracleConnectionStringBuilder db)
{
this.db = db;
}
public EventRepository(OracleConnectionStringBuilder db)
{
this.db = db;
}
/// <summary>
/// Read <c>connectStringName</c> and <c>applicationName</c>
/// </summary>
/// <param name="name"></param>
/// <param name="config"></param>
public override void Initialize(string name, NameValueCollection config)
{
foreach (string key in config)
{
switch (key)
{
// The oracle service to connect to
case "connectStringName":
var str = config[key];
var connectionString = ConfigurationManager.ConnectionStrings[str].ConnectionString;
if (string.IsNullOrWhiteSpace(connectionString))
{
str = string.Format("{0} is not a valid connection string name", str);
throw new ArgumentNullException("connectStringName", str);
}
_connectionStringBuilder = new OracleConnectionStringBuilder(connectionString);
break;
// Name of the application using this provider. This value is not used.
case "applicationName":
this.ApplicationName = config[key];
break;
}
}
if (_connectionStringBuilder == null)
{
// ReSharper disable NotResolvedInText
throw new ArgumentNullException("connectStringName", "We need to connect to an oracle database to retrieve roles for users");
// ReSharper restore NotResolvedInText
}
base.Initialize(name, config);
}
public PerformanceDetailsRepository(OracleConnectionStringBuilder db)
{
this.db = db;
}
private void SafeExecuteCommand(Action<OracleCommand> action)
{
using (var connection = new OracleConnection(_connectionString))
{
OracleTransaction transaction = null;
OracleCommand com;
try
{
connection.Open();
OracleSqlUtility.SetSqlUserInfo(connection, _userInfo);
transaction = connection.BeginTransaction();
com = connection.CreateCommand();
com.CommandTimeout = SqlUtility.SqlCommandTimeout;
com.Transaction = transaction;
}
catch (OracleException ex)
{
if (transaction != null)
transaction.Rollback();
var csb = new OracleConnectionStringBuilder(_connectionString);
string msg = string.Format(CultureInfo.InvariantCulture, "Could not connect to data source '{0}', userID '{1}'.", csb.DataSource, csb.UserID);
_logger.Error(msg);
_logger.Error(ex.ToString());
throw new FrameworkException(msg, ex);
}
try
{
var setNationalLanguage = OracleSqlUtility.SetNationalLanguageQuery();
if (!string.IsNullOrEmpty(setNationalLanguage))
{
_logger.Trace("Setting national language: {0}", SqlUtility.NationalLanguage);
com.CommandText = setNationalLanguage;
com.ExecuteNonQuery();
}
action(com);
transaction.Commit();
}
catch (OracleException ex)
{
if (com != null && !string.IsNullOrWhiteSpace(com.CommandText))
_logger.Error("Unable to execute SQL query:\r\n" + com.CommandText);
string msg = "OracleException has occurred:\r\n" + ReportSqlErrors(ex);
if (ex.Number == 911)
msg += "\r\nCheck that you are not using ';' at the end of the command's SQL query.";
_logger.Error(msg);
_logger.Error(ex.ToString());
throw new FrameworkException(msg, ex);
}
finally
{
TryRollback(transaction);
}
}
}
public InstrumentInfoRepository(OracleConnectionStringBuilder db)
{
this.db = db;
}
/// <summary>
/// Returns true if we are able to successfully connect to oracle using the supplied username and password.
/// </summary>
/// <param name="username"></param>
/// <param name="password"></param>
/// <exception cref="System.Web.Security.MembershipPasswordException">Password has expired and needs to be changed before login can be allowed</exception>
/// <returns></returns>
public override bool ValidateUser(string username, string password)
{
var builder = new OracleConnectionStringBuilder(_connectionString)
{
UserID = username,
Password = password,
Pooling = false,
ProxyUserId = string.Empty,
ProxyPassword = string.Empty
};
OracleDatastore db = null;
try
{
db = new OracleDatastore(HttpContext.Current.Trace);
db.CreateConnection(builder.ConnectionString, string.Empty);
db.Connection.Open();
return true;
}
catch (OracleException ex)
{
// Connection could not be opened
Trace.TraceWarning(ex.Message);
switch (ex.Number)
{
case 1017:
// Invalid user name password
Trace.TraceWarning("Invalid password specified for user {0}", username);
return false;
case 28001:
// Password expired
throw new MembershipPasswordException("Password has expired. Please change your password and try again.", ex);
default:
throw;
}
}
finally
{
// For clearing the cached roles of the user.
OracleRoleProvider orp = Roles.Providers.OfType<OracleRoleProvider>().SingleOrDefault();
if (orp != null)
{
orp.ClearRoleCache(username);
}
if (db != null)
{
db.Dispose();
}
}
}
public SearchRepository(OracleConnectionStringBuilder db)
{
this.db = db;
this.queryBuilder = new OracleSearchQueryBuilder();
}
/// <summary>
/// The password change will succeed only if the old password is valid.
/// </summary>
/// <param name="username"></param>
/// <param name="oldPassword"></param>
/// <param name="newPassword"></param>
/// <returns>true if password successfully changed. false if the old password is invalid</returns>
/// <remarks>
/// Any data base exception encountered will be propagated to the caller.
/// Sharad 15 Feb 2012: Supported voluntary changes of passwords. Earlier only expired passwords could be changed.
/// Sharad 21 Feb 2012: Raising ValidatingPassword event
/// </remarks>
public override bool ChangePassword(string username, string oldPassword, string newPassword)
{
if (string.IsNullOrWhiteSpace(username))
{
throw new ArgumentNullException("username");
}
if (string.IsNullOrWhiteSpace(oldPassword))
{
throw new ArgumentNullException("oldPassword");
}
if (string.IsNullOrWhiteSpace(newPassword))
{
throw new ArgumentNullException("newPassword");
}
var e = new ValidatePasswordEventArgs(username, newPassword, true);
OnValidatingPassword(e);
if (e.Cancel)
{
// App decided to cancel user creation
return false;
}
var builder = new OracleConnectionStringBuilder(_connectionString)
{
UserID = username,
Password = oldPassword,
Pooling = false,
ProxyUserId = string.Empty,
ProxyPassword = string.Empty
};
// Try to login as passed user with old password to ensure that the old password is valid
using (var db = new OracleDatastore(HttpContext.Current.Trace))
{
var msg = string.Format("Opening connection to {0} for user {1}",
builder.DataSource, builder.UserID);
Trace.WriteLine(msg, "OracleMembershipProvider");
db.CreateConnection(builder.ConnectionString, builder.UserID);
Trace.WriteLine(msg, "Opening connection with old password");
try
{
db.Connection.Open();
}
catch (OracleException ex)
{
switch (ex.Number)
{
case 1017:
// Invalid user name password
Trace.TraceWarning("Invalid password specified for user {0}", username);
return false;
case 28001:
// If we are using ODP.NET, we can change the password now
// This will only work if the user's password has expired
Trace.WriteLine(msg, "Password expired error oracle exception encountered");
db.Connection.OpenWithNewPassword(newPassword);
return true;
default:
throw;
}
}
// If we get here, the old password was valid. Now we will change the password
//REPLACE is used to remove exception ORA-28221
Trace.WriteLine(msg, "Executing ALTER USER with new password");
var query = string.Format("ALTER USER {0} IDENTIFIED BY \"{1}\" REPLACE \"{2}\"", username, newPassword, oldPassword);
db.ExecuteNonQuery(query, null);
}
return true;
}
