# Monday, 28 May 2007

Normally you have the possibility to choose Enable Selection in the Smart tag of a GridView control. This results in an extra column in front of the GridView with the text Select. But what if you don't want it like that but want to be able to use an image for example to select that row?

Well, a neat solution's to add a TemplateField and in the ItemTemplate place an ImageButton control. Why this one? Because it has a CommandName property available you can use. All you have to do is to set it to the predefined word Select.

Here's a small example to show what I mean:

<%@ Page Language="C#" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <script runat="server"> </script> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server"> <title>Untitled Page</title> </head> <body> <form id="form1" runat="server"> <div> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
DataSourceID="SqlDataSource1"> <Columns> <asp:CommandField ShowSelectButton="True" /> <asp:BoundField DataField="LastName" HeaderText="LastName" SortExpression="LastName" /> <asp:BoundField DataField="FirstName" HeaderText="FirstName" SortExpression="FirstName" /> <asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" /> <asp:TemplateField> <ItemTemplate> <asp:ImageButton ID="ImageButton1" CommandName="Select" runat="server"
ImageUrl="../App_Themes/Black/Images/bullet-1.gif" /> </ItemTemplate> </asp:TemplateField> </Columns> <SelectedRowStyle BackColor="Red" /> </asp:GridView> <asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>" SelectCommand="SELECT [LastName], [FirstName], [Title] FROM [Employees]"></asp:SqlDataSource> </div> </form> </body> </html>

Grz, Kris.

Monday, 28 May 2007 15:46:12 (GMT Daylight Time, UTC+01:00)  #    Disclaimer  |  Comments [0]  | 
# Wednesday, 02 May 2007

I just recently found out about a custom Split function for SQL Server 2000/2005 while I was reading this article: Designing Reports with SQL Server Reporting Services 2005. It mentioned a custom Split function made in T-SQL that could take in a delimited string with id's.

Today I had the need of such a functionality in my current project and luckely remembered where I saw it in the first place. Since I like it I thought I would create a small example for it. What it does is retrieve, from the Northwind database, some data of the Employees table and binds it to a GridView control. If you check several checkboxes and press the button you get, for those selected emloyees, to see everything that's in the database for them.

Grab the Split function from the article or get it here:

IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_NAME = N'Split' 
)
   DROP FUNCTION Split
GO
CREATE FUNCTION dbo.Split
(
    @ItemList NVARCHAR(4000), 
    @delimiter CHAR(1)
)
RETURNS @IDTable TABLE (Item VARCHAR(50))  
AS      

BEGIN    
    DECLARE @tempItemList NVARCHAR(4000)
    SET @tempItemList = @ItemList

    DECLARE @i INT    
    DECLARE @Item NVARCHAR(4000)

    SET @tempItemList = REPLACE (@tempItemList, ' ', '')
    SET @i = CHARINDEX(@delimiter, @tempItemList)

    WHILE (LEN(@tempItemList) > 0)
    BEGIN
        IF @i = 0
            SET @Item = @tempItemList
        ELSE
            SET @Item = LEFT(@tempItemList, @i - 1)
        INSERT INTO @IDTable(Item) VALUES(@Item)
        IF @i = 0
            SET @tempItemList = ''
        ELSE
            SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i)
        SET @i = CHARINDEX(@delimiter, @tempItemList)
    END 
    RETURN
END  
GO

All credits go the respective author of the formentioned article for making the function.

I created the following stored procedure that makes use of the Split function:

CREATE PROCEDURE USP_RetrieveInformationForSelectedEmployees
    @p_selectedEmployees    NVARCHAR(50)
AS
BEGIN
    SELECT * 
    FROM Employees
    WHERE EmployeeID in (SELECT Item FROM split(@p_selectedEmployees, ','))
END

And this is the ASP.NET webform:

 

   1:  <%@ Page Language="C#" %>
   2:  <%@ Import Namespace="System.Data.SqlClient" %>
   3:  <%@ Import Namespace="System.Web.Configuration" %>
   4:  <%@ Import Namespace="System.Data" %>
   5:   
   6:  <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
   7:      "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
   8:   
   9:  <script runat="server">
  10:   
  11:      protected void Button1_Click(object sender, EventArgs e)
  12:      {
  13:          RetrieveInformationForSelectedEmployees();
  14:      }
  15:   
  16:      private void RetrieveInformationForSelectedEmployees()
  17:      {
  18:          StringBuilder sb = new StringBuilder();
  19:   
  20:          // First loop through the GridView and see which
  21:          // employees were selected. I use the StringBuilder
  22:          // since the list could be a very long list.
  23:          foreach (GridViewRow row in GridView1.Rows)
  24:          {
  25:              if (((CheckBox)row.FindControl("CheckBox1")).Checked)
  26:              {
  27:                  sb.Append(GridView1.DataKeys[row.RowIndex].Value.ToString() + ',');
  28:              }
  29:          }
  30:   
  31:          using (SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings
  32:              ["NorthwindConnectionString"].ConnectionString))
  33:          {
  34:              using (SqlCommand cmd = new SqlCommand("USP_RetrieveInformationForSelectedEmployees", conn))
  35:              {
  36:                  cmd.CommandType = CommandType.StoredProcedure;
  37:                  cmd.Parameters.AddWithValue("@p_selectedEmployees", sb.ToString());
  38:   
  39:                  DataSet ds = new DataSet();
  40:                  SqlDataAdapter da = new SqlDataAdapter(cmd);
  41:                  da.Fill(ds);
  42:   
  43:                  GridViewResult.DataSource = ds;
  44:                  GridViewResult.DataBind();
  45:              }
  46:          }
  47:      }
  48:      
  49:  </script>
  50:   
  51:  <html xmlns="http://www.w3.org/1999/xhtml" >
  52:  <head runat="server">
  53:      <title>Untitled Page</title>
  54:  </head>
  55:  <body>
  56:      <form id="form1" runat="server">
  57:      <div>
  58:          <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
  59:              DataKeyNames="EmployeeID" DataSourceID="SqlDataSource1">
  60:              <Columns>
  61:                  <asp:TemplateField>
  62:                      <ItemTemplate>
  63:                          <asp:CheckBox ID="CheckBox1" runat="server" />
  64:                      </ItemTemplate>
  65:                  </asp:TemplateField>
  66:                  <asp:BoundField DataField="LastName" HeaderText="LastName" 
  67:                      SortExpression="LastName" />
  68:                  <asp:BoundField DataField="FirstName" HeaderText="FirstName" 
  69:                      SortExpression="FirstName" />
  70:                  <asp:BoundField DataField="Title" HeaderText="Title" 
  71:                      SortExpression="Title" />
  72:              </Columns>
  73:          </asp:GridView>
  74:          <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
  75:              ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
  76:              SelectCommand="SELECT [EmployeeID], [LastName], [FirstName], [Title] 
  77:              FROM [Employees] ORDER BY [LastName], [FirstName]">
  78:          </asp:SqlDataSource>
  79:          
  80:          <asp:Button ID="Button1" runat="server" Text="Retrieve data" OnClick="Button1_Click" />
  81:          <p>
  82:              <asp:GridView runat="server" ID="GridViewResult" />
  83:          </p>
  84:      </div>
  85:      </form>
  86:  </body>
  87:  </html>

In lines 23 upto 29 I loop over the rows in the GridView and see if the checkbox in the first column was checked. If it was, I append the DataKey value of it to the StringBuilder instance. On line 37 I pass the string of all the selected employee IDs. The stored procedure uses the Split function in the IN clause.

Have fun!

Grz, Kris.

kick it on DotNetKicks.com

Wednesday, 02 May 2007 19:06:40 (GMT Daylight Time, UTC+01:00)  #    Disclaimer  |  Comments [4]  | 
# Monday, 05 June 2006

I read this kind of questions multiple times on the $blank(forums.asp.net,ASP.NET forums). Most of the time it's suggested that one can use one of the events of the grid control they're using to use the FindControl method to find a Label control in one of the template columns and add the current row number to its Text property. I also used to do it like that when I started with ASP.NET.

However it can be done easier, and in markup, without the use of events, and best of all, the grid controls(1) of ASP.NET support the technique.


Figure 1: The GridView, DataGrid, Repeater and DataList controls shown to present the technique.

If Figure 1 you can see that it also supports pagination, which is shown in the GridView control.

Code says more than words so here goes:

    1 <%@ Page Language="C#" %>

    2 

    3 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

    4 

    5 <script runat="server">

    6 

    7     protected void DataGrid1_PageIndexChanged(object source, DataGridPageChangedEventArgs e)

    8     {

    9         DataGrid1.CurrentPageIndex = e.NewPageIndex;

   10         DataGrid1.DataBind();

   11     }

   12 </script>

   13 

   14 <html xmlns="http://www.w3.org/1999/xhtml" >

   15 <head runat="server">

   16     <title>Autonumbering grid controls</title>

   17 </head>

   18 <body>

   19     <form id="form1" runat="server">

   20     <div>

   21         GridView</div>

   22         <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"

   23             AutoGenerateColumns="False" DataSourceID="SqlDataSource1" PageSize="5">

   24             <Columns>

   25             <asp:TemplateField>

   26                 <ItemTemplate>

   27                     <%# Container.DataItemIndex + 1 %>

   28                 </ItemTemplate>

   29             </asp:TemplateField>

   30                 <asp:BoundField DataField="CategoryName" HeaderText="CategoryName" SortExpression="CategoryName" />

   31                 <asp:BoundField DataField="Description" HeaderText="Description" SortExpression="Description" />

   32             </Columns>

   33         </asp:GridView>

   34         <br />

   35         DataGrid

   36         <br />

   37         <asp:DataGrid runat="server" ID="DataGrid1" DataSourceID="SqlDataSource1" AllowPaging="True" OnPageIndexChanged="DataGrid1_PageIndexChanged" PageSize="5">

   38             <Columns>

   39                 <asp:TemplateColumn>

   40                     <ItemTemplate>

   41                         <%# Container.DataSetIndex + 1 %>

   42                     </ItemTemplate>

   43                 </asp:TemplateColumn>

   44             </Columns>

   45         </asp:DataGrid><br />

   46         Repeater<br />

   47         <asp:Repeater ID="Repeater1" runat="server" DataSourceID="SqlDataSource1">

   48             <ItemTemplate>

   49                 <span style="margin-right:20px;"><%# Container.ItemIndex + 1 %></span>

   50                 <span><%# Eval("CategoryName") %> <%# Eval("Description") %></span><br />

   51             </ItemTemplate>

   52         </asp:Repeater><br />

   53         DataList

   54         <br />

   55         <asp:DataList ID="DataList1" runat="server" DataSourceID="SqlDataSource1">

   56             <ItemTemplate>

   57                 <%# Container.ItemIndex + 1 %>

   58                 CategoryName:

   59                 <asp:Label ID="CategoryNameLabel" runat="server" Text='<%# Eval("CategoryName") %>'>

   60                 </asp:Label><br />

   61                 Description:

   62                 <asp:Label ID="DescriptionLabel" runat="server" Text='<%# Eval("Description") %>'>

   63                 </asp:Label><br />

   64                 <br />

   65             </ItemTemplate>

   66         </asp:DataList>

   67         <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"

   68             SelectCommand="SELECT [CategoryName], [Description] FROM [Categories]"></asp:SqlDataSource>

   69     </form>

   70 </body>

   71 </html>

For simplicity I used the SqlDataSource control that ships with ASP.NET 2.0 and used the Northwind database (lines 67 - 68). The following lines are of importance: 27, 41, 49 and 57. The + 1 is added each time because of the zero based index.

As you can see, it's each time a very simple addition to the markup of the grid control you're using but it adds that nice extra touch of information that endusers like to see.

Grz, Kris.

(1): I tested it upon the GridView, DataGrid, DataList and Repeater controls.

kick it on DotNetKicks.com

Monday, 05 June 2006 15:27:28 (GMT Daylight Time, UTC+01:00)  #    Disclaimer  |  Comments [2]  |