日本好好热aⅴ|国产99视频精品免费观看|日本成人aV在线|久热香蕉国产在线

• 電腦軟件
• 單機游戲
• 安卓應用
• 安卓游戲
• 蘋果電腦
• 蘋果手機

首頁 → 西西教程 → 數據庫教程 → PetShop使用存儲過程與PLSQL批量處理實例

PetShop使用存儲過程與PLSQL批量處理實例

相關軟件相關文章發(fā)表評論 來源：西西整理時間：2013/3/3 21:13:56字體大�。�A-A+

作者：西西點擊：3次評論：1次標簽： 存儲過程

1、大概思路

 

備注：黃色為影響參數

2、PetShop4的經典數據庫連接代碼回顧

PetShop4有3個函數，具體有：

ExecuteReader:可以讀一個表的記錄，只能讀不能寫。

ExecuteScalar:只能讀一條記錄，一般用來判斷數據庫是否有數據等，只能讀不能寫。

ExecuteNonQuery:可以寫以可以讀。

這里介紹一下PrepareCommand、ExecuteNoQuery。

2.1   PrepareCommand

注意：當前函數是private的，不提供給外部調用。

        /// 

        /// Internal function to prepare a command for execution by the database
        /// 
        /// Existing command object
        /// Database connection object
        /// Optional transaction object
        /// Command type, e.g. stored procedure
        /// Command test
        /// Parameters for the command
        private static void PrepareCommand(OracleCommand cmd, OracleConnection conn, OracleTransaction trans, CommandType cmdType, string cmdText, OracleParameter[] commandParameters) {
            //Open the connection if required
            if (conn.State != ConnectionState.Open)
                conn.Open();

            //Set up the command
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            cmd.CommandType = cmdType;

            //Bind it to the transaction if it exists
            if (trans != null)
                cmd.Transaction = trans;

            // Bind the parameters passed in
            if (commandParameters != null) {
                foreach (OracleParameter parm in commandParameters)
                    cmd.Parameters.Add(parm);
            }
        }

2.2、ExecuteNoQuery

此函數：傳入連接串、執(zhí)行類型、SQL、參數

       ///

        /// Execute a database query which does not include a select
        ///

        /// Connection string to database
        /// Command type either stored procedure or SQL
        /// Acutall SQL Command
        /// Parameters to bind to the command
        ///
        public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters) {
            // Create a new Oracle command
            OracleCommand cmd = new OracleCommand();
            //Create a connection
            using (OracleConnection connection = new OracleConnection(connectionString)) {
                //Prepare the command
                PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
                //Execute the command
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return val;
            }
        }

此函數：傳入事務、執(zhí)行類型、SQL、參數

      ///

        /// Execute an OracleCommand (that returns no resultset) against an existing database transaction
        /// using the provided parameters.
        ///

        ///
        /// e.g.:
        ///  int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders", new OracleParameter(":prodid", 24));
        ///
        /// an existing database transaction
        /// the CommandType (stored procedure, text, etc.)
        /// the stored procedure name or PL/SQL command
        /// an array of OracleParamters used to execute the command
        /// an int representing the number of rows affected by the command
        public static int ExecuteNonQuery(OracleTransaction trans, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters) {
            OracleCommand cmd = new OracleCommand();
            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }

 此函數：傳入連接、執(zhí)行類型、SQL、參數

       ///

        /// Execute an OracleCommand (that returns no resultset) against an existing database connection
        /// using the provided parameters.
        ///

        ///
        /// e.g.:
        ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new OracleParameter(":prodid", 24));
        ///
        /// an existing database connection
        /// the CommandType (stored procedure, text, etc.)
        /// the stored procedure name or PL/SQL command
        /// an array of OracleParamters used to execute the command
        /// an int representing the number of rows affected by the command
        public static int ExecuteNonQuery(OracleConnection connection, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters) {
            OracleCommand cmd = new OracleCommand();
            PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }

3、如何寫好一個的OracleHelper

3.1   PetShop的OracleHelper

PetShop不是寫好了嗎？為什么還要自己寫？

eg:PetShop4的函數不足以方便我們操作數據庫，如批量插入需要防注入的參數時，需要等全部插入完再提交整個事務。

eg:PetShop4的函數在處理存儲過程里還不完善，返回值沒有指向。

3.2、OracleHelper

注意：PetShop4在參數上在調用OracleHelper考慮了緩存，這里暫時不考慮。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.OracleClient;
using System.Collections;
namespace Util
{
    public abstract class OracleHelper
    {
        ///

        /// 準備存儲過程執(zhí)行查詢
        ///

       
        /// 數據庫連接
        public static OracleTransaction GetTrans(string connectionString)
        {
            OracleConnection conn = new OracleConnection(connectionString);
            conn.Open();
            OracleTransaction trans = conn.BeginTransaction();
            return trans;
        }

        ///

        /// 返回視圖
        ///

        ///
        ///
        ///
        ///
        public static DataView ExecuteView(String connectionString, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
        {
            OracleCommand cmd = new OracleCommand();
            using (OracleConnection conn = new OracleConnection(connectionString))
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                DataSet ds = new DataSet();
                OracleDataAdapter da = new OracleDataAdapter(cmd);
                da.Fill(ds);
                DataView dv = ds.Tables[0].DefaultView;
                cmd.Parameters.Clear();
                return dv;
            }
        }
        ///

        /// 執(zhí)行并返回影響行數
        ///

        /// 連接字符串
        /// 執(zhí)行類型
        /// 執(zhí)行文本
        /// 參數
        ///
        public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, IList commandParameters)
        {
            OracleCommand cmd = new OracleCommand();
            using (OracleConnection connection = new OracleConnection(connectionString))
            {
                PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return val;
            }
        }

        ///

        /// Execute a database query which does not include a select
        ///

        /// Connection string to database
        /// Command type either stored procedure or SQL
        /// Acutall SQL Command
        /// Parameters to bind to the command
        ///
        public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
        {
            // Create a new Oracle command
            OracleCommand cmd = new OracleCommand();

            //Create a connection
            using (OracleConnection connection = new OracleConnection(connectionString))
            {

                //Prepare the command
                PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);

                //Execute the command
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return val;
            }
        }

        ///   

        ///    Execute    a OracleCommand (that returns a 1x1 resultset)    against    the    specified SqlTransaction
        ///    using the provided parameters.
        ///   

        ///    A    valid SqlTransaction
        ///    The CommandType (stored procedure, text, etc.)
        ///    The stored procedure name    or PL/SQL command
        ///    An array of    OracleParamters used to execute the command
        ///    An    object containing the value    in the 1x1 resultset generated by the command
        public static int ExecuteNonQuery(OracleTransaction trans, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
        {
            OracleCommand cmd = new OracleCommand();
            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }

        ///

        /// 執(zhí)行并返回影響行數，得手動關閉數據庫連接
        ///

        /// 連接字符串
        /// 執(zhí)行類型
        /// 執(zhí)行文本
        /// 參數
        ///
        public static int ExecuteNonQuery(OracleConnection connection, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
        {

            OracleCommand cmd = new OracleCommand();
            PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }

        ///

        /// Execute a select query that will return a result set
        ///

        /// Connection string
        //// the CommandType (stored procedure, text, etc.)
        /// the stored procedure name or PL/SQL command
        /// an array of OracleParamters used to execute the command
        ///
        public static OracleDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
        {

            //Create the command and connection
            OracleCommand cmd = new OracleCommand();
            OracleConnection conn = new OracleConnection(connectionString);

            try
            {
                //Prepare the command to execute
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);

                //Execute the query, stating that the connection should close when the resulting datareader has been read
                OracleDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return rdr;

            }
            catch
            {

                //If an error occurs close the connection as the reader will not be used and we expect it to close the connection
                conn.Close();
                throw;
            }
        }


        ///

        /// Internal function to prepare a command for execution by the database
        ///

        /// Existing command object
        /// Database connection object
        /// Optional transaction object
        /// Command type, e.g. stored procedure
        /// Command test
        /// Parameters for the command
        private static void PrepareCommand(OracleCommand cmd, OracleConnection conn, OracleTransaction trans, CommandType cmdType, string cmdText, OracleParameter[] commandParameters)
        {

            //Open the connection if required
            if (conn.State != ConnectionState.Open)
                conn.Open();

            //Set up the command
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            cmd.CommandType = cmdType;

            //Bind it to the transaction if it exists
            if (trans != null)
                cmd.Transaction = trans;

            // Bind the parameters passed in
            if (commandParameters != null)
            {
                // 若參數Value值為null時，插入到數據庫的值應該為DBNull.Value，且為ParameterDirection.Input
                foreach (OracleParameter parm in commandParameters)
                    if (parm.Value == null && parm.Direction == ParameterDirection.Input)
                    {
                        cmd.Parameters.Add(parm.ParameterName, parm.OracleType, parm.Size).Value = DBNull.Value;
                    }
                    else
                    {
                        cmd.Parameters.Add(parm);
                    }
            }
        }

        ///

        /// Internal function to prepare a command for execution by the database
        ///

        /// Existing command object
        /// Database connection object
        /// Optional transaction object
        /// Command type, e.g. stored procedure
        /// Command test
        /// Parameters for the command
        private static void PrepareCommand(OracleCommand cmd, OracleConnection conn, OracleTransaction trans, CommandType cmdType, string cmdText, IList commandParameters)
        {

            //Open the connection if required
            if (conn.State != ConnectionState.Open)
                conn.Open();

            //Set up the command
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            cmd.CommandType = cmdType;

            //Bind it to the transaction if it exists
            if (trans != null)
                cmd.Transaction = trans;

            // Bind the parameters passed in
            if (commandParameters != null)
            {
                // 若參數Value值為null時，插入到數據庫的值應該為DBNull.Value，且為ParameterDirection.Input
                foreach (OracleParameter parm in commandParameters)
                    if (parm.Value == null && parm.Direction == ParameterDirection.Input)
                    {
                        cmd.Parameters.Add(parm.ParameterName, parm.OracleType, parm.Size).Value = DBNull.Value;
                    }
                    else
                    {
                        cmd.Parameters.Add(parm);
                    }
            }
        }
    }
}

4、代碼示例

4.1、使用存儲過程

      ///

        /// 新增
        ///

        /// 實體
        /// 返回ID
        /// 返回消息
        ///
        private void executeWithOracleTrans(DEPT v_dept, ref int re, ref string msg)
        {
            try
            {
                OracleParameter[] paras = new OracleParameter[5];
                paras[0] = new OracleParameter("P_DEPTNO", OracleType.Number);
                paras[0].Value = v_dept.DEPTNO;
                paras[1] = new OracleParameter("P_DNAME", OracleType.VarChar);
                paras[1].Value = v_dept.DNAME;
                paras[2] = new OracleParameter("P_LOC", OracleType.VarChar);
                paras[2].Value = v_dept.LOC;
                paras[3] = new OracleParameter("X_RE", OracleType.Int32);
                paras[3].Direction = ParameterDirection.Output;
                paras[4] = new OracleParameter("X_MSG", OracleType.VarChar, 100);
                paras[4].Direction = ParameterDirection.Output;

               OracleHelper.ExecuteNonQuery(this.OracleConnectString, CommandType.StoredProcedure, "PKG_DEMO.Dept_Add", paras);
                re = Convert.ToInt32(paras[3].Value);
                msg = paras[4].Value.ToString();
            }
            catch (Exception ex)
            {
                re = 9;
                msg = ex.Message;
            }
        }

4.2   批處理之使用PL/SQL

      ///

        /// 用PL/SQL增加
        ///

        ///
        ///
        ///
        private void executeWithPLSQL(IList list_dept, ref int re, ref string msg)
        {
            string sql = string.Empty;
            string insert_sql = string.Empty;
            List list_parm = new List();
            try
            {
                int i = 0;
                foreach (DEPT v_dept in list_dept)
                {
                    insert_sql += "insert into DEPT (DEPTNO, DNAME, LOC) values(:P_DEPTNO" + i + ", :P_DNAME" + i + ", :P_LOC" + i + ");";
                    OracleParameter[] paras = new OracleParameter[3];
                    paras[0] = new OracleParameter("P_DEPTNO" + i, OracleType.Number);
                    paras[0].Value = v_dept.DEPTNO;
                    paras[1] = new OracleParameter("P_DNAME" + i, OracleType.VarChar);
                    paras[1].Value = v_dept.DNAME;
                    paras[2] = new OracleParameter("P_LOC" + i, OracleType.VarChar);
                    paras[2].Value = v_dept.LOC;
                    list_parm.Add(paras[0]);
                    list_parm.Add(paras[1]);
                    list_parm.Add(paras[2]);
                    i++;
                }
                sql = "begin " +
                    insert_sql +
                  ":X_RE  := 1; " +
                  ":X_MSG := '提示：新增成功!'; " +
                  "commit; " +
                "exception " +
                  "when others then " +
                    "rollback; " +
                    ":X_RE  := 9; " +
                    ":X_MSG := '操作失敗:[' || sqlcode || ':' || sqlerrm || ']'; " +
                "end; ";
                OracleParameter x_re = new OracleParameter("X_RE", OracleType.Int32);
                x_re.Direction = ParameterDirection.Output;
                OracleParameter x_msg = new OracleParameter("X_MSG", OracleType.VarChar, 100);
                x_msg.Direction = ParameterDirection.Output;
                list_parm.Add(x_re);
                list_parm.Add(x_msg);
                 OracleHelper.ExecuteNonQuery(this.OracleConnectString, CommandType.Text, sql, list_parm);
                re = Convert.ToInt32(x_re.Value);
                msg = x_msg.Value.ToString();
            }
            catch (Exception ex)
            {
                re = 9;
                msg = ex.Message;
            }
        }

4.3   批處理之使用事務

       ///

        /// 用事務新增
        ///

        ///
        ///
        ///
        private void executeWithTrans(IList list_dept, ref int re, ref string msg)
        {
            // 啟用事務進行控制
            OracleTransaction myTrans = OracleHelper.GetTrans(this.OracleConnectString);
            OracleConnection conn = myTrans.Connection;
            try
            {
                string sql = string.Empty;
                foreach (DEPT o in list_dept)
                {
                    sql = "insert into DEPT(DEPTNO,DNAME,LOC) values(:P_DEPTNO,:P_DNAME,:P_LOC)";
                    OracleParameter[] paras = new OracleParameter[3];
                    paras[0] = new OracleParameter("P_DEPTNO", OracleType.Int32);
                    paras[0].Value = o.DEPTNO;
                    paras[1] = new OracleParameter("P_DNAME", OracleType.VarChar);
                    paras[1].Value = o.DNAME;
                    paras[2] = new OracleParameter("P_LOC", OracleType.VarChar);
                    paras[2].Value = o.LOC;
                    OracleHelper.ExecuteNonQuery(myTrans, CommandType.Text, sql, paras);
                }
                myTrans.Commit();
                re = 1;
            }
            catch (Exception ex)
            {
                myTrans.Rollback();
                re = 9;
                msg = ex.Message;
            }
            finally
            {
                conn.Close();
            }
        }

5、運行效果

 

6 、小結

學好.Net，從PetShop開始。

源代碼下載：http://pan.baidu.com/share/link?shareid=443937&uk=85241834

數據庫存儲過程\PDF\視頻下載：http://pan.baidu.com/share/link?shareid=443943&uk=85241834

相關文章

01-02什么是存儲過程？Sql 存儲過程知識詳解

12-01delphi和sqlserver交互、delphi調用SQL Server存儲過程

10-30SQL Server中使用表類型參數批量添加和修改的存儲過程

06-08MS Sql Server 2005存儲過程與觸發(fā)器的安全隱患分析

05-17MSSQL 存儲過程實現更新 內容為空、則該字段不作變實例

02-14數據查詢分頁的相關概念 分頁的存儲過程

11-15MSSQL分頁存儲過程

10-02游標在Oracle數據庫存儲過程中的作用

03-17通用存儲過程分頁(使用ROW_NUMBER()和不使用ROW_NUMBER()兩種情況

03-09實現千萬級數據的分頁的通用存儲過程

相關評論

閱讀本文后您有什么感想? 已有23人給出評價!

• 8 喜歡
• 3 頂
• 1 難過
• 5 囧
• 3 圍觀
• 2 無聊

熱門評論

最新評論

發(fā)表評論 查看所有評論(1)

昵稱:

表情:

字數: 0/500 (您的評論需要經過審核才能顯示)

推薦文章

安裝MSDE2000，提示“為了安全起見，要求使用強 SA 密碼�！� SQL Server 2008 中文版全安裝過程圖文介紹

• SQL Server 2008 中文版全安裝過程圖文介紹
• SQL Server 2005的安裝過程介紹

相關下載

名稱大小下載

• 1SQL Procedure Writer331KB下載
• 2MySQL服務器圖形管理工具406.3M下載
• 3菜鳥級SQL21天自學通2.0M下載
• 4#1 CD RipperCD音頻處理工具1M下載
• 5(HostsAppend)Hosts文件編輯工具209KB下載
• 6(阿香婆硬盤維護工具)Ashampoo HDD Control18.8M下載
• 7.mesh格式文件查看(CEGui Mesh Viewer)13.0M下載
• 8.NET2.7M下載
• 9.NET CF 3.5魅族修改版2.8M下載

最新文章

• 

  安裝MSDE2000，提示“為了安全起見，要

  10-09-11
• 

  SQL Server 2008 中文版全安裝過程圖文

  10-05-22