Jump to content
alien

[C#] Phonebook using Linq to SQL

Recommended Posts

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

Share this post


Link to post
Share on other sites

Nu stiam de asta si chiar aveam nevoie de o baza de date pentru un program.O sa incerc sa il implementez si eu.Mersi.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...