" Live as if you were to die tomorrow. Learn as if you were to live forever.. "
- Mahatma Gandhi
" Live as if you were to die tomorrow. Learn as if you were to live forever.. "
- Mahatma Gandhi
| Background:
It is very common for people to use <asp:SqlDataSource> to bind controls in ASP.NET web pages. There are many developers who use <asp:SqlDataSource>to bind controls in their aspx pages. It is also prevalent to bind <asp:DropDownList> also using this <asp:SqlDataSource> But is it OK to use this for every single control (let us say all dropdowns) in a page? Please read On…. More Info: Typically this is what happens: I go to the design mode of aspx page in Visual Studio, drag a <asp:DropDownList>and as soon as I drop it in the web form, Visual Studio shows me a “chick” context menu sitting on the dropdown control titled as “Dropdown Tasks”. I have “Choose Data Source”, “Configure Data Source” among other things. You click on that menu, a nice little wizard comes up and you just keep clicking what you want and voila my dropdown is ready and bound and it is ready to deliver to my client.
So after this exercise, (when I choose a new <asp:SqlDataSource> in the wizard) typically these are the lines that are emitted in my aspx page. [Please read the code lines once here, even if you are very thorough with this to get accustomed to the control names, Etc]
<asp:DropDownList ID=”ddnCountry” runat=”server”DataSourceID=”sdsCountry” SkinID=”ddnBlue” DataTextField=”Name” DataValueField=”CountryRegionCode”> </asp:DropDownList> <asp:SqlDataSource ID=”sdsCountry” runat=”server” ConnectionString=”<%$ ConnectionStrings:AdventureWorks2008ConnectionString %>” SelectCommand=”SELECT [CountryRegionCode], [Name] FROM Person.[CountryRegion]”> </asp:SqlDataSource> Fantastic! I did not write a single line of code and here is my dropdown, ready to be shipped. So all well? I beg not. Let us expand the project and scenes. I have added a total of 200 pages in my project over a period of 3 months and there are 50 different pages which bind the same “Country” info. So in 50 different aspx pages I have the same piece of code repeating. Now the fun(?!?) begins – My client tells me that sometimes the order of the country is not correct please make sure to order the country names in alphabetical order in “Contact” page. So I change the query that was in there to “SELECT [CountryRegionCode], [Name] FROM Person.[CountryRegion] ORDER BY [Name]“. Job done, I upload the page again and inform my client that the “bug” is fixed. A week passes and my client screams that “I asked you to Order Country Names in alphabetical order, right? Now looks like that is not happening.” I ask him which page and his answer is “Shipping” page. So you get the point now, right? I did know that “Country” dropdown is present in 50 different pages when the client first complained about it and I was just afraid to touch “50 different” pages to change this and eventually I did not – But today I have no other option than to “touch” 50 pages! A week passes by and then my DBA informs me that the name of the schema “Person” in the DataBase “AdventureWorks2008” is now changed to “Persons” due to unavoidable reasons! Oops! I have to touch 50 pages again to change the schema name in the query! |
| Solution:
Probably I should have created a user control with this dropdown and then used it across all pages! Ok, I did not think about it and I did not “anticipate” that I would have this control repeating in so many places. Ok, what is the second option? I have something called <asp:ObjectDataSource> in ASP.Net and probably that was a better option here because <asp:ObjectDataSource> forces me to use centralized (if I create them as centralized) functions from classes which I can pass and my SQL would remain in just one place. But still this requires a lot of lines of code in every aspx with parameters and the Datasource Config and other stuff. Also this is not a one place solution for controls that repeat in multiple pages [Except the Query, everything else repeats in every place] So what other option I have? If only I had used a conventional binding using OOPS, with a separation of Business Logic/Data Access from my presentation then I would have been in a far better position! How? My aspx will just have just this – <asp:DropDownList ID=”ddnCountry”SkinID=”ddnBlue” runat=”server”> In my Page_Load event of .CS page I will write
//Call GetCountryList Function which is in the FillControls Class of BLayer BLayer.FillControls.GetCounrtyList(ddnCountry);
Following code is In BLayer: (Where Blyaer is either a namespace (folder) within the same project or a different project, depending on the project size, Etc) |
/// <summary>
/// Function Which Generates the Query to Bind Country Value Across the Project
/// </summary>
/// <param name="ddnCountry"></param>
public static void GetCounrtyList(System.Web.UI.WebControls.DropDownList ddnCountry)
{
string sqlCountry = "SELECT [CountryRegionCode] As Value, [Name] As Text FROM Person.[CountryRegion] ORDER BY [Name]";
BindDropDowns(sqlCountry, ddnCountry);
}
/// <summary>
/// This Function accepts the Query to Bind and the Dropdown Control to Bind And then Binds the Dropdown Control. Can be called From Anywhere in the project.
/// </summary>
/// <param name="sqlCountry"></param>
/// <param name="ddnCountry"></param>
private static void BindDropDowns(string sqlToBind, System.Web.UI.WebControls.DropDownList ddnBind)
{
using (conAdvenWorks) //SQL Connection Object, Initialized with Connection String
{
conAdvenWorks.Open();
using (SqlCommand cmdDropDown = new SqlCommand(sqlToBind, conAdvenWorks)) //Command with the Query
{
ddnBind.DataSource = cmdDropDown.ExecuteReader(); //Command gets executed and is set as datasource to the control
ddnBind.DataTextField = "Text";
ddnBind.DataValueField = "Value";
ddnBind.DataBind();
}
}
}
Please note that the BindDropDowns function is common to Bind ANY dropdown in my application. All I need to do is pass a query and the dropdown. So if I remove my comments, then almost with the same number of line that I take to bind one single dropdown using <asp:SqlDataSource> I can Bind All dropdowns in my application. The function GetCounrtyList can further be modified to be a common function with the help of Enum and a Database driven Query! So practically with just 10 lines of code and one Sql table (which will have all my SQL Queries to bind dropdowns (or even better, any data driven control) across my application and a enum to be passed from the .cs page (which is specific to that control/query) I can complete the whole story of DropDown Binding.
Please think about the amount of flexibility, code reuse, modularity this second approach provides.
So Is <asp:SqlDataSource> is a Bad Bad Devil?
Not necessary. It has its own uses, when we want to bind a grid with paging, sorting, etc – this is one of the best options – we do not need to write 100s of lines of code to achieve that. But the point here is we need to think, debate, ask, read, be certain before we use something for some purpose. Else, be ready to change 50 pages when a single “ORDER BY” clause needs a change in the Query!
Happy Programming!