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
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.
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");
}
}
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”.
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();
}
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.
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");
Now let’s create and call a Stored Procedure. There is a stored procedure named Customers By City.
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");
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.
Categories: ADO.NET
chsakell's Blog 
Leave a comment