SQL Temp Table, Table Variable and Global Table Variable

There are a few options when creating a table as part of a SQL operation. 

Notes: 

• The highlighted symbol is the indicator of the type of table.
• Temp table and table variable declaration/creation syntax is different

1.) Temp Table - This temp table is only active during the execution of the query and does not persist

DECLARE @tmp TABLE

(

Id INT,

Description VARCHAR(32)

)

INSERT INTO @tmp (ID, Description)

VALUES (1, 'test temp table')

2.)  Table Variable - This table will need DROPped as it will persist

CREATE TABLE #tmp 

(

Id INT,

Description VARCHAR(32)

)

INSERT INTO #tmp (ID, Description)

VALUES (1, 'test table variable')

DROP TABLE #tmp

3.)  Global Table Variable - This table will be accessible across query windows.  This table will need DROPped as it will persist.

CREATE TABLE ##globalTmp 

(

Id INT,

Description VARCHAR(32)

)

INSERT INTO ##globalTmp (ID, Description)

VALUES (1, 'test global table variable')

DROP TABLE ##globalTmp

SQL Pivot Table

A dynamic SQL pivot table can rearrange SQL output.  This can be useful if trying to format for Excel.

//Create a table variable

CREATE TABLE #yt

(

  [Store] int, 

  [Week] int, 

  [xCount] int

);

//Insert some values into the table

INSERT INTO #yt([Store], [Week], [xCount])

VALUES

    (102, 1, 96),

    (101, 1, 138),

    (105, 1, 37),

    (109, 1, 59),

    (101, 2, 282),

    (102, 2, 212),

    (105, 2, 78),

    (109, 2, 97),

    (105, 3, 60),

    (102, 3, 123),

    (101, 3, 220),

    (109, 3, 87);

//Generate the format of the top column values for the pivot table.  Week in this example.

DECLARE @cols AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(Week) 

                    from #yt

                    group by Week

                    order by Week

            FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')  ,1,1,'')

//Generate the SQL query with the dynamic column data

DECLARE @query  AS NVARCHAR(MAX)

SET @query = 'select * 

from 

(

  select store, week, xCount

  from #yt

) src

pivot

(

  sum(xcount)

  for week in (' + @cols + ')

) piv;'

//Execute the query

exec(@query)

//The results will display with the store and week as the row/column values

store	1	2	3
101	138	282	220
102	96	212	123
105	37	78	60
109	59	97	87

Share data between html pages

1. Pop out a window using a hashtag and appends the data that needs to be shared
• var childNoteWindow = window.open(URL + "popout.html#" + JSON.stringify(params), target, windowoption);
2. The pop out window retrieves the data
• var paramString =window.location.hash.substr(1);
3. The pop out window clears the hashtag detail from the URL. Added bonus: this does not cause page refresh.
• history.pushState("", document.title, window.location.pathname + window.location.search);

Only available with HTML5.

JQuery call to WCF service

Using jQuery to communicate to an Ajax-enabled WCF Service

WCF Service Definition using object response

[OperationContract]

[WebInvoke(Method = "POST")]

public TestResponse TestService(string input)

{

    try

    {

        TestResponse response = new TestResponse() { Message = string.Format("TestService result is {0}", input) };

        return response;

    }

}

JQuery Service Call using object

       $.ajax({

             type: "POST",

              url: "http://localhost:5274/MyService.svc/TestService",

             data: '{"input":"16"}',

              contentType: "application/json; charset=utf-8",

              success: function (data) {

                    var tmp = data.d;

                    alert(‘Message: ‘ + tmp.Message);

              },

              error: ServiceCallFailed

     });

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

WCF Service Definition using string response

[OperationContract]

[WebInvoke(Method = "POST")]

public string TestService(string input)

{

    try

    {

        TestResponse response = new TestResponse() { Message = string.Format("TestService result is {0}", input) };

        JavaScriptSerializer js = new JavaScriptSerializer(); //using System.Web.Script.Serialization;

        string json = js.Serialize(response);

        return json;

    }

}

JQuery Service Call using parse

       $.ajax({

             type: "POST",

              url: "http://localhost:5274/MyService.svc/TestService",

             data: '{"input":"16"}',

              contentType: "application/json; charset=utf-8",

              success: function (data) {

                    var response = $.parseJSON(data.d);

                    alert('Message: ' + response.Message);          },

              error: ServiceCallFailed

     });

WCF Service and Corss-Origin Resource Sharing (CORS)

1.) Add a Global.asax file to the WCF service
2.) Add the following into the "Application_Begin Request" Method
           
HttpContext.Current.Response.AddHeader("Access-Control-Allow-Origin", "*");
if (HttpContext.Current.Request.HttpMethod == "OPTIONS")
{
    HttpContext.Current.Response.AddHeader("Access-Control-Allow-Methods", "GET, POST");
    HttpContext.Current.Response.AddHeader("Access-Control-Allow-Headers", "Content-Type, Accept");
    HttpContext.Current.Response.AddHeader("Access-Control-Max-Age", "1728000");
    HttpContext.Current.Response.End();
}

Include SVG definition in CSS

//CSS
.bold {
background-image: url('data:image/svg+xml;charset=UTF-8,');
}

//HTML

jQuery AJAX call

$.ajax({
                async: true,
                dataType: 'xml',
                url: URL,
                success: function (data) {
                    div.innerHTML = new XMLSerializer().serializeToString(data.documentElement);
                },
error: function(XMLHttpRequest, textStatus, errorThrown) {
console.log("" + errorThrown);
}
            });

Quick and easy way to consume a REST service

//Define URI
string uri = http://blah/blah/

//Define REST variable
string id = "someId";

//Create WebClient and consume REST service
string response = null;
using(WebClient client = new WebClient())
{
    response = client.UploadString(string.Format("{0}{1}", uri, id), "");
}

Dynamically stream image to webform

Dynamically stream image to Panel control in WebForm

1.) Convert Stream to byte[] buffer
byte[] buffer = new byte[pd.Stream.Length];
pd.Stream.Read(buffer, 0, buffer.Length);


2.) Dynamically create Image control and set the ImageURL to the buffer
System.Web.UI.WebControls.Image img = new System.Web.UI.WebControls.Image();img.ImageUrl = "data:image/tiff;base64," + Convert.ToBase64String(content, 0, content.Length);
 3.) Add Image control to the Panel
 DisplayPanel.Controls.Add(img);



 

Iterate through Linq groups

Use IGrouping to iterate through the groups made by the Linq group

public static Dictionary<string, List<Request>> SplitRequestsByHeader(List<Request> requests)
{
 var ordRequests = new Dictionary<string, List<Request>>();

var groups =
from r in ordRequests
group r by string.Format("{0}{1}{2}{3}", r.DistrictId, r.CustomerId, r.UnitId, r.OrderType) into grp
orderby grp.Key
select grp;


//Note: instead of iterating, could convert directly to Dictionary
//ordRequests = groups.ToDictionary(group => group.Key, group => group.ToList());

  //Iterate each order within each group
foreach (IGrouping<string, Request> group in groups)
{
 List<Request> ordGroup = new List<Request>();

foreach (var r in group)
{

ordGroup.Add(r);

}

                ordRequests.Add(group.Key, ordGroup);

}
  return ordRequests;
}

How to GAC in Win8

In .net 4.0 Microsoft removed the ability to add DLLs to the Assembly simply by dragging and dropping into C:\Windows\assembly\.
Instead you need to use gacutil.exe, or create an installer to do it.

To use gacutil:
Start -> programs -> Microsoft Visual studio 2010 -> Visual Studio Tools -> Visual Studio Command Prompt (2010)
Then use these commands to uninstall and Reinstall respectively. Note I did NOT include .dll in the uninstall command.
gacutil /u myDLL
gacutil /i "C:\Program Files\Custom\myDLL.dll"

To use Gacutil on a non-development machine you will have to copy the executable and config file from your dev machine to the production machine. It looks like there are a few different versions of Gacutil. The one that worked for me, I found here:
C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bin\NETFX 4.0 Tools\gacutil.exe C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bin\NETFX 4.0 Tools\gacutil.exe.config
Copy the files here or to the appropriate .net folder;
C:\Windows\Microsoft.NET\Framework\v4.0.30319
Then use these commands to uninstall and reinstall respectively
"C:\Users\BHJeremy\Desktop\Installing to the Gac in .net 4.0\gacutil.exe" /u "myDLL"
"C:\Windows\Microsoft.NET\Framework\v4.0.30319\gacutil.exe" /i "C:\Program Files\Custom\myDLL.dll"

Assist with debugging .NET application

To assist with debugging any peculiar behavior with code in Visual Studios, use the Microsoft Service Trace Viewer.

1.) Create a trace file - c:\logs\Traces.svclog

2.) Add diagnostics configuration section to the config file

  <system.diagnostics>

    <sources>

      <source name="System.ServiceModel"

              switchValue="Information, ActivityTracing"

              propagateActivity="true">

        <listeners>

          <add name="traceListener"

              type="System.Diagnostics.XmlWriterTraceListener"

              initializeData="c:\logs\Traces.svclog"  />

        </listeners>

      </source>

    </sources>

  </system.diagnostics>

3.) Run the application to produce the behavior and check the log file for assistance.

XSLT for converting DataSet to JSON

<xsl:stylesheet version="1.0"

                           xmlns:xsl="http://www.w3.org/1999/XSL/Transform"

                           xmlns:msxsl="urn:schemas-microsoft-com:xslt"

                           exclude-result-prefixes="msxsl">

  <xsl:output method="text" indent="no" omit-xml-declaration="yes" />

  <xsl:template match="NewDataSet">

{

    "Jobs": [

        <xsl:apply-templates select="Table"/>

        {}

    ]

}

  </xsl:template>

 

  <xsl:template match="Table">

        {

            "id": "<xsl:value-of select="jobid"/>",

            "title": "<xsl:value-of select="jobtitle"/>",

            "location":{

                "address": "<xsl:value-of select="jobaddress"/>",

                "city": "<xsl:value-of select="jobcity"/>",

                "state": "<xsl:value-of select="jobstate"/>",

                "zip": "<xsl:value-of select="jobzip"/>"

            },

            "group": {

                "groupcode": "<xsl:value-of select="jobgroup"/>",

                "description": "<xsl:value-of select="jobgroupdescription"/>"

            },

            "otherinformation": "<xsl:value-of select="otherinformation"/>",

            "payload": "<xsl:value-of select="payload"/>"

                   

        },

    </xsl:template>

</xsl:stylesheet>

Convert XML format using XSLT

using System.Xml;

using System.Xml.XPath;

using System.Xml.Xsl;

public virtual Stream Transform(Stream payload)

{

    XPathDocument myXPathDoc = new XPathDocument(payload);

    XslCompiledTransform myXslTrans = new XslCompiledTransform();

    myXslTrans.Load(this.XsltPath); //Path to XSLT used for transform

    Stream formattedRequest = new MemoryStream();

    XmlTextWriter myWriter = new XmlTextWriter(formattedRequest, null);

    myXslTrans.Transform(myXPathDoc, null, myWriter);

    formattedRequest.Seek(0, SeekOrigin.Begin);

    payload.Close();

    return formattedRequest;    

}

Javascript Tutorial

https://www.destroyallsoftware.com/talks/wat

Image on end of TextBox or DropDownBox

Example of how to have image display at start of DropDownList

//css
 .icon{
  background: #ffffff url(/images/icon-search.gif) no-repeat 3px 50%;
}


<asp:DropDownList ID="NameDdl" runat="server" CssClass="icon"></asp:DropDownList>

Retain scroll position after PostBack

To keep current scroll position after postback, use some jQuery and an ASP HiddenField.

 1.) Add the HiddenField to the page:
<asp:HiddenField runat="server" ID="ScrollPosition" Value="" />

2.) Add the Javascript (requires jQuery)
       <script type="text/javascript">
        $(function () {
            var sp = $("#<%=ScrollPosition.ClientID%>");
            window.onload = function () {
                var position = parseInt(sp.val());
                if (!isNaN(position)) {
                    $(window).scrollTop(position);
                }
            };

            window.onscroll = function () {
                var position = $(window).scrollTop();
                sp.val(position);
            };
        });
    </script>

CSS use left, center and right images for border

Create complex borders that requires three images (left, right, center).

Create div with id/class
<div id="border"></div>




#border {
  width: 100%;
  height: 33px
  margin: 0 auto;
  padding: 0;
  text-align: left;
  position: relative;
  margin-top: 0px;
  max-width: 885px;
}


/* Comma separated list of border images */#border 
{
background: url(/images/border-l.jpg) top left no-repeat, url(/images/border-r.jpg) top right no-repeat, url(/images/border-c.jpg) top repeat;}