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
///
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
///
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
///
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
{
string sql = string.Empty;
string insert_sql = string.Empty;
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
{
// 启用事务进行控制
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
喜欢 
顶
难过
囧
围观
无聊
