c#向Oracle数据库中插入数据, 出现乱码问题, oracle11g(版本11.2)

数据库语言是:SIMPLIFIED CHINESE_CHINA.ZHS16GBK, 注册表machine->software->Oracle下的NLS_LANG键;

环境变量: nls_lang=SIMPLIFIED CHINESE_CHINA.ZHS16GBK; 与注册表相统一.

Oracle SQL Developer中工具->首选项->环境->编码: GBK.

连接字符串:

        //static string conStr = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=WIN-COSDJDSFC4C)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL)));User Id=scott;Password=tiger;";
        //static string conStr = "Provider=MSDAORA.1;Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=WIN-COSDJDSFC4C)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL)));User Id=scott;Password=tiger;";
        static string conStr = "provider='OraOleDb.Oracle';Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=WIN-COSDJDSFC4C)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL)));User Id=scott;Password=tiger;";

前两种乱码, 后一种正常.

第一种使用的是OracleClient方式，是微软专门针对Oracle数据库开发的

引用类库：System.Data.OracleClient.dll。 
　　命名空间：System.Data.OracleClient。
　　常用类：OracleConnection、OracleCommand、OracleDataAdapter、OracleTransaction、OracleDataReader等。
　　典型连接字符串：“data source=oratest;user id=scott;password=tiger”（注意：可不指定 provider 驱动）。

第二,三中使用的是OleDb方式，微软和Oracle公司各自提供了OleDb的驱动程序，使用方法的差别很少。

相同之处(后两种连接)
　　命名空间：System.Data.OleDb。
　　常用类：OleDbConnection、OleDbCommand、OleDbDataAdapter、OleDbTransaction、OleDbDataReader等。

不同之处
　　引用类库：微软的只需要System.Data.dll；若用Oracle的驱动，虽然也只要引入System.Data.dll，但前提是首先安装Oracle针对.Net的数据访问组件, 即ODAC。

测试代码:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;
using System.IO;
using System.Data.OracleClient;
using System.Data;
using System.Data.OleDb;


namespace ConsoleApplication3
{
    class Program
    {
        //有乱码
        //static string conStr = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=WIN-COSDJDSFC4C)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL)));User Id=scott;Password=tiger;";
        //static string conStr = "Provider=MSDAORA.1;Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=WIN-COSDJDSFC4C)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL)));User Id=scott;Password=tiger;";
        static string conStr = "provider='OraOleDb.Oracle';Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=WIN-COSDJDSFC4C)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL)));User Id=scott;Password=tiger;";
       
        static void Main(string[] args)
        {
            //OracleConnection sqlCon = new OracleConnection();
            OleDbConnection sqlCon = new OleDbConnection();
            try
            {
                sqlCon.ConnectionString = conStr;
                sqlCon.Open();
                InsertScriptOleDB(sqlCon);
                //DisplayDataOleDB(sqlCon);
            }
            catch (OracleException ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                sqlCon.Close();
            }
            Console.Read();
        }
        static void DisplayDataOleDB(OleDbConnection sqlCon)
        {
            string strSql = "select * from panquan";
            OleDbCommand comm = new OleDbCommand();
            comm.CommandType = CommandType.Text;
            comm.CommandText = strSql;
            comm.Connection = sqlCon;
            OleDbDataReader reader = comm.ExecuteReader();
            while (reader.Read())
            {
                Console.WriteLine("ID={0}, USERNAME={1}, KEY={2}, GRANTTO={3}, REALNAME={4},TEL={5}",
                    reader["ID"], reader["USERNAME"], reader["KEY"], reader["GRANTTO"], reader["REALNAME"], reader["TEL"]);
            }
        }
        static void InsertScriptOleDB(OleDbConnection sqlCon)
        {
            string strSql = GetScript("script.sql");
            string[] subSqls = strSql.Split(';');//script.sql文件中每条语句按分号分隔, 单独执行


            //strSql.Replace('\r', ' ').Replace('\n', ' ');
            for (int i = 0; i < subSqls.Length; i++)
                ExecuteSqlOleDB(sqlCon, subSqls[i]);


        }
        static void ExecuteSqlOleDB(OleDbConnection sqlconn, string Sql)
        {
            if (Sql == "\r\n") return;
            OleDbCommand Command = new OleDbCommand(Sql, sqlconn);
            Command.CommandType = CommandType.Text;
            try
            {
                if (Command.Connection.State == ConnectionState.Closed)
                {
                    Command.Connection.Open();
                }
                Command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.Write("In exception handler :" + ex.Message);
            }
            finally
            {
                //Command.Connection.Close();
                Command.Dispose();
            }
        }
        static void DisplayData(OracleConnection sqlCon)
        {
            string strSql = "select * from panquan";
            OracleCommand comm = new OracleCommand();
            comm.CommandType = CommandType.Text;
            comm.CommandText = strSql;
            comm.Connection = sqlCon;
            OracleDataReader reader = comm.ExecuteReader();
            while (reader.Read())
            {
                Console.WriteLine("ID={0}, USERNAME={1}, KEY={2}, GRANTTO={3}, REALNAME={4},TEL={5}",
                    reader["ID"], reader["USERNAME"], reader["KEY"], reader["GRANTTO"], reader["REALNAME"], reader["TEL"]);
            }
        }
        static void InsertScript(OracleConnection sqlCon)
        {
            string strSql = GetScript("script.sql");
            string[] subSqls = strSql.Split(';');


            //strSql.Replace('\r', ' ').Replace('\n', ' ');
            for (int i = 0; i < subSqls.Length; i++)
                ExecuteSql(sqlCon, subSqls[i]);


        }
        static void ExecuteSql(OracleConnection sqlconn, string Sql)
        {
            if (Sql == "\r\n") return;
            OracleCommand Command = new OracleCommand(Sql, sqlconn);
            Command.CommandType = CommandType.Text;
            try
            {
                if (Command.Connection.State == ConnectionState.Closed)
                {
                    Command.Connection.Open();
                }
                Command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.Write("In exception handler :" + ex.Message);
            }
            finally
            {
                //Command.Connection.Close();
                Command.Dispose();
            }
        }
        private static string GetScript(string name)
        {
            try
            {
                Assembly asm = Assembly.GetExecutingAssembly();
                MessageBox.Show("script name: "+asm.GetName().Name + "." + name);
                Stream str = asm.GetManifestResourceStream(asm.GetName().Name + "." + name);
                //Stream str = asm.GetManifestResourceStream(asm.Location.Substring(0,asm.Location.LastIndexOf('\\')+1) + name);
                StreamReader reader = new StreamReader(str, System.Text.Encoding.Default);
                //System.Text.StringBuilder output = new System.Text.StringBuilder();
                //string line = "";
                //while ((line = reader.ReadLine()) != null)
                //{
                //    output.Append(line + "\n");
                //}
                return reader.ReadToEnd();
            }
            catch (Exception e)
            {
                Console.WriteLine("Source: " + e.Source + "Message: " + e.Message);
                return "";
            }


        }
    }
}

script.sql:

-- Create table
create table MIAOSHU
(
  ID         NUMBER(8),
  BIANHAO    VARCHAR2(50),
  NIANYUE    DATE,
  DANWEI     VARCHAR2(50),
  BUMEN      VARCHAR2(50),
  XUHAO      VARCHAR2(50),
  LEIXING    VARCHAR2(50),
  NEIRONG    VARCHAR2(2000),
  ZERENREN   VARCHAR2(50),
  WANCHENG   DATE,
  BIAOZHI    VARCHAR2(2000),
  YANSHOUREN VARCHAR2(50)
)
tablespace JH_DB
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64
    minextents 1
    maxextents unlimited
  );




-- Create table
create table PANQUAN
(
  ID       NUMBER(8),
  USERNAME VARCHAR2(50),
  KEY      VARCHAR2(50),
  GRANTTO  VARCHAR2(50),
  REALNAME VARCHAR2(50),
  TEL      VARCHAR2(50)
)
tablespace JH_DB
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64
    minextents 1
    maxextents unlimited
  );


-- Create table
create table PFANGWEN
(
  ID   NUMBER(8),
  NAME VARCHAR2(50),
  TIME DATE
)
tablespace JH_DB
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64
    minextents 1
    maxextents unlimited
  );




-- Create table
create table ZHIXING
(
  ID             NUMBER(8),
  BIANHAO        VARCHAR2(50),
  SHIJI          DATE,
  CHENGGUO       VARCHAR2(50),
  KEZHANG        VARCHAR2(50),
  KESHEN         VARCHAR2(50),
  NEIBUYIJIAN    VARCHAR2(500),
  SHENQING       DATE,
  ZHUGUAN        VARCHAR2(50),
  ZHANSHEN       VARCHAR2(50),
  YANSHOUSHIJIAN DATE,
  YANSHOUYIJIAN  VARCHAR2(500),
  STATUS         VARCHAR2(50)
)
tablespace JH_DB
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64
    minextents 1
    maxextents unlimited
  );




-- Create table
create table SYS_ROLE
(
  ID          NUMBER(8),
  ROLE_NAME   VARCHAR2(50),
  ROLE_LIMITS VARCHAR2(50),
  ISTATUS     NUMBER(2) default 0 not null
)
tablespace JH_DB
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64
    minextents 1
    maxextents unlimited
  );




insert into panquan (ID, USERNAME, KEY, GRANTTO, REALNAME, TEL)
values (1, 'admin', '96E79218965EB72C92A549DD5A330112', '系统管理员', '系统管理员', '1238123123');


insert into panquan (ID, USERNAME, KEY, GRANTTO, REALNAME, TEL)
values (4, 'test2', '96E79218965EB72C92A549DD5A330112', '主管领导', '主管领导', '123123');


insert into panquan (ID, USERNAME, KEY, GRANTTO, REALNAME, TEL)
values (3, 'test1', '96E79218965EB72C92A549DD5A330112', '科长', '科员', '123123');


insert into panquan (ID, USERNAME, KEY, GRANTTO, REALNAME, TEL)
values (2, 'test', '96E79218965EB72C92A549DD5A330112', '科员', '测试1', '13848182841');

参考阅读: http://blog.csdn.net/myl0197/article/details/6262458