vb.net DAL Specify columns returned

Question

I have a Data Access Layer class that has a method (GetPeople) that will retrieve records from a SQL Server table (people). This table has more than 20 fields, including varbinary type.

Right now, SQL query is something like

SELECT * FROM people

From my BLL class, I will call DAL.GetPeople(), which will return all columns.

What would be the best way to specify which columns to return, so I could improve performance? For example, sometimes I would like to return all fields, other times, just one or two.

UPDATE To explain it better: In DAL I have a method GetPeople() which calls a SQL Server function GetPeople. In BLL I have a method GetPeople() which calls DAL.GetPeople(), after doing some business logic. In my presentation layer, I call BLL.GetPeople().

This is working, but on SQL function, I have "SELECT * FROM people". Sometimes I would like to retrieve only one column (eg. name) from table, but in this case all columns are returned, which I think is affects performance.

So, I would like to have a kind of dynamic SELECT query on this SQL Server function, whose columns returned would depend on how I call the function...

I think it depends on how you have written your DAL. ADO, EF, Simple.Data? As it stands this might be too open ended a question? — Neil Thompson, Feb 24 '14 at 15:28
The important thing is what type is your GetPeople method returning - Classes, datasets, other? Also, don't use Select * from ANYTHING. Specify the columns explicitly. — Jim Wooley, Feb 24 '14 at 16:14
a dataset... is what this method would return. Of course, I don't want to use "*", that's why I want to specify columns... — paezinc, Feb 24 '14 at 16:36

score 1 · Accepted Answer · answered Feb 24 '14 at 16:19

I think you are after something like this where you can pass in a comma-seperated list of column names

Private Function GenerateQuery(ByVal columnNames As String) As String

    '   columnNames in the following format 'column1,column2,column3'
    Dim lstColumnNames As String() = Split(columnNames, ",")
    Dim strSQL As New StringBuilder
    strSQL.Append("SELECT ")
    For intColNumber As Integer = 0 To lstColumnNames.GetUpperBound(0)
        strSQL.Append("[")
        strSQL.Append(lstColumnNames(intColNumber))
        strSQL.Append("]")
        If intColNumber < lstColumnNames.GetUpperBound(0) Then
            strSQL.Append(", ")
        End If
    Next
    strSQL.Append(" FROM People ")
    Return strSQL.ToString

End Function

You can use it like this: SqlCommand.CommandText = GenerateQuery("column1,column2,column3")

The column names are wrapped in [] symbols so you don't have to worry about reserved words causing the database to error.

This is what I've thought first place, but SQL query is on database (function), so the only way I can think about is passing fields string to this function and build query dynamically on that function, and then execute command using sp_executesql... — paezinc, Feb 24 '14 at 16:39
Yes, as far as I'm aware in that case there is little point having the function in the database because it will be dynamically generated and therefore not optimal in the way a static function would be. Personally I'd generate the string in the application, but that's just me :) — trucker_jim, Feb 24 '14 at 16:43

Jacob Lambert · Answer 2 · 2014-02-24T15:32:16.290

0

Change your SQL-query to something like

SELECT column1, column2, column3 FROM people;

EDIT:

What you are going to need to do is create function that will put your SQL string together for you. When i did this before, I had all of the available fields in a checked-list control, and if i wanted them pulled, I checked them. The checked items were then put through the function to assemble the string. It should be pretty simple since there are not any joins going on.

edited Feb 24 '14 at 15:32

answered Feb 24 '14 at 15:22

Jacob Lambert

7,449
8
27
47

Sorry, maybe I didn't explain right. Sometimes I want to "SELECT column1, column2, column3 FROM people", other times "SELECT column1 FROM people;" and other times "SELECT * FROM people;". I was just looking for a way to control which fields I would return. – paezinc Feb 24 '14 at 15:24

vb.net DAL Specify columns returned

2 Answers2