T-SQL Instead of Insert, replace column values

create trigger SetNegativeBalanceToZero
on dbo.CoopBucksBalances

instead of insert

as

    if exists
    (
        select *
        from inserted
        where StartBalance < 0
    )
    begin
 INSERT INTO dbo.CoopBucksBalances (
        Branch,
        StartBalance,
PrevYearStartBalance,
CurrentDayUsage,
ImportDateTime

    ) SELECT
        Branch,
        0,
PrevYearStartBalance,
CurrentDayUsage,
ImportDateTime
     FROM
        INSERTED      
    end
    else
    begin
     INSERT INTO dbo.CoopBucksBalances (
        Branch,
        StartBalance,
PrevYearStartBalance,
CurrentDayUsage,
ImportDateTime

    ) SELECT
        Branch,
        StartBalance,
PrevYearStartBalance,
CurrentDayUsage,
ImportDateTime
     FROM
        INSERTED
    end

go

C# EventArgs

from msdn: http://msdn.microsoft.com/en-us/library/system.eventargs(v=vs.71).aspx

using System;

// FireEventArgs: a custom event inherited from EventArgs.

public class FireEventArgs: EventArgs {
    public FireEventArgs(string room, int ferocity) {
        this.room = room;
        this.ferocity = ferocity;
    }

    // The fire event will have two pieces of information-- 
    // 1) Where the fire is, and 2) how "ferocious" it is.  

    public string room;
    public int ferocity;

}    //end of class FireEventArgs


// Class with a function that creates the eventargs and initiates the event
public class FireAlarm {

    // Events are handled with delegates, so we must establish a FireEventHandler
    // as a delegate:

    public delegate void FireEventHandler(object sender, FireEventArgs fe);

    // Now, create a public event "FireEvent" whose type is our FireEventHandler delegate. 

    public event FireEventHandler FireEvent;    

    // This will be the starting point of our event-- it will create FireEventArgs,
    // and then raise the event, passing FireEventArgs. 

    public void ActivateFireAlarm(string room, int ferocity) {

        FireEventArgs fireArgs = new FireEventArgs(room, ferocity);

        // Now, raise the event by invoking the delegate. Pass in 
        // the object that initated the event (this) as well as FireEventArgs. 
        // The call must match the signature of FireEventHandler.

        FireEvent(this, fireArgs); 
    }
}    // end of class FireAlarm


// Class which handles the event

class FireHandlerClass {

    // Create a FireAlarm to handle and raise the fire events. 

    public FireHandlerClass(FireAlarm fireAlarm)    {

        // Add a delegate containing the ExtinguishFire function to the class'
        // event so that when FireAlarm is raised, it will subsequently execute 
        // ExtinguishFire.

        fireAlarm.FireEvent += new FireAlarm.FireEventHandler(ExtinguishFire);
    }

    // This is the function to be executed when a fire event is raised. 
 
    void ExtinguishFire(object sender, FireEventArgs fe) {

        Console.WriteLine("\nThe ExtinguishFire function was called by {0}.", sender.ToString());

        // Now, act in response to the event.

        if (fe.ferocity < 2)
            Console.WriteLine("This fire in the {0} is no problem.  I'm going to pour some water on it.", fe.room);
        else if (fe.ferocity < 5)
            Console.WriteLine("I'm using FireExtinguisher to put out the fire in the {0}.",  fe.room);
        else 
            Console.WriteLine("The fire in the {0} is out of control.  I'm calling the fire department!", fe.room);
    }
}    //end of class FireHandlerClass

public class FireEventTest {
    public static void Main ()     {    

        // Create an instance of the class that will be firing an event.

        FireAlarm myFireAlarm = new FireAlarm();
        
        // Create an instance of the class that will be handling the event. Note that 
        // it receives the class that will fire the event as a parameter. 

        FireHandlerClass myFireHandler = new FireHandlerClass(myFireAlarm);
        
        //use our class to raise a few events and watch them get handled
        myFireAlarm.ActivateFireAlarm("Kitchen", 3);
        myFireAlarm.ActivateFireAlarm("Study", 1);
        myFireAlarm.ActivateFireAlarm("Porch", 5);
        
        return;

    }    //end of main

}    // end of FireEventTest

.NET web performance HttpContext.Current caching

http://msdn.microsoft.com/en-us/magazine/cc163854.aspx

Earlier in the article, I mentioned that small improvements to frequently traversed code paths can lead to big, overall performance gains. One of my absolute favorites of these is something I've termed per-request caching.

Whereas the Cache API is designed to cache data for a long period or until some condition is met, per-request caching simply means caching the data for the duration of the request. A particular code path is accessed frequently on each request but the data only needs to be fetched, applied, modified, or updated once. This sounds fairly theoretical, so let's consider a concrete example.

In the Forums application of Community Server, each server control used on a page requires personalization data to determine which skin to use, the style sheet to use, as well as other personalization data. Some of this data can be cached for a long period of time, but some data, such as the skin to use for the controls, is fetched once on each request and reused multiple times during the execution of the request.

To accomplish per-request caching, use the ASP.NET HttpContext. An instance of HttpContext is created with every request and is accessible anywhere during that request from the HttpContext.Current property. The HttpContext class has a special Items collection property; objects and data added to this Items collection are cached only for the duration of the request. Just as you can use the Cache to store frequently accessed data, you can use HttpContext.Items to store data that you'll use only on a per-request basis. The logic behind this is simple: data is added to the HttpContext.Items collection when it doesn't exist, and on subsequent lookups the data found in HttpContext.Items is simply returned.

-----------------------------------------------------------

From http://petesbloggerama.blogspot.com/2007/11/systemwebcachingcache-httpruntimecache.html

System.Web.Caching.Cache, HttpRuntime.Cache, and IIS Recycles

Recently I chimed in on a newsgroup thread to an OP (Original Poster) who was questioning why he was losing Cache items. This individual would load a dataset from the Cache object into a local object to use to perform processing. He had been using httpcontext.current.cache and noticed that sometimes he was not able to get the dataset out of the cache. He tried switching to HttpRuntime.Cache and it seemed to help.

The OP claimed that the application is under heavy hits, and once or twice every month he would see this error. He wanted to know why does the HttpContext.Current.Cache call work sometimes, while some other times does not.

A couple of posters replied, one of whom is a respected MVP who usually makes very astute, high-quality posts. He posted,"The cache is only valid during the life of the request. how is you[sic] code using the cache?"

Somebody else (also pretty astute, usually) responded, "Cache is valid during lifetime of application not request.....".

What's the answer?

Just to make sure, I posted some response information:

"ASP.NET applications recycle periodically, especially if there is a lot of memory pressure. When this happens, Cache goes away and is then resurrected.
While there could be other explanations, this is the most likely one. It should not matter whether you use HttpContext or HttpRuntime..."

I got a response:

"recycling doesn't effect [sic] HttpRuntime.Cache, which only exists for the request. during a recycle, currently running requests continue in the old
appdomain, so they still have this data."

That's only partially true. Yes, existing requests continue to finish up on the original AppDomain. However, that doesn't explain why the Cache item would be gone on any subsequent request, which is what the OP was really asking. So I responded in more detail:

"The System.Web.Caching.Cache class can be accessed either through the static property System.Web.HttpRuntime.Cache or through the helper instance properties System.Web.UI.Page and System.Web.HttpContext.Cache. (or HttpContext.Current.Cache) It is therefore available outside the context of a request. There is only one instance of this object throughout an entire application domain. Recycling an AppDomain (e.g. by touching web.config, or due to IIS causing the recycle due to memory pressure, etc.) will drop Application, Cache, and in-process Session information since that is stored in the AppDomain logical process. You can do this simple test:

protected void Page_Load(object sender, EventArgs e)
{
Cache["test"] = "Stuff in Cache";
System.Web.HttpRuntime.UnloadAppDomain(); // similar to an IIS AppDomain RecycleResponse.Redirect("Default2.aspx");
// Default2.aspx simply does: Response.Write((string) Cache["test"]); -- you should see nothing.}

---"

The main point here is that if your Application is recycled either due to IIS health settings, the web tree contents being changed, or because your buggy code throws an unhandled exception, then your Session , Application, and Cache contents go "bye bye". There are no ifs, ands or buts about it. You are getting a new AppDomain, and it starts life with nothing in either Session, Application, or Cache.

The most efficient way to access the Cache is via the HttpRuntime.Cache object directly, the other methods involve extra parsing for the current HttpContext . It's a very minor optimization though. In point of fact, you can use the HttpRuntime.Cache class from Windows Forms or even a Windows Service, and it will work just fine. All you need is a reference to System.Web. It is very efficient and has neat Invalidation callbacks and other useful features that would be hard to duplicate. It's also much more lightweight than the Enterprise Library Caching framework and if you believe in traveling with "batteries included" as I do, this is the best way to go.

Linq to Umbraco datacontext httpcontext cache

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace Techready14
{
    public partial class TR14DataContext
    {
 

        private static object createContextLock = new object();

        public static TR14DataContext Instance
        {
            get
            {
                if (HttpContext.Current != null)
                {
                    var context = HttpContext.Current.Cache["TR14DataContext"] as TR14DataContext;
                    if (context == null)
                    {
                        lock (createContextLock)
                        {
                            if (context == null)
                            {

                                context = new TR14DataContext();
                                HttpContext.Current.Cache["TR14DataContext"] = context;
                            }
                        }
                    }

                    return context;
                }
                else
                    return new TR14DataContext();
            }
        }
    }
}

ASP.NET friendly urls with IIS7 HttpModule

from: http://weblogs.asp.net/scottgu/archive/2007/02/26/tip-trick-url-rewriting-with-asp-net.aspx

Using an HttpModule to Perform Extension-Less URL Rewriting with IIS7

The above HttpModule approach works great for scenarios where the URL you are re-writing has a .aspx extension, or another file extension that is configured to be processed by ASP.NET.  When you do this no custom server configuration is required - you can just copy your web application up to a remote server and it will work fine.

There are times, though, when you want the URL to re-write to either have a non-ASP.NET file extension (for example: .jpg, .gif, or .htm) or no file-extension at all.  For example, we might want to expose these URLs as our public catalog pages (note they have no .aspx extension):

http://www.store.com/products/Books
http://www.store.com/products/DVDs
http://www.store.com/products/CDs

With IIS5 and IIS6, processing the above URLs using ASP.NET is not super easy.  IIS 5/6 makes it hard to perform URL rewriting on these types of URLs within ISAPI Extensions (which is how ASP.NET is implemented). Instead you need to perform the rewriting earlier in the IIS request pipeline using an ISAPI Filter.  I'll show how to-do this on IIS5/6 in the Approach 4 section below.

The good news, though, is that IIS 7.0 makes handling these types of scenarios super easy.  You can now have an HttpModule execute anywhere within the IIS request pipeline - which means you can use theURLRewriter module above to process and rewrite extension-less URLs (or even URLs with a .asp, .php, or .jsp extension).  Below is how you would configure this with IIS7:

<?xml version="1.0" encoding="UTF-8"?>
<configuration>

  <configSections>
    <section name="rewriter"
             requirePermission="false"
             type="Intelligencia.UrlRewriter.Configuration.RewriterConfigurationSectionHandler, Intelligencia.UrlRewriter" />
  </configSections>
 
  <system.web>
     
    <httpModules>
      <add name="UrlRewriter" type="Intelligencia.UrlRewriter.RewriterHttpModule, Intelligencia.UrlRewriter" />
    </httpModules>
   
  </system.web>

  <system.webServer>

    <modules runAllManagedModulesForAllRequests="true">
      <add name="UrlRewriter" type="Intelligencia.UrlRewriter.RewriterHttpModule" />
    </modules>

    <validation validateIntegratedModeConfiguration="false" />

  </system.webServer>

  <rewriter>
    <rewrite url="~/products/(.+)" to="~/products.aspx?category=$1" />
  </rewriter>
  </configuration>

Note the "runAllManagedModulesForAllRequests" attribute that is set to true on the <modules> section within <system.webServer>.  This will ensure that the UrlRewriter.Net module from Intelligencia, which was written before IIS7 shipped, will be called and have a chance to re-write all URL requests to the server (including for folders).  What is really cool about the above web.config file is that:

1) It will work on any IIS 7.0 machine.  You don't need an administrator to enable anything on the remote host.  It will also work in medium trust shared hosting scenarios.

2) Because I've configured the UrlRewriter in both the <httpModules> and IIS7 <modules> section, I can use the same URL Rewriting rules for both the built-in VS web-server (aka Cassini) as well as on IIS7.  Both fully support extension-less URLRewriting.  This makes testing and development really easy.

IIS 7.0 server will ship later this year as part of Windows Longhorn Server, and will support a go-live license with the Beta3 release in a few weeks.  Because of all the new hosting features that have been added to IIS7, we expect hosters to start aggressively offering IIS7 accounts relatively quickly - which means you should be able to start to take advantage of the above extension-less rewriting support soon.  We'll also be shipping a Microsoft supported URL-Rewriting module in the IIS7 RTM timeframe that will be available for free as well that you'll be able to use on IIS7, and which will provide nice support for advanced re-writing scenarios for all content on your web-server.

C# Consume REST Service using HttpClient

HttpClient http = new HttpClient();
        HttpQueryString qs = new HttpQueryString();
        qs.Add("code", "xxx");
        qs.Add("apikey", "xxx");
        Uri u = new Uri("http://xxx");
        HttpResponseMessage rm = http.Get(u,qs);
        rm.EnsureStatusIsSuccessful();    
        string s = rm.Content.ReadAsString();
 

T-SLQ Stored Procedure Return Value

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE GetUserCourseCompletedPercent
@UserEmail nchar(100),
@CourseId int

AS
BEGIN

SET NOCOUNT ON;
DECLARE @TotalCount REAL;
DECLARE @CompletedCount REAL;
(select @TotalCount =
count(ua.id)
from
 [MSInvolveOD].[dbo].[msinvolveCourseModule] m
 join [MSInvolveOD].[dbo].[msinvolveCourse] c
 on m.courseid = c.id
 join [MSInvolveOD].[dbo].[msinvolveModuleActivity] ma
 on
 ma.moduleid = m.id
 join [MSInvolveOD].[dbo].[msinvolveUserActivity] ua
 on ma.id = ua.activityid
 where c.id = @CourseId
 and ua.userid = @UserEmail )


select @CompletedCount =
count(ma.id)
from
 [MSInvolveOD].[dbo].[msinvolveCourseModule] m
 join [MSInvolveOD].[dbo].[msinvolveCourse] c
 on m.courseid = c.id
 join [MSInvolveOD].[dbo].[msinvolveModuleActivity] ma
 on
 ma.moduleid = m.id
 where c.id = @CourseId
 RETURN ROUND (( (@TotalCount / @CompletedCount) * 100 ), 0)                                                              

END
GO