SQLServer 数据库封装类
2024-09-01 10:05:05
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.SqlClient; using System.Data; using System.Configuration; namespace SQLHelper { public static class SqlHelper { //从配置文件中获取连接字符串 readonly修饰的变量只能在初始化或构造函数中赋值;其他地方只能读取 //private static readonly string constr = ConfigurationManager.ConnectionStrings["OperationHistory"].ConnectionString; private static readonly string constr = "Data Source=/*主机名或者目标主机IP地址*/;Initial Catalog=/*数据库名*/;User=sa;PassWord=123"; /// <summary> /// 增、删、改的方法[ExecuteNonQuery] 返回所影响的行数,执行其他 /// </summary> /// <param name="sql">Sql命令</param> /// <param name="cmdtype">SQL语句(CommandType.Text)或者存储过程(CommandType.StoredProcedure)</param> /// <param name="pms"></param> /// <returns></returns> public static int ExecuteNonQuery(string sql, CommandType cmdtype, params SqlParameter[] pms) { ; try { using (SqlConnection con = new SqlConnection(constr)) { using (SqlCommand cmd = new SqlCommand(sql, con)) { //存储过程或者Sql语句 cmd.CommandType = cmdtype; if (pms != null) { cmd.Parameters.AddRange(pms); } con.Open(); i = cmd.ExecuteNonQuery(); } } } catch (Exception) { throw; } return i; } /// <summary> /// 执行查询、查询多少条数据;返回第一行,第一列方法[ExecuteScalar] 返回-1执行失败 /// </summary> /// <param name="sql">Sql命令</param> /// <param name="cmdtype">SQL语句(CommandType.Text)或者存储过程(CommandType.StoredProcedure)</param> /// <param name="pms"></param> /// <returns></returns> public static object ExecuteScalar(string sql, CommandType cmdtype, params SqlParameter[] pms) { try { using (SqlConnection con = new SqlConnection(constr)) { using (SqlCommand cmd = new SqlCommand(sql, con)) { //存储过程或者Sql语句 cmd.CommandType = cmdtype; if (pms != null) { cmd.Parameters.AddRange(pms); } con.Open(); return cmd.ExecuteScalar(); } } } catch (Exception) { throw; } } /// <summary> /// 执行查询,返回多行、多列的方法ExecuteReader() /// </summary> /// <param name="sql">Sql命令</param> /// <param name="cmdtype">SQL语句(CommandType.Text)或者存储过程(CommandType.StoredProcedure)</param> /// <param name="pms"></param> /// <returns></returns> public static SqlDataReader ExecuteReader(string sql, CommandType cmdtype, params SqlParameter[] pms) { SqlConnection con = new SqlConnection(constr); using (SqlCommand cmd = new SqlCommand(sql, con)) { //存储过程或者Sql语句 cmd.CommandType = cmdtype; if (pms != null) { cmd.Parameters.AddRange(pms); } try { con.Open(); return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection); } catch { con.Close(); con.Dispose(); throw; } } } /// <summary> /// 执行返回多个查询时使用,返回DataTable类型 /// </summary> /// <param name="sql">Sql命令</param> /// <param name="cmdtype">SQL语句(CommandType.Text)或者存储过程(CommandType.StoredProcedure)</param> /// <param name="pms">参数</param> /// <returns></returns> public static DataTable ExecuteDataTable(string sql, CommandType cmdtype, params SqlParameter[] pms) { DataTable dt = new DataTable(); try { //通过adapter读取数据。 using (SqlDataAdapter adapter = new SqlDataAdapter(sql, constr)) { adapter.SelectCommand.CommandType = cmdtype; if (pms != null) { adapter.SelectCommand.Parameters.AddRange(pms); } adapter.Fill(dt); return dt; } } catch (Exception) { throw; } } /// <summary> /// 该方法是一个数据集方法在很多地方用得到的 /// </summary> /// <param name="sql">第一个参数是传sql语句</param> /// <param name="table">第二个参数是传数据库当中的表名</param> /// <returns></returns> public DataSet GetDataSet(string sql, CommandType cmdtype, string table) { try { using (SqlDataAdapter adapter = new SqlDataAdapter(sql, constr)) { adapter.SelectCommand.CommandType = cmdtype; DataSet ds = new DataSet(); adapter.Fill(ds, table); return ds; } } catch (Exception) { throw; } } } }
最新文章
- 搭建selenium自动化环境步骤
- xss跨站实例总结
- HDU1717小数化分数2
- Java 基础知识点(必知必会其二)
- 包装FTPWebRequest类
- CSS3学习笔记-1:CSS样式继承
- iOS-Mac Charles抓包工具的使用【Mac 抓包工具Charles】
- 团队作业4——第一次项目冲刺(Alpha版本)11.18
- windows 下安装MySQL 服务无法启动类问题
- 一个友盟BUG的思考和分析:Invalid update
- ZOJ 3963 Heap Partition(multiset + stl自带二分 + 贪心)题解
- 42.纯 CSS 创作一个均衡器 loader 动画
- ES6标准入门之数值的拓展解说
- Android项目开发四
- js openwindow
- string 线程安全
- JavaScript Number() Vs new Number()
- Could not contact Selenium Server; have you started it on &#39;localhost:4444&#39;
- python碎片记录(二)
- ExtJS 4.2.1学习笔记(一)——MVC架构与布局