Beginning Linq to Sql in C#.NET

In this tutorial, I will discuss about basic linq to sql overview, then how to get started very quickly with its usage on our C#.NET based applications(This can be used in both desktop and web applications in the exactly same way). Besides, I will also discuss about using 'select' query in linq to retrieve data and also traverse through the query results(in case of multiple result objects). To understand this tutorial properly, You should have handy knowledge on c# and also have visual studio 2008+(.NET Framework 3.0+ ) to successfully able to run LINQ To SQL examples.

What Is LINQ To SQL?

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 manipulates array/list collections. LinQ was released as part of the .NET framework 3.0 and can be used from languages supported by .NET framework like C#, VB etc. The term 'LINQ To SQL' refers to the technology by which we can use LINQ for access SQL Databases. Here 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:

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 successfully, 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 designer interface.

The designer interface has two part, one for dragging tables from server explorer(to create classes from tables automatically), 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, its very simple to get started our actually implementation in c# code. 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 database. Here is a sample codes of a function for such 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;
}

You can see, the syntax is much like sql, but not exactly same though. First, when you create a new dbml file with 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 treat 'u' as a object of User class, that actually represents database's 'users' table(you will might 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 advantage 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. its possible here as now all you are using are acting like a .NET class, so its 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 assign to a variable of type 'var'. This data type is also new from .NET framework 3.0 and 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 appropriate data type.
Next, "Enumerable.Count", this function count the number of rows(Or number of objects) returned by the query. You can use this function on 'var' type result object without need of casting 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 database for retrieve data. However, there is alternative simple ways also for avoid using query like syntax by using 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;
}

Retrieve A Single Row With LinQ:

On 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 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;
}

The above example will return a single record from 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:

In case, when LinQ query returns multiple results, we often need to traverse through all the result rows(here all LinQ objects) and process in some way. It can be done very easily with a foreach loop. Although for loop also can be used, however foreach is much better in performance(to know details, here is 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
     }
}

thanks your

Shibashish mohanty