ASP.NET使用简便实用的OracleHelper.cs
发布日期:2014-03-08 21:42:04
本人感觉简便实用的一个OracleHelper.cs代码如下:using System;
using System.Configuration;
using System.Data;
using System.Collections;
using System.Data.SqlClient;
using System.Data.OracleClient;
namespace TWH.DBUtility
{
public abstract class OracleHelper
{
public static readonly string TwHotelConnString = ConfigurationManager.ConnectionStrings["TwHotelConnString"].ConnectionString;
private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
public static int ExecuteNonQuery(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);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
}
public static DataTable ExecuteDataTable(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
{
OracleCommand cmd = new OracleCommand();
OracleConnection conn = new OracleConnection(connectionString);
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
OracleDataAdapter da = new OracleDataAdapter();
DataTable dt = new DataTable();
da.SelectCommand = cmd;
da.Fill(dt);
return dt;
}
finally
{
conn.Close();
}
}
public static DataSet ExecuteDataSet(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
{
OracleCommand cmd = new OracleCommand();
OracleConnection conn = new OracleConnection(connectionString);
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
OracleDataAdapter da = new OracleDataAdapter();
DataSet ds = new DataSet();
da.SelectCommand = cmd;
da.Fill(ds);
return ds;
}
finally
{
conn.Close();
}
}
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();
connection.Close();
return val;
}
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();
trans.Connection.Close();
return val;
}
public static OracleDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
{
OracleCommand cmd = new OracleCommand();
OracleConnection conn = new OracleConnection(connectionString);
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
OracleDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return rdr;
}
catch
{
conn.Close();
throw;
}
//finally
//{
// conn.Close();
//}
}
public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
{
OracleCommand cmd = new OracleCommand();
using (OracleConnection connection = new OracleConnection(connectionString))
{
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}
}
public static object ExecuteScalar(OracleConnection connection, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
{
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
connection.Close();
return val;
}
public static void CacheParameters(string cacheKey, params OracleParameter[] commandParameters)
{
parmCache[cacheKey] = commandParameters;
}
public static OracleParameter[] GetCachedParameters(string cacheKey)
{
OracleParameter[] cachedParms = (OracleParameter[])parmCache[cacheKey];
if (cachedParms == null)
return null;
OracleParameter[] clonedParms = new OracleParameter[cachedParms.Length];
for (int i = 0, j = cachedParms.Length; i < j; i++)
clonedParms[i] = (OracleParameter)((ICloneable)cachedParms[i]).Clone();
return clonedParms;
}
private static void PrepareCommand(OracleCommand cmd, OracleConnection conn, OracleTransaction trans, CommandType cmdType, string cmdText, OracleParameter[] cmdParms)
{
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.CommandTimeout = 180;
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trans != null)
cmd.Transaction = trans;
cmd.CommandType = cmdType;
if (cmdParms != null)
{
foreach (OracleParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
}
}
}
使用DATATABLE例子如下:
前台代码:
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="TwHotel._Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>读取EMP</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Repeater ID="Repeater1" runat="server">
<ItemTemplate>
<ul>
<li><%# Eval("ENAME") %> --- <%# Eval("JOB") %></li>
</ul>
</ItemTemplate>
</asp:Repeater>
</div>
</form>
</body>
</html>
后台代码:
using System;
using System.Data;
using System.Configuration;
using System.Collections;
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.Text;
using TWH.DBUtility;
namespace TWH
{
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
string strSql = "select * from EMP";
DataTable dt1 = new DataTable();
dt1=SqlHelper.ExecuteDataTable(SqlHelper.TwHotelConnString, CommandType.Text, strSql, null);
if (dt1.Rows.Count > 0)
{
Repeater1.DataSource = dt1;
Repeater1.DataBind();
}
}
}
}
显示结果:
SMITH --- CLERK
ALLEN --- SALESMAN
WARD --- SALESMAN
JONES --- MANAGER
MARTIN --- SALESMAN
BLAKE --- MANAGER
CLARK --- MANAGER
SCOTT --- ANALYST
KING --- MANAGER
TURNER --- SALESMAN
ADAMS --- CLERK
JAMES --- CLERK
FORD --- ANALYST
MILLER --- CLERK
把连接字符串直接作了参数!是不是用起来更简单。