Sql Database Wrapper

I often find myself repeating same processes in .NET over the course of several projects. One of these repeating tasks in writing code to access database data. .NET goes a long way towards assisting developers in simplifying their work and automating tasks. At times you need to query data in your application code to perform some logic. This can quickly become tedious, and to assist I created a C# class that provides some basic functionality for access an SQL database.

The source file is available to download in the form of a zip file at the bottom of the page. The source was developed with C#.NET in Visual Studio 2010 for the .NET framework version 4. It should work with other versions of .NET but may require minor tweaks. If you have problems using it in your code, leave a message below and I will try to assist.

1. Initialising the wrapper

   Include the .cs file from the zip in your project. Firstly you will need to provide a connection string, this can be loaded from application configuration file or supplied directly.
   

   // Assuming GetConnectionstring() returns a valid connection string
   SqlDbManager.ConnectionString = GetConnectionstring();

   The connection string property can only be initialised once. I added this requirement as my projects are dependant on one database or databases that are accessible from this database.

2. Creating a connection

   Once the wrapper has been initialised with a connection string, it can be used by calling the GetConnection() static method to retrieve a connection. The wrapper is designed to keep a number of connections to hand and provide them on demand. If there are no available connections, then a new connection is spawned. Also, the connection retrieval process has been developed with multi threading in mind.
   To retrieve a connection use :
   

       SqlDbManger connection = SqlDbManager.GetNewConnection();
       

3. Working with SqlDbManager

   once a new connection has been established it can be used to retrieve data in various ways. I have allowed for the most common scenarios of, update, insert, retrieve a single value, retrieve a record, or retrieve a set of data.
   

   1. Passing Parameters

      Any sql command can have parameters and the SqlDbManager will accept any number of parameters supplied to it.
      The parameters must be supplied in the form of a list, ie, List<SqlParameter>. List is generic type, and used to load SqlParameters to the command.
      

      List <SqlParameter> parameters = new List<SqlParameter>();
      parameters.add(new SqlParameter("@employeeName", "Joe Bloggs"));

   2. ExecuteNonQuery

      ExecuteNonQuery() works in a similar way to the SqlCommand.ExecuteNonQuery() method. It will carry out the requested command against the connection and return the number of rows affected. It is most effective for update or insert commands, where no data is returned.
      

      // Assuming there is a data table emplyee with columns id, and name already present
      int rowsAffected = connection.ExecuteNonQuery("INSERT INTO employee (id, name) VALUES (1, N'a')", CommandType.Text, null);

   3. ExecuteScalar

      ExecuteScalar() works in a similar way to SqlCommand.ExecuteScalar() method. It will carry out the requested command against the connection and return the first column of the first row.
      

      // Assuming there is a stored procedure sp_GetEmployeeID already present that returns an employee ID for a supplied employee name
      List <SqlParameter> parameters = new List<SqlParameter>();
      parameters.add(new SqlParameter("@employeeName", "Joe Bloggs"));
      int employeeID = (int)connection.ExecuteScalar("sp_GetEmployeeID", CommandType.StoredProcedure, parameters);

   4. ExecuteRetrieve

      ExecuteRetrieve() will get a datatable of the results returned from the database. This is most effective for instances where a Sql command will return multiple rows.
      

      // Assuming there is a stored procedure sp_GetAllEmployees already present that returns all employees
      DataTable employees= connection.ExecuteRetrieve("sp_GetEmployeeID", CommandType.StoredProcedure, null);

   5. ExecuteRetrieveRow

      ExecuteRetrieveRow() will return the first row from the results returned by the sq query. This is most effective for instances where a Sql command will return just one record or when only the first record is wanted.
      

      // Assuming there is a stored procedure sp_GetEmployee already present that returns information about one employee
      List <SqlParameter> parameters = new List<SqlParameter>();
      parameters.add(new SqlParameter("@employeeName", "Joe Bloggs"));
      DataRow employee = connection.ExecuteRetrieve("sp_GetEmployee", CommandType.StoredProcedure, parameters);

4. Summary

   The SqlDbManager class is a basic wrapper for accessing Sql data. It allows querying of the sql database in various ways, with different adaptations of the data.

Article Files : SqlDbManager.zip

Disclaimer: The content provided in this article is not warranted or guaranteed by rnddev. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts. As such it is inferred on to the reader to employ real-world tactics for security and implementation of best practices. I am not liable for any negative consequences that may result from implementing any information covered in this or other articles or blog posts.