Linq to SQL - Part II

In previous post we discussed how you can create DBML file and then use Linq to create SQL like queries to select the data from the database. In this post we will use the same project but see how you can use Linq to update, add and delete a record.

I have created a console app have methods for Select, Update, Add and Delete. Each method uses Linq queries to perform select, update, add and delete functions.

Finally, in Delete method, because my Customers table has a foreign key relationship with Customers_Audit table, I must first delete the record from the Customers_Audit table before deleting from the Customers table.

Let's review an example.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
namespace LinqToSQL
{
    class Program
    {
        static void Main(string[] args)
        {
            // DataContext takes a connection string 
            linqToSQLDataContext db = new linqToSQLDataContext();

            //Select all customers and print them out
            SelectCustomers(db);
            //Update a customer's last name
            UpdateCustomers(db);
            //Add a new customer. 
            //I have a trigger on Customers table which also inserts the new record into 
           //Customers_Audit table
            AddCustomers(db);
            //Delete a Customer
            //Since this table has a foreign key relationship with another table, 
            //I must delete record form both tables
            DeleteCustomer(db);

            Console.Read();

        }
        static void SelectCustomers(linqToSQLDataContext db)
        {
            var customerQuery = from d in db.Customers
                                orderby d.LastName
                                select d;
            foreach (var cust in customerQuery)
            {
                Console.WriteLine("id = {0}, FirstName = {1},LastName = {2}", 
                cust.CustomerID, cust.FirstName, cust.LastName);
            }

            Console.WriteLine(Environment.NewLine);

            //getting data from multiple tables
            var CustomersAndAddresses = from c in db.Customers
                                        from o in db.CustomerAddresses
                                        where c.CustomerID == o.CustomerID
                                        where c.CustomerID == 1
                                        select new { c.FirstName, c.LastName, o.Address1, 
                                                     o.Address2, o.City, o.State };

            foreach (var cust in CustomersAndAddresses)
            {
                Console.WriteLine("Name={0}, Address = {1},City = {2}", 
                cust.FirstName + ' ' + cust.LastName, cust.Address1 + ' ' + 
                cust.Address2, cust.City);
            }
        }
        /// <summary>
        /// Update existing Customers
        /// </summary>
        static void UpdateCustomers(linqToSQLDataContext db)
        {
            var customerQuery = from d in db.Customers
                                orderby d.LastName where d.LastName == "Smith"
                                select d;
            foreach (var cust in customerQuery)
            {
                cust.LastName = "Smithsonian";

            }
            db.SubmitChanges();
            Console.WriteLine("Last Name Updated");
         }
        /// <summary>
        /// Insert new Customers
        /// </summary>
        static void AddCustomers(linqToSQLDataContext db)
        {
            Customer newCustomer = new Customer();

            newCustomer.FirstName="Sonny";
            newCustomer.LastName="Hendrix";
            newCustomer.EmailAddress = "sHendrix@mail.com";
            db.Customers.InsertOnSubmit(newCustomer);
            db.SubmitChanges();
                   
            db.SubmitChanges();
            Console.WriteLine("New Customer Inserted");
        }
        /// <summary>
        /// Delete existing Customer
        /// </summary>
        static void DeleteCustomer(linqToSQLDataContext db)
        {
            var customerAuditQuery = from a in db.Customers_Audits
                                orderby a.LastName
                                where a.LastName == "Hendrix"
                                select a; 
            var customerQuery = from d in db.Customers
                                orderby d.LastName
                                where d.LastName == "Hendrix"
                                select d;
            db.Customers_Audits.DeleteAllOnSubmit(customerAuditQuery);
            db.Customers.DeleteAllOnSubmit(customerQuery);
            db.SubmitChanges();
            Console.WriteLine("Hendrix Deleted");
        }
    }
}

As you can see, it is fairly straightforward and works great for simple applications. It does get rather cumbersome in some cases and doesn't work in others as Linq to SQL only supports 1 to 1 mapping of tables, views etc. Entity framework is more suited for larger, complex applications. To create a DBML file to make your application aware of the underlying database schema, check out my previous post.

Thank you