64位windows，使用.net串連oracle 9總結

不行的情況:
  1)使用Ado。net，調用system.data.oracleclient，然後串連；
  2)使用odal，但是使用原生oraname；

行的情況：

 

           public static string BuildConnStr()
        {
            //Data Source=omc;User ID=spms;Password=spms;

            //user id=hr;password=hr;data source=//sales-server:1521/sales.us.acme.com
            try
            {
                return string.Format("user id={1};password={2};data source={0}",
                                     ConfigurationManager.AppSettings["oracleName"],
                                     ConfigurationManager.AppSettings["oracleUserName"],
                                     ConfigurationManager.AppSettings["oraclePassword"]);

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return string.Empty;
        }

        public DataSet Execute()
        {
            if (string.IsNullOrEmpty(_ConnectionString)) { throw (new Exception("無傳入資料連線字串")); }

            if (string.IsNullOrEmpty(_CommandText)) { throw (new Exception("無傳入SQL語句")); }

            if (!DSAFConnectionTest()) { throw (new Exception("嘗試資料庫連接失敗")); }

            try
            {

                
                OracleDataAdapter oracleDa = new OracleDataAdapter(_CommandText, _ConnectionString);

                oracleDa.Fill(_DSAFDataSet);
                oracleDa.Dispose();
            }
            catch (OracleException DB2Ex)
            {
                MessageBox.Show(DB2Ex.Message);
                throw DB2Ex;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                throw ex;
            }

            return _DSAFDataSet;
        }

         private bool DSAFConnectionTest()
         {
             try
             {
                 Conn = new OracleConnection(); Conn.ConnectionString = _ConnectionString;
 
                 Conn.Open(); Conn.Close();
             }
             catch(Exception ex)
             {
                 MessageBox.Show(_ConnectionString);
                 MessageBox.Show(ex.Message);
                 return (false);
             }
 
             return (true);
         }

安裝odal
    Download the ODAC (xcopy version) zip file to a directory for staging ODAC products.

Unzip ODAC (xcopy version) zip file to expand the contents.

The following directories will be created:

a) instantclient_11_2 - Oracle Instant Client

b) ODP.NET20 - Oracle Data Provider for .NET 2
c) ODP.NET4 - Oracle Data Provider for .NET 4
d) ASP.NET - Oracle Providers for ASP.NET 2
e) ASP.NET4 - Oracle Providers for ASP.NET 4
f) OLEDB - Oracle Provider for OLEDB
g) OraMTS - Oracle Services for MTS

The install.bat will also be extracted to the same location as the ODAC zip file, which is used to install and configure a specific product.

Execute install.bat to install and configure ODAC products.

To install all ODAC products, execute the following:

install.bat all c:\oracle odac

Each product can be also be installed and configured individually. As an example, executing the following will install only ODP.NET 2:

install.bat odp.net2 c:\oracle odac  使用這句安裝odp.net2

where "c:\oracle", for example, is your installation directory and "odac" is the Oracle Home Name that is used for the registry keys. In this example, the registry entries for this install is created under

HKLM\Sofware\Oracle\KEY_odac. Note that ODP.NET registry entries are simply created under HKLM\Software\Oracle\ODP.NET\, and not under HKLM\Sofware\Oracle\KEY_. If multiple products are installed to the same

destination directory, make sure that the Oracle Home Name that is specified as an argument to install.bat is the same for all those products.

By default, When install.bat is executed for a given product, the product and its dependencies will be installed and configured WITHOUT a check to see if there is a newer version of the product or its

dependencies already installed.

Below is the product dependency information:

a) Oracle Data Provider for .NET 2 depends on
    - Oracle Instant Client
b) Oracle Data Provider for .NET 4 depends on
    - Oracle Instant Client
c) Oracle Providers for ASP.NET 2 depends on
    - Oracle Instant Client
    - Oracle Data Provider for .NET 2
d) Oracle Providers for ASP.NET 4 depends on
    - Oracle Instant Client
    - Oracle Data Provider for .NET 4
e) Oracle Provider for OLEDB depends on
    - Oracle Instant Client
f) Oracle Services for MTS depends on
    - Oracle Instant Client

To not have the products dependencies installed and configured, execute install.bat with 'false' as the fourth parameter.

For example, executing

install.bat asp.net2 c:\oracle odac false

will only install and configure ASP.NET 2 and not install and configure ODP.NET 2 or instant client. If the last argument is not specified, the product and its dependencies will be installed and configured.

Add the install directory and install directory's bin subdirectory to the system PATH environment variable before any other Oracle directories. Otherwise, the products that are installed may not work

properly as it will not be able to load the necessary DLLs. for example, if c:\oracle was provided as the install directory, add c:\oracle and c:\oracle\bin to the system PATH.
這個要添加

Note that install.bat sets the value of the NLS_LANG registry key at HKLM\Sofware\Oracle\KEY_ to AMERICAN_AMERICA.WE8MSWIN1252 by default. Please modify install.bat or change this value in the registry after

the install if necessary.
這個先試試，先不用改，等出現  can not access NLS 再說

If OraMTS is installed, the OracleMTSRecoveryService is created with the LocalSystem account as the logon account. The credentials can be changed using the "services" administration tool. Please read the

OraMTS readme.txt for details. Note that after the install or the configuration, the service will not be started, since it requires the install directory and its bin directory to be included in the system

PATH.
這個不用管

Application using components from ODAC can use EZ CONNECT to connect to Oracle without depending on tnsnames.ora. Otherwise, configure or copy over tnsnames.ora from another machine and set TNS_ADMIN or

ORACLE_HOME environment variable appropriately.
我使用的是EZ CONNECT方式，使用tnsname.ora沒有配通