SQLHelper.cs类(访问数据库类)

using System;using System.Collections.Generic;using System.Text;using System.Data;using System.Data.SqlClient;using System.Configuration;namespace DBUtility{ public class SQLHelper { public static string sqlConnString = ConfigurationManager.ConnectionStrings["SqlConnString"].ConnectionString; /// <summary> /// 为执行数据库操作准备Command对象 /// </summary> /// <param name="cmd">SQL命令对象</param> /// <param name="conn">SQL连接对象</param> /// <param name="trans">SQL事务对象</param> /// <param name="cmdType">命令对象中命令的类型（普通SQL语句还是存储过程）</param> /// <param name="cmdText">命令内容</param> /// <param name="cmdParms">命令所使用的参数</param> private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms) { if (conn.State != ConnectionState.Open) conn.Open(); cmd.Connection = conn; cmd.CommandText = cmdText; if (trans != null) { cmd.Transaction = trans; } cmd.CommandType = cmdType; if (cmdParms != null) { foreach (SqlParameter parm in cmdParms) { cmd.Parameters.Add(parm); } } } public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); using (SqlConnection conn = new SqlConnection(connectionString)) { PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return val; } } #region 方法重载 public static int ExecuteNonQuery(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return val; } public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return val; } #endregion /// <summary> /// 获得一个SqlDataReader /// </summary> /// <param name="connectionString">连接数据库字符串</param> /// <param name="cmdType">SQL命令类型</param> /// <param name="cmdText">SQL命令文本</param> /// <param name="commandParameters">参数集合</param> /// <returns>SqlDataReader实例</returns> public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); SqlConnection conn = new SqlConnection(connectionString); //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 { PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);//如果关闭关联的DataReader对象,则关联的Connection对象也将关闭 cmd.Parameters.Clear(); return rdr; } catch (Exception ex) { conn.Close(); throw ex; } } public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); using (SqlConnection connection = new SqlConnection(connectionString)) { PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters); object val = cmd.ExecuteScalar(); cmd.Parameters.Clear(); return val; } } public static object ExecuteScalar(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters); object val = cmd.ExecuteScalar(); cmd.Parameters.Clear(); return val; } }}