CodeSamplez.com

Programming, Web development, Cloud Technologies

  • Facebook
  • Google+
  • RSS
  • Twitter
  • Home
  • Featured
    • C# Tutorials
      • LinQ Tutorials
      • Facebook C# API Tutorials
    • PHP Tutorials
      • CodeIgniter Tutorials
    • Amazon AWS Tutorials
  • Categories
    • Programming
    • Development
    • Database
    • Web Server
    • Source Control
    • Management
    • Project
  • About
  • Write
  • Contact
Home Database Beginning LinQ To SQL In C#.NET

Beginning LinQ To SQL In C#.NET

Rana Ahsan January 25, 2011 38 Comments


 Beginning LinQ To SQL In C#.NET    

Here, I will discuss about 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 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 traverse through the query results(in case of multiple result objects). To understand this tutorial properly, You should have handy knowledge on c# and 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#.


Read All LinQ Tutorials By CodeSamplez.com

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:

Linq To Sql Class In DBML Design

Select Data Using LinQ To SQL:

After we make the DBML files appropriately, it’s 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 an 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 assigned 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 proper 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 better in performance(to know details, you can refer to my for vs foreach article). 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
     }
}

References:

I have tried to give you a very basic overview for getting started with LINQ To SQL. However, in 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 with in 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. Wish you a very good journey with LINQ programming. Happy coding 🙂

Related

Filed Under: Database Tagged With: .net, c#, linq

About Rana Ahsan

Rana is a passionate software engineer/Technology Enthusiast.
Github: ranacseruet

Comments

  1. Chloé says

    February 8, 2011 at 3:38 pm

    Thank you for the short tutorial. It’s very helpful.

    Reply
  2. Given says

    June 13, 2011 at 5:29 am

    Keep up the good work! very good tutorial, i help me a lot

    Reply
  3. Clerio Hickmann says

    August 20, 2011 at 5:42 am

    Rana
    Very clear and simple, very helpful. Thank you !

    Reply
  4. Hanumantha says

    October 15, 2011 at 1:12 am

    Really good attempt….. It hepl a lot,.

    Reply
  5. Linda says

    November 9, 2011 at 7:37 am

    I would like to use a sql view to get the data instead of a table in c#. How I can do it? Thanks.

    Reply
    • Rana says

      November 19, 2011 at 8:11 am

      I didn’t get your point. Can you please explain a little more?

      Reply
    • Johannes says

      December 21, 2011 at 4:05 am

      Simply drag the view onto the (left part of the) designer instead of the table. That should do the trick.

      br johannes

      Reply
  6. sriniwaas41 says

    January 4, 2012 at 3:10 am

    excellent descrption

    Reply
  7. Jayesh Goswami says

    January 18, 2012 at 4:44 am

    i need to use sql tables and sp’s witout adding it in SqltoClasses. It is possible using only dbContext.

    Reply
  8. HassanBukhari says

    January 31, 2012 at 12:10 am

    Excellent

    Reply
  9. Shailendra says

    February 18, 2012 at 5:51 am

    Really helpful ! It helps me a lot to getting basic of Linq. Thanks again

    Reply
  10. Parveen Rathi says

    February 28, 2012 at 4:00 am

    Hello sir, you have not mention where i have write the above code after add table in linq

    Reply
    • Rana says

      March 2, 2012 at 4:12 am

      Wherever you add it, doesn’t matter, as long as you have its namespace added to your .cs file you are writing.

      Reply
  11. Ehsan Kayani says

    August 12, 2013 at 5:52 pm

    Very nice i wanted to lean this for a long time now. 🙂

    Reply
  12. Lera says

    February 15, 2014 at 10:58 am

    Thank you so much!

    Reply
  13. Steven says

    May 19, 2014 at 9:00 pm

    http://www.codeducky.org/sql-queries-in-linq/ is a post that explains how to convert common SQL queires, (e.g. queries with HAVING clauses, WHERE IN clause, CASE statement, etc.) into LINQ. It may be useful for developers who are learning LINQ.

    Reply
  14. Snehal says

    July 5, 2014 at 4:55 am

    Nice Articles..

    Reply
  15. suraj says

    August 1, 2014 at 9:31 pm

    very nice

    Reply
  16. Shahrukh Naeem says

    August 24, 2014 at 3:44 pm

    Please tell me how to insert a new record in database using linq to sql ????

    Reply
    • Md Ali Ahsan Rana says

      August 24, 2014 at 10:57 pm

      You will may like to checkout my other linq crud operations tutorial for that. Hope that helps.

      Reply
  17. N Khan says

    September 29, 2014 at 5:27 am

    Great Bro,

    Thanks and keep it up.

    Reply
  18. Michael says

    October 4, 2014 at 1:46 am

    Tried to learn LINQ a while ago but didn’t make sense but I am doing again and thanks to your article its all sinking in nicely. Thank you

    Reply
  19. Marv says

    November 13, 2014 at 11:54 pm

    You examples have made clear to me concepts that so far have appeared very complex and mysterious. Thank You for excellent articles.

    Reply
  20. Ko Soe says

    December 28, 2014 at 11:08 am

    excellent article. Thank you so much!

    Reply
  21. sats says

    April 7, 2015 at 1:38 pm

    Simply Great and nice…

    Reply
  22. Mahendra says

    April 19, 2015 at 1:44 am

    Hi,
    Will you please tell the significance of the code generated in the dbml file,
    Reply

    Reply
  23. Antonio says

    April 30, 2015 at 4:36 pm

    Excellent very easy thank you just I need !

    Reply
  24. Yakob Ubaidi says

    June 10, 2015 at 11:08 am

    what is that u. object ?

    Reply
  25. abhijeet nagargoje says

    September 7, 2015 at 2:05 am

    very nice

    Reply
  26. Dogar says

    September 11, 2015 at 10:02 am

    Nice One keep it up man (y)

    Reply
  27. Mahesh says

    October 7, 2015 at 7:09 am

    Good Article

    Reply
  28. Peter Krassoi says

    November 5, 2015 at 7:56 am

    How long does it take to forget the
    “if (condition)
    return true;
    return false;”
    programming structure?
    The “return condition;” is simplier and satisties the “one function-one return” criterion.

    Reply
  29. K Verma says

    January 20, 2016 at 12:41 am

    Precise and easy to understand…
    Hope this will become better with lot of more examples.
    Thank you .. really a good start…

    Reply
  30. Chalenge Mee says

    September 22, 2016 at 7:32 pm

    Excellent article. Explained very well. It is the first article that shows you how to use Linq. But there is a trade-off. Can anyone explain why Linq is better than just sql?

    Reply

Trackbacks

  1. Update Database Using LinQ To SQL In C# | codesamplez.com says:
    February 2, 2011 at 11:21 pm

    […] Database Update Operations In C# Recently, I have discussed about getting started linq to sql in c#.net. That articles also covered for creating select query for retrieving both single and […]

    Reply
  2. LinQ To SQL JOIN Operation In C# | codesamplez.com says:
    March 30, 2011 at 11:46 pm

    […] Using SQL JOIN Operation In LinQ/C# Posted on March 30, 2011 Being LinQ an Object Relational Mapper, We can avoid writing sql query in many cases. Where its needed to be written even for simple purpose, we can achieve what we need just by calling linq provided methods with proper parameters.This facilitates to make the code more meaningful and less buggy. Join operations are done in sql for retrieve data from a complex relationship between tables, using linq we can avoid this complexity in most of the cases, therefore no more brainstorming for writing big/complex queries anymore. In this tutorial, I will give some examples along with code samples for performing join operations with help of linq to sql easily and quickly. As this article focus completely on join operation, if you are a beginner linq programmer, consider my previous tutorial on getting started with linq to sql. […]

    Reply
  3. Linq To XML Tutorial | codesamplez.com says:
    October 5, 2011 at 1:08 am

    […] articles, I have described about working with linq on sql server database, which usually known as 'Linq To SQL'. Today I am going to show some code example , how linq can be applied on XML data. This is know as […]

    Reply
  4. Asp.net MVC 3 And Linq To SQL Based Tutorial | codesamplez.com says:
    December 20, 2012 at 3:27 am

    […] with asp.net mvc and razor template engine. Also, if you are new to linq, please consider reading linq to sql tutorial . We will be using Asp.NET MVC 3 and .NET Framework 4 for this sample […]

    Reply

Leave a Reply Cancel reply

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

Email Subscription

Never miss any programming tutorial again.

Popular Tutorials

  • How To Work With JSON In Node.js / JavaScript
  • Generate HTTP Requests using c#
  • PHP HTML5 Video Streaming Tutorial
  • How To Work With C# Serial Port Communication
  • Facebook C# API Tutorials
  • LinQ To SQL Database Update Operations In C#
  • Utilizing Config File In C#.NET Application
  • LinQ Query With Like Operator
  • Get Facebook C# Api Access Token
  • Control HTML5 Audio With Jquery

Recent Tutorials

  • Building Auth With JWT – Part 1
  • Document Your REST API Like A Pro
  • Understanding Golang Error Handling
  • Web Application Case Studies You Must Read
  • Getting Started With Golang Unit Testing
  • Getting Started With Big Data Analytics Pipeline
  • NodeJS Tips And Tricks For Beginners
  • Apple Push Notification Backend In NodeJS
  • Web Based Universal Language Translator, Voice/Text Messaging App
  • How To Dockerize A Multi-Container App From Scratch

Recent Comments

  • intolap on PHP HTML5 Video Streaming Tutorial
  • manishpanchal on PHP HTML5 Video Streaming Tutorial
  • Rana Ghosh on PHP HTML5 Video Streaming Tutorial
  • ld13 on Pipe Email To PHP And Parse Content
  • Daniel on PHP HTML5 Video Streaming Tutorial

Archives

Resources

  • CodeSamplez.com Demo

Tags

.net apache api audio aws c# cache cloud server codeigniter deployment doctrine facebook git github golang htaccess html5 http image java javascript linq mysql nodejs oop performance php phpmyadmin plugin process python regular expression scalability server smarty ssh tfs thread tips ubuntu unit-test utility web application wordpress wpf

Copyright © 2010 - 2021 · CodeSamplez.com ·

Copyright © 2021 · Streamline Pro Theme on Genesis Framework · WordPress · Log in