I'm using C# in Visual Studio 2005 with Sql server 2005 as my database. I'm wanting to know how to make my sql data sortable in C#. The code I created below works right now with manual data that was entered. However I'm wanting to use the sql connection
below and make that data sortable in the same way that the manual data works. Can someone help me intergrate this and make it work? I'm not sure how to do it and I can't seem to find a way to do it in a google search.
string strConnection = ConfigurationManager.ConnectionStrings["PICNetConnectionString"].ConnectionString;
SqlConnection Conn = new SqlConnection(strConnection);
string strSql = "SELECT ProjectName as ProjectName, id, POC, Priority, Status, Notes ";
strSql = strSql + " FROM Projects where Status <> 'Completed' or Status is Null order by Status desc, POC ";
SqlCommand cmd = new SqlCommand(strSql, Conn);
Conn.Open();
dgProducts.DataSource = cmd.ExecuteReader();
dgProducts.DataBind();
Conn.Close();
So if I use my DataGrid code below will I need to replace the <tbody> section? Doesn't the even, odd, even, odd in my previous code help the data move?
Storm91
0 Points
2 Posts
Sortable Datagrid in C#
Jun 15, 2010 12:54 PM|LINK
I'm using C# in Visual Studio 2005 with Sql server 2005 as my database. I'm wanting to know how to make my sql data sortable in C#. The code I created below works right now with manual data that was entered. However I'm wanting to use the sql connection below and make that data sortable in the same way that the manual data works. Can someone help me intergrate this and make it work? I'm not sure how to do it and I can't seem to find a way to do it in a google search.
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <base href="http://www.ajaxdaddy.com/web20/sorted-tables/"> <style type="text/css"> body {color: white;background: #C0C0C0;} a { color: white; } </style><title>SortedTable example</title> <meta http-equiv="content-type" content="text/html; charset=UTF-8"> <script type="text/javascript" src="javascript/sortedtable-event.js"></script> <script type="text/javascript" src="javascript/sortedtable.js"></script> <link rel="stylesheet" type="text/css" media="all" href="css/common.css" /> </head> <body> <script type="text/javascript"> function moveRows(s,d) { var a = new Array(); for (var o in s.selectedElements) { a.push(s.selectedElements[o]); } for (var o in a) { var elm = a[o]; var tds = elm.getElementsByTagName('td'); for (var i in tds) { if (tds[i].headers) tds[i].headers = d.table.id+''+tds[i].headers.substr(d.table.id.length); } d.body.appendChild(a[o]); d.deselect(a[o]); d.init(d.table); d.sort(); s.deselect(a[o]); s.init(s.table); } } </script> <div class="content"> <h1> </h1> <table class="sorted regroup" cellpadding="0" cellspacing="0"> <thead> <tr> <th style="cursor: pointer; width: 88px; height: 19px;" id="ProjectName" class="sortedplus"><span>Id</span></th> <th style="cursor: pointer; height: 19px;" id="POC"><span>ProjectName</span></th> <th style="cursor: pointer; height: 19px;" id="Priority"><span>POC</span></th> <th style="cursor: pointer; width: 23px; height: 19px;" id="Status"><span>Priority</span></th> <th style="cursor: pointer; width: 82px; height: 19px;" id="BeginDate"><span>Status</span></th> <th style="cursor: pointer; height: 19px;" id="HRS"><span>BeginDate</span></th> <th id="published" class="nosort" style="width: 65px; height: 19px"><span>HRS</span></th> </tr> </thead> <tfoot> <tr> <td>Footer 1</td> <td>Footer 2</td> <td>Footer 3</td> <td>Footer 4</td> <td>Footer 5</td> <td>Footer 6</td> <td>Footer 7</td> </tr> </tfoot> <tbody> <tr class="even" style="cursor: pointer;" id="row1"><td axis="number" headers="order" style="width: 88px; height: 19px">1</td><td axis="string" headers="title" style="height: 19px">Title 1 </td><td axis="date" headers="date" title="2006-02-20" style="height: 19px">20.2.2006</td><td axis="number" headers="num" style="width: 23px; height: 19px">92</td><td axis="sstring" headers="desc" style="width: 82px; height: 19px">Description 1 </td><td axis="date" headers="publish" title="2005-08-01" style="height: 19px">1.8.2005</td><td headers="published" style="width: 65px; height: 19px">x</td></tr> <tr class="odd" style="cursor: pointer;"><td axis="number" headers="order" style="width: 88px">2</td><td axis="string" headers="title">title 2 </td><td axis="date" headers="date" title="2006-08-04">4.8.2006</td><td axis="number" headers="num" style="width: 23px">71</td><td axis="sstring" headers="desc" style="width: 82px">description 2 </td><td axis="date" headers="publish" title="2005-03-05">5.3.2005</td><td headers="published" style="width: 65px">o</td></tr> <tr class="even" style="cursor: pointer;"><td axis="number" headers="order" style="width: 88px">3</td><td axis="string" headers="title">Title 3 </td><td axis="date" headers="date" title="2006-07-05">5.7.2006</td><td axis="number" headers="num" style="width: 23px">93</td><td axis="sstring" headers="desc" style="width: 82px">Description 3 </td><td axis="date" headers="publish" title="2006-08-04">4.8.2006</td><td headers="published" style="width: 65px">x</td></tr> <tr class="odd" style="cursor: pointer;"><td axis="number" headers="order" style="width: 88px">4</td><td axis="string" headers="title">Title 4 </td><td axis="date" headers="date" title="2006-09-03">3.9.2006</td><td axis="number" headers="num" style="width: 23px">5</td><td axis="sstring" headers="desc" style="width: 82px">Description 4 </td><td axis="date" headers="publish" title="2006-09-18">18.9.2006</td><td headers="published" style="width: 65px">x</td></tr> <tr class="even" style="cursor: pointer;"><td axis="number" headers="order" style="width: 88px">5</td><td axis="string" headers="title">Title 5 </td><td axis="date" headers="date" title="2005-09-10">10.9.2005</td><td axis="number" headers="num" style="width: 23px">54</td><td axis="sstring" headers="desc" style="width: 82px">Description 5 </td><td axis="date" headers="publish" title="2005-08-23">23.8.2005</td><td headers="published" style="width: 65px">x</td></tr> <tr class="odd" style="cursor: pointer;"><td axis="number" headers="order" style="width: 88px">6</td><td axis="string" headers="title">title 6 </td><td axis="date" headers="date" title="2004-12-21">21.12.2004</td><td axis="number" headers="num" style="width: 23px">90</td><td axis="sstring" headers="desc" style="width: 82px">description 6 </td><td axis="date" headers="publish" title="2005-10-18">18.10.2005</td><td headers="published" style="width: 65px">x</td></tr> <tr class="even" style="cursor: pointer;"><td axis="number" headers="order" style="width: 88px">7</td><td axis="string" headers="title">Title 7 </td><td axis="date" headers="date" title="2005-11-12">12.11.2005</td><td axis="number" headers="num" style="width: 23px">51</td><td axis="sstring" headers="desc" style="width: 82px">Description 7 </td><td axis="date" headers="publish" title="2005-10-02">2.10.2005</td><td headers="published" style="width: 65px">o</td></tr> <tr class="odd" style="cursor: pointer;"><td axis="number" headers="order" style="width: 88px">8</td><td axis="string" headers="title">Title 8 </td><td axis="date" headers="date" title="2006-03-10">10.3.2006</td><td axis="number" headers="num" style="width: 23px">69</td><td axis="sstring" headers="desc" style="width: 82px">Description 8 </td><td axis="date" headers="publish" title="2005-05-08">8.5.2005</td><td headers="published" style="width: 65px">o</td></tr> <tr class="even" style="cursor: pointer;"><td axis="number" headers="order" style="width: 88px">9</td><td axis="string" headers="title">Title 9 </td><td axis="date" headers="date" title="2005-08-22">22.8.2005</td><td axis="number" headers="num" style="width: 23px">57</td><td axis="sstring" headers="desc" style="width: 82px">Description 9 </td><td axis="date" headers="publish" title="2005-06-09">9.6.2005</td><td headers="published" style="width: 65px">o</td></tr> </tbody> </table> <dl> <dt>Common public calls: <!-- you can call move on a table --> </dt> <dd style="color: white"><a href="javascript:mySorted.move(1)">move selected row(s) up</a> <!-- you can also call it through a static method; the static method to move selected rows is different, it uses the element just to get the table --> </dd> <dd style="color: white"><a href="javascript:SortedTable.moveSelected(-1,document.getElementById('row1'))">move selected row(s) down</a> <!-- calling a method on the table --> </dd> <dd style="color: white"><a href="javascript:mySorted.cleansort()">clean sort</a> <!-- finding the table through a static method with a child element and then calling the method --> </dd> <dd style="color: white"><a href="javascript:SortedTable.getSortedTable(document.getElementById('row1')).cleanselect()">clean selection</a></dd> </dl> <hr> </div> <script type="text/javascript"> var sourceTable, destTable; function init() { sourceTable = new SortedTable('s'); destTable = new SortedTable('d'); mySorted = new SortedTable(); mySorted.colorize = function() { for (var i=0;i<this.elements.length;i++) { if (i%2){ this.changeClass(this.elements[i],'even','odd'); } else { this.changeClass(this.elements[i],'odd','even'); } } } mySorted.onsort = mySorted.colorize; mySorted.onmove = mySorted.colorize; mySorted.colorize(); secondTable = SortedTable.getSortedTable(document.getElementById('id')); secondTable.allowDeselect = false; } init(); </script> </body></html>
SQL connection stringstring strConnection = ConfigurationManager.ConnectionStrings["PICNetConnectionString"].ConnectionString; SqlConnection Conn = new SqlConnection(strConnection); string strSql = "SELECT ProjectName as ProjectName, id, POC, Priority, Status, Notes "; strSql = strSql + " FROM Projects where Status <> 'Completed' or Status is Null order by Status desc, POC "; SqlCommand cmd = new SqlCommand(strSql, Conn); Conn.Open(); dgProducts.DataSource = cmd.ExecuteReader(); dgProducts.DataBind(); Conn.Close();richiej
Participant
1771 Points
354 Posts
Re: Sortable Datagrid in C#
Jun 15, 2010 01:23 PM|LINK
To achieve what you want I think you will need to redesign your page.
Personally I've used grivviews in the past and bound my data to it. GridViews will perform the sort for you easily and almost automatically.
Storm91
0 Points
2 Posts
Re: Sortable Datagrid in C#
Jun 15, 2010 01:44 PM|LINK
So if I use my DataGrid code below will I need to replace the <tbody> section? Doesn't the even, odd, even, odd in my previous code help the data move?
<asp:DataGrid id="dgProducts" runat= "server" AllowSorting="True" AutoGenerateColumns="False" CellPadding="4" ForeColor="#333333" GridLines="None" Height="277px" Width="605px" PageSize="30" AllowCustomPaging="True" AllowPaging="True"> <Columns> <asp:BoundColumn DataField="ProjectName" HeaderText="ProjectName" SortExpression="dgProducts_Sort"> </asp:BoundColumn> <asp:BoundColumn DataField="POC" HeaderText="POC" SortExpression="dgProducts_Sort"> </asp:BoundColumn> <asp:BoundColumn DataField="Priority" HeaderText="Priority" SortExpression="dgProducts_Sort"> </asp:BoundColumn> <asp:BoundColumn DataField="Status" HeaderText="Status" SortExpression="dgProducts_Sort"> </asp:BoundColumn> </Columns> <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /> <EditItemStyle BackColor="#999999" /> <SelectedItemStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" /> <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" Mode="NumericPages" /> <AlternatingItemStyle BackColor="White" ForeColor="#284775" /> <ItemStyle BackColor="#F7F6F3" ForeColor="#333333" /> <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /> </asp:DataGrid>richiej
Participant
1771 Points
354 Posts
Re: Sortable Datagrid in C#
Jun 16, 2010 08:30 AM|LINK
I would say yes, your datagrid code should work better and it's easier to implement
Althought JS function could do it, its very difficult to sort that way using "bound" data. It can be done, but tricky.