How to: Creating a database using LINQ

Hey y’all!

It has been a long time but I finally found the time to post a short programming tutorial. I hope that this will be the first tutorial in a series but we will see.

What should you know?

Well first of all you should know what a database is and what it is used for.
You don’t have to know what LINQ is, I will explain this in a second but you should have some experience with C#.

What do you need?

  • You should have a SQL Server installed on your system. If you are using Visual Studio you should not have any trouble because it already installed SQL Server. I will be using SQL Server LocalDB in this tutorial.
  • You should have at least installed .NET Framework 3.5. This is the first .NET Framework where LINQ is included. I am using the .NET Framework 4.5.
  • I would recommend using Visual Studio for this tutorial (because I use it) but you can use whatever IDE you want.

What is LINQ?

LINQ stands for Language-Integrated Query. But what does this tell us?
Well, we use queries to get and filter data and maybe filter this data again. SQL does offer all this so why would I need LINQ?
To answer this question is easy: LINQ makes accessing data very, very uncomplicated. You can also use LINQ inside of C# to access collections or you could use LINQ to access XML files. You can nearly use it everywhere where it comes to data access.

So let’s have a look at the ordinary way of accessing a database C#/.NET:

[code language=”csharp”]
using (var connection = new SqlConnection(@"Data Source=(localDB)\MSSQLLocalDB;Initial Catalog=MyDataBase;Integrated Security=true")) {
connection.Open();
try {
using (var command = new SqlCommand("CREATE TABLE Users (FirstName TEXT, LastName TEXT, UserId INT)", connection )) {
command.ExecuteNonQuery();
}
} catch {
// command failed to execute
}
}
[/code]

Okay this seems pretty simple we create a SqlConnection and then open it. Then we create a SqlCommand to create a table in our database with the columns FirstName, LastName and UserId.

But the problem here is, that we have to create a new SqlCommand every time we want to send a new command. Also these commands can get pretty long if you have a more complex command. In addition they become unreadable and prone to error because it is basically a long string where you could easily forget a symbol or may misspell a word.

Nevertheless it is pretty simple from the design. You need a connection and then send commands. Accessing a database using LINQ needs a bit more work before you can start but pays off in the end.


 

Can you please start the tutorial?

What do I have to do beforehand?

So what you have to do before you access a database is to create the classes you want to use with LINQ.

Think of your database as a class that contains all information (tables) you want to access. So you have to define this class before using it. Here is an example:

[code language=”csharp”]
using System.Data.Linq;

public class MyDataContext : DataContext {

// the constructor of MyDataContext
public MyDataContext(string connectionString) : base(connectionString) {}
}
[/code]

As you may have noticed the class derives from DataContext. You always need to create a custom DataContext when you want to access or create a database. This DataContext is a class representation of your database. You can now add tables to this DataContext after you defined them in a separate class. What people sometimes get confused about is that you DO NOT define a table. You define a row and define in which table rows of this kind will be stored. So here is an example:

[code language=”csharp”]
using System.Data.Linq.Mapping;

[Table(Name = "Users")]
public class User {

// the user id
[Column(IsPrimaryKey = true)]
public int Id;

// the name
[Column(CanBeNull = false)]
public string UserName;

// the password
[Column(CanBeNull = false)]
public string UserPassword;
}
[/code]

This is the representation of a row in a table storing user data. What you have to remember at this point is that you use the LINQ flags before the class and properties. Let’s have a closer look at the several flags we used here.

  • This defines that the rows will be stored in a table called Users.

    [code language=”csharp”] [Table(Name = "Users")] [/code]

  • This flag is used to identify the following property as the primary key of the table. Note here that you have to set at least one property as a primary key.

    [code language=”csharp”][Column(IsPrimaryKey = true)][/code]

  • This flag is used to mark the following property as an non empty one. It as to be filled with data when creating or updating data resulting in an error if you try to insert empty data.

    [code language=”csharp”][Column(CanBeNull = false)][/code]

 

All we have to do now is to tell the DataContext we defined earlier to use these rows. Creating a table in a DataContext is as simple as creating a List<T> in a normal class. So here is the modified DataContext:

[code language=”csharp”]
using System.Data.Linq;

public class MyDataContext : DataContext {

// the table with users
public Table<User> Users;

// the constructor of MyDataContext
public MyDataContext(string connectionString) : base(connectionString) {}
}
[/code]

 

Using the DataContext

So now that we created all the info we need to access the database, let’s create it.

MyDataContext needs a connection string as parameter. So we first have to define this string when creating a new instance of MyDataContext. The connection string is the same as in the first example. So wherever you want to call the constructor of MyDataContext define the connection string before doing it.

[code language=”csharp”]
var connectionString = @"Data Source=(localDB)\MSSQLLocalDB;Initial Catalog=MyDataBase;Integrated Security=true";
var dataContext = new MyDataContext(connectionString);
[/code]

This will create a instance of MyDataContext BUT it will not create the database with the table. To create and use the database we have to do the following steps.

  • Check if the database already exists
  • If the database does not exist create it
  • Open a connection to the database

So here is the example:

[code language=”csharp”]
if(!dataContext.DatabaseExists()) {
dataContext.CreateDatabase();
}
dataContext.Connection.Open();
[/code]

That’s it. This will create a database with the name MyDataBase with a table Users.

 

IMPORTANT NOTE:

I found out that there can be problems when trying to access the database with the code provided above when you create the database because it didn’t exist before.The workaround for the problem is to create a second MyDataContext after you call dataContext.CreateDatabase(). Here is my code:

[code language=”csharp”]
var connectionString = @"Data Source=(localDB)\MSSQLLocalDB;Initial Catalog=MyDataBase;Integrated Security=true";
var dataContext = new MyDataContext(connectionString);

if(!dataContext.DatabaseExists()) {
dataContext.CreateDatabase();
}

var dataContext = new MyDataContext(connectionString); // this is the important line
dataContext.Connection.Open();
[/code]

For further reading about this issue please check here.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.