Representative SqlDataSource



  • <html><body style='color:#000000; background:#ffffff; '>
    <asp:SqlDataSource ID="SqlDataSourceLkupHC" 
    ConnectionString="<%$ ConnectionStrings:XXXX%>"
            SelectCommand="CREATE TABLE #temp6 (id VARCHAR(50), [name] VARCHAR(50))
                    INSERT #temp6 SELECT NULL AS id, ('None') AS [name]
                    INSERT #temp6 SELECT h_c_id AS id, [name] FROM lkup_h_c
                    SELECT * FROM #temp6
                    DROP TABLE #temp6" runat="server"></asp:SqlDataSource>
    

    In case this is too obtuse/.NET-y, the end result is a drop down with an extra list item "None" at the top.

    And yes, there were five other SqlDataSource's that used a similar methods. Hence "temp6".


  • Considered Harmful

    Wow. Haven't seen a WTF this good in a while.



  •  Yes, I just searched our code base for that command.

    I really expected it to be there.


  • Discourse touched me in a no-no place

    Does the database support TEMP TABLEs?



  • @dkf said:

    Does the database support TEMP TABLEs?

    Yes. Technically it works, but it is quite possibly the most ass-backwards, round about, inefficient way of achieving the desired result.



  • @mikeTheLiar said:

    @dkf said:
    Does the database support TEMP TABLEs?

    Yes. Technically it works, but it is quite possibly the most ass-backwards, round about, inefficient way of achieving the desired result.

    Two interesting points:
    1) there is nothing in the code that forces the (None) choice to be at the top of the list (likely but not sure)
    2) using DDL statements (CREATE TABLE) is the best way to prevent the database server from reusing the execution plan, which is unfortunate in this specific situation where the query has no parameters and is possibly used by concurrent users (as most webapps are)

    Also the "None" value is hard-coded, which makes this solution not future-proof. The right way to do this would clearly have been to call a web service from the SQL query.



  • @Ronald said:

    using DDL statements (CREATE TABLE) is the best way to prevent the database server from reusing the execution plan

    Should have used a table variable, obviously.



  • @Arnavion said:

    @Ronald said:
    using DDL statements (CREATE TABLE) is the best way to prevent the database server from reusing the execution plan

    Should have used a table variable, obviously.

    ASP.Net data controls have a hard time with temp tables at design time (the wizard can't see the columns). Table variables don't cause this problem.



  • @Ronald said:

    @Arnavion said:
    @Ronald said:
    using DDL statements (CREATE TABLE) is the best way to prevent the database server from reusing the execution plan

    Should have used a table variable, obviously.

    ASP.Net data controls have a hard time with temp tables at design time (the wizard can't see the columns). Table variables don't cause this problem.

    Or just use a UNION. It not only doesn't cause problems for the IDE, it also performs well. Of course it's probably crazy talk to just set AppendDataBoundItems to true and keep the database out of the whole idea of displaying a placeholder in the UI.


  • @Jaime said:

    @Ronald said:
    @Arnavion said:
    @Ronald said:
    using DDL statements (CREATE TABLE) is the best way to prevent the database server from reusing the execution plan

    Should have used a table variable, obviously.

    ASP.Net data controls have a hard time with temp tables at design time (the wizard can't see the columns). Table variables don't cause this problem.

    Or just use a UNION. It not only doesn't cause problems for the IDE, it also performs well. Of course it's probably crazy talk to just set AppendDataBoundItems to true and keep the database out of the whole idea of displaying a placeholder in the UI.

    AppendDataBoundItems is a nightmare when you have to re-bind or do funny stuff like paging.

    This is a situation that unfortunately does not offer a clear, WTF-proof solution. But the temp table approach is setting the bar pretty low.



  • Wow, didn't the "SelectCommand" attribute gave this person a clue of what it's supposed to do?

    OTOH, shouldn't .Net be forcing some control over this to allow queries only? So there, TRWTF is .Net :-)



  • When I see SQL code in view, I think .Net. No where else will you see a database table being directly bound to page controls, no middle layer of any sort.



  • @Ronald said:

    This is a situation that unfortunately does not offer a clear, WTF-proof solution.


    In the worst case one can try to subclass the control and override the relevant methods. (Of course, this isn't always easy with System.Web.UI because a lot of methods to which you need access to correctly override its virtual methods are internal rather than protected).



  • Wow... haven't seen SqlDataSource in a long time.  Did I go back in time to .NET 2.0? 

    This is why more people need to learn how to do shit correctly by using design patterns.  You don't have to deal with this shit if you use a variation of Model View Presenter, or hell even just grab an ICollection from your data layer/repository that way you can include the "dummy" value there.  Logic bound to a page in this day and age is fucking insane.



  • @pjt33 said:

    @Ronald said:

    This is a situation that unfortunately does not offer a clear, WTF-proof solution.

    In the worst case one can try to subclass the control and override the relevant methods. (Of course, this isn't always easy with System.Web.UI because a lot of methods to which you need access to correctly override its virtual methods are internal rather than protected).
     

    Encapsulation avoids all of those issues, and has other benefits....



  • @TheCPUWizard said:

    @pjt33 said:

    In the worst case one can try to subclass the control and override the relevant methods. (Of course, this isn't always easy with System.Web.UI because a lot of methods to which you need access to correctly override its virtual methods are internal rather than protected).
     

    Encapsulation avoids all of those issues, and has other benefits....


    The thing is that this is true even of ASP.Net controls whose sole purpose in life is to be subclassed. But this is getting off-topic, so I'll stop there.



  • @veggen said:

    When I see SQL code in view, I think .Net. No where else will you see a database table being directly bound to page controls, no middle layer of any sort.
    You see it with all software developed by novices.  .Net and PHP have more instances of this because those platforms are the ones that beginners choose.



  • @Ronald said:

    AppendDataBoundItems is a nightmare when you have to re-bind or do funny stuff like paging.

    This is a situation that unfortunately does not offer a clear, WTF-proof solution. But the temp table approach is setting the bar pretty low.

    You are correct that the AppendDataBoundItems can behave poorly under those circumstances.  But, it doesn't matter.  Pushing a UI feature (no selection displays "None" in control) down to the database is a bad idea and is just as bad a violation of separation of concerns as putting SQL in a view. The right answer is to find or build a control that behaves the way you want it to. There's bunch of jQuery plug-ins that you can slap on a standard <select> element to give the desired effect, you could write your own control and give it a property like "NullSelectionText", or you could extend Microsoft's control.  There's gotta be at least twenty good solutions to this problem.

  • Discourse touched me in a no-no place

    @Jaime said:

    There's gotta be at least twenty good solutions to this problem.
    Fortunately for this site, there's thousands of bad solutions too.



  •  @Jaime said:

    @veggen said:

    When I see SQL code in view, I think .Net. No where else will you see a database table being directly bound to page controls, no middle layer of any sort.
    You see it with all software developed by novices.  .Net and PHP have more instances of this because those platforms are the ones that beginners choose.

    It's a decent way to learn at first, the problem is you get people who learn and then say "I'm a developer now!" and keep using it.  I'm inclined to agree though, outside of .NET and PHP there is no ecosystem where it's encouraged or even shown frequently how to just crank out database code in a view without any concept of encapsulation or separation of concerns, beginner or otherwise.  Yet in .NET it's far too common to see demos show just dragging and dropping something, clicking through a few screens in a wizard, and having a somewhat functional simplistic app that showcases textbook worst practices for any serious development.

     


Log in to reply