ASP.NET Sort Data Results

Need to sort a returned data set numerically.

[jomoweb]

Hi, the following code populates a dropdown box with a set of integers from database, but I would like to sort them ascending from 0 upwards. Easily if possible:

lstMinCars.DataSource = maker.GetDistinctNumCar();
lstMinCars.DataTextField = "NumCar";
lstMinCars.DataBind();

for (int i = 0; i < lstMinCars.Items.Count; i++)

{lstMaxCars.Items.Add(lstMinCars.Items[i].Value.ToString());}

[Ocean10000]

Depending on your coding. Wouldn't it be easier to make it so that maker.GetDistinctNumCar() returns the list presorted for you?

[jomoweb]

I thought I was providing the code. ASP.NET is pretty new. I believe I have found the code powering GetDistinctNumCar(). How would one sort the returned result?

public SqlDataReader GetDistinctNumCar() {

SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
SqlCommand myCommand = new SqlCommand("MakerDistinctNumCar", myConnection);

myCommand.CommandType = CommandType.StoredProcedure;

myConnection.Open();

SqlDataReader result = myCommand.ExecuteReader(CommandBehavior.CloseConnection);

return result;}

[Ocean10000]

MakerDistinctNumCar is actually a stored procedure, which I am assuming you can put an "order by" clause in to make it do what you want.

example
"select NumCar from exampletable order by NumCar"

[jomoweb]

Thanks Ocean. I would have guessed it would be something like that.

I guess my problem is finding the stored procedure. I know this is a loaded question, but where would one normally store these procedures? I am used to looking for an "includes" folder or "config.php" file. Is there a standard place where these would go in a site directory.

I'm a super noob with this framework.

[Ocean10000]

Couple of questions for you. What database are you using MS SQL, MS Access, MySQL, or other? If you don't know I can show you how to do it a different way, using datasets in dot.net which will allow you to sort and that.

[jomoweb]

I do not know, don't have access to the DB. I would giess MS Access, but not sure...

[Ocean10000]

I did this in notepad and didn't have any way of testing it. But it should create the connection, command objects as before. But instead of using a forward only reader, we use a dataset to load it in memory. Once in memory it is possible to sort it. And then return the sorted data via the Dataview object. I didn't include any try/catch blocks but this should at least give you an idea how to handle it. I prefer to do the sorting in the database end but since you don't have the option this way will work.

public System.Data.DataView GetDistinctNumCar()
{
 SqlConnection Conn = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
 SqlCommand cmd = new SqlCommand("MakerDistinctNumCar", myConnection);
 cmd.CommandType = CommandType.StoredProcedure;
 System.Data.DataSet ds = new System.Data.DataSet();
 System.Data.SqlClient.SqlDataAdapter adapter;
 adapter = new System.Data.SqlClient.SqlDataAdapter(cmd);
 adapter.Fill(ds);
 System.Data.DataView dv = ds.Tables[0].DefaultView;
 dv.Sort = "NumCar";
 return dv;
}