- {
- public class SQLHelper
- {
- private string strConn = null;
- private SqlConnection Conn = null;
- private SqlCommand Cmd = null;
- private SqlDataReader Sqlsdr = null;
- public SQLHelper()
- {
- //利用反射连接数据库
- strConn = ConfigurationManager.ConnectionStrings["strConn"].ConnectionString;
- //strConn = "data source=192.168.24.83;initial catalog=NewsSystem;user id=sa;password=123456";
- Conn = new SqlConnection(strConn);
- }
- /// <summary>
- /// 状态
- /// </summary>
- /// <returns>SqlConnection</returns>
- private SqlConnection GetConn()
- {
- if (Conn.State == ConnectionState.Closed)
- {
- Conn.Open();
- }
- return Conn;
- }
- /// <summary>
- /// 执行不带参数的增删改操作
- /// </summary>
- /// <param name="CmdText">执行的SQL语句或存储过程</param>
- /// <param name="CmdType">类型</param>
- /// <returns>增删改的操作数</returns>
- public int ExecuteNonQuery(string CmdText, CommandType CmdType)
- {
- //数据库连接语句
- int res;
- try
- {
- Cmd = new SqlCommand(CmdText, GetConn());
- Cmd.CommandType = CmdType;
- res = Cmd.ExecuteNonQuery();
- }
- catch (Exception ex)
- {
- throw ex;
- }
- finally
- {
- if (Conn.State == ConnectionState.Open)
- {
- Conn.Close();
- }
- }
- return res;
- }
- /// <summary>
- /// 执行带参数的增删改操作
- /// </summary>
- /// <param name="CmdText">执行的SQL语句或存储过程</param>
- /// <param name="Parameters">参数</param>
- /// <param name="CmdType">类型(SQL语句或存储过程)</param>
- /// <returns>增删改的操作数</returns>
- public int ExecuteNonQuery(string CmdText, SqlParameter[] Parameters, CommandType CmdType)
- {
- int res;
- try
- {
- Cmd.Parameters.AddRange(Parameters);
- Cmd = new SqlCommand(CmdText, GetConn());
- Cmd.CommandType = CmdType;
- res = Cmd.ExecuteNonQuery();
- }
- catch (Exception ex)
- {
- throw ex;
- }
- finally
- {
- if (Conn.State == ConnectionState.Open)
- {
- Conn.Close();
- }
- }
- return res;
- }
- /// <summary>
- /// 不带参数的查询
- /// </summary>
- /// <param name="CmdText">执行的SQL语句或存储过程</param>
- /// <param name="CmdType">类型</param>
- /// <returns>查询的数据</returns>
- public DataTable ExecuteQuery(string CmdText, CommandType CmdType)
- {
- DataTable dt = new DataTable();
- using (Sqlsdr = Cmd.ExecuteReader(CommandBehavior.CloseConnection))
- {
- Cmd = new SqlCommand(CmdText, GetConn());
- Cmd.CommandType = CmdType;
- dt.Load(Sqlsdr);
- }
- return dt;
- }
- /// <summary>
- /// 带参数的查询
- /// </summary>
- /// <param name="CmdText">SQL语句或存储过程</param>
- /// <param name="Parameters">参数</param>
- /// <param name="CmdType">类型</param>
- /// <returns>查询的数据</returns>
- public DataTable ExecuteQuery(string CmdText, SqlParameter[] Parameters, CommandType CmdType)
- {
- DataTable dt = new DataTable();
- using (Sqlsdr = Cmd.ExecuteReader(CommandBehavior.CloseConnection))
- {
- Cmd = new SqlCommand(CmdText, GetConn());
- Cmd.CommandType = CmdType;
- Cmd.Parameters.AddRange(Parameters);
- dt.Load(Sqlsdr);
- }
- return dt;
- }
- }
- }
- //该片段来自于http://www.codesnippet.cn/detail/100920135766.html
来源: http://www.codesnippet.cn/detail/100920135766.html