C#操作存储过程的通用封装
using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Collections;
namespace mjq.zy5777.com
{
#region 管理数据库连接类开始
/// <summary>
///管理数据库连接
/// </summary>
public class db
{
public db()
{
//
//TODO: 在此处添加构造函数逻辑
//
}
private static string connStr = "sqlConnStr";
public static string ConnStr
{
get { return connStr; }
set { connStr = value; }
}
public static SqlConnection getConn()
{
cache cat = new cache();
string connStr = cat.getConnStr();
SqlConnection conn = new SqlConnection(connStr);
return conn;
}
public static void openConn(ref SqlConnection conn)
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
}
public static void closeConn(SqlConnection conn)
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
}
#endregion
#region 数据缓存类开始
/// <summary>
/// 缓存数据库连接字符串
/// </summary>
public class cache
{
Hashtable hTable;
public cache()
{
hTable = new Hashtable();
}
/// <summary>
/// 把数据库连接字符串放入缓存
/// </summary>
public void setConnStr()
{
string connStr = db.ConnStr;
string getConnStr = ConfigurationManager.ConnectionStrings[connStr].ConnectionString;
hTable.Add("connStr", getConnStr);
}
/// <summary>
/// 从缓存中获取数据库连接字符串
/// </summary>
/// <returns>数据库连接字符串</returns>
public string getConnStr()
{
if (hTable.Contains("connStr"))
{
return (string)hTable["connStr"];
}
else
{
setConnStr();
return (string)hTable["connStr"];
}
}
}
#endregion
#region 存储过程类开始
/// <summary>
/// 封装存储过程操作
/// </summary>
public class execStoreProce
{
public execStoreProce()
{
//构造函数
}
/// <summary>
/// 生成执行存储过程的SqlCommand
/// </summary>
/// <param name="proce_name">
/// 存储过程名称
/// </param>
/// <param name="conn">引用参数：sqlConnection数据库连接</param>
/// <returns>
/// SqlCommand存储过程命令
/// </returns>
public static SqlCommand createCmd(string proce_name, ref SqlConnection conn,params SqlParameter[] paramters)
{
conn = db.getConn();
db.openConn(ref conn);
SqlCommand cmd = new SqlCommand(proce_name, conn);
cmd.CommandType = CommandType.StoredProcedure;
if (paramters!=null)
{
foreach (SqlParameter para in paramters)
{
cmd.Parameters.Add(para);
}
}
return cmd;
}
/// <summary>
/// 生成执行存储过程的SqlDataAdapter
/// </summary>
/// <param name="proc_name">存储过程名称</param>
/// <param name="cmd">引用参数：sqlCommand命令</param>
/// <param name="conn">引用参数：sqlConnection数据库连接</param>
/// <param name="paramters">存储过程参数</param>
/// <returns>返回sqlDataAdapter存储过程试配器</returns>
public static SqlDataAdapter createAdpt(string proc_name, ref SqlCommand cmd, ref SqlConnection conn, params SqlParameter[] paramters)
{
conn = db.getConn();
db.openConn(ref conn);
SqlDataAdapter adpt = new SqlDataAdapter();
cmd = createCmd(proc_name, ref conn);
if (paramters != null)
{
foreach (SqlParameter para in paramters)
{
cmd.Parameters.Add(para);
}
}
adpt.SelectCommand = cmd;
return adpt;
}
/// <summary>
/// 生成存储过程参数
/// </summary>
/// <param name="paramName"></param>
/// <param name="dbType"></param>
/// <param name="value"></param>
/// <returns></returns>
public static SqlParameter createSqlParameter(string paramName,SqlDbType dbType,Object value)
{
SqlParameter param = new SqlParameter(paramName, dbType);
param.Value = value;
return param;
}
/// <summary>
/// 生成存储过程参数重载方法
/// </summary>
/// <param name="paramName"></param>
/// <param name="dbType"></param>
/// <param name="size">参数长度</param>
/// <param name="value"></param>
/// <returns></returns>
public static SqlParameter createSqlParameter(string paramName, SqlDbType dbType,int size, Object value)
{
SqlParameter param = new SqlParameter(paramName, dbType,size);
param.Value = value;
return param;
}
/// <summary>
/// 生成存储过程参数重载方法
/// </summary>
/// <param name="paramName"></param>
/// <param name="dbType"></param>
/// <param name="size">参数长度</param>
/// <param name="value"></param>
/// <param name="direction">参数类型：out/return</param>
/// <returns></returns>
public static SqlParameter createSqlParameter(string paramName, SqlDbType dbType, int size,ParameterDirection direction)
{
SqlParameter param = new SqlParameter(paramName, dbType, size);
param.Direction = direction;
return param;
}
}
#endregion
}
freedom -