Insert and Delete with DLinq

In my last entry, I showed how to fetched record from SQL server using LINQ. I did some more work on LINQ and was able to insert a new record and delete particular record using LINQ. In order to insert or delete, I needed to start Distributed Transaction Coordinator in my SQL server. Below is the simple code use to insert a record,

Authors aut = new Authors();

aut.AuId = id;
aut.AuFname = fName;
aut.AuLname = lName;
aut.Phone = phone;

db.Authors.Add(aut);
db.SubmitChanges();

Continuing the work I left last time, I made an object of Authors and added ID, first name, last name and phone to it. Then calling the SubmitChanges method, the actual data is written to database. Similarly, deleting a record is also not so hard.

var authors = (
    from a in db.Authors
    where a.AuId == id
    orderby a.AuFname
    select a).First();

db.Authors.Remove(authors);
db.SubmitChanges();

In order to delete a record, First method is called with the query, which returns a particular record. By passing this record to the Remove method of Authors object, a record will be deleted. With this, record deletion is only limited to memory data. Actual record will be deleted only after calling SubmitChanges method. The next step is to explore transaction in LINQ.

Linq and DLinq in action

Beta version of Visual C# 2005 Express Edition was lying around my hard disk for some times. I was stuck with a project and didn't have chance to look into it. When I visited LINQ site, I found the way to use it. I've downloaded Tech Preview version of LINQ and installed it. Integration with Visual C# 2005 was not so hard and I was already in new Linq consol application window. I found couple of doc files on 'Hands on Lab' inside 'Docs' folder. After reading a file for DLinq, I got general idea on how actually LINQ works. In order to start with DLinq, I first made a ObjectModel of 'Pubs' database using a tool 'sqlmetal.exe'. This tool is installed in Bin folder and able to create a C# source file which contains mapping of every table and column to respective objects. The resulting source was then included in Linq console application. With little modification in ObjectModel, I was able to use SqlConnection object with my 'pubs' class. By writing a code (as shown below), I was able to see the result of database immediately. The bottom line is, with LINQ, there is no need to write SQL. The query language is now integrated in programming language. I think it is easy to use and developer doesn't need to have knowledge of SQL. Till now, I have learnt to get the result from database. Now my target is to learn how to insert, update and delete records using LINQ.

using System;
using System.Collections .Generic;
using System.Text;
using System.Query;
using System.Xml.XLinq;
using System.Data.DLinq;
using System.Data.SqlClient;
using pubs;

namespace LINQApp
{
    class Program
    {
        static void Main(string[] args)
        {
            dbQuery();
        }

        static void dbQuery()
        {
            SqlConnection conn = 
                     new SqlConnection(@"SERVER={YourDBServer};user id={uid};password=;database=pubs;");
           
            Pubs db = new Pubs(conn);

             var authors = 
                 from  a in db.Authors
                 select a;

            foreach (var auth in authors)
            {
                Console.WriteLine(" {0} {1} | {2} | {3} ", 
                                       auth.AuFname, auth.AuLname, auth.Address, auth.Phone);
            }
           
            Console.ReadLine();
        }
    }
}