数据库访问通用类(SQL server)
using System; using System.Configuration; using System.Data; using System.Data.SqlClient; namespace DBHelper { public class sqlServerDBHelper { //<connectionStrings> //<add name = "connStr" connectionString="server=.;database=TestBase;uid=lyc;pwd=123456" providerName="System.Data.SqlClient"/> //</connectionStrings> private static readonly string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString; //1、执行增删改查任务,Insert、Delete、Update //2、执行返回一个结果的查询 //3、执行查询,返回sqldatareader //4、执行查询,返回dataset //5、执行查询,返回datatable /// <summary> /// 执行T-SQL ,返回受影响的行数 Insert Update Delete /// </summary> /// <param name="sql">slq语句</param> /// <param name="cmdType">sql命令类型,1=sql语句,2=存储过程</param> /// <param name="paras">sql参数</param> /// <returns></returns> public static int ExecuteNonQuery(string sql, int cmdType, params SqlParameter[] paras) { int count = 0; using (SqlConnection conn = new SqlConnection(connStr)) { SqlCommand cmd = new SqlCommand(sql, conn); if (cmdType == 2) cmd.CommandType = CommandType.StoredProcedure; if (paras != null && paras.Length > 0) cmd.Parameters.AddRange(paras); conn.Open(); count = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); conn.Close(); } return count; } /// <summary> /// 执行查询,返回结果集第一行第一列的值,忽略其他行或列 object /// </summary> /// <param name="sql"></param> /// <param name="cmdType"></param> /// <param name="paras"></param> /// <returns></returns> public static object ExecuteScalar(string sql, int cmdType, params SqlParameter[] paras) { object result = null; using (SqlConnection conn = new SqlConnection(connStr)) { SqlCommand cmd = new SqlCommand(sql, conn); if (cmdType == 2) cmd.CommandType = CommandType.StoredProcedure; if (paras != null && paras.Length > 0) cmd.Parameters.AddRange(paras); conn.Open(); result = cmd.ExecuteScalar(); cmd.Parameters.Clear(); conn.Close(); } return result; } /// <summary> /// 执行查询,生成SqlDataReader /// </summary> /// <param name="sql"></param> /// <param name="cmdType"></param> /// <param name="paras"></param> /// <returns></returns> /// <exception cref="Exception"></exception> public static SqlDataReader ExecuteReader(string sql, int cmdType, params SqlParameter[] paras) { SqlDataReader result = null; SqlConnection conn = new SqlConnection(connStr); SqlCommand cmd = new SqlCommand(sql, conn); if (cmdType == 2) cmd.CommandType = CommandType.StoredProcedure; if (paras != null && paras.Length > 0) cmd.Parameters.AddRange(paras); try { conn.Open(); result = cmd.ExecuteReader(CommandBehavior.CloseConnection); cmd.Parameters.Clear(); } catch (SqlException ex) { conn.Close(); throw new Exception("查询异常", ex); } return result; } /// <summary> /// 填充DataSet 一个或多个结果集 /// </summary> /// <param name="sql"></param> /// <param name="cmdType"></param> /// <param name="paras"></param> /// <returns></returns> public static DataSet GetDataSet(string sql, int cmdType, params SqlParameter[] paras) { DataSet dt = new DataSet(); using (SqlConnection conn = new SqlConnection(connStr)) { SqlCommand cmd = new SqlCommand(sql, conn); if(cmdType == 2) cmd.CommandType = CommandType.StoredProcedure; if(paras != null && paras.Length > 0) cmd.Parameters.AddRange(paras); SqlDataAdapter adapter = new SqlDataAdapter(cmd); conn.Open(); adapter.Fill(dt); cmd.Parameters.Clear(); conn.Close(); } return dt; } public static DataTable GetDataTable(string sql ,int cmdType,params SqlParameter[] paras) { DataTable dt = new DataTable(); using (SqlConnection conn = new SqlConnection(connStr)) { SqlCommand cmd = new SqlCommand (sql, conn); if (cmdType == 2) cmd.CommandType = CommandType.StoredProcedure; if (paras != null && paras.Length > 0) cmd.Parameters.AddRange(paras); conn.Open(); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(dt); cmd.Parameters.Clear(); conn.Close(); } return dt; } } }