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
}
}
命名空间待修改:
namespace Paint.Command //待修改
异常处理待修改(默认为WinForm):
try
{ }
catch (Exception ex)
{
MessageBox.Show(ex.Message, "提示");
}