public void Execute(string name, Action<OracleConnection> a, bool useTransaction = false)
{
ConnectionString cs = Database.GetConnectionString(name);
OracleConnectionStringBuilder builder = new OracleConnectionStringBuilder
{
DataSource = cs.DataSource,
UserID = cs.UserID,
Password = cs.Password,
PersistSecurityInfo = cs.PersistSecurityInfo
};
using (OracleConnection conn = new OracleConnection(builder.ConnectionString))
{
conn.Open();
if (useTransaction)
{
using (OracleTransaction trans = conn.BeginTransaction())
{
a(conn);
trans.Commit();
}
}
else
a(conn);
conn.Close();
}
}
public void Execute(object parameter)
{
try
{
OracleConnectionStringBuilder builder = new OracleConnectionStringBuilder();
builder.UserID = UserID;
builder.Password = Password;
builder.DataSource = Ezconstr;
OracleConnection testConn = new OracleConnection(builder.ConnectionString);
testConn.Open();
//testConn.Close();
_connection = testConn;
NotifyPropertyChanged("Connection");
Message = "OK";
}
catch (OracleException e)
{
Message = e.Message;
}
}
/// <summary>Damの選択</summary>
private void cmbDataProvider_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
// 初期は、無効にして、ODP.NET、HiRDB選択時のみ有効にする。
this.nudNumOfBind.Enabled = false;
if (this.cmbDataProvider.SelectedItem.ToString() == Literal.DAP_SQL)
{
//sqlClient
this._dam = new DamSqlSvr();
//接続文字列のサンプルを設定する（空の場合）。
SqlConnectionStringBuilder csb = new SqlConnectionStringBuilder();
csb.DataSource = this._ip;
csb.InitialCatalog = "Northwind";
csb.UserID = this._uid;
csb.Password = this._pwd;
this.txtCnnStr.Text = csb.ConnectionString;
// 状態
((ToolStripStatusLabel)this.statBar.Items[0]).Text = this.RM_GetString("STATUS_SQL_CREATED");
}
else if (this.cmbDataProvider.SelectedItem.ToString() == Literal.DAP_OLE)
{
//OLEDB.NET
this._dam = new DamOLEDB();
//接続文字列のサンプルを設定する（空の場合）。
OleDbConnectionStringBuilder csb = new OleDbConnectionStringBuilder();
csb.Provider = "Provider";
csb.DataSource = "DataSourceName";
csb.FileName = "FileName";
this.txtCnnStr.Text = csb.ConnectionString;
// 活性
this.nudNumOfBind.Enabled = true;
// 状態
((ToolStripStatusLabel)this.statBar.Items[0]).Text = this.RM_GetString("STATUS_OLE_CREATED");
}
if (this.cmbDataProvider.SelectedItem.ToString() == Literal.DAP_ODB)
{
//ODBC.NET
this._dam = new DamODBC();
//接続文字列のサンプルを設定する（空の場合）。
OdbcConnectionStringBuilder csb = new OdbcConnectionStringBuilder();
csb.Driver = "DriverName";
csb.Dsn = "DataSourceName";
this.txtCnnStr.Text = csb.ConnectionString;
// 活性
this.nudNumOfBind.Enabled = true;
// 状態
((ToolStripStatusLabel)this.statBar.Items[0]).Text = this.RM_GetString("STATUS_ODB_CREATED");
}
if (this.cmbDataProvider.SelectedItem.ToString() == Literal.DAP_ORA)
{
//Oracle Client
this._dam = new DamOraClient();
//接続文字列のサンプルを設定する（空の場合）。
System.Data.OracleClient.OracleConnectionStringBuilder csb = new System.Data.OracleClient.OracleConnectionStringBuilder();
csb.DataSource = this._ip + "/orcl";
csb.UserID = this._uid;
csb.Password = this._pwd;
this.txtCnnStr.Text = csb.ConnectionString;
// 活性
this.nudNumOfBind.Enabled = true;
// 状態
((ToolStripStatusLabel)this.statBar.Items[0]).Text = this.RM_GetString("STATUS_ODP_CREATED");
}
if (this.cmbDataProvider.SelectedItem.ToString() == Literal.DAP_ODP)
{
//ODP.NET
this._dam = new DamOraOdp();
//接続文字列のサンプルを設定する（空の場合）。
Oracle.DataAccess.Client.OracleConnectionStringBuilder csb = new Oracle.DataAccess.Client.OracleConnectionStringBuilder();
csb.DataSource = this._ip + "/orcl";
csb.UserID = this._uid;
csb.Password = this._pwd;
this.txtCnnStr.Text = csb.ConnectionString;
// 活性
this.nudNumOfBind.Enabled = true;
// 状態
((ToolStripStatusLabel)this.statBar.Items[0]).Text = this.RM_GetString("STATUS_ODP_CREATED");
}
else if (this.cmbDataProvider.SelectedItem.ToString() == Literal.DAP_DB2)
{
//DB2.NET
this._dam = new DamDB2();
//接続文字列のサンプルを設定する（空の場合）。
DB2ConnectionStringBuilder csb = new DB2ConnectionStringBuilder();
//csb.Server = this._ip + ":50000";
csb.Database = "SAMPLE";
csb.UserID = this._uid;
csb.Password = this._pwd;
this.txtCnnStr.Text = csb.ConnectionString;
// 状態
((ToolStripStatusLabel)this.statBar.Items[0]).Text = this.RM_GetString("STATUS_DB2_CREATED");
}
//else if (this.cmbDataProvider.SelectedItem.ToString() == Literal.DAP_HiRDB)
//{
// //HiRDBデータ プロバイダ
// this._dam = new DamHiRDB();
// //接続文字列のサンプルを設定する（空の場合）。
// //HiRDBデータ プロバイダは、ConnectionStringBuilderがない。
// string csb = "";
// csb += "DataSource=C:\\Windows\\HiRDB.ini;";
// csb += "UID=" + this._uid + ";";
// csb += "PWD=" + this._pwd + ";";
// this.txtCnnStr.Text = csb;
// // 活性
// this.nudNumOfBind.Enabled = true;
// // 状態
// ((ToolStripStatusLabel)this.statBar.Items[0]).Text = Literal.STATUS_HRD_CREATED;
//}
else if (this.cmbDataProvider.SelectedItem.ToString() == Literal.DAP_MySQL)
{
//MySQL Connector/NET
this._dam = new DamMySQL();
//接続文字列のサンプルを設定する（空の場合）。
MySqlConnectionStringBuilder csb = new MySqlConnectionStringBuilder();
csb.Server = this._ip;
csb.Database = "test";
csb.UserID = this._uid;
csb.Password = this._pwd;
this.txtCnnStr.Text = csb.ConnectionString;
// 状態
((ToolStripStatusLabel)this.statBar.Items[0]).Text = this.RM_GetString("STATUS_MSL_CREATED");
}
else if (this.cmbDataProvider.SelectedItem.ToString() == Literal.DAP_PstgrS)
{
//Npgsql
this._dam = new DamPstGrS();
//接続文字列のサンプルを設定する（空の場合）。
//NpgsqlConnectionStringBuilderは、冗長な接続文字列を返すので使わない。
//NpgsqlConnectionStringBuilder csb = new NpgsqlConnectionStringBuilder();
string csb = "";
//csb.Host = this._ip;
//csb.Database = "postgres";
//csb.UserName = this._uid;
//csb.Password = this._pwd;
csb += "HOST=" + this._ip + ";";
csb += "DATABASE=postgres;";
csb += "UID=" + this._uid + ";";
csb += "PWD=" + this._pwd + ";";
this.txtCnnStr.Text = csb;
// 状態
((ToolStripStatusLabel)this.statBar.Items[0]).Text = this.RM_GetString("STATUS_PGS_CREATED");
}
else
{
//ありえない
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void ReadOracleVersion(string dsn)
{
string version, compile, oracleversion, error;
try
{
OracleConnectionStringBuilder csb = new OracleConnectionStringBuilder();
csb.DataSource = dsn;
csb.UserID = _user;
csb.Password = _password;
OracleConnection conn = new OracleConnection(csb.ConnectionString);
conn.Open();
OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT build,svcpack,compile FROM sysadm.vversion";
OracleDataReader rdr = cmd.ExecuteReader(CommandBehavior.SingleRow);
if (rdr.Read())
{
version = rdr.GetString(rdr.GetOrdinal("BUILD")) + "." + rdr.GetString(rdr.GetOrdinal("SVCPACK"));
compile = rdr.GetString(rdr.GetOrdinal("COMPILE")).ToString();
}
else
{
version = "-";
compile = "-";
}
cmd.CommandText = "SELECT MAX(v.version) version FROM sys.product_component_version v WHERE upper(v.product) LIKE '%ORACLE%'";
OracleDataReader rdr2 = cmd.ExecuteReader(CommandBehavior.SingleRow);
if (rdr2.Read())
{
oracleversion = rdr2.GetString(rdr2.GetOrdinal("VERSION"));
}
else
{
oracleversion = "-";
}
rdr.Close();
rdr.Dispose();
rdr2.Close();
rdr2.Dispose();
cmd.Dispose();
conn.Close();
conn.Dispose();
error = " On ";
Databases.Add(new VersionType(dsn, version, compile, oracleversion, string.Empty, string.Empty,string.Empty,error, string.Empty, string.Empty));
}
catch (OracleException ex)
{
var ver = from d in previousDatabases
where d._name == dsn
select d._version;
version = String.Join("", ver);
var comp = from d in previousDatabases
where d._name == dsn
select d._compile;
compile = String.Join("", comp);
var ora = from d in previousDatabases
where d._name == dsn
select d._oracleversion;
oracleversion = String.Join("", ora);
if (ex.Number == 12514)
{
error = " Off ";
}
else
{
error = ex.Message.Replace("\r\n", string.Empty);
}
Databases.Add(new VersionType(dsn, version, compile, oracleversion, string.Empty, string.Empty, string.Empty,error, string.Empty, string.Empty));
}
}
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 ViewModelConnectOracle()
{
_connStrBuilder = new OracleConnectionStringBuilder();
}
/// <summary>
/// Setup connection based on XML or default values.
/// </summary>
public void ConnectionSetup(string UserId, string Password)
{
// Read XML File //
xmlFile.ReadFile();
// Store Builder Values //
Oracle.DataAccess.Client.OracleConnectionStringBuilder builder = new Oracle.DataAccess.Client.OracleConnectionStringBuilder();
builder.UserID = UserId;
builder.Password = Password;
builder.MinPoolSize = Convert.ToInt32(xmlFile.XmlValues["MinPoolSize"]);
builder.ConnectionLifeTime = Convert.ToInt32(xmlFile.XmlValues["ConnectionLifeTime"]);
builder.ConnectionTimeout = Convert.ToInt32(xmlFile.XmlValues["ConnectionTimeout"]);
builder.IncrPoolSize = Convert.ToInt32(xmlFile.XmlValues["IncrPoolSize"]);
builder.DecrPoolSize = Convert.ToInt32(xmlFile.XmlValues["DecrPoolSize"]);
builder.MaxPoolSize = Convert.ToInt32(xmlFile.XmlValues["MaxPoolSize"]);
builder.ValidateConnection = Convert.ToBoolean(xmlFile.XmlValues["ValidateConnection"]);
builder.DataSource = string.Format("(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL={0})(HOST={1})(PORT={2})))(CONNECT_DATA=(SERVICE_NAME={3})))",
xmlFile.XmlValues["Protocol"],
xmlFile.XmlValues["Host"],
xmlFile.XmlValues["Port"],
xmlFile.XmlValues["ServiceName"]);
connectionString = builder.ConnectionString;
}
static void Main(string[] args)
{
var oracleConnectionStringBuilder = new OracleConnectionStringBuilder {
DataSource = "localhost/orcl",
UserID = "ed",
Password = "******"
};
var db = new Database(oracleConnectionStringBuilder.ConnectionString, "Oracle.DataAccess.Client");
try
{
var products = db.QueryNoParams("SELECT * FROM PRODUCTS", CommandType.Text, new ProductRowMapper());
foreach (var product in products)
{
Console.WriteLine(product);
}
}
catch (Exception e)
{
Console.WriteLine(e);
}
try
{
var products = db.Query("SELECT * FROM PRODUCTS where id = :id", CommandType.Text, "id", DbType.String, "E1", new ProductRowMapper());
Console.WriteLine("Filtered by ID = E1");
foreach (var product in products)
{
Console.WriteLine(product);
}
}
catch (Exception e)
{
Console.WriteLine(e);
}
try
{
DbParameter idParam = db.BuildInParam("id", DbType.String, "E1");
DbParameter nameParam = db.BuildInParam("name", DbType.String, "Rear Lights");
var dbParameters = new[] {idParam, nameParam};
var products = db.Query<Product>("SELECT * FROM PRODUCTS where id = :id or name = :name", CommandType.Text, dbParameters, new ProductRowMapper());
Console.WriteLine("Filtered by ID = E1 or Name = Rear Lights");
foreach (var product in products)
{
Console.WriteLine(product);
}
}
catch (Exception e)
{
Console.WriteLine(e);
}
var oracleConnection = new OracleConnection(oracleConnectionStringBuilder.ConnectionString);
try
{
Console.ForegroundColor = ConsoleColor.Green;
Console.WriteLine("Starting work with Oracle");
Console.ForegroundColor = ConsoleColor.White;
oracleConnection.Open();
var oracleCommand = oracleConnection.CreateCommand();
oracleCommand.CommandText = "SELECT * FROM PRODUCTS";
var oracleDataReader = oracleCommand.ExecuteReader();
if (!oracleDataReader.HasRows)
return;
while (oracleDataReader.Read())
{
var id = oracleDataReader.GetString(oracleDataReader.GetOrdinal("ID"));
var name = oracleDataReader.GetString(oracleDataReader.GetOrdinal("Name"));
var price = oracleDataReader.GetDecimal(oracleDataReader.GetOrdinal("Price"));
var remarks = oracleDataReader.GetString(oracleDataReader.GetOrdinal("Remarks"));
Console.WriteLine("ID: {0}, Name: {1}, Price: {2}, Remarks: {3}", id, name, price, remarks);
}
}
catch (Exception ex)
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("Oracle error: {0}", ex.ToString());
}
finally
{
oracleConnection.Close();
oracleConnection.Dispose();
Console.ForegroundColor = ConsoleColor.Green;
Console.WriteLine("Stopping work with Oracle");
}
}
/// <summary>
/// Builds a connection string.
/// </summary>
/// <param name="dataSource">The data source field of the connection string to build.</param>
/// <param name="accountName">The name of the account to use when connecting to the database server.</param>
/// <param name="password">The password for the account.</param>
/// <param name="trustedConnection">Whether to use integrated security to create a trusted connection with the
/// database server. An accountName and password are not required for a trusted connection, the Windows account
/// running the application will be used.</param>
/// <param name="loadBalancing">Whether connection should be optimized for RAC databases.</param>
/// <param name="dbaPrivilege">The database system level privilege to make the connection with. DBAPrivilege defines all valid values.
/// Use NONE for non-system connections</param>
/// <returns>The built connection string, or null if the string could not be built.</returns>
private static string BuildConnectionStringFromDataSource(string dataSource, string accountName, string password, bool trustedConnection, bool loadBalancing, string dbaPrivilege)
{
if (!string.IsNullOrWhiteSpace(dataSource) && !string.IsNullOrWhiteSpace(accountName) && !string.IsNullOrWhiteSpace(password) && !string.IsNullOrWhiteSpace(dbaPrivilege))
{
// A host name was supplied.
OracleConnectionStringBuilder builder = new OracleConnectionStringBuilder
{
// Create the connection string.
DataSource = dataSource,
// Optimize the connection for RAC load balancing if necessary.
LoadBalancing = loadBalancing
};
// Set the system privilege level of the connection, if specified.
if (dbaPrivilege != DBAPrivilege.NONE)
{
builder.DBAPrivilege = dbaPrivilege;
}
// Check whether a trusted connection should be created.
if (trustedConnection)
{
// Use the application's Windows account and integrated security to create a trusted connection.
builder.ConnectionString += "Integrated Security=SSPI;";
}
else
{
// A trusted connection is not required. Use standard security.
// Check that an account name and password were supplied.
if (!string.IsNullOrWhiteSpace(accountName) && !string.IsNullOrWhiteSpace(password))
{
// An account name and password were supplied.
builder.UserID = accountName;
builder.Password = password;
}
else
{
// An account name or password was not supplied.
// Throw the appropriate exception.
return null;
}
}
return builder.ToString();
}
else
{
// All of the required values to build the string were not provided.
return null;
}
}
