SQLHelper帮助类

SQLHelper帮助类

通过分装的方法：

public class SQLHelper { private static readonly string ConnectionString = ConfigurationManager.AppSettings["conn"].ToString(); //SqlParameter[] 方便传递数组 ///

/// 主要用于封装Command对象的ExecuteNonQuery方法,用于数据的增删改 ///

/// Connection对象 /// Command.CommandText /// Command.CommandType /// Command.Parameters /// 返回受影响的行数 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(); } } ///

/// 封装Command对象的ExecuteReader 方法用于数据的查询 ///

/// Connection对象 /// Command.CommandText /// Command.CommandType /// Command.Parameters /// 返回SqlDataReader对象 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; } } ///

/// 封装Commond对象的ExecuteScalar方法，用于返回首行首列数据 ///

/// Connection对象 /// Command.CommandText /// Command.CommandType /// Command.Parameters /// 返回的是object单一的值 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(); } } ///

/// 主要用于返回DataTable 查询的数据 ///

/// Connection对象 /// Command.CommandText /// Command.CommandType /// Command.Parameters /// 返回DataTable对象 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(); } } ///

/// 主要用于给Command对象进行初始化赋值工作 ///

/// 是操作的Comman对象 /// Connection对象 /// Command.CommandText /// Command.CommandType /// Command.Parameters 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[] 方便传递数组 ///

/// 主要用于封装Command对象的ExecuteNonQuery方法,用于数据的增删改 ///

/// Connection对象 /// Command.CommandText /// Command.CommandType /// Command.Parameters /// 返回受影响的行数 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(); } } ///

/// 封装Command对象的ExecuteReader 方法用于数据的查询 ///

/// Connection对象 /// Command.CommandText /// Command.CommandType /// Command.Parameters /// 返回SqlDataReader对象 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; } } ///

/// 封装Commond对象的ExecuteScalar方法，用于返回首行首列数据 ///

/// Connection对象 /// Command.CommandText /// Command.CommandType /// Command.Parameters /// 返回的是object单一的值 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(); } } ///

/// 主要用于返回DataTable 查询的数据 ///

/// Connection对象 /// Command.CommandText /// Command.CommandType /// Command.Parameters /// 返回DataTable对象 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(); } } ///

/// 主要用于给Command对象进行初始化赋值工作 ///

/// 是操作的Comman对象 /// Connection对象 /// Command.CommandText /// Command.CommandType /// Command.Parameters 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 { ///

/// 定义静态的连接字符串 ///

private static string ConnString { get { return System.Configuration.ConfigurationManager.ConnectionStrings["ErpConnString"].ConnectionString; } } ///

/// 用于执行一个insert/update/delete语句或则相应的存储过程 ///

/// insert/update/delete SQL语句或则 存储过程 /// 指定命令的类型 /// 参数集合 /// int 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(); } } ///

/// 执行一个Select语句或则相关的存储过程 并返回DataSet ///

/// Select语句或则相关的存储过程 /// 命令对象类型 /// Select语句或则相关的存储过程 所需的参数 /// DataSet 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; } ///

/// 执行一个Select语句或则相关的存储过程 并返回DataSet ///

/// Select语句或则相关的存储过程 /// 命令对象类型 /// Select语句或则相关的存储过程 所需的参数 /// DataSet 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; } ///

/// 执行一个Select语句或则相关的存储过程 并返回DataTable ///

/// Select语句或则相关的存储过程 /// 命令对象类型 /// Select语句或则相关的存储过程 所需的参数 /// DataTable 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; } ///

/// 执行一个Select语句或则相关的存储过程 并返回DataTable ///

/// Select语句或则相关的存储过程 /// 命令对象类型 /// Select语句或则相关的存储过程 所需的参数 /// DataTable 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; } ///

/// 执行一个Select语句或则相关的存储过程 并返回查询对象 ///

/// Select语句或则相关的存储过程 /// 命令对象类型 /// Select语句或则相关的存储过程 所需的参数 /// object 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(); } } }

///

/// 执行一个Select语句或则相关的存储过程 并返回一个数据阅读器对象 ///

/// Select语句或则相关的存储过程 /// 指定命令对象的类型 /// 参数结合 /// SqlDataReader 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; } }

重载的方法：

///

/// 实现所有对SQL Server数据库的所有访问操作 ///

public class SqlDBHelp { private static string _connStr = "server=.uid=sa;pwd=;database=B2C"; private static SqlConnection sqlcon; ///

/// 获取一个可用于数据库操作的连接类 ///

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; } } ///

/// 根据查询的语句返回执行受影响的行数 ///

/// Insert、Update、Delete语句 /// 执行受影响的行数 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; } ///

/// 根据查询的语句返回执行受影响的行数 ///

/// Insert、Update、Delete语句 /// 给SQL语句传递的参数集合 /// 执行受影响的行数 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; } ///

/// 根据查询的语句获取查询的结果集 ///

/// Select语句 /// 查询的结果-表数据 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; } ///

/// 根据查询的语句获取查询的结果集 ///

/// Select语句 /// 给SQL语句传递的参数集合 /// 查询的结果-表数据 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; } ///

/// 根据查询的语句返回一个值 ///

/// Select语句 /// 单值 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(); } ///

/// 根据查询的语句返回一个值 ///

/// Select语句 /// 给SQL语句传递的参数集合 /// 单值 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(); } ///

/// 根据查询语句返回轻量级的SqlDataReader对象 ///

/// Select语句 /// 轻量级的SqlDataReader对象 public static SqlDataReader GetReader(string strsql) { SqlCommand sqlcmd = new SqlCommand(strsql,Connection); return sqlcmd.ExecuteReader(CommandBehavior.CloseConnection); } ///

/// 根据查询语句返回轻量级的SqlDataReader对象 ///

/// Select语句 /// 给SQL语句传递的参数集合 /// 轻量级的SqlDataReader对象 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; } ///

/// 关闭数据库连接 ///

private static void CloseConnection() { if (sqlcon != null) { sqlcon.Close(); } } }

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。