Digital signatures are used to verify the integrity of data being passed from the originator (the signer) to a recipient (the verifier). The signatures are generated and verified using public key cryptography.

The signer of a message has a pair of cryptographic keys: a public key, which everyone in the world knows, and a private key, which is kept secret by the signer.

The verifier knows only the public key, which is used to verify that the signer knew the private key and the message.

Strong Named Assemblies

• Strong names offer a powerful mechanism for giving .NET Framework assemblies unique identities.
  
• The strong name for an assembly consists of five parts:
  • a public key (RSA cryptographic public key that helps verify the assembly's authenticity)
  • a simple name (text string—usually the name of the file (without the extension) that contains the assembly)
  • a version (four-part version number, in the form of Major.Minor.Build.Revision (i.e.  1.0.0.1)
  • an optional culture (Target audience for the assembly, such as "neutral" (default audience), "en-us" (English – United States) or "fr" (France) etc.)
  • and an optional processor architecture (Defines the assembly's format, such as MSIL (intermediate language) or x86 (binary for Intel x86 processors)
    
• An assembly is strong-named during the build process using the private key which corresponds to the public key in the strong name.  The strong name can be verified using the public key.
  
• The .NET Framework verifies the strong name signature.  If it cannot be verified the Framework will not load the assembly.
  
• The above has one exception.  Strong named assemblies that are used in the GAC (Global Assembly Cache) are not verified each time the .NET Framework loads them.  The assemblies in the GAC are verified only at the time they are installed in the GAC as the GAC already is locked down and assemblies can only be installed by an individual who has admin permissions.  Once an assembly was installed in the GAC, it's considered to be safe and being verified earlier (during gacutil.exe phase)
  
• Why use strong names…Strong names prevent others from spoofing your code.  i.e. a malicious user cannot modify your code and successfully re-sign it.  Strong names are required to store shared assemblies in the global assembly cache (GAC). This is because the GAC allows multiple versions of the same assembly to reside on your system simultaneously, so that each application can find and use its own version of your assembly. This helps avoid DLL Hell, where applications that may be compiled to different versions of your assembly could potentially break because they are all forced to use the same version of your assembly.
  
• Strong name signatures do not contain any reliable information about the publisher.  In order to trust keys from other organizations ensure you have a secure channel to get their public key.  Remember that through the public key the strong named assembly can be verified that it was signed with the corresponding private key.  Hence strong names are secure only when the strong name private key is kept secure.
  
• Strong-name signing is a good idea for most applications, especially those that are deployed over a network or any medium not fully controlled by the deployer.
  
• All assemblies referenced by a strong-named assembly must also be strong-named. If you reference an assembly written by a third party that is not strong-name signed, you cannot strong-name sign your assembly.
  
• Strong-name signing makes servicing more complicated. Under current versioning policy, an assembly will always attempt to load the exact version of the assembly it was built against. If, for example, your application was built against version 1.0.0.0 of a strong-named assembly and you fix a bug in the assembly, bumping the version number to 1.0.0.1, the existing application will not find the updated assembly.

  There are a few ways to deal with this situation. You can rebuild the application against the new assembly. Obviously, this is an annoying process for just picking up a bug fix and it may not be an option in some situations. Still, it works fine when your code is not widely deployed or the assembly being serviced is not widely shared across applications.

  Another option is if the assembly is installed in the GAC, you can use publisher policy to redirect loads for version 1.0.0.0 to version 1.0.0.1. However, publisher policy is complicated.

  Yet another option is to fix the bug and not change the assembly version number, so existing applications can still find the assembly. This is the approach the .NET Framework uses for bug fixes, although it’s not appropriate for new features or anything that breaks compatibility. If you are adding new features, you should bump the assembly version up and have applications rebuild to opt in to the new features.

• Assemblies loaded in the GAC perform better as they are loaded from central system space and the verification process has already been completed.  The strong name ensures correct component versioning helping to prevent components with the same name from conflicting with each other.
  
• To install assemblies in the GAC they must be strong named(signed).
  
• Delay signing allows you to generate a partial signature during development with access only to the public key. 
  • The private key can be stored securely out of the hands of the developers and used to apply the final strong name signature just before shipping your code. 
  • Use the public key to delay sign your assemblies during development.
  • Because the assemblies are not fully signed yet, you’ll also have to configure your development machines to skip strong name signature verification for your key—otherwise, the .NET Framework will not allow you to load the delay-signed assemblies.  You must configure the .NET Framework to skip verification for the delay-signed assemblies using your public key. To do this, you use the sn.exe tool again. i.e. sn –Vr {assemblyName} 
    
    Another options…to configure your development machine to skip all verification for delay signed assemblies signed with a particular key.  Using sn –T {assemblyName} will give you the public key token.  Then execute the following to skip strong name verification for any assembly using that public key token sn – Vr *,{public token}  Any assembly delay signed with that public key will now skip strong name signature verification and run on your development machine.  It should never be done on production computers because it opens up those machines to assembly spoofing attacks. 
    
    Another technique to acquire the public key token is to use the public key file alone.
    i.e. sn – tp {publickeyfile}
    
  • When you are ready to ship your code, use the (well guarded) private key to apply the final strong name signature to your delay-signed assemblies.  sn –R {assemblyName} {your key file}  Now your assembly has a full signature.  It is possible to use command line “For” command to iterate over each dll saving a lot of typing
    i.e. for {%variable|%%variable} in (set) do command [ CommandLineOptions]
    for %f in (*.dll) do sn –R %f MyKeyFile.snk
    
    if you use this within a batch file replace %f with %ff
    

Additional Notes:

• Create a strong name key (often your organization will already have one established)
  i.e. sn –k MyKeyFile.snk  This creates a strong name key pair (both public and private keys in the same file)
• Developers typically only need the public key portion.  sn –p MyKeyFile.snk MyKeyFilePublic.snk  This extracts the public key and creates a new file MyKeyFilePublic.snk.  At this point, MyKeyFile.snk should be put in a save location and available to Administrators only
• To access the public key value and token sn – tp MyKeyFIlePublic.snk.  At this point, store the output in a text file somewhere.  We will use both the public key value and token.
  

Within the .NET Framework data types such as Boolean can either be true or false. Right?  Well, with the release of 2.0 Framework it has been possible to create nullable data types.  A nullable type can represent all the values of the underlying type plus empty (or undefined).  In the case of our Boolean it would be True/False and Nothing.

It is only possible to create a nullable types Nullable(Of T) for value types since the default value for reference types is already nothing.  The following types are valid as nullable: Boolean, Byte, Int16, Int32, Int64, Single, Double, Decimal, DateTime.  The following examples show examples on how to create a nullable variable type:

Dim success as New Nullable(Of Boolean)()

Dim customerId as New Nullable(Of Integer)()

With the release of .NET 3.5 it is also possible to use the short form versions:

Dim success as Boolean?

Dim customerId as Integer?

In C#

bool? success;
int? customerId;

To check of a nullable type as a value it is possible to use the HasValue  or Value properties.  Use the System.Nullable.GetValueOrDefault property return either the assigned value, or the default value for the underlying type.  

If customerId.HasValue Then

  ‘do something

End If

If assigning a nullable type to a non-nullable type you must cast operator is necessary.  For example:

int newCustomerId = (int)customerId;  // in C#

Nullable types can be useful when dealing with NULL values from a database query however they are not the same.  That is to say that DBNULL value is not the same as Nothing.  When returning data from the database the following code will be necessary:

int? customerId = null;

if (!DBNULL.Value.Equals(reader[“CustomerId”])){    // this code converts the DBNULL to a c# null
  customerId = (int)reader[“CustomerId”];
}

Generics provide a way for developers to define subroutines, functions, fields, properties as well as classes, structures, interfaces and even delegates in such a way that the parameters are not of any particular type.  In a sense the constructs are defined in a generic approach.

The .NET Framework contains the following namespaces:System.Collections, System.Collections.Generic.    The latter mimics very much the features of the former. 

System.Collections namespace (nongeneric)

ICollection is the most primitive interface of the System.Collections

Public Interface ICollection

  Inherits IEnumerable

  Sub CopyTo(ByVal array As Array, ByVal index As Integer)

  ReadOnly Property Count() As Integer

  ReadOnly Property IsSynchronized() As Boolean

  ReadOnly Property SyncRoot() As Object

End Interface

IDictionary is a simple collection that maintains a set of name/value pairs.  Using this type allows you such features as returning an item by say license plate number or persons name for example.

Public Interface IDictionary

  Inherits ICollection, IEnumerable

  Sub Add(ByVal key As Object, ByVal value As Object)

  Sub Clear()

  Function Contains(ByVal key As Object) As Boolean

  Function GetEnumerator() As IDictionaryEnumerator

  Sub Remove(ByVal key As Object)

  ReadOnly Property IsFixedSize() As Boolean

  ReadOnly Property IsReadOnly() As Boolean

  Property Item(ByVal key As Object) As Object

  ReadOnly Property Keys() As ICollection

  ReadOnly Property Values() As ICollection

End Interface

IList provides the ability to insert, remove and index items

Public Interface IList

  Inherits ICollection, IEnumerable

  Function Add(ByVal value As Object) As Integer

  Sub Clear()

  Function Contains(ByVal value As Object) As Boolean

  Function IndexOf(ByVal value As Object) As Integer

  Sub Insert(ByVal index As Integer, ByVal value As Object)

  Sub Remove(ByVal value As Object)

  Sub RemoveAt(ByVal index As Integer)

  ReadOnly Property IsFixedSize() As Boolean

  ReadOnly Property IsReadOnly() As Boolean

  Property Item(ByVal index As Integer) As Object

End Interface

So far I have show the key interfaces within System.Collections.  The interfaces alone do not provide the implementation.  The following will document the relationships between the above interfaces and the specific types within the System.Collections namespace.

While the above are types of System.Collections there are additional types within the System.Collections.Specialized such as BitVector32 (collection of booleans), CollectionsUtil (ignores case in strings), HybridDictionary (starts as ListDictionary however it switches automatically to hashTable as the collection gets large), ListDictionary (for small collections of 10 items or less), NameValueCollection (sorted collection of string keys and string values), StringCollection (collection of strings), StringDictionary (a hashtable with key values strongly typed to string type), StringEnumerator.

Now with respect to the System.Collections.Generic namespace; it contains a series of types that leverage the features of the for mentioned collections however they bring to the forefront the advantages: improved performance by avoiding boxing and unboxing, type-safety, code maintenance issues.  The generic collection when declared specifies the type of the collection item.  In the following example we create a generic collection that can only contain integers.  If the developer attempts to insert anything other than an Integer they will get a design time error.

Boxing/Unboxing is a term that is used when a value type variable is stored in a reference type structure.  This occurs with all nongeneric types as they operate of the System.Objects type.

For example:
Dim ar as New ArrayList()
ar.Add(123) ‘boxing occurs
Dim i as Integer = CType(ar(0), Integer) ‘unboxing occurs

Using a generic collection:
Dim ar as New List(Of Integer)
ar.Add(10) ‘no boxing
Dim i as Integer = ar(0) ‘no unboxing

The following is a list of interfaces within the System.Collections.Generic namespace: ICollections(Of T), IComparer(Of T), IDictionary(Of K, V), IEnumerable(Of T), IEnumerator(Of T), and IList(Of T).  There respective classes are as follows:

The convention is that generic types specify their placeholders typically with T to represent the type of variable the collection is to contain.

So far we have discussed generic in the sense of a collection class.  With all the same benefits a method can be defined as generic.  The following example shows how the method was defined to take two parameters of Type T.  Using a generic function like this can allow one function to operate on more than one type of variable.  The convention is to follow the method name with the type of the parameters passed in.  Calling the following function would look something like Test(Of String)(“test1”, “Test2”)

Public Function Test(Of T)(ByRef o1 as T, ByRef o2 as T) as T
  ‘do something
End Function

It is possible to establish constraints with respect to the type of T.  The following is a complete list of the types of constraints:
Of T as Structure > the type parameter must be a value type (ie. structure)
Of T as Class > the type parameter must be a reference type (i.e. classes)
Of T as New > the type parameter must have a default constructor
Of T as NameOf BaseClass > the type parameter must be derived from the class specified by NameOfBaseClass
Of T as NameOfInterface > the type parameter must implement the interface specified by NameOfInterface

’new items must have a default constructor
Public Class TestClass(Of T as New)
  ‘stuff here
End Class

The term generic can apply to Interfaces as well. In any case the interface will define the contract that the implementing class must adhere to.  When implementing a generic interface the supporting type specifies the placeholder type:

Public Interface IMyInterface(Of T)
  Function Add(ByVal x as T, ByVal y as T) as T
End Interface

Public Class MyClass
  Implements IMyInterface(Of Integer)

Public Function Add(ByVal x as Integer, ByVal y as Integer) as Integer
  Return x + y
End Function

By all means utilize the generic collection namespace over the system.collection nongeneric namespace.  In addition to improved performance(avoiding boxing/unboxing) your code will avoid type-safety issues of earlier releases of .NET.

Web Sites 

1.) Microsoft Data Platform Site – news and updated releases of their most recent data products

2.) ADO.NET Team blog - With so many new technologies and approaches this seems to be the source of the news with respect to data.

Development 

3.) Excel generation on server (CodePlex)

4.) James Newton-King Json.NET - The Json.NET library makes working with JavaScript and JSON formatted data in .NET simple. Quickly read and write JSON using the JsonReader and JsonWriter or serialize your .NET objects with a single method call using the JsonSerializer.

5.) LinqToSQL A Detailed Review by Jesse Libery

6.) JQuery – 10 Tips for Better Code – check out also Best JQuery Tutorials here

Collaboration

8.) EtherPad – web based word processor that allows people to work together

9.) Microsoft SharedView - Connect with up to 15 people in different locations and get your point across by showing them what's on your screen. Share, review, and update documents with multiple people in real time.A Windows Live ID (Passport, Hotmail, or MSN) is required to start sessions, but not to join sessions. New in version 1.0: we have added a web based join experience to make SharedView even easier.

Operating Systems  

10.)  Windows 7 Edition Comparison Chart 

11.)  Windows XP to Windows 7 Upgrade

Tools
12.)  ImgBurn – DVD Burner utility (Free)

13.) WinDirStat:Windows Directory Statistics (Free)

Fun

14.)  Mondays – fun (sometimes crude) podcast with Carl Franklin(DotNetRocks), Karen Greenwald, Mark Miller(CodeRush) and Richard Campbell(RunAs Radio)

Scenario: While a partial page postback is occurring disable all form fields and give the user a pleasant please wait message that can be used consistently across your application.  We need to create an overlay that works in conjunction with the updatepanel as well as direct from JavaScript.

Problem: Design the update progress html and css so that the entire page is overlaid with gray background.  This works pretty good until scrolling on the page is involved.  When the page is tall enough that the browser scrolls the overlay must by sized dynamically with each post.  If you fail to do this, you will end up with odd visual with the overlay only covering a portion of the page.

The problem above is resolved in the solution described within this post.  It covers the asp.net, html, css and JavaScript code to accomplish the above in re-usable fashion.

I was using the UpdatePanel control which enables you to build rich, client-centric web applications.  By using the UpdatePanel controls you can refresh selected parts of the page instead of refreshing the whole page with a postback.  While the partial page post back occurs an UpdateProgress control is available to provide the user with a friendly ‘please wait’ message.  This all works seamlessly out of the box. 

In my scenario I needed to overlay and disable all controls (make unavailable) for the user while the partial page update happens.  The application allows for file uploads (which cannot participate in partial page updates).  A full post back must occur for this to work correctly. 

My ASPX page has the following structure with typical UpdatePanel, ContentTemplate, Triggers and UpdateProgress controls.  The trigger designates that the btnSubmitAdmin will perform a full postback.  I want the full postback user interaction to look similar as the partial page updates.  As a result, I will be using the same updateprogress visual for both partial and full postbacks.

<body onload="fncOnLoad()">

<form id="form1" runat="server">

    <asp:ScriptManager ID="Scriptmanager1" runat="server" EnablePartialRendering="true"></asp:ScriptManager>

    <asp:UpdatePanel ID="updatePanel1" runat="server"  UpdateMode="Conditional" >

       <ContentTemplate>               

        <!-- Content Goes Here -->                

          </ContentTemplate>              

        <Triggers>

            <asp:PostBackTrigger ControlID="btnSubmitAdmin" />            

        </Triggers>     

   </asp:UpdatePanel>                    

   <asp:UpdateProgress ID="UpdateProgress1" runat="server" AssociatedUpdatePanelID="updatePanel1" DynamicLayout="true" DisplayAfter="1000">

        <ProgressTemplate >

             <uc1:WaitPanel ID="WaitPanel1" runat="server" />

        </ProgressTemplate>

    </asp:UpdateProgress>    

</form>

As you can see above the updateprogress has a user control WaitPanel.  It is this html/css that will be shown when the user performs a postback.  The WaitPanel is registered at the top of the page.

<%@ Control Language="VB" AutoEventWireup="false" CodeFile="WaitPanel.ascx.vb" Inherits="Uc_WaitPanel" %>

        <div id="OuterTableCellOverlay">

            <div id="InnerTableCellOverlay">

                <b>... Please Wait ...</b>

                <br />

                <asp:Image runat="server" ImageUrl="~/Images/indicator_waitanim.gif" />                

            </div>

        </div>  

I wanted this solution to be easily applied to other pages as well as other projects.  As a result, I created both wait.css and wait.js files that can be copied across projects.  These files functional aspects of the wait.  The user control in the page load registers both the style sheet (css) and includes the JavaScript library.

Partial Class Uc_WaitPanel

    Inherits System.Web.UI.UserControl

    Protected Sub Uc_WaitPanel_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Dim css As New HtmlLink()

        With css

            .Href = ResolveClientUrl("~/Style/Wait.css")

            .Attributes("rel") = "stylesheet"

            .Attributes("type") = "text/css"

            .Attributes("media") = "all"

        End With

        Page.Header.Controls.Add(css)

        Dim url As String = ResolveClientUrl("~/Scripts/Wait.js")

        Page.ClientScript.RegisterClientScriptInclude("wait", url)

    End Sub

End Class

So far I have shown the aspx code and the code that displays the please wait message.  When this is displayed I am using CSS to overlay a gray and place ‘please wait..’ in the forefront.

/* updateprogress css */

    #OuterTableCellOverlay

    {

        background-color: white;  

        filter:alpha(opacity=85);

        -moz-opacity:0.85;        

        z-index: 999;

        width: 100%;

        height: 100%;

        position: absolute;

        top: 0;

        left: 0;                        

    }

    #InnerTableCellOverlay

    {

        border: 1px solid black;

        padding: 10px;

        background-color: #eee;

        z-index: 998;

        background-color: #eee;

        filter:alpha(opacity=100);

        position: absolute;

        top: 0pt;

        left: 0pt;            

        text-align: center;        

    }

As it stands right now when a partial page update is performed the ‘please wait’ message will be shown in the forefront with gray background on the complete page.
I now want the following… 1.)  to show this same image when performing full postbacks 2.) I want to be able to use this from JavaScript without the update panel.  In order to use from JavaScript (after the user selects submit) I attach a function via

<asp:Button ID="btnSubmitAdmin" runat="server" CssClass="btn" style="width:100px;" 

          OnClientClick="return fncValidateSubmit('Ready to Submit?');" Text="Save" 

          ToolTip="Save" Visible="false" />

//validate before submitting signature authority            

    function fncValidateSubmit(msg) {

        //perform some client side validation checks here...

        if (valid == true) {

            var answer = confirm(msg);                    

            if (answer) {

                ShowWait('UpdateProgress1');

                return answer;

            }

        } else {

            alert(errmsg);

            return false;

        }

    }

Here you will notice a call to ShowWait(‘UpdateProgress1’);  This function is within the Wait.js file that was included with the user control.

function ShowWait(progressControlId) {

    SetWaitDimensions();

    document.getElementById(progressControlId).style.display = 'block';

}

So all very cool that accomplishes 1.) above showing the same update progress message when performing a full postback.  If I want to show this message at any time I can simply call ShowWait and pass in the id of the container for the Wait message.  At this point, the code that is needed is simply the user control, the style sheet and the wait JavaScript library.

The big issue that remains is to have the overlay cover the entire browser window independent of any scrolling.  This was an problem.  Without this adjustment after the user scrolled down half the page and performed any sort of postback the gray overlay was only shown on a portion of the page leaving a very ugly user interface.

The solution: involved calling a JavaScript function upon body load.  Upon page load loadAjaxHandlers() is called.   This function is necessary to intercept between the user clicking the button and the partial post occurring.  This enables us to run some JavaScript to size the overlay and center the wait message properly.  Below you can see that I am calling SetWaitDimensions before the post.  This function determines the browser window as well as the scroll dimensions and determines the size of the required overlay and also how to center the wait message.

////call the following from <body onload="load()">

////required so that background blur is dimensioned correctly (with respect to scroll issues with browser)

////enable js to be called after ajax postback

function loadAjaxHandlers() {

    Sys.WebForms.PageRequestManager.getInstance().add_endRequest(EndRequestHandler);

    //will process during the initialization of the postback

    Sys.WebForms.PageRequestManager.getInstance().add_initializeRequest(

                    function() {

                        SetWaitDimensions();

                    }

               )

}

The following is my complete Wait.js file which you can see sizes the overlay and positions the wait message.

////call the following from <body onload="load()">

////required so that background blur is dimensioned correctly (with respect to scroll issues with browser)

////enable js to be called after ajax postback

function loadAjaxHandlers() {

    Sys.WebForms.PageRequestManager.getInstance().add_endRequest(EndRequestHandler);

    //will process during the initialization of the postback

    Sys.WebForms.PageRequestManager.getInstance().add_initializeRequest(

                    function() {

                        SetWaitDimensions();

                    }

               )

}

function ShowWait(progressControlId) {

    SetWaitDimensions();

    document.getElementById(progressControlId).style.display = 'block';

}

//set dimensions of blur element, positions progress box

function SetWaitDimensions() {

    if (document.getElementById) {

        var blur = document.getElementById('OuterTableCellOverlay');

        var progress = document.getElementById('InnerTableCellOverlay');

        progress.style.width = '444px';

        progress.style.height = '100px';

        var vp = getViewport();

        var dm = getElementDimensions(document.body);

        var sp = getScrollPosition();

        if (vp.height > dm.height)

            blur.style.height = vp.height + 'px';

        else

            blur.style.height = dm.height + 'px';

        blur.style.width = '100%';

        blur.style.top = (sp.y + ((vp.height - dm.height) / 3)) + 'px';

        blur.style.left = (sp.x + ((vp.width - dm.width) / 2)) + 'px';

        progress.style.top = document.documentElement.clientHeight / 3 - progress.style.height.replace('px', '') / 2 + 'px';

        progress.style.left = document.body.offsetWidth / 2 - progress.style.width.replace('px', '') / 2 + 'px';

    }

}

//returns view port dimensions

function getViewport() {

    var v = { width: 0, height: 0 };

    if (window.innerHeight) {

        v.height = window.innerHeight;

        v.width = window.innerWidth;

    } else if (document.documentElement.clientHeight) {

        v.height = document.documentElement.clientHeight;

        v.width = document.documentElement.clientWidth;

    } else {

        v.height = document.body.clientHeight;

        v.width = document.body.clientWidth;

    }

    return v;

}

//returns dimensions of element

function getElementDimensions(el) {

    var dim = { width: 0, height: 0 };

    dim.width = el.offsetWidth;

    dim.height = el.offsetHeight;

    return dim;

}

//returns window scroll position

function getScrollPosition() {

    var pos = { x: 0, y: 0 };

    pos.x = window.pageXOffset ? window.pageXOffset : document.documentElement.scrollLeft;

    pos.y = window.pageYOffset ? window.pageYOffset : document.documentElement.scrollTop;

    return pos;

}

While writing this post, I recognized that there was a nice feature that I would like to include.  Currently, the gray overlay covers the entire browser window.  I would like modify so that I can pass in the container object.  By doing this I could use in smaller portions on the page quite easily. 

This is quite easily moved between projects which was one of my goals with this solution. If you have any comments or improvements please let me know.

In order to concatenate strings (delimited by with a string) from multiple rows in a SQL Table to a single field the Coalesce command is the one to use.  Typically COALESCE is used to return a single field value which represents multiple rows concatenated by a string.

DECLARE @EmployeeList varchar(100)

SELECT @EmployeeList = COALESCE(@EmployeeList + ', ', '') + 

   CAST(Emp_UniqueID AS varchar(5))

FROM SalesCallsEmployees

WHERE SalCal_UniqueID = 1

SELECT @EmployeeList

The output from the following would be something like 1,2, 3

The following example uses a function to return a joined table field with the primary data selected.  The challenge was to return a single row from one table while returning values from the joined table into a single field.  To accomplish this I used syntax similar to the following:

i.e.

SELECT personID,dbo.fn_CombineValues(personID) Roles
  FROM [People]

The following function was created to support the above sql query.  The function accepts the key to be used in the the joined table and returns a string value representing in this case the roles related to the primary table.

CREATE FUNCTION fn_CombineValues

 (

     @FK_ID INT --The foreign key from TableA which is used to fetch corresponding records

 )

 RETURNS VARCHAR(8000)

 AS

 BEGIN

 DECLARE @SomeColumnList VARCHAR(8000);

 SELECT @SomeColumnList = COALESCE(@SomeColumnList + ', ', '') + CAST(Role AS varchar(20)) 

 FROM dbo.SA_PeopleRoles C

 INNER JOIN dbo.SA_Roles r ON r.roleid=c.RoleID

 WHERE C.personID = @FK_ID;

 RETURN 

 (

 SELECT @SomeColumnList

 )

 END

References:http://stackoverflow.com/questions/111341/combine-multiple-results-in-a-subquery-into-a-single-comma-separated-value

Over the years there have been a number of methods to move SQL data into Microsoft Excel.  Using Automation you can call methods and properties that are specific to excel which gives you the greatest flexibility for specifying the location of your data in the workbook.  The following are two recent approaches to export Sql Server table row data to an excel workbook. 

1.) Using Automation you can use transfer data cell by cell

2.) Transfer data in an array to a range of cells

I created a WPF application with a button "Export" for this code.  The click event creates performs the the following actions

a.) Gets a reference to the automation object
b.) Adds a default workbook
c.) Gets the list of tables within the database
d.) Gets data
e.) Exports the data to excel worksheets (there are 2 methods defined (1) range method (2) cell by cell
f.) Saves the excel sheet
g.) Clean up

This blog posts shows two ways to extract data from SQL Server and place into an Excel document. The Range method was much quicker than the cell by cell approach.  The code below gets a list of all tables within a particular SQL Database and exports the data to Excel. 

I didn’t use was Excel 2007 document format at this.  If you have a good post or web link using this technique I would happily reference.  Let me know if you would like the Visual Studio project.

private void btnExport_Click(object sender, RoutedEventArgs e) {

    lblMessage.Content="Export Started..";

    WpfApplication.DoEvents();

    ApplicationClass app = CreateExcelDocument(false);

    //add workbook to excel document

    Workbook workbook = app.Workbooks.Add(Type.Missing);

    DataSet ds = GetData(GetTableList());            

    //shown below are 2 method of moving data from sql to excel, only use one of them

    //(1)call method to export to worksheet using range functionality (much faster than cell by cell method

    Export(ds, ref workbook);

    //(2)cell by cell method

    //InsertIntoExcel(app, ds);

     string fileName = @"C:\Safety3.xlsx";

     SaveDoc(workbook, fileName);

     CloseExcelDocument(app);

     lblMessage.Content = "Finished..";

}

a.) Create Excel document

/// <summary>

/// creates excel document

/// </summary>

private ApplicationClass CreateExcelDocument(bool visible) {

    ApplicationClass app = null;

    try {

        app = new Microsoft.Office.Interop.Excel.ApplicationClass();

        app.Visible = visible;

    } catch (Exception ex) {

        MessageBox.Show(ex.ToString());

    }

    return app;

}

c.) The following method GetTableList() returns a List<ExportInfo> where ExportInfo is a structure that has the Name and SQL to be used for each table to be exported.

public class ExportInfo {

    public string Name { get; set; }

    public string Sql { get; set; }

}

/// <summary>

/// get list of sql tables in database

/// </summary>

/// <returns>list of ExportInfo objects</returns>

private List<ExportInfo> GetTableList() {

    System.Data.DataTable tables = new System.Data.DataTable("Tables");

    using (SqlConnection connection = new SqlConnection(GetConnectionString())) {

        SqlCommand command = connection.CreateCommand();

        command.CommandText = "select table_name as Name from INFORMATION_SCHEMA.tables where TABLE_TYPE = 'BASE TABLE'";

        connection.Open();

        tables.Load(command.ExecuteReader(CommandBehavior.CloseConnection));

        connection.Close();

    }

    List<ExportInfo> exps = new List<ExportInfo>();

    foreach (DataRow row in tables.Rows) {

        exps.Add(new ExportInfo(){Name=row[0].ToString(), Sql = String.Format("select top 1 * from {0}",row[0].ToString())});

    }

    return exps;            

}

d.) Gets Data by interating over collection of exportInfo’s to retrieve sql statements

/// <summary>

/// get data from sql server

/// </summary>

/// <param name="exps"></param>

/// <returns>dataset with return results</returns>

private System.Data.DataSet GetData(List<ExportInfo> exps) {

    DataSet ds = new DataSet();

    string executeSql = string.Empty;

    foreach (ExportInfo exp in exps) {

        executeSql += exp.Sql + ";";

    }

    string connectionString = GetConnectionString();

    SqlConnection conn = new SqlConnection(connectionString);

    SqlDataAdapter adapter = new SqlDataAdapter(executeSql, conn);

    try {

        adapter.Fill(ds);

    } catch (Exception ex) {

    }

    int index = 0;

    foreach (System.Data.DataTable dt in ds.Tables) {

        dt.TableName = exps[index].Name;

        index += 1;

    }

    return ds;

}

e.) Export using range (1)

/// <summary>

/// Copy data from dataset into workbook

/// </summary>

/// <param name="dataSet"></param>

/// <param name="excelWorkbook"></param>        

private void Export(DataSet dataSet, ref Workbook excelWorkbook) {

    int sheetIndex = 0;

    // build rawData 2 dimensional array with data for each datatable            

    foreach (System.Data.DataTable dt in dataSet.Tables) {

        // Copy the DataTable to an object array

        object[,] data = new object[dt.Rows.Count + 1, dt.Columns.Count];

        // Copy the column names to the first row of the object array

        for (int col = 0; col < dt.Columns.Count; col++) {

            data[0, col] = dt.Columns[col].ColumnName;

        }

        // Copy the values to the object array

        for (int col = 0; col < dt.Columns.Count; col++) {

            for (int row = 0; row < dt.Rows.Count; row++) {

                data[row + 1, col] = dt.Rows[row].ItemArray[col];

            }

        }

        // Calculate the final column letter

        string finalColLetter = string.Empty;

        string colCharset = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";

        int colCharsetLen = colCharset.Length;

        if (dt.Columns.Count > colCharsetLen) {

            finalColLetter = colCharset.Substring(

                (dt.Columns.Count - 1) / colCharsetLen - 1, 1);

        }

        finalColLetter += colCharset.Substring(

                (dt.Columns.Count - 1) % colCharsetLen, 1);

        // Create a new Sheet

        Worksheet excelSheet = (Worksheet)excelWorkbook.Sheets.Add(excelWorkbook.Sheets.get_Item(++sheetIndex),

                                                                    Type.Missing, 1, XlSheetType.xlWorksheet);

        excelSheet.Name = dt.TableName; // name new sheet name of table

        // Fast data export to Excel

        string excelRange = string.Format("A1:{0}{1}", finalColLetter, dt.Rows.Count + 1);

        excelSheet.get_Range(excelRange, Type.Missing).Value2 = data;

        // Mark the first row as BOLD

        ((Range)excelSheet.Rows[1, Type.Missing]).Font.Bold = true;

    }

}

Export by iterating of Cells (2)

/// <summary>

/// inserts data into excel row by row

/// </summary>

/// <param name="excel"></param>

/// <param name="ds"></param>

private void InsertIntoExcel(Microsoft.Office.Interop.Excel.Application excel, System.Data.DataSet ds) {

    foreach (System.Data.DataTable dt in ds.Tables) {

        Microsoft.Office.Interop.Excel.Worksheet theSheet = (Microsoft.Office.Interop.Excel.Worksheet)excel.Workbooks[1].Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);

        theSheet.Name = dt.TableName;

        int colIndex = 0;

        int rowIndex = 1;

        string err = string.Empty;

        try {

            colIndex = 0;

            foreach (DataColumn col in dt.Columns) {

                colIndex += 1;

                excel.Cells[1, colIndex] = col.ColumnName;

            }

            rowIndex = 1;

            foreach (DataRow row in dt.Rows) {

                rowIndex += 1;

                colIndex = 0;

                foreach (DataColumn col in dt.Columns) {

                    colIndex += 1;

                    excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();

                }

            }

        } catch (Exception ex) {

            err += "RowIndex=" + rowIndex.ToString() + " ColIndex=" + colIndex.ToString();

        }

    } // next 

}

f.)  g.) Save and clean up

/// <summary>

/// Save workbook to file system

/// </summary>

/// <param name="excelWorkbook"></param>

/// <param name="fileName"></param>

private void SaveDoc(Workbook excelWorkbook, string fileName) {

    // Save and Close the Workbook

    excelWorkbook.SaveAs(fileName, XlFileFormat.xlWorkbookNormal, Type.Missing,

        Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive,

        Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

    excelWorkbook.Close(true, Type.Missing, Type.Missing);

    excelWorkbook = null;

}

/// <summary>

/// Clean up and close document

/// </summary>

/// <param name="excelApp"></param>

private void CloseExcelDocument(ApplicationClass excelApp) {

    // Release the Application object

    excelApp.Quit();

    excelApp = null;

    // Collect the unreferenced objects

    GC.Collect();

    GC.WaitForPendingFinalizers();

}

The following SQL will list all indexes in within the database that you run the sql script.  I was looking for the option within Visual Studio - Data Dude add-on that displayed index differences between two databases.  I eventually discovered the line by line index comparison by exploding the Table-Indexes however I was unable to create the newly added script index in the 2nd database.  Is this possible?

In lieu of the shortcomings within Data-Dude I used the following script to manually compare and script the indexes in both databases.

DECLARE GetTables CURSOR READ_ONLY
FOR
  SELECT TABLE_NAME
  FROM INFORMATION_SCHEMA.TABLES
  WHERE TABLE_TYPE = 'BASE TABLE'
  AND OBJECTPROPERTY (OBJECT_ID(TABLE_NAME), 'IsMSShipped') = 0

DECLARE @TableName sysname
OPEN GetTables

FETCH NEXT FROM GetTables INTO @TableName
WHILE (@@fetch_status = 0)
BEGIN
  EXEC sp_helpindex @TableName
  FETCH NEXT FROM GetTables INTO @TableName
END

CLOSE GetTables
DEALLOCATE GetTables

There are many options within the Microsoft stack of products for AJAX type functionality.  The following article covers the primary mechanisms that utilize the Microsoft out of the box Ajax functionality.  Ajax must be one of the technologies used in all current web applications.  The primary benefit is the improved user experience by providing quick, responsive feedback.

Core features include Ajax Client Libraries and Ajax Server Controls.  These libraries and controls enable the developer to quickly implement solutions within any ASP.NET application. 

I have also included in this article the examples for utilizing the Microsoft AJAX libraries using:

• update panel server control
• access to web service from JavaScript
• access to page methods (without web service)
• HttpRequest/Post from javascript.

• Client-Script libraries that provide features for object-oriented development which enable high level of consistency and modularity in client scripting.
  • Browser compatibility
  • Extensions to JavaScript such as classes, namespaces, event handling, inheritance, data types and object serialization
  • Networking layer that handles communication with web based services
• Sys Namespace
• Javasript Shortcuts i.e. $get(‘elementId’), $addHandler, $removeHandler

Asp.Net AJAX Server Controls

• ScriptManager
  • Manages Javascript
  • Enables partial page rendering w/UpdatePanel
  • Creates client side proxies for Web Services
  • Provides registration methods for scripts

<asp:ScriptManager ID="mng1" runat="server"
                    EnablePartialRendering="true|false"
                    EnablePageMethods="true|false"
                    ScriptMode="Auto|Inherit|Debug|Release"
                    ScriptPath="...">                        
                    <Scripts>
                        <asp:ScriptReference Name="script.js" Assembly="..." />
                    </Scripts>
                    <Services>
                        <asp:ServiceReference Path="~/MyWebService.asmx" />                        
                    </Services>    
       </asp:ScriptManager>

• ScriptManagerProxy
  • Used with Master Pages
  • If you include the ScriptManager control on the master page, it can act as the ScriptManager control for all content pages.  If you want to register scripts or services declaratively in a content page, you can add a ScriptManagerProxy control to the content page.
    
• UpdatePanel
  • Improves user experience of application by updating a portion of the web page thereby improving interactivity
  • By default controls within the update panel can trigger the update.  ChildrenAsTriggers property is by default set to true.  In our example the link button is within the update panel and so can trigger the partial page update.  By default any postback control inside an update panel causes an async postback
  • Controls outside of the update panel can also trigger the panel contents to update.  For example look at btnGo, it resides outside the panel however it is defined within the panel as a trigger/control that can force the async update to the panel contents.  Reference the <asp:AsyncPostBackTrigger> element within the <Trigger> element of the updatePanel
  • set EnableParitalPageRendering=”true”
  • Shown here is how a timer defined outside of the panel can trigger the update panel to refresh at a defined interval

     <asp:UpdatePanel id=”panel1” runat=”server”
                             UpdateMode=”Always/Conditional”
                             ChildrenAsTriggers=”true/false”>
                      <Triggers>
                            <asp:AsyncPostBackTrigger ControlID=”timer1”/>
                       </Triggers>
                       <ContentTemplate>
                            <!—page content here -->
                      </ContentTemplate>
       </asp:UpdatePanel>
      <asp:Timer id=”timer1” runat=”server” Interval=”3000” OnTick=”OnTimerTick”/>

• UpdateProgress
  • provides status information about partial-page updates in the UpdatePanel controls.  The content can be customized.  To prevent flashing when a partial-page update is very fast, you can specify a delay before the UpdatePanel control is displayed

Update Panel Example

Code behind which responds to the button click to update a label control with the current time.

public partial class Panel : System.Web.UI.Page

{

    protected void Page_Load(object sender, EventArgs e)

    {

        lblName.Text = DateTime.Now.ToString();

    }

    protected void lnkButton_Click(object sender, EventArgs e) {

        lblName.Text = DateTime.Now.ToString();

    }

}

Page HTML code with embedded update panel which responds to a control within the panel and a button defined as a trigger outside of the panel control.

<body>

    <form id="form1" runat="server">

        <asp:ScriptManager ID="ScriptManager1" runat="server" EnablePartialRendering="true"></asp:ScriptManager>

        <asp:UpdatePanel ID="UpdatePanel1" runat="server">

            <ContentTemplate>

                 <asp:Label ID="lblName" runat="server" CssClass="lbl" Text=""></asp:Label>

                 <asp:LinkButton ID="lnkButton" runat="server" CssClass="lnk" onclick="lnkButton_Click" Text="Update"></asp:LinkButton>

            </ContentTemplate>        

            <Triggers> 

              <asp:AsyncPostBackTrigger ControlID="btnGo" />

            </Triggers>

        </asp:UpdatePanel>

        <asp:UpdateProgress ID="UpdateProgress1" runat="server" AssociatedUpdatePanelID="UpdatePanel1">

            <ProgressTemplate>

                Updating.

            </ProgressTemplate>

        </asp:UpdateProgress>

        <br />

        <br />

        <asp:Button ID="btnGo" runat="server" CssClass="btn" Text="Go" />

    </form>

</body>

Ajax and Web Service Example

• Script Callable Web Service
• Proxies are automatically generated
• [ScriptService] Attribute
• If the web service returns a complex type, the succeeded callback function receives a return value in the form of Javascript object that corresponds to the server type.
  
  Web Service code (simple function that returns ‘Hello World’)
  • Web Service has [ScriptService] attribute

  [WebService(Namespace = "http://tempuri.org/")]

  [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]

  [System.Web.Script.Services.ScriptService]

  public class MyWebService : System.Web.Services.WebService {

      public MyWebService () {

      }

      [WebMethod]

      public string HelloWorld() {

          return "Hello World";

      }    

  }

Page Code (page with button which calls js function, which in turn calls the web service defined within ScriptManager-ScriptReference tag)

<form id="form1" runat="server">

    <asp:ScriptManager ID="ScriptManager1" runat="server">

        <Services>

            <asp:ServiceReference Path="~/MyWebService.asmx" />

        </Services>        

    </asp:ScriptManager>

    <asp:Button ID="btnGet" runat="server" CssClass="btn" Text="Get" OnClientClick="update();return false" />

    <script type="text/javascript">

        function update() {

            MyWebService.HelloWorld(onCompleted, onFailed);

        }

        function onCompleted(result, context, methodName) {

            alert(result);

        }

        function onFailed(err, context, methodName) {

            alert(err.get_message());

        }

    </script>

</form>

dy>

ml>

 

Page Method Example

• Allows javascript Ajax call back to page without web service
• use EnablePageMethods=true in the ScriptManager control

The following is a sample showing a code behind static method marked with WebMethod attribute.

public partial class PageMethod : System.Web.UI.Page

{

    protected void Page_Load(object sender, EventArgs e)

    {

        Response.Write(DateTime.Now.ToString());

    }

    [System.Web.Services.WebMethod]

    public static string update(string input){

        return input.ToUpper();

    }

}

Aspx code with a button that calls javascript, which in turn calls the page method ‘update’ to return uppercase string.

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

    <title></title>

</head>

<body>

    <form id="form1" runat="server">

    <asp:ScriptManager ID="ScriptManager1" runat="server" EnablePartialRendering="true" EnablePageMethods="true" />

    <asp:Button ID="btnGet" runat="server" CssClass="btn" Text="Get" OnClientClick="update();return false" />

        <script type="text/javascript">
   1:  
   2:             function update() {
   3:                 PageMethods.update("test", onCompleted);
   4:                 return false;
   5:             }
   6:             function onCompleted(result) {
   7:                 alert(result.toString());
   8:             }
   9:         
</script>

    </form>

</body>

</html>

HTTP Requests (from Javascript) Sample

• The async communication layer enables javascript to make a request over the network to any http end point by using Sys.Net.WebRequest class
• The sample performs a post and get http request from javascript

Page code that initiates the javascript call GetWebRequest().

<form id="form1" runat="server">

<asp:ScriptManager ID="ScriptManager1" runat="server">

    <Scripts>

        <asp:ScriptReference Path="~/connect.js" />

    </Scripts>

</asp:ScriptManager>

<asp:Button ID="btnGo" runat="server" CssClass="btn" Text="GetWebRequest"  OnClientClick="GetWebRequest();return false;" />

<asp:Button ID="btnPost" runat="server" CssClass="btn" Text="PostWebRequest" OnClientClick="PostWebRequest();return false;" />

<div id="result">initial content</div>

</form>

Javascript (connect.js) file which performs the webrequest

// connect.js

var resultElement;

function pageLoad() {

    resultElement = $get("result");    

}

function GetWebRequest() {

    alert("performing web request");

    // Instantiate a WebRequest.

    var wRequest = new Sys.Net.WebRequest();

    // Set the request URL.      

    wRequest.set_url("getTarget.htm");

    alert("Target Url: getTarget.htm");

    // Set the request verb.

    wRequest.set_httpVerb("GET");

    // Set the request callback function.

    wRequest.add_completed(OnWebRequestCompleted);

    // Clear the results area.

    resultElement.innerHTML = "";

    // Execute the request.

    wRequest.invoke();      

}

// This function performs a POST Web request.

function PostWebRequest() {

    alert("Performing Post Web request.");

    // Instantiate a WebRequest.

    var wRequest = new Sys.Net.WebRequest();

    // Set the request URL.      

    wRequest.set_url("postTarget.aspx");

    alert("Target Url: postTarget.aspx");

    // Set the request verb.

    wRequest.set_httpVerb("POST");

    // Set the request handler.

    wRequest.add_completed(OnWebRequestCompleted);

    // Set the body for he POST.

    var requestBody =

        "Message=Hello! Do you hear me?";

    wRequest.set_body(requestBody);

    wRequest.get_headers()["Content-Length"] =

        requestBody.length;

    // Clear the results area.

    resultElement.innerHTML = "";

    // Execute the request.

    wRequest.invoke();

}

// This callback function processes the 

// request return values. It is called asynchronously 

// by the current executor.

function OnWebRequestCompleted(executor, eventArgs) {

    if (executor.get_responseAvailable()) {

        // Clear the previous results. 

        resultElement.innerHTML = "";

        // Display Web request status. 

        resultElement.innerHTML +=

          "Status: [" + executor.get_statusCode() + " " +

                    executor.get_statusText() + "]" + "<br/>";

        // Display Web request headers.

        resultElement.innerHTML += "Headers: ";

        resultElement.innerHTML += executor.getAllResponseHeaders() + "<br/>";

        // Display Web request body.

        resultElement.innerHTML +="Body:";

        if (document.all)

            resultElement.innerText += executor.get_responseData();

        else

            resultElement.textContent += executor.get_responseData();

    }

}

if (typeof (Sys) !== "undefined") Sys.Application.notifyScriptLoaded();

Target Html file used in the Get

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >

<head>

    <title>Hello Page</title>

</head>

<body>

    <p> 

        Hello there. 

        I got your GET Web request!

    </p>

</body>

</html>

Post Aspx Page

<%@ Page Language="C#" AutoEventWireup="true"  %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >

<head id="Head1" runat="server">

    <title>Post Target</title>

    <script language="C#" runat="server">

        protected void Page_Load(object sender, EventArgs e)

        {

            if (HttpContext.Current.Request.Form["Message"] != null)

                LabelID.Text = 

                    HttpContext.Current.Request.Form["Message"].ToString();

        }

    </script>

</head>

<body>

    <form id="form1" runat="server">

    <div>

        <h1>WebRequestPost Target</h1>

        <p>

            <asp:Textbox id="LabelID"  

                Text="test" runat="server"/>

        </p>

        Yes, I got your POST Web request!

    </div>

    </form>

</body>

</html>

There are a few ways of deploying a .NET 2.0 ASP.NET application

• using Web Site Deployment Project
• using VS.NET Publish Command
• using VS.NET Build Command

1.) Using VS.NET Build

ASP.NET not Visual Studio performs the build.  ASP.NET builds everything, including .cs and .vb code files and places all resulting assemblies in folder structure under Temporary ASP.NET files directory.  As ASP.NET does all of the compilation, the debug setting in the compilation section of the web.config controls debug or release mode.  Compile with debug=true and you'll find the .pdb debugging symbol files alongside each assembly.  In this scenario the Configuration Manager is obsolete (not used) and as such the only option is 'Debug'.

2.) Using VS.NET Publish

This option is available when you are ready to publish to production.  The Publish command will precompile a web application and place the results into a director of your choosing (IIS/FTP/Directory).  Options are available on the Publish dialog box that map to aspnet_compiler switches.  The aspnet_compiler tool has option to create pdb files however this is not available on the dialog box (within vs.net).  Publish always builds in release mode without pdb files.  The Publish command does not change the debug setting in the web.config SO if you precompile and updateable (option 'allow this precompiled site to be updateable') web site and then update the web site in place (which will result in a dynamic compilation) those dynamic compilations will produce debug code and pdb files.

3.) Using Web Site Deployment Project (WSD)

This project allows VS.NET to use MSBUILD files provided by WSD to ask for debug and release builds.  This tool uses the aspnet_compiler similar to above with the Publish option however the WSD option will change the debug setting in the web.config to false for release builds (different than the Publish option)  By default the built files will be in respective debug or release directories.

Conclusion

VS.NET Build - builds web site to Temporary ASP.NET files directory with options specified in web.config

VS.NET Publish - builds to release mode (always) however does not change the compilation mode in web.config file (which can lead to less than optimum performance if site is dynamically recompiled)

Web Deployment Project - Builds based on Configuration Manager mode (debug/release) AND updates the web.config with additional options for creating debug symbols and swapping out web.config sections based on release mode

see related post http://blog.davidyardy.com/archive/2008/07/05/asp.net-2.0-compilation-models-again.aspx

1.) Download install VS.NET 2008 SP1 patch

Visual Studio 2008 SP1 has the following patch to allow IntelliSense with jQuery found here.  Note: this is a patch that is applied after .NET 3.5 SP1 and Visual Studio 2008 SP1.  Information about this patch can be found here.

I did find that the vsdoc.js version must match the version of jquery in order for the intellisense to function correctly.  If the versions mismatch intellisense will not work.

2.) Download jQuery documentation library

3.) Download jQuery 

4.) Copy both 2 and 3 to your solution Scripts folder

5.) In your aspx reference the jquery file

<script src="Scripts/jquery-1.2.6.min.js" type="text/javascript"></script>

or

<asp:ScriptManager runat="server" ID="scriptmanager1">
    <Scripts>
        <asp:ScriptReference Path="~/Scripts/jquery-1.2.6.min" />
    </Scripts>
</asp:ScriptManager>

Some Quick Tips

• If you are using a master page the script reference only needs to exist in the master page
• javascript intellisense will not work in user controls by default as the user control doesn’t have a reference to the js file.  A work around (use the following at the top of the user control).  At runtime ASP.NET will not render this tag however Visual Studio will evaluate the script and provide intellisense
  <% if (false) { %>
        <script src=”../Scripts/jquery-1.2.6.min.js” type=”text/javascript”></script>
  <% } %>
• a recommended method of including js files is to create one js file which entail will include references to any number of specific js files, then on your web page include a reference to this one single js file
  <reference path=”~/Scripts/jquery-1.2.6.min.js” />
• a good approach is to remove the version number from the jquery files to allow easier updates to these files in the future

Path(s) for Script References

• File-Relative Paths i.e. ../../file.js This type of path is relative to the currently loaded file.  Support - ASP.NET Web forms / MVC
• App-Relative Paths i.e. ~/folder/file.js  Is calculated from the base of your application.  ASP.NET Web forms supports this type of path however the path must be within a scriptreference tag or select asp.net controls which have runat=”server”
• Site-Relative Paths i.e. /folder/file.js  Is calculated from the base of your site.  Supported by ASP.NET Web forms / MVC however is not supported by Visual Studio
• Absolute Paths i.e. http://site/folder/file.js  Supported by ASP.NET Web forms / MVC and Visual Studio

If using Visual Studio(ASP.NET Web forms) recommended to use App-Relative paths ~/folder/file.js.  If using MVC use file-relative paths.

I had a need to compare two SQL Server table for differences between them.  I started using .NET dataset features (merge, acceptchanges, getchanges) as follows:

There are a few gotcha's with the above code.  The primary problem was that both tables must have primary keys defined.  I figured ok, I could create primary keys through code for the related DataTables however I soon realized that there were duplicate rows within the tables.

SQL Server 2005 has Except and Intersect functions (http://msdn.microsoft.com/en-us/library/ms188055(SQL.90).aspx) that return distinct values by comparing the results of two queries.  The entire row is compared against another row from another table.

Except returns any distinct values from the left query that are not found on the right query.
Intersect returns any distinct values that are returned by both the query on the left and right sides.

In order to use the number and order of the columns must be the same in the queries and also the data types must be comparable. 

To return all rows in table1 that do not match exactly the rows in table2, you can use Except ...
select * from table1 except select * from table2

(likewise to find the opposite just reverse the table names above)

To return all rows in table1 that match exactly what is in table2, using Intersect...
select * from table1 intersect select * from table2

Combining the above two... (the following will return the differences)
select 'table1' as tblName, *  from
  (select * from Table1 except select * from Table2) x
union all
select 'table2' as tblName, *  from
  (select * from Table2 except select *  from Table1 ) x

If you are fortunate to have primary keys you can of course still use IN/NOT IN type queries however it seems that performance is much improved with the Except/Intersect approach.

Recently, Microsoft released their ASP.NET AJAX Framework which allows developers to build AJAX applications more easily.  ASP.NET AJAX consists of two pieces. 

1.) Microsoft AJAX Library - contains a set of script files that provide common functions and an OO programming framework

2.) ASP.NET 2.0 AJAX Extensions - includes a set of server controls that allows developers to add AJAX functionality by dragging and dropping controls onto a page

The following are brief descriptions of the javascript extensions made available by the AJAX Client Library by Namespace.

Global Namespace - contains members and types that extend base JavaScript objects.

Array Extensions (add, addRange, clear, clone, contains, dequeue, enqueue, forEach, indexOf, insert, pars, remove, removeAt)

Boolean Extension (parse - converts a string into a Boolean)

Date Extension (format, localeFormat, parseInvariant, parseLocale)
        var today = new Date();
        alert (today.format('D'));

        d - Short date pattern (05/10/07)
        D - Long date pattern (Thursday, 10 May 2007)
        t - Short time pattern (18:05)
        T - Long time pattern (18:05:12)
        F- Full date pattern (Thursday, 10 May 2007 18:05:12)
        M - Month and date pattern (May 10)
        s - Sortable date and time pattern (2007-05-10T18:05:12)
        Y - Year and month pattern (2007 May)

Error Extensions (argument, argumentNull, argumentOutOfRange, argumentType, argumentUndefined, create, invalidOperation, notImplemented, parameterCount, popStackFrame)

Number Extension (format, localeFormat, parseInvariant, parseLocale)

        p - number is converted to a string that represents a percent
        d - converted to a string of decimal digits
        c - converted to a string that represents a currency
        n - converted to a string of the form "-d,ddd,dd"

        var num = Number.parseInvariant("130.33");
        alert (num.localeFormat("c")); // $130.33

Object Extensions (getType, getTypeName)
        getType - returns the type of specified object
        getTypeName - returns the type name of an object

String Extension (endsWith, format, localeFormat, startsWith, trim, trimEnd, trimStart)

Sys Namespace

         Sys - root namespace containing some base classes such as Sys.CultureInfo
                  Application - objects/methods that expose client events and manage client components
                  ApplicationLoadEventArgs - container object for arguments of the Application Load event
                  CancelEventArgs - base class for events that can be canceled
                  Component - base class for all asp.net ajax objects including Control class and Behavior class
                  CultureInfo - object that can be used to provide locale specific functionality
                  Debug code - debugging and tracing functionality
                  EventArgs - used for storing event arguments
                  EventHandlerList - collection of client events for a component containing event names and handlers 
                  PropertyChangedEventArgs - contains event arguments associated with changed properties
                  StringBuilder - facilitates more efficient string concatenation

         Sys.Net - provides networking and communication support
         Sys.UI - contains set of classes for UI support
         Sys.Services - support for asp.net application services such as login/authentication
         Sys.Serialization - provides support for data serialization/Json
         Sys.WebForms - contains classes for async page loading

Ever want to know if you have 3.5 SP1 installed?  Shown below are two ways to determine.

1.) Take a look at HKLM\SOFTWARE\MICROSOFT\NET Framework Setup\NDP\v3.5

Shown below are two servers.  The top one does not have SP1 installed while the lower one does.

2.) An easy way to see if you have 3.5 SP1 installed is to navigate to http://www.hanselman.com/smallestdotnet/ Near the top of this page you will be given an indication if you have the latest installed.

If you do not have it installed you can use the following link to navigate you to Microsoft Downloads for Microsoft .NET Framework 3.5 Service Pack 1.  This link will installed a small installer that will need to be executed.  A wizard will step you through the installation and additional downloads.

If you prefer a complete download of this SP1 you can use this link.

Where the column name matches exactly…
SELECT name FROM sysobjects WHERE id IN ( SELECT id FROM syscolumns WHERE name = '{text}' )

Where the column name is like…
SELECT name FROM sysobjects WHERE id IN ( SELECT id FROM syscolumns WHERE name like '%{text}%' )

The following will show all occurrences of the column in any object
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE '%doctorid%'