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