Archives

Archives / 2008 / February
  • Easy SQL “If Record Exists, Update It. If Not, Insert It.”

    A very common scenario is the one where we want to update the information in a record if it already exists in the table, and if it doesn’t exist, we want to create a new record with the information. 

    The most common solution to this problem is using IF EXISTS  (subquery).  This comes to mind first because it matches how we think about the problem (as you can see by the title of this article!).  We say “If the criterion exists in this initial subquery, then I’ll do this.  If not, I’ll do this other thing.”  This results in a three-step process:

    1. Do the subquery (SELECT whatever FROM wherever WHERE something).
    2. Evaluate the EXISTS statement (is it there or not?).
    3. Execute either the UPDATE or INSERT statement.

    Now, let’s try an “unnatural” shortcut.  I say unnatural because it doesn’t follow that “natural” logic occurring in our brain that I mentioned above.  Instead, let’s just do the update, and if it fails, then we’ll do the insert.  When the update fails, that just means that no rows were affected, not that an error was thrown.  Now we are down to a one-step (if the update succeeds) or two-step process (if we have to insert instead).  This is much more efficient!

    Example:

    This is not necessarily a practical example, but let’s say that we have a table called “Users” which has three fields:  “UserID”, “FirstName”, and “LastName”.  If a record already exists with the specified UserID, simply update it with the new @FirstName and @LastName values.  If it does not exist, create a new record with those values.

    CREATE PROCEDURE dbo.spAddUserName
         (
         @UserID AS int,
         @FirstName AS varchar(50),
         @LastName AS varchar(50)
         )
    AS
         BEGIN
              DECLARE @rc int    
    
              UPDATE [Users]
                 SET FirstName = @FirstName, LastName = @LastName
               WHERE UserID = @UserID   
    
              /* how many rows were affected? */
              SELECT @rc = @@ROWCOUNT    
    
              IF @rc = 0
                   BEGIN
                        INSERT INTO [Users]
                                    (FirstName, LastName)
                             VALUES (@FirstName, LastName)
                   END         
    
         END

    Read more...

  • View Source Trick for Pages with Partial Rendering

    Many people when developing want to look at the browser’s View Source to make sure that things are being outputted correctly or to see where in the DOM certain things are showing up. However, if you are using the Ajax concept of partial rendering (usually via UpdatePanels), what you get is the initial state of the page when it was first loaded before any partial page updates, not the state of the page as it is currently.

    To see the “current” source for the page, simply paste the following code into your browser’s location bar. It’s a little long, but it covers most browsers.

    javascript:if (typeof(window.document.body.outerHTML) != 'undefined'){'<xmp>'+window.document.body.outerHTML+'</xmp>'} else if (typeof(document.getElementsByTagName('html')[0].innerHTML) != 'undefined'){ '<xmp>'+document.getElementsByTagName('html')[0].innerHTML+'</xmp>'} else if (typeof(window.document.documentElement.outerHTML) != 'undefined'){ '<xmp>'+window.document.documentElement.outerHTML+'</xmp>'} else { alert('Your browser does not support this.') }

    Read more...

  • Controlling the ASP.NET Timer Control with JavaScript

    Have you ever wanted to control your <asp:Timer> control from client-side code?

    Let’s say you’ve named your timer ‘Timer1’. The first step is to create a reference to this component:

    var timer = Sys.Application.findComponent(‘<%= Timer1.ClientID %>’);

    Or, better yet, use the $find() shortcut:

    var timer = $find(‘<%= Timer1.ClientID %>’);

    You can then easily access the timer’s interval and enabled properties, as well as start and stop the timer.

    //returns the timer’s interval in milliseconds:
    var waitTime = timer.get_interval;       
    
    //sets the timer’s interval to 5000 milliseconds (or 5 seconds):
    timer.set_interval(5000);       
    
    //returns whether or not the timer is enabled:
    var isTimerEnabled = timer.get_enabled();       
    
    //disables the timer:
    timer.set_enabled(false);       
    
    //starts the timer:
    timer._startTimer();       
    
    //stops the timer:
    timer._stopTimer();

    For the more adventurous of you who would like to look at the client-side behavior code for the Timer control and who elected to download the source code for the AJAX Control Toolkit, you can probably find the .js file at:

    Drive:\Program Files\Microsoft ASP.NET\AJAX Control Toolkit\AJAXControlToolkit\Compat\Timer\Timer.js

    (If you don’t know what you are doing, do not make any changes to this file!)

    Read more...

  • A Client-side Ajax Login for ASP.NET

    A question was posed on the ASP.NET forums recently asking how to have a login control that doesn’t refresh the page.  The ideal solution would be to just drop an ASP.NET Login control inside an updatepanel.  However, the Login control (along with PasswordRecovery, ChangePassword, and CreateUserWizard controls whose contents have not been converted to editable templates) is not supported inside an UpdatePanel.  They are just not compatible with partial-page updates.

    The ASP.NET AJAX Library does include a proxy class that allows client-side authentication, the Sys.Services.AuthenticationService Class!  We can therefore create a very simple and straight-forward “roll-your-own” login solution that does not require a full postback.

    Getting Started:

    I am going to assume that you have completed all of the normal, necessary steps to allow authentication, but the authentication service is not enabled by default.  You must enable it in the web.config file.  Add the following to your web.config within the <configuration> section:

      <system.web.extensions>
        <scripting>
          <webServices>
            <authenticationService enabled="true" requireSSL="false"/>
          </webServices>
        </scripting>
      </system.web.extensions>

    Next, create two DIVs on your page:  one for the “anonymous view” and one for the “logged-in view”.  Set the the display for both DIVs to ‘none’.

      <div id="AnonymousView" style="display: none;">
          <input id="txtUsername" type="text" /><br />
          <input id="pwdPassword" type="password" /><br />
          <input id="chkRememberMe" type="checkbox" />Remember Me<br />
          <input id="btnLogIn" type="button" value="Log In" />
      </div>
      <div id="LoggedInView" style="display: none;">
          Logged in.<br />
          <input id="btnLogOut" type="button" value="Log Out" />
      </div>

    The get_isLoggedIn() property of the class then allows you to show and hide the DIVs appropriately.

      var ssa = Sys.Services.AuthenticationService;
      if (ssa.get_isLoggedIn()) {
          $get('LoggedInView').style.display = '';
      } else {
          $get('AnonymousView').style.display = '';
      }

    The Complete Code:

      <body>
        <form id="form1" runat="server">
            <asp:ScriptManager ID="ScriptManager1" runat="server" />
            <div id="AnonymousView" style="display: none;">
                <input id="txtUsername" type="text" /><br />
                <input id="pwdPassword" type="password" /><br />
                <input id="chkRememberMe" type="checkbox" />Remember Me<br />
                <input id="btnLogIn" type="button" value="Log In" />
            </div>
            <div id="LoggedInView" style="display: none;">
                Logged in.<br />
                <input id="btnLogOut" type="button" value="Log Out" />
            </div>
        </form>
    </body>
            <script type="text/javascript">
                // Hook up the click events of the log in and log out buttons.
                $addHandler($get('btnLogIn'), 'click', loginHandler);
                $addHandler($get('btnLogOut'), 'click', logoutHandler);
                var ssa = Sys.Services.AuthenticationService;
                if (ssa.get_isLoggedIn()) {
                    $get('LoggedInView').style.display = '';
                } else {
                    $get('AnonymousView').style.display = '';
                }
               
                function loginHandler() {
                    var username = $get('txtUsername').value;
                    var password = $get('pwdPassword').value;
                    var isPersistent = $get('chkRememberMe').checked;
                    var customInfo = null;
                    var redirectUrl = null;
                    // Log them in.
                    ssa.login(username,
                              password,
                              isPersistent,
                              customInfo,
                              redirectUrl,
                              onLoginComplete,
                              onError);
                }
               
                function logoutHandler() {
                    // Log them out.
                    var redirectUrl = null;
                    var userContext = null;
                    ssa.logout(redirectUrl,
                               onLogoutComplete,
                               onError,
                               userContext);
                }
               
                function onLoginComplete(result, context, methodName) {
                    // Logged in.  Hide the anonymous view.
                    $get('LoggedInView').style.display = '';
                    $get('AnonymousView').style.display = 'none';
                }
               
                function onLogoutComplete(result, context, methodName) {
                    // Logged out.  Hide the logged in view.
                    $get('LoggedInView').style.display = 'none';
                    $get('AnonymousView').style.display = '';
                }
               
                function onError(error, context, methodName) {
                    alert(error.get_message());
                }
             
            </script>

    Comments and Possible Enhancements:

    • There will always be a page refresh on logout.  This is necessary to ensure that any user-specific information is cleared from the page.
    • You can place the two DIVs inside a third DIV and style that, thus showing a consistant style (width, height, border, etc.) for both child DIVs.
    • You will have probably noticed that there is a redirectUrl parameter for both the login() and logout() methods.  This, along with querystring parameters, could easily be adapted to create a login page that users are redirected to for authentication and redirected from once logged in.  

    References:

    http://asp.net/ajax/documentation/live/ClientReference/Sys.Services/AuthenticationServiceClass/default.aspx

    Read more...