【SQLServer】DBHelper即C#数据库底层封装
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Configuration; using System.Data.SqlClient; using System.Data; namespace MYHelper { //不加public,只有dal私有引用; //封装执行sql server增删改查辅助类,提供相应执行sql语句和存储过程的方法 //作者:白宁超(http://www.cnblogs.com/baiboy/) static class SQLHelper { //读取配置文件中的连接字符串 static string connstr = ConfigurationManager.ConnectionStrings["sql"].ConnectionString; /// <summary> /// 执行非查询sql语句,返回受影响行数,如果执行非增删改则返回-1 /// </summary> /// <param name="sql">sql语句</param> /// <param name="paras">参数数组</param> /// <returns>影响行数res</returns> public static int ExecuteNonQuery(string sql, params SqlParameter[] paras) { int res = -1; using (SqlConnection conn = new SqlConnection(connstr)) { using (SqlCommand cmd = new SqlCommand(sql, conn)) { cmd.CommandType = CommandType.StoredProcedure; if (paras != null || paras.Length > 0) { cmd.Parameters.AddRange(paras); } conn.Open(); res = cmd.ExecuteNonQuery(); } } return res; } /// <summary> /// 执行非查询sql语句,返回受影响行数,如果执行非增删改则返回-1 /// </summary> /// <param name="sql">sql语句</param> /// <param name="paras">参数数组</param> /// <returns>影响行数res</returns> public static int ExecuteNonParaQuery(string sql) { int res = -1; using (SqlConnection conn = new SqlConnection(connstr)) { using (SqlCommand cmd = new SqlCommand(sql, conn)) { conn.Open(); res = cmd.ExecuteNonQuery(); } } return res; } /// <summary> /// 执行读取数据,返回一个对象 /// </summary> /// <param name="sql">sql语句</param> /// <param name="paras">参数数组</param> /// <returns>返回一个对象o</returns> public static object ExecuteScalar(string sql, params SqlParameter[] paras) { object o = null; using (SqlConnection conn = new SqlConnection(connstr)) { using (SqlCommand cmd = new SqlCommand(sql, conn)) { cmd.CommandType = CommandType.StoredProcedure; if (paras != null) { cmd.Parameters.AddRange(paras); } conn.Open(); o = cmd.ExecuteScalar(); } } return o; } /// <summary> /// 执行查询sql语句,返回一个对象 /// </summary> /// <param name="sql">sql语句</param> /// <param name="paras">查询参数</param> /// <returns>返回DataReader对象</returns> public static SqlDataReader ExecuteReader(string sql, params SqlParameter[] paras) { SqlConnection conn = new SqlConnection(connstr); using (SqlCommand cmd = new SqlCommand(sql, conn)) { cmd.CommandType = CommandType.StoredProcedure; if (paras != null) { cmd.Parameters.AddRange(paras); } conn.Open(); try { return cmd.ExecuteReader(CommandBehavior.CloseConnection); } catch (Exception ex) { cmd.Dispose(); throw ex; } } } /// <summary> /// 执行查询sql语句,返回一个无参数dataset对象 /// </summary> /// <param name="sql">sql语句</param> /// <param name="paras"></param> /// <returns>返回dataset 对象</returns> public static DataSet GetDataSetNotPara(string sql) { DataSet ds = new DataSet(); using (SqlConnection conn = new SqlConnection(connstr)) { using (SqlCommand cmd = new SqlCommand(sql, conn)) { //根据传来的参数。决定是sql语句还是存储过程 cmd.CommandType = CommandType.StoredProcedure; conn.Open(); using (SqlDataAdapter sda = new SqlDataAdapter(cmd)) { sda.Fill(ds); } } } return ds; } /// <summary> /// 执行查询sql语句,返回一个无参数dataset对象 /// </summary> /// <param name="sql">sql语句</param> /// <param name="paras"></param> /// <returns>返回dataset 对象</returns> public static DataTable GetDataTableNotPara(string sql) { DataTable dt = new DataTable(); using (SqlConnection conn = new SqlConnection(connstr)) { using (SqlCommand cmd = new SqlCommand(sql, conn)) { //根据传来的参数。决定是sql语句还是存储过程 conn.Open(); using (SqlDataAdapter sda = new SqlDataAdapter(cmd)) { sda.Fill(dt); } } } return dt; } /// <summary> /// 执行查询sql语句,返回一个dataset对象 /// </summary> /// <param name="sql">sql语句</param> /// <param name="paras">查询参数</param> /// <returns>返回dataset 对象</returns> public static DataSet GetDataSet(string sql, params SqlParameter[] paras) { DataSet ds = new DataSet(); using (SqlConnection conn = new SqlConnection(connstr)) { using (SqlCommand cmd = new SqlCommand(sql, conn)) { //根据传来的参数。决定是sql语句还是存储过程 cmd.CommandType = CommandType.StoredProcedure; //添加参数 cmd.Parameters.AddRange(paras); conn.Open(); using (SqlDataAdapter sda = new SqlDataAdapter(cmd)) { sda.Fill(ds); } } } return ds; } /// <summary> /// 可以执行sql语句或存储过程 /// </summary> /// <param name="text"></param> /// <param name="ct"></param> /// <param name="param"></param> /// <returns></returns> public static DataTable ProcGetTable(string sql, params SqlParameter[] param) { DataTable dt = new DataTable(); using (SqlConnection conn = new SqlConnection(connstr)) { using (SqlCommand cmd = new SqlCommand(sql, conn)) { //根据传来的参数。决定是sql语句还是存储过程 cmd.CommandType = CommandType.StoredProcedure; //添加参数 cmd.Parameters.AddRange(param); //cmd.Parameters.Add("@name", SqlDbType.NVarChar, 20).Value = param[0]; //cmd.Parameters.Add("@pwd", SqlDbType.NVarChar, 20).Value = param[1]; conn.Open(); using (SqlDataAdapter sda = new SqlDataAdapter(cmd)) { sda.Fill(dt); } } } return dt; } /// <summary> /// 实现分页功能 /// </summary> /// <param name="sql">sql语句</param> /// <param name="paras">参数数组(显示index页和每页显示条数size)</param> /// <returns>查询结果</returns> public static DataTable GetParaTable(string sql, params SqlParameter[] paras) { DataSet ds = new DataSet(); using (SqlConnection conn = new SqlConnection(connstr)) { using (SqlDataAdapter da = new SqlDataAdapter(sql, conn)) { if (paras != null) { da.SelectCommand.Parameters.AddRange(paras); } da.SelectCommand.CommandType = CommandType.StoredProcedure; da.Fill(ds); } } return ds.Tables[0]; } } }
作者:白宁超,工学硕士,现工作于四川省计算机研究院,研究方向是自然语言处理和机器学习。曾参与国家自然基金项目和四川省科技支撑计划等多个省级项目。著有《自然语言处理理论与实战》一书。 自然语言处理与机器学习技术交流群号:436303759 。
出处:http://www.cnblogs.com/baiboy/
本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。