SQLHelper.cs
using System; using System.Data; using System.Configuration; using System.Data.SqlClient; namespace DAL { public class SQLHelper { #region 私有字段 //获取连接字符串 private static string ConString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString; //创建连接对象 public static SqlConnection con = null; //创建命令执行对象 public static SqlCommand cmd = null; //创建向前只读数据集 public static SqlDataReader sdr = null; //创建数据适配器 public static SqlDataAdapter adp = null; //创建前后读取数据集 public static DataTable dt = null; //声明一个Object对象 public static object obj = null; #endregion #region 创建一个打开后的数据库连接对象 /// <summary> /// 创建一个打开后的数据库连接对象 /// </summary> /// <returns></returns> private static SqlConnection GetOpenConnection() { //实例化出一个链接对象 con = new SqlConnection(ConString); //判断是否为关闭状态 if (con.State == ConnectionState.Closed) con.Open(); return con; } #endregion #region ExecuteTable /// <summary> /// 返回相应的数据表(无参数) /// </summary> /// <param name="text">要执行的语句,SQL语句或者是存储过程</param> /// <param name="type">要执行的类型,SQL语句或者是存储过程</param> /// <returns></returns> public static DataTable ExecuteTable(string text, CommandType type) { dt = new DataTable(); using (adp = new SqlDataAdapter(text, GetOpenConnection())) { try { adp.SelectCommand.CommandType = type; adp.Fill(dt); } catch (Exception ex) { throw ex; } } return dt; } /// <summary> /// 返回相应的数据表(重载,使用单个参数) /// </summary> /// <param name="text">要执行的语句,SQL语句或者是存储过程</param> /// <param name="para">使用的单个参数</param> /// <param name="type">要执行的类型,SQL语句或者是存储过程</param> /// <returns></returns> public static DataTable ExecuteTable(string text, SqlParameter para, CommandType type) { dt = new DataTable(); using (adp = new SqlDataAdapter(text, GetOpenConnection())) { try { adp.SelectCommand.CommandType = type; adp.SelectCommand.Parameters.Add(para); adp.Fill(dt); } catch (Exception ex) { throw ex; } } return dt; } /// <summary> /// 返回相应的数据表(重载,使用参数数组) /// </summary> /// <param name="text">要执行的语句,SQL语句或者是存储过程</param> /// <param name="para">使用的参数数组</param> /// <param name="type">要执行的类型,SQL语句或者是存储过程</param> /// <returns></returns> public static DataTable ExecuteTable(string text, SqlParameter[] paras, CommandType type) { dt = new DataTable(); using (adp = new SqlDataAdapter(text, GetOpenConnection())) { try { adp.SelectCommand.CommandType = type; adp.SelectCommand.Parameters.AddRange(paras); adp.Fill(dt); } catch (Exception ex) { throw ex; } } return dt; } #endregion #region ExcuteReader /// <summary> /// 返回SqlDataReader数据集(不使用参数) /// </summary> /// <param name="text"></param> /// <param name="type"></param> /// <returns></returns> public static SqlDataReader ExcuteReader(string text, CommandType type) { using (cmd = new SqlCommand(text, GetOpenConnection())) { try { cmd.CommandType = type; sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection); } catch (Exception ex) { throw ex; } return sdr; } } /// <summary> /// 重载,返回SqlDataReader数据集(使用单个参数) /// </summary> /// <param name="text">要执行的SQL语句或是存储过程</param> /// <param name="para">要使用的单个参数</param> /// <param name="type">SQL语句或是存储过程</param> /// <returns></returns> public static SqlDataReader ExcuteReader(string text, SqlParameter para, CommandType type) { using (cmd = new SqlCommand(text, GetOpenConnection())) { try { cmd.CommandType = type; cmd.Parameters.Add(para); sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection); } catch (Exception ex) { throw ex; } return sdr; } } /// <summary> /// 重载,返回SqlDataReader数据集(使用参数数组) /// </summary> /// <param name="text">要执行的SQL语句或是存储过程</param> /// <param name="para">要使用的单个参数数组</param> /// <param name="type">SQL语句或是存储过程</param> /// <returns></returns> public static SqlDataReader ExcuteReader(string text, SqlParameter[] paras, CommandType type) { using (cmd = new SqlCommand(text, GetOpenConnection())) { try { cmd.CommandType = type; cmd.Parameters.AddRange(paras); sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection); } catch (Exception ex) { throw ex; } return sdr; } } #endregion #region ExcuteScalar /// <summary> /// 返回数据集第一行第一列的值 /// </summary> /// <param name="text"></param> /// <param name="type"></param> /// <returns></returns> public static object ExcuteScalar(string text, CommandType type) { using (cmd = new SqlCommand(text, GetOpenConnection())) { try { cmd.CommandType = type; obj = cmd.ExecuteScalar(); } catch (Exception ex) { throw ex; } } return obj; } /// <summary> /// 重载(返回数据集合第一行第一列的值) /// </summary> /// <param name="text">要执行的SQL语句或是存储过程</param> /// <param name="para">要使用的单个参数</param> /// <param name="type">SQL语句或者是存储过程</param> /// <returns></returns> public static object ExcuteScalar(string text, SqlParameter para, CommandType type) { using (cmd = new SqlCommand(text, GetOpenConnection())) { try { cmd.CommandType = type; cmd.Parameters.Add(para); obj = cmd.ExecuteScalar(); } catch (Exception ex) { throw ex; } } return obj; } /// <summary> /// 重载(返回数据集合第一行第一列的值) /// </summary> /// <param name="text">要执行的SQL语句或是存储过程</param> /// <param name="para">要使用的参数数组</param> /// <param name="type">SQL语句或者是存储过程</param> /// <returns></returns> public static object ExcuteScalar(string text, SqlParameter[] paras, CommandType type) { using (cmd = new SqlCommand(text, GetOpenConnection())) { try { cmd.CommandType = type; cmd.Parameters.AddRange(paras); obj = cmd.ExecuteScalar(); } catch (Exception ex) { throw ex; } } return obj; } #endregion #region ExecuteNonQuery /// <summary> /// 返回受影响的行数(不使用参数) /// </summary> /// <param name="text">要执行的SQL语句或者是存储过程</param> /// <param name="type">执行SQL语句或者是存储过程</param> /// <returns></returns> public static int ExecuteNonQuery(string text, CommandType type) { int i = 0; using (cmd = new SqlCommand(text, GetOpenConnection())) { try { cmd.CommandType = type; i = cmd.ExecuteNonQuery(); } catch (Exception ex) { throw ex; } } return i; } /// <summary> /// 返回受影响的行数(重载,使用单个参数) /// </summary> /// <param name="text">要执行的SQL语句或者是存储过程</param> /// <param name="para">要使用的单个参数</param> /// <param name="type">执行SQL语句或者是存储过程</param> /// <returns></returns> public static int ExecuteNonQuery(string text, SqlParameter para, CommandType type) { int i = 0; using (cmd = new SqlCommand(text, GetOpenConnection())) { try { cmd.CommandType = type; cmd.Parameters.Add(para); i = cmd.ExecuteNonQuery(); } catch (Exception ex) { throw ex; } } return i; } /// <summary> /// 返回受影响的行数(重载,使用参数数组) /// </summary> /// <param name="text">要执行的SQL语句或者是存储过程</param> /// <param name="para">要使用的参数数组</param> /// <param name="type">执行SQL语句或者是存储过程</param> /// <returns></returns> public static int ExecuteNonQuery(string text, SqlParameter[] paras, CommandType type) { int i = 0; using (cmd = new SqlCommand(text, GetOpenConnection())) { try { cmd.CommandType = type; cmd.Parameters.AddRange(paras); i = cmd.ExecuteNonQuery(); } catch (Exception ex) { throw ex; } } return i; } #endregion } }
命名空间待修改:
1 namespace Paint.Command //待修改
异常处理待修改(默认为WinForm):
1 try 2 { 3 4 } 5 catch (Exception ex) 6 { 7 MessageBox.Show(ex.Message, "提示"); 8 }