public class DBHelper
{
private static string strConn = ConfigurationManager.ConnectionStrings["strConn"].ConnectionString;
private static SqlConnection conn = new SqlConnection(strConn);
/// <summary>
/// 获取数据库连接对象
/// </summary>
public static SqlConnection Conn
{
get
{
return conn;
}
}
/// <summary>
/// 打开连接
/// </summary>
public static void ConnOpen()
{
if (conn.State == System.Data.ConnectionState.Closed)
conn.Open();
}
/// <summary>
/// 关闭连接
/// </summary>
public static void ConnClose()
{
if (conn.State == System.Data.ConnectionState.Open)
conn.Close();
}
/// <summary>
/// 对数据进行增删查改
/// </summary>
/// <param name="strsql">sql 语句</param>
/// <returns></returns>
public static int ExecuteNonQuery(string strsql)
{
int i = 0;
try
{
SqlCommand comm = new SqlCommand();
comm.CommandText = strsql;//command需要执行的sql 语句
comm.Connection = conn;//连接对象
ConnOpen();//打开连接
i = comm.ExecuteNonQuery();//command方法,它的返回值类型为int型。多用于执行增加,删除,修改数据。返回受影响的行数。当select操作时,返回-1
}
catch (Exception e)
{
}
finally
{
ConnClose();//执行完command之后 进行关闭
}
return i;
}
/// <summary>
/// 带参数进行增删查改
/// </summary>
/// <param name="strsql"></param>
/// <param name="param"></param>
/// <returns></returns>
public static int ExecuteNonQuery(string strsql, SqlParameter[] param)
{
int i = 0;
try
{
SqlCommand cmd = new SqlCommand(strsql, conn);
cmd.Parameters.AddRange(param);
ConnOpen();
i = cmd.ExecuteNonQuery();
}
catch (Exception e)
{
}
finally
{
ConnClose();
}
return i;
}
//带参数的查询
public static SqlDataReader ExecuteReader(string strSql, params SqlParameter[] param)
{
SqlDataReader dr = null;
try
{
SqlCommand comm = new SqlCommand(strSql, conn);
comm.Parameters.AddRange(param);
ConnOpen();
dr = comm.ExecuteReader();
}
catch (Exception e)
{
}
finally
{
//在阅读器没有读取数据前不能关闭数据库连接,否则将读取不到数据
}
return dr;
}
/// <summary>
/// 查询是否有该数据
/// </summary>
/// <param name="strSql">数据操作的Sql语句</param>
/// <returns></returns>
public static bool Exists(string strSql, SqlParameter[] param)
{
bool bReturn = false;
try
{
SqlCommand comm = new SqlCommand(strSql, conn);
comm.Parameters.AddRange(param);
ConnOpen();
bReturn = (int)comm.ExecuteScalar() > 0;
}
catch (Exception e)
{
}
finally
{
ConnClose();
}
return bReturn;
}
/// <summary>
/// 查询是否有该数据
/// </summary>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <returns></returns>
public static int ExecuteScalar(string sql, params SqlParameter[] param)
{
int i = 0;
SqlCommand comm = new SqlCommand(sql, conn);
comm.Parameters.AddRange(param);
try
{
ConnOpen();
i = Convert.ToInt32(comm.ExecuteScalar().ToString());
}
catch (Exception)
{
}
finally
{
comm.Dispose();
}
return i;
}
public static DataSet GetAll(string sql)
{
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
DataSet ds = new DataSet();
da.Fill(ds);
return ds;
}
public static DataSet GetAll(string sql, SqlParameter[] param)
{
SqlCommand comm = new SqlCommand(sql, conn);
comm.Parameters.AddRange(param);
SqlDataAdapter da = new SqlDataAdapter(comm);
DataSet ds = new DataSet();
da.Fill(ds);
return ds;
}
/// <summary>
/// dataset 存储过程进行数据修改
/// </summary>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <returns></returns>
public static DataSet ExcuteDatasetProc(string sql, params SqlParameter[] param )
{
SqlCommand comm = new SqlCommand(sql, conn);
comm.Parameters.AddRange(param);
comm.CommandType = System.Data.CommandType.StoredProcedure;
ConnOpen();
using (SqlDataAdapter da = new SqlDataAdapter(comm))
{
DataSet ds = new DataSet();
da.Fill(ds);
comm.Parameters.Clear();
ConnClose();
return ds;
}
}
/// <summary>
/// 操作存储过程
/// </summary>
/// <param name="strSql">数据操作的Sql语句</param>
/// <returns></returns>
public static int ExecuteProcedure(string strSql, SqlParameter[] param)
{
int i = 0;
try
{
SqlCommand comm = new SqlCommand(strSql, conn);
comm.Parameters.AddRange(param);
comm.CommandType = System.Data.CommandType.StoredProcedure;
ConnOpen();
i = comm.ExecuteNonQuery();
}
catch (Exception e)
{
}
finally
{
ConnClose();
}
return i;
}
/// <summary>
/// 执行存储过程查询数据
/// </summary>
/// <param name="strSql">存储过程名称</param>
/// <param name="param">存储过程参数</param>
/// <returns></returns>
public static SqlDataReader ExecuteDataReaderProcedure(string strSql, SqlParameter[] param)
{
SqlDataReader dr = null;
try
{
SqlCommand comm = new SqlCommand(strSql, conn);
comm.Parameters.AddRange(param);
comm.CommandType = System.Data.CommandType.StoredProcedure;
ConnOpen();
dr = comm.ExecuteReader();
}
catch (Exception e)
{
}
finally
{
//在阅读器没有读取数据前不能关闭数据库连接,否则将读取不到数据
}
return dr;
}
}