SQL Arrays and Filling in DropDownList/Combobox with ‘Select One…’

Posted on: August 25th, 2011 by
Comments Requested

As I have explained before while describing how to implement SQL for loops, performing data manipulation is faster in the database itself compared to grabbing the data out of the database and manipulating it in your code. For this reason, I have tried to use SQL operations everywhere I can to reduce the load time of my C#.NET web applications. One of my favorite examples is loading a dropdown control in ASP straight from the database. You can always use a SqlDataSource control to grab your data and specify a simple SELECT statement, but, what about the all too familiar ‘Select One…’ item?

Select One Example

Many times I’ve seen developers use the SqlDataSource to grab all the data rows from the database and then add an extra item for the ‘Select One…’ with DropDownList.Items.Add method in the code-behind, or just grab all the database information straight in the code-behind without using a SqlDataSource. There is a faster method to do this which only involves the database. All the data you need, including the ‘Select One…’ item will come from the database. And so, with this method, you will be free to use SqlDataSource control in your ASPX page once and wont need to manipulate the data anywhere else in your code. To implement this, we’ll utilize a SQL Array implemented with the SQL TABLE identifier. Drop down to the final solution.

First thing to do in our SQL code is to declare our array. We include the TABLE identifier and specify a variable name and type for the elements that this array will contain. You can also specify multiple variables delimited with a comma to represent multi-dimensional arrays. Here, I’ll include a multi-dimensional array and go along with our example of grabbing the data for our DropDownList.


DECLARE @mylist TABLE (text_variable VARCHAR(100) NOT NULL, value_variable INT NOT NULL)


[Red]

Our ‘text_variable’ represents the field that our DropDownList will bind the DataTextField attribute to and the ‘value_variable’ represents the field that the DropDownList will bind the DataValueField to.

[Yellow]

We begin filling our SQL Array by grabbing the desired data from the database tables. In this example I grab records from the ‘Countries’ table and insert the retrieved column values into the SQL Array I’ve declared. As you can see below, I’ve included a CONVERT procedure for my ID field. This is because I want to make sure the value_variable will be of type ‘VARCHAR’ so that when I want to add a VARCHAR value to the array in the value_variable field, I won’t get an error. Of course, if you want this field to be maintained as anything else, you’re free to do so, provided that you only attempt to add variables into value_variable that are of the same type.


INSERT INTO @mylist (text_variable, value_variable) SELECT Country_Name, CONVERT(VARCHAR, ID) FROM Countries


[Green]

Now that we’ve filled our SQL Array with all the meaty data, it’s time to tack on the ‘Select One…’ item to our list. We do a similar thing that we did while grabbing the data from the database, only, this time, we SELECT hard coded text.


INSERT INTO @mylist (text_variable, value_variable) SELECT ‘Select One…’, ’0′


[Blue]

And the last thing to do is return the records of the array so that when we call this stored procedure with SqlDataSource we are sure that it will return what we expect. We do this with a simple SELECT statement.


SELECT text_variable, value_variable from @mylist order by value_variable


And here’s the entire final solution:


DECLARE @mylist TABLE (text_variable VARCHAR(100) NOT NULL, value_variable INT NOT NULL)



INSERT INTO @mylist (text_variable, value_variable) SELECT Country_Name, CONVERT(VARCHAR, ID) FROM Countries



INSERT INTO @mylist (text_variable, value_variable) SELECT ‘Select One…’, ’0′



SELECT text_variable, value_variable from @mylist order by value_variable


More on SQL Arrays:

  1. Ode To Code: Table Variables In T-SQL
  2. MSDN: DECLARE @local_variable (Transact-SQL)
  3. SQL Server Performance: Temporary Tables vs. Table Variables and Their Effect on SQL Server Performance

Tags: , , , , ,

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>