就想要一个SQLHelper 谢谢分享 嘿嘿 分不多 没分了^_^
解决方案 »
- 求助:找不到字段:“System.Web.GlobalPerfCounter.REQUESTS_REJECTED”
- 为什么VS2008自动产生HTML代码
- web rbac 細粒控制請教。。
- 图片缩小后显示
- 我是新手,大家帮帮我!
- 初接触ACCESS的问题!! 请教啊
- 各位实现图片新闻...给一点思路..
- vs2005数据集文件的问
- 页面中要提交的文本框是不是有一定的限制,如果有600个怎么就不能提交了呢?
- 请教在Page_Load时怎样加载一个单元格的Style??
- 删除功能为什么不能实现,是不是语法问题,replace函数。
- FormsAuthentication.SetAuthCookie(context.Request["UserName"],true);
在 download.microsoft.com 搜索 Enterprise Library。
/// <summary>
/// 自定义访问通用类
/// </summary>
public class SqlDbHelper
{
string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["conn"].ConnectionString; /// <summary>
/// 构造函数
/// </summary>
public SqlDbHelper()
{ } /// <summary>
/// ExecuteNonQuery操作,对数据库进行 增、删、改 操作((1)
/// </summary>
/// <param name="sql">要执行的SQL语句</param>
/// <returns></returns>
public int ExecuteNonQuery(string sql)
{
return ExecuteNonQuery(sql, CommandType.Text, null);
} /// <summary>
/// ExecuteNonQuery操作,对数据库进行 增、删、改 操作(2)
/// </summary>
/// <param name="sql">要执行的SQL语句</param>
/// <param name="commandType">要执行的查询类型(存储过程、SQL文本)</param>
/// <returns></returns>
public int ExecuteNonQuery(string sql, CommandType commandType)
{
return ExecuteNonQuery(sql, commandType, null);
} /// <summary>
/// ExecuteNonQuery操作,对数据库进行 增、删、改 操作(3)
/// </summary>
/// <param name="sql">要执行的SQL语句</param>
/// <param name="commandType">要执行的查询类型(存储过程、SQL文本)</param>
/// <param name="parameters">参数数组</param>
/// <returns></returns>
public int ExecuteNonQuery(string sql, CommandType commandType, SqlParameter[] parameters)
{
int count = 0;
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.CommandType = commandType;
if (parameters != null)
{
foreach (SqlParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
}
connection.Open();
count = command.ExecuteNonQuery();
}
}
return count;
} /// <summary>
/// SqlDataAdapter的Fill方法执行一个查询,并返回一个DataSet类型结果(1)
/// </summary>
/// <param name="sql">要执行的SQL语句</param>
/// <returns></returns>
public DataSet ExecuteDataSet(string sql)
{
return ExecuteDataSet(sql, CommandType.Text, null);
} /// <summary>
/// SqlDataAdapter的Fill方法执行一个查询,并返回一个DataSet类型结果(2)
/// </summary>
/// <param name="sql">要执行的SQL语句</param>
/// <param name="commandType">要执行的查询类型(存储过程、SQL文本)</param>
/// <returns></returns>
public DataSet ExecuteDataSet(string sql, CommandType commandType)
{
return ExecuteDataSet(sql, commandType, null);
} /// <summary>
/// SqlDataAdapter的Fill方法执行一个查询,并返回一个DataSet类型结果(3)
/// </summary>
/// <param name="sql">要执行的SQL语句</param>
/// <param name="commandType">要执行的查询类型(存储过程、SQL文本)</param>
/// <param name="parameters">参数数组</param>
/// <returns></returns>
public DataSet ExecuteDataSet(string sql, CommandType commandType, SqlParameter[] parameters)
{
DataSet ds = new DataSet();
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.CommandType = commandType;
if (parameters != null)
{
foreach (SqlParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
}
SqlDataAdapter adapter = new SqlDataAdapter(command);
adapter.Fill(ds);
}
}
return ds;
} /// <summary>
/// SqlDataAdapter的Fill方法执行一个查询,并返回一个DataTable类型结果(1)
/// </summary>
/// <param name="sql">要执行的SQL语句</param>
/// <returns></returns>
public DataTable ExecuteDataTable(string sql)
{
return ExecuteDataTable(sql, CommandType.Text, null);
} /// <summary>
/// SqlDataAdapter的Fill方法执行一个查询,并返回一个DataTable类型结果(2)
/// </summary>
/// <param name="sql">要执行的SQL语句</param>
/// <param name="commandType">要执行的查询类型(存储过程、SQL文本)</param>
/// <returns></returns>
public DataTable ExecuteDataTable(string sql, CommandType commandType)
{
return ExecuteDataTable(sql, commandType, null);
} /// <summary>
/// SqlDataAdapter的Fill方法执行一个查询,并返回一个DataTable类型结果(3)
/// </summary>
/// <param name="sql">要执行的SQL语句</param>
/// <param name="commandType">要执行的查询类型(存储过程、SQL文本)</param>
/// <param name="parameters">参数数组</param>
/// <returns></returns>
public DataTable ExecuteDataTable(string sql, CommandType commandType, SqlParameter[] parameters)
{
DataTable data = new DataTable();
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.CommandType = commandType;
if (parameters != null)
{
foreach (SqlParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
}
SqlDataAdapter adapter = new SqlDataAdapter(command);
adapter.Fill(data);
}
}
return data;
} /// <summary>
/// ExecuteReader执行一查询,返回一SqlDataReader对象实例(1)
/// </summary>
/// <param name="sql">要执行的SQL语句</param>
/// <returns></returns>
public SqlDataReader ExecuteReader(string sql)
{
return ExecuteReader(sql, CommandType.Text, null);
} /// <summary>
/// ExecuteReader执行一查询,返回一SqlDataReader对象实例(2)
/// </summary>
/// <param name="sql">要执行的SQL语句</param>
/// <param name="commandType">要执行的查询类型(存储过程、SQL文本)</param>
/// <returns></returns>
public SqlDataReader ExecuteReader(string sql, CommandType commandType)
{
return ExecuteReader(sql, commandType, null);
} /// <summary>
/// ExecuteReader执行一查询,返回一SqlDataReader对象实例(3)
/// </summary>
/// <param name="sql">要执行的SQL语句</param>
/// <param name="commandType">要执行的查询类型(存储过程、SQL文本)</param>
/// <param name="parameters">参数数组</param>
/// <returns></returns>
public SqlDataReader ExecuteReader(string sql, CommandType commandType, SqlParameter[] parameters)
{
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand command = new SqlCommand(sql, connection);
command.CommandType = commandType;
if (parameters != null)
{
foreach (SqlParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
}
connection.Open();
return command.ExecuteReader(CommandBehavior.CloseConnection);
} /// <summary>
/// ExecuteScalar执行一查询,返回查询结果的第一行第一列(1)
/// </summary>
/// <param name="sql">要执行的SQL语句</param>
/// <returns></returns>
public Object ExecuteScalar(string sql)
{
return ExecuteScalar(sql, CommandType.Text, null);
} /// <summary>
/// ExecuteScalar执行一查询,返回查询结果的第一行第一列(2)
/// </summary>
/// <param name="sql">要执行的SQL语句</param>
/// <param name="commandType">要执行的查询类型(存储过程、SQL文本)</param>
/// <returns></returns>
public Object ExecuteScalar(string sql, CommandType commandType)
{
return ExecuteScalar(sql, commandType, null);
} /// <summary>
/// ExecuteScalar执行一查询,返回查询结果的第一行第一列(3)
/// </summary>
/// <param name="sql">要执行的SQL语句</param>
/// <param name="commandType">要执行的查询类型(存储过程、SQL文本)</param>
/// <returns></returns>
public Object ExecuteScalar(string sql, CommandType commandType, SqlParameter[] parameters)
{
object result = null;
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.CommandType = commandType;
if (parameters != null)
{
foreach (SqlParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
}
connection.Open();
result = command.ExecuteScalar();
}
}
return result;
} /// <summary>
/// 返回当前连接的数据库中所有由用户创建的数据库
/// </summary>
/// <returns></returns>
public DataTable GetTables()
{
DataTable data = null;
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
data = connection.GetSchema("Tables");
}
return data;
}
}
DBAccessusing System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
namespace DBAccess
{
/// <summary>
/// The SqlHelper class is intended to encapsulate high performance,
/// scalable best practices for common uses of SqlClient.
/// </summary>
public abstract class DBAccess
{
//Database connection strings
public readonly string ConnectionString = ConfigurationManager.ConnectionStrings["csdbConnectionString"].ConnectionString; // Hashtable to store cached parameters
private Hashtable parmCache = Hashtable.Synchronized(new Hashtable()); public SqlConnection GetConnection()
{
SqlConnection conn = new SqlConnection(ConnectionString);
conn.Open();
return conn;
} public int ExecuteNonQuery(string cmdText, params SqlParameter[] commandParameters)
{
int val = -1;
using(SqlConnection conn = GetConnection())
{
SqlCommand cmd = CreateCommand(conn, null, cmdText, commandParameters);
val = cmd.ExecuteNonQuery();
}
//cmd.Parameters.Clear();
return val;
} public int ExecuteNonQuery(SqlTransaction trans, string cmdText, params SqlParameter[] commandParameters)
{
int val = -1;
using(SqlConnection conn = GetConnection())
{
SqlCommand cmd = CreateCommand(conn, trans, cmdText, commandParameters);
val = cmd.ExecuteNonQuery();
}
//cmd.Parameters.Clear();
return val;
} public SqlDataReader ExecuteReader(string cmdText, params SqlParameter[] commandParameters)
{
// we use a try/catch here because if the method throws an exception we want to
// close the connection throw code, because no datareader will exist, hence the
// commandBehaviour.CloseConnection will not work
try
{
SqlDataReader rdr = null;
using(SqlConnection conn = GetConnection())
{
SqlCommand cmd = CreateCommand(conn, null, cmdText, commandParameters);
rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
//cmd.Parameters.Clear();
return rdr;
}
catch (Exception ex)
{
//conn.Close();
throw ex;
}
} public object ExecuteScalar(string cmdText, params SqlParameter[] commandParameters)
{
object val = null;
using(SqlConnection conn = GetConnection())
{
SqlCommand cmd = CreateCommand(conn, null, cmdText, commandParameters);
val = cmd.ExecuteScalar();
}
//cmd.Parameters.Clear();
return val; } public DataSet ExecuteDataSet(string cmdText, params SqlParameter[] commandParameters)
{
DataSet ds = new DataSet();
SqlDataAdapter adapter = null;
using (SqlConnection conn = GetConnection())
{
SqlCommand cmd = CreateCommand(conn, null, cmdText, commandParameters);
adapter = new SqlDataAdapter(cmd);
adapter.Fill(ds);
}
//cmd.Parameters.Clear();
return ds; } public void CacheParameters(string cacheKey, params SqlParameter[] commandParameters)
{
parmCache[cacheKey] = commandParameters;
} public SqlParameter[] GetCachedParameters(string cacheKey)
{
SqlParameter[] cachedParms = (SqlParameter[])parmCache[cacheKey]; if (cachedParms == null)
return null; SqlParameter[] clonedParms = new SqlParameter[cachedParms.Length]; for (int i = 0, j = cachedParms.Length; i < j; i++)
clonedParms[i] = (SqlParameter)((ICloneable)cachedParms[i]).Clone(); return clonedParms;
} private SqlCommand CreateCommand(SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
{
if (null == conn)
{
conn = new SqlConnection(ConnectionString);
}
if (conn.State != ConnectionState.Open)
conn.Open();
SqlCommand cmd = new SqlCommand(); cmd.Connection = conn;
cmd.CommandText = cmdText; if (trans != null)
cmd.Transaction = trans; cmd.CommandType = CommandType.Text; if (cmdParms != null)
{
foreach (SqlParameter parm in cmdParms)
{
if (parm != null)
cmd.Parameters.Add(parm);
}
}
return cmd;
}
}
}
--------------------------
SqlHelper
using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace DBAccess
{
public class SqlHelper:DBAccess
{
private List<SqlParameter> m_ListParameter = null; public DataTable ExecuteDataTable(string cmdText, params SqlParameter[] commandParameters)
{
DataSet ds = ExecuteDataSet(cmdText, commandParameters);
if (null != ds)
{
if (0 != ds.Tables.Count)
{
return ds.Tables[0];
}
else
{
return null;
}
}
else
{
return null;
}
} public DataTable ExecuteDataTable(string cmdText)
{
return ExecuteDataTable(cmdText, GetParameters());
} public DataRow ExecuteDataRow(string cmdText, params SqlParameter[] commandParameters)
{
DataTable dt = ExecuteDataTable(cmdText, commandParameters);
if (null != dt)
{
if (0 != dt.Rows.Count)
{
return dt.Rows[0]; }
else
{
return null;
}
}
else
{
return null;
}
} public DataRow ExecuteDataRow(string cmdText)
{
return ExecuteDataRow(cmdText, GetParameters());
} public int ExecuteNonQuery(string cmdText)
{
return ExecuteNonQuery(cmdText, GetParameters());
}
public int ExecuteNonQuery(SqlTransaction trans, string cmdText)
{
return ExecuteNonQuery(trans, cmdText, GetParameters());
}
public DataSet ExecuteDataSet(string cmdText)
{
return ExecuteDataSet(cmdText, GetParameters());
}
public SqlDataReader ExecuteReader(string cmdText)
{
return ExecuteReader(cmdText, GetParameters());
}
public object ExecuteScalar(string cmdText)
{
return ExecuteScalar(cmdText, GetParameters());
} private SqlParameter GetParameter(string paraName, SqlDbType paraType, object value)
{
SqlParameter para = new SqlParameter(paraName, paraType);
para.Value = value; return para;
} private SqlParameter GetParameter(string paraName, SqlDbType paraType, object value, int size)
{
SqlParameter para = new SqlParameter(paraName, paraType);
para.Value = value;
para.Size = size;
return para;
} public void AddParameter(string paraName, SqlDbType paraType, object value)
{
if (null == m_ListParameter)
{
m_ListParameter = new List<SqlParameter>();
}
SqlParameter para = GetParameter(paraName, paraType, value);
m_ListParameter.Add(para);
} private SqlParameter[] GetParameters()
{
SqlParameter[] paras = null;
if (null != m_ListParameter)
{
if (0 != m_ListParameter.Count)
{
paras = m_ListParameter.ToArray();
}
m_ListParameter = null;
} return paras;
}
}
}
你用微软PETSHOP示例项目中的那个就OK
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace MyBlogDAL
{
public static class DBHelper
{ private static SqlConnection connection;
/// <summary>
///
/// </summary>
public static SqlConnection Connection
{
get
{
string connectionString = ConfigurationManager.ConnectionStrings["MyBlog"].ConnectionString;
if (connection == null)
{
connection = new SqlConnection(connectionString);
connection.Open();
}
else if (connection.State == System.Data.ConnectionState.Closed)
{
connection.Open();
}
else if (connection.State == System.Data.ConnectionState.Broken)
{
connection.Close();
connection.Open();
}
return connection;
}
}
/// <summary>
/// 执行并返回影响行数
/// </summary>
/// <param name="safeSql"></param>
/// <returns></returns>
public static int ExecuteCommand(string safeSql)
{
SqlCommand cmd = new SqlCommand(safeSql, Connection);
int result = cmd.ExecuteNonQuery();
return result;
}
/// <summary>
/// 执行并返回影响行数
/// </summary>
/// <param name="sql"></param>
/// <param name="values"></param>
/// <returns></returns>
public static int ExecuteCommand(string sql, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
return cmd.ExecuteNonQuery();
}
/// <summary>
/// 执行并返回执行结果中的第一列
/// </summary>
/// <param name="safeSql"></param>
/// <returns></returns>
public static int GetScalar(string safeSql)
{
SqlCommand cmd = new SqlCommand(safeSql, Connection);
int result = Convert.ToInt32(cmd.ExecuteScalar());
return result;
}
/// <summary>
/// 执行并返回执行结果中的第一列
/// </summary>
/// <param name="sql"></param>
/// <param name="values"></param>
/// <returns></returns>
public static int GetScalar(string sql, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
int result = Convert.ToInt32(cmd.ExecuteScalar());
return result;
}
/// <summary>
/// 根据sql语句获得sqldatareader
/// </summary>
/// <param name="safeSql"></param>
/// <returns></returns>
public static SqlDataReader GetReader(string safeSql)
{
SqlCommand cmd = new SqlCommand(safeSql, Connection);
SqlDataReader reader = cmd.ExecuteReader();
return reader;
}
/// <summary>
/// 根据sql语句获得sqldatareader
/// </summary>
/// <param name="sql"></param>
/// <param name="values"></param>
/// <returns></returns>
public static SqlDataReader GetReader(string sql, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
SqlDataReader reader = cmd.ExecuteReader();
return reader;
}
/// <summary>
/// 根据sql语句获得DataTable
/// </summary>
/// <param name="safeSql"></param>
/// <returns></returns>
public static DataTable GetDataSet(string safeSql)
{
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(safeSql, Connection);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
return ds.Tables[0];
}
/// <summary>
/// 根据sql语句获得DataTable
/// </summary>
/// <param name="sql"></param>
/// <param name="values"></param>
/// <returns></returns>
public static DataTable GetDataSet(string sql, params SqlParameter[] values)
{
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
return ds.Tables[0];
}
}
}