1 using System;
2 using System.Collections.Generic;
3 using System.Linq;
4 using System.Web;
5 using System.Data.SqlClient;
6 using System.Data;
7
8 /// <summary>
9 ///DBHelper 的摘要说明
10 /// </summary>
11 public class DBHelper
12 {
13 private static DBHelper _instance;
14 protected DBHelper()
15 {
16 //
17 //TODO: 在此处添加构造函数逻辑
18 //
19 }
20 public static DBHelper getInstance()
21 {
22 if (_instance == null)
23 {
24 _instance = new DBHelper();
25 }
26 return _instance;
27 }
28 //返回一个连接
29 public static SqlConnection ReturnConn()
30 {
31 string strConn = System.Configuration.ConfigurationManager.AppSettings["ConnectionString"].ToString();
32 SqlConnection Conn = new SqlConnection(strConn);
33 if (Conn.State.Equals(ConnectionState.Closed))
34 {
35 Conn.Open();
36 }
37 return Conn;
38 }
39 //带参数的CreateCmd命令
40 public static SqlCommand CreatCmd(string procName, SqlParameter[] prams)
41 {
42 SqlConnection Conn = ReturnConn();
43 SqlCommand Cmd = new SqlCommand();
44 Cmd.CommandType = CommandType.StoredProcedure;
45 Cmd.Connection = Conn;
46 Cmd.CommandText = procName;
47 if (prams != null)
48 {
49 foreach (SqlParameter parameter in prams)
50 {
51 if (parameter != null)
52 {
53 Cmd.Parameters.Add(parameter);
54 }
55 }
56 }
57 return Cmd;
58 }
59 //重载带参数带连接的CreateCmd命令
60 public static SqlCommand CreatCmd(string procName, SqlParameter[] prams, SqlConnection Conn)
61 {
62 SqlConnection SqlConn = Conn;
63 if (SqlConn.State.Equals(ConnectionState.Closed))
64 {
65 SqlConn.Open();
66 }
67 SqlCommand Cmd = new SqlCommand();
68 Cmd.CommandType = CommandType.StoredProcedure;
69 Cmd.Connection = SqlConn;
70 Cmd.CommandText = procName;
71 if (prams != null)
72 {
73 foreach (SqlParameter parameter in prams)
74 {
75 if (parameter != null)
76 {
77 Cmd.Parameters.Add(parameter);
78 }
79 }
80 }
81 return Cmd;
82 }
83 //重载不带参数带连接的CreateCmd命令
84 public static SqlCommand CreatCmd(string procName, SqlConnection Conn)
85 {
86 SqlConnection SqlConn = Conn;
87 if (SqlConn.State.Equals(ConnectionState.Closed))
88 {
89 SqlConn.Open();
90 }
91 SqlCommand Cmd = new SqlCommand();
92 Cmd.CommandType = CommandType.StoredProcedure;
93 Cmd.Connection = SqlConn;
94 Cmd.CommandText = procName;
95 return Cmd;
96 }
97 //重载不带参数不带连接的CreateCmd命令
98 public static SqlCommand CreatCmd(string procName)
99 {
100 SqlConnection SqlConn = ReturnConn();
101 SqlCommand Cmd = new SqlCommand();
102 Cmd.CommandType = CommandType.StoredProcedure;
103 Cmd.Connection = SqlConn;
104 Cmd.CommandText = procName;
105 return Cmd;
106 }
107 //带参数的RunProcGetReader
108 public static SqlDataReader RunProcGetReader(string procName, SqlParameter[] prams)
109 {
110 SqlCommand Cmd = CreatCmd(procName, prams);
111 SqlDataReader Dr = Cmd.ExecuteReader(CommandBehavior.CloseConnection);
112 return Dr;
113 }
114 //重载带参数和连接的RunProcGetReader
115 public static SqlDataReader RunProcGetReader(string procName, SqlParameter[] prams, SqlConnection Conn)
116 {
117 SqlCommand Cmd = CreatCmd(procName, prams, Conn);
118 SqlDataReader Dr = Cmd.ExecuteReader(CommandBehavior.CloseConnection);
119 return Dr;
120 }
121 //重载带连接的RunProcGetReader
122 public static SqlDataReader RunProcGetReader(string procName, SqlConnection Conn)
123 {
124 SqlCommand Cmd = CreatCmd(procName, Conn);
125 SqlDataReader Dr = Cmd.ExecuteReader(CommandBehavior.CloseConnection);
126 return Dr;
127 }
128 //重载不带参数和连接的RunProcGetReader
129 public static SqlDataReader RunProcGetReader(string procName)
130 {
131 SqlCommand Cmd = CreatCmd(procName);
132 SqlDataReader Dr = Cmd.ExecuteReader(CommandBehavior.CloseConnection);
133 return Dr;
134 }
135 //返回执行命令后的表
136 public static DataTable RunProcGetTable(string procName, SqlParameter[] prams, SqlConnection Conn)
137 {
138 SqlCommand Cmd = CreatCmd(procName, prams, Conn);
139 SqlDataAdapter Dtr = new SqlDataAdapter();
140 DataSet Ds = new DataSet();
141 Dtr.SelectCommand = Cmd;
142 Dtr.Fill(Ds);
143 DataTable Dt = Ds.Tables[0];
144 Conn.Close();
145 return Dt;
146
147 }
148 //重载返回执行命令后的表
149 public static DataTable RunProcGetTable(string procName, SqlParameter[] prams)
150 {
151 SqlConnection Conn = ReturnConn();
152 SqlCommand Cmd = CreatCmd(procName, prams, Conn);
153 SqlDataAdapter Dtr = new SqlDataAdapter();
154 DataSet Ds = new DataSet();
155 Dtr.SelectCommand = Cmd;
156 Dtr.Fill(Ds);
157 DataTable Dt = Ds.Tables[0];
158 Conn.Close();
159 return Dt;
160
161 }
162 //重载返回执行命令后的表
163 public static DataTable RunProcGetTable(string procName)
164 {
165 SqlConnection Conn = ReturnConn();
166 SqlCommand Cmd = CreatCmd(procName, Conn);
167 SqlDataAdapter Dtr = new SqlDataAdapter();
168 DataSet Ds = new DataSet();
169 Dtr.SelectCommand = Cmd;
170 Dtr.Fill(Ds);
171 DataTable Dt = Ds.Tables[0];
172 Conn.Close();
173 return Dt;
174
175 }
176 //返回执行SQL语句后受影响的行数
177 public static int RunExecute(string procName)
178 {
179 SqlConnection Conn = ReturnConn();
180 SqlCommand Cmd = CreatCmd(procName, Conn);
181 int intResult = Cmd.ExecuteNonQuery();
182 Conn.Close();
183 return intResult;
184
185 }
186 //返回执行SQL语句后受影响的行数，带参数重载
187 public static int RunExecute(string procName, SqlParameter[] prams)
188 {
189 SqlConnection Conn = ReturnConn();
190 SqlCommand Cmd = CreatCmd(procName, prams, Conn);
191 int intResult = Cmd.ExecuteNonQuery();
192 Conn.Close();
193 return intResult;
194 }
195 //执行并返回第一行第一列
196 public static int RunExecuteScalar(string procName)
197 {
198 SqlConnection Conn = ReturnConn();
199 SqlCommand Cmd = CreatCmd(procName, Conn);
200 int intResult = Convert.ToInt32(Cmd.ExecuteScalar());
201 Conn.Close();
202 return intResult;
203 }
204 //执行并返回第一行第一列,带参数重载
205 public static int RunExecuteScalar(string procName, SqlParameter[] prams)
206 {
207 SqlConnection Conn = ReturnConn();
208 SqlCommand Cmd = CreatCmd(procName, prams, Conn);
209 int intResult = Convert.ToInt32(Cmd.ExecuteScalar());
210 Conn.Close();
211 return intResult;
212 }
213 }