常用的SqlHelper类
using System.Configuration; using System.Data; using System.Data.OleDb; namespace DAL { /// <summary> /// 数据库操作助手工具类 /// 本类为抽象类不可以被实例化,需要的时候直接调用 /// </summary> public abstract class DBHelper { //初始化字段 private static OleDbConnection conn = null; private static OleDbCommand cmd = null; #region GetConn /// <summary> /// 获取数据库连接 /// </summary> /// <returns>打开数据库连接</returns> private static OleDbConnection GetConn() { //从配置文件获取数据库连接字符串 string connStr = ConfigurationManager.ConnectionStrings["DBPath"].ConnectionString; conn = new OleDbConnection(connStr); //判断数据库当前状态并选择是否打开连接 if (conn.State == ConnectionState.Closed) { conn.Open(); } return conn; } #endregion #region ExecuteNonQuery /// <summary> /// 执行不带参数的增删改SQL语句或存储过程 /// </summary> /// <param name="sql">传入的SQL语句</param> /// <returns>受影响行数</returns> public static int ExecuteNonQuery(string sql) { using (conn = GetConn()) { using (cmd = conn.CreateCommand()) { cmd.CommandText = sql; return cmd.ExecuteNonQuery(); } } } #endregion #region ExecuteNonQuery带参 /// <summary> /// 执行带参数的增删改SQL语句或存储过程 /// </summary> /// <param name="sql">传入的SQL语句</param> /// <param name="parameters">传入的参数</param> /// <returns>受影响行数</returns> public static int ExecuteNonQuery(string sql, params OleDbParameter[] parameters) { using (conn = GetConn()) { using (cmd = conn.CreateCommand()) { cmd.CommandText = sql; foreach (OleDbParameter p in parameters) { cmd.Parameters.Add(p); } return cmd.ExecuteNonQuery(); } } } #endregion #region ExecuteNonQuery带命令类型 /// <summary> /// 执行不带参数的增删改SQL语句或存储过程 /// </summary> /// <param name="sql">传入的SQL语句</param> /// <param name="ct">命令类型</param> /// <returns>受影响行数</returns> public static int ExecuteNonQuery(string sql, CommandType ct) { using (conn = GetConn()) { using (cmd = conn.CreateCommand()) { cmd.CommandText = sql; cmd.CommandType = ct; return cmd.ExecuteNonQuery(); } } } #endregion #region ExecuteNonQuery带参和命令类型 /// <summary> /// 执行带参数的增删改SQL语句或存储过程 /// </summary> /// <param name="sql">传入的SQL语句</param> /// <param name="ct">命令类型</param> /// <param name="parameters">传入的参数</param> /// <returns>受影响行数</returns> public static int ExecuteNonQuery(string sql, CommandType ct, params OleDbParameter[] parameters) { using (conn = GetConn()) { using (cmd = conn.CreateCommand()) { cmd.CommandText = sql; cmd.CommandType = ct; foreach (OleDbParameter p in parameters) { cmd.Parameters.Add(p); } return cmd.ExecuteNonQuery(); } } } #endregion #region ExecuteScalar /// <summary> /// 执行不带参数的SQL取数据表第一行第一列的值 /// </summary> /// <param name="sql">传入的SQL语句</param> /// <returns>查询结果中第一行第一列的值</returns> public static object ExecuteScalar(string sql) { using (conn = GetConn()) { using (cmd = conn.CreateCommand()) { cmd.CommandText = sql; return cmd.ExecuteScalar(); } } } #endregion #region ExecuteScalar带参 /// <summary> /// 执行带参数的SQL取数据表第一行第一列的值 /// </summary> /// <param name="sql">传入的SQL语句</param> /// <param name="parameters">传入的参数</param> /// <returns>查询结果中第一行第一列的值</returns> public static object ExecuteScalar(string sql, params OleDbParameter[] parameters) { using (conn = GetConn()) { using (cmd = conn.CreateCommand()) { cmd.CommandText = sql; foreach (OleDbParameter p in parameters) { cmd.Parameters.Add(p); } return cmd.ExecuteScalar(); } } } #endregion #region ExecuteScalar带命令类型 /// <summary> /// 执行不带参数的SQL取数据表第一行第一列的值 /// </summary> /// <param name="sql">传入的SQL语句</param> /// <param name="ct">命令类型</param> /// <returns>查询结果中第一行第一列的值</returns> public static object ExecuteScalar(string sql, CommandType ct) { using (conn = GetConn()) { using (cmd = conn.CreateCommand()) { cmd.CommandText = sql; cmd.CommandType = ct; return cmd.ExecuteScalar(); } } } #endregion #region ExecuteScalar带参和命令类型 /// <summary> /// 执行带参数的SQL取数据表第一行第一列的值 /// </summary> /// <param name="sql">传入的SQL语句</param> /// <param name="ct">命令类型</param> /// <param name="parameters">传入的参数</param> /// <returns>查询结果中第一行第一列的值</returns> public static object ExecuteScalar(string sql, CommandType ct, params OleDbParameter[] parameters) { using (conn = GetConn()) { using (cmd = conn.CreateCommand()) { cmd.CommandText = sql; cmd.CommandType = ct; foreach (OleDbParameter p in parameters) { cmd.Parameters.Add(p); } return cmd.ExecuteScalar(); } } } #endregion #region ExecuteDataSet /// <summary> /// 执行不带参数的SQL取数据集 /// </summary> /// <param name="sql">传入的SQL语句</param> /// <returns>数据集</returns> public static DataSet ExecuteDataSet(string sql) { using (conn = GetConn()) { using (cmd = conn.CreateCommand()) { cmd.CommandText = sql; DataSet dataset = new DataSet(); OleDbDataAdapter adapter = new OleDbDataAdapter(cmd); adapter.Fill(dataset); return dataset; } } } #endregion #region ExecuteDataSet带参 /// <summary> /// 执行带参数的SQL取数据集 /// </summary> /// <param name="sql">传入的SQL语句</param> /// <param name="parameters">传入的参数</param> /// <returns>数据集</returns> public static DataSet ExecuteDataSet(string sql, params OleDbParameter[] parameters) { using (conn = GetConn()) { using (cmd = conn.CreateCommand()) { cmd.CommandText = sql; foreach (OleDbParameter p in parameters) { cmd.Parameters.Add(p); } DataSet dataset = new DataSet(); OleDbDataAdapter adapter = new OleDbDataAdapter(cmd); adapter.Fill(dataset); return dataset; } } } #endregion #region ExecuteDataSet带命令类型 /// <summary> /// 执行不带参数的SQL取数据集 /// </summary> /// <param name="sql">传入的SQL语句</param> /// <param name="ct">命令类型</param> /// <returns>数据集</returns> public static DataSet ExecuteDataSet(string sql, CommandType ct) { using (conn = GetConn()) { using (cmd = conn.CreateCommand()) { cmd.CommandText = sql; cmd.CommandType = ct; DataSet dataset = new DataSet(); OleDbDataAdapter adapter = new OleDbDataAdapter(cmd); adapter.Fill(dataset); return dataset; } } } #endregion #region ExecuteDataSet带参和命令类型 /// <summary> /// 执行带参数的SQL取数据集 /// </summary> /// <param name="sql">传入的SQL语句</param> /// <param name="ct">命令类型</param> /// <param name="parameters">传入的参数</param> /// <returns>数据集</returns> public static DataSet ExecuteDataSet(string sql, CommandType ct, params OleDbParameter[] parameters) { using (conn = GetConn()) { using (cmd = conn.CreateCommand()) { cmd.CommandText = sql; cmd.CommandType = ct; foreach (OleDbParameter p in parameters) { cmd.Parameters.Add(p); } DataSet dataset = new DataSet(); OleDbDataAdapter adapter = new OleDbDataAdapter(cmd); adapter.Fill(dataset); return dataset; } } } #endregion #region ExecuteDataTable /// <summary> /// 执行不带参数的SQL取数据表 /// </summary> /// <param name="sql">传入的SQL语句</param> /// <returns>数据表</returns> public static DataTable ExecuteDataTable(string sql) { using (conn = GetConn()) { using (cmd = conn.CreateCommand()) { cmd.CommandText = sql; DataSet dataset = new DataSet(); OleDbDataAdapter adapter = new OleDbDataAdapter(cmd); adapter.Fill(dataset); return dataset.Tables[0]; } } } #endregion #region ExecuteDataTable带参 /// <summary> /// 执行带参数的SQL取数据表 /// </summary> /// <param name="sql">传入的SQL语句</param> /// <param name="parameters">传入的参数</param> /// <returns>数据表</returns> public static DataTable ExecuteDataTable(string sql, params OleDbParameter[] parameters) { using (conn = GetConn()) { using (cmd = conn.CreateCommand()) { cmd.CommandText = sql; foreach (OleDbParameter p in parameters) { cmd.Parameters.Add(p); } DataSet dataset = new DataSet(); OleDbDataAdapter adapter = new OleDbDataAdapter(cmd); adapter.Fill(dataset); return dataset.Tables[0]; } } } #endregion #region ExecuteDataTable带命令类型 /// <summary> /// 执行不带参数的SQL取数据表 /// </summary> /// <param name="sql">传入的SQL语句</param> /// <param name="ct">命令类型</param> /// <returns>数据表</returns> public static DataTable ExecuteDataTable(string sql, CommandType ct) { using (conn = GetConn()) { using (cmd = conn.CreateCommand()) { cmd.CommandText = sql; cmd.CommandType = ct; DataSet dataset = new DataSet(); OleDbDataAdapter adapter = new OleDbDataAdapter(cmd); adapter.Fill(dataset); return dataset.Tables[0]; } } } #endregion #region ExecuteDataTable带参和命令类型 /// <summary> /// 执行带参数的SQL取数据表 /// </summary> /// <param name="sql">传入的SQL语句</param> /// <param name="ct">命令类型</param> /// <param name="parameters">传入的参数</param> /// <returns>数据表</returns> public static DataTable ExecuteDataTable(string sql, CommandType ct, params OleDbParameter[] parameters) { using (conn = GetConn()) { using (cmd = conn.CreateCommand()) { cmd.CommandText = sql; cmd.CommandType = ct; foreach (OleDbParameter p in parameters) { cmd.Parameters.Add(p); } DataSet dataset = new DataSet(); OleDbDataAdapter adapter = new OleDbDataAdapter(cmd); adapter.Fill(dataset); return dataset.Tables[0]; } } } #endregion } }
专注互联网开发的:斯通麦博