Search the Community

This tutorial will demonstrate the basics of using LINQ to SQL in C#. What is LINQ Suppose you are writing an application using .NET. Chances are high that at some point you’ll need to persist objects to a database, query the database, and load the results back into objects. The problem is that in most cases, at least with relational databases, there is a gap between your programming language and the database. Good attempts have been made to provide object-oriented databases, which would be closer to object-oriented platforms and imperative programming languages such as C# and VB.NET. However, after all these years, relational databases are still pervasive, and you still have to struggle with data access and persistence in all of your programs. The original motivation behind LINQ was to address the conceptual and technical difficulties encountered when using databases with .NET programming languages. With LINQ, Microsoft’s intention was to provide a solution for the problem of object-relational mapping, as well as to simplify the interaction between objects and data sources. LINQ eventually evolved into a general-purpose language-integrated querying toolset. This toolset can be used to access data coming from in-memory objects (LINQ to Objects), databases (LINQ to SQL), XML documents (LINQ to XML), a file-system, or any other source. So now that we know what LINK is, lets see it in action. We will be implementing a very basic Phonebook. Step 1 - Defining the database You can use agenda.sql file to create the database automatically. Create the two tables in you're SQL server. Persons CREATE TABLE [dbo].[Persons]( [PersonID] [int] NOT NULL, [Name] [nvarchar](30), [Address] [nvarchar](100) Phones CREATE TABLE [dbo].[Phones]( [PhoneID] [int] NOT NULL, [PersonID] [int] NOT NULL, [Number] [char](15), [Description] [nvarchar](50) Step 2 - LINQ to SQL Now we need to link the two tables from the database to our LINQ map(DBML). Open Agenda_Linq.dbml file, delete any tables there and using the Server Explorer from VS drag and drop the two tables from the database in the DBML designer. Now we need to create an association between the two tables: right click-->add-->association. Select Parent class - Persons and Child class and link the files PersonID in the two tables. Now the linking to your database is done. Step 3 - Use LINQ to work with the database Select all persons from Persons table /// <summary> /// Gets all persons. /// </summary> /// <returns></returns> public IEnumerable<Person> GetAllPersons() { Agenda_LinqDataContext agenda = new Agenda_LinqDataContext(); DataLoadOptions option = new DataLoadOptions(); option.LoadWith<Person>(person => person.Phones); agenda.LoadOptions = option; var query = from person in agenda.Persons orderby person.Name select person; var listOfPersons = query.ToList(); agenda.Dispose(); return listOfPersons; } Add/Delete Person internal void AddNewPerson(Person p) { Agenda_LinqDataContext agenda = new Agenda_LinqDataContext(); agenda.Persons.InsertOnSubmit(p); agenda.SubmitChanges(); agenda.Dispose(); } internal void DeletePerson(Person selectedPerson) { Agenda_LinqDataContext agenda = new Agenda_LinqDataContext(); agenda.Persons.Attach(selectedPerson); agenda.Persons.DeleteOnSubmit(selectedPerson); agenda.SubmitChanges(); agenda.Dispose(); } Add/Delete Phone internal void AddPhone(Phone p) { Agenda_LinqDataContext agenda = new Agenda_LinqDataContext(); agenda.Phones.InsertOnSubmit(p); agenda.SubmitChanges(); agenda.Dispose(); } internal void DeletePhone(Phone phone) { Agenda_LinqDataContext agenda = new Agenda_LinqDataContext(); agenda.Phones.Attach(phone); agenda.Phones.DeleteOnSubmit(phone); agenda.SubmitChanges(); agenda.Dispose(); } You get the picture. As you can see the database is transformed in C# classes by the DBML and you are using now those classes instead of the actual database. Full source code here: hg clone https://bitbucket.org/rokill3r/linqagenda Alien