LINQ to SQL : CRUD operations and Stored procedures

The fundamentals operations that a storage system provides are the CRUD ones. CRUD stands for CREATE, READ, UPDATE, DELETE and corresponds to the SQL statements INSERT, SELECT, UPDATE and DELETE respectively. This post will show you how to query CRUD operations with LINQ to SQL and how to call a stored procedure defined in your SQL Server, as well. In a previous post we have seen how to create your entity model for your LINQ to SQL engine, in other words how to map your entity classes to your database tables. It is really important to know how to create and map your own entities but in most cases you might want to rely on particular tools which help you accomplish this task. Be aware that writing mapping information by hand can be time consuming and error-prone. In this post, we will use the SqlMetal.exe tool to generate the Northwind database entities. Then, we ‘ll use these entities in our project to query CRUD operations. Let’s start.

Create a new C# Console Application named CrudOperationsWithLinqSql in your Visual Studio. Right click your project and add a reference to the System.Data.Linq assembly. It’s time to create out entities now. Open a terminal and navigate to the folder where the SqlMetal.exe file exists. In my pc, it’s under the

C:\Program Files (x86)\Microsoft SDKs\Windows\v8.0A\bin\NETFX 4.0 Tools

folder. Type the following code to generate a .dbml file which will contain our entities. This file will have all mapping information for the Northwind database tables in the Microsoft SQL Server.

>SqlMetal.exe /server:localhost /database:Northwind /dbml:C:\SqlMetal\DAL\Northwind.dbml /namespace:Northwind.DAL /Context:NorthWindDataContext /provider:SQL2008 /pluralize

linqtosql2_1

You may have to change your server name and the location C:\SqlMetal\DAL I used to store the new file. The file will contain a NorthWindDataContext class derived from the System.Data.Linq.DataContext class under a NorthWind.DAL namespace. Copy this file and paste it in your project (or add it as existing item..). That’s it, you have all your entities mapped to all your database tables in just a second. Open the Northwind.designer.cs file and see that not only your entities are mapped to your tables, but all relationships information have been created too.

linqtosql2_2

Add a Connection class and define your connection string for your NorthwindDataContext class, in the Program.cs file. Make sure you change your Data Source respectively.

namespace CrudOperationsWithLinqSql
{
    public static class Connections
    {
        // Connection with SQL Server 2012
        public const string ConnectionString = "Data Source=developer-pc; Initial Catalog=Northwind;Integrated Security=True";

    }

    class Program
    {
        static void Main(string[] args)
        {
        }
    }
}

Let’s start creating our CRUD operations. Add a new C# class file named “Queries” in your project. Add references to the System.Data.Linq and the NorthWind.DAL namespaces. Add a CreateCustomer function like this.

using System.Data.Linq;
using NorthWind.DAL;

namespace CrudOperationsWithLinqSql
{
    public class Queries
    {
        public static void CreateCustomer(string customerID, string companyName, string contactName,
            string contactTitle, string address, string city, string region, string postalCode,
            string country, string phone, string fax)
        {
            NorthWindDataContext db = new NorthWindDataContext(Connections.ConnectionString);
            var customer = new Customer
            {
                CustomerID = customerID,
                CompanyName = companyName,
                ContactName = contactName,
                ContactTitle = contactTitle,
                Address = address,
                City = city,
                Region = region,
                PostalCode = postalCode,
                Country = country,
                Phone = phone, 
                Fax = fax
            };
            db.Customers.InsertOnSubmit(customer);
            db.SubmitChanges();
        }
    }
}

You need to call the DataContext.SubmitChanges() function, otherwise the sql query won’t be sent to the database. Call this method in your main method in the Program class and create a new Customer record in the Northwind database. Go and validate that indeed this record was added to the Customers table.

class Program
    {
        static void Main(string[] args)
        {
            Queries.CreateCustomer("CHRST", "Microsoft", "Christos", "Mr.", "Athens",
                "Athens", "SP", "1234", "Greece", "1234567890", "1234567890");
        }
    }

linqtosql2_3

In the same way create another function in the Queries class for creating new Orders. Later, we will create three Orders assigning them to the previous customer using the CustomerID Order’s foreign key.

public static void CreateOrder(string customerID, int employeeID, DateTime orderDate,
            DateTime requiredDate, DateTime shippedDate, int shipVia, decimal freight, string shipName,
            string shipAddress, string shipCity, string shipRegion, string shipPostalCode, string shipCountry)
        {
            NorthWindDataContext db = new NorthWindDataContext(Connections.ConnectionString);
            var order = new Order
            {
                CustomerID = customerID,
                EmployeeID = employeeID,
                OrderDate = orderDate,
                RequiredDate = requiredDate,
                ShippedDate = shippedDate,
                ShipVia = shipVia,
                Freight = freight,
                ShipName = shipName,
                ShipAddress = shipAddress,
                ShipCity = shipCity,
                ShipRegion = shipRegion,
                ShipPostalCode = shipPostalCode,
                ShipCountry = shipCountry,
            };
            db.Orders.InsertOnSubmit(order);
            db.SubmitChanges();
        }
static void Main(string[] args)
        {
            //Queries.CreateCustomer("CHRST", "Microsoft", "Christos", "Mr.", "Athens",
            //    "Athens", "SP", "1234", "Greece", "1234567890", "1234567890");
            Queries.CreateOrder("CHRST", 4, DateTime.Now, DateTime.Now, DateTime.Now,
                3, 20m, "Chris", "Athens", "Athens", "GR", "2352", "Greece");
            
        }

Either call three times the CreateOrder method or run the above code three times in a row to create some orders for the Customer with CustomerID=”CHRST”.

linqtosql2_4

Nice, now let’s try to delete our Customer with “CustomerID=CHRST”. Create a new method as follow in the Queries class, and call it from your main method passing “CHRST” as a parameter. What do you see?

public static void DeleteCustomer(string customerID)
        {
            NorthWindDataContext db = new NorthWindDataContext(Connections.ConnectionString);
            Table<Customer> customers = db.GetTable<Customer>();

            var custToDelete = customers.Where(c => c.CustomerID == customerID).Single();
            customers.DeleteOnSubmit(custToDelete);

            db.SubmitChanges();
        }

linqtosql2_5

We got an Sql.Exception violating a Reference key constraint. It’s normal though, considering the fact that this customer is associated with three orders. These orders have a foreign key CustomerID pointing to the customer we want to delete. So.. what can we do to resolve this? We need to call the DeleteAllOnSubmit function on the Orders table, passing as a parameter our customer’s orders. Change the above DeleteCustomer function’s code like this.

public static void DeleteCustomer(string customerID)
        {
            NorthWindDataContext db = new NorthWindDataContext(Connections.ConnectionString);
            Table<Customer> customers = db.GetTable<Customer>();

            var custToDelete = customers.Where(c => c.CustomerID == customerID).Single();
            customers.DeleteOnSubmit(custToDelete);
            db.Orders.DeleteAllOnSubmit(custToDelete.Orders);

            db.SubmitChanges();
        }

Now both your customer record and it’s associated orders has been deleted. If you had created OrderDetails records for each of the above orders, you would have to call a DeleteAllOnSubmit(order.OrderDetails) for each of those orders (in a foreach loop for example).

Updating records is kind of easy. You simply retrieve your record with a where clause, change the field you wish and then call the SubmitChanges(). I won’t show you such an example, but instead I will show you an advanced one. Have you ever considered changing a primary’s key value? It would probably through an exception, since all associated child records would crush, pointing to a non existing primary key. The solution, is to create a new record, pass all the old values except from the new primary key, assign all relationships to the new record, add it to the Context and then delete the old one. Finally, you can call the SubmitChanges. Let’s see the example, where we are going to change a Customer record with CustomerID “BLAUS” to “BLASS”. As you can see this customer has made 7 orders.

linqtosql2_6

public static void ChangeCustomerPrimaryKey(string oldCustID, string newCustID)
        {
            NorthWindDataContext db = new NorthWindDataContext(Connections.ConnectionString);
            Table<Customer> customers = db.GetTable<Customer>();

            var oldCustomer = customers.Single(c => c.CustomerID == oldCustID);
            var newCustorer = new Customer
            {
                CustomerID = newCustID,
                Address = oldCustomer.Address,
                City = oldCustomer.City,
                CompanyName = oldCustomer.CompanyName,
                ContactName = oldCustomer.ContactName,
                ContactTitle = oldCustomer.ContactTitle,
                Country = oldCustomer.Country,
                Fax = oldCustomer.Fax,
                Orders = oldCustomer.Orders,
                Phone = oldCustomer.Phone,
                PostalCode = oldCustomer.PostalCode,
                Region = oldCustomer.Region
            };
            customers.InsertOnSubmit(newCustorer);
            customers.DeleteOnSubmit(oldCustomer);
            db.SubmitChanges();
        }

Run this command in your main method and check the result in your database.

 Queries.ChangeCustomerPrimaryKey("BLAUS", "BLASS");

linqtosql2_7

Now let’s create and call a Stored Procedure. There is a stored procedure named Customers By City.

linqtosql2_8

Notice that this procedure takes as a parameter the City and returns some customer data but not a Customer record. We will use a new class named CustomerDetails to store those values. You decorate your function that points to a stored procedure with a Function attribute. This attribute has a Name parameter which is the stored procedure’s name and a IsComposable argument, which when it is True the method wraps a stored procedure, otherwise wraps a User Defined Function (UDF). The stored procedure is called through the DataContext.ExecuteMethodCall, hence we need to create our stored procedure inside the NorthWindDataContext class which is under the NorthWind.designer.cs file (generated by the SqlMetal.exe tool). Create the CustomerDetails class above the NorthWindDataContext class.

    public class CustomerDetails
    {
        public string CustomerID;
        public string CompanyName;
        public string City;
        public string ContactName;
    }
	
	
	[global::System.Data.Linq.Mapping.DatabaseAttribute(Name="Northwind")]
	[global::System.Data.Linq.Mapping.ProviderAttribute(typeof(System.Data.Linq.SqlClient.Sql2008Provider))]
	public partial class NorthWindDataContext : System.Data.Linq.DataContext
	{
		
		private static System.Data.Linq.Mapping.MappingSource mappingSource = new AttributeMappingSource();
        // Code omitted 

Add the stored procedure in the NorthWindDataContext class as follow.

public partial class NorthWindDataContext : System.Data.Linq.DataContext
	{

        [Function(Name = "Customers by City", IsComposable = false)]
        public ISingleResult<CustomerDetails> CustomersByCity(string param1)
        {
            IExecuteResult executeResult = this.ExecuteMethodCall(this,
                (MethodInfo)(MethodInfo.GetCurrentMethod()), // Get Stored Procedure Name
                 param1);    // Stored Procedure Parameter
            ISingleResult<CustomerDetails> result = (ISingleResult<CustomerDetails>)executeResult.ReturnValue;
            return result;
        }
        // Code omitted

Now create a method to call the stored procedure, inside the Queries class.

public static void CustomersByCity(string city)
        {
            NorthWindDataContext db = new NorthWindDataContext(Connections.ConnectionString);

            foreach(var item in db.CustomersByCity(city))
                Console.WriteLine("Customer ID: " + item.CustomerID +
                    "Name: " + item.ContactName + "Company: " + item.CompanyName + 
                    "City: " + item.City);
        }

Call it from your main method passing “London” as the parameter’s value.

Queries.CustomersByCity("London");

linqtosql2_9

That’s it, we have seen lot’s of interesting things in this post, how to automate the Entity model creation with the SqlMetal.exe tool, how to query CRUD operations with LINQ to SQL and finally how to call a Stored Procedure. I hope you enjoyed the post.

Advertisements


Categories: ADO.NET

Tags: , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

mohitgoyal.co

Automating infrastructure one line at a time

Diary Of A Programmer

Because every day is worth noting

Chara Plessa

The purpose of this blog is to broaden my education, promote experimentation and enhance my professional development. Albert Einstein once said that “If you can’t explain it simply, you don’t understand it well enough” and I strongly believe him!

chsakell's Blog

Anything around ASP.NET MVC,WEB API, WCF, Entity Framework & AngularJS

Kumikoro

A Front End Developer's Blog

Muhammad Hassan

Full Stack Developer | ASP.NET | MVC | WebAPI | Advanced Javascript | AngularJS | Angular2 | C# | ES6 | SQL | TypeScript | HTML5 | NodeJS, MS candidate @LUMS, Grad & EX-Adjunct Faculty @NUCES-FAST, seasonal blogger & open-source contributor. Seattle, WA.

Software Engineering

Web development

IEvangelist

.NET, ASP.NET, C#, MVC, TypeScript, AngularJS

leastprivilege.com

Dominick Baier on Identity & Access Control

Happy DotNetting

In Love with Technology

Knoldus

Knols of experience to your advantage

knowshnet

Search - Read - Request - Share

Rahul's space

Learn, Share and Grow with me !

Dhananjay Kumar

Developer Evangelist @Infragistics | MVP @Microsoft

SQL Authority with Pinal Dave

SQL Server Performance Tuning Expert

Conficient Blog

Random bits of tech from @conficient

Code! Code! Code!

SOLID & KISS

Code Wala

Designing and coding

Microsoft Mentalist

A way to start with Microsoft Technologies

%d bloggers like this: