Toolkit.DataAccess

  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 }