.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Articles >> LINQ >> Post New Resource Bookmark and Share   

 Subscribe to Articles

CRUD operations using LINQ Entities

Posted By:Shivprasad koirala       Posted Date: July 13, 2009    Points: 25    Category: LINQ    URL: http://www.questpond.com  
 

CRUD operations using LINQ Entities

Introduction and Goal

Still new to LINQ below are some real quick starters

LINQ In-memory commits and physical commits

Step 1 :- Create the entity customer class

Step 2:- Create using LINQ

Create data context

Set the data for insert

Do an in-memory update

Do the final physical commit

The final create LINQ code

Step 3:- Update using LINQ

Create data Context

Select the customer LINQ object which we want to update

Finally set new values and update data to physical database

The final code of LINQ update

Step 4:- Delete using LINQ

DeleteOnSubmit

Step 5 :- Self explanatory LINQ select and read

The LINQ CRUD code
 

Introduction and Goal
 

This is a pretty simple tutorial dedicated to LINQ newbie's who want to learn how to do CRUD operations using LINQ entities. I am sure many experienced LINQ players would criticize me for such a mild article. One of the biggest catch which I found is the in-memory update service by LINQ, which I think for any newbie for LINQ is a must to understand.

So we will start with an understanding of the differences between in-memory updates and physical commits and then we will deep in to the code for each database operation. Again this article is one of those small sprints which I need to run so that I can complete the huge LINQ FAQ project.

Watch my 500 videos dedicated to Microsoft technologies @ http://www.questpond.com
 


Screen shot of what we are going to talk about
 

Still new to LINQ below are some real quick starters
 

Are you a complete newbie http://www.dotnetspark.com/kb/665-linq-faq-for-newbies.aspx  

Want to define 1-* and *-1 using LINQ http://www.dotnetspark.com/kb/659-one-many-and-one-one-relationship-using.aspx  


Do not know how to call stored procedures using LINQ http://www.dotnetspark.com/kb/663-simple-6-steps-to-use-stored-procedure-linq.aspx  
 

LINQ In-memory commits and physical commits
 

Entity objects forms the base of LINQ technologies. So when any data is submitted to database it goes through the LINQ objects. Database operations are done through 'DataContext' class. As said previously entities form the base of LINQ, so all the data is sent to these entities first and then its routed to the actual physical database. Due to this nature of working database commits is a two step process, the first step is in-memory and final step is physical commits.
In order to do in-memory operation 'DataContext' has provided 'DeleteOnSubmit' and 'InsertOnSubmit' methods. When we call these methods from the 'DataContext' class they add and update data in the entity objects memory. Please note these methods do not change / add new data in the actual database.
Once we are done with the in-memory operations and we want to send all the updates to the database for that we need to call 'SubmitChanges()' method. This method finally commits data in to the physical database.
 

So let's consider a customer table (customerid, customercode and customername) and see how we can do the in-memory and physical commit operations.
 

Step 1 :- Create the entity customer class
 

So as a first step we create the entity of customer class as shown in the below code snippet.


[Table(Name = "Customer")]
public class clsCustomerEntity
{
private int _CustomerId;
private string _CustomerCode;
private string _CustomerName;

[Column(DbType = "nvarchar(50)")]
public string CustomerCode
{
set
{
_CustomerCode = value;
}
get
{
return _CustomerCode;
}
}

[Column(DbType = "nvarchar(50)")]
public string CustomerName
{
set
{
_CustomerName = value;
}
get
{
return _CustomerName;
}
}

[Column(DbType = "int", IsPrimaryKey = true,IsDbGenerated=true)]
public int CustomerId
{
set
{
_CustomerId = value;
}
get
{
return _CustomerId;
}
}
}

Step 2:- Create using LINQ
 

Create data context
 

So the first thing is to create a 'datacontext' object using the connection string.
 

DataContext objContext = new DataContext(strConnectionString);

Set the data for insert
 

Once you create the connection using the 'DataContext' object the next step is to create the customer entity object and set the data to the object property.
 

clsCustomerEntity objCustomerData = new clsCustomerEntity();
objCustomerData.CustomerCode = txtCustomerCode.Text;
objCustomerData.CustomerName = txtCustomerName.Text;

Do an in-memory update


We then do an in-memory update in entity objects itself using 'InsertOnSubmit' method.
 

objContext.GetTable().InsertOnSubmit(objCustomerData);

Do the final physical commit
 

Finally we do a physical commit to the actual database. Please note until we do not call 'SubmitChanges()' data is not finally committed to the database.


objContext.SubmitChanges();


The final create LINQ code
 

Below is the final LINQ code put together.
 

DataContext objContext = new DataContext(strConnectionString);
clsCustomerEntity objCustomerData = new clsCustomerEntity();
objCustomerData.CustomerCode = txtCustomerCode.Text;
objCustomerData.CustomerName = txtCustomerName.Text;
objContext.GetTable().InsertOnSubmit(objCustomerData);
objContext.SubmitChanges();

Step 3:- Update using LINQ
 

So let's take the next database operation i.e. update.
 

Create data Context
 

As usual we first need to create a 'datacontext' object using the connection string as discussed in the create step.


DataContext objContext = new DataContext(strConnectionString);

Select the customer LINQ object which we want to update
 

Get the LINQ object using LINQ query which we want to update


var MyQuery = from objCustomer in objContext.GetTable()
where objCustomer.CustomerId == Convert.ToInt16(txtCustomerId.Text)
select objCustomer;

Finally set new values and update data to physical database
 

Do the updates and call 'SubmitChanges()' to do the final update.


clsCustomerEntity objCustomerData = (clsCustomerEntity)MyQuery.First();
objCustomerData.CustomerCode = txtCustomerCode.Text;
objCustomerData.CustomerName = txtCustomerName.Text;
objContext.SubmitChanges();

The final code of LINQ update
 

Below is how the final LINQ update query looks like.


DataContext objContext = new DataContext(strConnectionString);
var MyQuery = from objCustomer in objContext.GetTable()
where objCustomer.CustomerId == Convert.ToInt16(txtCustomerId.Text)
select objCustomer;
clsCustomerEntity objCustomerData = (clsCustomerEntity)MyQuery.First();
objCustomerData.CustomerCode = txtCustomerCode.Text;
objCustomerData.CustomerName = txtCustomerName.Text;
objContext.SubmitChanges();

Step 4:- Delete using LINQ
 

Let's take the next database operation delete.


DeleteOnSubmit
 

We will not be going through the previous steps like creating data context and selecting LINQ object , both of them are explained in the previous section. To delete the object from in-memory we need to call 'DeleteOnSubmit()' and to delete from final database we need use 'SubmitChanges()'.


objContext.GetTable().DeleteOnSubmit(objCustomerData);
objContext.SubmitChanges();

Step 5 :- Self explanatory LINQ select and read


Now on the final step selecting and reading the LINQ object by criteria. Below is the code snippet which shows how to fire the LINQ query and set the object value to the ASP.NET UI.


DataContext objContext = new DataContext(strConnectionString);

var MyQuery = from objCustomer in objContext.GetTable()
where objCustomer.CustomerId == Convert.ToInt16(txtCustomerId.Text)
select objCustomer;

clsCustomerEntity objCustomerData = (clsCustomerEntity)MyQuery.First();
txtCustomerCode.Text = objCustomerData.CustomerCode;
txtCustomerName.Text = objCustomerData.CustomerName;

The LINQ CRUD code
 

You can download the complete CRUD source code from here
 


 Subscribe to Articles

     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend