I had some trouble today figuring out why sorting a dataset wasn't working. Basically my SQL statement put an ORDER BY 'column' DESC but when I dumped it into a dataset, for some reason the order disappeared. I figured out that in order to sort a DataSet, you actually have to put the data inside that DataSet into something like a DataView. Then, you sort the DataView with myView.Sort. Here's my sample, working code.
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Data.Sql;
using System.Collections.Generic;
using Microsoft.ApplicationBlocks.Data;
public void GetItemNumber()
{
string sConnectionString = "Data Source=sqlserver;Database=MY-DATABASE-NAME;uid=userid;pwd=password";
SqlConnection objConn = new SqlConnection(sConnectionString);
objConn = new SqlConnection(sConnectionString);
objConn.Open();
try
{
string MySQLStatement =
"SELECT ItemNumber FROM ItemTable where ItemNumber " +
"LIKE '%" + this.txtItemumber.Text + "%' ORDER BY ItemNumber DESC"; //this DESC doesn't seem to do anything.
using (SqlDataAdapter da = new SqlDataAdapter(MySQLStatement, objConn))
{
//fill the DataSet
DataSet ds = new DataSet();
da.Fill(ds);
//Create DataView using the data in my DataSet.
DataView myView = new DataView();
myView = ds.Tables[0].DefaultView;
//sort in DESC order with ColumnName
myView.Sort = "ItemNumber DESC";
foreach (DataRow dr in ds.Tables[0].Rows)
{
this.txtOutputItemNumber.Text = myView[0].Row[0].ToString();
}
}
}
catch (System.Exception e)
{
this.lblErrors.Text = e.Message.ToString();
}
finally
{
objConn.Close();
}
}
That should save somebody some time some day. If this helps you and you use it, please post a comment or link to this post. Thanks!