Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

Sorting Data in a SQL Database

.NET Framework 1.1

The following example binds a DataGrid control to a database. The column headings contain links that, when clicked, cause the table to use that column as the sort key. The table can be sorted in ascending or descending order.

The example sorts a DataView taken from a table in the DataSet. The DataView, which is a view of a single table, has a Sort property. The DataSet, a memory-resident database that can contain several tables, does not.

To see a similar example executed, run the DataGrid11.aspx sample in the ASP.NET QuickStart.

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<html>
<script language="VB" runat="server">
   Dim myConnection As SqlConnection
   Sub Page_Load(Src As Object, E As EventArgs) 
      '  Create a connection to the "pubs" SQL database located on the 
      ' local computer.
      myConnection = New SqlConnection("server=localhost;" _
         & "database=pubs;Trusted_Connection=Yes")
      ' Sort on id field the first time through.
      if Not IsPostBack Then 
         BindGrid("au_id")
      End If
   End Sub
   
   ' The myDataGrid_Sort function takes the event arguments object
   ' DataGridSortCommandEventArgs E, which is passed in from the   
   ' OnSortCommand method in the DataGrid control, and passes it with 
   ' the call to BindGrid.
   Protected Sub MyDataGrid_Sort(Src As Object, _
      E As DataGridSortCommandEventArgs) 
      BindGrid(E.SortExpression)
   End Sub
   Sub BindGrid(sortField As String) 
      ' Set up the SqlDataAdapter to get the "Authors" table from the 
      ' "pubs" database.
      Dim myCommand As SqlDataAdapter = New SqlDataAdapter("SELECT *" _
         & " FROM Authors", myConnection)
      ' Fill a New DataSet.
      Dim ds As DataSet = New DataSet()
      myCommand.Fill(ds)
      ' Create a DataView based on the first  (and only) DataTable in the
      ' DataSet. Sort the data using the specified sort field.
      Dim view As DataView = ds.Tables(0).DefaultView
      ' To sort in ascending order:
      view.Sort = sortField & " ASC"
      ' To sort in descending order:
      ' view.Sort = sortField & " DESC"
      ' Bind the DataGrid to the sorted DataView.
      MyDataGrid.DataSource = view
      MyDataGrid.DataBind()
   End Sub
</script>

<body>
   <%-- Display the data  in the body of the page. --%>
   <h3><font face="Verdana">Sorting Data in a DataGrid Control</font></h3>
   <%-- Within a form, create a DataGrid control and initialize its 
      attributes. --%>
   <form runat="server">
      <ASP:DataGrid id="MyDataGrid" runat="server" 
         OnSortCommand="MyDataGrid_Sort"
         Width="700"
         BackColor="#ccccff" 
         BorderColor="black"
         ShowFooter="false" 
         CellPadding=3 
         CellSpacing="0"
         Font-Name="Verdana"
         Font-Size="8pt"
         HeaderStyle-BackColor="#aaaadd"
         AllowSorting="true" 
      />
   </form>
</body>
</html>

[C#]
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<html>
<script language="C#" runat="server">
   SqlConnection myConnection;
   protected void Page_Load(Object Src, EventArgs E) 
   {
      // Create a connection to the "pubs" SQL database located on the 
      // local computer.
      myConnection = new SqlConnection("server=localhost;" +
         "database=pubs;Trusted_Connection=Yes");
      // Determine whwther the page is a postback. If it is not a 
      // postback, call BindGrid and pass the ID column name as the 
      // sort key the first time through.
      if (!IsPostBack) BindGrid("au_id");
   }
   // The MyDataGrid_Sort function takes the event arguments object
   // DataGridSortCommandEventArgs E, which is passed in from the
   // OnSortCommand method in the DataGrid control, and passes it with 
   // the call to BindGrid.
   protected void MyDataGrid_Sort(Object Src, 
      DataGridSortCommandEventArgs E) 
   {
      BindGrid(E.SortExpression);
   }
   // The BindGrid function is passed the sort field parameter (the sort
   //  column) from the MyDataGrid_Sort function. It uses this information
   // to sort the table according to the specified column.
   public void BindGrid(String sortfield) 
   {
      // Set up the SqlDataAdapter to get the "Authors" table from the 
      // "pubs" database.
      SqlDataAdapter myCommand = new SqlDataAdapter("SELECT * FROM " +
         "Authors", myConnection);
      // Fill a new DataSet.
      DataSet ds = new DataSet();
      myCommand.Fill(ds);
      // Create a DataView based on the first (and only) DataTable in the
      // DataSet. Sort the data using the specified sort field.
      DataView view = ds.Tables[0].DefaultView;
      // To sort in ascending order:
      view.Sort = sortfield + " ASC";
      // To sort in descending order:
      // view.Sort = sortfield + " DESC";
      // Bind the DataGrid to the sorted DataView.
      MyDataGrid.DataSource=view;
      MyDataGrid.DataBind();
   }
</script>

<body>
   <%-- Display the data  in a DataGrid control --%>
   <h3><font face="Verdana">Sorting Data in a DataGrid Control</font></h3>
   <%-- Within an HTML form, create a DataGrid control and initialize 
      its attributes. --%>
   <form runat="server">
      <ASP:DataGrid id="MyDataGrid" runat="server" 
         OnSortCommand="MyDataGrid_Sort"
         Width="700"
         BackColor="#ccccff" 
         BorderColor="black"
         ShowFooter="false" 
         CellPadding=3 
         CellSpacing="0"
         Font-Name="Verdana"
         Font-Size="8pt"
         HeaderStyle-BackColor="#aaaadd"
         AllowSorting="true" 
      />
   </form>
</body>
</html>   

See Also

Accessing Data with ASP.NET | Accessing Data with ADO.NET | System.Web.UI.WebControls Namespace | DataGrid Class | DataView Class

Show:
© 2015 Microsoft