CRUD Operations Using LINQ To SQL In ASP.NET #mesameergaikwad

CRUD Operations Using LINQ To SQL In ASP.NET

LINQ

LINQ Stands For Language Integrated Query, It is a common programming model that can be used to query all types of data such as database, dataset, XML, and many other types of data.

First Open SQL Server and Create a Table name tblDetails which include three fields:

create table tblDetails (

Id int Primary Key,

Name varchar(50),

ImgPath varchar(100)

)

Open Visual Studio and Create a New Web Application. File-New Project-Asp.Net Web Application Name it as LINQ Application.

Right, Click on Application And Create New Folder Name it as Images to Store the Path of the Images which will be Uploaded.

Right Click on Application and Add New Item Web Form Name it as Details.aspx.

Add two textboxes text search, txtName, two buttons btnSubmit and btnSearch, and a Grid View to display Data. Take two Hidden Fields one for storing Id which is the primary key that will be required for updating and Deleting Record. Another for storing image Path. Take a File Upload Control for Image Upload. Just write the following code on aspx page.

<%@ Page Language=”C#” AutoEventWireup=”true” CodeBehind=”Details.aspx.cs” Inherits=”LINQApplication.Details” %>
<!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”>
<script src=”Scripts/jquery-1.10.2.min.js” type=”text/javascript”></script>
<script type=”text/javascript” language=”javascript”>
function ValidateName(e)
{
var keyCode = (e.which) ? e.which : e.keyCode
if (((keyCode >= 65 && keyCode <= 90) || (keyCode >= 97 && keyCode <= 122)) || keyCode == 32)
{
document.getElementById(“chkName”).style.display = “none”;
return true;
}
else
{
document.getElementById(“chkName”).style.display = “inline”;
return false;
}
}
</script>
<title></title>
</head>
<body>
<form id=”form1" runat=”server”>
<div>
<table>
<tr>
<td>
<asp:HiddenField ID=”hdnValue” runat=”server” /> </td>
</tr>
<tr>
<td>
<asp:HiddenField ID=”hdfHidden” runat=”server” /> </td>
</tr>
<tr>
<td> Name : </td>
<td>
<asp:TextBox ID=”txtName” runat=”server” onkeypress=”return ValidateName(event);”> </asp:TextBox> <span id=”chkName” style=”color: Red; display: none”>Name Should Not Contain Numeric Values</span> </td>
</tr>
<tr>
<td> Please Select Image : </td>
<td>
<asp:FileUpload ID=”imgFile” runat=”server” /> </td>
</tr>
<tr>
<td>
<asp:Button ID=”btnSubmit” runat=”server” Text=”Submit” onclick=”btnSubmit_Click” /> </td>
<td>
<asp:Button ID=”btnCancel” runat=”server” Text=”Cancel” onclick=”btnCancel_Click” /> </td>
</tr>
<tr>
<td> Search by Name: </td>
<td>
<asp:TextBox ID=”txtSearch” runat=”server”></asp:TextBox>
</td>
<td>
<asp:Button ID=”btnSearch” runat=”Server” Text=”Search” onclick=”btnSearch_Click” /> </td>
</tr>
<tr>
<td>
<asp:GridView ID=”gvDisplayDetails” DataKeyNames=”Id” runat=”server” AutoGenerateColumns=”false” onrowcommand=”gvDisplayDetails_RowCommand” onrowdeleting=”gvDisplayDetails_RowDeleting” onselectedindexchanging=”gvDisplayDetails_SelectedIndexChanging”>
<Columns>
<asp:BoundField DataField=”Name” HeaderText=”Name” />
<asp:TemplateField HeaderText=”Images”>
<ItemTemplate>
<asp:Image ID=”img” runat=”server” ImageUrl=’<%# Bind(“ImgPath”, “~/Images/{0}”) %>’ /> </ItemTemplate>
</asp:TemplateField>
<asp:CommandField ButtonType=”Button” SelectText=”Edit” ShowSelectButton=”True” />
<asp:TemplateField>
<ItemTemplate>
<asp:Button runat=”server” ID=”btnDelete” OnClientClick=”return confirm(‘Are you sure,you want to delete this record ?’);” Text=”Delete” CommandName=”Delete” /> </ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</td>
</tr>
</table>
</div>
</form>
</body>
</html>

Now again right-click on the Application and Add New a LINQ to SQL Class. Select Add New Item then in Installed Templates.

Select Data then Select LINQ to SQL Classes, Name it as PersonalDetails. Extension is .dbml.

Now From Server Explorer just drag and drop your table on dbml file and save it. You will see in web.config a connection string is created automatically:

<add name=”sameerDBConnectionString” connectionString=”Data Source=.\SQLEXPRESS;Initial Catalog=sameer;Integrated Security=True”

providerName=”System.Data.SqlClient” />

Here TestDB is my DataBase Name.

Generate Events for GridView Selected Index Changing, OnRowCommand, OnRowDeleting.
Just write the following code on the CS side.
using System;
using System.Collections.Generic;
using System.LINQ;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Configuration;
using System.Data.SqlClient;
namespace LINQApplication
{
public partial class Details: System.Web.UI.Page
{
PersonalDetailsDataContext obj = new PersonalDetailsDataContext();
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings[“TestDBConnectionString”].ToString());
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
BindGrid();
btnCancel.Visible = false;
}
}
private void BindGrid()
{
try
{
var Details = from details in obj.tblDetails
select new
{
details.Id,
details.Name,
details.ImgPath
};
if (Details.Any())
{
gvDisplayDetails.DataSource = Details;
gvDisplayDetails.DataBind();
}
else
{
Showmsg(“No Record Found”);
}
}
catch (Exception ex)
{
throw ex;
}
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
try
{
if (btnSubmit.Text == “Submit”)
{
if (checkBlank())
{
Save();
}
}
else if (btnSubmit.Text == “Update”)
{
Update();
}
}
catch (Exception ex)
{
throw ex;
}
}
private void Update()
{
try
{
string filename = UploadImages();
int id = Convert.ToInt32(hdnValue.Value);
var singleRecord = obj.tblDetails.First(dtl => dtl.Id == id);
singleRecord.Name = txtName.Text;
if (imgFile.HasFile)
{
singleRecord.ImgPath = filename;
}
else
{
singleRecord.ImgPath = hdfHidden.Value;
}
obj.SubmitChanges();
btnSubmit.Text = “Submit”;
Showmsg(“Information Updated Successfulluy”);
BindGrid();
Clear();
btnCancel.Visible = false;
}
catch (Exception ex)
{
throw ex;
}
}
private void Save()
{
try
{
string fileName = UploadImages();
tblDetail objDetail = new tblDetail();
objDetail.Name = txtName.Text;
if (imgFile.HasFile)
{
objDetail.ImgPath = fileName;
}
else
{
objDetail.ImgPath = hdfHidden.Value;
}
obj.tblDetails.InsertOnSubmit(objDetail);
obj.SubmitChanges();
Showmsg(“Details Inserted Successfully”);
BindGrid();
Clear();
}
catch (Exception ex)
{
throw ex;
}
}
private string UploadImages()
{
string result = string.Empty;
if (imgFile.HasFile)
{
string extension = System.IO.Path.GetExtension(imgFile.PostedFile.FileName);
if (extension == “.jpg” || extension == “.png” || extension == “.img”)
{
result = imgFile.PostedFile.FileName;
imgFile.PostedFile.SaveAs(MapPath(“~”) + “/Images/” + result);
}
}
return result;
}
private bool checkBlank()
{
try
{
bool flag = true;
string msg = string.Empty;
if (!imgFile.HasFile)
{
msg += “Please Upload File, “;
flag = false;
}
else
{
if (!(Path.GetExtension(imgFile.PostedFile.FileName).Equals(“.jpg”) || Path.GetExtension(imgFile.PostedFile.FileName).Equals(“.img”) || Path.GetExtension(imgFile.PostedFile.FileName).Equals(“.png”)))
{
msg += “Please Upload Valid File, “;
flag = false;
}
}
if (txtName.Text == string.Empty)
{
msg += “Please Enter Name, “;
flag = false;
}
if (msg.Length > 0)
{
msg = msg.Remove(msg.Length — 2);
Showmsg(msg);
}
return flag;
}
catch (Exception ex)
{
throw ex;
}
}
private void Showmsg(string Message)
{
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), “Alert”, “alert(‘“ + Message + “‘);”, true);
}
private void Clear()
{
txtName.Text = “”;
txtSearch.Text = “”;
}
protected void gvDisplayDetails_SelectedIndexChanging(object sender, GridViewSelectEventArgs e)
{
try
{
string id = gvDisplayDetails.DataKeys[e.NewSelectedIndex].Value.ToString();
hdnValue.Value = id;
var singleRecord = obj.tblDetails.Single(dtls => dtls.Id == Convert.ToInt32(id));
txtName.Text = singleRecord.Name;
hdfHidden.Value = singleRecord.ImgPath;
btnSubmit.Text = “Update”;
btnCancel.Visible = true;
}
catch (Exception ex)
{
throw ex;
}
}
protected void gvDisplayDetails_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
try
{
int Id = Convert.ToInt32(gvDisplayDetails.DataKeys[e.RowIndex].Value);
var singleRecord = obj.tblDetails.First(dtl => dtl.Id == Id);
obj.tblDetails.DeleteOnSubmit(singleRecord);
obj.SubmitChanges();
Showmsg(“Records Deleted Successfully”);
BindGrid();
}
catch (Exception ex)
{
throw ex;
}
}
protected void gvDisplayDetails_RowCommand(object sender, GridViewCommandEventArgs e)
{}
protected void btnCancel_Click(object sender, EventArgs e)
{
try
{
btnSubmit.Text = “Submit”;
BindGrid();
btnCancel.Visible = false;
Clear();
}
catch (Exception ex)
{
throw ex;
}
}
protected void btnSearch_Click(object sender, EventArgs e)
{
try
{
if (txtSearch.Text != string.Empty && txtSearch.Text != “”)
{
string txt = txtSearch.Text;
BindSearch(txt);
Clear();
}
else
{
Showmsg(“Please Enter a Keyword to Search”);
}
}
catch (Exception ex)
{
throw ex;
}
}
private void BindSearch(string name)
{
try
{
var results = from details in obj.tblDetails where details.Name.Contains(name)
select new
{
details.Id,
details.Name,
details.ImgPath
};
if (results.Any())
{
gvDisplayDetails.DataSource = results;
gvDisplayDetails.DataBind();
}
else
{
Showmsg(“No Record Found for Particular Keyword”);
BindGrid();
}
}
catch (Exception ex)
{
throw ex;
}
}
}
}
We can see I have created an object of PersonalDataContext class it contains all entities that map to tables in databases. On page load on If(!Page.IsPostBack) i have called a method BindGrid() which bind Details to Gridview. Also, I have used Any to see if the result of the LINQ query contains data.
Also, there are two methods, one for submit and another for the update, for submit and update I have used insert on submit and just passed the object of the table entity to it. Then submit changes to save changes. For Deletion use Deletion Submit.

These are basic Crud operations using LINQ to SQL in ASP.NET, I will come up with more details on LINQ in my next blog posts. #mesameergaikwad

CRUD Operations Using LINQ To SQL In ASP.NET #mesameergaikwad