Asp.Net Oracle數據的通用操作類

來源：懂視網責編：小采時間：2020-11-27 22:43:38

Asp.Net Oracle數據的通用操作類

Asp.Net Oracle數據的通用操作類:在一個項目中，可能用到2種數據，比如說ORACLE ，MS_SQLSERVER 同時要用到，MS-SQL的數據通用類在很多的教程中都有現成的例子和代碼，但是ORACLE比較少見，但是本次項目中正好用到，貼出來和大家一起共享。這里用OraDbHelper.cs做常見的四種數據操

推薦度：

點擊下載本文 文檔為doc格式

導讀Asp.Net Oracle數據的通用操作類:在一個項目中，可能用到2種數據，比如說ORACLE ，MS_SQLSERVER 同時要用到，MS-SQL的數據通用類在很多的教程中都有現成的例子和代碼，但是ORACLE比較少見，但是本次項目中正好用到，貼出來和大家一起共享。這里用OraDbHelper.cs做常見的四種數據操

在一個項目中，可能用到2種數據，比如說ORACLE ，MS_SQLSERVER 同時要用到，MS-SQL的數據通用類在很多的教程中都有現成的例子和代碼，但是ORACLE比較少見，但是本次項目中正好用到，貼出來和大家一起共享。
這里用OraDbHelper.cs做常見的四種數據操作
一 ExecuteDataTable 返回到內存數據表
二 SqlDataReader 直接讀數據
三 ExecuteScalar 獲得一條數據
四 ExecuteNonQuery 對數據庫執行增刪改操作
環境：客戶端WIN XP 安裝的是ORACLE 8.17客戶端
數據庫服務器是：Red Hat Enterprise Linux AS 安裝的是Oracle Database 10g
代碼如下:


SQL*Plus: Release 8.1.7.0.0 - Production on 星期六 12月 19 18:10:19 2009 
(c) Copyright 2000 Oracle Corporation. All rights reserved. 
連接到: 
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production 
With the Partitioning, OLAP and Data Mining options 
 
首先 OraDbHelper.cs 
 代碼如下:
using System; 
using System.Data; 
using System.Configuration; 
using System.Web; 
using System.Web.Security; 
using System.Web.UI; 
using System.Web.UI.WebControls; 
using System.Web.UI.WebControls.WebParts; 
using System.Web.UI.HtmlControls; 
using System.Data.SqlClient; 
using System.Data.OracleClient; 
namespace xjy.DAL 
{ 
/// <summary> 
/// OraDbHelper 對ORACLE 數據庫操作的通用類 
/// 作者;薛佳岳 
/// 日期:2009-12-18 
/// </summary> 
public class OraDbHelper 
{ 
private string connectionString; 
/// <summary> 
/// 設置數據庫字符串的連接 
/// </summary> 
public string ConnectionString 
{ 
set { connectionString = value; } 
} 
public OraDbHelper(string connectionString) 
{ 
this.connectionString = connectionString; 
} 
#region ExecuteDataTable 
/// <summary> 
/// 執行一個查詢，并返回結果集 
/// </summary> 
/// <param name="commandText">要執行的查詢SQL文本命令</param> 
/// <returns>返回查詢結果集</returns> 
public DataTable ExecuteDataTable(string commandText) 
{ 
return ExecuteDataTable(commandText, CommandType.Text, null); 
} 
/// <summary> 
/// 執行一個查詢,并返回查詢結果 
/// </summary> 
/// <param name="commandText">要執行的SQL語句</param> 
/// <param name="commandType">要執行的查詢語句的類型，如存儲過程或者SQL文本命令</param> 
/// <returns>返回查詢結果集</returns> 
public DataTable ExecuteDataTable(string commandText, CommandType commandType) 
{ 
return ExecuteDataTable(commandText, commandType, null); 
} 
/// <summary> 
/// 執行一個查詢,并返回查詢結果 
/// </summary> 
/// <param name="commandText">要執行的SQL語句</param> 
/// <param name="commandType">要執行的查詢語句的類型，如存儲過程或者SQL文本命令</param> 
/// <param name="parameters">PL-SQL 語句或存儲過程的參數數組</param> 
/// <returns></returns> 
public DataTable ExecuteDataTable(string commandText, CommandType commandType, params OracleParameter[] parameters) 
{ 
DataTable data = new DataTable();//實例化DataTable，用于裝載查詢結果集 
using (OracleConnection connection = new OracleConnection(connectionString)) 
{ 
using (OracleCommand command = new OracleCommand(commandText, connection)) 
{ 
command.CommandType = commandType;//設置command的CommandType為指定的CommandType 
//如果同時傳入了參數，則添加這些參數 
if (parameters != null) 
{ 
foreach (OracleParameter parameter in parameters) 
{ 
command.Parameters.Add(parameter); 
} 
} 
//通過包含查詢SQL的SqlCommand實例來實例化SqlDataAdapter 
OracleDataAdapter adapter = new OracleDataAdapter(command); 
adapter.Fill(data);//填充DataTable 
} 
} 
return data; 
} 
#endregion ExecuteDataTable 
#region ExecuteReader 
/// <summary> 
/// 將 CommandText 發送到 Connection 并生成一個 OracleDataReader。 
/// </summary> 
/// <param name="commandText">要執行的查詢SQL文本命令</param> 
/// <returns></returns> 
public OracleDataReader ExecuteReader(string commandText) 
{ 
return ExecuteReader(commandText, CommandType.Text, null); 
} 
/// <summary> 
/// 將 CommandText 發送到 Connection 并生成一個 OracleDataReader。 
/// </summary> 
/// <param name="commandText">要執行的SQL語句</param> 
/// <param name="commandType">要執行的查詢語句的類型，如存儲過程或者SQL文本命令</param> 
/// <returns></returns> 
public OracleDataReader ExecuteReader(string commandText, CommandType commandType) 
{ 
return ExecuteReader(commandText, commandType, null); 
} 
/// <summary> 
/// 將 CommandText 發送到 Connection 并生成一個 OracleDataReader。 
/// </summary> 
/// <param name="commandText">要執行的SQL語句</param> 
/// <param name="commandType">要執行的查詢語句的類型，如存儲過程或者SQL文本命令</param> 
/// <param name="parameters">Transact-SQL 語句或存儲過程的參數數組</param> 
/// <returns></returns> 
public OracleDataReader ExecuteReader(string commandText, CommandType commandType, OracleCommand[] parameters) 
{ 
OracleConnection connection = new OracleConnection(connectionString); 
OracleCommand command = new OracleCommand(commandText, connection); 
//如果同時傳入了參數，則添加這些參數 
if (parameters != null) 
{ 
foreach (OracleCommand parameter in parameters) 
{ 
command.Parameters.Add(parameter); 
} 
} 
connection.Open(); 
//CommandBehavior.CloseConnection參數指示關閉Reader對象時關閉與其關聯的Connection對象 
return command.ExecuteReader(CommandBehavior.CloseConnection); 
} 
#endregion ExecuteReader 
#region ExecuteScalar 
/// <summary> 
/// 從數據庫中檢索單個值（例如一個聚合值）。 
/// </summary> 
/// <param name="commandText">要執行的查詢PL-SQL文本命令</param> 
/// <returns></returns> 
public Object ExecuteScalar(string commandText) 
{ 
return ExecuteScalar(commandText, CommandType.Text, null); 
} 
/// <summary> 
/// 從數據庫中檢索單個值（例如一個聚合值）。 
/// </summary> 
/// <param name="commandText">要執行的SQL語句</param> 
/// <param name="commandType">要執行的查詢語句的類型，如存儲過程或者PL-SQL文本命令</param> 
/// <returns></returns> 
public Object ExecuteScalar(string commandText, CommandType commandType) 
{ 
return ExecuteScalar(commandText, commandType, null); 
} 
/// <summary> 
/// 從數據庫中檢索單個值（例如一個聚合值）。 
/// </summary> 
/// <param name="commandText">要執行的SQL語句</param> 
/// <param name="commandType">要執行的查詢語句的類型，如存儲過程或者SQL文本命令</param> 
/// <param name="parameters">PL-SQL 語句或存儲過程的參數數組</param> 
/// <returns></returns> 
public Object ExecuteScalar(string commandText, CommandType commandType, OracleParameter[] parameters) 
{ 
object result = null; 
using (OracleConnection connection = new OracleConnection(connectionString)) 
{ 
using (OracleCommand command = new OracleCommand(commandText, connection)) 
{ 
command.CommandType = commandType;//設置command的CommandType為指定的CommandType 
//如果同時傳入了參數，則添加這些參數 
if (parameters != null) 
{ 
foreach (OracleParameter parameter in parameters) 
{ 
command.Parameters.Add(parameter); 
} 
} 
connection.Open();//打開數據庫連接 
result = command.ExecuteScalar(); 
} 
} 
return result;//返回查詢結果的第一行第一列，忽略其它行和列 
} 
#endregion ExecuteScalar 
#region ExecuteNonQuery 
/// <summary> 
/// 對數據庫執行增刪改操作 
/// </summary> 
/// <param name="commandText">要執行的查詢pl-sql文本命令</param> 
/// <returns></returns> 
public int ExecuteNonQuery(string commandText) 
{ 
return ExecuteNonQuery(commandText, CommandType.Text, null); 
} 
/// <summary> 
/// 對數據庫執行增刪改操作 
/// </summary> 
/// <param name="commandText">要執行的pl-sql語句</param> 
/// <param name="commandType">要執行的查詢語句的類型，如存儲過程或者pl-sql文本命令</param> 
/// <returns></returns> 
public int ExecuteNonQuery(string commandText, CommandType commandType) 
{ 
return ExecuteNonQuery(commandText, commandType, null); 
} 
/// <summary> 
/// 對數據庫執行增刪改操作 
/// </summary> 
/// <param name="commandText">要執行的pl-sql語句</param> 
/// <param name="commandType">要執行的查詢語句的類型，如存儲過程或者pl-sql文本命令</param> 
/// <param name="parameters">pl-sql 語句或存儲過程的參數數組</param> 
/// <returns>返回執行操作受影響的行數</returns> 
public int ExecuteNonQuery(string commandText, CommandType commandType, OracleParameter[] parameters) 
{ 
int count = 0; 
using (OracleConnection connection = new OracleConnection(connectionString)) 
{ 
using (OracleCommand command = new OracleCommand(commandText, connection)) 
{ 
command.CommandType = commandType;//設置command的CommandType為指定的CommandType 
//如果同時傳入了參數，則添加這些參數 
if (parameters != null) 
{ 
foreach (OracleParameter parameter in parameters) 
{ 
command.Parameters.Add(parameter); 
} 
} 
connection.Open();//打開數據庫連接 
count = command.ExecuteNonQuery(); 
} 
} 
return count;//返回執行增刪改操作之后，數據庫中受影響的行數 
} 
#endregion ExecuteNonQuery 
} 
} 
 
一測試datatable 
前臺代碼 
 代碼如下:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %> 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> 
<html xmlns="http://www.w3.org/1999/xhtml"> 
<head runat="server"> 
<title>無標題頁</title> 
</head> 
<body> 
<form id="form1" runat="server"> 
<div> 
<asp:GridView ID="GridView1" runat="server"> 
</asp:GridView> 
</div> 
</form> 
</body> 
</html> 
 
后臺代碼 
 代碼如下:
using System; 
using System.Data; 
using System.Configuration; 
using System.Collections; 
using System.Web; 
using System.Web.Security; 
using System.Web.UI; 
using System.Web.UI.WebControls; 
using System.Web.UI.WebControls.WebParts; 
using System.Web.UI.HtmlControls; 
using xjy.DAL; 
using System.Data.SqlClient; 
public partial class _Default : System.Web.UI.Page 
{ 
protected void Page_Load(object sender, EventArgs e) 
{ 
ConnectionStringSettings setting = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString1"]; 
string myStr = setting.ConnectionString; 
OraDbHelper myora = new OraDbHelper(myStr); 
DataTable t1 = new DataTable(); 
t1=myora.ExecuteDataTable("select art_no,descr from article where art_no<100"); 
GridView1.DataSource=t1; 
GridView1.DataBind(); 
} 
} 
 
結果如圖


二 測試 SqlDataReader 
前臺代碼不變 
后臺代碼如下： 
 代碼如下:

using System; 
using System.Data; 
using System.Configuration; 
using System.Collections; 
using System.Web; 
using System.Web.Security; 
using System.Web.UI; 
using System.Web.UI.WebControls; 
using System.Web.UI.WebControls.WebParts; 
using System.Web.UI.HtmlControls; 
using xjy.DAL; 
using System.Data.SqlClient; 
using System.Data.OracleClient; 
public partial class _Default : System.Web.UI.Page 
{ 
protected void Page_Load(object sender, EventArgs e) 
{ 
ConnectionStringSettings setting = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString1"]; 
string myStr = setting.ConnectionString; 
OraDbHelper myora = new OraDbHelper(myStr); 
OracleDataReader myDr = myora.ExecuteReader("select art_no,descr from article where art_no<100"); 
while (myDr.Read()) 
{ 
Response.Write("|" + myDr[0] + "|" + myDr[1]+" 
"); 
Response.Write("____________________________________"); 
} 
myDr.Close(); 
myDr.Dispose(); 
} 
} 
 
結果如圖 


三測試ExecuteScalar 
前臺代碼 
 代碼如下:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %> 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> 
<html xmlns="http://www.w3.org/1999/xhtml"> 
<head runat="server"> 
<title>無標題頁</title> 
</head> 
<body> 
<form id="form1" runat="server"> 
<div> 
<asp:Label ID="Label1" runat="server" Text="Label"></asp:Label> 
</div> 
</form> 
</body> 
</html> 
 
后臺代碼 
 代碼如下:
using System; 
using System.Data; 
using System.Configuration; 
using System.Collections; 
using System.Web; 
using System.Web.Security; 
using System.Web.UI; 
using System.Web.UI.WebControls; 
using System.Web.UI.WebControls.WebParts; 
using System.Web.UI.HtmlControls; 
using xjy.DAL; 
using System.Data.SqlClient; 
using System.Data.OracleClient; 
public partial class _Default : System.Web.UI.Page 
{ 
protected void Page_Load(object sender, EventArgs e) 
{ 
ConnectionStringSettings setting = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString1"]; 
string myStr = setting.ConnectionString; 
OraDbHelper myora = new OraDbHelper(myStr); 
string shuliang; 
shuliang = myora.ExecuteScalar("select count(art_no) from article where art_no<100").ToString(); 
Label1.Text = shuliang; 
} 
} 
 
結果如圖：


四測試ExecuteNonQuery
前臺代碼：
 代碼如下:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %> 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> 
<html xmlns="http://www.w3.org/1999/xhtml"> 
<head runat="server"> 
<title>無標題頁</title> 
</head> 
<body> 
<form id="form1" runat="server"> 
<div> <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label> 
<asp:GridView ID="GridView1" runat="server"> 
</asp:GridView> 
<asp:Label ID="Label2" runat="server" Text="Label"></asp:Label> 
<asp:GridView ID="GridView2" runat="server"> 
</asp:GridView> 
<asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="恢復值" /> 
</div> 
</form> 
</body> 
</html> 
 
后臺代碼 
 代碼如下:
using System; 
using System.Data; 
using System.Configuration; 
using System.Collections; 
using System.Web; 
using System.Web.Security; 
using System.Web.UI; 
using System.Web.UI.WebControls; 
using System.Web.UI.WebControls.WebParts; 
using System.Web.UI.HtmlControls; 
using xjy.DAL; 
using System.Data.SqlClient; 
using System.Data.OracleClient; 
public partial class _Default : System.Web.UI.Page 
{ 
protected void Page_Load(object sender, EventArgs e) 
{ 
ConnectionStringSettings setting = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString1"]; 
string myStr = setting.ConnectionString; 
OraDbHelper myora = new OraDbHelper(myStr); 
DataTable t1 = new DataTable(); 
Label1.Text = "更新前的值"; 
t1 = myora.ExecuteDataTable("select art_no,descr from article where art_no=8"); 
GridView1.DataSource = t1; 
GridView1.DataBind(); 
if (myora.ExecuteNonQuery("update article set descr='更新描述測試值' where art_no=8")>0) 
{ 
Label2.Text = "更新后的值"; 
t1 = myora.ExecuteDataTable("select art_no,descr from article where art_no=8"); 
GridView2.DataSource = t1; 
GridView2.DataBind(); 
} 
} 
protected void Button1_Click(object sender, EventArgs e) 
{ 
ConnectionStringSettings setting = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString1"]; 
string myStr = setting.ConnectionString; 
OraDbHelper myora = new OraDbHelper(myStr); 
DataTable t1 = new DataTable(); 
if (myora.ExecuteNonQuery("update article set descr='可憐可憐60ML/瓶' where art_no=8") > 0) 
{ 
Label2.Text = "更新后的值"; 
t1 = myora.ExecuteDataTable("select art_no,descr from article where art_no=8"); 
GridView2.DataSource = t1; 
GridView2.DataBind(); 
} 
} 
} 
 
結果： 

 
 




聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com