Beginning LinQ To SQL In C#.NET

Here, I will discuss a beginner’s LinQ to SQL tutorial overview, then explain how to get started very quickly with its usage on our C#.NET-based applications(This can be used in desktop and web applications in the same way). Besides, I will also discuss using the ‘select’ query in LINQ to retrieve data and traverse through the query results(in case of multiple result objects). To understand this tutorial properly, You should have handy knowledge of c# and have Visual Studio 2008+(.NET Framework 3.0+ ) to successfully run LINQ To SQL examples.

What Is LINQ To SQL?

The full meaning of LINQ is ‘Language Integrated Query’, which replaces the traditional SQL query execution process. Moreover, it is not only applicable to manipulating database results but can also be used to manipulate array/list collections. LinQ was released as part of the .NET framework 3.0 and can be used in languages supported by the .NET framework, such as C#, VB, etc. The term ‘LINQ To SQL’ refers to the technology by which we can use LINQ to access SQL Databases. In this tutorial, I will show step-by-step ways to get started with LINQ To SQL programming with C#.

Mapping LINQ To SQL Class From SQL Server Database:

The first step to be able to use LinQ on our SQL database is to define a way by which .NET can recognize the database as Classes/Objects, so we will need to map the database tables/stored procedures to LinQ to SQL classes. To accomplish this task, first, open your project in the solution explorer, right-click->add->new item, in the ‘data’ categories, there is a type named ‘LINQ To SQL Classes’. Select that. We will get a .dbml file created along with the designer interface.

The designer interface has two parts: one for dragging tables from server Explorer (to create classes from tables automatically), and another is for methods where we can drag stored procedures, etc. After dragging all classes from Server Explorer, we are done. Here is a sample DB structure that we will be using on the way of this tutorial:

Select Data Using LinQ To SQL:

After we make the DBML files appropriately, it’s very simple to get started with our actual implementation in c# code. The most interesting thing is that we will be using SQL query-like syntax right from the c# code to get database results. Suppose we are trying to validate a user against a given username/password from the database. Here is a sample code of a function for such a purpose:

public bool IsValidUser(string userName, string passWord)
{
DBNameDataContext myDB = new DBNameDataContext();
var userResults = from u in myDB.Users
                         where u.Username == userName
                         && u.Password == passWord
                         select u;
return Enumerable.Count(userResults) > 0;
}Code language: JavaScript (javascript)

The syntax is similar to SQL, but not the same. First, when you create a new DBML file with the name ‘DBName.dbml’, a corresponding DataContext class in .net is created, named something like ‘DBNameDataContext’ (These DataContext classes are now responsible for .NET to Database communications). In the LINQ query syntax, this object will be used to present the database.

Next, whenever we write something like “from u in myDB.Users,” Visual Studio automatically treats ‘u’ as an object of the User class that represents the database’s ‘users’ table(you will also notice that if your database table contains a plural form like ‘users’, it automatically makes it singular when creating the LINQ to SQL class like ‘User’, though the table name used in the query will still be plural).

Next, notice one of the most valuable advantages of LINQ: if you make any data type mistake in your code, you will be notified immediately while compiling your project. This will save lots of your debugging time and lessen the DB errors. It’s possible here as now all you are using is acting like a .NET class, so it’s simply validating the property’s data type. Of course, you will have to remember that if you change your DB structure/column data type, etc, later on, you should have to drag the tables from server Explorer to DBML once again to reflect your DB changes to the LINQ to SQL classes.

Next, note that the result is assigned to a variable of type ‘var’. This data type is also new to .NET Framework 3.0 and is used to represent data with dynamic types. That means, here, any kind of data returned from the LINQ query will be assigned to that variable, and you will have just to cast that to the proper data type.

Next, the “Enumerable.Count” function counts the number of rows(Or a number of objects) returned by the query. You can use this function on a ‘var’ type result object without the need to cast it to any intermediate form.

Select Operation Without SQL Syntax in LinQ:

The above example showed how to use LinQ To SQL syntax to query databases to retrieve data. However, there are alternative simple ways to avoid using query-like syntax, such as the integrated ‘Where’ method. Here is a simple code example to accomplish that:

public bool IsValidUser(string userName, string passWord)
{
       DBNameDataContext myDB = new DBNameDataContext();
       List<User> users = myDB.Users.Where(u => u.Username == userName && u.Password==passWord);
       if(users.Count>0)
       {
             return true;
       }
       return false;
}Code language: PHP (php)

Retrieve A Single Row With LinQ:

In the above example, we learned how to execute an SQL-like statement. However, LINQ provides much more flexibility than that. Like, if you need a single item/row from the database table, it can be done very quickly. Here is a code sample for such cases:

public User GetUser(string userName)
{
     DBNameDataContext myDB = new DBNameDataContext();
     User user = myDB.Users.Single(u, u.UserName=>userName);
     return user;
}Code language: PHP (php)

The above example will return a single record from the database table. In the “u.UserName=>userName” part, you can mention any column name you want to be validated.

‘Foreach’ Loop Through All LinQ To SQL Returned Results:

When the LinQ query returns multiple results, we often need to traverse through all the result rows(here, all LinQ objects) and process them in some way. It can be done very easily with a foreach loop. Although for loop also can be used, however, “foreach” is better in performance(to know details, you can refer to my for vs foreach article). Here are the code samples for using the foreach loop for traversing through all result objects:

foreach(User user in userResults)
{
    //checking the result as like object
    if(user.Role == 'admin')
     {
         //do whatever you need
     }
}Code language: PHP (php)

References:

I have tried to give you a fundamental overview for getting started with LINQ To SQL. However, in an actual application, you will need to know many more details. Here are some resource links where you will get some more valuable resources to enrich your knowledge base within LINQ:

• Official MSDN Documentation on LINQ
• 101 LinQ Examples
• Blog article on LinQ at weblogs.asp.net

I will try to post more articles about some more advanced LinQ to SQL tutorials. I wish you a very good journey with LINQ programming. Happy coding 🙂