.net的SQL参数传递实现
来源:互联网 发布:淘宝其他费用 编辑:程序博客网 时间:2024/06/06 07:12
参考Petshop 4 设计(http://www.cnblogs.com/Files/ltc31/Microsoft%20.NET%20Pet%20Shop%204.0.rar
),本文主要演示的是怎么准备参数和传递。数据库是Oracle。部分代码如下(用到的其他相关配置参见上篇文档):
其中演示代码如下:
测试通过!
上面的过程只有查询,怎样做其他的操作?大概代码如下:页面测试代码如下:
测试通过!
),本文主要演示的是怎么准备参数和传递。数据库是Oracle。部分代码如下(用到的其他相关配置参见上篇文档):
/// <summary>
/// Execute a select query that will return a result set
/// </summary>
//// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or PL/SQL command</param>
/// <param name="commandParameters">an array of OracleParamters used to execute the command</param>
/// <returns></returns>
public override OracleDataReader ExecuteReader(CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
{
//Create the command and connection
OracleCommand cmd = new OracleCommand();
OracleConnection conn = this.conn;
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;
}
}
/// <summary>
/// Internal function to prepare a command for execution by the database
/// </summary>
/// <param name="cmd">Existing command object</param>
/// <param name="conn">Database connection object</param>
/// <param name="trans">Optional transaction object</param>
/// <param name="cmdType">Command type, e.g. stored procedure</param>
/// <param name="cmdText">Command test</param>
/// <param name="commandParameters">Parameters for the command</param>
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);
}
}
/// Execute a select query that will return a result set
/// </summary>
//// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or PL/SQL command</param>
/// <param name="commandParameters">an array of OracleParamters used to execute the command</param>
/// <returns></returns>
public override OracleDataReader ExecuteReader(CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
{
//Create the command and connection
OracleCommand cmd = new OracleCommand();
OracleConnection conn = this.conn;
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;
}
}
/// <summary>
/// Internal function to prepare a command for execution by the database
/// </summary>
/// <param name="cmd">Existing command object</param>
/// <param name="conn">Database connection object</param>
/// <param name="trans">Optional transaction object</param>
/// <param name="cmdType">Command type, e.g. stored procedure</param>
/// <param name="cmdText">Command test</param>
/// <param name="commandParameters">Parameters for the command</param>
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);
}
}
其中演示代码如下:
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OracleClient;
using HHSCInfor.App_Code.Database;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
AbsDB conn = DBConn.GetDBConn();
OracleParameter para1 = new OracleParameter(":gnbh",3);
OracleParameter para2 = new OracleParameter(":flag", "0");
OracleParameter[] paraValue ={ para1, para2 };
OracleDataReader dr = conn.ExecuteReader(0, "select * from sysFunction where 功能编号=:gnbh and 标志=:flag", paraValue);
while(dr.Read())
{
Label1.Text = (String)dr.GetValue(1);
break;
}
conn.Close();
}
}
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OracleClient;
using HHSCInfor.App_Code.Database;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
AbsDB conn = DBConn.GetDBConn();
OracleParameter para1 = new OracleParameter(":gnbh",3);
OracleParameter para2 = new OracleParameter(":flag", "0");
OracleParameter[] paraValue ={ para1, para2 };
OracleDataReader dr = conn.ExecuteReader(0, "select * from sysFunction where 功能编号=:gnbh and 标志=:flag", paraValue);
while(dr.Read())
{
Label1.Text = (String)dr.GetValue(1);
break;
}
conn.Close();
}
}
上面的过程只有查询,怎样做其他的操作?大概代码如下:
public override int ExeSql(CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
{
//创建命令
OracleCommand cmd = new OracleCommand();
OracleConnection connection = this.conn;
//准备命令
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
//执行命令
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
//成功返回1 ,否则返回0
return val;
}
{
//创建命令
OracleCommand cmd = new OracleCommand();
OracleConnection connection = this.conn;
//准备命令
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
//执行命令
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
//成功返回1 ,否则返回0
return val;
}
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OracleClient;
using HHSCInfor.App_Code.Database;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
AbsDB conn = DBConn.GetDBConn();
OracleParameter para1 = new OracleParameter(":gnbh",3);
OracleParameter para2 = new OracleParameter(":flag", "1");
OracleParameter[] paraValue ={ para1, para2 };
int resultFlag = conn.ExeSql(0, "update sysFunction set 标志 = 0 where 功能编号=:gnbh and 标志=:flag", paraValue);
///下面的将0 换成枚举型显示
///int resultFlag = conn.ExeSql(CommandType.Text, "update sysFunction set 标志 = 0 where 功能 /// 编号=:gnbh and 标志=:flag", paraValue);
Label1.Text = resultFlag.ToString();
conn.Close();
}
}
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OracleClient;
using HHSCInfor.App_Code.Database;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
AbsDB conn = DBConn.GetDBConn();
OracleParameter para1 = new OracleParameter(":gnbh",3);
OracleParameter para2 = new OracleParameter(":flag", "1");
OracleParameter[] paraValue ={ para1, para2 };
int resultFlag = conn.ExeSql(0, "update sysFunction set 标志 = 0 where 功能编号=:gnbh and 标志=:flag", paraValue);
///下面的将0 换成枚举型显示
///int resultFlag = conn.ExeSql(CommandType.Text, "update sysFunction set 标志 = 0 where 功能 /// 编号=:gnbh and 标志=:flag", paraValue);
Label1.Text = resultFlag.ToString();
conn.Close();
}
}
- .net的SQL参数传递实现
- [NET]asp.net如何实现页面间的参数传递
- .net如何实现页面间的参数传递
- .net如何实现页面间的参数传递
- .net如何实现页面间的参数传递
- 在ASP.NET中实现页面间的参数传递
- Asp.net如何实现页面间的参数传递
- 在ASP.NET中实现页面间的参数传递
- 在ASP.NET中实现页面间的参数传递
- 在ASP.NET中实现页面间的参数传递
- ASP.NET中实现页面间的参数传递
- ASP.NET中实现页面间的参数传递
- .net如何实现页面间的参数传递
- asp.net中实现页面间的参数传递
- .net如何实现页面间的参数传递
- ADF实现SQL,传递参数查询sql
- sql exec 参数的传递
- 【C#.NET】ASP.NET中实现页面间的参数传递 QueryString\Application\Session\Cookie
- 瑞星,請問我的哪封郵件才不是垃圾郵件?
- 超超超经典的 SQL 语句 大全
- Ajax核心:XMLHTTP组件相关技术资料
- Oracle 分析函数的使用
- 半小时教你学会正则表达式
- .net的SQL参数传递实现
- DataGrid使用----删除数据前弹出提示框
- C#常用函数和方法集汇总
- C#图片处理之:亮度和对比度的校正
- ASP.NET C# 获取IP与MAC
- MicroTip#4 const Args: array of ... 的应用
- C#读取excel表
- 不用在xwindow 下启服务的修改
- C# 数据采集进展(实验储备)