Asp.Net Oracle數(shù)據(jù)的通用操作類
更新時間：2009年12月20日 02:20:47 作者：
Asp.Net連接Oracle數(shù)據(jù)的通用操作類并且利用它對數(shù)據(jù)庫查詢、匯總、更新等操作演示
在一個項目中，可能用到2種數(shù)據(jù)，比如說ORACLE ，MS_SQLSERVER 同時要用到，MS-SQL的數(shù)據(jù)通用類在很多的教程中都有現(xiàn)成的例子和代碼，但是ORACLE比較少見，但是本次項目中正好用到，貼出來和大家一起共享。
這里用OraDbHelper.cs做常見的四種數(shù)據(jù)操作
一 ExecuteDataTable 返回到內(nèi)存數(shù)據(jù)表
二 SqlDataReader 直接讀數(shù)據(jù)
三 ExecuteScalar 獲得一條數(shù)據(jù)
四 ExecuteNonQuery 對數(shù)據(jù)庫執(zhí)行增刪改操作
環(huán)境：客戶端WIN XP 安裝的是ORACLE 8.17客戶端
數(shù)據(jù)庫服務(wù)器是：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 數(shù)據(jù)庫操作的通用類
/// 作者;薛佳岳
/// 日期:2009-12-18
/// </summary>
public class OraDbHelper
{
private string connectionString;
/// <summary>
/// 設(shè)置數(shù)據(jù)庫字符串的連接
/// </summary>
public string ConnectionString
{
set { connectionString = value; }
}
public OraDbHelper(string connectionString)
{
this.connectionString = connectionString;
}
#region ExecuteDataTable
/// <summary>
/// 執(zhí)行一個查詢，并返回結(jié)果集
/// </summary>
/// <param name="commandText">要執(zhí)行的查詢SQL文本命令</param>
/// <returns>返回查詢結(jié)果集</returns>
public DataTable ExecuteDataTable(string commandText)
{
return ExecuteDataTable(commandText, CommandType.Text, null);
}
/// <summary>
/// 執(zhí)行一個查詢,并返回查詢結(jié)果
/// </summary>
/// <param name="commandText">要執(zhí)行的SQL語句</param>
/// <param name="commandType">要執(zhí)行的查詢語句的類型，如存儲過程或者SQL文本命令</param>
/// <returns>返回查詢結(jié)果集</returns>
public DataTable ExecuteDataTable(string commandText, CommandType commandType)
{
return ExecuteDataTable(commandText, commandType, null);
}
/// <summary>
/// 執(zhí)行一個查詢,并返回查詢結(jié)果
/// </summary>
/// <param name="commandText">要執(zhí)行的SQL語句</param>
/// <param name="commandType">要執(zhí)行的查詢語句的類型，如存儲過程或者SQL文本命令</param>
/// <param name="parameters">PL-SQL 語句或存儲過程的參數(shù)數(shù)組</param>
/// <returns></returns>
public DataTable ExecuteDataTable(string commandText, CommandType commandType, params OracleParameter[] parameters)
{
DataTable data = new DataTable();//實例化DataTable，用于裝載查詢結(jié)果集
using (OracleConnection connection = new OracleConnection(connectionString))
{
using (OracleCommand command = new OracleCommand(commandText, connection))
{
command.CommandType = commandType;//設(shè)置command的CommandType為指定的CommandType
//如果同時傳入了參數(shù)，則添加這些參數(shù)
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 發(fā)送到 Connection 并生成一個 OracleDataReader。
/// </summary>
/// <param name="commandText">要執(zhí)行的查詢SQL文本命令</param>
/// <returns></returns>
public OracleDataReader ExecuteReader(string commandText)
{
return ExecuteReader(commandText, CommandType.Text, null);
}
/// <summary>
/// 將 CommandText 發(fā)送到 Connection 并生成一個 OracleDataReader。
/// </summary>
/// <param name="commandText">要執(zhí)行的SQL語句</param>
/// <param name="commandType">要執(zhí)行的查詢語句的類型，如存儲過程或者SQL文本命令</param>
/// <returns></returns>
public OracleDataReader ExecuteReader(string commandText, CommandType commandType)
{
return ExecuteReader(commandText, commandType, null);
}
/// <summary>
/// 將 CommandText 發(fā)送到 Connection 并生成一個 OracleDataReader。
/// </summary>
/// <param name="commandText">要執(zhí)行的SQL語句</param>
/// <param name="commandType">要執(zhí)行的查詢語句的類型，如存儲過程或者SQL文本命令</param>
/// <param name="parameters">Transact-SQL 語句或存儲過程的參數(shù)數(shù)組</param>
/// <returns></returns>
public OracleDataReader ExecuteReader(string commandText, CommandType commandType, OracleCommand[] parameters)
{
OracleConnection connection = new OracleConnection(connectionString);
OracleCommand command = new OracleCommand(commandText, connection);
//如果同時傳入了參數(shù)，則添加這些參數(shù)
if (parameters != null)
{
foreach (OracleCommand parameter in parameters)
{
command.Parameters.Add(parameter);
}
}
connection.Open();
//CommandBehavior.CloseConnection參數(shù)指示關(guān)閉Reader對象時關(guān)閉與其關(guān)聯(lián)的Connection對象
return command.ExecuteReader(CommandBehavior.CloseConnection);
}
#endregion ExecuteReader
#region ExecuteScalar
/// <summary>
/// 從數(shù)據(jù)庫中檢索單個值（例如一個聚合值）。
/// </summary>
/// <param name="commandText">要執(zhí)行的查詢PL-SQL文本命令</param>
/// <returns></returns>
public Object ExecuteScalar(string commandText)
{
return ExecuteScalar(commandText, CommandType.Text, null);
}
/// <summary>
/// 從數(shù)據(jù)庫中檢索單個值（例如一個聚合值）。
/// </summary>
/// <param name="commandText">要執(zhí)行的SQL語句</param>
/// <param name="commandType">要執(zhí)行的查詢語句的類型，如存儲過程或者PL-SQL文本命令</param>
/// <returns></returns>
public Object ExecuteScalar(string commandText, CommandType commandType)
{
return ExecuteScalar(commandText, commandType, null);
}
/// <summary>
/// 從數(shù)據(jù)庫中檢索單個值（例如一個聚合值）。
/// </summary>
/// <param name="commandText">要執(zhí)行的SQL語句</param>
/// <param name="commandType">要執(zhí)行的查詢語句的類型，如存儲過程或者SQL文本命令</param>
/// <param name="parameters">PL-SQL 語句或存儲過程的參數(shù)數(shù)組</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;//設(shè)置command的CommandType為指定的CommandType
//如果同時傳入了參數(shù)，則添加這些參數(shù)
if (parameters != null)
{
foreach (OracleParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
}
connection.Open();//打開數(shù)據(jù)庫連接
result = command.ExecuteScalar();
}
}
return result;//返回查詢結(jié)果的第一行第一列，忽略其它行和列
}
#endregion ExecuteScalar
#region ExecuteNonQuery
/// <summary>
/// 對數(shù)據(jù)庫執(zhí)行增刪改操作
/// </summary>
/// <param name="commandText">要執(zhí)行的查詢pl-sql文本命令</param>
/// <returns></returns>
public int ExecuteNonQuery(string commandText)
{
return ExecuteNonQuery(commandText, CommandType.Text, null);
}
/// <summary>
/// 對數(shù)據(jù)庫執(zhí)行增刪改操作
/// </summary>
/// <param name="commandText">要執(zhí)行的pl-sql語句</param>
/// <param name="commandType">要執(zhí)行的查詢語句的類型，如存儲過程或者pl-sql文本命令</param>
/// <returns></returns>
public int ExecuteNonQuery(string commandText, CommandType commandType)
{
return ExecuteNonQuery(commandText, commandType, null);
}
/// <summary>
/// 對數(shù)據(jù)庫執(zhí)行增刪改操作
/// </summary>
/// <param name="commandText">要執(zhí)行的pl-sql語句</param>
/// <param name="commandType">要執(zhí)行的查詢語句的類型，如存儲過程或者pl-sql文本命令</param>
/// <param name="parameters">pl-sql 語句或存儲過程的參數(shù)數(shù)組</param>
/// <returns>返回執(zhí)行操作受影響的行數(shù)</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;//設(shè)置command的CommandType為指定的CommandType
//如果同時傳入了參數(shù)，則添加這些參數(shù)
if (parameters != null)
{
foreach (OracleParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
}
connection.Open();//打開數(shù)據(jù)庫連接
count = command.ExecuteNonQuery();
}
}
return count;//返回執(zhí)行增刪改操作之后，數(shù)據(jù)庫中受影響的行數(shù)
}
#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();
}
}
結(jié)果如圖
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();
}
}
結(jié)果如圖
<%@ 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;
}
}
結(jié)果如圖：
<%@ 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="恢復(fù)值" />
</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();
}
}
}
結(jié)果：
這里用OraDbHelper.cs做常見的四種數(shù)據(jù)操作
一 ExecuteDataTable 返回到內(nèi)存數(shù)據(jù)表
二 SqlDataReader 直接讀數(shù)據(jù)
三 ExecuteScalar 獲得一條數(shù)據(jù)
四 ExecuteNonQuery 對數(shù)據(jù)庫執(zhí)行增刪改操作
環(huán)境：客戶端WIN XP 安裝的是ORACLE 8.17客戶端
數(shù)據(jù)庫服務(wù)器是：Red Hat Enterprise Linux AS 安裝的是Oracle Database 10g
復(fù)制代碼 代碼如下:
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
復(fù)制代碼 代碼如下:
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 數(shù)據(jù)庫操作的通用類
/// 作者;薛佳岳
/// 日期:2009-12-18
/// </summary>
public class OraDbHelper
{
private string connectionString;
/// <summary>
/// 設(shè)置數(shù)據(jù)庫字符串的連接
/// </summary>
public string ConnectionString
{
set { connectionString = value; }
}
public OraDbHelper(string connectionString)
{
this.connectionString = connectionString;
}
#region ExecuteDataTable
/// <summary>
/// 執(zhí)行一個查詢，并返回結(jié)果集
/// </summary>
/// <param name="commandText">要執(zhí)行的查詢SQL文本命令</param>
/// <returns>返回查詢結(jié)果集</returns>
public DataTable ExecuteDataTable(string commandText)
{
return ExecuteDataTable(commandText, CommandType.Text, null);
}
/// <summary>
/// 執(zhí)行一個查詢,并返回查詢結(jié)果
/// </summary>
/// <param name="commandText">要執(zhí)行的SQL語句</param>
/// <param name="commandType">要執(zhí)行的查詢語句的類型，如存儲過程或者SQL文本命令</param>
/// <returns>返回查詢結(jié)果集</returns>
public DataTable ExecuteDataTable(string commandText, CommandType commandType)
{
return ExecuteDataTable(commandText, commandType, null);
}
/// <summary>
/// 執(zhí)行一個查詢,并返回查詢結(jié)果
/// </summary>
/// <param name="commandText">要執(zhí)行的SQL語句</param>
/// <param name="commandType">要執(zhí)行的查詢語句的類型，如存儲過程或者SQL文本命令</param>
/// <param name="parameters">PL-SQL 語句或存儲過程的參數(shù)數(shù)組</param>
/// <returns></returns>
public DataTable ExecuteDataTable(string commandText, CommandType commandType, params OracleParameter[] parameters)
{
DataTable data = new DataTable();//實例化DataTable，用于裝載查詢結(jié)果集
using (OracleConnection connection = new OracleConnection(connectionString))
{
using (OracleCommand command = new OracleCommand(commandText, connection))
{
command.CommandType = commandType;//設(shè)置command的CommandType為指定的CommandType
//如果同時傳入了參數(shù)，則添加這些參數(shù)
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 發(fā)送到 Connection 并生成一個 OracleDataReader。
/// </summary>
/// <param name="commandText">要執(zhí)行的查詢SQL文本命令</param>
/// <returns></returns>
public OracleDataReader ExecuteReader(string commandText)
{
return ExecuteReader(commandText, CommandType.Text, null);
}
/// <summary>
/// 將 CommandText 發(fā)送到 Connection 并生成一個 OracleDataReader。
/// </summary>
/// <param name="commandText">要執(zhí)行的SQL語句</param>
/// <param name="commandType">要執(zhí)行的查詢語句的類型，如存儲過程或者SQL文本命令</param>
/// <returns></returns>
public OracleDataReader ExecuteReader(string commandText, CommandType commandType)
{
return ExecuteReader(commandText, commandType, null);
}
/// <summary>
/// 將 CommandText 發(fā)送到 Connection 并生成一個 OracleDataReader。
/// </summary>
/// <param name="commandText">要執(zhí)行的SQL語句</param>
/// <param name="commandType">要執(zhí)行的查詢語句的類型，如存儲過程或者SQL文本命令</param>
/// <param name="parameters">Transact-SQL 語句或存儲過程的參數(shù)數(shù)組</param>
/// <returns></returns>
public OracleDataReader ExecuteReader(string commandText, CommandType commandType, OracleCommand[] parameters)
{
OracleConnection connection = new OracleConnection(connectionString);
OracleCommand command = new OracleCommand(commandText, connection);
//如果同時傳入了參數(shù)，則添加這些參數(shù)
if (parameters != null)
{
foreach (OracleCommand parameter in parameters)
{
command.Parameters.Add(parameter);
}
}
connection.Open();
//CommandBehavior.CloseConnection參數(shù)指示關(guān)閉Reader對象時關(guān)閉與其關(guān)聯(lián)的Connection對象
return command.ExecuteReader(CommandBehavior.CloseConnection);
}
#endregion ExecuteReader
#region ExecuteScalar
/// <summary>
/// 從數(shù)據(jù)庫中檢索單個值（例如一個聚合值）。
/// </summary>
/// <param name="commandText">要執(zhí)行的查詢PL-SQL文本命令</param>
/// <returns></returns>
public Object ExecuteScalar(string commandText)
{
return ExecuteScalar(commandText, CommandType.Text, null);
}
/// <summary>
/// 從數(shù)據(jù)庫中檢索單個值（例如一個聚合值）。
/// </summary>
/// <param name="commandText">要執(zhí)行的SQL語句</param>
/// <param name="commandType">要執(zhí)行的查詢語句的類型，如存儲過程或者PL-SQL文本命令</param>
/// <returns></returns>
public Object ExecuteScalar(string commandText, CommandType commandType)
{
return ExecuteScalar(commandText, commandType, null);
}
/// <summary>
/// 從數(shù)據(jù)庫中檢索單個值（例如一個聚合值）。
/// </summary>
/// <param name="commandText">要執(zhí)行的SQL語句</param>
/// <param name="commandType">要執(zhí)行的查詢語句的類型，如存儲過程或者SQL文本命令</param>
/// <param name="parameters">PL-SQL 語句或存儲過程的參數(shù)數(shù)組</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;//設(shè)置command的CommandType為指定的CommandType
//如果同時傳入了參數(shù)，則添加這些參數(shù)
if (parameters != null)
{
foreach (OracleParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
}
connection.Open();//打開數(shù)據(jù)庫連接
result = command.ExecuteScalar();
}
}
return result;//返回查詢結(jié)果的第一行第一列，忽略其它行和列
}
#endregion ExecuteScalar
#region ExecuteNonQuery
/// <summary>
/// 對數(shù)據(jù)庫執(zhí)行增刪改操作
/// </summary>
/// <param name="commandText">要執(zhí)行的查詢pl-sql文本命令</param>
/// <returns></returns>
public int ExecuteNonQuery(string commandText)
{
return ExecuteNonQuery(commandText, CommandType.Text, null);
}
/// <summary>
/// 對數(shù)據(jù)庫執(zhí)行增刪改操作
/// </summary>
/// <param name="commandText">要執(zhí)行的pl-sql語句</param>
/// <param name="commandType">要執(zhí)行的查詢語句的類型，如存儲過程或者pl-sql文本命令</param>
/// <returns></returns>
public int ExecuteNonQuery(string commandText, CommandType commandType)
{
return ExecuteNonQuery(commandText, commandType, null);
}
/// <summary>
/// 對數(shù)據(jù)庫執(zhí)行增刪改操作
/// </summary>
/// <param name="commandText">要執(zhí)行的pl-sql語句</param>
/// <param name="commandType">要執(zhí)行的查詢語句的類型，如存儲過程或者pl-sql文本命令</param>
/// <param name="parameters">pl-sql 語句或存儲過程的參數(shù)數(shù)組</param>
/// <returns>返回執(zhí)行操作受影響的行數(shù)</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;//設(shè)置command的CommandType為指定的CommandType
//如果同時傳入了參數(shù)，則添加這些參數(shù)
if (parameters != null)
{
foreach (OracleParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
}
connection.Open();//打開數(shù)據(jù)庫連接
count = command.ExecuteNonQuery();
}
}
return count;//返回執(zhí)行增刪改操作之后，數(shù)據(jù)庫中受影響的行數(shù)
}
#endregion ExecuteNonQuery
}
}
一測試datatable
前臺代碼
復(fù)制代碼 代碼如下:
<%@ 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>
后臺代碼
復(fù)制代碼 代碼如下:
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();
}
}
結(jié)果如圖
二 測試 SqlDataReader
前臺代碼不變
后臺代碼如下：
復(fù)制代碼 代碼如下:
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();
}
}
結(jié)果如圖
三測試ExecuteScalar
前臺代碼
復(fù)制代碼 代碼如下:
<%@ 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>
后臺代碼
復(fù)制代碼 代碼如下:
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;
}
}
結(jié)果如圖：
四測試ExecuteNonQuery
前臺代碼：
復(fù)制代碼 代碼如下:
<%@ 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="恢復(fù)值" />
</div>
</form>
</body>
</html>
后臺代碼
復(fù)制代碼 代碼如下:
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();
}
}
}
結(jié)果：
相關(guān)文章
除了"在操作系統(tǒng)中修改時區(qū)信息,然后重啟.NET應(yīng)用程序,使其生效"之外,如何在不修改操作系統(tǒng)時區(qū)的前提下,修改.NET中的默認時區(qū)呢,下面小編給大家分享更改.NET中的默認時區(qū)的操作方法,感興趣的朋友一起看看吧2024-06-06
.net core并發(fā)請求發(fā)送HttpWebRequest的坑解決
這篇文章主要給大家介紹了關(guān)于.net core并發(fā)請求發(fā)送HttpWebRequest的坑的相關(guān)資料，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面來一起看看吧2018-12-12
asp.net 臟字典過濾問題 用正則表達式來過濾臟數(shù)據(jù)
asp.net 臟字典過濾問題 用正則表達式來過濾臟數(shù)據(jù)2009-10-10
用Html5與Asp.net MVC上傳多個文件的實現(xiàn)代碼
Html 5 的有一些File API，對Form表單增強的特性，讓我們輕松支持多文件上傳，看下面的Html片斷代碼2012-08-08
如何為CheckBoxList和RadioButtonList添加滾動條
這篇文章主要介紹了為CheckBoxList和RadioButtonList添加滾動條的方法，感興趣的小伙伴們可以參考一下2016-07-07
asp.net 點縮略圖彈出隨圖片大小自動調(diào)整的頁面
程序用asp.net編寫，功能主要是，點pic_small.Aspx頁面的縮略圖后彈出pic_all.aspx頁面，pic_all.aspx頁面的大小要根據(jù)圖片大小自動調(diào)整2009-06-06
.Net中如何操作IIS的虛擬目錄原理分析及實現(xiàn)方案
編程控制IIS實際上很簡單，和ASP一樣，.Net中需要使用ADSI來操作IIS，但是此時我們不再需要GetObject這個東東了，因為Net為我們提供了更加強大功能的新東東2012-12-12
asp.net開發(fā)與web標準的沖突問題的一些常見解決方法
Visual Studio .net從2003到現(xiàn)在的2008，一路走來慢慢強大……從以前的vs2003能自動改亂你的html代碼到現(xiàn)在在vs2008中都能直接對html代碼進行w3c標準驗證并提示了，非常不易。2009-02-02
asp.net Repeater 數(shù)據(jù)綁定的具體實現(xiàn)(圖文詳解)
此例子綁定的數(shù)據(jù)源為微軟在mssql2000中提供的Northwind數(shù)據(jù)庫中的表Categories。2013-07-07
C#實現(xiàn)HTTP協(xié)議迷你服務(wù)器(兩種方法)
用C#語言實現(xiàn)HTTP協(xié)議的服務(wù)器類本文將以兩種稍微有差別的方式用C#語言實現(xiàn)；要完成高性能的Web服務(wù)功能，通常都是需要寫入到服務(wù)，如IIS，Apache Tomcat感興趣的朋友可以了解下，或許對你學習c#有所幫助2013-02-02