public class DBHelper { private static string con = ConfigurationManager.ConnectionStrings["connString"].ConnectionString; /// <summary> /// 执行不带参的删除,修改的sql语句返回受影响行数 /// </summary> /// <param name="sql"></param> /// <returns></returns> public static int ExecuteCommand(string sql) { int num= 0; using(SqlConnection conn=new SqlConnection (con)) { conn.Open(); SqlCommand cmd = new SqlCommand(sql,conn); num = cmd.ExecuteNonQuery(); } return num; } /// <summary> /// 执行带参的删除,修改的sql语句返回受影响行数 /// </summary> /// <param name="sql"></param> /// <returns></returns> public static int ExecuteCommand(string sql,params SqlParameter[] para) { int num = 0; using (SqlConnection conn = new SqlConnection(con)) { conn.Open(); SqlCommand cmd = new SqlCommand(sql, conn); cmd.Parameters.AddRange(para); num = cmd.ExecuteNonQuery(); } return num; } /// <summary> /// 执行不带参的sql语句返回单个值 /// </summary> /// <param name="sql"></param> /// <returns></returns> public static int GetScalar(string sql) { int num=0; using(SqlConnection conn=new SqlConnection (con)) { conn.Open(); SqlCommand cmd = new SqlCommand(sql,conn); num =Convert .ToInt32 ( cmd.ExecuteScalar()); } return num; } /// <summary> /// 执行带参的sql语句返回单个值 /// </summary> /// <param name="sql"></param> /// <returns></returns> public static int GetScalar(string sql,params SqlParameter [] para) { int num = 0; using (SqlConnection conn = new SqlConnection(con)) { conn.Open(); SqlCommand cmd = new SqlCommand(sql, conn); cmd.Parameters.AddRange(para); num = Convert.ToInt32(cmd.ExecuteScalar()); } return num; } /// <summary> /// 执行不带参的sql语句SqlDataReader /// </summary> /// <param name="sql"></param> /// <returns></returns> public static SqlDataReader GetReader(string sql) { SqlDataReader rd =null; SqlConnection conn = new SqlConnection(con); conn.Open(); SqlCommand cmd = new SqlCommand(sql, conn); rd = cmd.ExecuteReader(CommandBehavior .CloseConnection); return rd; } /// <summary> /// 执行带参的sql语句SqlDataReader /// </summary> /// <param name="sql"></param> /// <returns></returns> public static SqlDataReader GetReader(string sql,params SqlParameter [] para) { SqlDataReader rd = null ; SqlConnection conn = new SqlConnection(con); conn.Open(); SqlCommand cmd = new SqlCommand(sql, conn); cmd.Parameters.AddRange(para); rd = cmd.ExecuteReader(CommandBehavior .CloseConnection); return rd; } /// <summary> /// 执行不带参的sql语句,返回一张表 /// </summary> /// <param name="sql"></param> /// <returns></returns> public static DataTable GetDataSet(string sql) { DataSet dset = new DataSet(); using (SqlConnection conn=new SqlConnection (con)) { conn.Open(); SqlDataAdapter adapter = new SqlDataAdapter(sql, conn); adapter.Fill(dset); } return dset.Tables[0];//返回DataSet中的第一个表 } /// <summary> /// 执行带参的sql语句,返回一张表 /// </summary> /// <param name="sql"></param> /// <returns></returns> public static DataTable GetDataSet(string sql,params SqlParameter [] para) { DataSet dset = new DataSet(); using (SqlConnection conn = new SqlConnection(con)) { conn.Open(); SqlDataAdapter adapter = new SqlDataAdapter(sql, conn); adapter.SelectCommand.Parameters.AddRange(para);//为SqlDataAdapter添加参数 adapter.Fill(dset); } return dset.Tables[0];//返回DataSet中的第一个表 } }