Oracle ODBC

项目中有一个需求从Oracle中取数据然后把数据插入到MS-SQL里呀，而且要求用ODBC方式来取数据

在测试机器中引有System.Data.Odbc 命名空间，用类似的OdbcConnection， OdbcCommand，OdbcDataReader等之类的类操作Oracle，在测试机的时，代码是可以运行的，机器的环境是--操作系统：Windows server 2003，32位机器，Oracle：版本是 10g

把代码烤到另一台开发环境--操作系统： Windows  Server 2008，64位机器，Oracle：版本是10g 客户端时运行的时候，在执行ExecuteReader方法时会报错误，其实执行OdbcDataAdapter.Fill(DataTable)时也会类似的错误的，其错误信息为：算术运算溢出！

后来通过改变其读取的方式，再操作成功！不再用OdbConnmand之类的类，引入System.Data.OracleClient命名空间。

在通过Odbc代码连接Oracle时，需建立一个Odbc dsn 连接

具体通过：程序--->管理工具--->数据源：

引进System.Data.OracleClient，用Oracle本身的类库,诸如OracleCommand，具体代码如下：

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OracleClient;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data.Odbc;

namespace FAOracletoSQLApp
{
    class Program
    {
        static void Main(string[] args)
        {

            //string strOracleConn = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.120)

(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=ORACLE)));User Id=gan;Password=gan;";
           
            string strOracleConn = "DSN=ORACLE;Uid=GAN;Pwd=GAN;";

            //string strSQLconn = "Data Source=192.168.0.121;Initial Catalog=WSS_Content;Persist Security Info=True;User 

ID=sa;Password=sa";//测试
            string strSQLconn = "Provider=SQLOLEDB;Server=192.168.0.121;Database=WSS_Content;uid=sa;pwd=sa";//测试

 
            string strDeleteCommand = "delete from test ";//测试
            
            string strInsertCommand = "insert into test(UniqueID,PMail) values('{0}','{1}')";//测试
           
 
            string strOracleSelectCommand = "select EMPLOYEE_CODE as UniqueID,CONTACT_ADDRESS as PMail from v_k2";//测试
            
            

            SqlConnection sqlConn = null;
            SqlTransaction myTrans = null;
            OracleDataReader dr = null;
            SqlCommand sqlCmd = null;
            //OdbcDataAdapter da = null;
            long rownum=0;
            try
            {
                DataTable dt = new DataTable();
                //dt.Columns.Add(new DataColumn("UniqueID",typeof(string)));
                //dt.Columns.Add(new DataColumn("PMail",typeof(string)));

                sqlConn = new SqlConnection(strSQLconn);

                Console.WriteLine("Open the SQL Connection.......");
                sqlConn.Open();

                Console.WriteLine("Begin SQL Transaction.......");
                myTrans = sqlConn.BeginTransaction();

                Console.WriteLine("New SQL Command.......");
                sqlCmd = new SqlCommand();
                sqlCmd.Connection = sqlConn;
                sqlCmd.Transaction = myTrans;

                Console.WriteLine("Delete Data.......");
                sqlCmd.CommandText = strDeleteCommand;
                sqlCmd.ExecuteNonQuery();//执行删除命令

                Console.WriteLine("New Oracle ODBC Connection......");
                using (OracleConnection conn = new OracleConnection(strOracleConn))
                {
                    Console.WriteLine("Open the Oracle Connection.......");
                    conn.Open();

                    Console.WriteLine("Create Oracle Command.........");
                    OracleCommand cmd = new OracleCommand(strOracleSelectCommand, conn);
                    Console.WriteLine(cmd.CommandText);
                    //cmd.CommandType = System.Data.CommandType.Text;

                    Console.WriteLine("Create Oracle DataReader");
                    dr = cmd.ExecuteReader();
                    //Console.WriteLine("Create ODBC DataAdapter....");
                    //da = new OdbcDataAdapter(cmd);
                    //Console.WriteLine("Fill Data ....");
                    //da.Fill(dt);
                    //if (da!=null)
                    //{
                    //    foreach (DataRow dtRow in dt.Rows)
                    //    {
                    //        Console.WriteLine(string.Format("Unique ID:{0},PMail:{1}",ConvertObj(dr

["UniqueID"]),ConvertObj(dr["PMail"])));
                    //    }
                    //}
                    Console.WriteLine("Insert Data Begin......");
                    while (dr.Read())
                    {
                        rownum += 1;
                        //Console.Write("Rwonum"+rownum);
                        sqlCmd.CommandText = string.Format(strInsertCommand, ConvertObj(dr["UniqueID"]), ConvertObj(dr

["PMail"]));
                        sqlCmd.ExecuteNonQuery();//执行插入数据语句
                        if (rownum % 10000 == 0)
                        {
                            Console.WriteLine("Commit Data " + rownum + "....");
                            myTrans.Commit();
                            myTrans = sqlConn.BeginTransaction();
                            sqlCmd.Transaction = myTrans;
                        }
                    }
                    if(rownum>0&&rownum%10000!=0)
                    {
                       myTrans.Commit();
                    }
                    Console.WriteLine("Dome");
                }

            }
            catch (Exception ex)
            {
                Console.WriteLine("Eror:" + ex.Message);
                Console.WriteLine("Trace:"+ex.StackTrace);
                if (myTrans!=null)
                {
                    Console.WriteLine("Rolling Back.....");
                    myTrans.Rollback();
                    Console.WriteLine("Rolling Back  Dome.....");

                }

            }
            finally
            {
                if (dr!=null)
                {
                    dr.Close();
                    dr = null;
                }
                if (myTrans!=null)
                {
                    myTrans.Dispose();
                }
                if (sqlConn!=null)
                {
                    sqlConn.Close();
                    sqlConn = null;
                }
            }
            Console.ReadKey();
            
        }

        public static string ConvertObj(object obj)
        {
            return obj == null ? string.Empty : obj.ToString();
        }
    }
}

 Not:System.Data.OracleClient需安装Oracle客户端