Building SQL Query with SQL Data Adapter? Generating query off of configuration section

Question

I'm not sure if I'm approaching this the correct way, but am very open and appreciative of any suggestions.

I am building a chart off of data in a SQL database.

The chart has 'Age' and 'Percentile' or those are the 2 fields I am most concerned with.

I have a config section in my web.config called 'Percentiles'. This is so the percentiles to be displayed can be identified in this config section. For example:

<percentiles>
<add percentile="P50" visible="true">
<add percentile="P75" visible="true">
<add percentile="P85" visible="false">
<add percentile="P95" visible="true">
...
</percentiles>

I will parse the list of percentiles and would like to either filter my DataSet or generate my query based on that list. The percentiles are stored as columns in the database. So for example I want my query to look like this based on that configuration

SELECT P50, P75, P95 FROM MyData

Now I suppose I could just do SELECT * and remove those columns from my DataSet, or just not add them as 'Series' to my chart. However, I am a novice developer looking to improve the way I do things. In my little experience it seems grabbing all of the data (albeit a small amount) is not a good practice.

So, this is where I arrive at the SQLDataAdapter. Is it possible to build my query via code to only include the columns I want? The psuedocode I am wondering if is possible is something like this:

foreach(Percentile p in PercentileConfiguration)
{
    myDataAdapter.Select.Columns.Add(p.Percentile);
}

Is this a good approach? Any and all suggestions very appreciated! Thank you!

Answer 1

I'm confused. At first glance, this seems to be a question purely about how to query a database. And the answer to that is that you construct a SQL command to do that. I can't understand why you are storing database field names in the web.config. If you want a query to look like "SELECT P50, P75, P95 FROM MyData", then make it so.

Answer 2

SELECT * .... is going to slightly slower than SELECT P50, P75, etc. But only marginally. If your only running the query once ( as opposed to multiple times in a loop, or something), and your table doesnt have a massive amount of columns, or columns with huge data, then the performance hit is going to be so nominal that the effort and maintenance in selecting specific columns will probably not be worth it. Someone once told me that if you have to choice between code that is readable(maintainable) and code that will run a millisecond faster go with the readable code.

That said, if your going to do it, you just need to construct the sql text and then pass that to your adapter.

Something like:

StringBuilder columns = new StringBuilder();
foreach(Percentile p in PercentileConfiguration)
{
    if(columns.ToString().Length > 0) columns.Append(", ");
    columns.Append(p.Percentile);
}
string sql = string.Format("SELECT {0} FROM myTable", columns.ToString());
SqlDataAdapter da = new SqlDataAdapter(sql, connectionString);
...
...
...