Other Posts in Database

  1. SQL Helper Class and Jack Thompson
  2. Determining if a Database or Table Exists in SQL Server
  3. Helpful SQL Queries for SQL Server in C#
  4. Simplified Database Creation and Pulling Database Structure in C#

SQL Helper Class and Jack Thompson

6/5/2008

I've got a couple things to talk about today. The first is code that you might find useful. The other day I was bored and rather annoyed at dealing with the SQL helper class we have at work, so I went and created a new one:

   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: }

The class handles connections, stored procedures, transactions, parameters, etc. Pretty much everything that the old class did with one exception. When you are getting data, you supply the function with a default value.  If the value returned by the call is null or an empty string or something, it returns the default value that you supplied. I've been using it in a set of data layer objects that I had to create and it has definitely helped cut down on my screaming and cursing at the shear number of null values this database has... And don't get me started on the fact that it's due to bad design in the first place... Anyway, use the code and hopefully it will make your day a bit easier as well. 

Now for the fun part of the post... Jack Thompson looks like he's going to be disbarred (perhaps for 10 years). I have nothing against people who feel that kids shouldn't play adult themed games. I like the idea of checking IDs to buy them, I think parents should be better informed, etc. (I feel the same about movies, etc.) I also have nothing against lawyers. I work for lawyers, heck, I'm married to a lawyer. But damn that man is crazy... So to me, him being disbarred=good thing. Although I have to say that he scares me. I keep waiting for him to snap and just start killing everyone in sight.



Comments