<i id="0ngdt"></i>

    1. <track id="0ngdt"><delect id="0ngdt"></delect></track>

        <track id="0ngdt"><delect id="0ngdt"></delect></track>

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

        在一個項目中,可能用到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();
        }
        }
        }

        結果:

        AspNet技術Asp.Net Oracle數據的通用操作類,轉載需保留來源!

        鄭重聲明:本文版權歸原作者所有,轉載文章僅為傳播更多信息之目的,如作者信息標記有誤,請第一時間聯系我們修改或刪除,多謝。

        女男啪啪免费小视频
        <i id="0ngdt"></i>

          1. <track id="0ngdt"><delect id="0ngdt"></delect></track>

              <track id="0ngdt"><delect id="0ngdt"></delect></track>