PetShop使用存储过程与PLSQL批量处理实例

　　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

　　此函数：传入连接串、执行类型、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;

　　}

　　}

　　此函数：传入事务、执行类型、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;

　　}

　　此函数：传入连接、执行类型、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

　　{

　　///

　　/// 准备存储过程执行查询

　　///

　　///数据库连接

　　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;

　　}

　　}

　　///

　　/// 执行并返回影响行数

　　///

　　///连接字符串

　　///执行类型

　　///执行文本

　　///参数

　　///

　　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;

　　}

　　///

　　/// 执行并返回影响行数，得手动关闭数据库连接

　　///

　　///连接字符串

　　///执行类型

　　///执行文本

　　///参数

　　///

　　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