|
页面实现：这是我自己拖动的页面，几个简单的按钮和文本框。对按钮产生事件操作。现在我们先查看存储过程，明白程序中调用的过程是怎么编写的。
存储过程代码如下：
存储过程：
插入数据：
CREATE OR REPLACE Procedure p_insert_t_cls --存储过程名称
(
p_stuid in CLASSES.ID%type,
p_stuname in varchar
)
as
BEGIN
insert into classes
values
(p_stuid,p_stuname);
commit;
end;
===============================================
删除 ：（带返回参数）
create or replace procedure proc_delete
(
isid in number , P_ROWS OUT NUMBER
)
is
begin
delete classes where id=isid;
If SQL%Found Then
DBMS_OUTPUT.PUT_LINE('删除成功！');
P_ROWS := 1;
Else
DBMS_OUTPUT.PUT_LINE('删除失败！');
P_ROWS := 0;
End If;
commit;
end
;
删除 ： （不带返回参数）
create or replace procedure p_delete_t_cls1(
cla_id in Number
)
is
begin
DELETE FROM classes WHERE id = cla_id;
commit;
end p_delete_t_cls1;
删除 ： （不带返回参数）指定ID删除
create or replace procedure p_delete_t_cls is
begin
DELETE FROM classes WHERE id = 7;
commit;
end p_delete_t_cls;
====================================================
修改数据：（不带返回参数）
create or replace procedure p_update_t_cls1(
p_stuid in Number,
p_stuname in Nvarchar2
)
is
begin
update classes x set x.classname = p_stuname where x.id = p_stuid;
commit;
end p_update_t_cls1;
修改数据： ：（带返回参数）
create or replace procedure proc_update(
p_stuid in Number,
p_stuname in Nvarchar2,
P_ROW out number
)
is
begin
update classes set classname = p_stuname where id = p_stuid;
If SQL%Found Then
DBMS_OUTPUT.PUT_LINE('更新成功！');
P_ROW := 1;
Else
DBMS_OUTPUT.PUT_LINE('更新失败！');
P_ROW := 0;
End If;
commit;
end proc_update;
修改数据： ： （不带返回参数）指定ID修改
create or replace procedure p_update_t_cls
is
begin
update classes x set x.classname = '44' where x.id = 3;
commit;
end p_update_t_cls;
====================================================
查询所有数据：（带返回参数 游标）
CREATE OR REPLACE PACKAGE pkg_test1
AS
TYPE myrctype IS REF CURSOR;
PROCEDURE get (p_id NUMBER, p_rc OUT myrctype);
END pkg_test1 ;
create or replace function testpro1 return types1.cursorType1 is
lc1 types1.cursorType1;
begin
open lc1 for select id,classname from classes;
return lc1;
end testpro1;
传递ID查询数据：（带返回参数 游标）传递ID查询数据
create or replace package types as
type cursorType is ref cursor;
end;
create or replace function testpro(IV IN NUMBER) return types.cursorType is
lc types.cursorType;
begin
open lc for select * from test where ID=IV;
return lc;
end testpro;
====================================================
下面我们来对每一个事件的详细代码进行编辑 ，代码如下：
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OracleClient;
using Comm;
namespace WindowsFormsApplication1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
String oradb = "Data Source=orcl;User ID=KEYPROJECTDATA;Password=KEYPROJECT;";
/// <summary>
/// 通过ID 增加数据
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button1_Click(object sender, EventArgs e)
{
try
{
OracleConnection conn = new OracleConnection(oradb);
conn.Open();
int str1 = Convert.ToInt32(textBox2.Text);
string str2 = textBox3.Text;
StringBuilder sqlPInsert = new StringBuilder();
sqlPInsert.Append("p_insert_t_cls");
OracleParameter[] param =
{
new OracleParameter("p_stuid", OracleType.Number),
new OracleParameter("p_stuname", OracleType.VarChar)
};
param[0].Value = str1;
param[1].Value = str2;
int i = OracleHelper.ExecuteNonQuery(oradb, CommandType.StoredProcedure, sqlPInsert.ToString(), param);
if (i > 0)
{
MessageBox.Show("插入成功！");
}
else {
MessageBox.Show("插入失败！");
};
conn.Dispose();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
finally
{
}
}
/// <summary>
/// 通过ID 删除数据
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button2_Click(object sender, EventArgs e)
{
try
{
//OracleConnection conn = new OracleConnection(oradb);
//conn.Open();
// 第一种测试
////string str3 = textBox4.Text;
//int str1 = Convert.ToInt32(textBox4.Text);
////string sqlDelete = "p_delete_t_cls1("+str3+")";
//StringBuilder sqlDelete = new StringBuilder();
//sqlDelete.Append("p_delete_t_cls1 ");
//OracleParameter[] param =
//{
//new OracleParameter("cla_id", OracleType.Number)
//};
//param[0].Value = str1;
//int i = OracleHelper.ExecuteNonQuery(oradb, CommandType.StoredProcedure, sqlDelete.ToString(), param);
// ===================================================== 例子
//OracleCommand orclCMD = new OracleCommand();
//orclCMD.Connection = conn;
//orclCMD.CommandText = "gd_p_base_start_manual"; //存储过程名
//orclCMD.CommandType = CommandType.StoredProcedure;//表面是存储过程 如果创建存储过程是有变量，所以还要声明变量
//OracleParameter IdIn = orclCMD.Parameters.Add("ddmonth", OracleType.VarChar, 16); //输入参数
//IdIn.Direction = ParameterDirection.Input;
//IdIn.Value = XXX;
//OracleParameter IdIn1 = orclCMD.Parameters.Add("planid", OracleType.VarChar, 16); //输入参数
//IdIn1.Direction = ParameterDirection.Input;
//IdIn1.Value =XXX;
//orclCMD.ExecuteNonQuery();
//IdIn1.Direction = ParameterDirection.Output...
// =====================================================
//OracleParameter op = new OracleParameter("c", OracleType.Cursor);
//op.Direction = ParameterDirection.ReturnValue;
//cmd.Parameters.Add(op);
OracleConnection conn = new OracleConnection(oradb);
conn.Open();
int str1 = Convert.ToInt32(textBox4.Text);
OracleCommand orclCMD = new OracleCommand();
orclCMD.Connection = conn;
orclCMD.CommandText = "proc_delete"; //存储过程名
orclCMD.CommandType = CommandType.StoredProcedure;//表面是存储过程 如果创建存储过程是有变量，所以还要声明变量
OracleParameter IdIn = orclCMD.Parameters.Add("isid", OracleType.Number); //输入参数
IdIn.Direction = ParameterDirection.Input;
IdIn.Value = str1;
OracleParameter IdIn1 = orclCMD.Parameters.Add("P_ROWS", OracleType.VarChar, 16); //输入参数
//IdIn1.Direction = ParameterDirection.Output;
IdIn1.Direction = ParameterDirection.Output;
//IdIn1.Value ="";
//orclCMD.Parameters.Add(IdIn1);
orclCMD.ExecuteNonQuery();
//MessageBox.Show(IdIn1.Value + "");
int i = Convert.ToInt32(IdIn1.Value);
//MessageBox.Show(IdIn1.Value + "");
if (i > 0)
{
MessageBox.Show("删除成功！");
}
else if (i == 0)
{
MessageBox.Show("无此数据！");
}
else
{
MessageBox.Show("删除失败！");
};
conn.Dispose();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
finally
{
}
}
/// <summary>
/// 通过ID修改数据
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button3_Click(object sender, EventArgs e)
{
try
{
// -===========================================================================================================
////OracleConnection conn = new OracleConnection(oradb);
////conn.Open();
////// 通过传递参数ID调用存储过程 更新数据
////int str5 = Convert.ToInt32(textBox5.Text);
////string str6 = textBox6.Text;
//////string sqlUpdate = "p_update_t_cls1(" + str5 + "," + str6 + ")";
////StringBuilder sqlUpdate = new StringBuilder();
////sqlUpdate.Append("p_update_t_cls1 ");
////OracleParameter[] param =
////{
////new OracleParameter("p_stuid", OracleType.Number),
////new OracleParameter("p_stuname", OracleType.VarChar)
////};
////param[0].Value = str5;
////param[1].Value = str6;
//////OracleCommand cmd = new OracleCommand(sqlUpdate, conn);
//////cmd.CommandType = CommandType.Text;
////int i = OracleHelper.ExecuteNonQuery(oradb, CommandType.StoredProcedure, sqlUpdate.ToString(), param);
// -===========================================================================================================
int str5 = Convert.ToInt32(textBox5.Text);
string str6 = textBox6.Text;
OracleConnection conn = new OracleConnection(oradb);
conn.Open();
OracleCommand command = new OracleCommand();
command.Connection = conn;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "proc_update";
OracleParameter orac = command.Parameters.Add("p_stuid", OracleType.Number);
orac.Direction = ParameterDirection.Input;
orac.Value = str5;
OracleParameter orac1 = command.Parameters.Add("p_stuname", OracleType.VarChar, 20);
orac1.Direction = ParameterDirection.Input;
orac1.Value = str6;
OracleParameter orac2 = command.Parameters.Add("P_ROW", OracleType.Number);
orac2.Direction = ParameterDirection.Output;
//orac2.Value = ;
command.ExecuteNonQuery();
//MessageBox.Show(orac2.Value+"");
int i = Convert.ToInt32(orac2.Value);
if (i > 0)
{
MessageBox.Show("更新成功！");
}else if( i == 0)
{
MessageBox.Show("无此操作数据！");
}
else
{
MessageBox.Show("更新失败！");
};
conn.Dispose(); //Close()也可以。
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
finally
{
}
}
/// <summary>
/// 查询一条数据
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button4_Click(object sender, EventArgs e)
{
try
{
OracleConnection conn = new OracleConnection(oradb);
conn.Open();
// 查询指定ID的数据
//string sqlSelect = " select id,classname from classes where id = 3";
// 通过传递参数来获得数据集
int str7 = Convert.ToInt32(textBox1.Text);
string sqlSelect = "p_select_t_cls(" + str7 + ")";
OracleCommand cmd = new OracleCommand(sqlSelect, conn);
cmd.CommandType = CommandType.Text;
OracleDataAdapter da = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
//DataTable dt = ds.Tables[0];
comboBox1.DataSource = ds.Tables[0];
comboBox1.DisplayMember = "Classname";
comboBox1.ValueMember = "ID";
conn.Dispose();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
finally
{
}
}
/// <summary>
/// 查询所有数据
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button5_Click(object sender, EventArgs e)
{
try
{
// 网络找的
OracleConnection conn = new OracleConnection(oradb);
conn.Open();
//OracleCommand cmd = new OracleCommand("testpro1", conn);
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "testpro1";
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter op = new OracleParameter("lc1", OracleType.Cursor);
op.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(op);
DataSet ds = new DataSet();
OracleDataAdapter da = new OracleDataAdapter(cmd);
da.Fill(ds);
this.dataGridView1.DataSource = ds.Tables[0];
conn.Dispose();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
finally
{
}
}
/// <summary>
/// 通过ID 查询一条数据
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button6_Click(object sender, EventArgs e)
{
//OracleParameter IdIn = orclCMD.Parameters.Add("isid", OracleType.Number); //输入参数
//IdIn.Direction = ParameterDirection.Input;
//IdIn.Value = str1;
// 自己简化版
OracleConnection conn = new OracleConnection(oradb);
OracleCommand cmd = new OracleCommand("testpro", conn);
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter op1 = new OracleParameter("IV", OracleType.Number);
op1.Direction = ParameterDirection.Input;
op1.Value = 1;
cmd.Parameters.Add(op1);
OracleParameter op = new OracleParameter("c", OracleType.Cursor);
op.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(op);
DataSet ds = new DataSet();
OracleDataAdapter da = new OracleDataAdapter(cmd);
//da.Fill(ds);
//this.dataGridView1.DataSource = ds.Tables[0];
da.Fill(ds, "test");
this.dataGridView1.DataSource = ds.Tables["test"];
}
}
}
文件OracleOP.cs .NET中的方法 用于执行oracle sql语句 ： 下面是封装的方法：公司框架 以供学习使用：
using System;
using System.Data;
using System.Data.OracleClient;
public class OracleOP
{
protected static OracleConnection con;//连接对象
public OracleOP()
{
//con = new OracleConnection(System.Configuration.ConfigurationSettings.AppSettings["orclconn"].ToString());
}
public OracleOP(string constr)
{
con = new OracleConnection(constr);
}
#region 打开数据库连接
/// <summary>
/// 打开数据库连接
/// </summary>
private static void Open()
{
//打开数据库连接
if (con.State == ConnectionState.Closed)
{
try
{
//打开数据库连接
con.Open();
}
catch (Exception e)
{
throw e;
}
}
}
#endregion
#region 关闭数据库连接
/// <summary>
/// 关闭数据库连接
/// </summary>
private static void Close()
{
//判断连接的状态是否已经打开
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
#endregion
#region 执行查询语句，返回OracleDataReader ( 注意：调用该方法后，一定要对OracleDataReader进行Close )
/// <summary>
/// 执行查询语句，返回OracleDataReader ( 注意：调用该方法后，一定要对OracleDataReader进行Close )
/// </summary>
/// <param name="sql">查询语句</param>
/// <returns>OracleDataReader</returns>
public static OracleDataReader ExecuteReader(string sql)
{
try
{
Open();
OracleDataReader myReader;
OracleCommand cmd = new OracleCommand(sql, con);
myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Dispose();
return myReader;
}
catch (OracleException e)
{
throw e;
}
finally
{
Close();
}
}
#endregion
#region 执行SQL语句返回受影响的行数
/// <summary>
/// 执行SQL语句返回受影响的行数
/// </summary>
/// <param name="sql">SQL语句</param>
public static int ExecuteSql(string sql)
{
try
{
Open();
OracleCommand cmd = new OracleCommand(sql, con);
int reVal = cmd.ExecuteNonQuery();
cmd.Dispose();
return reVal;
}
catch (OracleException e)
{
throw e;
}
finally
{
Close();
}
}
#endregion
#region 执行SQL语句，返回数据到DataSet中
/// <summary>
/// 执行SQL语句，返回数据到DataSet中
/// </summary>
/// <param name="sql">sql语句</param>
/// <returns>返回DataSet</returns>
public static DataSet GetDataSet(string sql)
{
try
{
Open();//打开数据连接
DataSet ds = new DataSet();
OracleDataAdapter adapter = new OracleDataAdapter(sql, con);
adapter.Fill(ds);
adapter.Dispose();
return ds;
}
catch (OracleException e)
{
throw e;
}
finally
{
Close();//关闭数据库连接
}
}
#endregion
#region 执行SQL语句，返回数据到自定义DataSet中
/// <summary>
/// 执行SQL语句，返回数据到DataSet中
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="DataSetName">自定义返回的DataSet表名</param>
/// <returns>返回DataSet</returns>
public static DataSet GetDataSet(string sql, string DataSetName)
{
try
{
Open();
DataSet ds = new DataSet();
OracleDataAdapter adapter = new OracleDataAdapter(sql, con);
adapter.Fill(ds, DataSetName);
adapter.Dispose();
return ds;
}
catch (OracleException e)
{
throw e;
}
finally
{
Close();
}
}
#endregion
#region 执行Sql语句,返回带分页功能的自定义dataset----数据量不大的情况可以用
/// <summary>
/// 执行Sql语句,返回带分页功能的自定义dataset
/// </summary>
/// <param name="sql">Sql语句</param>
/// <param name="PageSize">每页显示记录数</param>
/// <param name="CurrPageIndex">当前页</param>
/// <param name="DataSetName">返回dataset表名</param>
/// <returns>返回DataSet</returns>
public static DataSet GetDataSet(string sql, int PageSize, int CurrPageIndex, string DataSetName)
{
try
{
Open();//打开数据连接
DataSet ds = new DataSet();
OracleDataAdapter adapter = new OracleDataAdapter(sql, con);
adapter.Fill(ds, PageSize * (CurrPageIndex - 1), PageSize, DataSetName);
adapter.Dispose();
return ds;
}
catch (OracleException e)
{
throw e;
}
finally
{
Close();//关闭数据库连接
}
}
#endregion
#region 执行SQL语句，返回记录总数
/// <summary>
/// 执行SQL语句，返回记录总数
/// </summary>
/// <param name="sql">sql语句</param>
/// <returns>返回记录总条数</returns>
public static int GetRecordCount(string sql)
{
try
{
Open();//打开数据连接
int recordCount = 0;
OracleCommand command = new OracleCommand(sql, con);
OracleDataReader dataReader = command.ExecuteReader();
while (dataReader.Read())
{
recordCount++;
}
dataReader.Close();
command.Dispose();
return recordCount;
}
catch (OracleException e)
{
throw e;
}
finally
{
Close();//关闭数据库连接
}
}
#endregion
#region 取当前序列,条件为seq.nextval或seq.currval
/// <summary>
/// 取当前序列
/// </summary>
/// <param name="seqstr"></param>
/// <param name="table"></param>
/// <returns></returns>
public static decimal GetSeq(string seqstr)
{
try
{
Open();
decimal seqnum = 0;
string sql = "select " + seqstr + " from dual";
OracleCommand command = new OracleCommand(sql, con);
OracleDataReader dataReader = command.ExecuteReader();
if (dataReader.Read())
{
seqnum = decimal.Parse(dataReader[0].ToString());
}
dataReader.Close();
dataReader.Dispose();
command.Dispose();
return seqnum;
}
catch (OracleException e)
{
throw e;
}
finally
{
Close();
}
}
#endregion
#region 统计某表记录总数
/// <summary>
/// 统计某表记录总数
/// </summary>
/// <param name="KeyField">主键/索引键</param>
/// <param name="TableName">数据库.用户名.表名</param>
/// <param name="Condition">查询条件不带where</param>
/// <returns>返回记录总数</returns>
public static int GetRecordCount(string keyField, string tableName, string condition)
{
try
{
int RecordCount = 0;
string sql = "select count(" + keyField + ") as count from " + tableName + " where " + condition;
DataSet ds = GetDataSet(sql);
if (ds.Tables[0].Rows.Count > 0)
{
RecordCount = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
}
ds.Clear();
ds.Dispose();
return RecordCount;
}
catch (OracleException e)
{
throw e;
}
}
/// <summary>
/// 统计某表记录总数
/// </summary>
/// <param name="Field">可重复的字段</param>
/// <param name="tableName">数据库.用户名.表名</param>
/// <param name="condition">查询条件</param>
/// <param name="flag">字段是否主键</param>
/// <returns>返回记录总数</returns>
public int GetRecordCount(string Field, string tableName, string condition, bool flag)
{
try
{
int RecordCount = 0;
if (flag)
{
RecordCount = GetRecordCount(Field, tableName, condition);
}
else
{
string sql = "select count(distinct(" + Field + ")) as count from " + tableName + " where " + condition;
DataSet ds = GetDataSet(sql);
if (ds.Tables[0].Rows.Count > 0)
{
RecordCount = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
}
ds.Clear();
ds.Dispose();
}
return RecordCount;
}
catch (OracleException e)
{
throw e;
}
}
#endregion
#region 执行存储过程，返回影响的行数
/// <summary>
/// 执行存储过程，返回影响的行数
/// </summary>
/// <param name="procedureName">存储过程名</param>
/// <param name="parameters">参数</param>
/// <returns></returns>
public static int ExecuteProcedure(string procedureName, OracleParameter[] parameters)
{
try
{
Open();
OracleCommand command = new OracleCommand();
command.CommandText = procedureName;
command.Connection = con;
command.CommandType = CommandType.StoredProcedure;
foreach (OracleParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
int reVal = command.ExecuteNonQuery();
command.Dispose();
return reVal;
}
catch (OracleException e)
{
throw e;
}
finally
{
Close();
}
}
#endregion
#region 执行存储过程，返回影响的行数和输入参数
/// <summary>
/// 执行存储过程，返回影响的行数和输出参数
/// </summary>
/// <param name="procedureName">存储过程名称</param>
/// <param name="parameters">输入参数</param>
/// <param name="ReOut">out参数返回值</param>
/// <returns>int所影响的行数</returns>
public static int ExecuteProcedure(string procedureName, OracleParameter[] parameters, out object ReOut)
{
try
{
Open();
OracleCommand command = new OracleCommand();
command.Connection = con;
command.CommandText = procedureName;
command.CommandType = CommandType.StoredProcedure;
foreach (OracleParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
int reVal = command.ExecuteNonQuery();
ReOut = command.Parameters["Out"];
command.Dispose();
return reVal;
}
catch (OracleException e)
{
throw e;
}
finally
{
Close();
}
}
#endregion
#region 执行存储过程，返回DataSet
/// <summary>
/// 执行存储过程，返回DataSet
/// </summary>
/// <param name="procedureName">存储过程名称</param>
/// <param name="parameters">存储过程参数</param>
/// <returns>DataSet</returns>
public static DataSet GetProcedure(string procedureName, string table, OracleParameter[] parameters)
{
try
{
Open();
DataSet ds = new DataSet();
OracleCommand command = new OracleCommand();
command.Connection = con;
command.CommandText = procedureName;
command.CommandType = CommandType.StoredProcedure;
foreach (OracleParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
OracleDataAdapter adapter = new OracleDataAdapter(command);
adapter.Fill(ds, table);
adapter.Dispose();
command.Dispose();
return ds;
}
catch (OracleException e)
{
throw e;
}
finally
{
Close();
}
}
#endregion
/// <summary>
///执行读
/// </summary>
private static OracleDataReader ExecuteReader(string procedureName, OracleParameter[] parameters)
{
try
{
Open();
OracleCommand command = new OracleCommand();
command.Connection = con;
command.CommandText = procedureName;
command.CommandType = CommandType.StoredProcedure;
foreach (OracleParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
OracleDataReader dr;
dr = command.ExecuteReader((CommandBehavior)((int)CommandBehavior.CloseConnection));
return (OracleDataReader)dr;
}
catch (OracleException e)
{
throw e;
}
finally
{
Close();
}
}
/// <summary>
///执行读
/// </summary>
private enum OracleConnectionOwnership
{
/// <summary>Connection is owned and managed by OracleHelper</summary>
Internal,
/// <summary>Connection is owned and managed by the caller</summary>
External
}
}
OracleHelper.cs 封装文件：这是.NET 中的方法 用于执行存储过程
// ===================================================================
//
//====================================================================
// 文件：OracleHelper.cs
// 项目名称：通用OracleHeper
// 创建时间：2013-10-14
// 负责人：黄雪亮
// ===================================================================
using System;
using System.Data;
using System.Xml;
using System.Data.OracleClient;
using System.Collections;
namespace Comm
{
/// <summary>
///数据库访问类
/// </summary>
public sealed class OracleHelper
{
#region private utility methods & constructors
//数据库构造函数
private OracleHelper() {}
/// <summary>
/// command:命令，commandParameters：命令参数
/// </summary>
private static void AttachParameters(OracleCommand command, OracleParameter[] commandParameters)
{
foreach (OracleParameter p in commandParameters)
{
//check for derived output value with no value assigned
if ((p.Direction == ParameterDirection.InputOutput) && (p.Value == null))
{
p.Value = DBNull.Value;
}
command.Parameters.Add(p);
}
}
/// <summary>
/// commandParameters：命令参数，command:命令参数值
/// </summary>
private static void AssignParameterValues(OracleParameter[] commandParameters, object[] parameterValues)
{
if ((commandParameters == null) || (parameterValues == null))
{
return;
}
if (commandParameters.Length != parameterValues.Length)
{
throw new ArgumentException("Parameter count does not match Parameter Value count.");
}
for (int i = 0, j = commandParameters.Length; i < j; i++)
{
commandParameters[i].Value = parameterValues[i];
}
}
/// <summary>
/// This method opens (if necessary) and assigns a connection, transaction, command type and parameters
/// to the provided command.
/// </summary>
/// <param name="command">命令</param>
/// <param name="connection">连接</param>
/// <param name="transaction">事务</param>
/// <param name="commandType">命令类型 </param>
/// <param name="commandText">命令文本</param>
/// <param name="commandParameters">命令参数</param>
private static void PrepareCommand(OracleCommand command, OracleConnection connection, OracleTransaction transaction, CommandType commandType, string commandText, OracleParameter[] commandParameters)
{
//if the provided connection is not open, we will open it
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
//associate the connection with the command
command.Connection = connection;
//set the command text (stored procedure name or Oracle statement)
command.CommandText = commandText;
//if we were provided a transaction, assign it.
if (transaction != null)
{
command.Transaction = transaction;
}
//set the command type
command.CommandType = commandType;
//attach the command parameters if they are provided
if (commandParameters != null)
{
AttachParameters(command, commandParameters);
}
return;
}
#endregion private utility methods & constructors
#region ExecuteNonQuery
/// <summary>
///执行命令
/// </summary>
public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText)
{
return ExecuteNonQuery(connectionString, commandType, commandText, (OracleParameter[])null);
}
/// <summary>
///执行命令
/// </summary>
public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
{
//create & open an OracleConnection, and dispose of it after we are done.
using (OracleConnection cn = new OracleConnection(connectionString))
{
cn.Open();
//call the overload that takes a connection in place of the connection string
return ExecuteNonQuery(cn, commandType, commandText, commandParameters);
}
}
/// <summary>
///执行命令
/// </summary>
public static int ExecuteNonQuery(string connectionString, string spName, params object[] parameterValues)
{
//if we got parameter values, we need to figure out where they go
if ((parameterValues != null) && (parameterValues.Length > 0))
{
//pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
OracleParameter[] commandParameters = OracleHelperParameterCache.GetSpParameterSet(connectionString, spName);
//assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues);
//call the overload that takes an array of OracleParameters
return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName, commandParameters);
}
//otherwise we can just call the SP without params
else
{
return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName);
}
}
/// <summary>
///执行命令
/// </summary>
public static int ExecuteNonQuery(OracleConnection connection, CommandType commandType, string commandText)
{
//pass through the call providing null for the set of OracleParameters
return ExecuteNonQuery(connection, commandType, commandText, (OracleParameter[])null);
}
/// <summary>
///执行命令
/// </summary>
public static int ExecuteNonQuery(OracleConnection connection, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
{
//create a command and prepare it for execution
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, connection, (OracleTransaction)null, commandType, commandText, commandParameters);
//finally, execute the command.
return cmd.ExecuteNonQuery();
}
/// <summary>
///执行命令
/// </summary>
public static int ExecuteNonQuery(OracleConnection connection, string spName, params object[] parameterValues)
{
//if we got parameter values, we need to figure out where they go
if ((parameterValues != null) && (parameterValues.Length > 0))
{
//pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
OracleParameter[] commandParameters = OracleHelperParameterCache.GetSpParameterSet(connection.ConnectionString, spName);
//assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues);
//call the overload that takes an array of OracleParameters
return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName, commandParameters);
}
//otherwise we can just call the SP without params
else
{
return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName);
}
}
/// <summary>
///执行命令
/// </summary>
public static int ExecuteNonQuery(OracleTransaction transaction, CommandType commandType, string commandText)
{
//pass through the call providing null for the set of OracleParameters
return ExecuteNonQuery(transaction, commandType, commandText, (OracleParameter[])null);
}
/// <summary>
///执行命令
/// </summary>
public static int ExecuteNonQuery(OracleTransaction transaction, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
{
//create a command and prepare it for execution
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);
//finally, execute the command.
return cmd.ExecuteNonQuery();
}
/// <summary>
///执行命令
/// </summary>
public static int ExecuteNonQuery(OracleTransaction transaction, string spName, params object[] parameterValues)
{
//if we got parameter values, we need to figure out where they go
if ((parameterValues != null) && (parameterValues.Length > 0))
{
//pull the parameters for this stored procedure from the parameter cache (or discover them & populet the cache)
OracleParameter[] commandParameters = OracleHelperParameterCache.GetSpParameterSet(transaction.Connection.ConnectionString, spName);
//assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues);
//call the overload that takes an array of OracleParameters
return ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName, commandParameters);
}
//otherwise we can just call the SP without params
else
{
return ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName);
}
}
#endregion ExecuteNonQuery
#region ExecuteDataSet
/// <summary>
///返回Dataset
/// </summary>
public static void ExecuteDataset(OracleConnection connection, DataSet ds, string table, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
{
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, connection, (OracleTransaction)null, commandType, commandText, commandParameters);
OracleDataAdapter da = new OracleDataAdapter(cmd);
da.Fill(ds, table);
cmd.Parameters.Clear();
}
/// <summary>
///返回Dataset
/// </summary>
public static void ExecuteDataset(string connectionString, DataSet ds, string table, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
{
using (OracleConnection cn = new OracleConnection(connectionString))
{
cn.Open();
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, cn, (OracleTransaction)null, commandType, commandText, commandParameters);
OracleDataAdapter da = new OracleDataAdapter(cmd);
da.Fill(ds, table);
cmd.Parameters.Clear();
}
}
/// <summary>
///返回Dataset
/// </summary>
public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText)
{
//pass through the call providing null for the set of OracleParameters
return ExecuteDataset(connectionString, commandType, commandText, (OracleParameter[])null);
}
/// <summary>
///返回Dataset
/// </summary>
public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
{
//create & open an OracleConnection, and dispose of it after we are done.
using (OracleConnection cn = new OracleConnection(connectionString))
{
cn.Open();
//call the overload that takes a connection in place of the connection string
return ExecuteDataset(cn, commandType, commandText, commandParameters);
}
}
/// <summary>
///返回Dataset
/// </summary>
public static DataSet ExecuteDataset(string connectionString, string spName, params object[] parameterValues)
{
//if we got parameter values, we need to figure out where they go
if ((parameterValues != null) && (parameterValues.Length > 0))
{
//pull the parameters for this stored procedure from the parameter cache (or discover them & populet the cache)
OracleParameter[] commandParameters = OracleHelperParameterCache.GetSpParameterSet(connectionString, spName);
//assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues);
//call the overload that takes an array of OracleParameters
return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName, commandParameters);
}
//otherwise we can just call the SP without params
else
{
return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName);
}
}
/// <summary>
///返回Dataset
/// </summary>
public static DataSet ExecuteDataset(OracleConnection connection, CommandType commandType, string commandText)
{
//pass through the call providing null for the set of OracleParameters
return ExecuteDataset(connection, commandType, commandText, (OracleParameter[])null);
}
/// <summary>
///返回Dataset
/// </summary>
public static DataSet ExecuteDataset(OracleConnection connection, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
{
//create a command and prepare it for execution
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, connection, (OracleTransaction)null, commandType, commandText, commandParameters);
//create the DataAdapter & DataSet
OracleDataAdapter da = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();
ds.Locale = System.Globalization.CultureInfo.InvariantCulture;
//fill the DataSet using default values for DataTable names, etc.
da.Fill(ds);
//return the dataset
return ds;
}
/// <summary>
///返回Dataset
/// </summary>
public static DataSet ExecuteDataset(OracleConnection connection, string spName, params object[] parameterValues)
{
//if we got parameter values, we need to figure out where they go
if ((parameterValues != null) && (parameterValues.Length > 0))
{
//pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
OracleParameter[] commandParameters = OracleHelperParameterCache.GetSpParameterSet(connection.ConnectionString, spName);
//assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues);
//call the overload that takes an array of OracleParameters
return ExecuteDataset(connection, CommandType.StoredProcedure, spName, commandParameters);
}
//otherwise we can just call the SP without params
else
{
return ExecuteDataset(connection, CommandType.StoredProcedure, spName);
}
}
/// <summary>
///返回Dataset
/// </summary>
public static DataSet ExecuteDataset(OracleTransaction transaction, CommandType commandType, string commandText)
{
//pass through the call providing null for the set of OracleParameters
return ExecuteDataset(transaction, commandType, commandText, (OracleParameter[])null);
}
/// <summary>
///返回Dataset
/// </summary>
public static DataSet ExecuteDataset(OracleTransaction transaction, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
{
//create a command and prepare it for execution
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);
//create the DataAdapter & DataSet
OracleDataAdapter da = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();
ds.Locale = System.Globalization.CultureInfo.InvariantCulture;
//fill the DataSet using default values for DataTable names, etc.
da.Fill(ds);
//return the dataset
return ds;
}
/// <summary>
///返回Dataset
/// </summary>
public static DataSet ExecuteDataset(OracleTransaction transaction, string spName, params object[] parameterValues)
{
//if we got parameter values, we need to figure out where they go
if ((parameterValues != null) && (parameterValues.Length > 0))
{
//pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
OracleParameter[] commandParameters = OracleHelperParameterCache.GetSpParameterSet(transaction.Connection.ConnectionString, spName);
//assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues);
//call the overload that takes an array of OracleParameters
return ExecuteDataset(transaction, CommandType.StoredProcedure, spName, commandParameters);
}
//otherwise we can just call the SP without params
else
{
return ExecuteDataset(transaction, CommandType.StoredProcedure, spName);
}
}
#endregion ExecuteDataSet
#region ExecuteReader
/// <summary>
///执行读
/// </summary>
private enum OracleConnectionOwnership
{
/// <summary>Connection is owned and managed by OracleHelper</summary>
Internal,
/// <summary>Connection is owned and managed by the caller</summary>
External
}
/// <summary>
///执行读
/// </summary>
private static OracleDataReader ExecuteReader(OracleConnection connection, OracleTransaction transaction, CommandType commandType, string commandText, OracleParameter[] commandParameters, OracleConnectionOwnership connectionOwnership)
{
//create a command and prepare it for execution
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters);
//create a reader
OracleDataReader dr;
// call ExecuteReader with the appropriate CommandBehavior
if (connectionOwnership == OracleConnectionOwnership.External)
{
dr = cmd.ExecuteReader();
}
else
{
dr = cmd.ExecuteReader((CommandBehavior)((int)CommandBehavior.CloseConnection));
}
return (OracleDataReader) dr;
}
/// <summary>
///执行读
/// </summary>
public static OracleDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText)
{
//pass through the call providing null for the set of OracleParameters
return ExecuteReader(connectionString, commandType, commandText, (OracleParameter[])null);
}
/// <summary>
///执行读
/// </summary>
public static OracleDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
{
//create & open an OraclebConnection
OracleConnection cn = new OracleConnection(connectionString);
cn.Open();
try
{
//call the private overload that takes an internally owned connection in place of the connection string
return ExecuteReader(cn, null, commandType, commandText, commandParameters, OracleConnectionOwnership.Internal);
}
catch
{
//if we fail to return the OracleDataReader, we need to close the connection ourselves
cn.Close();
throw;
}
}
/// <summary>
///执行读
/// </summary>
public static OracleDataReader ExecuteReader(string connectionString, string spName, params object[] parameterValues)
{
//if we got parameter values, we need to figure out where they go
if ((parameterValues != null) && (parameterValues.Length > 0))
{
//pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
OracleParameter[] commandParameters = OracleHelperParameterCache.GetSpParameterSet(connectionString, spName);
//assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues);
//call the overload that takes an array of OracleParameters
return ExecuteReader(connectionString, CommandType.StoredProcedure, spName, commandParameters);
}
//otherwise we can just call the SP without params
else
{
return ExecuteReader(connectionString, CommandType.StoredProcedure, spName);
}
}
/// <summary>
///执行读
/// </summary>
public static OracleDataReader ExecuteReader(OracleConnection connection, CommandType commandType, string commandText)
{
//pass through the call providing null for the set of OracleParameters
return ExecuteReader(connection, commandType, commandText, (OracleParameter[])null);
}
/// <summary>
///执行读
/// </summary>
public static OracleDataReader ExecuteReader(OracleConnection connection, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
{
//pass through the call to the private overload using a null transaction value and an externally owned connection
return ExecuteReader(connection, (OracleTransaction)null, commandType, commandText, commandParameters, OracleConnectionOwnership.External);
}
/// <summary>
///执行读
/// </summary>
public static OracleDataReader ExecuteReader(OracleConnection connection, string spName, params object[] parameterValues)
{
//if we got parameter values, we need to figure out where they go
if ((parameterValues != null) && (parameterValues.Length > 0))
{
OracleParameter[] commandParameters = OracleHelperParameterCache.GetSpParameterSet(connection.ConnectionString, spName);
AssignParameterValues(commandParameters, parameterValues);
return ExecuteReader(connection, CommandType.StoredProcedure, spName, commandParameters);
}
//otherwise we can just call the SP without params
else
{
return ExecuteReader(connection, CommandType.StoredProcedure, spName);
}
}
/// <summary>
///执行读
/// </summary>
public static OracleDataReader ExecuteReader(OracleTransaction transaction, CommandType commandType, string commandText)
{
//pass through the call providing null for the set of OracleParameters
return ExecuteReader(transaction, commandType, commandText, (OracleParameter[])null);
}
/// <summary>
///执行读
/// </summary>
public static OracleDataReader ExecuteReader(OracleTransaction transaction, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
{
//pass through to private overload, indicating that the connection is owned by the caller
return ExecuteReader(transaction.Connection, transaction, commandType, commandText, commandParameters, OracleConnectionOwnership.External);
}
/// <summary>
///执行读
/// </summary>
public static OracleDataReader ExecuteReader(OracleTransaction transaction, string spName, params object[] parameterValues)
{
//if we got parameter values, we need to figure out where they go
if ((parameterValues != null) && (parameterValues.Length > 0))
{
OracleParameter[] commandParameters = OracleHelperParameterCache.GetSpParameterSet(transaction.Connection.ConnectionString, spName);
AssignParameterValues(commandParameters, parameterValues);
return ExecuteReader(transaction, CommandType.StoredProcedure, spName, commandParameters);
}
//otherwise we can just call the SP without params
else
{
return ExecuteReader(transaction, CommandType.StoredProcedure, spName);
}
}
#endregion ExecuteReader
#region ExecuteScalar
/// <summary>
///执行读
/// </summary>
public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText)
{
//pass through the call providing null for the set of OracleParameters
return ExecuteScalar(connectionString, commandType, commandText, (OracleParameter[])null);
}
/// <summary>
///执行读
/// </summary>
public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
{
//create & open an OracleConnection, and dispose of it after we are done.
using (OracleConnection cn = new OracleConnection(connectionString))
{
cn.Open();
//call the overload that takes a connection in place of the connection string
return ExecuteScalar(cn, commandType, commandText, commandParameters);
}
}
/// <summary>
///执行读
/// </summary>
public static object ExecuteScalar(string connectionString, string spName, params object[] parameterValues)
{
//if we got parameter values, we need to figure out where they go
if ((parameterValues != null) && (parameterValues.Length > 0))
{
//pull the parameters for this stored procedure from the parameter cache (or discover them & populet the cache)
OracleParameter[] commandParameters = OracleHelperParameterCache.GetSpParameterSet(connectionString, spName);
//assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues);
//call the overload that takes an array of OracleParameters
return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName, commandParameters);
}
//otherwise we can just call the SP without params
else
{
return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName);
}
}
/// <summary>
///执行读
/// </summary>
public static object ExecuteScalar(OracleConnection connection, CommandType commandType, string commandText)
{
//pass through the call providing null for the set of OracleParameters
return ExecuteScalar(connection, commandType, commandText, (OracleParameter[])null);
}
/// <summary>
///执行读
/// </summary>
public static object ExecuteScalar(OracleConnection connection, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
{
//create a command and prepare it for execution
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, connection, (OracleTransaction)null, commandType, commandText, commandParameters);
//execute the command & return the results
return cmd.ExecuteScalar();
}
/// <summary>
///执行读
/// </summary>
public static object ExecuteScalar(OracleConnection connection, string spName, params object[] parameterValues)
{
//if we got parameter values, we need to figure out where they go
if ((parameterValues != null) && (parameterValues.Length > 0))
{
//pull the parameters for this stored procedure from the parameter cache (or discover them & populet the cache)
OracleParameter[] commandParameters = OracleHelperParameterCache.GetSpParameterSet(connection.ConnectionString, spName);
//assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues);
//call the overload that takes an array of OracleParameters
return ExecuteScalar(connection, CommandType.StoredProcedure, spName, commandParameters);
}
//otherwise we can just call the SP without params
else
{
return ExecuteScalar(connection, CommandType.StoredProcedure, spName);
}
}
/// <summary>
///执行读
/// </summary>
public static object ExecuteScalar(OracleTransaction transaction, CommandType commandType, string commandText)
{
//pass through the call providing null for the set of OracleParameters
return ExecuteScalar(transaction, commandType, commandText, (OracleParameter[])null);
}
/// <summary>
///执行读
/// </summary>
public static object ExecuteScalar(OracleTransaction transaction, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
{
//create a command and prepare it for execution
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);
//execute the command & return the results
return cmd.ExecuteScalar();
}
/// <summary>
///执行读
/// </summary>
public static object E |
客服电话
APP下载
官方微信
请发表评论