Save,Edit,Update,Delete and Bind Asp.net ListBox using LINQ to SQL

Introduction: In this article i am going to explain How to Insert, Edit, Update, Delete, Cancel, Show and Bind ListBox using LINQ to SQL in asp.net.In previous articles i explained How to Bind,Save,Edit,Update,Cancel,Delete,Paging example in GridView and MVC 4 application to Create,Read,Update,Delete and Search functionality and WCF Service to perform bind,insert,edit,update,delete in gridview and Create crystal reports in visual studio 2010 and Show jQuery tooltip message on mouse over on asp.net controls and How to clear or reset all asp.net controls on web page

Click on image to enlarge

Description: In this article you will learn the basic operations like Save, Edit, Update and Delete operations in asp.net using LINQ to SQL. Newly inserting record will get displayed in ListBox control and clicking on any record from ListBox will fetch the corresponding details from the database and fills up the appropriate fields on the form, so that we can edit or delete that record as shown in demo above.

Implementation:  Let's create an asp.net application using LINQ to see it in action.

• First of all create a SQL SERVER Database and name it "emp_db" and also create a table "EMP_TABLE" using the script below:

CREATE TABLE [dbo].[EMP_TABLE]

(

                [EMP_ID] [int] IDENTITY(1,1) NOT NULL,

                [EMP_NAME] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

                [AGE] [int] NULL,

                [DEPT] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

                [SALARY] [bigint] NULL

)

• Now open visual studio ->File menu -> New -> Website. New Web site dialog box will open as shown in image below-> Select Visual C#  from the left pane under Installed Templates and also select ASP.NET Empty Web Site ->Select the location to save the application e.g. D:\lalit\LinqDemo  and click Ok button.

Click on image to enlarge

• Now from the Website menu -> Add New Item ->Select LINQ to SQL Classes-> rename it from the default name DataClasses.dbml to emp.dbml  as shown in image below and click on Add button.





Click on image to enlarge

• A dialog box will appear as shown in image below:

•  Just click on Yes button. It will add the emp.dbml file in the App_Code folder in the solution explorer as shown in image below.

• And also left to solution explorer a window will open as shown in image below:

Click on image to enlarge

•  This window is called ORM (Object-relational mapping).This window has two parts. On the Left portion of this window we drag the table from the server explorer and drop here and on Right portion we drag the Function, procedures etc from the server explorer and drop here.

• Now connect your database with the visual studio .In Server explorer right click on the Data Connections and select Add connection as shown in image below

Click on image to enlarge

• Then a dialog box will open as shown in image below. Select your Server Name and database name. Then click Ok button. It will add and connect the Sql server database in the Server Explorer of Visual Studio.

Click on image to enlarge

• Now from Server explorer expand your connected database and select and drag and drop the EMP_TABLE to the left portion of the ORM window as shown in image below.

Click on image to enlarge

• It will automatically create the connection string in the web.config file as.

<connectionStrings>

    <add name="emp_dbConnectionString" connectionString="Data Source=LocalServer;Initial Catalog=emp_db;Integrated Security=True"

      providerName="System.Data.SqlClient" />

  </connectionStrings>

Note: Replace the Data Source and Initial catalog as per your application.

• Check emp.designer.cs file that is under emp.dbml in solution explorer. Here DataContext class is the main class which internally interacts with the SQL Engine.

• Now open Website menu-> select Add new item->Select Web Form and create the form. Design the page as:

HTML Source

<div>

    <fieldset style="width:400px">

    <legend>Save,Edit,Update,Delete,Bind Asp.net Listview</legend>   

    <table>

            <tr>

            <td colspan="2">

                    <asp:HiddenField ID="hdEmpId" runat="server" />

                </td>               

                <td rowspan="6" valign="top">

                    <asp:ListBox ID="lstEmp" runat="server" AutoPostBack="True" Height="131px"

                        Width="134px" onselectedindexchanged="lstEmp_SelectedIndexChanged"

                        style="margin-left: 0px; margin-bottom: 0px;"></asp:ListBox>

                </td>

            </tr>

            <tr>

                <td width="90px">

                    Emp Name</td>

                <td>

                    <asp:TextBox ID="txtEmpName" runat="server" Width="181px"></asp:TextBox>

                </td>

            </tr>

            <tr>

                <td>

                    Age</td>

                <td>

                    <asp:TextBox ID="txtAge" runat="server" Width="181px"></asp:TextBox>

                </td>

            </tr>

            <tr>

                <td>

                    Department</td>

                <td>

                    <asp:TextBox ID="txtDept" runat="server" Width="181px"></asp:TextBox>

                </td>

            </tr>

            <tr>

                <td>

                    Salary</td>

                <td>

                    <asp:TextBox ID="txtSal" runat="server" Width="181px"></asp:TextBox>

                </td>

            </tr>

            <tr>

                <td>

                    &nbsp;</td>

                <td align="center">

                    <asp:Button ID="btnSave" runat="server" Text="Save" onclick="btnSave_Click" />

                    <asp:Button ID="btnUpdate" runat="server" Text="Update"

                        onclick="btnUpdate_Click" />

                    <asp:Button ID="btnDelete" runat="server" Text="Delete" onclick="btnDelete_Click" />

                    <br />

                    <asp:Button ID="btnDisplay" runat="server" Text="Display"

                        onclick="btnDisplay_Click" />

                 

                    <asp:Button ID="btnCancel" runat="server" Text="Cancel"

                        onclick="btnCancel_Click" />                 

                </td>

            </tr>

        </table>

        </fieldset>

    </div>

•  Now in the code behind file (default.aspx.cs) write the code as:    

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

public partial class _Default : System.Web.UI.Page

{

    empDataContext obj = new empDataContext();

    protected void Page_Load(object sender, EventArgs e)

    {

        if (!Page.IsPostBack)

        {

            DisplayEmpRecords();

        }

        btnUpdate.Enabled = false;

        btnDelete.Enabled =  false;

        btnDisplay.Enabled = false;

    }

    protected void btnSave_Click(object sender, EventArgs e)

    {

        EMP_TABLE db = new EMP_TABLE

        {           

            EMP_NAME = txtEmpName.Text,

            AGE= Convert.ToInt32(txtAge.Text),

            DEPT = txtDept.Text,

            SALARY =Convert.ToInt64(txtSal.Text)

        };

        obj.EMP_TABLEs.InsertOnSubmit(db);

        obj.SubmitChanges();

        DisplayEmpRecords();

        ClearControls();

    }

    protected void btnUpdate_Click(object sender, EventArgs e)

    {

        EMP_TABLE db = obj.EMP_TABLEs.First(k => k.EMP_ID == Convert.ToInt32(hdEmpId.Value));

        db.EMP_NAME = txtEmpName.Text;

        db.AGE = Convert.ToInt32(txtAge.Text);

        db.DEPT = txtDept.Text;

        db.SALARY = Convert.ToInt64(txtSal.Text);

        obj.SubmitChanges();

        DisplayEmpRecords();

        ClearControls();

        btnSave.Enabled = true;

    }

    protected void btnDelete_Click(object sender, EventArgs e)

    {

        EMP_TABLE db = obj.EMP_TABLEs.First(k => k.EMP_ID == Convert.ToInt32(hdEmpId.Value));

        obj.EMP_TABLEs.DeleteOnSubmit(db);

        obj.SubmitChanges();

        DisplayEmpRecords();

        ClearControls();

        btnSave.Enabled = true;

    }

    protected void btnDisplay_Click(object sender, EventArgs e)

    {

        DisplayEmpRecords();

        btnDisplay.Enabled = true;

    }

    private void DisplayEmpRecords()

    {

        var q = from p in obj.EMP_TABLEs

                select new { p1 = p.EMP_ID, p2 = p.EMP_NAME };

        lstEmp.DataTextField = "p2";

        lstEmp.DataValueField = "p1";

        lstEmp.DataSource = q;

        lstEmp.DataBind();

    }

    private void ClearControls()

    {     

        txtEmpName.Text = string.Empty;

        txtAge.Text = string.Empty;

        txtDept.Text = string.Empty;

        txtSal.Text = string.Empty;

        hdEmpId.Value = string.Empty;       

        txtEmpName.Focus();       

    }

    protected void lstEmp_SelectedIndexChanged(object sender, EventArgs e)

    {

        var q = (from p in obj.EMP_TABLEs

                 where p.EMP_ID == Convert.ToInt32(lstEmp.SelectedValue)

                 select p).SingleOrDefault();

        if (q != null)

        {

            hdEmpId.Value = q.EMP_ID.ToString();

            txtEmpName.Text = q.EMP_NAME;

            txtAge.Text = q.AGE.ToString();

            txtDept.Text = q.DEPT;

            txtSal.Text = q.SALARY.ToString();

            btnUpdate.Enabled = true;

            btnDelete.Enabled = true;

            btnSave.Enabled = false;

        }        

    }

    protected void btnCancel_Click(object sender, EventArgs e)

    {

        ClearControls();

        lstEmp.Items.Clear();

        btnDisplay.Enabled = true;

        btnSave.Enabled = true;

    }   

}

Now over to you:

" I hope you have got How to perform the Save, Edit ,Update, Delete and bind functions in asp.net using LINQ to SQL and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linked in and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates."

• Tweet
• Share
• Share
• Share

Related Post