Linq to sql mapping using attributes

Problem:

What if you need access relational database using Linq to Sql? Using Linq to Sql you can provide operations on data stored in Sql Server databases. Linq to Sql uses Data Context to perform the required operations and it can work both in connected and disconnected mode. Now how to map the result of queries onto classes?

Impact:

There are two ways in which you can map the result of queries on the classes.

Firstly using attributes and second using xml

Solution:

We here need to accomplish our task using attributes.

Firstly create a attributes for the class

//Here Customer is name of the Table in Database to mapped to
//CustomerEntity Class and Table attribute defines mapping.

[Table(Name = "Customer")]

public class CustomerEntity

{

    //CustomerID field of Customer Table needs to be mapped to
    //ID member-field of CustomerEntity Class and similarly
    //for other fields.

       

    [Column(Name="CustomerID")]

    public int ID;

    [Column(Name="CustomerName")]

    public string Name;

    [Column(Name="CustomerAddress")]

    public string Address;

    [Column(Name="CustomerDetails")]

    public string Details;

   

    //Note- here if you are mapping members-fields of class with
    //same name as fields of Table in database then you don’t
    //need Name value with Column attribute
    //just mention [Column]

   

}

Then to access data use the following code.

public class Tester

{

    static void Main(string[] args)

    {

        //First create the connection string to your database

        string connString = @"server =yourSqlServername;

                            integrated security = true;

                            database = myDatabaseName";

       

        //Next step is to create datacontext that will help us
        //in all the communication process.
        //Don’t forget to pass the connection 

       //string to datacontext.

        DataContext db = new DataContext(connString);

       

        //Get the data from table. Here GetTable gets the
        //collection of type CustomerEntity which is actually
        //mapped to our Customer Table in DB and we store the
        //returned collection in table of type class

        Table<CustomerEntity> cust =
                        db.GetTable<CustomerEntity>();

        //Now we are using Linq to access data from customer
        //table based on our requirement.Here I am grabbing
        //all customers orderby name whose

        //ID is greater than 5

        

        var custs_query = from c in cust

                          where c.ID > 5

                          orderby c.Name

                          select c;

        //Now the final step to print result of query

        foreach (var cq in custs_query)

            Console.WriteLine("{0}, {1}, {2}, {3}",

               cq.ID,

               cq.Name,

               cq.Address,

               cq.Details );