通过分装的方法：
public class SQLHelper
{
private static readonly string ConnectionString = ConfigurationManager.AppSettings["conn"].ToString();
//SqlParameter[] 方便传递数组
/// <summary>
/// 主要用于封装Command对象的ExecuteNonQuery方法,用于数据的增删改
/// </summary>
/// <param name="conn">Connection对象</param>
/// <param name="cmdText">Command.CommandText</param>
/// <param name="cmdType">Command.CommandType</param>
/// <param name="cmdParams">Command.Parameters</param>
/// <returns>返回受影响的行数</returns>
public static int ExecuteNonQuery(string cmdText, CommandType cmdType, params SqlParameter[] cmdParams)
{
SqlConnection conn = new SqlConnection(ConnectionString);
SqlCommand comm = new SqlCommand();
PrepareCommand(comm, conn, cmdText, cmdType, cmdParams);
try
{
return comm.ExecuteNonQuery();
}
catch (SqlException ex)
{
throw ex;
}
finally {
conn.Close();
}
}
/// <summary>
/// 封装Command对象的ExecuteReader 方法用于数据的查询
/// </summary>
/// <param name="conn">Connection对象</param>
/// <param name="cmdText">Command.CommandText</param>
/// <param name="cmdType">Command.CommandType</param>
/// <param name="cmdParams">Command.Parameters</param>
/// <returns>返回SqlDataReader对象</returns>
public static SqlDataReader ExcuteReader(string cmdText, CommandType cmdType, params SqlParameter[] cmdParams)
{
SqlConnection conn = new SqlConnection(ConnectionString);
SqlCommand comm = new SqlCommand();
PrepareCommand(comm, conn, cmdText, cmdType, cmdParams);
try
{
//自动关闭
return comm.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (SqlException ex)
{
throw ex;
}
}
/// <summary>
/// 封装Commond对象的ExecuteScalar方法，用于返回首行首列数据
/// </summary>
/// <param name="conn">Connection对象</param>
/// <param name="cmdText">Command.CommandText</param>
/// <param name="cmdType">Command.CommandType</param>
/// <param name="cmdParams">Command.Parameters</param>
/// <returns>返回的是object单一的值</returns>
public static object ExecuteScalar(string cmdText, CommandType cmdType, params SqlParameter[] cmdParams)
{
SqlConnection conn = new SqlConnection(ConnectionString);
SqlCommand comm = new SqlCommand();
PrepareCommand(comm, conn, cmdText, cmdType, cmdParams);
try
{
return comm.ExecuteScalar();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
/// <summary>
/// 主要用于返回DataTable 查询的数据
/// </summary>
/// <param name="conn">Connection对象</param>
/// <param name="cmdText">Command.CommandText</param>
/// <param name="cmdType">Command.CommandType</param>
/// <param name="cmdParams">Command.Parameters</param>
/// <returns>返回DataTable对象</returns>
public static DataTable GetDataTable(string cmdText, CommandType cmdType, params SqlParameter[] cmdParams)
{
SqlConnection conn = new SqlConnection();
SqlCommand comm = new SqlCommand();
PrepareCommand(comm, conn, cmdText, cmdType, cmdParams);
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = comm;
DataSet ds = new DataSet();
try
{
//自动打开自动关闭 实现断开式的链接
da.Fill(ds);
return ds.Tables[0];
}
catch (SqlException ex)
{
throw ex;
}
finally {
conn.Close();
}
}
/// <summary>
/// 主要用于给Command对象进行初始化赋值工作
/// </summary>
/// <param name="comm">是操作的Comman对象</param>
/// <param name="conn">Connection对象</param>
/// <param name="cmdText">Command.CommandText</param>
/// <param name="cmdType">Command.CommandType</param>
/// <param name="cmdParams">Command.Parameters</param>
private static void PrepareCommand(SqlCommand comm, SqlConnection conn, string cmdText, CommandType cmdType, SqlParameter[] cmdParams)
{
if (conn.State == ConnectionState.Closed) conn.Open();
comm.Connection = conn;
comm.CommandText = cmdText;
comm.CommandType = cmdType;
if (cmdParams != null)
{
for (int i = 0; i < cmdParams.Length; i++)
{
comm.Parameters.Add(cmdParams[i]);
}
}
}
}
Access数据库的帮助类：
public class AccessHelper
{
private static readonly string ConnectionString = ConfigurationManager.AppSettings["conn"].ToString();
//SqlParameter[] 方便传递数组
/// <summary>
/// 主要用于封装Command对象的ExecuteNonQuery方法,用于数据的增删改
/// </summary>
/// <param name="conn">Connection对象</param>
/// <param name="cmdText">Command.CommandText</param>
/// <param name="cmdType">Command.CommandType</param>
/// <param name="cmdParams">Command.Parameters</param>
/// <returns>返回受影响的行数</returns>
public static int ExecuteNonQuery(string cmdText, CommandType cmdType, params OleDbParameter[] cmdParams)
{
OleDbConnection conn = new OleDbConnection(ConnectionString);
OleDbCommand comm = new OleDbCommand();
PrepareCommand(comm, conn, cmdText, cmdType, cmdParams);
try
{
return comm.ExecuteNonQuery();
}
catch (OleDbException ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
/// <summary>
/// 封装Command对象的ExecuteReader 方法用于数据的查询
/// </summary>
/// <param name="conn">Connection对象</param>
/// <param name="cmdText">Command.CommandText</param>
/// <param name="cmdType">Command.CommandType</param>
/// <param name="cmdParams">Command.Parameters</param>
/// <returns>返回SqlDataReader对象</returns>
public static OleDbDataReader ExcuteReader(string cmdText, CommandType cmdType, params OleDbParameter[] cmdParams)
{
OleDbConnection conn = new OleDbConnection(ConnectionString);
OleDbCommand comm = new OleDbCommand();
PrepareCommand(comm, conn, cmdText, cmdType, cmdParams);
try
{
//自动关闭
return comm.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (OleDbException ex)
{
throw ex;
}
}
/// <summary>
/// 封装Commond对象的ExecuteScalar方法，用于返回首行首列数据
/// </summary>
/// <param name="conn">Connection对象</param>
/// <param name="cmdText">Command.CommandText</param>
/// <param name="cmdType">Command.CommandType</param>
/// <param name="cmdParams">Command.Parameters</param>
/// <returns>返回的是object单一的值</returns>
public static object ExecuteScalar(string cmdText, CommandType cmdType, params OleDbParameter[] cmdParams)
{
OleDbConnection conn = new OleDbConnection(ConnectionString);
OleDbCommand comm = new OleDbCommand();
PrepareCommand(comm, conn, cmdText, cmdType, cmdParams);
try
{
return comm.ExecuteScalar();
}
catch (OleDbException ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
/// <summary>
/// 主要用于返回DataTable 查询的数据
/// </summary>
/// <param name="conn">Connection对象</param>
/// <param name="cmdText">Command.CommandText</param>
/// <param name="cmdType">Command.CommandType</param>
/// <param name="cmdParams">Command.Parameters</param>
/// <returns>返回DataTable对象</returns>
public static DataTable GetDataTable(string cmdText, CommandType cmdType, params OleDbParameter[] cmdParams)
{
OleDbConnection conn = new OleDbConnection(ConnectionString);
OleDbCommand comm = new OleDbCommand();
PrepareCommand(comm, conn, cmdText, cmdType, cmdParams);
OleDbDataAdapter da = new OleDbDataAdapter();
da.SelectCommand = comm;
DataSet ds = new DataSet();
try
{
//自动打开自动关闭 实现断开式的链接
da.Fill(ds);
return ds.Tables[0];
}
catch (OleDbException ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
/// <summary>
/// 主要用于给Command对象进行初始化赋值工作
/// </summary>
/// <param name="comm">是操作的Comman对象</param>
/// <param name="conn">Connection对象</param>
/// <param name="cmdText">Command.CommandText</param>
/// <param name="cmdType">Command.CommandType</param>
/// <param name="cmdParams">Command.Parameters</param>
private static void PrepareCommand(OleDbCommand comm, OleDbConnection conn, string cmdText, CommandType cmdType, OleDbParameter[] cmdParams)
{
if (conn.State == ConnectionState.Closed) conn.Open();
comm.Connection = conn;
comm.CommandText = cmdText;
comm.CommandType = cmdType;
if (cmdParams != null)
{
for (int i = 0; i < cmdParams.Length; i++)
{
comm.Parameters.Add(cmdParams[i]);
}
}
}
}
比较全的写法：
public static class DBHelper
{
private static SqlConnection connection;
public static SqlConnection Connection
{
get
{
string connectionString = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
if (connection == null)
{
connection = new SqlConnection(connectionString);
connection.Open();
}
else if (connection.State == System.Data.ConnectionState.Closed)
{
connection.Open();
}
else if (connection.State == System.Data.ConnectionState.Broken)
{
connection.Close();
connection.Open();
}
return connection;
}
}
public static int ExecuteCommand(string safeSql)
{
SqlCommand cmd = new SqlCommand(safeSql, Connection);
int result = cmd.ExecuteNonQuery();
return result;
}
public static int ExecuteCommand(string sql, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
return cmd.ExecuteNonQuery();
}
public static int GetScalar(string safeSql)
{
SqlCommand cmd = new SqlCommand(safeSql, Connection);
int result = Convert.ToInt32(cmd.ExecuteScalar());
return result;
}
public static int GetScalar(string sql, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
int result = Convert.ToInt32(cmd.ExecuteScalar());
return result;
}
public static SqlDataReader GetReader(string safeSql)
{
SqlCommand cmd = new SqlCommand(safeSql, Connection);
SqlDataReader reader = cmd.ExecuteReader();
return reader;
}
public static SqlDataReader GetReader(string sql, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
SqlDataReader reader = cmd.ExecuteReader();
return reader;
}
public static DataTable GetDataSet(string safeSql)
{
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(safeSql, Connection);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
return ds.Tables[0];
}
public static DataTable GetDataSet(string sql, params SqlParameter[] values)
{
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
return ds.Tables[0];
}
public static object GetValueByWhetherNull(object obj) {
return obj == null ? (object)DBNull.Value : obj;
}
}
通过重载：
public class DataBaseHelper
{
/// <summary>
/// 定义静态的连接字符串
/// </summary>
private static string ConnString
{
get {
return System.Configuration.ConfigurationManager.ConnectionStrings["ErpConnString"].ConnectionString;
}
}
/// <summary>
/// 用于执行一个insert/update/delete语句或则相应的存储过程
/// </summary>
/// <param name="cmdText">insert/update/delete SQL语句或则 存储过程</param>
/// <param name="type">指定命令的类型</param>
/// <param name="pars">参数集合</param>
/// <returns>int</returns>
private static int ExcuteSqlReturnInt(string cmdText, CommandType type, params SqlParameter[] pars)
{
//实例化连接对象
SqlConnection conn = new SqlConnection();
try
{
//打开连接对象
if (conn.State == ConnectionState.Closed || conn.State == ConnectionState.Broken)
{
conn.Open();
}
//实例化命令对象
SqlCommand sqlcommand = new SqlCommand(cmdText, conn);
//对命令对象参数集合赋值
if (pars != null && pars.Length > 0)
{
foreach (SqlParameter p in pars)
{
sqlcommand.Parameters.Add(p);
}
}
//给命令对象指定类型
sqlcommand.CommandType = type;
//通过ExecuteNonQuery执行数据库命令，并返回数据库受影响的行数。
int count = sqlcommand.ExecuteNonQuery();
return count;
}
catch (Exception ex)
{
return 0;
}
finally
{
conn.Close();
}
}
/// <summary>
/// 执行一个Select语句或则相关的存储过程 并返回DataSet
/// </summary>
/// <param name="cmdText">Select语句或则相关的存储过程</param>
/// <param name="type">命令对象类型</param>
/// <param name="pars">Select语句或则相关的存储过程 所需的参数</param>
/// <returns>DataSet</returns>
private static DataSet SelectSQLReturnDataSet(string cmdText, CommandType type, params SqlParameter[] pars)
{
SqlConnection conn = new SqlConnection(ConnString);
SqlDataAdapter sqldataadapter = new SqlDataAdapter(cmdText, conn);
if (pars != null && pars.Length > 0)
{
foreach (SqlParameter p in pars)
{
sqldataadapter.SelectCommand.Parameters.Add(p);
}
}
sqldataadapter.SelectCommand.CommandType = type;
DataSet dt = new DataSet();
sqldataadapter.Fill(dt);
return dt;
}
/// <summary>
/// 执行一个Select语句或则相关的存储过程 并返回DataSet
/// </summary>
/// <param name="cmdText">Select语句或则相关的存储过程</param>
/// <param name="type">命令对象类型</param>
/// <param name="pars">Select语句或则相关的存储过程 所需的参数</param>
/// <returns>DataSet</returns>
private static DataSet SelectSQLReturnDataSet(string cmdText, CommandType type)
{
SqlConnection conn = new SqlConnection(ConnString);
SqlDataAdapter sqldataadapter = new SqlDataAdapter(cmdText, conn);
sqldataadapter.SelectCommand.CommandType = type;
DataSet dt = new DataSet();
sqldataadapter.Fill(dt);
return dt;
}
/// <summary>
/// 执行一个Select语句或则相关的存储过程 并返回DataTable
/// </summary>
/// <param name="cmdText">Select语句或则相关的存储过程</param>
/// <param name="type">命令对象类型</param>
/// <param name="pars">Select语句或则相关的存储过程 所需的参数</param>
/// <returns>DataTable</returns>
private static DataTable SelectSQLReturnTable(string cmdText, CommandType type, SqlParameter[] pars)
{
SqlConnection conn = new SqlConnection(ConnString);
SqlDataAdapter sqldataadatapter = new SqlDataAdapter(cmdText, conn);
if (pars != null && pars.Length > 0)
{
foreach (SqlParameter p in pars)
{
sqldataadatapter.SelectCommand.Parameters.Add(p);
}
}
sqldataadatapter.SelectCommand.CommandType = type;
DataTable dt = new DataTable();
sqldataadatapter.Fill(dt);
return dt;
}
/// <summary>
/// 执行一个Select语句或则相关的存储过程 并返回DataTable
/// </summary>
/// <param name="cmdText">Select语句或则相关的存储过程</param>
/// <param name="type">命令对象类型</param>
/// <param name="pars">Select语句或则相关的存储过程 所需的参数</param>
/// <returns>DataTable</returns>
private static DataTable SelectSQLReturnTable(string cmdText, CommandType type)
{
SqlConnection conn = new SqlConnection(ConnString);
SqlDataAdapter sqldataadatapter = new SqlDataAdapter(cmdText, conn);
sqldataadatapter.SelectCommand.CommandType = type;
DataTable dt = new DataTable();
sqldataadatapter.Fill(dt);
return dt;
}
/// <summary>
/// 执行一个Select语句或则相关的存储过程 并返回查询对象
/// </summary>
/// <param name="cmdText">Select语句或则相关的存储过程</param>
/// <param name="type">命令对象类型</param>
/// <param name="pars">Select语句或则相关的存储过程 所需的参数</param>
/// <returns>object</returns>
private static object SelectSQLReturnObject(string cmdText, CommandType type, SqlParameter[] pars)
{
SqlConnection conn = new SqlConnection(ConnString);
try
{
SqlCommand cmd = new SqlCommand(cmdText, conn);
if (pars != null && pars.Length > 0)
{
foreach (SqlParameter p in pars)
{
cmd.Parameters.Add(pars);
}
}
if (conn.State == ConnectionState.Closed || conn.State == ConnectionState.Broken)
{
conn.Open();
}
cmd.CommandType = type;
object obj = cmd.ExecuteScalar();
return obj;
}
catch (Exception ex)
{
return null;
}
finally
{
conn.Close();
}
}
}
/// <summary>
/// 执行一个Select语句或则相关的存储过程 并返回一个数据阅读器对象
/// </summary>
/// <param name="cmdText">Select语句或则相关的存储过程</param>
/// <param name="type">指定命令对象的类型</param>
/// <param name="pars">参数结合</param>
/// <returns>SqlDataReader</returns>
private static SqlDataReader SelectSQLReturnReader(string cmdText, CommandType type, SqlParameter[] pars)
{
SqlConnection conn = new SqlConnection(ConnString);
try
{
SqlCommand cmd = new SqlCommand(cmdText, conn);
if (pars != null && pars.Length > 0)
{
foreach (SqlParameter p in pars)
{
cmd.Parameters.Add(p);
}
}
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
cmd.CommandType = type;
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return reader;
}
catch (Exception ex)
{
return null;
}
}
重载的方法：
/// <summary>
/// 实现所有对SQL Server数据库的所有访问操作
/// </summary>
public class SqlDBHelp
{
private static string _connStr = "server=.uid=sa;pwd=;database=B2C";
private static SqlConnection sqlcon;
/// <summary>
/// 获取一个可用于数据库操作的连接类
/// </summary>
private static SqlConnection Connection
{
get
{
if (sqlcon == null)
{
sqlcon = new SqlConnection(_connStr);
sqlcon.Open();
}
else if (sqlcon.State == ConnectionState.Broken || sqlcon.State == ConnectionState.Closed)
{
sqlcon.Close();
sqlcon.Open();
}
return sqlcon;
}
}
/// <summary>
/// 根据查询的语句返回执行受影响的行数
/// </summary>
/// <param name="strsql">Insert、Update、Delete语句</param>
/// <returns>执行受影响的行数</returns>
public static int GetExecute(string strsql)
{
int i=-1;
try
{
SqlCommand sqlcmd = new SqlCommand(strsql, Connection);
i= sqlcmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
CloseConnection();
}
return i;
}
/// <summary>
/// 根据查询的语句返回执行受影响的行数
/// </summary>
/// <param name="strsql">Insert、Update、Delete语句</param>
/// <param name="p">给SQL语句传递的参数集合</param>
/// <returns>执行受影响的行数</returns>
public static int GetExecute(string strsql,params SqlParameter[] p)
{
int i = -1;
try
{
SqlCommand sqlcmd = new SqlCommand(strsql, Connection);
sqlcmd.Parameters.AddRange(p);
i = sqlcmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
CloseConnection();
}
return i;
}
/// <summary>
/// 根据查询的语句获取查询的结果集
/// </summary>
/// <param name="strsql">Select语句</param>
/// <returns>查询的结果-表数据</returns>
public static DataTable GetTable(string strsql)
{
DataTable dt = null;
try
{
SqlDataAdapter sda = new SqlDataAdapter(strsql, Connection);
dt = new DataTable();
sda.Fill(dt);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
CloseConnection();
}
return dt;
}
/// <summary>
/// 根据查询的语句获取查询的结果集
/// </summary>
/// <param name="strsql">Select语句</param>
/// <param name="p">给SQL语句传递的参数集合</param>
/// <returns>查询的结果-表数据</returns>
public static DataTable GetTable(string strsql,params SqlParameter[] p)
{
DataTable dt = null;
try
{
SqlDataAdapter sda = new SqlDataAdapter(strsql, Connection);
sda.SelectCommand.Parameters.AddRange(p);
dt = new DataTable();
sda.Fill(dt);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
CloseConnection();
}
return dt;
}
/// <summary>
/// 根据查询的语句返回一个值
/// </summary>
/// <param name="strsql">Select语句</param>
/// <returns>单值</returns>
public static string GetSingle(string strsql)
{
object o = "";
try
{
SqlCommand sqlcmd = new SqlCommand(strsql, Connection);
o = sqlcmd.ExecuteScalar();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
CloseConnection();
}
return o.ToString();
}
/// <summary>
/// 根据查询的语句返回一个值
/// </summary>
/// <param name="strsql">Select语句</param>
/// <param name="p">给SQL语句传递的参数集合</param>
/// <returns>单值</returns>
public static string GetSingle(string strsql,params SqlParameter[] p)
{
object o = "";
try
{
SqlCommand sqlcmd = new SqlCommand(strsql, Connection);
sqlcmd.Parameters.AddRange(p);
o = sqlcmd.ExecuteScalar();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
CloseConnection();
}
return o.ToString();
}
/// <summary>
/// 根据查询语句返回轻量级的SqlDataReader对象
/// </summary>
/// <param name="strsql">Select语句</param>
/// <returns>轻量级的SqlDataReader对象</returns>
public static SqlDataReader GetReader(string strsql)
{
SqlCommand sqlcmd = new SqlCommand(strsql,Connection);
return sqlcmd.ExecuteReader(CommandBehavior.CloseConnection);
}
/// <summary>
/// 根据查询语句返回轻量级的SqlDataReader对象
/// </summary>
/// <param name="strsql">Select语句</param>
/// <param name="p">给SQL语句传递的参数集合</param>
/// <returns>轻量级的SqlDataReader对象</returns>
public static SqlDataReader GetReader(string strsql,params SqlParameter[] p)
{
SqlCommand sqlcmd = new SqlCommand(strsql, Connection);
sqlcmd.Parameters.AddRange(p);
return sqlcmd.ExecuteReader(CommandBehavior.CloseConnection);
}
public static bool GetTransOperate(string[] strsqls)
{
bool isflag = false;
SqlTransaction trans=Connection.BeginTransaction();
SqlCommand sqlcmd = new SqlCommand();
try
{
foreach (string s in strsqls)
{
sqlcmd.CommandText = s;
sqlcmd.Connection = sqlcon;
sqlcmd.ExecuteNonQuery();
}
isflag = true;
trans.Commit();
}
catch (Exception ex)
{
isflag = false;
trans.Rollback();
throw new Exception(ex.Message);
}
finally
{
CloseConnection();
}
return isflag;
}
/// <summary>
/// 关闭数据库连接
/// </summary>
private static void CloseConnection()
{
if (sqlcon != null)
{
sqlcon.Close();
}
}
}
