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, "提示");
}