(原) ODP.NET 演示如何从DataSet中获取LOB数据 附：.NET 环境下连接Oracle数据库

演示如何从DataSet中获取LOB数据。

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

namespace Sample11
{
    /**//// <summary>
    /// 演示如何从DataSet中获取LOB数据
    /// </summary>
    class Program
    {

        static void Main(string[] args)
        {
            // connect
            string constr = "User Id=scott;Password=tiger;Data Source=(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.24)(PORT = 1521))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = bjoracle.oracle10g.mynet)))";
            OracleConnection conn = Connect(constr);

            // setup the table and data
            Setup(conn);

            // Create the oracleCommand
            OracleCommand cmd = new OracleCommand("SELECT story FROM multimedia_tab");
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;
            
            // Create the OracleDataAdapter
            OracleDataAdapter da = new OracleDataAdapter(cmd);

            // Dataset
            DataSet ds = new DataSet();
            try
            {
                da.FillSchema(ds, SchemaType.Source, "Media");

                // Fill data
                da.Fill(ds, "Media");

                // Obtain LOB data
                Console.WriteLine(ds.Tables["Media"].Rows[0]["story"]);
            }
            catch (Exception ex)
            {
                Console.WriteLine("ERROR: {0}", ex.Message);
            }
            finally
            {
                // Dispose OracleCommand
                cmd.Dispose();

                // Close and Dispose OracleConnection object
                conn.Close();
                conn.Dispose();
            }

            Console.ReadLine();
        }

        /**//// <summary>
        /// 打开数据库连接
        /// </summary>
        /// <param name="connectStr"></param>
        /// <returns></returns>
        public static OracleConnection Connect(string connectStr)
        {
            OracleConnection conn = new OracleConnection(connectStr);
            try
            {
                conn.Open();
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: {0}", ex.Message);
            }
            return conn;
        }

        /**//// <summary>
        /// 创建必要的表和测试数据
        /// </summary>
        /// <param name="conn"></param>
        public static void Setup(OracleConnection conn)
        {
            StringBuilder blr;
            OracleCommand cmd = new OracleCommand("", conn);

            blr = new StringBuilder();
            blr.Append("DROP TABLE multimedia_tab");
            cmd.CommandText = blr.ToString();
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: {0}", ex.Message);
            }

            blr = new StringBuilder();
            blr.Append("CREATE TABLE multimedia_tab(thekey NUMBER(4) PRIMARY KEY,");
            blr.Append("story CLOB, sound BLOB)");
            cmd.CommandText = blr.ToString();
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: {0}", ex.Message);
            }

            blr = new StringBuilder();
            blr.Append("INSERT INTO multimedia_tab values(");
            blr.Append("1,");
            blr.Append("'This is a long story. Once upon a time',");
            blr.Append("'65645444564113418787311578783121548787878')");
            cmd.CommandText = blr.ToString();
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: {0}", ex.Message);
            }
        }
    }
}

在学习例子的过程中想到了如何利用 Oracle.DataAccess.Client.OracleConnection 连接远程服务器的服务，就像SQL Server一样指定IP，这里需要的只是将tnsnames.ora中的连接信息拷贝过来就可以了。
这里对 .NET 连接 oracle 数据库 做些总结：

1、ODBC

//New version 
Driver={Microsoft ODBC for Oracle};Server=myServerAddress;Uid=myUsername;Pwd=myPassword; 
 

//Old version 
Driver={Microsoft ODBC Driver for Oracle};ConnectString=OracleServer.world;Uid=myUsername;Pwd=myPassword; 
 

2、OLE DB, OleDbConnection (.NET)

//Standard security This connection string uses a provider from Microsoft.  
Provider=msdaora;Data Source=MyOracleDB;User Id=myUsername;Password=myPassword; 
 
//Trusted connection  
Provider=msdaora;Data Source=MyOracleDB;Persist Security Info=False;Integrated Security=Yes; 
 
//Standard Security This connection string uses a provider from Oracle.  
Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;User Id=myUsername;Password=myPassword; 
 
//Trusted Connection 
Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;OSAuthent=1; 

3、Oracle.DataAccess.Client.OracleConnection

//Standard 
Data Source=TORCL;User Id=myUsername;Password=myPassword; 
 
//Using integrated security 
Data Source=TORCL;Integrated Security=SSPI; 

//Using ODP.NET without tnsnames.ora 
Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=MyHost)(PORT=MyPort)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=MyOracleSID)));User Id=myUsername;Password=myPassword; 

4、OracleConnection, Oracle Data Provider, ODP.NET, System.Data.OracleClient.OracleConnection

// Standard 
Data Source=MyOracleDB;Integrated Security=yes; 
 
// Specifying username and password 
Data Source=MyOracleDB;User Id=myUsername;Password=myPassword;Integrated Security=no; 
 
// Omiting tnsnames.ora 
SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=MyHost)(PORT=MyPort))(CONNECT_DATA=(SERVICE_NAME=MyOracleSID)));uid=myUsername;pwd=myPassword; 
 
Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=MyHost)(PORT=MyPort))(CONNECT_DATA=(SERVICE_NAME=MyOracleSID)));User Id=myUsername;Password=myPassword; 
 
// Using Connection Pooling 
Data Source=myOracleDB;User Id=myUsername;Password=myPassword;Min Pool Size=10;Connection Lifetime=120;Connection Timeout=60;Incr Pool Size=5;Decr Pool Size=2; 

// Windows Authentication 
Data Source=myOracleDB;User Id=/; 
 
// Privileged Connection With SYSDBA privileges  
Data Source=myOracleDB;User Id=SYS;Password=SYS;DBA Privilege=SYSDBA; 

// Privileged Connection With SYSOPER privileges  
Data Source=myOracleDB;User Id=SYS;Password=SYS;DBA Privilege=SYSOPER; 
 
// Utilizing the Password Expiration functionality 
Data Source=myOracleDB;User Id=myUsername;Password=myPassword; 
oConn.OpenWithNewPassword(sTheNewPassword);  
 
// Proxy Authentication 
Data Source=myOracleDB;User Id=myUsername;Password=myPassword;Proxy User Id=pUserId;Proxy Password=pPassword; 

5、Core Labs OraDirect (.NET)

// Standard
User ID=myUsername;Password=myPassword;Host=ora;Pooling=true;Min Pool Size=0;Max Pool Size=100;Connection Lifetime=0; 

6、Data Shape

// MS Data Shape 
Provider=MSDataShape.1;Persist Security Info=False;Data Provider=MSDAORA;Data Source=orac;User Id=myUsername;Password=myPassword; 

更多的信息请参见：http://www.connectionstrings.com/