1 using System.Collections.Generic;
2 using System.Linq;
3 using System.Web;
4 using System.Data;
5 using System.Data.SqlClient;
6
7 namespace TestWeb.DLL
8 {
9 public class DataAccess
10 {
11 private string connectionString = "";
12
13 public string ConnectionString
14 {
15 get
16 {
17 return connectionString;
18 }
19 set
20 {
21 connectionString = value;
22 }
23 }
24
25 protected string BuildSqlStr()
26 {
27 return ConfiguretionSettings.AppSettings["slja"].ToString();
28 }
29
30 protected SqlConnection BuildSqlConnection()
31 {
32 if (connectionString == "")
33 {
34 connectionString = BuildSqlStr();
35 }
36 SqlConnection conn = new SqlConnection(connectionString);
37 conn.Open();
38 return conn;
39 }
40
41 protected Sqlconnection BuildSqlConnection(connectionString)
42 {
43 SqlConnection conn = new SqlConnection(connectionString);
44 conn.Open();
45 return conn;
46 }
47
48 protected SqlCommand BuildSqlCommand()
49 {
50 SqlCommand Comobj = new SqlCommand();
51 Comobj.Connection = BuildSqlConnection();
52 return Comobj;
53 }
54 protected SqlCommand BuildSqlCommand(string cmdText)
55 {
56 return new SqlCommand(cmdText, BuildSqlConnection());
57 }
58
59 protected DataSet BuildSqlDataSet(string cmdText)
60 {
61 SqlCommand ObjectCommand = BuildSqlCommand(cmdText);
62 SqlDataAdapter ObjectAdapter = new SqlDataAdapter(ObjectCommand);
63 DataSet Ds = new DataSet();
64 ObjectAdapter.Fill(Ds);
65 ObjectCommand.Connection.Close();
66 ObjectCommand.Connection.Dispose();
67 ObjectCommand.Dispose();
68 return Ds;
69 }
70
71 protected DataReader BuildSqlDataReader(string cmdText)
72 {
73 SqlCommand ObjectCommand = BuildSqlCommand(cmdText);
74 SqlDataReader ObjectDataReader = ObjectCommand.ExecuteReader();
75 ObjectCommand.Connection.Close();
76 ObjectCommand.Connection.Dispose();
77 ObjectCommand.Dispose();
78 return ObjectDataReader;
79 }
80
81 protected int BuildSqlExecuteNonQuery(string cmdText)
82 {
83 SqlCommand ObjectCommand = BuildSqlCommand(cmdText);
84 int cellout = ObjectCommand.ExecuteNonQuery();
85 ObjectCommand.Connection.Close();
86 ObjectCommand.Connection.Dispose();
87 ObjectCommand.Dispose();
88 return cellout;
89 }
90
91 protected string BuildSqlExecuteScalar(string cmdText)
92 {
93 SqlCommand ObjectCommand = BuildSqlCommand(cmdText);
94 string returnStr = string.Empty;
95 if (ObjectCommand.ExecuteScalar() != null)
96 {
97 returnStr = ObjectCommand.ExecuteScalar().Tostring();
98 }
99 ObjectCommand.Connection.Close();
100 ObjectCommand.Connection.Dispose();
101 ObjectCommand.Dispose();
102 return returnStr;
103 }
104
105 //sisi
106 //four and four----------------------------------------------------------------------------------
107
108 protected int RunSqlParametersExecuteNonQuery(string Sqlstr, SqlParameter[] parameters)
109 {
110 SqlCommand ObjectCommand = BuildSqlCommand();
111 ObjectCommand.CommandType = CommandType.Text;
112 ObjectCommand.CommandText = Sqlstr;
113 int count = 0;
114 foreach (SqlParameter parameter in parameters)
115 {
116 ObjectCommand.Parameters.Add(parameter);
117 }
118 count = ObjectCommand.ExecuteNonQuery();
119 ObjectCommand.Connection.Close();
120 ObjectCommand.Connection.Dispose();
121 ObjectCommand.Dispose();
122 return count;
123 }
124
125 protected int RunSqlParametersExecuteNonQuery(string Sqlstr, SqlParameter parameters)
126 {
127 SqlCommand ObjectCommand = BuildSqlCommand();
128 ObjectCommand.CommandType = CommandType.Text;
129 ObjectCommand.CommandText = Sqlstr;
130 int count = 0;
131 ObjectCommand.Parameters.Add(parameters);
132 count = ObjectCommand.ExecuteNonQuery();
133 ObjectCommand.Connection.Close();
134 ObjectCommand.Connection.Dispose();
135 ObjectCommand.Dispose();
136 return count;
137 }
138
139 protected int RunSqlParametersExecuteNonQuery(DbCommand sqlCommand, CommandType commtype, string Sqlstr)
140 {
141 sqlCommand.Connection = this.BuildSqlConnection();
142 sqlCommand.CommandType = commtype;
143 sqlCommand.ComandText = Sqlstr;
144 int count = sqlCommand.ExecuteNonQuery();
145 sqlCommand.Connection.Close();
146 sqlCommand.Connection.Dispose();
147 sqlCommand.Dispose();
148 return count;
149 }
150
151 protected int RunSqlParametersExecuteNonQuery(DbCommand sqlCommand)
152 {
153 sqlCommand.Connection = this.BuildSqlConnection();
154 int count = sqlCommand.ExecuteNonQuery();
155 sqlCommand.Connection.Close();
156 sqlCommand.Connection.Dispose();
157 sqlCommand.Dispose();
158 return count;
159 }
160
161 //----------------------------------------------------------------------------------------------------
162
163 protected string RunSqlParametersExecuteScalar(string Sqlstr, SqlParameter[] parameters)
164 {
165 SqlCommand ObjectCommand = BuildSqlCommand();
166 ObjectCommand.CommandType = CommandType.Text;
167 ObjectCommand.CommandText = Sqlstr;
168 string returnStr = string.Empty;
169 foreach (SqlParameter parameter in parameters)
170 {
171 ObjectCommand.Parameters.Add(parameter);
172 }
173 returnStr = ObjectCommand.ExecuteScalar();
174 ObjectCommand.Connection.Close();
175 ObjectCommand.Connection.Dispose();
176 ObjectCommand.Dispose();
177 return returnStr;
178 }
179
180 protected string RunSqlParametersExecuteScalar(string Sqlstr, SqlParameter parameters)
181 {
182 SqlCommand ObjectCommand = BuildSqlCommand();
183 ObjectCommand.CommandType = CommandType.Text;
184 ObjectCommand.CommandText = Sqlstr;
185 string returnStr = string.Empty;
186 ObjectCommand.Parameters.Add(parameters);
187 returnStr = ObjectCommand.ExecuteScalar();
188 ObjectCommand.Connection.Close();
189 ObjectCommand.Connection.Dispose();
190 ObjectCommand.Dispose();
191 return returnStr;
192 }
193
194 protected string RunSqlParametersExecuteScalar(DbCommand sqlCommand, CommandType commtype, string Sqlstr)
195 {
196 sqlCommand.Connection = this.BuildSqlConnection();
197 sqlCommand.CommandType = commtype;
198 sqlCommand.ComandText = Sqlstr;
199 string returnStr = sqlCommand.ExecuteScalar();
200 sqlCommand.Connection.Close();
201 sqlCommand.Connection.Dispose();
202 sqlCommand.Dispose();
203 return returnStr;
204 }
205
206 protected string RunSqlParametersExecuteScalar(DbCommand sqlCommand)
207 {
208 sqlCommand.Connection = this.BuildSqlConnection();
209 string returnStr = sqlCommand.ExecuteScalar();
210 sqlCommand.Connection.Close();
211 sqlCommand.Connection.Dispose();
212 sqlCommand.Dispose();
213 return returnStr;
214 }
215
216 //---------------------------------------------------------------------------------------------------
217
218 protected DataSet RunSqlParametersReturnDataSet(string Sqlstr, SqlParameter[] parameters)
219 {
220 SqlCommand ObjectCommand = BuildSqlCommand();
221 ObjectCommand.CommandType = CommandType.Text;
222 ObjectCommand.CommandText = Sqlstr;
223 foreach (SqlParameter parameter in parameters)
224 {
225 ObjectCommand.Parameters.Add(parameter);
226 }
227 SqlDataAdapter ObjectAdapter = new SqlDataAdapter(ObjectCommand);
228 DataSet Ds = new DataSet();
229 ObjectAdapter.Fill(Ds,"DataList");
230 ObjectCommand.Connection.Close();
231 ObjectCommand.Connection.Dispose();
232 ObjectCommand.Dispose();
233 return Ds;
234 }
235
236 protected DataSet RunSqlParametersReturnDataSet(string Sqlstr, SqlParameter parameters)
237 {
238 SqlCommand ObjectCommand = BuildSqlCommand();
239 ObjectCommand.CommandType = CommandType.Text;
240 ObjectCommand.CommandText = Sqlstr;
241 foreach (SqlParameter parameter in parameters)
242 ObjectCommand.Parameters.Add(parameter);
243 SqlDataAdapter ObjectAdapter = new SqlDataAdapter(ObjectCommand);
244 DataSet Ds = new DataSet();
245 ObjectAdapter.Fill(Ds,"DataList");
246 ObjectCommand.Connection.Close();
247 ObjectCommand.Connection.Dispose();
248 ObjectCommand.Dispose();
249 return Ds;
250 }
251
252 protected DataSet RunSqlParametersReturnDataSet(DbCommand sqlCommand, CommandType commtype, string Sqlstr)
253 {
254 sqlCommand.Connection = this.BuildSqlConnection();
255 sqlCommand.CommandType = commtype;
256 sqlCommand.ComandText = Sqlstr;
257 SqlDataAdapter ObjectAdapter = new SqlDataAdapter(sqlCommand);
258 DataSet Ds = new DataSet();
259 ObjectAdapter.Fill(Ds,"DataList");
260 sqlCommand.Connection.Close();
261 sqlCommand.Connection.Dispose();
262 sqlCommand.Dispose();
263 return Ds;
264 }
265
266 protected DataSet RunSqlParametersReturnDataSet(DbCommand sqlCommand)
267 {
268 sqlCommand.Connection = this.BuildSqlConnection();
269 SqlDataAdapter ObjectAdapter = new SqlDataAdapter(sqlCommand);
270 DataSet Ds = new DataSet();
271 ObjectAdapter.Fill(Ds,"DataList");
272 sqlCommand.Connection.Close();
273 sqlCommand.Connection.Dispose();
274 sqlCommand.Dispose();
275 return Ds;
276 }
277
278 //-------------------------------------------------------------------------------------------
279
280 protected SqlDataReader RunSqlParametersRuturnDataReader(string Sqlstr, SqlParameter[] parameters)
281 {
282 SqlCommand ObjectCommand = BuildSqlCommand();
283 ObjectCommand.CommandType = CommandType.Text;
284 ObjectCommand.CommandText = Sqlstr;
285 foreach (SqlParameter parameter in parameters)
286 {
287 ObjectCommand.Parameters.Add(parameter);
288 }
289 SqlDataReader sdr = ObjectCommand.ExecuteReader();
290 ObjectCommand.Connection.Close();
291 ObjectCommand.Connection.Dispose();
292 ObjectCommand.Dispose();
293 return sdr;
294 }
295
296 protected SqlDataReader RunSqlParametersRuturnDataReader(string Sqlstr, SqlParameter parameters)
297 {
298 SqlCommand ObjectCommand = BuildSqlCommand();
299 ObjectCommand.CommandType = CommandType.Text;
300 ObjectCommand.CommandText = Sqlstr;
301 foreach (SqlParameter parameter in parameters)
302 ObjectCommand.Parameters.Add(parameter);
303 SqlDataReader sdr = ObjectCommand.ExecuteReader();
304 ObjectCommand.Connection.Close();
305 ObjectCommand.Connection.Dispose();
306 ObjectCommand.Dispose();
307 return sdr;
308 }
309
310 protected SqlDataReader RunSqlParametersRuturnDataReader(DbCommand sqlCommand, CommandType commtype, string Sqlstr)
311 {
312 sqlCommand.Connection = this.BuildSqlConnection();
313 sqlCommand.CommandType = commtype;
314 sqlCommand.ComandText = Sqlstr;
315 SqlDataReader sdr = ObjectCommand.ExecuteReader();
316 sqlCommand.Connection.Close();
317 sqlCommand.Connection.Dispose();
318 sqlCommand.Dispose();
319 return sdr;
320 }
321
322 protected SqlDataReader RunSqlParametersRuturnDataReader(DbCommand sqlCommand)
323 {
324 sqlCommand.Connection = this.BuildSqlConnection();
325 SqlDataReader sdr = ObjectCommand.ExecuteReader();
326 sqlCommand.Connection.Close();
327 sqlCommand.Connection.Dispose();
328 sqlCommand.Dispose();
329 return sdr;
330 }
331 }
332 }
