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 = 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); 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 = 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(); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(dt); cmd.Parameters.Clear(); conn.Close(); } return dt; } } }
原文地址:http://www.cnblogs.com/hanzq/p/16784114.html
1. 本站所有资源来源于用户上传和网络,如有侵权请邮件联系站长!
2. 分享目的仅供大家学习和交流,请务用于商业用途!
3. 如果你也有好源码或者教程,可以到用户中心发布,分享有积分奖励和额外收入!
4. 本站提供的源码、模板、插件等等其他资源,都不包含技术服务请大家谅解!
5. 如有链接无法下载、失效或广告,请联系管理员处理!
6. 本站资源售价只是赞助,收取费用仅维持本站的日常运营所需!
7. 如遇到加密压缩包,默认解压密码为"gltf",如遇到无法解压的请联系管理员!
8. 因为资源和程序源码均为可复制品,所以不支持任何理由的退款兑现,请斟酌后支付下载
声明:如果标题没有注明"已测试"或者"测试可用"等字样的资源源码均未经过站长测试.特别注意没有标注的源码不保证任何可用性