LINQ - DB Operations
LINQ is fun to work with. I was a bit reluctant to start with LINQ, but once in, it took me like forest fire.
Its one of the most interesting things I have worked in the recent past. To start with, LINQ provides an
interface to query objects for data. Objects might contain data in them or they could communicate with DB and
fetch results for you. Basically, LINQ acts like a ORM tool.
I did a descent amount of work in NHibernate, which is yet another ORM tool. I am not going to do a compare and
contrast on these too, but they work almost the same way, except for the point LINQ is slightly faster.
LINQ can be used to query objects, DB's and XML. Here we'd see our operations on DB, while querying objects will be
available as a seperate post, shortly.
I started with a simple web application, that would query DB to perform CRUD operations, extract of that is
deliniated.
Create your data source with tables, and requisite data.
This is the structure of the Person table I used for my example
Id int
FirstName nvarchar(50)
LastName nvarchar(50)
IDRole int
I also have a Role table which has the following format
Id int
Designation nvarchar(50)
Create a new Web/Windows project, Add a new LINQ to SQL class. I created a web application with the name LIQWebSamples
Provide it a useful name. This would result in a dbml file. I named it People.dbml.
Open the datasource in server explorer, drag Person table and drop it in the dbml file. I added a new form CRUDForm.aspx
My web page designer code is appended
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="CRUDForm.aspx.cs" Inherits="LIQWebSamples.CRUDForm" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<table cellspacing="15" cellpadding = "10">
<tr>
<td>
<asp:Button ID="btnNew" runat="server" Text="New" onclick="btnNew_Click" />
</td>
<td>
<asp:Button ID="btnEdit" runat="server" Text="Edit" onclick="btnEdit_Click" />
</td>
<td>
<asp:Button ID="btnUpdate" runat="server" Text="Update"
onclick="btnUpdate_Click" />
</td>
<td>
<asp:Button ID="btnDelete" runat="server" Text="Delete"
onclick="btnDelete_Click" />
</td>
</tr>
</table>
<hr />
<table cellspacing="15" cellpadding = "10">
<tr>
<td>
<asp:Label ID="Label1" runat="server" Text="First Name"></asp:Label>
</td>
<td>
<asp:DropDownList ID="ddListFName" runat="server" AutoPostBack="false">
</asp:DropDownList>
</td>
<td>
<asp:Label ID="Label2" runat="server" Text="Last Name"></asp:Label>
</td>
<td>
<asp:DropDownList ID="ddListLName" runat="server" AutoPostBack="false">
</asp:DropDownList>
</td>
<td>
<asp:Button ID="btnSearch" runat="server" Text="Search"
onclick="btnSearch_Click"/>
</td>
</tr>
</table>
<hr />
<br />
<table cellpadding="10" cellspacing="15">
<tr>
<td>
<asp:Label ID="Label3" runat="server" Text="ID"></asp:Label>
</td>
<td>
<asp:TextBox ID="txtID" runat="server"></asp:TextBox><br />
</td>
</tr>
<tr>
<td>
<asp:Label ID="Label4" runat="server" Text="First Name"></asp:Label>
</td>
<td>
<asp:TextBox ID="txtFName" runat="server"></asp:TextBox><br />
</td>
</tr>
<tr>
<td>
<asp:Label ID="Label5" runat="server" Text="Last Name"></asp:Label>
</td>
<td>
<asp:TextBox ID="txtLName" runat="server"></asp:TextBox><br />
</td>
</tr>
<tr>
<td>
<asp:Label ID="Label6" runat="server" Text="Role Id"></asp:Label>
</td>
<td>
<asp:TextBox ID="txtRoleId" runat="server"></asp:TextBox><br />
</td>
</tr>
</table>
<br />
<asp:Label ID="lblStatus" runat="server" Font-Bold="True" Font-Size="Larger" ForeColor="#FFCC00"></asp:Label>
</div>
</form>
</body>
</html>
Congratulations, you have created your entity and object to head on LINQ. Take some moment to browse through
DBML file. It would help you understand how mapping is done
Select operation
Let's do a simple select operation to fill in First Name and Last name field. Ensure you have some valid data
in your DB
As I was telling you, there are 2 ways queries can be executed in LINQ, a longer format or Lambda expressions/
format. We'll see both. Lets take a look at the longer format first.
In all of the samples, you 'll see me using a new object called DataContext. People with ADO/ADO.NET background,
can treat this equivalent to a connection class for communication with DB
public ListGetPeopleByFirstName()
{
Debug.WriteLine("First Name, Start Time :" + DateTime.Now.ToString());
PeopleDataContext db = new PeopleDataContext();
var pFName = from p in db.Persons select p.FirstName;
Debug.WriteLine(pFName.ToString());
return pFName.ToList();
}
Here we do a similar operation, as above. But fetch last name, using Lambda expression. It's more easy isn't.
There are no performance differences between Lambda expressions and the longer format. But certainly there
are syntactical differences, as you can see. It's much easy doing a Lambda operation then doing a longer format
public ListGetPeopleByLastName()
{
Debug.WriteLine("Last Name Start Time :" + DateTime.Now.ToString());
PeopleDataContext db = new PeopleDataContext();
//var pLName = from p in db.Persons select p.LastName;
var pLName = db.Persons.Select(p => p.LastName);
Debug.WriteLine(pLName.ToString());
return pLName.ToList();
}
A sample of filter is attached
public ListGetPersonInfoFromFirstName(string sFName)
{
Debug.WriteLine("Search on first name start : " + DateTime.Now.ToString());
PeopleDataContext db = new PeopleDataContext();
var lstPeople = from p in db.Persons
where p.FirstName == sFName
select p;
Debug.WriteLine(lstPeople.ToString());
return lstPeople.ToList();
}
A similar filter using Lambda expression
public ListGetPersonInfoFromLastName(string sLName)
{
Debug.WriteLine("Search on last name start : " + DateTime.Now.ToString());
PeopleDataContext db = new PeopleDataContext();
var lstPeople = db.Persons.Where(p => p.LastName == sLName).Select(p => p);
Debug.WriteLine(lstPeople.ToString());
return lstPeople.ToList();
}
A sample on multiple filter operation
public ListGetPersonInfoFromFirstAndLastName(string sFName, string sLName)
{
Debug.WriteLine("Search on first & last name start : " + DateTime.Now.ToString());
PeopleDataContext db = new PeopleDataContext();
var lstPeople = db.Persons.Where(p => p.FirstName == sFName && p.LastName == sLName).Select(p => p);
Debug.WriteLine(lstPeople.ToString());
return lstPeople.ToList();
}
Insert operation
Insert operation is going to be very simple. All that we need to do is create a new object of the entity that
needs to be inserted (in our case, Person class), and pass it on to the datacontext for update operations
public bool InsertNewPerson(int nId, string sFName, string sLName, int nRoleId)
{
//presumed that validations have happened upfront
Debug.WriteLine("New record insert start : " + DateTime.Now.ToString());
PeopleDataContext db = new PeopleDataContext();
Person pers = new Person();
pers.Id = nId;
pers.FirstName = sFName;
pers.LastName = sLName;
pers.IDRole = nRoleId;
db.Persons.Add(pers);
db.SubmitChanges();
//db.Persons.Add(
return true;
}
Update Operation
Very similar to Insert operation. First we load the object that needs to be updated, make changes to the object,
and call SubmitChanges method of DataContext.
public bool UpdatePerson(int nId, string sFName, string sLName, int nRoleId)
{
Debug.WriteLine("Update record start : " + DateTime.Now.ToString());
PeopleDataContext db = new PeopleDataContext();
Person per = (Person)db.Persons.Single(p => p.Id == nId);
per.FirstName = sFName;
per.LastName = sLName;
per.IDRole = nRoleId;
db.SubmitChanges();
return true;
}
Delete Operation
Fetch the record to be deleted and invoke Remove Method
public bool DeletePerson(int nId)
{
Debug.WriteLine("Delete record start : " + DateTime.Now.ToString());
PeopleDataContext db = new PeopleDataContext();
//if there are more than one person to be deleted, use this code
//var delPerson = db.Persons.Where(p => p.Id == nId).Select(p=>p);
var delPerson = db.Persons.Single(p => p.Id == nId);
//check to see if there are records for deletion
db.Persons.Remove(delPerson);
//db.Persons.RemoveAll(delPerson);
db.SubmitChanges();
return true;
}
}
}
You are done with all basic CRUD operations. I have also attached code behind which will help you see how I
am binding records retreived for display. Hope it helps
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Xml.Linq;
using System.Diagnostics;
using LIQWebSamples.BusinessOperations;
namespace LIQWebSamples
{
public partial class CRUDForm : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
LoadDataInControls();
}
else
{
if (Session["Status"] != null)
{
string sStatus = Session["Status"].ToString();
if (sStatus.Equals("UPDATED"))
{
LoadDataInControls();
}
}
}
}
private void LoadDataInControls()
{
Debug.WriteLine("Page Load Start Time :" + DateTime.Now.ToString());
PeopleOperations op = new PeopleOperations();
ddListFName.DataSource = op.GetPeopleByFirstName();
Debug.WriteLine("First Name End Time :" + DateTime.Now.ToString());
ddListLName.DataSource = op.GetPeopleByLastName();
Debug.WriteLine("Last Name End Time :" + DateTime.Now.ToString());
Debug.WriteLine("Page Load - Data Binding, Start Time :" + DateTime.Now.ToString());
ddListFName.DataBind();
ddListLName.DataBind();
ddListFName.Items.Add(" ");
ddListLName.Items.Add(" ");
ddListLName.SelectedIndex = 13;
ddListFName.SelectedIndex = 13;
Debug.WriteLine("Page Load End Time :" + DateTime.Now.ToString());
}
protected void btnSearch_Click(object sender, EventArgs e)
{
PeopleOperations op = new PeopleOperations();
ListlstPerson = null;
string sText = ddListFName.SelectedValue;
sText = ddListFName.SelectedItem.Value;
if (ddListLName.Text.Trim().Length > 0 && ddListFName.Text.Trim().Length <= 0) { //search for last name lstPerson = op.GetPersonInfoFromLastName(ddListLName.SelectedValue); Debug.WriteLine("Search on last name end : " + DateTime.Now.ToString()); } else if (ddListLName.Text.Trim().Length <= 0 && ddListFName.Text.Trim().Length > 0)
{
//search for first name
lstPerson = op.GetPersonInfoFromFirstName(ddListFName.Text);
Debug.WriteLine("Search on first name end : " + DateTime.Now.ToString());
}
else if (ddListFName.Text.Trim().Length > 0 && ddListLName.Text.Trim().Length > 0)
{
//search for first & last name
lstPerson = op.GetPersonInfoFromFirstAndLastName(ddListFName.Text, ddListLName.Text);
Debug.WriteLine("Search on first & last name end : " + DateTime.Now.ToString());
}
else
{
//both are null, so no seach is done on DB
lstPerson = null;
}
if (lstPerson != null)
foreach (Person p in lstPerson)
{
txtID.Text = p.Id.ToString();
txtFName.Text = p.FirstName;
txtLName.Text = p.LastName;
txtRoleId.Text = p.IDRole.ToString();
}
}
private void ClearFields()
{
txtID.Text = "";
txtFName.Text = "";
txtLName.Text = "";
txtRoleId.Text = "";
}
protected void btnNew_Click(object sender, EventArgs e)
{
ClearFields();
Session["status"] = "NEW";
}
private string UpdateStatusMessage(int nStatus)
{
string sStatusMsg = "";
if (nStatus == 1)
{
//New insert
sStatusMsg = "Successfully inserted record";
}
else if (nStatus == 2)
{
//Update operation
sStatusMsg = "Successfully updated record";
}
else if (nStatus == 3)
{
//delete operation
sStatusMsg = "Successfully deleted record";
}
else
{
//get lost
sStatusMsg = null;
}
return sStatusMsg;
}
protected void btnUpdate_Click(object sender, EventArgs e)
{
PeopleOperations op = new PeopleOperations();
int nId = System.Convert.ToInt32(txtID.Text);
string sFName = txtFName.Text;
string sLName = txtLName.Text;
int nRoleId = System.Convert.ToInt32(txtRoleId.Text);
if(Session["Status"].Equals("NEW"))
{
//validate records
//save new record
if (op.InsertNewPerson(nId, sFName, sLName, nRoleId) == true)
{
//update message or redirect to success page
ClearFields();
lblStatus.Text = UpdateStatusMessage(1);
Session["Status"] = "UPDATED";
}
else
{
Session["Status"] = "ERROR UPDATE";
}
}
else if(Session["Status"].Equals("EDIT"))
{
//Update existing record
if (op.UpdatePerson(nId, sFName, sLName, nRoleId) == true)
{
lblStatus.Text = UpdateStatusMessage(2);
Session["Status"] = "UPDATED";
ClearFields();
}
else
{
Session["Status"] = "ERROR UPDATE";
}
}
else
{
}
}
protected void btnEdit_Click(object sender, EventArgs e)
{
Session["status"] = "EDIT";
}
protected void btnDelete_Click(object sender, EventArgs e)
{
if (txtID.Text.Length > 0)
{
int nId = System.Convert.ToInt32(txtID.Text);
PeopleOperations op = new PeopleOperations();
if (op.DeletePerson(nId) == true)
{
lblStatus.Text = UpdateStatusMessage(3);
}
}
ClearFields();
}
}
}
No comments:
Post a Comment