C#

A basic database SCRUD class using abstract and generic modifiers

By DeveloperDrive Staff

Introduction

SCRUD is short for Select, Create, Read, Update and Delete. These are the general actions that any program will perform when working with databases. Yes, there are plenty of ORM applications ( http://en.wikipedia.org/wiki/Object-relational_mapping ) out there that can replace this, but as a general introduction to abstract or generic classes and general development I still think this is worth a read.

I should also explain why I have used an abstract class instead of an interface. It’s quiet simple really; I wanted the class to have optional/common functionality and state. You don’t always want to perform all SCRUD actions, and any data mapper should always have a connection object. This connection object can be shared, initialized outside of the class or be part of a transaction. This combined with the generic class gives us greater flexibility and utility when using the data mapper.

I’ll expand on this and add more functionality on this in the 2nd part of the tutorial next week.

Generic Classes
http://msdn.microsoft.com/en-us/library/sz6zd40f(v=vs.110).aspx
Abstract
http://msdn.microsoft.com/en-us/library/sf985hc5(v=vs.110).aspx

Step 1: Creating our base class, IDataMapper

We know the methods we want to define (SCRUD), but we also know that we’ll need a database connection. We also do not know yet, what type of data we will be using.

To define a generic class you declare the class as normal and then append <T> e.g. IDataMapper<T>

Now when we are implementing our main class, we can define for each implementation the type of object we will be dealing with. 

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Coding.Exercise
{
    /// <summary>
    /// Abstract/Interface class common to all data mappers
    /// </summary>
    /// <typeparam name="T">The type of object the mapper is for</typeparam>
    public abstract class IDataMapper<T>
    {
        /// <summary>
        /// A .Net database connection (SQL Server, MySql, Oracle, etc.... )
        /// </summary>
        public IDbConnection Connection { get; private set; }

        /// <summary>
        /// Reads configuration from the app.config and initializes the data mapper
        /// </summary>
        /// <param name="connection">A .net connection that implements IDbConnection</param>
        public IDataMapper(IDbConnection connection)
        {
            if (connection == null)
                throw new ArgumentNullException("A valid database connection is required");

            this.Connection = connection;
        }

        /// <summary>
        /// Default select method for type T
        /// </summary>
        /// <param name="exError">Out exception object</param>
        /// <returns>List of type T</returns>
        public abstract List<T> Select(out Exception exError);

        /// <summary>
        /// Default create method for type T
        /// </summary>
        /// <param name="instance">The instance to create</param>
        /// <param name="exError">Out exception object</param>
        /// <returns>Boolean success/failure</returns>
        public abstract bool Create(T instance, out Exception exError);

        /// <summary>
        /// Default read method for type T 
        /// </summary>
        /// <param name="ID">ID of instance to read</param>
        /// <param name="exError">Out exception object</param>
        /// <returns>Instance of type T</returns>
        public abstract T Read(int ID, out Exception exError);

        /// <summary>
        /// Default read method for type T 
        /// </summary>
        /// <param name="instance">Object of instance to read</param>
        /// <param name="exError">Out exception object</param>
        /// <returns>Instance of type T</returns>
        public abstract T Read(T instance, out Exception exError);

        /// <summary>
        /// Default update method for type T
        /// </summary>
        /// <param name="instance">Object of instance to update</param>
        /// <param name="exError">Out exception object</param>
        /// <returns>Instance of type T</returns>
        public abstract bool Update(T instance, out Exception exError);

        /// <summary>
        /// Default delete method for type T 
        /// </summary>
        /// <param name="ID">ID of instance to delete</param>
        /// <param name="exError">Out exception object</param>
        /// <returns>Boolean success/failure</returns>
        public abstract bool Delete(int ID, out Exception exError);

        /// <summary>
        /// Default delete method for type T 
        /// </summary>
        /// <param name="instance">Object of instance to delete</param>
        /// <param name="exError">Out exception object</param>
        /// <returns>Boolean success/failure</returns>
        public abstract bool Delete(T instance, out Exception exError);
    }
}

When defining this class you’ll notice I have also added an “out” Exception parameter to the SCRUD methods. I will use this to handle and return any exceptions that occur within the method. Using a try/catch block you can safely handle the exceptions you need while returning useful states such as false, null or an empty list. This reduces the number of conditional checks you have and reduces the risk of unhandled exceptions causing havoc.

Next we’ll need a class that we want to perform SCRUD actions on, let’s go with a simple User. 

/// <summary>
/// A simple user class
/// </summary>
public class User
{
    /// <summary>
    /// A nullable user id
    /// </summary>
    public int? UserID = null;

    /// <summary>
    /// The username
    /// </summary>
    public string Username = null;
}

Step 2: Implementing our generic data mapper

We can now create our User_DataMapper class 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using System.Data;

namespace Coding.Exercise
{
    public class User_DataMapper
    {
    }
}

We then have to implement the IDataMapper class from above and specify the User class as its Type. 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using System.Data;

namespace Coding.Exercise
{
    public class User_DataMapper : IDataMapper<User>
    {
    }
}

Once done, right click on “IDataMapper<User>” and select “Implement Abstract Class”

You should now have a class that looks like this 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using System.Data;

namespace Coding.Exercise
{
    public class User_DataMapper : IDataMapper<User>
    {
        public override List<User> Select(out Exception exError)
        {
            throw new NotImplementedException();
        }

        public override bool Create(User instance, out Exception exError)
        {
            throw new NotImplementedException();
        }

        public override User Read(int ID, out Exception exError)
        {
            throw new NotImplementedException();
        }

        public override User Read(User instance, out Exception exError)
        {
            throw new NotImplementedException();
        }

        public override bool Update(User instance, out Exception exError)
        {
            throw new NotImplementedException();
        }

        public override bool Delete(int ID, out Exception exError)
        {
            throw new NotImplementedException();
        }

        public override bool Delete(User instance, out Exception exError)
        {
            throw new NotImplementedException();
        }
    }
}

Step 3: Implementing our methods

Let’s implement the first method Select.

public override List<User> Select(out Exception exError)
{
    List<User> returnValue = new List<User>();
    exError = null;

    try
    {
        if (this.Connection.State != ConnectionState.Open)
            this.Connection.Open();

        using (SqlCommand command = new SqlCommand("SELECT [UserID], [Username] FROM dbo.[User]", (SqlConnection)this.Connection))
        {
            using (SqlDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                    returnValue.Add(new User() { UserID = reader.GetInt32(0), Username = reader.GetString(1) });
            }
        }
    }
    catch (Exception ex)
    {
        exError = ex;
    }

    return returnValue;
}

As you can see I’m using a SqlDataReader here, but there are other ways of reading from a result set.

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.aspx

I have also wrapped the command and reader initialization in using statements. Using statements can be used on classes that implement IDisposable. What this means is that at the end of the using statement, the command and reader objects are properly closed and cleaned up.

Step 4: Using it!

The first thing we will need to do is create a User table to read. Using the SQL Server Management Studio, create a database and use the following SQL to create the table.

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[User]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[User](
    [UserID] [int] IDENTITY(1,1) NOT NULL,
    [Username] [nvarchar](100) NOT NULL,    
    CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED ( [UserID] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]
) ON [PRIMARY]
END
GO

Now all I have to do to get the list of users is use the following code.

Exception exError = null;

SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder.IntegratedSecurity = true;
builder.ConnectTimeout = 30;
builder.DataSource = @"SERVER\INSTANCE";
builder.InitialCatalog = "Database";

using( SqlConnection connection = new SqlConnection(builder.ConnectionString))
{
    User_DataMapper user_mapper = new User_DataMapper(connection);
    List<User> users = user_mapper.Select(out exError);

    if(exError != null)
    {
        //do something with the users list
    }
    else
        //Report and handle the exception
        MessageBox.Show("Unable to get list of users, " + exError.Message);
}

Conclusion

So there you have it, a simple typed class that you can customize very easily for any object. This method allows you to create consistent, readable and re-usable classes for database SCRUD actions.

What’s up next?

In this tutorial I used the IDbConnection interface, instead of the SqlConnection for the database connection. In the next tutorial I will show you how to take advantage of this using a Database Abstraction Layer class. This class will show you how to implement a Factory Pattern (http://en.wikipedia.org/wiki/Factory_method_pattern ) to automatically load either a SQL Server, MySql or Oracle database connection based on the connection string. 

/// <summary>
/// A .Net database connection (SQL Server, MySql, Oracle, etc.... )
/// </summary>
public IDbConnection Connection { get; private set; }

*A quick note

This project was created using Visual Studio 2012 and SQL Server Express
Visual Studio Express can be downloaded for free from http://www.microsoft.com/visualstudio/eng/downloads#d-2012-express and SQL Server Express can be downloaded for free from http://www.microsoft.com/en-us/sqlserver/editions/2012-editions/express.aspx

By Jonathan Schnittger
A battle hardened software developer with a mixed and colorful background. Can't come up with a decent author bio More articles by Jonathan Schnittger
DesignBombs

How to build your own theme for Grav CMS

By Angie Vella
Grav is an open source flat-file CMS platform, built by the RocketTheme Team. While there are plenty of great CMS platforms available, they are all mostly database-driven, which can be overkill for smaller websites. Instead of a database, Grav uses folders and a basic file structure, it is focused on speed, simplicity, and flexibility. After reading all of the documentation and spending some time trying Grav out, I’m definitely sold and will be using the platform. I hope you will too. What We’ll...
DesignBombs

2 Ways to Create the Holy Grail Layout with Flexbox

By DeveloperDrive Staff
Before the flexbox layout module was introduced, it had been a challenge to create the holy grail layout. We had to use all kinds of tweaks to achieve this kind of layout loved both by marketers and website owners, as it allows us to use two sidebars at the same time — one on the left and one on the right side. Thanks to flexbox, now we can build it in just a few lines of code. So, in this tutorial, we will look into how to create the holy grail layout with flexbox — in two different ways. First, we will use one huge...
DesignBombs

How to Create a JavaScript Chart with Chart.js

By DeveloperDrive Staff
If you have an information-rich website or application, you might want to visualize your data so that users can better make sense of it. Although tables can be quite useful in certain cases such as pricing and feature comparison tables, other kind of information is easier to process with the help of some kind of data visualization. JavaScript libraries such as Chart.js let you generate different types of HTML5 charts such as line, bar, pie, doughnut, and area charts. Chart.js is a versatile library...
DesignBombs

How to Create a Collapsing Header Effect with Pure CSS

By DeveloperDrive Staff
Collapsing headers are an excellent solution to show important information to users, such as special offers and key notifications. Many developers rely on JavaScript when they create such effects, however, it's perfectly possible to create a simpler collapsing header effect with pure CSS, too. Collapsing headers work similar to parallax effects. The background of the collapsing header stays fixed so that the content below it can flow on top of it when the user scrolls down the page. In this tutorial,...
CSS

How to Create a Responsive Image Gallery with Flexbox

By DeveloperDrive Staff
Flexbox is a CSS layout module that makes the creation of fully flexible user interfaces possible. It offers an easy-to-use alternative to floats and a couple of jQuery plugins such as image gallery libraries as well. Flexbox is an ideal choice for several typical CSS tasks and fits especially well with one-dimensional layouts. In this article, we will look into how to use flexbox to create a responsive image gallery that looks well at every viewport size. 1. Create the HTML ... First, let's create the...
CSS

A Step-by-Step Guide to Dark Mode in CSS

By DeveloperDrive Staff
Enabling dark mode in CSS allows website visitors to switch to an eye-friendly and resource-saving design whenever they want. There are a couple of UX patterns you can use to add a dark theme to your site. In this tutorial, we will show you how to add a simple jQuery toggle to the top of the page so that users can easily switch dark mode on and off. Our demo will be responsive as well, so it will look good on all device sizes, from smartphones to desktops to large screens. Here is how the light mode will finally look...
DesignBombs

25 must read HTML, CSS and jQuery books

By DeveloperDrive Staff
Everyone knows that in order to create the best websites you need to be up-to-date with the latest trends and developments. We asked experts and long timer designers and developers to share with us their favorite books, and the result is a selection of the very best books that everyone should read. Our list includes 25 books that all developers must read if they want to create great web sites. We cover everything from HTML to CSS, JavaScript and jQuery books. 1. Introducing HTML5 ...    ...
DesignBombs

How to Password Protect Content on Your WordPress Site

By Rafay Ansari
For a popular content management system like WordPress, security is of paramount importance. Millions of people use WordPress to share information with people all over the world. And since WordPress makes this so easy and gives you complete control over your content, there might be cases where you’d like to share your content with a select few people. Perhaps you want to share a wedding photography album with your guests or share selected articles with only your subscribers. Whether you need to...
Home CSS Deals DesignBombs HTML HTML5 JavaScript jQuery Miscellaneous Mobile MySQL News PHP Resources Security Snippet Tools Tutorial Web Development Web Services WordPress