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 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.