Ordenar datos en una base de datos SQL

Artículo
04/11/2008

En el siguiente ejemplo se enlaza un control DataGrid a una base de datos. Los títulos de columna contienen vínculos que, al hacer clic en ellos, hacen que la tabla utilice dicha columna como clave de ordenación. La tabla se puede ordenar en sentido ascendente o descendente.

En el ejemplo se ordena una DataView tomada de una tabla del DataSet. DataView, que es una vista de una única tabla, tiene una propiedad Sort. DataSet, una base de datos residente en memoria que puede contener varias tablas, no la tiene.

Para ver un ejemplo similar, hay que ejecutar el ejemplo DataGrid11.aspx del Tutorial de ASP.NET.

<%@ 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>

Vea también

Acceso a datos con ASP.NET | Acceso a datos con ADO.NET | System.Web.UI.WebControls (Espacio de nombres) | DataGrid (Clase) | DataView (Clase)

Ordenar datos en una base de datos SQL

Vea también

Recursos adicionales