摘要: =空&&conn.State!=ConnectionState.Closed){conn.Close();conn.Open();}else{conn.Open(),}returnDBHelper.conn;}//////关闭连接//publicstaticvoidcloseCon(){if(Conn!=null&&Conn.State!
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Configuration; using System.Data.SqlClient; using System.Data; using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Configuration; using System.Data.SqlClient; using System.Data;
namespace TestPrj //空间名字 TestPrj { public class DBHelper //类名 DBHelper { //获取数据库连接字符串 private static string connStr = ConfigurationManager.ConnectionStrings["sqlserver"].ConnectionString; private static SqlConnection conn; public static SqlConnection Conn { get { if (conn == null) { conn = new SqlConnection(connStr); conn.Open(); } if (conn != null && conn.State != ConnectionState.Closed) { conn.Close(); conn.Open(); } else { conn.Open(); } return DBHelper.conn; } } /// <summary> /// 关闭连接 /// </summary>
public static void closeConn() { if (Conn != null && Conn.State != ConnectionState.Closed) { Conn.Close(); } } /// <summary> /// 查询操作 /// </summary> /// <param name="sql">传入以select打头的sql语句或存储过程 </param> /// <param name="type">命令类型 </param> /// <param name="pars">数组 </param> /// <returns></returns>
public static SqlDataReader getReader(string sql, CommandType type, params SqlParameter[] pars) { SqlCommand cmd = new SqlCommand(sql, Conn); cmd.CommandType = type; cmd.Parameters.AddRange(pars); return cmd.ExecuteReader(); } public static SqlDataReader getReader(string sql, CommandType type, params SqlParameter[] pars) { SqlCommand cmd = new SqlCommand(sql, Conn); cmd.CommandType = type; cmd.Parameters.AddRange(pars); return cmd.ExecuteReader(); } /// <summary> /// 执行非查询操作(执行增、删、改操作的),不支持事务 /// </summary> /// <param name="sql">传入以insert|update|delete打头的sql语句或存储过程 </param> /// <param name="type">命令类型 </param> /// <param name="pars">数组 </param> /// <returns></returns>
public static int ExecuteNoneQuery(string sql, CommandType type, params SqlParameter[] pars) { int result = 0; SqlCommand cmd = new SqlCommand(sql, Conn); cmd.CommandType = type; if (pars != null) { cmd.Parameters.AddRange(pars); } result = cmd.ExecuteNonQuery(); //关闭连接对象 CloseConn(); return result; } public static int ExecuteNoneQuery(string sql, CommandType type, params SqlParameter[] pars) { int result = 0; SqlCommand cmd = new SqlCommand(sql, Conn); cmd.CommandType = type; if (pars != null) { cmd.Parameters.AddRange(pars); } result = cmd.ExecuteNonQuery(); //关闭连接对象 CloseConn(); return result; } /// <summary> /// 非查询操作(执行增、删、改操作的),支持事务,进行批量操作 /// </summary> /// <param name="sql">传入以insert|update|delete打头的sql语句或存储过程 </param> /// <param name="conn">数据库连接 </param> /// <param name="type">命令类型 </param> /// <param name="pars">数组 </param> /// <returns></returns>
public static int ExecuteNoneQuery(string sql, SqlConnection conn, CommandType type, params SqlParameter[] pars) { int result = 0; //接受外部提供的数据连接对象,并且此连接在本次操作后不要关闭 SqlCommand cmd = new SqlCommand(sql, conn); cmd.CommandType = type; cmd.Parameters.AddRange(pars); result = cmd.ExecuteNonQuery(); return result; } public static int ExecuteNoneQuery(string sql, SqlConnection conn, CommandType type, params SqlParameter[] pars) { int result = 0; //接受外部提供的数据连接对象,并且此连接在本次操作后不要关闭 SqlCommand cmd = new SqlCommand(sql, conn); cmd.CommandType = type; cmd.Parameters.AddRange(pars); result = cmd.ExecuteNonQuery(); return result; } /// <summary> /// 获取单个值,返回首行首列 /// </summary> /// <param name="sql">传入以select打头的sql语句 </param> /// <param name="type">命令类型 </param> /// <param name="pars">数组 </param> /// <returns></returns>
public static object GetScalar(string sql, CommandType type, params SqlParameter[] pars) { object obj = null; SqlCommand cmd = new SqlCommand(sql, Conn); cmd.CommandType = type; cmd.Parameters.AddRange(pars); obj = cmd.ExecuteScalar(); //关闭连接对象 CloseConn(); return obj; } } }