Introduction
This article creates a Generic class which can be used to perform the basic CRUD operations in just a one line of code.
The pre-requirement for this article is that we need to have a LINQ to SQL generated classes, because the generic class has been designed in such
a way to perform opertion only with the LINQ to SQL generated classes.
Using the Code
A brief description of how to use the article or code. The class names, the methods and properties, any tricks or tips.
The pre-requisite for this generic class is that we should have the LINQ to SQL generated classes available.
The process to generate the LINQ to SQL classes can be found here.
We will assume all the classes in the LINQ to SQL classes as the entity and also that the name of the DataContext class created is TestDBContext.
So, we need to create a interface named IEntity having the generic method signatures available. The code for the interface is
public interface IEntity<T> where T : class
{
IList<T> List();
IList<T> List(int? page, int? pageSize, Expression<Func<T, bool>> predicate, Expression<Func<T, object>> sort);
void Add(T item);
T Get(Int64 Id);
void Update(T item);
bool Delete(T Item);
}
To create a generic class we need to implement this interface and name it as Entity.
Before creating this class we need to have some namespaces included in our class i.e.,
using System.Data.Linq;
using System.Reflection;
using System.Linq.Expressions;
The main problem raised during the creating of this generic class was that different tables in the database can have their primary key column defined with
different types of data types. For example, one Table might have its Primary Key defined as 'BIGINT' and another table might have defined its primary
key data type with 'VARCHAR'. So, to get the data from these tables using this generic class, we need to send the column name alongwith the data type of
the column. And my generic class will determine the property of the class as per the parameter defined and will return the result. The other operation on
of the table were easy and needed on 2-3 lines of code.
Code for the Entity class
public class Entity<T> : IEntity<T> where T : class
{
public virtual void Add(T item)
{
using (TestDBContext db = new TestDBContext())
{
db.GetTable<T>().InsertOnSubmit(item);
db.SubmitChanges();
}
}
public virtual IList<T> List()
{
using (TestDBContext db = new TestDBContext())
{
return db.GetTable<T>().ToList();
}
}
public virtual IList<T> List(int? page, int? pageSize, System.Linq.Expressions.Expression<Func<T, bool>> predicate, System.Linq.Expressions.Expression<Func<T, object>> sort)
{
var result = this.List().AsQueryable();
if (predicate != null)
result = result.Where(predicate);
if (sort != null)
result = result.OrderBy(sort);
if (page.HasValue && pageSize.HasValue)
result = result.Skip((page.Value - 1) * pageSize.Value).Take(pageSize.Value);
return result.ToList();
}
public virtual T Get(Int64 value)
{
return Get(typeof(System.Int64), "ID", value);
}
public virtual T Get(string value)
{
return Get(typeof(System.String), "ID", value);
}
public virtual T Get(Type propertyType, string propertyName, object propertyValue)
{
T result = null;
using (TestDBContext db = new TestDBContext())
{
IQueryable<T> queryableData = db.GetTable<T>().AsQueryable<T>();
if (queryableData != null)
{
ParameterExpression pe = Expression.Parameter(typeof(T), "entity");
Expression left = Expression.Property(pe, GetPropertyInfo(propertyName));
Expression right = Expression.Constant(propertyValue, propertyType);
Expression predicateBody = Expression.Equal(left, right);
MethodCallExpression whereCallExpression = Expression.Call(
typeof(Queryable),
"Where",
new Type[] { queryableData.ElementType },
queryableData.Expression,
Expression.Lambda<Func<T, bool>>(predicateBody, new ParameterExpression[] { pe }));
IQueryable<T> results = queryableData.Provider.CreateQuery<T>(whereCallExpression);
foreach (T item in results)
{
result = item;
break;
}
}
return result;
}
}
public virtual void Update(T item)
{
using (TestDBContext db = new TestDBContext())
{
db.GetTable<T>().Attach(item, true);
db.SubmitChanges();
}
}
public virtual bool Delete(T Item)
{
using (TestDBContext db = new TestDBContext())
{
db.GetTable<T>().DeleteOnSubmit(Item);
db.SubmitChanges();
return true;
}
}
private PropertyInfo GetPropertyInfo(string propertyName)
{
PropertyInfo[] properties = typeof(T).GetProperties();
PropertyInfo result = null;
foreach (PropertyInfo pi in properties)
{
if (pi.Name.Equals(propertyName))
{
result = pi;
break;
}
}
return result;
}
}
Example of calling the Entity class for CRUD operations:
NOTES obj = new NOTES();
obj.CATEGORY = 'First Category';
obj.SUBJECT = 'Subject of the Category';
obj.NODETEXT = 'Sample text of the Node';
Entity<NOTES> entity = new Entity</NOTES>();
entity.Add(obj);
Entity<NOTES> entity = new Entity<NOTES>();
NOTES note = entity.Get(typeof(System.Int64), "ID", 1);
....
Points of Interest
How to: Create LINQ to SQL Classes
How to: Use Expression Trees