1 using System;
2 using System.Collections;
3 using System.Collections.Generic;
4 using System.Linq;
5 using System.Text;
6 using System.Data;
7 using System.Data.SqlClient;
8
9 namespace Toolkit
10 {
11 public class DataAccess
12 {
13 private string connectionString = null;
14 private SqlConnection connection = null;
15 private bool isAutoClose = true;
16
17 private Dictionary<string, SqlCommand> queryCommand = new Dictionary<string, SqlCommand>();
18 private Dictionary<string, SqlDataReader> queryDataReader = new Dictionary<string, SqlDataReader>();
19 private Dictionary<string, int[]> queryResultRows = new Dictionary<string, int[]>();
20 private Dictionary<string, string[,]> queryResultFields = new Dictionary<string, string[,]>();
21 private Dictionary<string, List<object[]>> queryResultValues = new Dictionary<string, List<object[]>>();
22
23 private string errorMessage;
24
25 public string ErrorMessage
26 {
27 get { return errorMessage; }
28 }
29
30 public DataAccess(string connectionString)
31 {
32 this.connectionString = connectionString;
33 }
34
35 public bool IsOpen()
36 {
37 if (connection != null)
38 {
39 return connection.State == ConnectionState.Open;
40 }
41 return false;
42 }
43
44 public bool Open()
45 {
46 isAutoClose = false;
47 return DbOpen();
48 }
49
50 private bool DbOpen()
51 {
52 try
53 {
54 connection = new SqlConnection(connectionString);
55 connection.Open();
56 return true;
57 }
58 catch (SqlException e)
59 {
60 errorMessage = e.Message;
61 }
62 return false;
63 }
64
65 public bool Close()
66 {
67 isAutoClose = true;
68 return DbClose();
69 }
70
71 private bool DbClose()
72 {
73 try
74 {
75 if (connection != null)
76 {
77 connection.Close();
78 connection = null;
79 }
80 return true;
81 }
82 catch (SqlException e)
83 {
84 errorMessage = e.Message;
85 }
86 return false;
87 }
88
89 public int ExecuteUpdate(string sql)
90 {
91 if (!IsOpen())
92 DbOpen();
93 if (IsOpen())
94 {
95 try
96 {
97 SqlCommand command = new SqlCommand(sql, connection);
98 int returnValue = command.ExecuteNonQuery();
99 if (isAutoClose)
100 DbClose();
101 return returnValue;
102 }
103 catch (SqlException e)
104 {
105 errorMessage = e.Message;
106 }
107 }
108 return -1;
109 }
110
111 public string ExecuteQuery(string sql)
112 {
113 return ExecuteQuery(sql, false);
114 }
115
116 public string ExecuteQuery(string sql, bool isLineByLine)
117 {
118 if (!IsOpen())
119 DbOpen();
120 if (IsOpen())
121 {
122 try
123 {
124 SqlCommand command = new SqlCommand(sql, connection);
125 SqlDataReader dataReader = command.ExecuteReader();
126 int fieldCount = dataReader.FieldCount;
127 string[,] fields = new string[fieldCount, 2];
128 for (int i = 0; i < fieldCount; i++)
129 {
130 fields[i, 0] = dataReader.GetName(i);
131 fields[i, 1] = dataReader.GetFieldType(i).Name;
132 }
133 List<object[]> values = new List<object[]>();
134 values.Add(new object[fieldCount]);
135 int[] rows = new int[3] { 0, -1, 0 };
136 string queryId = Guid.NewGuid().ToString();
137 if (!isLineByLine)
138 {
139 while (dataReader.Read())
140 {
141 object[] valueItems = new object[fieldCount];
142 for (int i = 0; i < fieldCount; i++)
143 valueItems[i] = dataReader.GetValue(i);
144 values.Add(valueItems);
145 }
146
147 //释放资源
148 dataReader.Close();
149 dataReader.Dispose();
150 command.Dispose();
151 if (isAutoClose)
152 DbClose();
153 }
154 else
155 {
156 rows[0] = 1;
157 queryCommand.Add(queryId, command);
158 queryDataReader.Add(queryId, dataReader);
159 }
160 queryResultValues.Add(queryId, values);
161 queryResultFields.Add(queryId, fields);
162 queryResultRows.Add(queryId, rows);
163 return queryId;
164 }
165 catch (SqlException e)
166 {
167 errorMessage = e.Message;
168 if (isAutoClose)
169 DbClose();
170 }
171 }
172 return null;
173 }
174
175 public bool Read(string queryId)
176 {
177 bool returnValue = false;
178 if (queryResultRows[queryId][0].Equals(0))
179 {
180 if (queryResultRows[queryId][2] <= queryResultValues[queryId].Count - 2)
181 {
182 queryResultRows[queryId][2]++;
183 queryResultValues[queryId][queryResultRows[queryId][2]].CopyTo(queryResultValues[queryId][0], 0);
184 if ((int)queryResultRows[queryId][2] >= queryResultValues[queryId].Count - 1)
185 queryResultValues[queryId].RemoveRange(1, queryResultValues[queryId].Count - 1);
186 returnValue = true;
187 }
188 }
189 else
190 {
191 if (!queryResultValues[queryId][1][1].Equals(0))
192 {
193 if (queryResultValues[queryId][1][1].Equals(-1))
194 queryResultValues[queryId][1][1] = queryDataReader[queryId].Read() ? 1 : 0;
195 if (queryResultValues[queryId][1][1].Equals(1) || queryResultValues[queryId][1][1].Equals(2))
196 {
197 queryResultValues[queryId][1][2] = (int)queryResultValues[queryId][1][2] + 1;
198 for (int i = 0; i < queryResultValues[queryId][0].Length; i++)
199 queryResultValues[queryId][0][i] = queryDataReader[queryId].GetValue(i);
200 returnValue = true;
201 }
202 if (queryResultValues[queryId][1][1].Equals(2))
203 queryResultValues[queryId][1][1] = 0;
204 if (queryResultValues[queryId][1][1].Equals(1))
205 queryResultValues[queryId][1][1] = queryDataReader[queryId].Read() ? 1 : 2;
206 if (queryResultValues[queryId][1][1].Equals(0))
207 {
208 //释放资源
209 queryDataReader[queryId].Close();
210 queryDataReader[queryId].Dispose();
211 queryDataReader.Remove(queryId);
212 queryCommand[queryId].Dispose();
213 queryCommand.Remove(queryId);
214
215 if (isAutoClose)
216 DbClose();
217 }
218 }
219 }
220 return returnValue;
221 }
222
223 public int Row(string queryId)
224 {
225 return queryResultRows[queryId][2];
226 }
227
228 private int FindFieldIndex(string fieldName, string queryId)
229 {
230 for (int i = 0; i < queryResultValues[queryId][0].Length; i++)
231 {
232 if (queryResultFields[queryId][i, 0] == fieldName)
233 return i;
234 }
235 return -1;
236 }
237
238 public string[] GetFields(string queryId)
239 {
240 string[] returnValues = new string[queryResultValues[queryId][0].Length];
241 for (int i = 0; i < returnValues.Length; i++)
242 returnValues[i] = queryResultFields[queryId][i, 0];
243 return returnValues;
244 }
245
246 public object[] GetValues(string queryId)
247 {
248 return queryResultValues[queryId][0];
249 }
250
251 //GetString
252 public string GetString(int fieldIndex, string queryId)
253 {
254 if (fieldIndex >= 0 && fieldIndex < queryResultValues[queryId][0].Length && queryResultValues[queryId][0][fieldIndex] != DBNull.Value)
255 {
256 switch (queryResultFields[queryId][fieldIndex, 1])
257 {
258 case "Int32":
259 case "UInt32":
260 case "Int64":
261 case "UInt64":
262 case "Single":
263 case "Double":
264 case "Decimal":
265 return queryResultValues[queryId][0][fieldIndex].ToString();
266 case "DateTime":
267 return ((DateTime)queryResultValues[queryId][0][fieldIndex]).ToString("yyyy-MM-dd HH:mm:ss");
268 }
269 return (string)queryResultValues[queryId][0][fieldIndex];
270 }
271 return null;
272 }
273 public string GetString(string fieldName, string queryId)
274 {
275 return GetString(FindFieldIndex(fieldName, queryId), queryId);
276 }
277
278 //GetInt
279 public int GetInt(int fieldIndex, string queryId)
280 {
281 if (fieldIndex >= 0 && fieldIndex < queryResultValues[queryId][0].Length && queryResultValues[queryId][0][fieldIndex] != DBNull.Value)
282 {
283 switch (queryResultFields[queryId][fieldIndex, 1])
284 {
285 case "UInt32":
286 return Int32.Parse(queryResultValues[queryId][0][fieldIndex].ToString());
287 case "Int64":
288 return (Int32)queryResultValues[queryId][0][fieldIndex];
289 case "UInt64":
290 return Int32.Parse(queryResultValues[queryId][0][fieldIndex].ToString());
291 }
292 return (Int32)queryResultValues[queryId][0][fieldIndex];
293 }
294 return 0;
295 }
296 public int GetInt(string fieldName, string queryId)
297 {
298 return GetInt(FindFieldIndex(fieldName, queryId), queryId);
299 }
300
301 //GetDouble
302 public double GetDouble(int fieldIndex, string queryId)
303 {
304 if (fieldIndex >= 0 && fieldIndex < queryResultValues[queryId][0].Length && queryResultValues[queryId][0][fieldIndex] != DBNull.Value)
305 {
306 switch (queryResultFields[queryId][fieldIndex, 1])
307 {
308 case "Decimal":
309 return (double)queryResultValues[queryId][0][fieldIndex];
310 }
311 return (double)queryResultValues[queryId][0][fieldIndex];
312 }
313 return 0;
314 }
315 public double GetDouble(string fieldName, string queryId)
316 {
317 return GetDouble(FindFieldIndex(fieldName, queryId), queryId);
318 }
319
320 //GetDouble
321 public decimal GetDecimal(int fieldIndex, string queryId)
322 {
323 if (fieldIndex >= 0 && fieldIndex < queryResultValues[queryId][0].Length && queryResultValues[queryId][0][fieldIndex] != DBNull.Value)
324 {
325 switch (queryResultFields[queryId][fieldIndex, 1])
326 {
327 case "Double":
328 return (decimal)queryResultValues[queryId][0][fieldIndex];
329 }
330 return (decimal)queryResultValues[queryId][0][fieldIndex];
331 }
332 return 0;
333 }
334 public decimal GetDecimal(string fieldName, string queryId)
335 {
336 return GetDecimal(FindFieldIndex(fieldName, queryId), queryId);
337 }
338
339 //GetDouble
340 public DateTime GetDateTime(int fieldIndex, string queryId)
341 {
342 if (fieldIndex >= 0 && fieldIndex < queryResultValues[queryId][0].Length && queryResultValues[queryId][0][fieldIndex] != DBNull.Value)
343 {
344 switch (queryResultFields[queryId][fieldIndex, 1])
345 {
346 case "String":
347 return Convert.ToDateTime(queryResultValues[queryId][0][fieldIndex]);
348 }
349 return (DateTime)queryResultValues[queryId][0][fieldIndex];
350 }
351 return new DateTime();
352 }
353 public DateTime GetDateTime(string fieldName, string queryId)
354 {
355 return GetDateTime(FindFieldIndex(fieldName, queryId), queryId);
356 }
357 public void Dispose(string queryId)
358 {
359 queryResultRows.Remove(queryId);
360 queryResultFields.Remove(queryId);
361 queryResultValues[queryId].Clear();
362 queryResultValues.Remove(queryId);
363 if (queryDataReader.ContainsKey(queryId))
364 {
365 queryDataReader[queryId].Close();
366 queryDataReader[queryId].Dispose();
367 queryDataReader.Remove(queryId);
368 }
369 if (queryCommand.ContainsKey(queryId))
370 {
371 queryCommand[queryId].Dispose();
372 queryCommand.Remove(queryId);
373 }
374 if (isAutoClose)
375 DbClose();
376 }
377
378 public static string AddSlashes(string value)
379 {
380 value = value.Replace("\\", "\\\\");
381 value = value.Replace("‘", "\\‘");
382 value = value.Replace("\"", "\\\"");
383 return value;
384 }
385
386 public static string[] SqlCombine(Hashtable data, string nonString, string escape)
387 {
388 string f = "", v = "", fv = "";
389 string filed;
390 foreach (DictionaryEntry entry in data)
391 {
392 filed = entry.Key.ToString();
393 if (!String.IsNullOrEmpty(escape) && escape.Length == 2)
394 {
395 filed = escape.Substring(0, 1) + filed + escape.Substring(1, 1);
396 }
397 f += ", " + filed;
398 v += ", ";
399 fv += ", " + filed + " = ";
400 if (nonString != null && nonString != "" && ("," + nonString + ",").IndexOf("," + entry.Key.ToString() + ",") > -1)
401 {
402 v += entry.Value.ToString();
403 fv += entry.Value.ToString();
404 }
405 else
406 {
407 v += "‘" + AddSlashes(entry.Value.ToString()) + "‘";
408 fv += "‘" + AddSlashes(entry.Value.ToString()) + "‘";
409 }
410 }
411 return new string[] { f.Substring(2), v.Substring(2), fv.Substring(2) };
412 }
413 }
414 }