1 using System;
2 using System.Collections.Generic;
3 using System.Linq;
4 using System.Text;
5 using System.Configuration;
6 using System.Data.SqlClient;
7 using System.Data;
8
9
10 namespace TestPrj //空间名字 TestPrj
11
12 {
13 public class DBHelper //类名 DBHelper
14 {
15
16
17 private static string connStr = ConfigurationManager.ConnectionStrings["sqlserver"].ConnectionString;
18
19 private static SqlConnection conn;
20
21 public static SqlConnection Conn
22 {
23 get
24 {
25 if (conn == null)
26 {
27 conn = new SqlConnection(connStr);
28 conn.Open();
29 }
30 if (conn != null && conn.State != ConnectionState.Closed)
31 {
32 conn.Close();
33 conn.Open();
34 }
35 else
36 {
37 conn.Open();
38 }
39 return DBHelper.conn;
40 }
41 }
42
43
44 /// <summary>
45 /// 关闭连接
46 /// </summary>
47 public static void closeConn()
48 {
49 if (Conn != null && Conn.State != ConnectionState.Closed)
50 {
51 Conn.Close();
52 }
53 }
54
55
56 /// <summary>
57 /// 查询操作
58 /// </summary>
59 /// <param name="sql"></param>
60 /// <param name="type"></param>
61 /// <param name="pars"></param>
62 /// <returns></returns>
63 public static SqlDataReader getReader(string sql, CommandType type, params SqlParameter[] pars)
64 {
65 SqlCommand cmd = new SqlCommand(sql, Conn);
66 cmd.CommandType = type;
67 cmd.Parameters.AddRange(pars);
68 return cmd.ExecuteReader();
69 }
70
71
72 /// <summary>
73 /// 执行非查询操作(执行增、删、改操作的)，不支持事务
74 /// </summary>
75 /// <param name="sql"></param>
76 /// <param name="type"></param>
77 /// <param name="pars"></param>
78 /// <returns></returns>
79 public static int ExecuteNoneQuery(string sql, CommandType type, params SqlParameter[] pars)
80 {
81 int result = 0;
82
83 SqlCommand cmd = new SqlCommand(sql, Conn);
84 cmd.CommandType = type;
85 if (pars != null)
86 {
87 cmd.Parameters.AddRange(pars);
88 }
89
90 result = cmd.ExecuteNonQuery();
91
92 //关闭连接对象
93 CloseConn();
94
95 return result;
96 }
97
98 /// <summary>
99 /// 非查询操作(执行增、删、改操作的)，支持事务，进行批量操作
100 /// </summary>
101 /// <param name="sql"></param>
102 /// <param name="conn"></param>
103 /// <param name="type"></param>
104 /// <param name="pars"></param>
105 /// <returns></returns>
106 public static int ExecuteNoneQuery(string sql, SqlConnection conn, CommandType type, params SqlParameter[] pars)
107 {
108 int result = 0;
109
110 //接受外部提供的数据连接对象，并且此连接在本次操作后不要关闭
111 SqlCommand cmd = new SqlCommand(sql, conn);
112 cmd.CommandType = type;
113 cmd.Parameters.AddRange(pars);
114
115 result = cmd.ExecuteNonQuery();
116
117 return result;
118 }
119
120 /// <summary>
121 /// 获取单个值，首行首列
122 /// </summary>
123 /// <param name="sql"></param>
124 /// <param name="type"></param>
125 /// <param name="pars"></param>
126 /// <returns></returns>
127 public static object GetScalar(string sql, CommandType type, params SqlParameter[] pars)
128 {
129 object obj = null;
130
131 SqlCommand cmd = new SqlCommand(sql, Conn);
132 cmd.CommandType = type;
133 cmd.Parameters.AddRange(pars);
134
135 obj = cmd.ExecuteScalar();
136
137 //关闭连接对象
138 CloseConn();
139
140 return obj;
141 }
142 }
143 }
