Please help! I have lots of questions.

In case some of you have read my previous posts, you may be aware that I'm writing a webboard application as a replacement for the old one.

The old one currently have approximately 50000 topics in the database, each has on average 10 replies (I just check recently. I though it was only 7000 topics).

I need to provide paging and sorting feature to the topic list. But I can't just SELECT all of them and let GridView do the paging/sorting, right?
I have been using stored procedures to store my SQL statement for several projects now. I know how to deal with the paging feature (ROW_NUMBER), but the sorting requires me to change to change the "ORDER BY" clause.

1. Can somebody tell me how to change the ORDER BY clause in the stored procedure(s) at runtime? Or does anyone have other approach?

Currently I'm thinking about moving back from store procedures to hard-code SQL statements, and then modify/generate the SQL statement for each paging / sorting. But I've learn that stored procedures give more performance and security.

2. According to the situation I provided, is it worth moving from stored procedures to hard-code SQL?

I'm also using 3-tier architecture approach + OOP. But I reach a conflict in my thoughts.

You see, according to OOP, I'm supposed to create classes that reflect the actual objects in the real-world, right? In my case the classes are "Board, Topic, Reply, ..." According to this and 3-tier approach, I intend to use ObjectDataSource as a bridge between Presentation Logic and Business Logic. But I wonder what my datasource class should return

3. Should my data source class return data objects like

1st approach

[DataObject(True)]
pubic class TopicDataSource{
public static Topic[] GetTopicList() { }
}

or should it return DataSet / DataTable / DataReader like

2nd approach

[DataObject(True)]
public class TopicDataSource{
public static DataTable GetTopicList() {}
}

Personally I think approach 1 is more OOP and allow for more extendability, but approach 2 might be faster.

4. If I go with approach 1, how should I control which property of my data objects is read-onlyafter it's has been inserted/created?
Can I just set my data object's property to be readonly? Or do I have to set it at page level (i.e. GridView-> Columns -> BoundField -> ReadOnly=True)?
Or do I set it and the page leveland write a code to throw an exception in the rare case the application / user try to change it's value? Or else?

Please help. These questions slow me down for days now.

If there's any concepts that I misunderstood, please tell me. I'm aware that I don't know as much as some of you.

I will be extremely grateful to anyone who answer any of my questions.

Thanks a lot.

PS. For those who think my questions are stupid, I'm very, very sorry that I bother you.

Hello my friend,

First thing - do not call yourself stupid for asking questions. The stupid ones are those that never ask and then end up breaking something or building something crap. My answers are as follows: -

1) Use stored procedures for performance and then change the order by clause by using a DataView, like this: -

// instead of this
//myRepeater.DataSource = myDataSet;
//myRepeater.DataBind();

// do this (use a stored procedure to fill a dataset and then filter the dataset)
DataView dv = new DataView(myDataSet.Tables[0]);
dv.Sort = "MyField DESC";

myRepeater.DataSource = dv;
myRepeater.DataBind();

2) If all you need to change is the order by clause, use stored procedures with the above example. Only go back to hardcoded SQL if you need to specify the fields to select at runtime or if there are very complex and/or clauses at runtime and the data has a lot of records.

3) If you find approach 2 to be faster then use it. Extensibility is no substitute for performance. We do all the hard stuff so the application runs fast, as opposed to building a second class project to suit us.

4) You can have a Locked property, or whatever name you prefer, on your object. Then in the set property definition, have the following: -

Set(ByVal value As String)
If Me.Locked = False Then
MyProperty = value
End If
End Set

I hope this helps you.

Kind regards

Scotty

|||Thank you Scotty (or Scott, perhaps). That help answer question 2, 3, 4 quite clear. Although if anyone think differently, or if anyone think something should be added, don't hesitate. Please answer it here.

But I still worry about problem 1.
Does the dataset get everything after calling SQLDataAdapter.Fill? In case I SELECT everything without paging? I mean, won't it use too much memory?

Also, if I implement paging in stored procedure, the sorting won't be correct.
For example if the data is like this

Table: Test
Test1
Row 1: a
Row 2: b
Row 3: c
Row 4: d
Row 5: e

Situation : startRecord = 0 MaxRecords = 3 sortColumns = "Test1 DESC"

If I use paging, I will get "a, b, c", right?
Then if I use dataview sorting, my data will reorder from "a, b, c" to - "c, b, a" which is the final result.
But that's not what we actually want. The final data should be "e, d, c" not "c, b, a", right?

Can somebody help?|||

I just thought of something.

Even if I plan to have 2nd, 3rd ... versions, performance come first, right? Just to be sure.

As I told you above, my program intend to replace an existing one create by another company. But currently version 1's main objective is to replace the old system only. I intend to have new features added after version 1 is finished, to be version 2.