在这里写了一个操作SqlServer的数据库操作类,如果有问题,请大家指出
using System;
using System.Collections.Generic;
using System.Text;
using System.Web;
using System.Data;
using System.Data.SqlClient;
namespace DBControl
{
public class DBHelp
{
#region 基本方法
//连接字符串
private string ConnectString
{
get
{
return System.Configuration.ConfigurationManager.AppSettings["SqlConString"];
}
}
/// <summary>
/// 新建一个SqlConnection,如果失败则抛出异常
/// </summary>
/// <returns>SqlConnection</returns>
private SqlConnection NewConnection()
{
try
{
SqlConnection sqlCon = new SqlConnection(ConnectString);
sqlCon.Open();
return sqlCon;
}
catch
{
throw new Exception("创建SqlConnection时出错!");
}
}
/// <summary>
/// 新建一个SqlCommand,如果失败则抛出异常
/// </summary>
/// <param name="cmdText">CommandText</param>
/// <param name="sqlCon">SqlConnection</param>
/// <param name="cmdType">CommandType</param>
/// <param name="parameters">CommandType</param>
/// <returns>SqlParameter</returns>
private SqlCommand NewCommand(String cmdText, SqlConnection sqlCon, CommandType cmdType, SqlParameter[] parameters)
{
try
{
SqlCommand sqlCom = new SqlCommand(cmdText, sqlCon);
sqlCom.CommandType = cmdType;
if (parameters != null)
{ sqlCom.Parameters.AddRange(parameters); }
return sqlCom;
}
catch
{
throw new Exception("创建SqlCommand时出错!");
}
}
/// <summary>
/// 新建一个SqlDataAdapter,如果失败则抛出异常
/// </summary>
/// <param name="sqlCom">SqlCommand</param>
/// <returns></returns>
private SqlDataAdapter NewDataAdapter(SqlCommand sqlCom)
{
try
{
SqlDataAdapter sqlDadp = new SqlDataAdapter(sqlCom);
return sqlDadp;
}
catch
{
throw new Exception("创建SqlDataAdapter时出错!");
}
}
#endregion
#region 查询用方法
/// <summary>
/// 查询数据并返回一个DataSet,适用于包含多条select语句的查询
/// </summary>
/// <param name="cmdText">查询语句或者存储过程名称</param>
/// <param name="cmdType">指定查询类型</param>
/// <param name="cmdType">参数数组</param>
/// <returns>一个包含多个查询结果DataSet</returns>
public DataSet ExecuteDataSet(String cmdText, CommandType cmdType, SqlParameter[] parameters)
{
DataSet set = new DataSet();
using (SqlConnection sqlCon = NewConnection())
{
if (sqlCon.State == ConnectionState.Closed)
{ sqlCon.Open(); }
using (SqlCommand sqlCom = NewCommand(cmdText, sqlCon, cmdType, parameters))
{
using (SqlDataAdapter sqlDadp = NewDataAdapter(sqlCom))
{
sqlDadp.Fill(set);
}
}
}
return set;
}
/// <summary>
/// 查询数据并返回一个DataSet,适用于包含多条select语句的查询
/// </summary>
/// <param name="cmdText">查询语句或者存储过程名称</param>
/// <param name="cmdType">指定查询类型</param>
/// <returns>一个包含多个查询结果DataSet</returns>
public DataSet ExecuteDataSet(String cmdText, CommandType cmdType)
{
if (cmdType == CommandType.StoredProcedure)
{
DataSet set = (DataSet)DBCache.GetCache(cmdText);
if (set == null)
{
set = ExecuteDataSet(cmdText, cmdType, null);
DBCache.SetCache(cmdText, set);
}
return set;
}
else
{
return ExecuteDataSet(cmdText, cmdType, null);
}
}
/// <summary>
/// 查询数据并返回一个DataTable
/// </summary>
/// <param name="cmdText">查询语句或者存储过程名称</param>
/// <param name="cmdType">指定查询类型</param>
/// <param name="cmdType">参数数组</param>
/// <returns>DataTable</returns>
public DataTable ExecuteDataTable(String cmdText, CommandType cmdType, SqlParameter[] parameters)
{
return ExecuteDataSet(cmdText,cmdType,parameters).Tables[0];
}
/// <summary>
/// 查询数据并返回一个DataTable
/// </summary>
/// <param name="cmdText">查询语句或者存储过程名称</param>
/// <param name="cmdType">指定查询类型</param>
/// <param name="cmdType">参数数组</param>
/// <returns>DataTable</returns>
public DataTable ExecuteDataTable(String cmdText, CommandType cmdType)
{
if (cmdType == CommandType.StoredProcedure)
{
DataTable table = (DataTable)DBCache.GetCache(cmdText);
if (table == null)
{
table = ExecuteDataTable(cmdText, cmdType, null);
DBCache.SetCache(cmdText, table);
}
return table;
}
else
{
return ExecuteDataTable(cmdText, cmdType, null);
}
}
/// <summary>
/// 查询数据并返回一个DataRow
/// </summary>
/// <param name="cmdText">查询语句或者存储过程名称</param>
/// <param name="cmdType">指定查询类型</param>
/// <param name="cmdType">参数数组</param>
/// <returns>DataRow</returns>
public DataRow ExecuteDataRow(String cmdText, CommandType cmdType, SqlParameter[] parameters)
{
return ExecuteDataSet(cmdText, cmdType, parameters).Tables[0].Rows[0];
}
/// <summary>
/// 查询数据并返回一个DataRow
/// </summary>
/// <param name="cmdText">查询语句或者存储过程名称</param>
/// <param name="cmdType">指定查询类型</param>
/// <returns>DataRow</returns>
public DataRow ExecuteDataRow(String cmdText, CommandType cmdType)
{
if (cmdType == CommandType.StoredProcedure)
{
DataRow row = (DataRow)DBCache.GetCache(cmdText);
if (row == null)
{
row = ExecuteDataRow(cmdText, cmdType, null);
DBCache.SetCache(cmdText, row);
}
return row;
}
else
{
return ExecuteDataRow(cmdText, cmdType, null);
}
}
/// <summary>
/// 查询数据并返回第一个数据
/// </summary>
/// <param name="cmdText">查询语句或者存储过程名称</param>
/// <param name="cmdType">指定查询类型</param>
/// <param name="parameters">参数数组</param>
/// <returns>Object</returns>
public Object ExecuteScalar(String cmdText, CommandType cmdType, SqlParameter[] parameters)
{
Object obj = new Object();
using (SqlConnection sqlCon = NewConnection())
{
if (sqlCon.State == ConnectionState.Closed)
{ sqlCon.Open(); }
using (SqlCommand sqlCom = NewCommand(cmdText, sqlCon, cmdType, parameters))
{
obj = sqlCom.ExecuteScalar();
}
}
return obj;
}
/// <summary>
/// 查询数据并返回第一个数据
/// </summary>
/// <param name="cmdText">查询语句或者存储过程名称</param>
/// <param name="cmdType">指定查询类型</param>
/// <returns>Object</returns>
public Object ExecuteScalar(String cmdText, CommandType cmdType)
{
if (cmdType == CommandType.StoredProcedure)
{
Object obj = DBCache.GetCache(cmdText);
if (obj == null)
{
obj = ExecuteScalar(cmdText, cmdType, null);
DBCache.SetCache(cmdText, obj);
}
return obj;
}
else
{
return ExecuteScalar(cmdText, cmdType, null);
}
}
#endregion
#region 新增、删除、更新用方法
/// <summary>
/// 返回执行是否成功,适用于新增、删除、更新操作
/// </summary>
/// <param name="cmdText">查询语句或者存储过程名称</param>
/// <param name="cmdType">指定查询类型</param>
/// <param name="parameters">参数数组</param>
/// <returns>true表示执行成功,false表示执行失败</returns>
public bool ExecuteBool(String cmdText, CommandType cmdType, SqlParameter[] parameters)
{
bool b = false;
using (SqlConnection sqlCon = NewConnection())
{
if (sqlCon.State == ConnectionState.Closed)
{ sqlCon.Open(); }
using (SqlCommand sqlCom = NewCommand(cmdText, sqlCon, cmdType, parameters))
{
b = sqlCom.ExecuteNonQuery() > 0 ? true : false;
}
}
return b;
}
/// <summary>
/// 返回执行是否成功,适用于新增、删除、更新操作
/// </summary>
/// <param name="cmdText">查询语句或者存储过程名称</param>
/// <param name="cmdType">指定查询类型</param>
/// <returns>true表示执行成功,false表示执行失败</returns>
public bool ExecuteBool(String cmdText, CommandType cmdType)
{
return ExecuteBool(cmdText, cmdType, null);
}
#endregion
}
}
这个操作类是基于缓存的,在进行大数据量或多用户进行查询数据的时候会有帮助
下面就是缓存类
using System;
using System.Collections.Generic;
using System.Text;
using System.Web;
using System.Web.Caching;
namespace DBControl
{
/// <summary>
/// 缓存
/// </summary>
public sealed class DBCache
{
private static Cache myCache = HttpContext.Current.Cache;
/// <summary>
/// 将数据以键值对的形式存入缓存
/// </summary>
/// <param name="Key">键</param>
/// <param name="Value">值</param>
public static void SetCache(String Key, Object Value)
{
myCache.Insert(Key, Value);
}
/// <summary>
/// 根据键在缓存中寻找以键值对的形式存入的值
/// </summary>
/// <param name="Key">键</param>
/// <returns>值</returns>
public static Object GetCache(String Key)
{
return myCache.Get(Key);
}
/// <summary>
/// 根据键在缓存中清除以键值对的形式存入的值
/// </summary>
/// <param name="Key">键</param>
public static void RemoveCache(String Key)
{
myCache.Remove(Key);
}
}
}