Using KnockoutJS with SignalR in ASP.NET MVC

KnockoutJS is a MVVM implementation for JavaScript written by Steve Sanderson, in my opinion the author of the best ASP.NET MVC textbooks available. Simply put it lets you bind a JavaScript object model to your HTML UI using a Read more

A MongoDB Tutorial using C# and ASP.NET MVC

In this post I'm going to create a simple ASP.NET MVC website for a simple blog that uses MongoDB and the offical 10gen C# driver. MongoDB is no NOSQL database that stores information as Binary JSON (BSON) in documents. I Read more

Linq To SQL Tutorial

Check out some of my other Linq to SQL posts: EntityBase Inheritance Modifiers with SQLMetal Linq to SQL with WCF Services Linq to SQL Framework (Repository/Business wrapper) ObjectDataSource binding with server side paging and sorting Load Options Generic Framework using reflection This is a basic tutorial for Read more

Linq to SQL Tutorial – Base Repository/Business Logic wrapper

Posted on by Joe in C#, Linq | 2 Comments

Before Linq to SQL I always separated out my Entities, Data Access Layer and Business Logic Layer.  With Linq to SQL it’s a little different as I feel the generated classes are kind of like the Entity and Data Access Layer combined.  Rather than using the generated entities directly it is still good practice to have a Business Logic Layer.  In this example I am using the term Repository which is an aspect of Domain-Driven Design.

Each entity will likely need it’s own repository object, as there will business logic that is specific for that entity, but there will be logic that is shared between all entities so it is worth creating a base object for this.

I have created a generic abstract class called RepositoryBase which has a constraint for EntityBase which is the base entity class I created in my Set inheritance modifiers with SQLMetalpost.  This ensures that the repositories can only be used with the objects created by Linq to SQL.

To handle my DataContext I am creating an instance of it in the constructor and keeping this instance for the life of the object.  For this reason my base repository implements IDisposable to handle disposing the DataContext.

Here is the code for my base repository:


public abstract class RepositoryBase<T> : IDisposable where T : EntityBase
{
    private UniversityDataContext _context;

    public RepositoryBase()
    {
        _context = new UniversityDataContext(ConfigurationManager.ConnectionStrings["UniversityConnectionString"].ConnectionString);
    }

    public UniversityDataContext Context
    {
        get
        {
            return _context;
        }
    }

    public List<T> GetAll()
    {
        return _context.GetTable<T>().ToList();
    }
    public T GetByID(int id)
    {
        return _context.GetTable<T>().FirstOrDefault(e => e.ID.Equals(id));
    }

    public int Count()
    {
        return _context.GetTable<T>().Count();
    }

    public void Save(T entity)
    {
        if (entity.IsNew)
        {
            _context.GetTable<T>().InsertOnSubmit(entity);
        }
        else
        {
            _context.GetTable<T>().Attach(entity, true);
        }

        _context.SubmitChanges();
    }

    public void Delete(T entity)
    {
        if (!entity.IsNew)
        {
            _context.GetTable<T>().Attach(entity);
            _context.GetTable<T>().DeleteOnSubmit(entity);
            _context.SubmitChanges();
        }
    }

    public void Dispose()
    {
        if (_context != null)
        {
            _context.Dispose();
        }
    }
}

You can see it contains methods that are relevant for all entities.  A method to get all entities, a method to get all entities by ID which I can do due to ID being a virtual member in my EntityBase class which is overridden by each entity.  It also has generic methods for saving and deleting which will work for any of the entities.  There will be a lot more logic that could go into this base class, but for now this just shows the basics.

Using this class you can derive separate repositories for each entity. I have a public property for the Context which allows any derived classes to use the DataContext instance.  Here is my implementation for StudentRepository which adds a couple of student specific methods:


public class StudentRepository : RepositoryBase<Student>
{
    public List<Student> GetByForename(string forename)
    {
        return Context.Students.Where(s => s.Forename.Equals(forename, StringComparison.CurrentCultureIgnoreCase)).ToList();
    }

    public List<Student> GetBySurname(string surname)
     {
        return Context.Students.Where(s => s.Surname.Equals(surname, StringComparison.CurrentCultureIgnoreCase)).ToList();
    }
}

Using this derived class I could use any of the base methods:

StudentRepository rep = new StudentRepository();
Student s = rep.GetByID(1);
s.Forename = "Joseph";
rep.Save(s);
gvStudents.DataSource = new StudentRepository().GetAll();
gvStudents.DataBind();

I could also use any of the entity specific methods:

StudentRepository rep = new StudentRepository();
List<Student> students = rep.GetByForename("Joseph");
foreach (Student s in students)
{
    rep.Delete(s);
}

To explicitly dispose of the object and the underlying DataContext I could do the following:

using (StudentRepository rep = new StudentRepository())
{
    List<Student> students = rep.GetByForename("Joseph");
    foreach (Student s in students)
    {
        rep.Delete(s);
    }
}

Linq To SQL Tutorial – Set inheritance modifiers with SQLMetal

Posted on by Joe in C#, Linq | 12 Comments

I have created an base class which my entities inherit from that gives a property called IsNew which returns true if the the value of ID is 0; all of my tables use ID for the identifier.

 

public abstract class EntityBase
{
    public virtual int ID { get; set; }

    public bool IsNew
    {
        get
        {
            return ID == 0;
        }
    }
}

For this to work each of my entities need to use the override modifier for the ID property.  Using the O/R Designer it is possible to set the inheritance modifier for each property, but this is not possible using SQLMetal and I need to use SQLMetal to set the base class.

To overcome this I have a batch file that first creates my .dbml file using SQLMetal:

SQLMetal.exe /server:localhost /database:University /dbml:University.dbml /namespace:Entities /context:UniversityDataContext /pluralize

I then run a console application to find column nodes with the name of ID, and add the Modifier attribute:

EntityProcessor.exe University.dbml

The code for EntityProcessor looks like this:

 

static void Main(string[] args)
{
    try
    {
        //Ensure there is an argument for the DBML file
        if (args.Count() == 0)
        {
            throw new ApplicationException("DBML path expected.");
        }

        string dbml = args[0];

        //Load the DBML file
        XmlDocument xmlDoc = new XmlDocument();
        xmlDoc.Load(dbml);

        //Loop through the tables
        foreach (XmlNode node in xmlDoc.GetElementsByTagName("Table"))
        {
            //Loop through the nodes for the type
            foreach (XmlNode child in node.FirstChild.ChildNodes)
            {
                //Find the ID column node
                if (child.Name.Equals("Column") && child.Attributes["Name"].Value.Equals("ID"))
                {
                    //Create the modifier attribute to add to ID column
                    XmlAttribute modifierAttribute = xmlDoc.CreateAttribute("Modifier");
                    modifierAttribute.Value = "Override";
                    child.Attributes.Append(modifierAttribute);
                }
            }
        }

        //Save the updated DBML file
        xmlDoc.Save(dbml);

        Console.WriteLine("Processing complete");
    }
    catch (Exception ex)
    {
        Console.WriteLine("An error occured: {0}", ex.Message);
    }
}

Finally I use SQLMetal again to create my entities from the updated .dbml file:

SQLMetal.exe /code:University.cs /entitybase:EntityBase /namespace:Entities University.dbml

My new entities now have an ID property with an overrides modifier that looks like this:

[Column(Storage="_ID", AutoSync=AutoSync.OnInsert, DbType="Int NOT NULL IDENTITY", IsPrimaryKey=true, IsDbGenerated=true)]
public override int ID
{
    get
    {
        return this._ID;
    }
    set
    {
        if ((this._ID != value))
        {
            this.OnIDChanging(value);
            this.SendPropertyChanging();
            this._ID = value;
            this.SendPropertyChanged("ID");
            this.OnIDChanged();
        }
    }
}

I can now call IsNew on any of my entities to check if they have been inserted into the database.

Linq To SQL Tutorial – EntityBase – Base class for entities

Posted on by Joe in C#, Linq | 3 Comments

When using Linq To SQL you may often find yourself in a position where you want each of your entities to inherit from a base class.  As generated classes are partial classes you could create a second partial class for each entity which inherits a base class, but this is impractical.  The O/R designer does not give you the option for a base entity class but it can be achieved using the SQLMetal command line tool.

SQLMetal allows you to create a .dbml file or a code file containing your entities.  SQLMetal Builder provides a simple user interface for the command line tool and also generates batch files.

I have created an abstract class called EntityBase which I want all of my generated entities to inherit. I can use the following arguments with SQLMetal in order to create my entities that inherit this class.

<br />
SQLMetal.exe /server:localhost /database:University /code:University.cs /namespace:Entities /context:UniversityDataContext /entitybase:EntityBase /pluralize<br />

SQLMetal will look at server localhost and the database University.  It will create the entities in an file called University.cs with the namespace Entities.  The DataContext will be called UniversityDataContext and all entities will inherit EntityBase.  I am also using the pluralize argument which plurlises the table names, for example Student become Students;  I find this is more intuative when writing queries.

Linq To SQL Tutorial

Posted on by Joe in C#, Linq | 27 Comments

Check out some of my other Linq to SQL posts:

This is a basic tutorial for using Linq To SQL introducing some of the concepts behind it.

For this tutorial I have created a simple database for a University.  I will probably use this database and expand on it for any future posts where a database is required.  Below you can see the structure of the database.

ERD

Create a new website called Universities and add a second project called DAL to the solution.  This is the Data Access Layer where we will create the Linq To SQL classes.

Universities Solution

Right-click on the DAL project and select Add > New Item.  From the dialog choose ‘LINQ to SQL Classes’ and call the file University.dbml.

New Linq to SQL file

The .dbml extension stands for Database Markup Language.  The file itself is essentially an XML file that describes the database which is used to generate our classes.  When you add your new dbml file the Object Relational Designer (O/R Designer) should launch which is a visual tool for creating your model.  There is also a command-line tool called SqlMetal which gives you further control over how your model is created.  In this instance we will use the O/R Designer.

Locate your database in the Server Explorer window (View > Server Explorer), and drag and drop the tables to use onto the surface of the O/R Designer.  The result should look like this:

Object Relational Designer

Save and close the the O/R desinger.  In solution explorer you should see the University.dbml file, expanding this shows two other files, University.dbml.layout and University.designer.cs.

Solution Explorer

Examine University.designer.cs and you will find your DataContext which inherits System.Data.Linq.DataContext, and your model classes which were generated from the dbml file.  These classes are all partial classes which allows you to easily build them out to add extra functionality which won’t be affected if you need to regenerate from the dbml.

At this point we are now ready to use the generated classes.  In the website create a reference to the DAL project, and to System.Data.Linq.

linqtosql_referencelinq

Create a simple form to add a new Student with a GridView to display the current students. It should look something like this:

Student Form

Firstly we want to bind the data from the Title table in the database to the Title DropDownList on the form.  Manually add some data to this table such as Mr, Mrs and Miss.  In the Page_Load event handler put a (!Page.IsPostBack) condition and call the following method:

private void PopulateTitles()
{
using (UniversityDataContext context = new UniversityDataContext())
{
ddlTitle.DataSource = context.Titles;
ddlTitle.DataTextField = "Name";
ddlTitle.DataValueField = "ID";
ddlTitle.DataBind();
}
}

This method first creates an instance of our DataContext to use.  We then set the DataSource of the DropDownList to context.Title which is of type System.Data.Linq.Table.  We can query this object further, but in this instace I want all rows from the table.  The DataTextField and DataValueField properties are set to the relevant properties of the Title object.  Run the application and the Title dropdown will be populated with the values from the database.

The next step is to create a new student and save it to the database.  Call the following method from the Save button event handler.


private void SaveStudent()
{
using (UniversityDataContext context = new UniversityDataContext())
{
Student newStudent = new Student()
{
TitleID = Convert.ToInt32(ddlTitle.SelectedValue),
Forename = txtForename.Text,
Surname = txtSurname.Text,
DOB = Convert.ToDateTime(txtDOB.Text),
EmailAddress = string.IsNullOrEmpty(txtEmail.Text) ? null : txtEmail.Text,
Phone = string.IsNullOrEmpty(txtPhone.Text) ? null : txtPhone.Text
};

context.Students.InsertOnSubmit(newStudent);
context.SubmitChanges();
}
}

We are creating a new Student object and setting the properties of that object based on the form values.  We are using an Object Initializer which allows setting the properties of the object without having to explicity envoke a constructor.  The penultimate line adds our newly created object to the table in a pending state, then calling the SubmitChanges method on the DataContext submits any pending changes in the context.

Now that there is data in our Student table it would be good to be able to see it.  Add the following method to our code and call it from the Page_Load event handler.


private void PopulateStudents()
{
using (UniversityDataContext context = new UniversityDataContext())
{
var students = from s in context.Students
select new
{
Title = s.Title.Name,
Forename = s.Forename,
Surname = s.Surname,
DOB = s.DOB.ToShortDateString(),
EmailAddress = s.EmailAddress,
Phone = s.Phone
};

gvStudents.DataSource = students;
gvStudents.DataBind();
}
}

This method uses a Linq query to assign an Anonymous Type using the var keyword.  We could bind context.Students to the GridView as we did with the Titles, but in this instance I want the Name of the Title for the student using context.Student.Title.Name and I also want to show the DOB without the time.  The Linq query is selecting all rows in the Students table, and creating a collection of the Anonymous Type setting each of its properties.  We then set the datasource of the GridView to this collection and call DataBind.

I hope this tutorial gives a good overview of Linq To SQL for anybody new to it.  In the real world you would probably not access your object model directly from the page, but write a repository model that handles all the data access and DataContext.  I will write more about how to do this at a later date.

Enum description using reflection and extension methods

Posted on by Joe in C# | Leave a comment

I don’t take credit for this as it was written by another developer on the the project, but I thought it was good and worth writing about.

On the current project I’m working on we use a lot of enumerations for statuses which are shown in drop-down lists and grids. It can get a bit ugly from a UI perspective when simply using the ToString method as enumeration values cannot have spaces and are usually shortened for ease of use when coding.

One way to overcome this is to add a description attribute to each enumeration value and write an extension method that uses reflection to obtain the value in the description attribute.

The description attribute is part of the System.ComponentModel namespace so we need to import this.  The attribute value can then be set on each enumeration value:

public enum Status : int
{
    [Description("Application pending")]
    Pending = 0,
    [Description("Application received")]
    Received = 1,
    [Description("Application processing in progress")]
    Processing = 2,
    [Description("Application processed")]
    Processed = 3
}

The next step is to create the extension method.  Extension methods were introduced in  C# 3 and allow you to easily add new methods to existing types.  Scott Guthrie has a good blog post on them here.  Extensions methods need to be static, and contained in a static class.  In the method signature the ‘this’ keyword is used to indicate which type the extension method is for.  Below is the extension method that will get the description attribute for an enumeration using reflection.

public static string Description(this Enum enumeration)
{
    string value = enumeration.ToString();
    Type type = enumeration.GetType();
    //Use reflection to try and get the description attribute for the enumeration
    DescriptionAttribute[] descAttribute = (DescriptionAttribute[])type.GetField(value).GetCustomAttributes(typeof(DescriptionAttribute), false);
    return descAttribute.Length > 0 ? descAttribute[0].Description : value;
}

Any enumeration will now have a method called Description that will try to return the value of the description attribute, and if the attribute is not present return the result of calling ToString.  For example:

Console.WriteLine(Status.Processing.Description());

 
Will display:

exmethod1

As the extension method is for Enum, and not just the Status enumeration I created, we could do this:

Array values = Enum.GetValues(typeof(Status));
foreach (Enum e in values)
    Console.WriteLine("{0} - {1}", e, e.Description());

The result here would be:

exmethod2

There are lots of uses for extension methods which are used extensively with Linq, but I particularly like this idea to provide a more readable value for an enumeration.