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

Insert,Edit,Update,Delete and Bind Asp.Net ListBox
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.
Insert,Edit,Update,Delete and Bind Asp.Net ListBox
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.

  • Insert,Edit,Update,Delete and Bind Asp.Net ListBox
    Click on image to enlarge
  • A dialog box will appear as shown in image below:
Insert,Edit,Update,Delete and Bind Asp.Net ListBox

  •  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.
Insert,Edit,Update,Delete and Bind Asp.Net ListBox

  • And also left to solution explorer a window will open as shown in image below:
Insert,Edit,Update,Delete and Bind Asp.Net ListBox
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

Insert,Edit,Update,Delete and Bind Asp.Net ListBox
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.
Insert,Edit,Update,Delete and Bind Asp.Net ListBox
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.
Insert,Edit,Update,Delete and Bind Asp.Net ListBox
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."
Previous
Next Post »

4 comments

Click here for comments
Unknown
admin
November 20, 2013 ×

hello sir, i question to ask u.have a form to insert and edit it. i already successful to insert it.but when i want to update it using store procedure then the error came out.
here is my SP:
ALTER PROCEDURE [dbo].[SP_TESTLOGUPDATE1]
@Call_logID int,
@Log_TypeApp varchar(50),
@Log_TypeCust varchar(50),
@Log_name varchar(100),
@Log_code char(20),
@Log_ContactNo varchar(50),
@Log_Branch varchar(50),
@mRegistration int,
@mTrade int,
@mWatchList int,
@mChart int,
@mNews int,
@mOddLot int,
@mLoginIdRecovery int,
@mPasswordRecovery int,
@mTradingPinRecoveroy int,
@mActivateLogin int,
@mActivateTrading int,
@mResendValidationEmail int,
@mPriceFeed int,
@mOrderBook int,
@mClientPortfolio int,
@mOnlineReports int,
@mOthers int,
@Log_Remarks varchar(100),
@Log_Action varchar(100),
@Log_Status varchar(50),
@Log_EntryBy varchar(100)
--@EntryTime datetime
AS SET NOCOUNT ON
begin
UPDATE [dbo].[Call_Log_Service]
SET Log_TypeCust= @Log_TypeCust,
Log_Name = @Log_name,
Log_code= @Log_code,
Log_ContactNo =@Log_ContactNo,
Log_Branch= @Log_Branch ,
Log_TypeApp= @Log_TypeApp,
Log_inquiryReg = (mRegistration + mTrade + mWatchList +mChart+mNews+mOddLot ),
Log_ActionRecovery = (mLoginIdRecovery + mPasswordRecovery + mTradingPinRecoveroy +mActivateLogin+mActivateTrading+mResendValidationEmail),
Log_Technical = (mPriceFeed +mOrderBook +mClientPortfolio +mOnlineReports +mOthers),
mRegistration= @mRegistration,
mTrade= @mTrade ,
mWatchList= @mWatchList,
mChart= @mChart ,
mNews = @mNews ,
mOddLot= @mOddLot ,
mLoginIdRecovery= @mLoginIdRecovery ,
mPasswordRecovery= @mPasswordRecovery ,
mTradingPinRecoveroy = @mTradingPinRecoveroy ,
mActivateLogin = @mActivateLogin ,
mActivateTrading = @mActivateTrading ,
mResendValidationEmail = @mResendValidationEmail ,
mPriceFeed = @mPriceFeed ,
mOrderBook = @mOrderBook ,
mClientPortfolio = @mClientPortfolio ,
mOnlineReports= @mOnlineReports ,
mOthers = @mOthers ,
Log_Remarks = @Log_Remarks,
Log_Action= @Log_Action,
Log_Status = @Log_Status ,
Log_EntryBy = @Log_EntryBy
--EntryTime = @EntryTime
where Call_logID =@Call_logID
end

Reply
avatar
November 20, 2013 ×

Hi, what error you are facing in this stored procedure..

Reply
avatar
Shankar
admin
July 22, 2014 ×

Good job Lalit...

Reply
avatar
July 25, 2014 ×

thanks Shankar..:)

Reply
avatar

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily..