Tuesday, October 30, 2018

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
store123
101138282220
10296212123
105377860
109599787


Friday, January 26, 2018

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.

Tuesday, January 2, 2018

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

     });

Friday, December 29, 2017

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();
}

Wednesday, December 27, 2017

Include SVG definition in CSS



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

//HTML

Thursday, December 14, 2017

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);
}
            });