SHARE
TWEET

C# Wrapper class for SQL Server

a guest Sep 19th, 2014 586 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Web;
  5.  
  6. using System.Configuration;
  7. using System.Data;
  8. using System.Data.Sql;
  9. using System.Data.SqlClient;
  10.  
  11. /// <summary>
  12. /// New interface for database connectivity.
  13. /// </summary>
  14. public class Database
  15. {
  16.     #region Object properties
  17.  
  18.     private SqlConnection connection;
  19.     public DataTable resultsTable;
  20.     public List<SqlParameter> queryParameters;
  21.  
  22.     #endregion
  23.  
  24.     #region Constructs
  25.     /// <summary>
  26.     /// Default construct
  27.     /// </summary>
  28.     public Database( )
  29.     {
  30.         connection = new SqlConnection(
  31.             ConfigurationManager.ConnectionStrings[
  32.                 ConfigurationManager.AppSettings["ConnectionString"].ToString()
  33.             ].ToString()
  34.         );
  35.  
  36.         resultsTable = new DataTable();
  37.         queryParameters = new List<SqlParameter>();
  38.  
  39.     }
  40.  
  41.  
  42.  
  43.     /// <summary>
  44.     /// Construct for specific connection strings
  45.     /// </summary>
  46.     /// <param name="connectionName">Name of the connection string in web.config</param>
  47.     public Database( string connectionName )
  48.     {
  49.         try
  50.         {
  51.             connection = new SqlConnection(
  52.                 ConfigurationManager.ConnectionStrings[connectionName].ToString()
  53.             );
  54.  
  55.             resultsTable = new DataTable();
  56.             queryParameters = new List<SqlParameter>();
  57.         }
  58.         catch
  59.         {
  60.             throw new Exception( "A connection string with that name could not be found in web.config" );
  61.         }
  62.  
  63.     }
  64.  
  65.  
  66.  
  67.     /// <summary>
  68.     /// Construct for manual connection strings (No Validation)
  69.     /// </summary>
  70.     /// <param name="connectionString">The custom connection string</param>
  71.     /// <param name="manual">UNUSED</param>
  72.     public Database( string connectionString, bool manual )
  73.     {
  74.         connection = new SqlConnection(
  75.             connectionString
  76.         );
  77.  
  78.         resultsTable = new DataTable();
  79.         queryParameters = new List<SqlParameter>();
  80.     }
  81.     #endregion
  82.  
  83.     #region commandExecutors
  84.     /// <summary>
  85.     /// Executes a Scalar query and returns the int result
  86.     /// </summary>
  87.     /// <param name="commandName">Name of the stored procedure to execute</param>
  88.     /// <param name="commandType">SQL command Type (Usally CommandType.StoredProcedure)</param>
  89.     /// <param name="hasParameters">True if query requires parameters, else false if not.</param>
  90.     /// <returns>The integer result of the scalar query</returns>
  91.     /// REF: http://msdn.microsoft.com/en-gb/library/system.data.sqlclient.sqlcommand.executescalar%28v=vs.110%29.aspx
  92.     public object runScalar( string commandName, bool hasParameters, TypeCode returnType )
  93.     {
  94.         //Verify Parameters have been supplied if required.
  95.         if ( ( hasParameters && this.queryParameters.Count > 0 ) || ( !hasParameters ) )
  96.         {
  97.             SqlCommand command = new SqlCommand( commandName, this.connection )
  98.             {
  99.                 CommandType = CommandType.StoredProcedure
  100.             };
  101.  
  102.             if ( queryParameters != null && hasParameters )
  103.             {
  104.                 //Add each parameter from the parameter collection to the command object.
  105.                 foreach ( SqlParameter param in queryParameters )
  106.                     command.Parameters.Add( param );
  107.             }
  108.  
  109.             try
  110.             {
  111.                 this.connection.Open();
  112.                 if ( returnType == TypeCode.Int32 )
  113.                     return (int)command.ExecuteScalar();
  114.                 else if ( returnType == TypeCode.String )
  115.                     return command.ExecuteScalar().ToString();
  116.                 else if ( returnType == TypeCode.DateTime )
  117.                     return Convert.ToDateTime( command.ExecuteScalar() );
  118.                 else
  119.                     return -1;
  120.             }
  121.             catch
  122.             {
  123.                 return -1;
  124.             }
  125.             finally
  126.             {
  127.                 this.connection.Close();
  128.                 this.cleanUp();
  129.             }
  130.         }
  131.         else
  132.             throw new Exception( "You attempted to execute a select query with an incorrect number of required parameters. If the query doesn't need parameters, set hasParameters to false in method call." );
  133.     }
  134.  
  135.  
  136.     /// <summary>
  137.     /// Executes a SELECT statement against the database
  138.     /// </summary>
  139.     /// <param name="commandName">Name of the stored proceedure to execute</param>
  140.     /// <param name="commandType">SQL command Type (Usually CommandType.StoredProceedure)</param>
  141.     /// <param name="hasParameters">True if query requires parameters, else false if not.</param>
  142.     /// <returns></returns>
  143.     public DataTable runSelect( string commandName, bool hasParameters )
  144.     {
  145.         //Verify Parameters have been supplied if required.
  146.         if ( ( hasParameters && this.queryParameters.Count > 0 ) || ( !hasParameters ) )
  147.         {
  148.             //Create a new command instance.
  149.             SqlCommand command = new SqlCommand( commandName, this.connection )
  150.             {
  151.                 CommandType = CommandType.StoredProcedure,
  152.                 CommandTimeout = 600
  153.             };
  154.  
  155.             if ( queryParameters != null && hasParameters )
  156.             {
  157.                 //Add each parameter from the parameter collection to the command object.
  158.                 foreach ( SqlParameter param in queryParameters )
  159.                     command.Parameters.Add( param );
  160.             }
  161.  
  162.             try
  163.             {
  164.                 SqlDataAdapter adapter = new SqlDataAdapter( command );
  165.  
  166.                 //Flush resultsTable
  167.                 resultsTable = new DataTable();
  168.                 //Open the connection
  169.                 connection.Open();
  170.                 //Create an SqlDataAdapter instance, and use it to fill the 'resultSet' datatable with the returned results.
  171.                 //Command is executed here.
  172.                 adapter.Fill( resultsTable );
  173.  
  174.                 return resultsTable;
  175.             }
  176.             //TODO: Improve this error handling.
  177.             //Handle SQL Specific exceptions
  178.             catch ( SqlException sqlEx )
  179.             {
  180.                 return null;
  181.             }
  182.             //Handle Generic exceptions
  183.             catch ( Exception ex )
  184.             {
  185.                 return null;
  186.             }
  187.             //Close the database connection.
  188.             finally
  189.             {
  190.                 this.connection.Close();
  191.                 this.cleanUp();
  192.             }
  193.         }
  194.         else
  195.             throw new Exception( "You attempted to execute a select query with an incorrect number of required parameters. If the query doesn't need parameters, set hasParameters to false in method call." );
  196.     }
  197.  
  198.     /// <summary>
  199.     /// Executes and INSERT statement against the database.
  200.     /// </summary>
  201.     /// <param name="commandName">Name of the stored proceedure to execute</param>
  202.     /// <param name="commandType">SQL command Type (Usually CommandType.StoredProceedure)</param>
  203.     /// <returns></returns>
  204.     public int runInsert( string commandName )
  205.     {
  206.         if ( this.getParamCount() > 0 )
  207.         {
  208.             //Create a new command instance.
  209.             SqlCommand command = new SqlCommand( commandName, this.connection )
  210.             {
  211.                 CommandType = CommandType.StoredProcedure
  212.             };
  213.  
  214.             if ( this.queryParameters != null )
  215.             {
  216.                 //Add each parameter from the parameter collection to the command object.
  217.                 foreach ( SqlParameter param in this.queryParameters )
  218.                     command.Parameters.Add( param );
  219.             }
  220.  
  221.             try
  222.             {
  223.                 this.connection.Open();
  224.  
  225.                 int rowsAffected = command.ExecuteNonQuery();
  226.  
  227.                 return rowsAffected;
  228.             }
  229.             //TODO: Improve this error handling.
  230.             //Handle SQL Specific exceptions
  231.             catch ( SqlException sqlEx )
  232.             {
  233.                 return -1;
  234.             }
  235.             //Handle Generic exceptions
  236.             catch ( Exception ex )
  237.             {
  238.                 return -1;
  239.             }
  240.             //Close the database connection.
  241.             finally
  242.             {
  243.                 this.connection.Close();
  244.                 this.cleanUp();
  245.             }
  246.         }
  247.         else
  248.             throw new Exception( "Query is missing required parameters!" );
  249.     }
  250.  
  251.     /// <summary>
  252.     /// Execures an UPDATE command against the database
  253.     /// </summary>
  254.     /// <param name="commandName">Name of the stored proceedure to execute</param>
  255.     /// <param name="commandType">SQL command Type (Usually CommandType.StoredProceedure)</param>
  256.     /// <returns></returns>
  257.     public int runUpdate( string commandName )
  258.     {
  259.         if ( this.getParamCount() > 0 )
  260.         {
  261.             //Create a new command instance.
  262.             SqlCommand command = new SqlCommand( commandName, this.connection )
  263.             {
  264.                 CommandType = CommandType.StoredProcedure
  265.             };
  266.  
  267.             //Add each parameter from the parameter collection to the command object.
  268.             foreach ( SqlParameter param in this.queryParameters )
  269.                 command.Parameters.Add( param );
  270.  
  271.             try
  272.             {
  273.                 this.connection.Open();
  274.  
  275.                 int rowsAffected = command.ExecuteNonQuery();
  276.  
  277.                 return rowsAffected;
  278.             }
  279.             //TODO: Improve this error handling.
  280.             //Handle SQL Specific exceptions
  281.             catch ( SqlException sqlEx )
  282.             {
  283.                 return 0;
  284.             }
  285.             //Handle Generic exceptions
  286.             catch ( Exception ex )
  287.             {
  288.                 return 0;
  289.             }
  290.             //Close the database connection.
  291.             finally
  292.             {
  293.                 this.connection.Close();
  294.                 this.cleanUp();
  295.             }
  296.         }
  297.         else
  298.             throw new Exception( "Query is missing required parameters!" );
  299.     }
  300.  
  301.     /// <summary>
  302.     /// Executes a DELETE command against the database.
  303.     /// </summary>
  304.     /// <param name="commandName">Name of the stored proceedure to execute</param>
  305.     /// <param name="commandType">SQL command Type (Usually CommandType.StoredProceedure)</param>
  306.     /// <returns></returns>
  307.     public int runDelete( string commandName )
  308.     {
  309.         if ( this.getParamCount() > 0 )
  310.         {
  311.             //Create a new command instance.
  312.             SqlCommand command = new SqlCommand( commandName, this.connection )
  313.             {
  314.                 CommandType = CommandType.StoredProcedure
  315.             };
  316.  
  317.             if ( this.queryParameters != null )
  318.             {
  319.                 //Add each parameter from the parameter collection to the command object.
  320.                 foreach ( SqlParameter param in this.queryParameters )
  321.                     command.Parameters.Add( param );
  322.             }
  323.             else
  324.             {
  325.                 //Force an exception, as DELETE commands require parameters.
  326.                 throw new Exception( "" );
  327.             }
  328.  
  329.             try
  330.             {
  331.                 this.connection.Open();
  332.  
  333.                 int rowsAffected = command.ExecuteNonQuery();
  334.  
  335.                 return rowsAffected;
  336.             }
  337.             //TODO: Improve this error handling.
  338.             //Handle SQL Specific exceptions
  339.             catch ( SqlException sqlEx )
  340.             {
  341.                 return 0;
  342.             }
  343.             //Handle Generic exceptions
  344.             catch ( Exception ex )
  345.             {
  346.                 return 0;
  347.             }
  348.             //Close the database connection.
  349.             finally
  350.             {
  351.                 this.connection.Close();
  352.                 this.cleanUp();
  353.             }
  354.         }
  355.         else
  356.             throw new Exception( "Query is missing required parameters!" );
  357.     }
  358.  
  359.     #endregion
  360.  
  361.     #region Utility Methods - INCOMPLETE
  362.  
  363.     public ConnectionState getConnectionState( )
  364.     {
  365.         return this.connection.State;
  366.     }
  367.  
  368.     public bool isConnected( )
  369.     {
  370.         ConnectionState connectionState = getConnectionState();
  371.  
  372.         if ( connectionState == ConnectionState.Open )
  373.             return true;
  374.         else
  375.             return false;
  376.     }
  377.  
  378.     /// <summary>
  379.     /// Returns the number if records stored in the result datatable
  380.     /// </summary>
  381.     /// <returns>Number of datatable rows. Returns -1 if object is null.</returns>
  382.     public int getResultTableSize( )
  383.     {
  384.         if ( this.resultsTable != null )
  385.             return this.resultsTable.Rows.Count;
  386.         else
  387.             return -1;
  388.     }
  389.  
  390.     /// <summary>
  391.     /// Returns the number of parameters that have been created for the query
  392.     /// </summary>
  393.     /// <returns>Number of SqlParameter objects present in the List. Returns -1 if object is null.</returns>
  394.     public int getParamCount( )
  395.     {
  396.         if ( this.queryParameters != null )
  397.             return this.queryParameters.Count;
  398.         else
  399.             return -1;
  400.     }
  401.  
  402.     /// <summary>
  403.     /// Ensures that the SQLParameter List has been cleaned. This allows multiple commands to be
  404.     /// exectued on the same database object instance.
  405.     /// </summary>
  406.     private void cleanUp( )
  407.     {
  408.         //Reinitialise the attribute.
  409.         queryParameters = new List<SqlParameter>();
  410.     }
  411.  
  412.     #endregion
  413. }
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top