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 – 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.