LINQ Demo with ASP.NET Web Application

Download source - 23.95 KB

Introduction

This is an ASP.NET Linq demo.

Background

If you have knowledge of bind datagrid and add, update and delete record through the ASP.NET application and no idea about the Linq integration, then this article will give you an idea of how to use Linq.

Using the Code

This is a sample application for beginner users who want to start the Linq implementation with ASP.NET.

Step 1

Create a new Web application project:

Step 2

Create a Web Form:

<asp:Button ID="btnView" runat="server" Text="View" OnClick="btnView_Click" />
    <asp:Button ID="btnAddNew" runat="server" Text="Add New" OnClick="btnAddNew_Click" />
    <table id="tblForm" runat="server">
        <tr>
            <td>Country Name:</td>
            <td><asp:TextBox ID="txtCountryName" runat="server"></asp:TextBox></td>
        </tr>
        <tr>
            <td>Country Code:</td>
            <td><asp:TextBox ID="txtCountryCode" runat="server"></asp:TextBox></td>
        </tr>
        <tr>
            <td colspan="2">
                <asp:Button ID="btnSave" runat="server" Text="Add" 
		OnClick="btnSave_Click" />
                <asp:Button ID="btnUpdate" runat="server" Text="Update" 
		OnClick="btnUpdate_Click" />
                <asp:Button ID="btnCancel" runat="server" Text="Cancel" 
		OnClick="btnCancel_Click" />
            </td>
        </tr>
    </table>
    <table id="tblGrid" runat="server">
        <tr>
            <td><asp:Label ID="lblID" runat="server" Visible="false"></asp:Label>
                <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" 
		OnRowCommand="GridView1_RowCommand">
                    <Columns>
                        <asp:BoundField DataField="Countryid" HeaderText="ID" />
                        <asp:BoundField DataField="CountryName" 
			HeaderText="Country Name" />
                        <asp:TemplateField>
                            <ItemTemplate>
                                <asp:LinkButton ID="lnkEdit" runat="server" 
				CommandArgument='<%# Eval("Countryid")%>'
                                    CommandName="cmdEdit" Text="Edlit"></asp:LinkButton>
                                  ---  
                                <asp:LinkButton ID="lnkDelete" runat="server" 
				CommandArgument='<%# Eval("Countryid")%>'
                                    CommandName="cmdDelete" Text="Delete">
			     </asp:LinkButton>
                            </ItemTemplate>
                        </asp:TemplateField>
                    </Columns>
                </asp:GridView>
            </td>
        </tr>
    </table>

Step 3

Add a new item – Linq to SQL classes:

Step 4

Create a database table -- tblCountry:

CREATE TABLE [dbo].[tblCountry](
	[Countryid] [int] IDENTITY(1,1) NOT NULL,
	[CountryName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[CountryCode] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Status] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	
 CONSTRAINT [PK_tblCountry] PRIMARY KEY CLUSTERED 
(
	[Countryid] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Step 5

Connect the database to the project:

Step 6

Select the table for Linq table integration:

Step 7

Set database connection string in web.config file:

<appSettings>
  <add key="constr" value="Data Source = machinename\SQLEXPRESS;
	Initial Catalog=test;User ID=tuser; password=tpass"/>
</appSettings>

Step 8

Write Insert record method:

protected void btnSave_Click(object sender, EventArgs e)
        {
            using(DLCountryiesDataContext countries = new DLCountryiesDataContext())
            {
                tblCountry country = new tblCountry
                 {
                     CountryName = txtCountryName.Text.Trim(),
                     CountryCode = txtCountryCode.Text.Trim(),
                     Status = "Active"
                 };
                countries.Connection.ConnectionString = 
		System.Configuration.ConfigurationManager.AppSettings["constr"];
                countries.tblCountries.InsertOnSubmit(country);
                countries.SubmitChanges();
                ViewData();
            }
        }

Step 9

Write method View Records in Gridview:

private void ViewData()
        {
            ClearForm();
            DLCountryiesDataContext db = new DLCountryiesDataContext();
            db.Connection.ConnectionString = 
		System.Configuration.ConfigurationManager.AppSettings["constr"];
            var country =
                from c in db.tblCountries
                select c;

            GridView1.DataSource = country;
            GridView1.DataBind();
        }

Step 10

Write methods for edit and delete records:

protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
        {
            if (e.CommandName == "cmdEdit")
            {
                ClearForm();
                DLCountryiesDataContext db = new DLCountryiesDataContext();
                db.Connection.ConnectionString = 
		System.Configuration.ConfigurationManager.AppSettings["constr"];
                var country =
                    from c in db.tblCountries
                    where (c.Countryid == Convert.ToInt32(e.CommandArgument.ToString()))
                    select c;
                lblID.Text = e.CommandArgument.ToString();
                foreach (tblCountry cntry in country)
                {
                    txtCountryName.Text = cntry.CountryName;
                    txtCountryCode.Text = cntry.CountryCode;
                }
                btnSave.Visible = false;
                btnUpdate.Visible = true;
            }
            if (e.CommandName == "cmdDelete")
            {
                DLCountryiesDataContext db = new DLCountryiesDataContext();
                db.Connection.ConnectionString = 
		System.Configuration.ConfigurationManager.AppSettings["constr"];
                var country =
                    from c in db.tblCountries
                    where (c.Countryid == Convert.ToInt32(e.CommandArgument.ToString()))
                    select c;

                db.tblCountries.DeleteAllOnSubmit(country);
                db.SubmitChanges();
                ViewData();
            }
        }

Step 11

Write method for update record:

protected void btnUpdate_Click(object sender, EventArgs e)
        {
            DLCountryiesDataContext db = new DLCountryiesDataContext();
            db.Connection.ConnectionString = 
		System.Configuration.ConfigurationManager.AppSettings["constr"];

            var country = db.tblCountries.Single
		(p => p.Countryid == Convert.ToInt32(lblID.Text));
            country.CountryName = txtCountryName.Text.Trim();
            country.CountryCode = txtCountryCode.Text.Trim();
            db.SubmitChanges();
            ViewData();
        }

Step 12

Common methods for page (it's not mandatory to use in page):

protected void btnView_Click(object sender, EventArgs e)
        {         
            ViewData();
            HideForm();
        }

        protected void btnAddNew_Click(object sender, EventArgs e)
        {
            ShowForm();
            btnSave.Visible = true;
            btnUpdate.Visible = false;            
        }

        protected void btnCancel_Click(object sender, EventArgs e)
        {   
            ViewData();
            HideForm();
        }
        
        private void HideForm()
        {
            tblForm.Visible = false;
            tblGrid.Visible = true;
            btnView.Visible = false;
            btnAddNew.Visible = true;            
        }
        
        private void ShowForm()
        {
            tblForm.Visible = true;
            tblGrid.Visible = false;
            btnView.Visible = true;
            btnAddNew.Visible = false;
        }
        
        private void ClearForm()
        {
            txtCountryCode.Text = "";
            txtCountryName.Text = "";
        }