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; } }