1: /*
2: Copyright (c) 2010 <a href="http://www.gutgames.com">James Craig</a>
3:
4: Permission is hereby granted, free of charge, to any person obtaining a copy
5: of this software and associated documentation files (the "Software"), to deal
6: in the Software without restriction, including without limitation the rights
7: to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
8: copies of the Software, and to permit persons to whom the Software is
9: furnished to do so, subject to the following conditions:
10:
11: The above copyright notice and this permission notice shall be included in
12: all copies or substantial portions of the Software.
13:
14: THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
15: IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
16: FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
17: AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
18: LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
19: OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
20: THE SOFTWARE.*/
21:
22: #region Usings
23: using System;
24: using System.Data;
25: using System.Data.SqlClient;
26:
27: #endregion
28:
29: namespace Utilities.SQL
30: { 31: /// <summary>
32: /// SQL Helper class
33: /// </summary>
34: public class SQLHelper:IDisposable
35: { 36: #region Constructors
37:
38: /// <summary>
39: /// Constructor
40: /// </summary>
41: /// <param name="Command">Stored procedure/SQL Text to use</param>
42: /// <param name="ConnectionUsing">The connection string to user</param>
43: /// <param name="CommandType">The command type of the command sent in</param>
44: public SQLHelper(string Command, string ConnectionUsing,CommandType CommandType)
45: { 46: Connection = new SqlConnection(ConnectionUsing);
47: _Command = Command;
48: _ExecutableCommand = new SqlCommand(_Command, Connection);
49: _ExecutableCommand.CommandType = CommandType;
50: this._CommandType = CommandType;
51: }
52:
53: #endregion
54:
55: #region Public Functions
56:
57: /// <summary>
58: /// Begins a transaction
59: /// </summary>
60: public void BeginTransaction()
61: { 62: Transaction = Connection.BeginTransaction();
63: Command = _Command;
64: }
65:
66: /// <summary>
67: /// Commits a transaction
68: /// </summary>
69: public void Commit()
70: { 71: if (Transaction != null)
72: { 73: Transaction.Commit();
74: }
75: }
76:
77: /// <summary>
78: /// Rolls back a transaction
79: /// </summary>
80: public void Rollback()
81: { 82: if (Transaction != null)
83: { 84: Transaction.Rollback();
85: }
86: }
87:
88: /// <summary>
89: /// Opens the connection
90: /// </summary>
91: public void Open()
92: { 93: if (_ExecutableCommand != null)
94: { 95: if (_ExecutableCommand.Connection != null)
96: { 97: _ExecutableCommand.Connection.Open();
98: }
99: }
100: }
101:
102: /// <summary>
103: /// Closes the connection
104: /// </summary>
105: public void Close()
106: { 107: if (_ExecutableCommand != null)
108: { 109: if (_ExecutableCommand.Connection != null)
110: { 111: _ExecutableCommand.Connection.Close();
112: }
113: }
114: }
115:
116: /// <summary>
117: /// Adds a parameter to the call (for strings only)
118: /// </summary>
119: /// <param name="ID">Name of the parameter</param>
120: /// <param name="Value">Value to add</param>
121: /// <param name="Length">Size of the string(either -1 or 5000 should be used to indicate nvarchar(max))</param>
122: public void AddParameter(string ID, string Value, int Length)
123: { 124: if (Length == 5000)
125: { 126: Length = -1;
127: }
128: if (_ExecutableCommand != null)
129: { 130: if (_ExecutableCommand.Parameters.Contains(ID))
131: { 132: if (string.IsNullOrEmpty(Value))
133: { 134: _ExecutableCommand.Parameters[ID].IsNullable = true;
135: _ExecutableCommand.Parameters[ID].Value = System.DBNull.Value;
136: }
137: else
138: { 139: _ExecutableCommand.Parameters[ID].Value = Value;
140: }
141: }
142: else
143: { 144: SqlParameter Parameter = _ExecutableCommand.Parameters.Add(ID, SqlDbType.NVarChar, Length);
145: if (string.IsNullOrEmpty(Value))
146: { 147: Parameter.IsNullable = true;
148: Parameter.Value = System.DBNull.Value;
149: }
150: else
151: { 152: Parameter.Value = Value;
153: }
154: }
155: }
156: }
157:
158: /// <summary>
159: /// Adds an output parameter
160: /// </summary>
161: /// <param name="ID">Name of the parameter</param>
162: /// <param name="Type">SQL type of the parameter</param>
163: public void AddOutputParameter(string ID, SqlDbType Type)
164: { 165: if (_ExecutableCommand != null)
166: { 167: if (_ExecutableCommand.Parameters.Contains(ID))
168: { 169: _ExecutableCommand.Parameters[ID].Value = null;
170: _ExecutableCommand.Parameters[ID].Direction = ParameterDirection.Output;
171: }
172: else
173: { 174: SqlParameter Parameter = _ExecutableCommand.Parameters.Add(ID, Type);
175: Parameter.Value = null;
176: Parameter.Direction = ParameterDirection.Output;
177: }
178: }
179: }
180:
181: /// <summary>
182: /// Adds an output parameter
183: /// </summary>
184: /// <param name="ID">Name of the parameter</param>
185: /// <param name="Length">Length of the string (either -1 or 5000 should be used to indicate nvarchar(max))</param>
186: public void AddOutputParameter(string ID, int Length)
187: { 188: if (Length == 5000)
189: { 190: Length = -1;
191: }
192: if (_ExecutableCommand != null)
193: { 194: if (_ExecutableCommand.Parameters.Contains(ID))
195: { 196: _ExecutableCommand.Parameters[ID].Value = null;
197: _ExecutableCommand.Parameters[ID].Direction = ParameterDirection.Output;
198: }
199: else
200: { 201: SqlParameter Parameter = _ExecutableCommand.Parameters.Add(ID, SqlDbType.NVarChar, Length);
202: Parameter.Value = null;
203: Parameter.Direction = ParameterDirection.Output;
204: }
205: }
206: }
207:
208: /// <summary>
209: /// Adds a parameter to the call (for all types other than strings)
210: /// </summary>
211: /// <param name="ID">Name of the parameter</param>
212: /// <param name="Value">Value to add</param>
213: /// <param name="Type">SQL type of the parameter</param>
214: public void AddParameter(string ID, object Value, SqlDbType Type)
215: { 216: if (_ExecutableCommand != null)
217: { 218: if (_ExecutableCommand.Parameters.Contains(ID))
219: { 220: if (Value == null)
221: { 222: _ExecutableCommand.Parameters[ID].IsNullable = true;
223: _ExecutableCommand.Parameters[ID].Value = System.DBNull.Value;
224: }
225: else
226: { 227: _ExecutableCommand.Parameters[ID].Value = Value;
228: }
229: }
230: else
231: { 232: SqlParameter Parameter = _ExecutableCommand.Parameters.Add(ID, Type);
233: if (Value == null)
234: { 235: Parameter.IsNullable = true;
236: Parameter.Value = System.DBNull.Value;
237: }
238: else
239: { 240: Parameter.Value = Value;
241: }
242: }
243: }
244: }
245:
246: /// <summary>
247: /// Executes the stored procedure and returns a reader object
248: /// </summary>
249: public void ExecuteReader()
250: { 251: if (_ExecutableCommand != null)
252: { 253: _Reader = _ExecutableCommand.ExecuteReader();
254: }
255: }
256:
257: /// <summary>
258: /// Executes the stored procedure as a non query
259: /// </summary>
260: /// <returns>Number of rows effected</returns>
261: public int ExecuteNonQuery()
262: { 263: if (_ExecutableCommand != null)
264: { 265: return _ExecutableCommand.ExecuteNonQuery();
266: }
267: return 0;
268: }
269:
270: /// <summary>
271: /// Executes the stored procedure as a scalar query
272: /// </summary>
273: /// <returns>The object of the first row and first column</returns>
274: public object ExecuteScalar()
275: { 276: if (_ExecutableCommand != null)
277: { 278: return _ExecutableCommand.ExecuteScalar();
279: }
280: return null;
281: }
282:
283: /// <summary>
284: /// Is there more information?
285: /// </summary>
286: /// <returns>True if there is more rows, false otherwise</returns>
287: public bool Read()
288: { 289: if (_Reader != null)
290: { 291: return _Reader.Read();
292: }
293: return false;
294: }
295:
296: /// <summary>
297: /// Returns an output parameter's value
298: /// </summary>
299: /// <param name="ID">Parameter name</param>
300: /// <param name="Default">Default value for the parameter</param>
301: /// <returns>if the parameter exists (and isn't null or empty), it returns the parameter's value. Otherwise the default value is returned.</returns>
302: public object GetOutputParameter(string ID, object Default)
303: { 304: if (_ExecutableCommand != null)
305: { 306: if (_ExecutableCommand.Parameters[ID] != null && !string.IsNullOrEmpty(_ExecutableCommand.Parameters[ID].ToString()))
307: { 308: return _ExecutableCommand.Parameters[ID].Value;
309: }
310: }
311: return Default;
312: }
313:
314: /// <summary>
315: /// Returns a parameter's value
316: /// </summary>
317: /// <param name="ID">Parameter name</param>
318: /// <param name="Default">Default value for the parameter</param>
319: /// <returns>if the parameter exists (and isn't null or empty), it returns the parameter's value. Otherwise the default value is returned.</returns>
320: public object GetParameter(string ID, object Default)
321: { 322: if (_Reader != null)
323: { 324: if (_Reader[ID] != null && !string.IsNullOrEmpty(_Reader[ID].ToString()))
325: { 326: return _Reader[ID];
327: }
328: }
329: return Default;
330: }
331:
332: /// <summary>
333: /// Returns a parameter's value
334: /// </summary>
335: /// <param name="Position">Position in the row</param>
336: /// <param name="Default">Default value for the parameter</param>
337: /// <returns>if the parameter exists (and isn't null or empty), it returns the parameter's value. Otherwise the default value is returned.</returns>
338: public object GetParameter(int Position, object Default)
339: { 340: if (_Reader != null)
341: { 342: if (_Reader[Position] != null && !string.IsNullOrEmpty(_Reader[Position].ToString()))
343: { 344: return _Reader[Position];
345: }
346: }
347: return Default;
348: }
349:
350: /// <summary>
351: /// Clears the parameters
352: /// </summary>
353: public void ClearParameters()
354: { 355: if (_ExecutableCommand != null)
356: { 357: _ExecutableCommand.Parameters.Clear();
358: }
359: }
360:
361: #endregion
362:
363: #region Properties
364:
365: /// <summary>
366: /// Stored procedure's name or SQL Text
367: /// </summary>
368: public string Command
369: { 370: get { return _Command; } 371: set
372: { 373: _Command = value;
374: if (_Reader != null)
375: { 376: _Reader.Close();
377: _Reader.Dispose();
378: _Reader = null;
379: }
380: if (_ExecutableCommand != null)
381: { 382: _ExecutableCommand.Dispose();
383: _ExecutableCommand = null;
384: }
385: if (Transaction != null)
386: { 387: _ExecutableCommand = new SqlCommand(_Command, Connection, Transaction);
388: }
389: else
390: { 391: _ExecutableCommand = new SqlCommand(_Command, Connection);
392: }
393: _ExecutableCommand.CommandType = _CommandType;
394: }
395: }
396:
397: /// <summary>
398: /// Command Type
399: /// </summary>
400: public CommandType CommandType
401: { 402: get { return _CommandType; } 403: set
404: { 405: _CommandType = value;
406: if (_Reader != null)
407: { 408: _Reader.Close();
409: _Reader.Dispose();
410: _Reader = null;
411: }
412: if (_ExecutableCommand != null)
413: { 414: _ExecutableCommand.Dispose();
415: _ExecutableCommand = null;
416: }
417: if (Transaction != null)
418: { 419: _ExecutableCommand = new SqlCommand(_Command, Connection, Transaction);
420: }
421: else
422: { 423: _ExecutableCommand = new SqlCommand(_Command, Connection);
424: }
425: _ExecutableCommand.CommandType = _CommandType;
426: }
427: }
428:
429: #endregion
430:
431: #region Private Variables
432: private SqlConnection Connection = null;
433: private string _Command = null;
434: private SqlCommand _ExecutableCommand = null;
435: private SqlDataReader _Reader = null;
436: private CommandType _CommandType;
437: private SqlTransaction Transaction = null;
438: #endregion
439:
440: #region IDisposable Members
441:
442: public void Dispose()
443: { 444: if (Connection != null)
445: { 446: Connection.Dispose();
447: Connection = null;
448: }
449: if (Transaction != null)
450: { 451: Transaction.Dispose();
452: Transaction = null;
453: }
454: if (_ExecutableCommand != null)
455: { 456: _ExecutableCommand.Dispose();
457: _ExecutableCommand = null;
458: }
459: if (_Reader != null)
460: { 461: _Reader.Dispose();
462: _Reader = null;
463: }
464: }
465:
466: #endregion
467: }
468: }
RSS Feed
Twitter Feed