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("Id:{0}", p.ProductId.ToString()));Response.Write(string.Format("Name:{0}", p.Name));Response.Write(string.Format("Description:{0}", p.Description));Response.Write(string.Format("Price:{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 ProductProduct 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 productDeleteProduct(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…