62,025
社区成员
发帖
与我相关
我的任务
分享
public static class DBHelper
{
private static SqlConnection connection;
public static SqlConnection Connection
{
get
{
string connectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=E:\BookShop\DB\MyBookShop.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";
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;
}
}
public static int ExecuteCommand(string safeSql)
{
SqlCommand cmd = new SqlCommand(safeSql, Connection);
int result = cmd.ExecuteNonQuery();
return result;
}
public static int ExecuteCommand(string sql, SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
return cmd.ExecuteNonQuery();
}
public static int ExecuteCommand(string sql, SqlParameter value)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.Add(value);
int result = cmd.ExecuteNonQuery();
return result;
}
public static int ExecuteScalar(string safeSql)
{
SqlCommand cmd = new SqlCommand(safeSql, Connection);
int result = (int)cmd.ExecuteScalar();
return result;
}
public static int ExecuteScalar(string sql, SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
int result = (int)cmd.ExecuteScalar();
return result;
}
public static int ExecuteScalar(string sql, SqlParameter value)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.Add(value);
int result = (int)cmd.ExecuteScalar();
return result;
}
public static SqlDataReader ExecuteReader(string safeSql)
{
SqlCommand cmd = new SqlCommand(safeSql, Connection);
SqlDataReader reader = cmd.ExecuteReader();
return reader;
}
public static SqlDataReader ExecuteReader(string sql, SqlParameter value)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.Add(value);
SqlDataReader reader = cmd.ExecuteReader();
return reader;
}
public static SqlDataReader ExecuteReader(string sql, SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
SqlDataReader reader = cmd.ExecuteReader();
return reader;
}
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];
}
public static SqlDataReader GetReader(string safeSql)
{
SqlCommand cmd = new SqlCommand(safeSql, Connection);
SqlDataReader reader = cmd.ExecuteReader();
return reader;
}
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;
}
}
public abstract class SQLAccess
{
// Fields
public static readonly string connString;
private static string key = string.Empty;
private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
private static int[] tmp = new int[] { 0x17, 0xea, 0xc3, 0xa5, 0xc9, 240, 0x8f, 0xc6 };
// Methods
static SQLAccess()
{
foreach (int num in tmp)
{
key = key + ((char) num).ToString();
}
try
{
connString = SecurityManager.Decrypt(ConfigurationManager.AppSettings["connectstring"].ToString(), key);
}
catch
{
}
}
protected SQLAccess()
{
}
public static void CacheParam(string cacheKey, params SqlParameter[] cmdParam)
{
parmCache[cacheKey] = cmdParam;
}
public static DataSet ExecuteDataSet(string connString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
{
DataSet set2;
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(connString);
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
DataSet dataSet = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = cmd;
adapter.Fill(dataSet);
cmd.Parameters.Clear();
set2 = dataSet;
}
catch
{
conn.Close();
throw;
}
finally
{
if (conn != null)
{
conn.Dispose();
}
}
return set2;
}
public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] cmdParams)
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParams);
int num = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return num;
}
public static int ExecuteNonQuery(string connString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParams)
{
SqlCommand cmd = new SqlCommand();
using (SqlConnection connection = new SqlConnection(connString))
{
PrepareCommand(cmd, connection, null, cmdType, cmdText, cmdParams);
int num = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
connection.Close();
return num;
}
}
public static SqlDataReader ExecuteReader(string connString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
{
SqlDataReader reader2;
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(connString);
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
reader2 = reader;
}
catch
{
conn.Close();
throw;
}
return reader2;
}
public static object ExecuteScalar(string connString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParams)
{
SqlCommand cmd = new SqlCommand();
using (SqlConnection connection = new SqlConnection(connString))
{
PrepareCommand(cmd, connection, null, cmdType, cmdText, cmdParams);
object obj2 = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return obj2;
}
}
public static SqlParameter[] GetCacheParam(string cacheKey)
{
SqlParameter[] parameterArray = (SqlParameter[]) parmCache[cacheKey];
if (parameterArray == null)
{
return null;
}
return parameterArray;
}
private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
{
if (conn.get_State() != ConnectionState.Open)
{
conn.Open();
}
cmd.Connection = conn;
cmd.set_CommandText(cmdText);
if (trans != null)
{
cmd.Transaction = trans;
}
cmd.set_CommandType(cmdType);
if (cmdParms != null)
{
foreach (SqlParameter parameter in cmdParms)
{
cmd.Parameters.Add(parameter);
}
}
}
}
public abstract class SqlHelper {
//链接字符串
public static readonly string ConnnectionString = ConfigurationManager.ConnectionStrings["SqlConnString"].ConnectionString;
//存储过程参数和值
public static string[] Params = null;
public static object[] Values = null;
//错误信息
public static string ErrorString = "";
/// <summary>
/// 填充SqlCommand对象
/// </summary>
/// <param name="conn">SqlConnection 连接对象</param>
/// <param name="cmd">要填充的SqlCommand对象</param>
/// <param name="strSqlOrProcedure">要执行的Sql语句或存储过程</param>
/// <param name="cmdType">命令类型</param>
protected static void PrepareCommand(SqlConnection conn, SqlCommand cmd, string strSqlOrProcedure, CommandType cmdType) {
cmd.Connection = conn;
cmd.CommandText = strSqlOrProcedure;
cmd.CommandType = cmdType;
if (Params != null && Values != null && Params.Length == Values.Length) {
for (int i = 0; i < Params.Length; i++) {
SqlParameter sp = new SqlParameter();
sp.ParameterName = Params[i];
sp.Value = Values[i];
sp.Direction = ParameterDirection.Input;
cmd.Parameters.Add(sp);
}
}
Params = null;
Values = null;
}
/// <summary>
/// 执行查询,只返回影响的行数
/// </summary>
/// <param name="strSqlOrProcedure">要执行的Sql语句或存储过程</param>
/// <param name="cmdType">命令类型</param>
/// <returns>返回影响的行数</returns>
public static int ExecNonQuery(string strSqlOrProcedure, CommandType cmdType) {
SqlCommand cmd = new SqlCommand();
SqlConnection conn = null;
try {
conn = new SqlConnection(ConnnectionString);
conn.Open();
PrepareCommand(conn, cmd, strSqlOrProcedure, cmdType);
int nCount = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return nCount;
}
catch (Exception e) {
ErrorString = e.Message;
conn.Close();
throw;
}
}
/// <summary>
/// 执行查询,只返回第一行第一列的数据
/// </summary>
/// <param name="strSqlOrProcedure">要执行的Sql语句或存储过程</param>
/// <param name="cmdType">命令类型</param>
/// <returns>返回第一行第一列的数据</returns>
public static object ExecScalar(string strSqlOrProcedure, CommandType cmdType) {
SqlCommand cmd = new SqlCommand();
SqlConnection conn = null;
object val = null;
try {
conn = new SqlConnection(ConnnectionString);
conn.Open();
PrepareCommand(conn, cmd, strSqlOrProcedure, cmdType);
val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}
catch (Exception e) {
ErrorString = e.Message;
conn.Close();
throw;
}
}
public static SqlDataReader ExecReader(string strSqlOrProcedure, CommandType cmdType) {
SqlCommand cmd = new SqlCommand();
SqlConnection conn = null;
try {
conn = new SqlConnection(ConnnectionString);
conn.Open();
PrepareCommand(conn, cmd, strSqlOrProcedure, cmdType);
SqlDataReader sdr = cmd.ExecuteReader();
cmd.Parameters.Clear();
return sdr;
}
catch (Exception e) {
ErrorString = e.Message;
conn.Close();
throw;
}
}
/// <summary>
/// 执行查询,并返回结果
/// </summary>
/// <param name="strSqlOrProcedure">要执行的Sql语句或存储过程</param>
/// <param name="cmdType">命令类型</param>
/// <returns>返回DataSet查询结果</returns>
public static DataSet ExecQuery(string strSqlOrProcedure, CommandType cmdType) {
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand();
SqlConnection conn = null;
try {
conn = new SqlConnection(ConnnectionString);
conn.Open();
PrepareCommand(conn, cmd, strSqlOrProcedure, cmdType);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
sda.Fill(ds);
cmd.Parameters.Clear();
return ds;
}
catch (Exception e) {
ErrorString = e.Message;
conn.Close();
throw;
}
}
}