namespace DAL{ public class SQLHelper { //定义一个连接字符串 string connstr = ConfigurationManager.ConnectionStrings["Constr"].ConnectionString; #region 执行非查询语句,返回受影响的行数 /// <summary> /// 执行非查询语句,返回受影响的行数 /// </summary> /// <param name="sql"></param> /// <param name="ct"></param> /// <param name="parameters"></param> /// <returns></returns> public int ExecuteNonQuery(string sql, CommandType ct, params SqlParameter[] parameters) { using (SqlConnection conn = new SqlConnection(connstr)) { conn.Open(); using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = sql; cmd.CommandType = ct; foreach (SqlParameter parameter in parameters) { cmd.Parameters.Add(parameter); } return cmd.ExecuteNonQuery(); } } } #endregion #region 执行sql语句,返回第一行第一列的值 /// <summary> /// 执行SQL语句,返回第一行第一列的值,因为不知道类型,所以用Object /// </summary> /// <param name="sql"></param> /// <param name="ct"></param> /// <param name="parameters"></param> /// <returns></returns> public object ExecuteScalar(string sql, CommandType ct, params SqlParameter[] parameters) { using (SqlConnection conn = new SqlConnection(connstr)) { conn.Open(); using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = sql; cmd.CommandType = ct; foreach (SqlParameter parameter in parameters) { cmd.Parameters.Add(parameter); } return cmd.ExecuteScalar(); } } } #endregion #region 执行sql语句,返回一个表 /// <summary> /// 执行sql语句,返回一个表 /// </summary> /// <param name="sql"></param> /// <param name="ct"></param> /// <param name="parameters"></param> /// <returns></returns> public DataTable ExecuteDataTable(string sql, CommandType ct, params SqlParameter[] parameters) { using (SqlConnection conn = new SqlConnection(connstr)) { conn.Open(); using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = sql; cmd.CommandType = ct; foreach (SqlParameter parameter in parameters) { cmd.Parameters.Add(parameter); } DataSet ds = new DataSet(); SqlDataAdapter adapter = new SqlDataAdapter(cmd); adapter.Fill(ds); return ds.Tables[0]; } } } #endregion }} |
|