Friday, October 5, 2012

ASP.NET - Almost complete use of Datagrid

I was on a need of creating datagrid object on my page with sorting, filtering, updating needs. You could make use of below example for some complex cases (Dropdown, Date selector update columns for relational data table updates, filters, multiple update queries)

    <table border="0" cellspacing="0">
        <tbody>
<tr>
        <th>Owner_Name</th>
        <th>OS</th>
        <th>Type</th>
        <th>Status</th>
        <th>Action Type</th>
        </tr>
<tr>
        <td><asp:dropdownlist appenddatabounditems="true" autopostback="true" datasourceid="dsPopulateOwner_Name" datatextfield="UNAME" datavaluefield="UID" font-size="11px" id="ddlOwner_Name" runat="server" width="130px">
            <asp:listitem text="*" value="%"></asp:listitem>
        </asp:dropdownlist>
        <asp:sqldatasource connectionstring="&lt;%$ ConnectionStrings:Connection %&gt;" id="dsPopulateOwner_Name" runat="server" selectcommand="SELECT
            Id as UID, Name as UNAME FROM tblUser"></asp:sqldatasource>
        </td>
        <td><asp:dropdownlist appenddatabounditems="true" autopostback="true" datasourceid="dsPopulateOS" datavaluefield="OS" font-size="11px" id="ddlOS" runat="server" width="130px">
            <asp:listitem text="All" value="%"></asp:listitem>
        </asp:dropdownlist>
        <asp:sqldatasource connectionstring="&lt;%$ ConnectionStrings:Connection %&gt;" id="dsPopulateOS" runat="server" selectcommand="SELECT
            DISTINCT OS FROM tblServer"></asp:sqldatasource></td>
        <td><asp:dropdownlist appenddatabounditems="true" autopostback="true" datasourceid="dsPopulateType" datavaluefield="Type" font-size="11px" id="ddlType" runat="server" width="130px">
            <asp:listitem text="All" value="%"></asp:listitem>
        </asp:dropdownlist>
        <asp:sqldatasource connectionstring="&lt;%$ ConnectionStrings:Connection %&gt;" id="dsPopulateType" runat="server" selectcommand="SELECT
            DISTINCT Type FROM tblServer"></asp:sqldatasource></td>
        <td><asp:dropdownlist appenddatabounditems="true" autopostback="true" datasourceid="dsPopulateStatus" datavaluefield="Status" font-size="11px" id="ddlStatus" runat="server" width="130px">
            <asp:listitem text="All" value="%"></asp:listitem>
        </asp:dropdownlist>
        <asp:sqldatasource connectionstring="&lt;%$ ConnectionStrings:Connection %&gt;" id="dsPopulateStatus" runat="server" selectcommand="SELECT
            DISTINCT Status FROM tblServer"></asp:sqldatasource></td>
        <td><asp:dropdownlist appenddatabounditems="true" autopostback="true" datasourceid="dsPopulateActionType" datavaluefield="ActionType" font-size="11px" id="ddlActionType" runat="server" width="130px">
            <asp:listitem text="All" value="%"></asp:listitem>
        </asp:dropdownlist>
        <asp:sqldatasource connectionstring="&lt;%$ ConnectionStrings:Connection %&gt;" id="dsPopulateActionType" runat="server" selectcommand="SELECT
            DISTINCT ActionType FROM tblServer"></asp:sqldatasource></td>
        </tr>
</tbody></table>
<asp:gridview allowsorting="True" autogeneratecolumns="False" cellpadding="3" datakeynames="Id" datasourceid="SqlDataSource1" id="GridView1" pageindex="1" pagesize="100" runat="server" width="95%">
        <alternatingrowstyle backcolor="#B6CFA5">
        <columns>
        <asp:templatefield headertext="Select">
        <itemtemplate>
        <asp:checkbox id="chkSelect" runat="server">
        </asp:checkbox></itemtemplate>
        </asp:templatefield>
            <asp:boundfield datafield="Id" headertext="ID" readonly="true" sortexpression="Id" visible="true">
            <asp:boundfield controlstyle-cssclass="cssWdth" datafield="Hostname" headertext="Hostname" sortexpression="Hostname">
            </asp:boundfield>
            <asp:boundfield controlstyle-cssclass="cssWdth" datafield="IP_Address" headertext="IP_Address" sortexpression="IP_Address">
            </asp:boundfield>
            <asp:templatefield headerstyle-horizontalalign="Left" headertext="Owner" sortexpression="Owner"> 
               <edititemtemplate> 
                <asp:dropdownlist appenddatabounditems="true" autopostback="false" cssclass="cssWdth" datasourceid="dsPopulateOwner_Name" datatextfield="UNAME" datavaluefield="UID" id="ddlEditOwner_Name" runat="server" selectedvalue="&lt;%# Bind(&quot;Owner&quot;) %&gt;">
                </asp:dropdownlist>
               </edititemtemplate>  
               <itemtemplate> 
                <asp:label id="lblOwner_Name" runat="server" text="&lt;%# Bind(&quot;Owner_Name&quot;) %&gt;"></asp:label> 
               </itemtemplate>
            </asp:templatefield>
            <asp:templatefield headerstyle-horizontalalign="Left" headertext="OS" sortexpression="OS"> 
               <edititemtemplate> 
                <asp:dropdownlist cssclass="cssWdth" datasourceid="dsPopulateOS" datatextfield="OS" datavaluefield="OS" id="ddlEditOS" runat="server" selectedvalue="&lt;%# Bind(&quot;OS&quot;) %&gt;">
                </asp:dropdownlist>
               </edititemtemplate>  
               <itemtemplate> 
                <asp:label id="lblOS" runat="server" text="&lt;%# Bind(&quot;OS&quot;) %&gt;"></asp:label> 
               </itemtemplate>
            </asp:templatefield>
            <asp:templatefield headerstyle-horizontalalign="Left" headertext="Type" sortexpression="Type"> 
               <edititemtemplate> 
                <asp:dropdownlist cssclass="cssWdth" datasourceid="dsPopulateType" datatextfield="Type" datavaluefield="Type" id="ddlEditType" runat="server" selectedvalue="&lt;%# Bind(&quot;Type&quot;) %&gt;">
                </asp:dropdownlist>
               </edititemtemplate>  
               <itemtemplate> 
                <asp:label id="lblType" runat="server" text="&lt;%# Bind(&quot;Type&quot;) %&gt;"></asp:label> 
               </itemtemplate>
            </asp:templatefield>
            <asp:templatefield headerstyle-horizontalalign="Left" headertext="Status" sortexpression="Status"> 
               <edititemtemplate> 
                <asp:dropdownlist cssclass="cssWdth" datasourceid="dsPopulateStatus" datatextfield="Status" datavaluefield="Status" id="ddlEditStatus" runat="server" selectedvalue="&lt;%# Bind(&quot;Status&quot;) %&gt;">
                </asp:dropdownlist>
               </edititemtemplate>  
               <itemtemplate> 
                <asp:label id="lblStatus" runat="server" text="&lt;%# Bind(&quot;Status&quot;) %&gt;"></asp:label> 
               </itemtemplate>
            </asp:templatefield>
            <asp:templatefield headerstyle-horizontalalign="Left" headertext="ActionType" sortexpression="ActionType"> 
               <edititemtemplate> 
                <asp:dropdownlist appenddatabounditems="true" autopostback="false" cssclass="cssWdth" datasourceid="dsPopulateActionType" datatextfield="ActionType" datavaluefield="ActionType" id="ddlEditActionType" runat="server" selectedvalue="&lt;%# Bind(&quot;ActionType&quot;) %&gt;">
                </asp:dropdownlist>
               </edititemtemplate>  
               <itemtemplate> 
                <asp:label id="lblActionType" runat="server" text="&lt;%# Bind(&quot;ActionType&quot;) %&gt;"></asp:label> 
               </itemtemplate>
            </asp:templatefield>
            <asp:templatefield headerstyle-horizontalalign="Left" headertext="ActionDate" sortexpression="ActionDate"> 
               <edititemtemplate> 
                <asp:textbox cssclass="cssWdth" id="TextBox2" runat="server" text="&lt;%# Bind(&quot;ActionDate&quot;) %&gt;"></asp:textbox>
                <ajaxtoolkit:calendarextender format="dd-MM-yyyy" id="CalendarExtender2" runat="server" targetcontrolid="TextBox2">
               </ajaxtoolkit:calendarextender></edititemtemplate>  
               <itemtemplate> 
                <asp:label id="lblActionDate" runat="server" text="&lt;%# Bind(&quot;ActionDate&quot;) %&gt;"></asp:label> 
               </itemtemplate>
            </asp:templatefield>
            <asp:commandfield showeditbutton="True">
        </asp:commandfield></asp:boundfield></columns>
        <editrowstyle backcolor="#FFCC99" width="95%">
        </editrowstyle></alternatingrowstyle></asp:gridview>
        </code><br />
<code><asp:button id="btnSelct" onclick="GetSelected" runat="server" text="Edit selected records">
        <asp:button id="btnCancel" postbackurl="~/Default.aspx" runat="server" text="Return">
        </asp:button></asp:button></code><br />
<code>
        <asp:sqldatasource connectionstring="&lt;%$ ConnectionStrings:Connection %&gt;" filterexpression="Owner_Name like '{0}%' and OS like '{1}%' and Type like '{2}%' and Status like '{3}%' and ActionType like '{4}%'" id="SqlDataSource1" runat="server" selectcommand="SELECT S.ID, S.HostName AS Hostname, S.IP AS IP_Address, U.Name AS Owner_Name, S.Owner, S.OS, S.Type, S.Status, S.ActionType, S.ActionDate 
            FROM tblServer AS S INNER JOIN tblUser AS U ON S.Owner = U.Id" updatecommand="UPDATE [tblServer] 
                           SET [HostName] = @HostName
                            ,[IP] = @IP_Address
                            ,[Owner] = @Owner
                            ,[OS] = @OS
                            ,[Type] = @Type
                            ,[Status] = @Status
                            ,[ActionType] = @ActionType
                            ,[ActionDate] = @ActionDate
                            ,[ModifiedDate] = CURRENT_TIMESTAMP
                            ,[ModifiedBy] = @ModifiedBy 
                           WHERE [Id] = @Id; 
                           INSERT INTO [tblActivityLog]
                           ([ActionTimelog]
                            ,[ActionCode]
                            ,[ActionUserName]
                            ,[ServerHostname]
                            ,[ServerLocation]
                            ,[ServerOS]
                            ,[ServerType]
                            ,[ServerOwner]
                            ,[ServerStatus]
                            ,[ServerFollowUpDate]
                            ,[ServerActionType]
                            ,[ServerActionDate]
                            ,[UserLogin]
                            ,[UserName]
                            ,[UserType]
                            ,[UserStatus]
                            ,[Notes]) 
                            values(
                            CURRENT_TIMESTAMP
                            ,@ActionCode
                            ,@ModifiedBy
                            ,@HostName
                            ,''
                            ,@OS
                            ,@Type
                            ,@Owner
                            ,@Status
                            ,''
                            ,@ActionType
                            ,@ActionDate
                            ,@ModifiedBy
                            ,@ModifiedBy
                            ,''
                            ,''
                            ,''
                            );">
            <filterparameters>
                     <asp:controlparameter controlid="ddlOwner_Name" name="Name" propertyname="SelectedItem.Text">
                     <asp:controlparameter controlid="ddlOS" name="OS" propertyname="SelectedValue">
                     <asp:controlparameter controlid="ddlType" name="Type" propertyname="SelectedValue"> 
                    <asp:controlparameter controlid="ddlStatus" name="Status" propertyname="SelectedValue">
                    <asp:controlparameter controlid="ddlActionType" name="ActionType" propertyname="SelectedValue">                
            </asp:controlparameter></asp:controlparameter></asp:controlparameter></asp:controlparameter></asp:controlparameter></filterparameters>
            <updateparameters>
                <asp:parameter name="Id" type="Int32">
                <asp:parameter name="HostName" type="String">
                <asp:parameter name="IP_Address" type="String">
                <asp:parameter name="Owner" type="Int32">
                <asp:parameter name="OS" type="String">
                <asp:parameter name="Type" type="String">
                <asp:parameter name="Status" type="String">
                <asp:parameter name="ActionType" type="String">
                <asp:parameter name="ActionDate" type="DateTime">
                <asp:parameter defaultvalue="UnKnown" name="ModifiedBy" type="string">
                <asp:parameter defaultvalue="UPD" name="ActionCode" type="string">
            </asp:parameter></asp:parameter></asp:parameter></asp:parameter></asp:parameter></asp:parameter></asp:parameter></asp:parameter></asp:parameter></asp:parameter></asp:parameter></updateparameters>
        </asp:sqldatasource>


No comments:

Post a Comment