Post
Ask Question

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 type of data.

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

    Id Primary Key,
    Name varchar(50),
    ImgPath varchar(100)

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

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

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

  5. Add two textboxes txtSearch, txtName, tow buttons btnSubmit and btnSearch and a Grid View to display Data. Take two Hidden Fields one for storing Id which is primary key which will be required for updating and Deleting Record. Another for storing image Path. Take a File Upload Control for Image Upload. Just write following code on aspx page.
    1. <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Details.aspx.cs" Inherits="LINQApplication.Details" %>  
    2.     <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">  
    3.     <html xmlns="http://www.w3.org/1999/xhtml">  
    4.   
    5.     <head runat="server">  
    6.         <script src="Scripts/jquery-1.10.2.min.js" type="text/javascript"></script>  
    7.         <script type="text/javascript" language="javascript">  
    8.         function ValidateName(e)  
    9.         {  
    10.             var keyCode = (e.which) ? e.which : e.keyCode  
    11.             if (((keyCode >= 65 && keyCode <= 90) || (keyCode >= 97 && keyCode <= 122)) || keyCode == 32)  
    12.             {  
    13.                 document.getElementById("chkName").style.display = "none";  
    14.                 return true;  
    15.             }  
    16.             else  
    17.             {  
    18.                 document.getElementById("chkName").style.display = "inline";  
    19.                 return false;  
    20.             }  
    21.         }  
    22.         </script>  
    23.         <title></title>  
    24.     </head>  
    25.   
    26.     <body>  
    27.         <form id="form1" runat="server">  
    28.             <div>  
    29.                 <table>  
    30.                     <tr>  
    31.                         <td>  
    32.                             <asp:HiddenField ID="hdnValue" runat="server" /> </td>  
    33.                     </tr>  
    34.                     <tr>  
    35.                         <td>  
    36.                             <asp:HiddenField ID="hdfHidden" runat="server" /> </td>  
    37.                     </tr>  
    38.                     <tr>  
    39.                         <td> Name : </td>  
    40.                         <td>  
    41.                             <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>  
    42.                     </tr>  
    43.                     <tr>  
    44.                         <td> Please Select Image : </td>  
    45.                         <td>  
    46.                             <asp:FileUpload ID="imgFile" runat="server" /> </td>  
    47.                     </tr>  
    48.                     <tr>  
    49.                         <td>  
    50.                             <asp:Button ID="btnSubmit" runat="server" Text="Submit" onclick="btnSubmit_Click" /> </td>  
    51.                         <td>  
    52.                             <asp:Button ID="btnCancel" runat="server" Text="Cancel" onclick="btnCancel_Click" /> </td>  
    53.                     </tr>  
    54.                     <tr>  
    55.                         <td> Search by Name: </td>  
    56.                         <td>  
    57.                             <asp:TextBox ID="txtSearch" runat="server"></asp:TextBox>  
    58.                         </td>  
    59.                         <td>  
    60.                             <asp:Button ID="btnSearch" runat="Server" Text="Search" onclick="btnSearch_Click" /> </td>  
    61.                     </tr>  
    62.                     <tr>  
    63.                         <td>  
    64.                             <asp:GridView ID="gvDisplayDetails" DataKeyNames="Id" runat="server" AutoGenerateColumns="false" onrowcommand="gvDisplayDetails_RowCommand" onrowdeleting="gvDisplayDetails_RowDeleting" onselectedindexchanging="gvDisplayDetails_SelectedIndexChanging">  
    65.                                 <Columns>  
    66.                                     <asp:BoundField DataField="Name" HeaderText="Name" />  
    67.                                     <asp:TemplateField HeaderText="Images">  
    68.                                         <ItemTemplate>  
    69.                                             <asp:Image ID="img" runat="server" ImageUrl='<%# Bind("ImgPath", "~/Images/{0}") %>' /> </ItemTemplate>  
    70.                                     </asp:TemplateField>  
    71.                                     <asp:CommandField ButtonType="Button" SelectText="Edit" ShowSelectButton="True" />  
    72.                                     <asp:TemplateField>  
    73.                                         <ItemTemplate>  
    74.                                             <asp:Button runat="server" ID="btnDelete" OnClientClick="return confirm('Are you sure,you want to delete this record ?');" Text="Delete" CommandName="Delete" /> </ItemTemplate>  
    75.                                     </asp:TemplateField>  
    76.                                 </Columns>  
    77.                             </asp:GridView>  
    78.                         </td>  
    79.                     </tr>  
    80.                 </table>  
    81.             </div>  
    82.         </form>  
    83.     </body>  
    84.   
    85. </html>  
  6. 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.

  7. 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:
    1. <add name="TestDBConnectionString" connectionString="Data Source=LIVINGROOM\SQLEXPRESS;Initial Catalog=Test   DB;Integrated Security=True"  
    2.    providerName="System.Data.SqlClient" />  
    Here TestDB is my DataBase Name.

  8. Generate Events for GridView Selected Index Changing, OnRowCommand, OnRowDeleting.

  9. Just write following code on CS side.
    1. using System;  
    2. using System.Collections.Generic;  
    3. using System.LINQ;  
    4. using System.Web;  
    5. using System.Web.UI;  
    6. using System.Web.UI.WebControls;  
    7. using System.IO;  
    8. using System.Configuration;  
    9. using System.Data.SqlClient;  
    10. namespace LINQApplication  
    11. {  
    12.     public partial class Details: System.Web.UI.Page  
    13.     {  
    14.         PersonalDetailsDataContext obj = new PersonalDetailsDataContext();  
    15.         SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TestDBConnectionString"].ToString());  
    16.         protected void Page_Load(object sender, EventArgs e)  
    17.         {  
    18.             if (!Page.IsPostBack)  
    19.             {  
    20.                 BindGrid();  
    21.                 btnCancel.Visible = false;  
    22.             }  
    23.         }  
    24.         private void BindGrid()  
    25.         {  
    26.             try  
    27.             {  
    28.                 var Details = from details in obj.tblDetails  
    29.                 select new  
    30.                 {  
    31.                     details.Id,  
    32.                         details.Name,  
    33.                         details.ImgPath  
    34.                 };  
    35.                 if (Details.Any())  
    36.                 {  
    37.                     gvDisplayDetails.DataSource = Details;  
    38.                     gvDisplayDetails.DataBind();  
    39.                 }  
    40.                 else  
    41.                 {  
    42.                     Showmsg("No Record Found");  
    43.                 }  
    44.             }  
    45.             catch (Exception ex)  
    46.             {  
    47.                 throw ex;  
    48.             }  
    49.         }  
    50.         protected void btnSubmit_Click(object sender, EventArgs e)  
    51.         {  
    52.             try  
    53.             {  
    54.                 if (btnSubmit.Text == "Submit")  
    55.                 {  
    56.                     if (checkBlank())  
    57.                     {  
    58.                         Save();  
    59.                     }  
    60.                 }  
    61.                 else if (btnSubmit.Text == "Update")  
    62.                 {  
    63.                     Update();  
    64.                 }  
    65.             }  
    66.             catch (Exception ex)  
    67.             {  
    68.                 throw ex;  
    69.             }  
    70.         }  
    71.         private void Update()  
    72.         {  
    73.             try  
    74.             {  
    75.                 string filename = UploadImages();  
    76.                 int id = Convert.ToInt32(hdnValue.Value);  
    77.                 var singleRecord = obj.tblDetails.First(dtl => dtl.Id == id);  
    78.                 singleRecord.Name = txtName.Text;  
    79.                 if (imgFile.HasFile)  
    80.                 {  
    81.                     singleRecord.ImgPath = filename;  
    82.                 }  
    83.                 else  
    84.                 {  
    85.                     singleRecord.ImgPath = hdfHidden.Value;  
    86.                 }  
    87.                 obj.SubmitChanges();  
    88.                 btnSubmit.Text = "Submit";  
    89.                 Showmsg("Information Updated Successfulluy");  
    90.                 BindGrid();  
    91.                 Clear();  
    92.                 btnCancel.Visible = false;  
    93.             }  
    94.             catch (Exception ex)  
    95.             {  
    96.                 throw ex;  
    97.             }  
    98.         }  
    99.         private void Save()  
    100.         {  
    101.             try  
    102.             {  
    103.                 string fileName = UploadImages();  
    104.                 tblDetail objDetail = new tblDetail();  
    105.                 objDetail.Name = txtName.Text;  
    106.                 if (imgFile.HasFile)  
    107.                 {  
    108.                     objDetail.ImgPath = fileName;  
    109.                 }  
    110.                 else  
    111.                 {  
    112.                     objDetail.ImgPath = hdfHidden.Value;  
    113.                 }  
    114.                 obj.tblDetails.InsertOnSubmit(objDetail);  
    115.                 obj.SubmitChanges();  
    116.                 Showmsg("Details Inserted Successfully");  
    117.                 BindGrid();  
    118.                 Clear();  
    119.             }  
    120.             catch (Exception ex)  
    121.             {  
    122.                 throw ex;  
    123.             }  
    124.         }  
    125.         private string UploadImages()  
    126.         {  
    127.             string result = string.Empty;  
    128.             if (imgFile.HasFile)  
    129.             {  
    130.                 string extension = System.IO.Path.GetExtension(imgFile.PostedFile.FileName);  
    131.                 if (extension == ".jpg" || extension == ".png" || extension == ".img")  
    132.                 {  
    133.                     result = imgFile.PostedFile.FileName;  
    134.                     imgFile.PostedFile.SaveAs(MapPath("~") + "/Images/" + result);  
    135.                 }  
    136.             }  
    137.             return result;  
    138.         }  
    139.         private bool checkBlank()  
    140.         {  
    141.             try  
    142.             {  
    143.                 bool flag = true;  
    144.                 string msg = string.Empty;  
    145.                 if (!imgFile.HasFile)  
    146.                 {  
    147.                     msg += "Please Upload File, ";  
    148.                     flag = false;  
    149.                 }  
    150.                 else  
    151.                 {  
    152.                     if (!(Path.GetExtension(imgFile.PostedFile.FileName).Equals(".jpg") || Path.GetExtension(imgFile.PostedFile.FileName).Equals(".img") || Path.GetExtension(imgFile.PostedFile.FileName).Equals(".png")))  
    153.                     {  
    154.                         msg += "Please Upload Valid File, ";  
    155.                         flag = false;  
    156.                     }  
    157.                 }  
    158.                 if (txtName.Text == string.Empty)  
    159.                 {  
    160.                     msg += "Please Enter Name, ";  
    161.                     flag = false;  
    162.                 }  
    163.                 if (msg.Length > 0)  
    164.                 {  
    165.                     msg = msg.Remove(msg.Length - 2);  
    166.                     Showmsg(msg);  
    167.                 }  
    168.                 return flag;  
    169.             }  
    170.             catch (Exception ex)  
    171.             {  
    172.                 throw ex;  
    173.             }  
    174.         }  
    175.         private void Showmsg(string Message)  
    176.         {  
    177.             ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "Alert", "alert('" + Message + "');", true);  
    178.         }  
    179.         private void Clear()  
    180.         {  
    181.             txtName.Text = "";  
    182.             txtSearch.Text = "";  
    183.         }  
    184.         protected void gvDisplayDetails_SelectedIndexChanging(object sender, GridViewSelectEventArgs e)  
    185.         {  
    186.             try  
    187.             {  
    188.                 string id = gvDisplayDetails.DataKeys[e.NewSelectedIndex].Value.ToString();  
    189.                 hdnValue.Value = id;  
    190.                 var singleRecord = obj.tblDetails.Single(dtls => dtls.Id == Convert.ToInt32(id));  
    191.                 txtName.Text = singleRecord.Name;  
    192.                 hdfHidden.Value = singleRecord.ImgPath;  
    193.                 btnSubmit.Text = "Update";  
    194.                 btnCancel.Visible = true;  
    195.             }  
    196.             catch (Exception ex)  
    197.             {  
    198.                 throw ex;  
    199.             }  
    200.         }  
    201.         protected void gvDisplayDetails_RowDeleting(object sender, GridViewDeleteEventArgs e)  
    202.         {  
    203.             try  
    204.             {  
    205.                 int Id = Convert.ToInt32(gvDisplayDetails.DataKeys[e.RowIndex].Value);  
    206.                 var singleRecord = obj.tblDetails.First(dtl => dtl.Id == Id);  
    207.                 obj.tblDetails.DeleteOnSubmit(singleRecord);  
    208.                 obj.SubmitChanges();  
    209.                 Showmsg("Records Deleted Successfully");  
    210.                 BindGrid();  
    211.             }  
    212.             catch (Exception ex)  
    213.             {  
    214.                 throw ex;  
    215.             }  
    216.         }  
    217.         protected void gvDisplayDetails_RowCommand(object sender, GridViewCommandEventArgs e)  
    218.         {}  
    219.         protected void btnCancel_Click(object sender, EventArgs e)  
    220.         {  
    221.             try  
    222.             {  
    223.                 btnSubmit.Text = "Submit";  
    224.                 BindGrid();  
    225.                 btnCancel.Visible = false;  
    226.                 Clear();  
    227.             }  
    228.             catch (Exception ex)  
    229.             {  
    230.                 throw ex;  
    231.             }  
    232.         }  
    233.         protected void btnSearch_Click(object sender, EventArgs e)  
    234.         {  
    235.             try  
    236.             {  
    237.                 if (txtSearch.Text != string.Empty && txtSearch.Text != "")  
    238.                 {  
    239.                     string txt = txtSearch.Text;  
    240.                     BindSearch(txt);  
    241.                     Clear();  
    242.                 }  
    243.                 else  
    244.                 {  
    245.                     Showmsg("Please Enter a Keyword to Search");  
    246.                 }  
    247.             }  
    248.             catch (Exception ex)  
    249.             {  
    250.                 throw ex;  
    251.             }  
    252.         }  
    253.         private void BindSearch(string name)  
    254.         {  
    255.             try  
    256.             {  
    257.                 var results = from details in obj.tblDetails where details.Name.Contains(name)  
    258.                 select new  
    259.                 {  
    260.                     details.Id,  
    261.                         details.Name,  
    262.                         details.ImgPath  
    263.                 };  
    264.                 if (results.Any())  
    265.                 {  
    266.                     gvDisplayDetails.DataSource = results;  
    267.                     gvDisplayDetails.DataBind();  
    268.                 }  
    269.                 else  
    270.                 {  
    271.                     Showmsg("No Record Found for Particular Keyword");  
    272.                     BindGrid();  
    273.                 }  
    274.             }  
    275.             catch (Exception ex)  
    276.             {  
    277.                 throw ex;  
    278.             }  
    279.         }  
    280.     }  
    281. }  
  10. We can see I have created a object of PersonalDataContext class it contains all entitys 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 result of LINQ query contains data.

  11. Also there are two method, one for submit and another for update, for submit and update I have used insert on submit and just passed the object ofthe table entity to it. Then submit changes to save changes. For Deletion used Deletion Submit.

These are basic Crud operations using LINQ to SQL in ASP.NET, I will come up with more details on LINQ inmy next blog posts.
Next Recommended Reading Effective Paging Using Linq in ASP.NET