Programming, Investing and FI

Linq To SQL

Probably what saved me from getting bored creating SQL stored procedures/statements. Linq suggests doing all of that stuff in a fraction of the time while staying in C#. All of this and still being backward compatible with these good old stored procedures!

The goal of Linq To SQL is to create an interface independent from the database engine that the application will be using. This will greatly simplify the work and the data interactions as well. Here is how it goes!

1. Setup the Environment

The first step in using Linq To SQL is to create a real SQL database. Assuming you have SQL Management Studio installed and a local implementation of Microsoft SQL Server (here is how to install the tools). I will create that super uncommon (hint: sarcasm!) Employees database with two tables including one relationship between an employee and the devices he owns in the business.

  • Employees – contains the list of employees.
  • Devices – contains devices owned by the employees, for example a monitor, a Blackberry, a PC, and so on.

Below is the final schema of the database. You can download the generated script and execute it on your server to recreate the same database I am using.

CREATE DATABASE [Inventory]
GO
USE [Inventory]
GO
CREATE TABLE [dbo].[Devices](
	[DeviceID] [int] IDENTITY(1,1) NOT NULL,
	[EmployeeID] [int] NOT NULL,
	[IdentificationNumber] [varchar](50) NOT NULL,
	[Brand] [varchar](50) NOT NULL,
	[Model] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Devices] PRIMARY KEY CLUSTERED 
(
	[DeviceID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Employees](
	[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
	[EmployeeNumber] [varchar](10) NOT NULL,
	[FirstName] [varchar](50) NOT NULL,
	[LastName] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED 
(
	[EmployeeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Devices]  WITH CHECK ADD  CONSTRAINT [FK_Devices_Employees] FOREIGN KEY([EmployeeID])
REFERENCES [dbo].[Employees] ([EmployeeID])
GO
ALTER TABLE [dbo].[Devices] CHECK CONSTRAINT [FK_Devices_Employees]
GO

Here is the final result on a diagram to make it a bit more visual.

Inventory Database Diagram

2. Create New Project in Visual Studio

The next step is to create a new project in Visual Studio. I’ve decided to simply create a console project with the .Net Framework.

LinqToSQL new Project

3. Add a Linq to SQL Data Context

This part is easy as well, right-click on your project or on a folder of your choice and add a new Linq To SQL Data Classes like shown below.

Add Data Context

You might have noticed that I’ve put the Data Context in two folders, the first one will create a namespace named DataAccess because I want to classify the Linq To SQL context as Data related. The second one is DataObjects. This is because Linq To SQL will create objects based on your database tables and stored procedures. These objects will have to be used individually by my data link files that I will create in the DataAccess folder. You will understand in a moment.

4. Link the Database to the Data Context

To create the data objects based on your database, you have to create a new connection and then drag and drop the tables on the big white rectangle. The one on the right is for your stored procedures and the like.

Click on the Server Explorer tab. If you can’t see it, you can find it in the View menu item at the top. Then right-click on Data Connections and Add Connection…

Server Explorer

You should see the following screen appear, you want to select Microsoft SQL Server and then click on Continue.

Microsoft SQL Server

Enter your database server informations in the next window and test your connection.

SQL Server Connection test

You can use either the sa account or the Windows Authentication if the server is on your own computer (or if you’ve allowed access to the computer you’re using to connect). Windows Authentication is generally considered the best practice.

You will notice that I am connecting to my first server called EIN in my network, but you will most probably have to enter localhost instead if you’ve installed SQL Server on your current computer. Remember that you can enter an IP address in there as well.

Once you’ve selected the right database in the last drop down list, click on Test Connection to make sure it really works and click OK.

5. Create Linq To SQL Objects

We’re almost done with the setup, this is the last step. You have to go into the Server Explorer tab and select the tables you want to add. Then, drag them on the larger white rectangle in you Data Context. Linq To SQL will then create the objects for you.

You will most probably be prompted this window if you’re using SQL Authentication instead of Windows Authentication. You can simply click yes but it’s still better to use Windows Authentication.

Clear Text Credentials

If you’re curious and using another computer/virtual machine as your SQL Server (without a domain), you can easily add Windows Authentication by creating an account with the same name/password as the one you’re currently using. Then give permission to this account on the database and by modifying the Membership properties to enable data_reader and data_writer.

Now if you drag and drop all your tables from the Server Explorer onto the white space, it should show something like that.

DataContext Linq To SQL

How to Access the Data?

Accessing the data is fairly easy, you have to follow these two steps :

  1. Create a DataContext object;
  2. Execute a Linq query on the DataContext object.

I suggest you use the same method as I am because I’ve found it way easier to organize things that way. DataLinks objects will be stored under the DataAccess folder and correspond to either a specific table or whatever makes sense to your data.

The Data Objects are all under the DataObjects folder as the Linq To SQL .dbml file is located there. Again, the reason why I am doing that is because the DataContext will create multiple objects based on the tables you will add to it. Therefore, it is logical to put your Context in this folder.

Linq To SQL Folder Structure

Here are a few examples of standard CRUD (Create, Read, Update, Delete) operations. They will be part of the DevicesDataLink.cs.

DevicesDataLink.cs

using System;
using System.Collections.Generic;
using System.Linq;

using LinqToSQL.DataAccess.DataObjects;

namespace LinqToSQL.DataAccess
{
    /// 
    /// Not thread safe DataLink
    /// 
    public class DevicesDataLink
    {
        InventoryDataContext _InventoryDataContext;

        public DevicesDataLink()
        {
            _InventoryDataContext = new InventoryDataContext();
        }

        /// 
        /// Retrieves a device with a specific IdentificationNumber.
        /// 
        /// 
        /// The serial number of the device.
        /// 
        /// The device with the provided IndentificationNumber.
        public Device Retrieve(string IdentificationNumber)
        {
            if (!Exists(IdentificationNumber))
                throw new ArgumentException("The device entered alread exists.", "DeviceToAdd");

            Device databaseObject = _InventoryDataContext.Devices.Single(device => device.IdentificationNumber == IdentificationNumber);
            return databaseObject;
        }

        /// 
        /// Retrieves all the devices.
        /// 
        /// 
        /// An IEnumerable containing all the devices in the database.
        public IEnumerable RetrieveAll()
        {
            IEnumerable devices = _InventoryDataContext.Devices;

            return devices;
        }

        /// 
        /// Adds a device to the database.
        /// 
        /// 
        /// The device to add.
        public void Add(Device DeviceToAdd)
        {
            if (Exists(DeviceToAdd.IdentificationNumber))
                throw new ArgumentException("The device entered alread exists.", "DeviceToAdd");

            _InventoryDataContext.Devices.InsertOnSubmit(DeviceToAdd);
            _InventoryDataContext.SubmitChanges();
        }

        /// 
        /// Updates a device in the database.
        /// 
        /// 
        /// The device to update.
        public void Update(Device DeviceToUpdate)
        {
            if (!Exists(DeviceToUpdate.DeviceID))
                throw new ArgumentException("The device entered does not exists.", "DeviceToUpdate");

            Device databaseObject = _InventoryDataContext.Devices.Single(device => device.DeviceID == DeviceToUpdate.DeviceID);

            databaseObject.EmployeeID = DeviceToUpdate.EmployeeID;
            databaseObject.IdentificationNumber = DeviceToUpdate.IdentificationNumber;
            databaseObject.Brand = DeviceToUpdate.Brand;
            databaseObject.Model = DeviceToUpdate.Model;

            _InventoryDataContext.SubmitChanges();
        }

        /// 
        /// Verifies the existence of a device according to a specific DeviceID.
        /// 
        /// 
        /// The DeviceID.
        /// 
        /// True if the device with the specified DeviceID exists, False otherwise.
        public bool Exists(int DeviceID)
        {
            if (_InventoryDataContext.Devices.Any(device => device.DeviceID == DeviceID))
                return true;
            return false;
        }

        /// 
        /// Verifies the existence of a device according to a specific IdentificationNumber.
        /// 
        /// 
        /// The IdentificationNumber.
        /// 
        /// True if the device with the specified IdentificationNumber exists, False otherwise.
        public bool Exists(string IdentificationNumber)
        {
            if (_InventoryDataContext.Devices.Any(device => device.IdentificationNumber == IdentificationNumber))
                return true;
            return false;
        }
    }
}

This code is intended to give you an idea of how quick and painless using Linq is to complete small CRUD (Create, Read, Update, Delete) operations. With a minimal amount of code I can do basic data manipulation to get me started and run some tests.

Note that the update method is a bit tricky as it involves a reference to the object in the database. That is, you retrieve the object using the Single method and then modify the parameters. Finally, you SubmitChanges to apply the changes to the data in the database.

An exist method is also convenient as it can check, using the Any Linq method, if there is already an object with certain criteria. You will notice that I am using two methods with the same name, this is called method overloading.

using System.Linq;

using LinqToSQL.DataAccess;
using LinqToSQL.DataAccess.DataObjects;

namespace LinqToSQL
{
class Program
{
static void Main(string[] args)
{
//Without the DataLink
Employee NewEmployee = new Employee();
NewEmployee.EmployeeNumber = "9830324";
NewEmployee.FirstName = "Alex";
NewEmployee.LastName = "Test";

//Create new data context
InventoryDataContext DataContext = new InventoryDataContext();

//Insert the employee
DataContext.Employees.InsertOnSubmit(NewEmployee);

//Submit the changes
DataContext.SubmitChanges();

//------------------------------------------------
//Retrieve Employee
Employee InsertedEmployee = DataContext.Employees.Single(employee => employee.EmployeeNumber == "9830324");

//With the DataLink
DevicesDataLink DevicesDataLink = new DevicesDataLink();

//Create new Device
Device NewDevice = new Device();
NewDevice.EmployeeID = InsertedEmployee.EmployeeID;
NewDevice.IdentificationNumber = "FSDWE43ASDBBYT76";
NewDevice.Brand = "Blackberry";
NewDevice.Model = "Priv";

//Add new device using our DataLink wrapper object
DevicesDataLink.Add(NewDevice);

Device AddedDevice = DevicesDataLink.Retrieve("FSDWE43ASDBBYT76");
}
}
}

</pre

The last piece of code shows the difference between standard DataContext insertion versus the DataLink wrapper insertion (Add method). Using the DataLink is in my opinion a better option as it really separates the data access from your business logic code. If you use the DataContext directly, you have no way to standardize the access to your data. You might want to go even further and add a parent to all your DataLink objects.

Linq Queries

The Linq queries is a more advanced topic that will be discussed in another post. In the meantime, if you want more information on the subject and would like to start coding with Linq To SQL right away, here is a link to the reference from Microsoft.

Linq To SQL can really help you get your database connection and operations done quickly! As shown above, it's a very powerful tool that should not be underestimated besides its big brother, Linq To Entities. In fact, we've barely touched the surface of what Linq To SQL can do and I hope this introduction helps you get started with this great technology!

Next article Compost, Recycling and the Like!
Previous article Speculation and Crypto-currencies

Related posts