|
数据库语言是: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');
|
