Today one of my friend asked me about simple insert,update and delete example with LINQ-To-SQL but at that time i was not having any simple example which will show the power of LINQ-To-SQL Example. So i have decided to create one simple example with LINQ-To-SQL and also decide to blog about it. Let's create a simple example which is very simple and also show basic step to use LINQ-To-SQL. First i have created a simple table called which have 4 fields like ProductId,Name,Description and Price. Like following.
Here is the description for each fields
- ProductId- A id which uniquely identify each product.
- Name- Name of Product
- Description- A short description about product
- Price- Price of Product.
Now, Lets create a Linq-To-SQL and rename it as MyDataContent.dbml as following. For that Go to your project ->Right Click->Add New Item->Go to Data Template-> LINQ to SQL Classes like following.
Then open Database Explorer and drag and drop Product table on the newly generated LINQ-to-SQL Class like following.
Now our LINQ-SQL-Class is Ready. Now we will insert some data to our table and then first we will write code to get all product information from the database. I have created a function called
GetProduct which will print all the product information on page with the help of
Response.Write . Following is code for that.
public void GetProducts()
{
using (MyDataContentDataContext context = new MyDataContentDataContext())
{
var Products = from product in context.Products
orderby product.Name
select product;
foreach(Product p in Products)
{
Response.Write(string.Format("<B>Id</B>:{0}", p.ProductId.ToString()));
Response.Write(string.Format("<B>Name</B>:{0}", p.Name));
Response.Write(string.Format("<B>Description</B>:{0}", p.Description));
Response.Write(string.Format("<B>Price</B>:{0}", p.Price.ToString()));
Response.Write("===========================================");
}
}
}
Now Let's Create a function to Add Product. Following is code for function to add product.
public void AddProduct(Product product)
{
using (MyDataContentDataContext context = new MyDataContentDataContext())
{
context.Products.InsertOnSubmit(product);
context.SubmitChanges();
}
}
In the above function i am passing a new object of product and passing that object to above function following is a code for that which create a new product via calling above function.
//Add New Product
Product product = new Product();
product.Name = "Product3";
product.Description="This is product 3 Description";
product.Price=10;
AddProduct(product);
Now we have added the code to add product now lets create a function to update the current product information. Following is a code for that.
public void UpdateProduct(int productId, string name, string description, double price)
{
using (MyDataContentDataContext context = new MyDataContentDataContext())
{
Product currentProduct = context.Products.FirstOrDefault(p => p.ProductId == productId);
currentProduct.Name = name;
currentProduct.Description = description;
currentProduct.Price = price;
context.SubmitChanges();
}
}
With the help of above function you can update current product like following.
////Update Product
UpdateProduct(2,"New Product2","New Description for product",20);
Now we added the code for update product so now let's create a sample code to delete a specific product. Following is a code for that.
public void DeleteProduct(int productId)
{
using (MyDataContentDataContext context = new MyDataContentDataContext())
{
Product currentProduct = context.Products.FirstOrDefault(p => p.ProductId == productId);
context.Products.DeleteOnSubmit(currentProduct);
context.SubmitChanges();
}
}
You can delete the product via passing product id as following.
//Delete product
DeleteProduct(3);
So that's it. you can create insert,update,delete operation with the help of LINQ-To-SQL within some minutes without writing so much code for .net and queries for databases.
Happy Programming...