Paging and Sorting in GridView without using Datasource control

Paging and Sorting in Gridview can be enabled in an ASP.NET by handling the PageIndexChanging and Sorting events of the Gridview

ASP.NET

 

In the web page add the gridview from the toolbox

<asp:GridView
ID=”gridView”
OnPageIndexChanging=”gridView_PageIndexChanging” 

OnSorting=”gridView_Sorting”
runat=”server” />

 

Code Behind

 

I have a function ConvertSortDirection to display the direction of sort.

private string ConvertSortDirection(SortDirection sortDireciton)
{
   string newSortDirection = String.Empty;

   switch (sortDirection)
   {
      case
SortDirection.Ascending:
         newSortDirection = “ASC”;
         break;

      case
SortDirection.Descending:
         newSortDirection = “DESC”;
         break;
   }

   return newSortDirection
}

 

PageIndexChanging

 

protected void gridView_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
   gridView.PageIndex = e.NewPageIndex;
   gridView.DataBind();
}

 

Sorting

protected void gridView_Sorting(object sender, GridViewSortEventArgs e)
{
   DataTable dataTable = gridView.DataSource as
DataTable;

   if (dataTable != null)
   {
      DataView dataView = new
DataView(dataTable);
      dataView.Sort = e.SortExpression + ” “ + ConvertSortDirectionToSql(e.SortDirection);

      gridView.DataSource = dataView;
      gridView.DataBind();
   }
}

 

Happy coding J

Deleting Multiple Rows in a GridView

 
 

A gridview allows us to delete only a single row at a time. We will extend this functionality to select multiple rows and delete all of the selected rows in a single stroke. In this article, I assume that you are aware of creating asp.net web applications and have worked with gridview.

The sample makes use of the Northwind database. We will be pulling data from the Employee table. For this sample to work, drop all the Foreign Key relationships on the Employee Table. To do so, in Sql Server Management Studio, browse to the Northwind database and open the Employee table in design view. Right click in the Table designer on the right hand side and choose ‘Relationships’. Select all the relationships like FK_Orders_Employees,  FK_EmployeeTerritories_Employees etc and delete them. This step is necessary as we will get a constraint violation exception if we do not do so.

Once we are through with the task of removing the relationships in the Employee table, let us explore the steps to create a gridview with functionality to delete multiple rows at a time.

Perform the following steps :

Step 1: Create an .aspx page and add a GridView and a SqlDataSource control to it.

Step 2: Configure the connection of SqlDataSource to point to the Northwind database.  Create queries for the Select and Delete commands. The resultant code will look similar as given below :

<asp:SqlDataSource
ID=”SqlDataSource1″
Runat=”server”

    SelectCommand=”SELECT EmployeeID, LastName, City FROM Employees”

    DeleteCommand=”DELETE FROM Employees WHERE [EmployeeID] = @EmployeeID”

    ConnectionString=”<%$ ConnectionStrings:NorthwindConnectionString %>
>

       <DeleteParameters>

           <asp:Parameter
Name=”EmployeeID”
/>

       </DeleteParameters>

</asp:SqlDataSource>                                   

Step 3: Once the SqlDataSource has been configured, bind the gridview with this data source.

Step 4: To create a checkbox in each row, follow these steps:

1.    Create a TemplateField inside the <Columns> to add custom content to each column.

2.    Inside the TemplateField, create an ItemTemplate with a CheckBox added to it.

<asp:TemplateField>

       <ItemTemplate>

             <asp:CheckBox
ID=”chkRows”
runat=”server”/>

      </ItemTemplate>

 </asp:TemplateField>

This will add a checkbox to each row in the grid.

Step 5: Add a button control, and rename it to btnMultipleRowDelete.

The resultant markup in the design view will look similar to the code below :

<asp:GridView
ID=”GridView1″
runat=”server”
AutoGenerateColumns=”False”
DataKeyNames=”EmployeeID” DataSourceID=”SqlDataSource1″>

  
 

   <Columns>

      <asp:TemplateField>

          <ItemTemplate>

            <asp:CheckBox
ID=”cbRows”
runat=”server”/>

          </ItemTemplate>

       </asp:TemplateField>

 
 

<asp:BoundField
DataField=”EmployeeID”
HeaderText=”EmployeeID”
InsertVisible=”False” ReadOnly=”True”
SortExpression=”EmployeeID”
/>

<asp:BoundField
DataField=”LastName”
HeaderText=”LastName”
SortExpression=”LastName”
/>

<asp:BoundField
DataField=”City”
HeaderText=”City”
SortExpression=”City”
/>

   </Columns>

</asp:GridView>

 
 

<asp:SqlDataSource
ID=”SqlDataSource1″
Runat=”server”

SelectCommand=”SELECT EmployeeID, LastName, City FROM Employees”

DeleteCommand=”DELETE FROM Employees WHERE [EmployeeID] = @EmployeeID”

ConnectionString=”<%$ ConnectionStrings:NorthwindConnectionString %>
>

   <DeleteParameters>

       <asp:Parameter
Name=”EmployeeID”
/>

   </DeleteParameters>

</asp:SqlDataSource>

 
 

<asp:Button

   ID=”btnMultipleRowDelete”

   OnClick=”btnMultipleRowDelete_Click”

   runat=”server”

   Text=”Delete Rows”
/>

 
 

In Code behind file (.cs) for C# and (.vb) for VB.NET, code the button click event. Our code will first loop through all the rows in the GridView. If a row is checked, the code retrieves the EmployeeID and passes the selected value to the Delete Command.

C#

 
 

 
 

protected
void btnMultipleRowDelete_Click(object sender, EventArgs e)

{

        // Looping through all the rows in the GridView

        foreach (GridViewRow row in GridView1.Rows)

        {

            CheckBox checkbox = (CheckBox)row.FindControl(“cbRows”);

 
 

            //Check if the checkbox is checked.

//value in the HtmlInputCheckBox’s Value property is set as the //value of the delete command’s parameter.

            if (checkbox.Checked)

            {

                // Retreive the Employee ID

int employeeID = Convert.ToInt32(GridView1.DataKeys[row.RowIndex].Value);

// Pass the value of the selected Employye ID to the Delete //command.

SqlDataSource1.DeleteParameters[“EmployeeID”].DefaultValue = employeeID.ToString();

                SqlDataSource1.Delete();

            }

        }

 }

 
 

Run the code, and select a few rows in the grid. ‘Delete Rows’ button, the selected rows get deleted. Rather than deleting rows one at a time, deleting them in a batch is a good practice. I would encourage you to read Scott Mitchell’s article for the same.

 
 

Conclusion

 
 

By default, the gridview provides the functionality to delete a single row at a time. In this article, we explored how to extend the functionality of the grid view and delete multiple rows. I hope this article was useful and I thank you for viewing it.

Add, Update, Delete Records in a Gridview using SqlDataSource

By default, the GridView control doesn’t have support for inserting new records. However you can use the built-in edit or delete functionality of the GridView control. Let us explore how to insert new records and Update and Delete existing records in Gridview. Just copy and paste the code in your project. We will be using the ‘Categories’ table in the ‘Northwind’ database.

GridView.aspx

        <asp:GridView
ID=”GridView1″
runat=”server”
AutoGenerateColumns=”False”
DataKeyNames=”CategoryID”

            DataSourceID=”SqlDataSource1″
ShowFooter=”true”
AllowPaging=”True”
AllowSorting=”True”
OnRowCommand=”GridView1_RowCommand”>

            <Columns>

           
 

                <asp:CommandField
ShowDeleteButton=”True”
ShowEditButton=”True”/>               

                <asp:TemplateField
HeaderText=”CategoryID”
InsertVisible=”False”
SortExpression=”CategoryID”>

                    <EditItemTemplate>

                        <asp:Label
ID=”Label1″
runat=”server”
Text=’<%# Eval(“CategoryID”) %>‘></asp:Label>

                    </EditItemTemplate>

                    <ItemTemplate>

                        <asp:Label
ID=”Label1″
runat=”server”
Text=’<%# Bind(“CategoryID”) %>‘></asp:Label>

                    </ItemTemplate>                  

                </asp:TemplateField>

                <asp:TemplateField
HeaderText=”CategoryName”
SortExpression=”CategoryName”>

                    <EditItemTemplate>

                        <asp:TextBox
ID=”TextBox1″
runat=”server”
Text=’<%# Bind(“CategoryName”) %>‘></asp:TextBox>

                    </EditItemTemplate>

                    <ItemTemplate>

                        <asp:Label
ID=”Label2″
runat=”server”
Text=’<%# Bind(“CategoryName”) %>‘></asp:Label>

                    </ItemTemplate>

                    <FooterTemplate>

                        <asp:TextBox
ID=”CategoryNameTextBox”
Runat=”server”></asp:TextBox>

                        </FooterTemplate>

                </asp:TemplateField>

                <asp:TemplateField
HeaderText=”Description”
SortExpression=”Description”>

                    <EditItemTemplate>

                        <asp:TextBox
ID=”TextBox2″
runat=”server”
Text=’<%# Bind(“Description”) %>‘></asp:TextBox>

                    </EditItemTemplate>

                    <ItemTemplate>

                        <asp:Label
ID=”Label3″
runat=”server”
Text=’<%# Bind(“Description”) %>‘></asp:Label>

                    </ItemTemplate>

                    <FooterTemplate>

                        <asp:TextBox
ID=”DescriptionTextBox”
Runat=”server”></asp:TextBox>

                    </FooterTemplate>                 

                </asp:TemplateField>

                <asp:templatefield>                  

                        <footertemplate>

                              <asp:linkbutton
id=”btnNew”
runat=”server”
commandname=”New”
text=”New”
/>

                        </footertemplate>

                  </asp:templatefield>

               
 

            </Columns>

        </asp:GridView>

 

//

        <asp:SqlDataSource
ID=”SqlDataSource1″
runat=”server”
ConnectionString=”Data Source=SUPROTIM;Initial Catalog=Northwind;Integrated Security=True”

            DeleteCommand=”DELETE FROM [Categories] WHERE [CategoryID] = @CategoryID”
InsertCommand=”INSERT INTO [Categories] ([CategoryName], [Description]) VALUES (@CategoryName, @Description)”

            ProviderName=”System.Data.SqlClient”
SelectCommand=”SELECT [CategoryID], [CategoryName], [Description] FROM [Categories]”

            UpdateCommand=”UPDATE [Categories] SET [CategoryName] = @CategoryName, [Description] = @Description WHERE [CategoryID] = @CategoryID”>

            <DeleteParameters>

                <asp:Parameter
Name=”CategoryID”
Type=”Int32″
/>

            </DeleteParameters>

            <UpdateParameters>

                <asp:Parameter
Name=”CategoryName”
Type=”String”
/>

                <asp:Parameter
Name=”Description”
Type=”String”
/>

                <asp:Parameter
Name=”CategoryID”
Type=”Int32″
/>

            </UpdateParameters>

            <InsertParameters>

                <asp:Parameter
Name=”CategoryName”
Type=”String”
/>

                <asp:Parameter
Name=”Description”
Type=”String”
/>

            </InsertParameters>

        </asp:SqlDataSource>

   
 

 
 

GridView.aspx.cs

protected
void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)

    {

        SqlConnection conn = new
SqlConnection(

                    ConfigurationManager.ConnectionStrings[“NorthwindConnectionString”].ConnectionString);

        try

        {

            if (e.CommandName.Equals(“New”))

            {

                LinkButton btnNew = e.CommandSource as
LinkButton;

                GridViewRow row = btnNew.NamingContainer as
GridViewRow;

                if (row == null)

                {

                    return;

                }

                TextBox txtCatName = row.FindControl(“CategoryNameTextBox”) as
TextBox;

                TextBox txtDescription = row.FindControl(“DescriptionTextBox”) as
TextBox;               

                SqlCommand cmd = new
SqlCommand(

                    “INSERT INTO [Categories] ([CategoryName], [Description]) VALUES (@CategoryName, @Description)”,

                    conn);

                cmd.Parameters.AddWithValue(“CategoryName”, txtCatName.Text);

                cmd.Parameters.AddWithValue(“Description”,txtDescription.Text);

                conn.Open();

                if (cmd.ExecuteNonQuery() == 1)

                {

                    GridView1.DataBind();

                }

            }

        }

        catch (Exception ex)

        {

 
 

        }

        finally

        {

            conn.Close();

        }

    }

Web.config

<connectionStrings>

            <addname=NorthwindConnectionStringconnectionString=Data Source =.;Integrated Security = SSPI; Initial Catalog=Northwind;/>

           
 

</connectionStrings>

Export GridView To Excel

Today it is common trying to export from Gridview in ASP.NET to excel right. Please find below the piece of code that can help you export Gridview details to excel.

C#

protected
void Button1_Click(object sender, EventArgs e)

    {

        Response.AddHeader(“content-disposition”, “attachment;filename=FileName.xls”);

        Response.Charset = String.Empty;

        Response.ContentType = “application/vnd.xls”;

        System.IO.StringWriter sw = new System.IO.StringWriter();

        System.Web.UI.HtmlTextWriter hw = new
HtmlTextWriter(sw);

        GridView1.RenderControl(hw);

        Response.Write(sw.ToString());

        Response.End();

    }

 
 

A scrollable ASP.NET Gridview with a fixed header

 

Introduction

ASP.NET Gridview has the provision of providing paging, set a particular pagesize etc.. But at times our clients are too adamant to demand for scrollable gridview with a steady header.Gridview as such doesn’t have a scrollable feature . But still a work around is possible. This is being discussed in this article that provides us a convenient scrollable grid with a steady header.

STEP 1- Scrollable GridView

Add the gridview into a asp:Panel control

<asp:Panel
runat=”server”
ScrollBars=”auto”
Width=”500px”>

            <asp:GridView
ID=”GridView1″
runat=”server”>

            </asp:GridView>

        </asp:Panel>

This Panel has a scroll bar feature so it tends to scroll the grid. But our problem is not yet solved. What is the next issue- Constant header.
The above code makes the whole panel content to scroll. But most of the application needs a constant header. Is it possible? This is explained in the next section 

STEP 2- Stable header
This can be done in 2 ways

1. Add the css to the Panel

01..fixedHeader

02. {

03. overflow: auto;

04. height: 145px;

05.  

06. }

07. table th

08. {

09.   

10. position: relative;

11.   

12. }

2. In the above way we have added style to the Panel as a whole.
Again we can achieve stable header by adding header style for the grid.
This is the style:

1..fixedHeader

2. {

3. background-color: aqua;

4. position: relative;

5. top: expression(this.offsetParent.scrollTop);

6. }


And the HTML becomes 

1.<HeaderStyle CssClass=”fixedHeader”
/>

 
 

Conclusion
This article deals with a scrollable gridview that has a stable header.Though this is not possible as such as the gridview doesnot have a scrollable property it can be done by a work around. I hope it helps.Happy Coding.

Thank you

Baimey

How to use Masked Edit Extender in ASP.NET

 

Introduction

In this article, I will show you step by step procedure, how to use a Masked Edit Extender
In ASP.NET using Visual Studio 2005 or Visual Studio 2008.

The use of Masked Edit Extender it that you are not require to type any number of character you have to just select from the up down button…it will change dynamically when you click the button,

To follow this code sample, you must use Visual Studio 2005 or Visual Studio 2008.

Note : I have used to comment HTML part of the code.

Note: Masked Edit Extender is a part of AjaxControlToolkit.


To attach right click on the toolbox under Ajax Extensions and select Choose Items – > Browse Button – > select the AjaxControlToolkit.dll. If you are using Visual Studio 2008, you may not need AJAX Toolkit but if you are using Visual Studio 2005, you will need to download it from http://www.msdn.com/ or http://www.asp.net/.

Here is step by step to implement a sample using mask extender.

Step 1.

Start -> All Programs -> Visual Studio 2005 or Visual Studio 2008

Step 2.

Now go to File Menu -> New -> Web Site

Step 3.

Under Visual Studio Installed Template-> Choose ASP.NET WEB SITE -> Choose File System from the location combo box -> Set the path by the browse button – > Choose the language from the Language ComboBox (Visual C# , Visual Basic , J #) Choose Visual C#.

Step 4.

Click on the OK Button.

This is the source code window and in this page you will se this code.


Type your webpage title here  

<form id=””form1?”
runat=””server””>

</form>

See here is a tab named Design in the bottom of this page.

Step 5.

 
 

Click on this tab and you will see a blank web page where you can drag any control from the toolbox (which is in the left side of this window).


Step 6.

Now drag controls under the AJAX Extensions.

First control you are going to drag and drop on the page is – Script Manager.

First register the ajaxtoolkit in the page. Thena dd the code below:

Enter the details below in
the textboxes and you will see how the masked control

Works

MaskType=”Number”
Mask=”999″
MessageValidatorTip=”true”
AcceptNegative=”None”

InputDirection=”RightToLeft”
ErrorTooltipEnabled=”true”
>

ControlToValidate=”TextBox2″
ControlExtender=”MaskedEditExtender1″

MinimumValue=”1″
MaximumValue=”105″
MinimumValueMessage=”Please enter correct

roll number” MaximumValueMessage=”Invalid roll number! The highest roll

number is
105″ Display=”Dynamic” IsValidEmpty=”false”

InvalidValueMessage=”The roll number does not exist”
EmptyValueMessage=”The roll number is not entered”>

Step 7.

Now run your web site by Ctrl + F5


Step 8.

When you run the Masked Edit Extender will seem like this following

Now in the Masked text boxes.you can’t enter characters or hexadecimal values you can only enter digits and here will be some restrictions. which the masked text box will follow.

Thank
you

Baimey

Introduction of the cookies in ASP.NET

Introduction

Cookies provide a useful means in Web applications to store user-specific information. For example, when a user visits your site, you can use cookies to store user preferences or other information. When the user visits your Web site another time, the application can retrieve the information it stored earlier.

 
 

There are three types of classes which allows to work with cookies.

1- HttpCookie  : This cookie property allows to create and Manupulate individual Http cookies in asp.net
2- HttpRequest  : This property allows to access cookies from client machine
3- HttpResponse  : This property allows to save and create cookie in client machine.

HttpResponse and HttpRequest

 
 

How To create cookies in asp.net ?


Response property is used to create cookies.

So here is simple cookie using Response.

 
 

1.Response.Cookies(“Name”).Value = “Jason Bourne”;

 
 

This line is saving the cookie where the cookie name is”Name” and the value is “Jason Bourne” in client machine.

 
 

How to set the time of expiration of cookie

 
 

1.Response.Cookies(“Name”).Expires = DateTime.Now.AddDays(1);

 
 

This line is using to set the expire date of this cookie which is 1 day.

 
 

How to retrieve values from cookies

 
 

1.string
name = this.context.Request.Cookies(“Name”).Value;

 
 

This line of code is retrieving the value from the cookie and stroing the string variable name and the value of cookie is “Jason Bourne” which we were saved before.

If this cookie dosen’t have any value then the string will return empty.

 
 

HttpCookie

HttpCookie is also a very useful class in creating and retrieving the values from cookies.

 
 

1.HttpCookie cook = new
HttpCookie(“Name”);

2.cook.Value      = “Jason Bourne”;

3.cook.Expires    = DateTime.Now.AddDays(1);

4.Response.Cookies.Add(cook);


This line is using to set the expire date of this cookie which is 1 day.


Conclusion

We have learned how to create cookies, Retrieve the values from Cookies, How to set the expire time of cookies etc.

Thank You

 
 

Baimey