Beginning LinQ To SQL In C#.NET

Here, I will discuss a basic LinQ to SQL tutorial overview, then 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 ‘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 able to 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 doesn’t only applicable to manipulate database results, but it can also be used to manipulate array/list collections. LinQ was released as part of the .NET framework 3.0 and can be used from languages supported by the .NET framework like 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#.

Read All LinQ Tutorials By CodeSamplez.com

Mapping LINQ To SQL Class From SQL Server Database:

The first step to be able to use LinQ on our SQL database, we will need 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-part, 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 we will be using SQL query-like syntax right from the c# code for getting 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 much like SQL, but not the same. First, when you create a new DBML file with the name ‘DBName.dbml’, there is created a corresponding DataContext class in .net and 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 if your database table contains plural form like ‘users’, it automatically makes it as singular when creating the LINQ to SQL class like ‘User’, though table name used in the query will still be plural).

Next, notice one of the most useful advantages of LINQ, if you make any kind of data type mistake in your code, you will be notified immediately while compiling your project. This will saves lots of your debugging time and lessen the DB errors at the same time. it’s possible here as now all you are using are 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 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 to just cast that to the proper data type.

Next, “Enumerable.Count“, this function counts the number of rows(Or a number of objects) returned by the query. You can use this function on ‘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 for querying databases to retrieve data. However, there are alternative simple ways also to avoid using query-like syntax by using 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 have learned to execute a 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 easily. 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 very basic overview for getting started with LINQ To SQL. However, in a real application, you will be needed to know much more details. Here are some resource links where you will get some more useful 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 🙂