Can you show a simple CRUD example using LINQ?

 Posted by ArticlesMaint on 9/29/2009 | Category: LINQ Interview questions | Views: 5632



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<clsCustomerEntity>().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<clsCustomerEntity>().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<clsCustomerEntity>()

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<clsCustomerEntity>();
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<clsCustomerEntity>()
where objCustomer.CustomerId == Convert.ToInt16(txtCustomerId.Text)
select objCustomer;
clsCustomerEntity objCustomerData = (clsCustomerEntity)MyQuery.First<clsCustomerEntity>();
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<clsCustomerEntity>().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<clsCustomerEntity>()
where objCustomer.CustomerId == Convert.ToInt16(txtCustomerId.Text)
select objCustomer;

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



Asked In: Many Interviews | Alert Moderator 

Comments or Responses

Login to post response