对数据库的增删改查,可以通用数据库助手类(牛腩新闻发布系统视频)
- using System;
- using System.Collections.Generic;
- using System.Text;
- using System.Data;
- using System.Data.SqlClient;
- using System.Configuration;
- namespace DAL
- {
- /// <summary>
- /// 数据库助手类
- /// </summary>
- public class SQLHelper
- {
- private SqlConnection conn = null;
- private SqlCommand cmd = null;
- private SqlDataReader sdr = null;
- public SQLHelper()
- {
- string connStr = ConfigurationManager.ConnectionStrings["连接字符串名"].ConnectionString;
- conn = new SqlConnection(connStr);
- }
- private SqlConnection GetConn()
- {
- if (conn.State == ConnectionState.Closed)
- {
- conn.Open();
- }
- return conn;
- }
- /// <summary>
- /// 执行不带参数的增删改SQL语句或存储过程
- /// </summary>
- /// <param name="cmdText">增删改SQL语句或存储过程</param>
- /// <param name="ct">命令类型</param>
- /// <returns></returns>
- public int ExecuteNonQuery(string cmdText, CommandType ct)
- {
- int res;
- using (cmd = new SqlCommand(cmdText, GetConn()))
- {
- cmd.CommandType = ct;
- res = cmd.ExecuteNonQuery();
- }
- return res;
- }
- /// <summary>
- /// 执行带参数的增删改SQL语句或存储过程
- /// </summary>
- /// <param name="cmdText">增删改SQL语句或存储过程</param>
- /// <param name="ct">命令类型</param>
- /// <returns></returns>
- public int ExecuteNonQuery(string cmdText, SqlParameter[] paras, CommandType ct)
- {
- int res;
- using (cmd = new SqlCommand(cmdText, GetConn()))
- {
- cmd.CommandType = ct;
- cmd.Parameters.AddRange(paras);
- res = cmd.ExecuteNonQuery();
- }
- return res;
- }
- /// <summary>
- /// 执行查询SQL语句或存储过程
- /// </summary>
- /// <param name="cmdText">查询SQL语句或存储过程</param>
- /// <param name="ct">命令类型</param>
- /// <returns></returns>
- public DataTable ExecuteQuery(string cmdText, CommandType ct)
- {
- DataTable dt = new DataTable();
- cmd = new SqlCommand(cmdText, GetConn());
- cmd.CommandType = ct;
- using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
- {
- dt.Load(sdr);
- }
- return dt;
- }
- /// <summary>
- /// 执行带参数的查询SQL语句或存储过程
- /// </summary>
- /// <param name="cmdText">查询SQL语句或存储过程</param>
- /// <param name="paras">参数集合</param>
- /// <param name="ct">命令类型</param>
- /// <returns></returns>
- public DataTable ExecuteQuery(string cmdText, SqlParameter[] paras, CommandType ct)
- {
- DataTable dt = new DataTable();
- cmd = new SqlCommand(cmdText, GetConn());
- cmd.CommandType = ct;
- cmd.Parameters.AddRange(paras);
- using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
- {
- dt.Load(sdr);
- }
- return dt;
- }
- }
- }