Skip to main content

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>


Labels:

Comments

Post a Comment

Popular posts from this blog

VBScript for compressing files in a folder and send on FTP

This is a simple vbscript compression script. works flawlessly on text files even bigger than 15GB. We have a SSIS data generation package installed on our DB server which generates huge data files for data mining operations for current year, current month. The below scripts are doing the rest; compress the files and FTP to the data mining server location.   '====================================================== ' Function : Zip datamart CSV, CTL files to local server for archive purposes. '====================================================== Function WindowsZip(sFile, sZipFile, szPath, szDate)   Set oZipShell = CreateObject("WScript.Shell")    Set oZipFSO = CreateObject("Scripting.FileSystemObject")   Set LogFile = oZipFSO.CreateTextFile(szPath & "\Logs\log_" & szDate & ".log", true)   LogFile.WriteLine("=======================================")   LogFile.WriteLine(Now & " - Com...
Post a Comment
Read more

MySQL Dump cronjob on linux server

Creating mysql dump for production databases is very important. In case of any failure or malefic attack on DB, The only way to restore to previous state is by having proper mysql dump file. Follows an example mysql dump shell script with auto naming with dumping time stamp. Its important to create the shell scripts in unix mode, if you create on a PC, It'll add some unwanted character encoding, and it'll lead to some erroneous response on the script. #!/bin/bash today=`date +%Y%m%d%H%M` backup="cmtdbbackup-$today.sql" mysqldump -u user -ppassword mydb > /apps/mysql_dump/$backup Its important to create the shell script carefully. No spaces, unless its required. in between -u and user name, there is a space, but no space between -p and password. As per the usage of system, we can decide on which interval the cronjob could be set. normally the format of a crontab entry is like the following. * * * * * /apps/scripts/scheduled_job.sh >> /dev/null 2>...
1 comment
Read more

Round the float

There are 3 types of rounding a float to integer is applicable in any language. we programmers will come across such situation in every project. the three types are, round to next bigger integer, with not considering the decimal value. round to the next smaller integer, with not considering the decimal value. round the float according to the value of decimal value, the real round of float number. The rounding methods depend on which scenario we need the round function. Follows the examples of php rounding methods. for the case 1, rounding to the next big integer, we use  ceil() function Example :  $rounded_value = ceil($floor_value); if rounded the following values, the results are like shown 2 = ceil(1.3) 2 = ceil(1.5) 2 = ceil(1.7) for the case 2, round to the next small integer, we could use floor() Example: $rounded_value = floor($floor_value); if rounded the following values, the results are like shown 1 = floor(1.3) 1 = floor(1.5) 1 = floor(1.7) f...
Post a Comment
Read more