C# 连接 Oracle 数据库（三种方式：OracleClient、ODBC、OLEDB）

1、OracleClient

//基于.NET 2.0，只有2.0中包含OracleClient

using System;

using System.Collections;

using System.ComponentModel;

using System.Data;

using System.Web;

using System.Web.Services;

using System.Web.Services.Protocols;

using System.Security.Cryptography;

using System.IO;

using System.Collections.Generic;

using System.Configuration;

using System.Data.SqlClient;

using System.Data.OracleClient;

public partial class orclService : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

Response.AddHeader("Access-Control-Allow-Origin", "*");

List<info> result = new List<info>();

string callback = Request.QueryString["method"];

if (callback == "getTableInfo")

result = getTableInfo();

Response.Write(result);

Response.End();

}

//<add key="orclCon" value="Data Source=ORCL;User Id=sa;Password=123;"/>

private static string strConn = ConfigurationSettings.AppSettings["orclCon"];

[WebMethod(Description = "<h3>Oracle连接测试</h3>")]

public List<info> getTableInfo()

{

List<info> list = new List<info>();

OracleConnection conn = new OracleConnection(strConn);

conn.Open();

string strComm = "select * from ATEST_POLYGON";

OracleCommand comm = new OracleCommand(strComm, conn);

OracleDataReader sdr = comm.ExecuteReader();

info strInfo = null;

while (sdr.Read())

{

strInfo = new info();

strInfo.ID = sdr["ID"].ToString();

strInfo.Content = sdr["CONTENT"].ToString();

list.Add(strInfo);

}

if (!sdr.HasRows)

{

strInfo.Content = "无匹配记录";

list.Add(strInfo);

}

sdr.Close();

return list;

}

public class info

{

private string id;

public string ID

{

get { return this.id; }

set { this.id = value; }

}

private string content;

public string Content

{

get { return this.content; }

set { this.content = value; }

}

}

}

2、ODBC（参见“Oracle 通过 ODBC 连接”）

using System;

using System.Collections;

using System.ComponentModel;

using System.Data;

using System.Linq;

using System.Web;

using System.Web.Services;

using System.Web.Services.Protocols;

using System.Xml.Linq;

using System.Security.Cryptography;

using System.IO;

using System.Collections.Generic;

using System.Configuration;

using System.Data.SqlClient;

using System.Data.Odbc;

public partial class orclService : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

Response.AddHeader("Access-Control-Allow-Origin", "*");

int result = 0;

string callback = Request.QueryString["method"];

if (callback == "getTableInfo")

result = getTableInfo();

Response.Write(result);

Response.End();

}

//<add key="orclCon_windows" value="DSN=oracle_windows;UID=sa;PWD=123;"/>

// <add key="orclCon_linux" value="DSN=oracle_linux;UID=sde;PWD=sde;"/>

private static string strConn = ConfigurationSettings.AppSettings["orclCon_linux"];

[WebMethod(Description = "<h3>Oracle连接测试</h3>")]

public int getTableInfo()

{

OdbcConnection odbcconn = new OdbcConnection(strConn);

odbcconn.Open();

string strComm = "select * from ATEST_POLYGON";

OdbcDataAdapter odbcda = new OdbcDataAdapter(strComm, odbcconn);

DataSet ds = new DataSet();

odbcda.Fill(ds);

DataTable dt = new DataTable();

dt = ds.Tables[0];

odbcconn.Close();

odbcconn.Dispose();

return dt.Rows.Count;

}

}

3、OLEDB

using System;

using System.Collections;

using System.ComponentModel;

using System.Data;

using System.Linq;

using System.Web;

using System.Web.Services;

using System.Web.Services.Protocols;

using System.Xml.Linq;

using System.Security.Cryptography;

using System.IO;

using System.Collections.Generic;

using System.Configuration;

using System.Data.SqlClient;

using System.Data.OleDb;

public partial class orclService : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

Response.AddHeader("Access-Control-Allow-Origin", "*");

List<info> result = new List<info>();

string callback = Request.QueryString["method"];

if (callback == "getTableInfo")

result = getTableInfo();

Response.Write(result);

Response.End();

}

//<add key="orclCon_windows" value="Provider=OraOLEDB.Oracle.1;Data Source=ORCL;User ID=sa;Password=123;Persist Security Info=False;"/>

//<add key="orclCon_linux" value="Provider=OraOLEDB.Oracle.1;Data Source=ORCL_LINUX;User ID=sde;Password=sde;Persist Security Info=False;"/>

private static string strConn = ConfigurationSettings.AppSettings["orclCon_windows"];

[WebMethod(Description = "<h3>Oracle连接测试</h3>")]

public List<info> getTableInfo()

{

List<info> list = new List<info>();

OleDbConnection conn = new OleDbConnection(strConn);

conn.Open();

string strComm = "select * from ATEST_POLYGON";

OleDbCommand comm = new OleDbCommand(strComm, conn);

OleDbDataReader sdr = comm.ExecuteReader();

info strInfo = null;

while (sdr.Read())

{

strInfo = new info();

strInfo.ID = sdr["ID"].ToString();

strInfo.Content = sdr["CONTENT"].ToString();

list.Add(strInfo);

}

if (!sdr.HasRows)

{

strInfo.Content = "无匹配记录";

list.Add(strInfo);

}

sdr.Close();

return list;

conn.Close();

conn.Dispose();

return list;

}

public class info

{

private string id;

public string ID

{

get { return this.id; }

set { this.id = value; }

}

private string content;

public string Content

{

get { return this.content; }

set { this.content = value; }

}

}

}