Getting started with LINQ to SQL in Visual C#.NET

Drawing QR Code in Visual C#.NET Getting started with LINQ to SQL

In most cases, we need to identify two things in a class: what table it is related to, and what columns the values are mapped to. Mapping the book table with the object is perhaps the simplest mapping possible. In this case, our database has a table called Book. Our object structure also represents a book instance with a class called Book. Thus we have a one-to-one mapping between both objects, and they are named the same. To declare the mapping, we add an attribute to the class declaration called Table as follows:

[Table] public class Book { }

If we want to be more explicit, we can declare the name of the source table by using a named parameter, Name, as follows:

[Table(Name="dbo.Book")] public class Book { }

Now that we ve mapped the class to the table, we need to indicate which properties are stored as columns in the table and how the columns map to the property information. We do this by adding a Column attribute to the properties we wish to map. For example, to map the Title property to the Title column of the book table, we add a Column attribute before the property declaration:

[Column] public String Title { get; set; }

We re not limited to direct mappings. We can specify some translation between the table column name and the object s property name. For example, our Book table has a column called PubDate. To make the business object easier for the client application developer to work with, we may wish to use a more verbose naming convention and name the property PublicationDate. To do this, we specify the name of the source column as part of the attribute s parameters.

[Column(Name="PubDate")] public DateTime PublicationDate { get; set; }

One thing we need to identify for each object is the primary key. In our case that will be the BookId property. Here, we combine the Name parameter with a new IsPrimaryKey parameter to declare the mapping. LINQ to SQL requires that at least one property from each object be specified as the primary key in order to manage object identity.

[Column(Name= ID , IsPrimaryKey=true)] public Guid BookId { get; set; }

We use the same method to declare the mappings for all of the properties in our class. The resulting declaration is shown in listing 6.4.

using System.Data.Linq;

[Table] public class Book Identity { column [Column(Name="ID", IsPrimaryKey=true)] public Guid BookId { get; set; } [Column] Standard public String Isbn { get; set; } column mapping [Column(CanBeNull=true)] public String Notes { get; set; } [Column] public Int32 PageCount { get; set; } [Column] public Decimal Price { get; set; } [Column(CanBeNull=true)] Specify the public String Summary { get; set; } column name [Column(Name="PubDate")] public DateTime PublicationDate { get; set; } [Column] public String Title { get; set; } [Column(Name="Subject")] public Guid SubjectId { get; set; } [Column(Name="Publisher")] public Guid PublisherId { get; set; } }

Although it may appear that we ve doubled the number of lines of code in our Book class, the net result will be drastically reduced code, as we will not need to worry about creating separate methods for the Create, Read, Update, and Delete (CRUD) methods. Additionally, we won t need a customized implementation for specialized querying operations. We declare the mappings once and the framework takes care of the rest. With the necessary changes made to our Book, you may be itching to see how to use LINQ to SQL to access our database. Although we ve specified how to access the tables and columns, we can t do anything until we identify the database that the tables live in. We need to set up our connection to the database. We do this by using a new DataContext object located in the System.Data.Linq namespace. Once that is done, rest assured, we will jump right in to querying our data.