简单的SqlHelper
时间：2021-07-01 10:21:17
帮助过：5人阅读
using System;
2 using System.Collections.Generic;
3 using System.Configuration;
4 using System.Data;
5 using System.Data.SqlClient;
6 using System.Linq;
7 using System.Text;
8 using System.Threading.Tasks;
9
10 namespace DAL
11 {
12 public static class SqlHelper
13 {
14 //定义一个连接字符串
15 //readonly修饰的变量，只能在初始化的时候复制，以及在构造函数中赋值，其他地方只能读取不能设置值
16 private static readonly string conStr = ConfigurationManager.ConnectionStrings[
""].ConnectionString;
17 /// <summary>
18 /// ExecuteNonQuery
19 /// </summary>
20 /// <param name="sql">要执行的语句/param>
21 /// <param name="type">指定类型（存储过程还是语句）</param>
22 /// <param name="pms">参数</param>
23 /// <returns></returns>
24 public static int ExecuteNonQuery(
string sql, CommandType type,
params SqlParameter[] pms)
25 {
26 using (SqlConnection con =
new SqlConnection(conStr))
27 {
28 using (SqlCommand cmd =
new SqlCommand(sql, con))
29 {
30 //判断传入的是sql语句还是存储过程
31 cmd.CommandType =
type;
32 if (pms !=
null)
33 {
34 cmd.Parameters.AddRange(pms);
35 }
36 con.Open();
37 return cmd.ExecuteNonQuery();
38 }
39 }
40 }
41 /// <summary>
42 /// 返回单个值
43 /// </summary>
44 /// <param name="sql"></param>
45 /// <param name="type"></param>
46 /// <param name="pms"></param>
47 /// <returns></returns>
48 public static object ExecuteScalar(
string sql, CommandType type,
params SqlParameter[] pms)
49 {
50 using (SqlConnection con =
new SqlConnection(conStr))
51 {
52 using (SqlCommand cmd =
new SqlCommand(sql, con))
53 {
54 cmd.CommandType =
type;
55 if (pms !=
null)
56 {
57 cmd.Parameters.AddRange(pms);
58 }
59 con.Open();
60 return cmd.ExecuteScalar();
61 }
62 }
63 }
64 public static SqlDataReader ExecuteReader(
string sql, CommandType type,
params SqlParameter[] pms)
65 {
66 //这里不使用using是因为reader对象不能关闭连接。reader对象在使用时，必须保证连接是打开的。
67 SqlConnection con =
new SqlConnection(conStr);
68 using (SqlCommand cmd =
new SqlCommand(sql, con))
69 {
70 cmd.CommandType =
type;
71 if (pms !=
null)
72 {
73 cmd.Parameters.AddRange(pms);
74 }
75 try
76 {
77 con.Open();
78 //使用CommandBehavior.CloseConnection，表示将来使用完毕sqlDatareader后，在关闭reader的同时，关闭关联的Connection对象。
79 return cmd.ExecuteReader(CommandBehavior.CloseConnection);
80 }
81 //异常执行
82 catch (Exception)
83 {
84 con.Close();
85 con.Dispose();
86 throw;
87 }
88 }
89 }
90 public static DataTable ExcuteDataTable(
string sql, CommandType type,
params SqlParameter[] pms)
91 {
92 DataTable dt =
new DataTable();
93 using (SqlDataAdapter adapter =
new SqlDataAdapter(sql, conStr))
94 {
95 adapter.SelectCommand.CommandType =
type;
96 if (pms!=
null)
97 {
98 adapter.SelectCommand.Parameters.AddRange(pms);
99 }
100 adapter.Fill(dt);
101 }
102 return dt;
103 }
104 }
105 }
简单的SqlHelper
标签：ext param 资源 color fill names using [] 崩溃
