Wednesday, 12 June 2013

Pivot Example in SQL Server

Hi All, I got the scenario where i need to present the row data as column header in SQL server 2008. My scenario might be bit different. Please have a look.













I need to present the month column data row wise corresponding to all the Fields
([AC_Metrics As Euro],[AC_Metrics As Units],[AC_Metrics As LC],[BD_Metrics As Euro],[BD_Metrics As Units],[BD_Metrics As LC]). According to my requirement data should be appear like -










In the above image, data is same as the original data, but the presentation style of the data has been changed. In the above image all months from the row data became column header and other corresponding details (Euro and Units) became the value(total sum).
So my requirement has been full filled. I tried to pivot the SQL table but in different way. 
I recognized that if there are two column then easily we can do the  pivoting but when there are more than two columns then pivoting might be little bit difficult. 
To implement this scenario i used pivoting after implementing the union and few temporary tables.

Step 1: Get all the data from the original table and insert into a temporary table.
SELECT  * into #Temp1 from YourTable

Step 2: Select required data from the temporary table and implement union as below after that insert this data into an another temporary table -

















Step 3: Declare variables as below - 
     DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

 Step 4: Assign the value to @cols and @query 
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(MonthYear) 
                    from #Temp2
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
        
set @query = 'SELECT' + @cols + '
            from 
            (
                select MonthYear,Amount
                from #Temp2
            ) x
            pivot 
            (
                max(Amount)
                for MonthYear in (' + @cols + ')
            ) p '


Step 5: Execute the query and drop the temporary tables
execute(@query)     
drop table #Temp1
drop table #Temp2


In this way i got the result for the required scenario.






Tuesday, 11 June 2013

wcf from jquery

Here I am assuming that readers already aware about How to create and publish a WCF service. As per the managing the data, There are two types of scenario  through WCF too.

1. Get (fetching the data) operation 
2. Post (insert, update and delete) operation

Here We will check both the scenario one by one respectively.

1. Calling a WCF service by using JQuery and perform data pulling (Get Operation)

Step 1At first we need to add a JQuery class library, which will take all the basic references.

<script src="http://ajax.aspnetcdn.com/ajax/jquery/jquery-1.9.1.min.js" type="text/javascript"></script>

Step 2: We just suppose to call a WCF service on the page load, or else you can call it on other 
           event handler like button click, change Index ...so on.

$(document).ready(
     function () {
     });

Step 3: Call a WCF through AJAX call.
     $.ajax({
             //Mention the operation type GET as data is pulling from the server
             type: "GET",
             //Content type sent to the server
             contentType: "application/json; charset=utf-8",
             //Expected result format form the server
             datatype: "json",
             //Pass the url to the service
             url: "http://localhost:8082/SampleWCF.svc/fetch?Id=10",
             beforeSend: function (req) {
                 //Show some progress bar image
                 //Do some validation like authentication(this is optional)
             },
             success: function (result, textStatus) {
                 //Hide the progress bar image
                 //Check if result is null
                 if ((result.d == null && jQuery.parseJSON(result).d.length > 0) || (result.d != null && result.d.length > 0)) {
                     //Show message for no record found
                 }
                 else {
                     //Fetch the result
                     alert(result);
//Or if result is a container then we can find the result by index also
alert(result.d[0].key; //Where key is nothing but name of the field
                 }
             }
         });

2. Calling a WCF service by using JQuery and perform data pushing (Post Operation)

Step 1 and step 2 will be same as described above

Step 3:
// Make a data set (which is required for the pushing)
var data = _EventSessionDS.data();

Step 3:

Call a WCF through AJAX call.
     $.ajax({
             //Mention the operation type GET as data is pushing to the server
             type: "POST",
             //Content type sent to the server
             contentType: "application/json; charset=utf-8",
             //Expected result format form the server
             datatype: "json",
            // If there is no need to process the data before send
            processData: false,
             //Pass the url to the service
             url: "http://localhost:8082/SampleWCF.svc/Push?Id=10",
            //Convert java script value to JSON 
            data: JSON.stringify({ pSessionsItem: data }),
             beforeSend: function (req) {
                 //Show some progress bar image
                 //Do some validation like authentication(this is optional)
             },
             success: function (result, textStatus) {
                 //Hide the progress bar image
               //show the success message
             }
         });

Delete scenario:
if we need to delete the data by calling a WCF service through JQuery then we need to just remove the data set and data from the above code.
var data = _EventSessionDS.data();
data: JSON.stringify({ pSessionsItem: data }),



Thursday, 6 June 2013

c# - uploading image to azure blob storage

To upload an image to the blob storage for further use, we will call a WCF service method by an Ajax call that returns the unique blob name as result. After that we will render the image into an html control for checking.
Please follow the below steps –

Step 1: Create a WCF method for uploading the image into the blob storage
public String ClientUpload()
        {
            // Declare a variable for holding the blob name
            String tUniqueBlobName = null;
            //Authanticate the user and get the Client Id Let’s say this is ClientId
            try
            {
               // initialize queue storage
               CloudBlobContainer tblob = StorageBlob.GetBlob(StorageBlob.Blobs.fileupload, ClientId);
              // Create a unique file name
               String tUniqueBlobName = string.Format("File_{0}.{1}", Guid.NewGuid().ToString(),        HttpContext.Current.Request.Files[0].FileName.Split('.').Last());

             // Get the blob
            CloudBlockBlob tBlob = tblob.GetBlockBlobReference(tUniqueBlobName);
            tBlob.Metadata.Add("OriginalName", HttpContext.Current.Request.Files[0].FileName);
            tBlob.Metadata.Add("FileExtention",   
            HttpContext.Current.Request.Files[0].FileName.Split('.').Last());
            tBlob.Metadata.Add("UploadedByRecId", pUser.Id.ToString());
            tBlob.Metadata.Add("UploadedByName", pUser.Name);
            tBlob.Metadata.Add("OwnerCompany", pClientId.ToString());
            //ObUser -  User’s object
                tUniqueBlobName = UploadToBlob(ClientId, ObUser);
            }
            catch (Exception ex)
            {
            //show the error
            }
            return tUniqueBlobName;
        }

 Step 2: Create html controls for holding and uploading the image

<div id="divPreview" class="divFrame">
                   </div>
                   <div class="DivButton">
                       <span id="ChangePhoto">Change</span>
//Create a control for image upload and set the display none.
                   <input id="inputUploadFile" name="inputUploadFile" type="file" multiple="false" accept="image/*" style="display:none;" />
</div>

Step 3: On page load of javascript initialize the Upload File control
$(Document).ready(function(){
//Call a method for initialize the file upload control
InstanciateFileUpload();
});
//------------------------------------------------------------------------------------
// Define the initialize file upload method
//------------------------------------------------------------------------------------
function InstanciateFileUpload() {
    $("#inputUploadFile").fileupload({
        limitMultiFileUploads: 1, //set the file upload limit
        replaceFileInput: false,
        dataType: 'json',
        url: _WebServiceAddress + "FileImport/ClientUpload", //WCF service method
        change: function (e, data) {
            if (e.target.files.length > 1) {
//show the error message file cannot be uploaded more than one
            }
        },
        beforeSend: function (req) {
//Do some validation if you want before sending the request
        },
        add: function (e, data) {
            // When a file is added to the upload queue, get a reference to the xhr
            jqXHR = data.submit();
        },
        start: function (e, data) {
            $("#previewProgress").show(); //show the progress image
        },
        done: function (e, data) {
            // Launch preview
            var tImageName = jQuery.parseJSON(data.jqXHR.responseText);
            RenderPreview($("#divPreview"), tImageName, true); //render into html conrol 
        },
        fail: function (e, data) {
            $("#previewProgress").hide(); //Hide the progress image
//Show some failure error message
}
    });
}
Step 4 -  Create a button click event handler for Change the profile picture
//event for change and remove photo
    $("#ChangePhoto").on("click", OnChangePhotoButtonClick);
//Define the method
function OnChangePhotoButtonClick(e) {
    $("#inputUploadFile").click();
}

That is it. Please follow the above steps, it should work.

Downloading of an image from Azure Blob Storage

To download an image from the blob storage for showing in profile picture, we will call a WCF service method that returns a combination of bytes of the image as the result which needs to be rendering to an html control (which will hold the profile picture).

Please follow the below steps –

Step 1: Create a WCF service method for downloading the image from the azure blob storage.

// pass parameter as pImageName, that need to be downloaded from blob

   public String ClientDownload(string pImageName)
{
//Authenticate the user and get the Client Id
Let’s say this is ClientId

//Get the cloud storage account
 var tStorageAccount = CloudStorageAccount.Parse(CloudConfigurationManager.GetSetting("DataConnectionString"))

// initialize queue storage
            CloudBlobClient tBlobStorage = tStorageAccount.CreateCloudBlobClient();
Blobs tBlob = StorageBlob.Blobs.fileupload;
            CloudBlobContainer tBlob = tBlobStorage.GetContainerReference(tBlob.ToString() + "-" + ClientId.ToString().ToLower());
       
//Create the container if it is not existing
blob.CreateIfNotExists();

 // Retrieve reference to a blob
CloudBlockBlob tMyBlob = tContainer.GetBlockBlobReference(pImageName);

// If there is no reference, show the exception

if (!tMyBlob.Exists())
                throw new WebFaultException<String>("File not found", HttpStatusCode.NotFound);

//Handling the image code and convert into into base 64 format
            MemoryStream tMemoryStream = new MemoryStream();
            tMyBlob.DownloadToStream(tMemoryStream);
            Byte[] tFile = tMemoryStream.ToArray();
String tFileAsBase64 = "";
           
tFileAsBase64 = strBase64Img = System.Convert.ToBase64String(tFile, 0, tFile.Length);

            return tFileAsBase64;
        }

Step 2: Create an html control for holding the image.

            Comment – Create a html <div> to hold the picture.

                   <div id="divPreview" class="divFrame">
                   </div>

            Comment – Put a progress image for checking the progress.

                   <img id="previewProgress" src=" loader_Progress.gif" />

 

Follow the CSS styles below (Optional)

<style
 /*Profile pic: picture frame div*/
        .divFrame {
            height: 100px;
            background-color: rgba(128, 128, 128, 0.25);
            text-align: center;
         }
    </style>

 

Step 3: Get result from a WCF service method by an Ajax call.

On the page load call a function to get the image code and render it into an html control.

Let’s say the function name is RenderPreview.

$(Document).ready(function(){

//function for download and render the image code into a html control.
RenderPreview($("#divPreview"),"SamplePicture.png" , false);
});

This is a custom method in Jquery, which is containing three parameters

Ø  Html control where we will render the image code

Ø  Picture name which picture code needs to download from the blob storage

Ø  Boolean value which shows the progress image


//Mehod to get the image code and render to a html control
function RenderPreview(pContainerToRender, pImageName, pIsProgress) {
    if (pImageName != "" && pImageName != undefined && pImageName != null) {
   // Call WCF service method by an Ajax call
    $.ajax({
            url: _”WCFAddressUrl/ClientDownload/" + pImageName,
            type: 'GET',
            dataType: 'json',
            beforeSend: function (req) {
             if (pIsProgress)  //show the progress image(waiting)
// Show the progress image for checking the status
                    $("#previewProgress").show();
            },
            error: function (xhr, status) {
// Hide the progress image for checking the status

                $("#previewProgress").hide();
// Display some error message if comes here
            },
            success: function (result) {
//set the default extension of the image is jpg
                var tExt = "jpg";
//Split by dot ”.”
                var tSplittedName = pImageName.split(".");
//Get the image actual extension

                if (tSplittedName.length == 2) {
                    tExt = tSplittedName[1];
                }

                var tImageSrc = "data:image/jpeg;base64," + result; // JPEG AVOID CHROME WARNING

//Make empty to the rendering control
                pContainerToRender.empty();

//Make an image variable                
var tImg = $("<img />")
                                .attr("alt", "image")
                                .attr("src", tImageSrc);

//put the styles on the image variable
                tImg.css("height", "100%");    //Image is creating dynamically so css need to define in the page (exception case)
                tImg.css("max-height", "290px");
//Render the image into HTML control
                pContainerToRender.append(tImg);

//Hide the progress bar
                $("#previewProgress").hide();
            }
        });
    }
}
Please find the abbreviation about marked by green above in the code

ClientDownload- This is WCF service method for downloading the image(C# code).
pImageName- Pass the parameter to WCF service method.
Result – Get the image code into base 64 string format.

Please follow the all steps, it should work fine.