Avoid SQL Injection in dynamic SQL query inside stored procedure.

Frnds,

I had a requirement where I had to write a stored procedure which will give the data back to the user. Looks very simple but the interesting part was that, Inside SP the table name was coming as a parameter with other parameters.
I went ahead created stored procedure something like shown below here:


Create PROC [sp_demo_injection02]
(
@tablename varchar(50),
@username varchar(50),
@lastname varchar (50)
)
AS
  declare @cmd nvarchar(max)
  declare @parameters nvarchar(max)
  set @cmd = N'SELECT * FROM '+ @tablename +' WHERE username = '+''''+ @username+''''
  set @cmd = @cmd+' and lastname= '+''''+@lastname+''''
  --print @cmd
  Exec @cmd
go

In runtime sql query for this
exec [sp_demo_injection02] 'tblUser','bkm','mahto'
generated like
SELECT * FROM tblUser WHERE username = 'bkm' and lastname= 'mahto'

but when I run the same SP with below parameter I was shocked:
[sp_demo_injection02] 'tblUser;Drop table tblUser;--','bkm','mahto'
Output generated query:
SELECT * FROM tblUser;Drop table tblUser;-- WHERE username = 'bkm' and lastname= 'mahto'

Let's see here what I did, Basically I have given a way to a hacker to delete/or do anything serious using my query. Above query will drop my table and this is a big security risk.

Above query is just an example, In real time it could be a big disaster. So this is called SQL Injection.

Now I must need to re-write my stored procedure to avoid this sql injection, so this is what I did:
I changed my above SP like this:

Create PROC [sp_demo_injection02](
@tablename varchar(50),
@username varchar(50),
@lastname varchar (50)
)
AS
  declare @cmd nvarchar(max)
  declare @parameters nvarchar(max)

  set @cmd = N'SELECT * FROM '+ quotename(@tablename)+' WHERE username = @username and @lastname=lastname' --Make use of quotename for sql object

 set @parameters = '@username varchar(50), @lastname varchar (50)' --Here goes all parameters except the sql object(table name, sp name)
  
EXEC sp_executesql @cmd, @parameters, @username = @username, @lastname = @lastname
go


Let me explain what i did here:
1. I removed concatenation for table name and parameters which was the biggest culprits here.

2. Also I used quotename(@tablename) : quotename is a sql function which will convert my @tablename string value to this [tblUser]. So any string withing bracket is considered as a sql object (table, stored proc, view...). this method can be apply to a string for 128 char long.
for more info: http://msdn.microsoft.com/en-us/library/ms176114.aspx

3. I converted all my string parameter to sql parameter.

set @parameters = '@username varchar(50), @lastname varchar (50)'

4. Executing my dynamic query using sp_executesql which executes my dynamic sql query by accepting parameters.
For more Info: http://msdn.microsoft.com/en-us/library/ms188001.aspx

In this way you can be sure of any sql attacks for your dynamic sql query. But for best practices you also must do following things:
Implement strong server side validation for all user inputs including cookie values.

1. Must do server side validation and escape or filter the special characters from user inputs.
2. Always use store procedures whenever possible.
3. Always use parameters with stored procedures or dynamic queries inside stored procedures.
4. Always use least privileged account (like read-only) to execute queries.
5. Avoid disclosing error (exception) details to the user, instead use customize error information for client. Because exception details may have critical information (like code logic, server info., etc)

Thanks.

Custom Handle Error Attribute to handle exception in MVC 3 app

Frnds.

In this post I am going to explain, How can we handle error in MVC  application through common custom error attribute.

First create the custom Error Handler Attribute class. So here is our attribute class:

 public class HandleExceptionAttribute : FilterAttribute, IExceptionFilter
    {
        public void OnException(ExceptionContext filterContext)
        {
            filterContext.ExceptionHandled = true;

            string controllerName = filterContext.RouteData.Values["controller"].ToString();
            string actionName = filterContext.RouteData.Values["action"].ToString();
            string exceptionMessage = string.Empty;

            //You may not need this tracing code. Ignore it!
            Trace.TraceError("Controller = {0} : Action = {1} : Message = {2} : StackTrace = {3}",
                controllerName,
                actionName,
                filterContext.Exception.Message,
                filterContext.Exception.StackTrace);

            //Below is the switch case to handle error for all the controller class in our
            //MVC application at one place.
            switch (controllerName)
            {
                case "State":
                    switch (actionName)
                    {
                        case "Create":
                            actionName = "Index";
                            exceptionMessage = "Sorry, an error occured on creating new State, Please try again.";
                            break;
                        case "Edit":
                            actionName = "Index";
                            exceptionMessage = "Sorry, an error occured on editing new State, Please try again.";
                            break;
                        case "Delete":
                            actionName = "Index";
                            exceptionMessage = "Sorry, an error occured on deleting new State, Please try again.";
                            break;
                        default:
                            break;
                    }
                    break;
                case "Account":
                    switch (actionName)
                    {
                        case "Register":
                            controllerName = "Register";
                            actionName = "Index";
                            exceptionMessage = "Sorry, an error occured on registering new User, Please try again.";
                            break;
                        case "Login":
                            controllerName = "Login";
                            actionName = "Index";
                            exceptionMessage = "Sorry, an error occured on Login, Please try again.";
                            break;

                    }

                    break;
            }

            //Creating ViewData Dictionary which will hold the error info to show to the end user.
            ViewDataDictionary viewData = new ViewDataDictionary();
            viewData.Add(new KeyValuePair<string, object>("controller", controllerName));
            viewData.Add(new KeyValuePair<string, object>("action", actionName));
            viewData.Add(new KeyValuePair<string, object>("message", exceptionMessage));

            //Calling error page inside shared folder to show the proper error message.
            filterContext.Result = new ViewResult { ViewName = "Error", ViewData = viewData };
        }
    }

Above attribute will help to handle error. You can add your controller and action method in switch case

statement as per your need.

Now Next step we will see how to use this attribute.

        [HandleException]
        public ActionResult Edit(StateModel stateModel)
        {
            try
            {
                // TODO: Add update logic here
                throw new InvalidOperationException();
            }
            catch(Exception ex)
            {
                throw ex;
            }
        }

Since my Custom Error Handler Attribute  is written to handle error for each action of controller so I need to use it before my action method. In above code [HandleException] attribute  will execute our Custom Handle Attribute class code written here in case of any error.

Next step we need to modify/create the error  page (Error.cshtml) inside shared folder to show the error message to the end user.

@{
    ViewBag.Title = "Error";
}

<h2>
    Sorry, @ViewData["message"].<br />
    <a href="@Url.Action(ViewData["action"].ToString(),ViewData["controller"].ToString())">Back</a>
</h2>

Note: Modify the above code as per your need and enjoy.

Thank You for reading.

CRUD operation using MVC3 and Jquery

Frnds,

In this post I am gonna write code which is explaining CRUD operation using MVC WebGrid and Jquery.

Below Image Gallary shows Grid Data with Paging and Sorting functionality.

   

 

In this post I am gonna write code which is explaining CRUD operation using MVC WebGrid and Jquery.

 Before proceeding further we need to have our model class. Here is the our model class.

StateModel.cs

namespace MyMVC3WebApp.Models
{
    public class StateModel
    {
        [ScaffoldColumn(false)]
        public int StateID { get; set; }

        [Required]
        [StringLength(10)]
        [Remote("StateNameExist","State")]
        public string StateName { get; set; }

        [Required]
        [StringLength(10)]
        public string CountryName { get; set; }
    }
}

In our above model class I have implemented basic validation including Custom Remote validation which actually checks

State Name existence into the DB before submitting form. Here Remote attribute has two parameters, first one is action method name and the second one is the Controller name.

Above code will help you to understand the basic CRUD operation for data. Please give your feedback if you really like it or if you have any suggestion here.

Now let’s create our controller class which will have the code for the database operation.

StateController.cs

namespace MyMVC3WebApp.Controllers
{
    public class Country
    {
        public string Name { get; set; }
    }

    public class StateController : Controller
    {

        private TestDataEntities db = new TestDataEntities();

      //Method which returns list of country for dropdown country selection.
        public JsonResult GetCountries()
        {
            List<Country> resultset = new List<Country>() {
                new Country(){Name="India"},
                new Country(){Name="US"},
                new Country(){Name="Australia"},
                new Country(){Name="Rusia"},
            };
            return Json(resultset, JsonRequestBehavior.AllowGet);
        }

//Method to check state name existence being called by Remote attribute implemented in Model class for validation.

        public JsonResult StateNameExist(string stateName)
        {
            int count = (from states in db.StateMasters.AsEnumerable()
                         where states.StateName.ToLower().Trim() == stateName.ToLower().Trim()
                         select states.StateName).Count();
            if (count > 0)
                return Json(string.Format("State name {0} already exists.", stateName), JsonRequestBehavior.AllowGet);
            else
                return Json(true, JsonRequestBehavior.AllowGet);

        }
//Method returns list of states.
        public ActionResult Index()
        {
            IEnumerable<StateModel> states = from st in db.GetAllStates()
                                             select new StateModel() { StateID = st.StateID, StateName = st.StateName, CountryName = st.CountryName };
            return View(states.ToList<StateModel>());
        }

        //
        // GET: /State/Details/5

        public ActionResult Details(int id)
        {
            return View();
        }

        //
        // GET: /State/Create

        public ActionResult Create()
        {
            return View(new StateModel());
        }

        //
        // POST: /State/Create

        [AcceptVerbs(HttpVerbs.Post)]
        public void AddNewState(string stateName, string countryName)
        {
            db.AddState(stateName, countryName);
        }

        [AcceptVerbs(HttpVerbs.Post)]
        public void UpdateState(int id, string stateName, string countryName)
        {
            StateMaster state = db.StateMasters.Single(s => s.StateID == id);
            state.StateName = stateName;
            state.CountryName = countryName;
            db.SaveChanges();
        }

        [HttpPost]
        public ActionResult Create(StateModel stateModel)
        {
            try
            {
                if (ModelState.IsValid)
                {
                    int result = Convert.ToInt32(db.AddState(stateModel.StateName, stateModel.CountryName));
                }

                return RedirectToAction("Index");
            }
            catch
            {
                return View();
            }
        }

        public ActionResult Delete(int id)
        {
            db.DeleteState(id);
            return RedirectToAction("Index");
        }
    }
}

Now this is the right time to create our view as we have ready with our model and controller class.

Index.cshtml

@model IEnumerable<MyMVC3WebApp.Models.StateModel>
@{
    ViewBag.Title = "Index";
}
<h2>
    Index</h2>
<link href="http://1.cdn.blog.com/wp-admin/../../Content/themes/base/jquery.ui.all.css" rel="stylesheet" type="text/css" />
<link href="http://1.cdn.blog.com/wp-admin/../../Content/themes/base/jquery.ui.dialog.css" rel="stylesheet" type="text/css" />
<script src="@Url.Content("~/Scripts/jquery.validate.min.js")" type="text/javascript"></script>
<script src="@Url.Content("~/Scripts/jquery.validate.unobtrusive.min.js")" type="text/javascript"></script>
<script src="http://1.cdn.blog.com/wp-admin/../../Scripts/jquery-ui-1.8.11.js" type="text/javascript"></script>
<style>
    body
    {
        font-size: 82.5%;
    }
    label, input
    {
        display: block;
    }
    input.text
    {
        margin-bottom: 12px;
        width: 95%;
        padding: .4em;
    }
    fieldset
    {
        padding: 0;
        border: 0;
        margin-top: 25px;
    }
    h1
    {
        font-size: 1.2em;
        margin: .6em 0;
    }
    div#users-contain
    {
        width: 350px;
        margin: 20px 0;
    }
    div#users-contain table
    {
        margin: 1em 0;
        border-collapse: collapse;
        width: 100%;
    }
    div#users-contain table td, div#users-contain table th
    {
        border: 1px solid #eee;
        padding: .6em 10px;
        text-align: left;
    }
    .ui-dialog .ui-state-error
    {
        padding: .3em;
    }
    .validateTips
    {
        border: 1px solid transparent;
        padding: 0.3em;
    }
    .odd
    {
        background-color: #FFF;
    }
    .even
    {
        background-color: #E8E8E8;
    }

    .grid
    {
        margin: 4px;
        border-collapse: collapse;
        width: 600px;
    }
    .head
    {
        background-color: #E8E8E8;
        font-weight: bold;
        color: #FFF;
    }
    .grid th, .grid td
    {
        border: 1px solid #C0C0C0;
        padding: 5px;
    }
    .alt
    {
        background-color: #E8E8E8;
        color: #000;
    }
    .gridItem
    {
        width: auto;
        font-weight: normal;
        font-style: normal;
    }

    .gridHiddenItem
    {
        visibility: hidden;
    }

    .img
    {
        width: 20px;
        height: 20px;
        text-align: center;
    }
</style>
<script type="text/javascript">
    $(function () {  //$(document).ready(function () {

        //Start-apply css on table data
        $('tr:even').addClass('even');
        $('tr:odd').addClass("odd");
        //End-apply css on table data

        //Start-a workaround for a flaw in the demo system (http://dev.jqueryui.com/ticket/4375), ignore!
        $("#dialog:ui-dialog").dialog("destroy");
        //End-a workaround for a flaw in the demo system (http://dev.jqueryui.com/ticket/4375), ignore!

        //Start-get controls
        var state = $("#stateName"),
   country = $("#countryName"),
            btnDel = $('#deleteRecord'),
   allFields = $([]).add(state).add(country),
   tips = $(".validateTips");
        //End-get controls

        //Start-Json Call to get all country to fill the country dropdown.
        $.getJSON('/State/GetCountries', '',
        function (obj) {
            var html = "";
            for (var i = 0; i < obj.length; i++) {
                html += "<option value='"
                    + obj[i].Name
                        + "'>" + obj[i].Name
                            + "</option>";
            }
            country.html(html);
        }
        );
        //End-Json Call to get all country to fill the country dropdown.

        //Start-apply css for validation error message
        function updateTips(t) {
            tips
    .text(t)
    .addClass("ui-state-highlight");
            setTimeout(function () {
                tips.removeClass("ui-state-highlight", 1500);
            }, 500);
        }
        //End-apply css for validation error message

        //Start-length check validation
        function checkLength(o, n, min, max) {
            if (o.val().length > max || o.val().length < min) {
                o.addClass("ui-state-error");
                updateTips("Length of " + n + " must be between " +
     min + " and " + max + ".");
                return false;
            } else {
                return true;
            }
        }
        //End-length check validation

        //Start-state name existence validation.
        function checkIfStateExist(o) {
            var val = o.val();
            var IsExist = false;
            //synchroneous ajax call
            $.ajax({
                type: 'GET',
                url: '/State/StateNameExist',
                dataType: 'json',
                success: function (obj) {
                    if (obj == true) {
                        IsExist = true;
                    }
                    else {
                        o.addClass("ui-state-error");
                        updateTips(obj);
                        IsExist = false;
                    }
                },
                data: { stateName: val },
                async: false
            });
            return IsExist;
        }
        //End-state name existence validation.

        //Start-Convert div form to model dialog
        $("#dialog-form").dialog({
            autoOpen: false,
            height: 350,
            width: 250,
            modal: true,
            buttons: {
                "Submit": function () {
                    var bValid = true;
                    allFields.removeClass("ui-state-error");
                    //Perform Validation here..
                    bValid = bValid && checkLength(state, "State Name", 3, 45);
                    bValid = bValid && checkIfStateExist(state);
                    if (bValid) {
                        var id = $("#stateID").val();
                        var st = state.val();
                        var ctry = country.val();
                        if (id != "") {
                            url = "/State/UpdateState";
                            $.post(url, { id: id, stateName: st, countryName: ctry }, function (data) {
                                //reload data
                                location.reload();
                            });
                        }
                        else {
                            var url = "/State/AddNewState";
                            $.post(url, { stateName: st, countryName: ctry }, function (data) {
                                //reload data
                                location.reload();
                            });
                        }

                        $(this).dialog("close");
                    }
                },
                Cancel: function () {
                    $(this).dialog("close");
                }
            },
            close: function () {
                allFields.val("").removeClass("ui-state-error");
            }
        });
        //End-Convert div form to model dialog

        //Start-show dialog on create new button click
        $("#create-state")
   .button()
   .click(function () {
       $('#dialog-form').attr('title', 'Create New State');
       $("#dialog-form").dialog("open");
   });
        //End-show dialog on create new button click

        //Start-show dialog on Edit button click
        $("a#editRecord")
   .click(function (obj) {
       $('#dialog-form').attr('title', 'Edit State');
       $("#dialog:ui-dialog").dialog("destroy");
       var trObj = $(this).parents("tr");
       var id = trObj[0].cells[0].lastChild.defaultValue;
       var state = trObj[0].cells[1].innerText.trim();
       var country = trObj[0].cells[2].innerText.trim();
       $("#stateID")[0].value = id;
       $("#stateName")[0].value = state;
       $("#countryName").val(country);
       $("#dialog-form").dialog("open");
   });
        //End-show dialog on Edit button click

    });

    function ConfirmDelete() {
        return confirm('Do you want to delete this record');
    }

</script>

<p>
    <a id="create-state" href="#">Create New</a>
</p>
<div>
    <table>
        <tr>
            <td>
                @{
                    var grid = new WebGrid(Model, canPage: true, rowsPerPage: 5);
                }
                <div id="grid">
                    @grid.GetHtml(
                 tableStyle: "grid",
                headerStyle: "head",
                alternatingRowStyle: "alt",
                columns: grid.Columns(
                    grid.Column(null, null, format: @<input type="hidden" name="ID" value="@item.StateID"/>),
                    grid.Column("StateName", "State", style: "gridItem", canSort: true),
                    grid.Column("CountryName", "Country", style: "gridItem", canSort: true),
                    grid.Column("Edit", format: @<text><a id="editRecord" href="#"><img alt="" src="../../Content/images/Edit.jpg"
                        style="height: 20px; width: 20px" /></a></text>, style: "imgstyle"),
                    grid.Column("Delete", format: @<text><a href="@Url.Action("Delete", "State", new { id = item.StateID })" onclick="javascript:return ConfirmDelete();"><img
                        alt="" src="../../Content/images/delete.png" style="height: 20px; width: 20px" /></a></text>, style: "imgstyle")
)
                )
                </div>
            </td>
        </tr>
    </table>
</div>
<div>
    <div id="dialog-form" title="State">
        <p>
            All form fields are required.</p>
        <form>
        <fieldset>
            <input id="stateID" type="text" style="visibility: hidden" />
            <label for="stateName">
                State</label>
            <input type="text" name="stateName" id="stateName" />
            <label for="country">
                Country</label>
            <select id="countryName" style="width: 95%">
            </select>
        </fieldset>
        </form>
    </div>
</div>

Above view has style, script and html code for the view. starting of this post, post has the output images for the above code. Enjoy!

Generic class for caching in windows application for .Net Framework 4.0

Let’s write a generic class to cache data in in-memory cache for window application (Framework 4.0). Please refer the below code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Runtime.Caching;
using System.IO;
namespace GenericUtils
{
    public static class CacheUtils<T> where T : class
    {
        private static ObjectCache cache = MemoryCache.Default;
        public static void SetCache(string key, T objValue)
        {
            //CacheItemPolicy policy = new CacheItemPolicy();
            //policy.AbsoluteExpiration = new DateTimeOffset(DateTime.Now.AddHours(2));
            //cache.Add(key, obj, policy);
            lock (typeof(CacheUtils<T>))
            {
                cache[key] = objValue;
            }
        }
        public static T GetCache(string key)
        {
            if (cache.Contains(key))
            {
                return (T)cache.Get(key);
            }
            return null;
        }
    }
}

Generic class for caching in windows application for .Net Framework 3.5, 3.0 and 2.0

A generic class to cache data in in-momory cache for window application (Framework 3.5, 3.0, 2.0). Please refer the below code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;

namespace Utils.Framework.Common
{
    /// <summary>
    /// Cache Utility class
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <remarks></remarks>
    public static class CacheUtils<T> where T : class
    {
        private static Dictionary<string, object> cachedHashSet = new Dictionary<string, object>();
        private static readonly object lockObject = new object();

        /// <summary>
        /// Sets the cache.
        /// </summary>
        /// <param name="key">The key.</param>
        /// <param name="objValue">The obj value.</param>
        /// <remarks></remarks>
        public static void SetCache(string key, T objValue)
        {

            lock (lockObject)
            {
                //Delete cache if it exists already 
                if (cachedHashSet.ContainsKey(key))
                {
                    DeleteCache(key);
                    cachedHashSet.Add(key, objValue);
                }
                else
                {
                    cachedHashSet.Add(key, objValue);
                }
            }

        }

        /// <summary>
        /// Gets the cache from cache memory.
        /// </summary>
        /// <param name="key">The key.</param>
        /// <returns></returns>
        /// <remarks></remarks>
        public static T GetCache(string key)
        {
            if (cachedHashSet.ContainsKey(key))
            {
                return cachedHashSet[key] as T;
            }
            return default(T);
        }

        /// <summary>
        /// Deleting cache.
        /// </summary>
        /// <param name="key"></param>
        /// <returns></returns>
        public static bool DeleteCache(string key)
        {
            if (cachedHashSet.ContainsKey(key))
            {
                cachedHashSet.Remove(key);
            }
            return true;

        }
    }
}

Get Columns Name as First Row of Result Table using SQL Query

One of my friend asked me :
How to get the Columns Name of Table as a First row of Result Table?
Ex: Table is:

and the result should be:

So Here is the answer for this: I have a table called StateMaster in my database and to get the above result below is the query.

Create table #tbl (col1 varchar(100),col2 varchar(100),col3 varchar(100))

INSERT INTO #tbl(col1,col2,col3)
VALUES(NULL,NULL,NULL)
DECLARE @name varchar(50),
@i int
set @i=1;
DECLARE tblcur CURSOR STATIC LOCAL FOR
SELECT column_name from information_schema.columns
WHERE table_name = 'StateMaster' --Do not use schamea prefix (dbo.) here.
ORDER BY ordinal_position

OPEN tblcur
WHILE 1 = 1
BEGIN
FETCH tblcur INTO @name
IF @@fetch_status <> 0
BREAK
Declare @sql varchar(max)
set @sql='UPDATE #tbl
set col'+Cast(@i as varchar)+'='''+@name+''''
exec (@sql)
set @i=@i+1
END
DEALLOCATE tblcur

INSERT INTO #tbl(col1,col2,col3)
Select * from dbo.StateMaster
select * from #tbl
drop table #tbl

Upload File to the Azure blob in chunks using Parallel Programming

Below written code describes how to upload a file to the blob in chunks. In below code i tried to write comment for each line of code.

/// <summary>
/// Upload file to the blob parallely.
/// </summary>
/// <param name="blobName">blob url</param>
/// <param name="uploadFileInfo">File Info</param>
/// <param name="chunkSizeInKB">Chunk Size in KB</param>
/// <returns></returns>
public static bool ParallelFileUploadToBlob(string blobName, FileInfo uploadFileInfo, int chunkSizeInKB)
{
    List<String> blockList = new List<String>();
    CloudBlockBlob UploadableBlob;

    try
    {
        //get the blob reference
        UploadableBlob = blobClient.GetBlockBlobReference(blobName);

        //read the contents of the file into byte array
        var dataToUpload = File.ReadAllBytes(uploadFileInfo.FullName);

        // if file byte is empty return false
        if (dataToUpload.Length == 0)
        {
            return false;
        }

        //creating block size in MB. Ex: 1024*1024=1MB
        Int32 blockLength = 1024 * Convert.ToInt32(chunkSizeInKB);

        //calculate number of chunks based on chunk size.
        var numberOfBlocks = ((int)dataToUpload.Length / blockLength) + 1;
        string[] blockIds = new string[numberOfBlocks];

        //Using Parallel programming to upload chunk datas of file
        Parallel.For(0, numberOfBlocks, x =>
        {
            var blockId = Convert.ToBase64String(Guid.NewGuid().ToByteArray());

            //calculating size of first chunk
            var currentLength = Math.Min(blockLength, dataToUpload.Length - (x * blockLength));

            //creating memory stream of the chunk of file and upload it to the blob.
            using (var memStream = new MemoryStream(dataToUpload, x * blockLength, currentLength))
            {
                retryPolicy.ExecuteAction(() =>
                {
                    //committing a block as a part of blob
                    UploadableBlob.PutBlock(blockId, memStream, null);
                });
            }
            blockIds[x] = blockId;
        });

        retryPolicy.ExecuteAction(() =>
        {
            //committing all blocks of a blob
            UploadableBlob.PutBlockList(blockIds);
        });

        return true;
    }
    catch (Exception ex)
    {
        //Handle Error.
        return false;
    }
}

Schedule an ASP page to run through task scheduler on Windows Azure

Follow the below steps to schedule a job(scheduling an asp page to run) to the task scheduler.

Step 1: Create a web role.

Step 2: Create a command file (*.cmd file).

Step 3: Include below code to the command file to schedule the task to the Task Scheduler automatically.

SCHTASKS /Create /SC DAILY /ST 02:15:00 /SD 12/12/2011 /TN Task_Name /TR “File/exe/task path to run” /RU SYSTEM /F /RL HIGHEST

Ex: SCHTASKS /Create /SC DAILY /ST 02:15:00 /SD 12/12/2011 /TN HelloWorldTest/TR “.\approot\ HelloWorldTest.vbs” /RU SYSTEM /F /RL HIGHEST

above code schedule task which execute  a vbscript file which internally calls the web page.

Note: More information on SCHTASKS command is available here

http://www.robvanderwoude.com/schtasks.php#End

Step 4: Create a vbscript (*.vbs) file and include the following code.

set x=createobject(“microsoft.xmlhttp”)

x.Open ”GET”, ”http://helloworldwebtest.cloudapp.net/index.asp”, False

x.Send

set x=nothing

Above code sample is calling to a wep page hosted on cloud.

Step 5: Include the below code to the servicedefinition file of the created azure project.

<WorkerRole name=”helloworld_workerrole” vmsize=”Small”>

    <Startup>

      <Task taskType=”simple” executionContext=”elevated” commandLine=”helloworld.cmd”>

      </Task>

    </Startup>

  </WorkerRole>

Above code will execute the command file whenever web role new instance will be created.

Step 5: Since this web role’s work is to schedule a job to the VM’s Task Scheduler we need not to go for more than one instance.

Step 6: Host the application to Windows Azure Platform.

Step 7: Confirm whether the job is schedule in VM’s task scheduler by logging remotely to the VM and open Task Scheduler.

Note: In this blog post I am explained a way to schedule a task automatically which calls a web page which can have a certain logic to perform some tasks.

Use Windows Azure Storage in your classic asp application

In this post I am going to explain how to use windows azure storage in our classic asp application. This is one of the scenario of moving your classic asp application to Azure Platform.
When we move to azure we can not use the server local space to save/upload files so in this case window azure is great things.
Below are the steps which need to be followed to achieve using of windows azure storage in classic asp application:
Step 1: Create a .net COM Visible class library solution which will have business logic to save/update/read file to/from the window storage.
To create a COM visible .net class library follow below steps:

•     Create an interface with basic method signatures like create file, delete file, read file and list files.
• 
  •          Create a class inheriting above interface

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using Microsoft.WindowsAzure.StorageClient;

using Microsoft.WindowsAzure;

using System.Net;

using Microsoft.WindowsAzure.ServiceRuntime;

using System.Runtime.InteropServices;

using System.Collections.Specialized;

using System.IO;

using System.Collections;

namespace StorageUtilities

{

    [ComVisible(true),

    GuidAttribute("FADA9268-29C1-4BD2-BE61-6ABC8DB81959")]

    [ProgId("StorageUtilities.FileStorageOperations")]

    public class FileStorageOperations : IFileStorageOperations

    {

        private CloudBlobClient blobStorage;

        private CloudStorageAccount storageAccount;

        /// <summary>

        /// create a new file inside the blob container

        /// </summary>

        /// <param name=”blobcontainerName”>BlobContainer name</param>

        /// <param name=”fileName”>file name with extension</param>

        /// <param name=”fileContent”>file content</param>

        /// <param name=”fileType”>file type (for example: application/xls for xls file</param>

        /// <returns></returns>

        public string CreateFile(string accountName, string accountKey,stringblobContainerName, string fileName, string fileContent, string fileType)

        {

            try

            {

                //storageAccount = CloudStorageAccount.DevelopmentStorageAccount;

                storageAccount=new CloudStorageAccount(newStorageCredentialsAccountAndKey(accountName,accountKey),true);

                // read account configuration settings

                blobContainerName = blobContainerName.ToLower();

                //Get the container reference

                blobStorage = storageAccount.CreateCloudBlobClient();

                CloudBlobContainer container = blobStorage.GetContainerReference(blobContainerName);

                // Create a blob in container and upload contents to it

                string uniqueBlobName = string.Format(blobContainerName + ”/” + fileName);

                var blob = blobStorage.GetBlobReference(uniqueBlobName);

                //var sas = blob.GetSharedAccessSignature(new SharedAccessPolicy()

                //{

                //    Permissions = SharedAccessPermissions.Write,

                //    SharedAccessExpiryTime = DateTime.Now.AddMinutes(10)

                //});

                blob.Properties.ContentType = fileType;

                blob.UploadText(fileContent);

                return blob.Uri.ToString();

            }

            catch (Exception ex)

            {

               return ”Error:” + ex.Message;

            }

        }

        ///// <summary>

        ///// Delete the existing file from the blobcontainer

        ///// </summary>

        ///// <param name=”blobcontainerName”>BlobContainer name</param>

        ///// <param name=”fileName”>file name with extension</param>

        ///// <returns></returns>

        public string DeleteFile(string accountName, string accountKey, stringblobContainerName, string fileName)

        {

            try

            {

                //storageAccount = CloudStorageAccount.DevelopmentStorageAccount;

                storageAccount=new CloudStorageAccount(newStorageCredentialsAccountAndKey(accountName,accountKey),true);

                blobContainerName = blobContainerName.ToLower();

                //Get the container reference

                blobStorage = storageAccount.CreateCloudBlobClient();

                CloudBlobContainer container = blobStorage.GetContainerReference(blobContainerName);

                //Get the blob reference and delete the file.

                string uniqueBlobName = string.Format(blobContainerName + ”/” + fileName);

                var blob = blobStorage.GetBlobReference(uniqueBlobName);

                blob.DeleteIfExists();

                return ”true”;

            }

            catch (Exception ex)

            {

                return ”Error:” + ex.Message;

            }

        }

        ///// <summary>

        ///// Read the existing file in blob and returns the contents as a string

        ///// </summary>

        ///// <param name=”blobcontainerName”>BlobContainer name</param>

        ///// <param name=”fileName”>file name with extension</param>

        ///// <returns></returns>

        public object[] ReadFileContent(string accountName, string accountKey,string blobContainerName, string fileName)

        {

            try

            {

                //storageAccount = CloudStorageAccount.DevelopmentStorageAccount;

                storageAccount = new CloudStorageAccount(newStorageCredentialsAccountAndKey(accountName, accountKey), true);

                ArrayList fileinfo = new ArrayList();

                blobContainerName = blobContainerName.ToLower();

                //Get the container reference

                blobStorage = storageAccount.CreateCloudBlobClient();

                CloudBlobContainer container = blobStorage.GetContainerReference(blobContainerName);

                //Get the blob reference and read the content of the file.

                string uniqueBlobName = string.Format(blobContainerName + ”/” + fileName);

                var blob = blobStorage.GetBlobReference(uniqueBlobName);

                TextReader stringReader = new StringReader(blob.DownloadText());

                while (stringReader.Peek() >= 0)

                {

                    fileinfo.Add(stringReader.ReadLine());

                }

                return fileinfo.Cast<object>().ToArray(); ;

            }

            catch (Exception ex)

            {

                throw ex;

            }

        }

        /// <summary>

        /// Returns files infos stored on blob

        /// </summary>

        /// <param name=”blobContainerName”>Blob Container Name</param>

        /// <returns></returns>

        public object[] Listfiles(string accountName, string accountKey, stringblobContainerName)

        {

            FileInfo[] filesInfo;

            try

            {

                //storageAccount = CloudStorageAccount.DevelopmentStorageAccount;

                storageAccount=new CloudStorageAccount(newStorageCredentialsAccountAndKey(accountName,accountKey),true);

                blobContainerName = blobContainerName.ToLower();

                //Get the container reference

                blobStorage = storageAccount.CreateCloudBlobClient();

                CloudBlobContainer container = blobStorage.GetContainerReference(blobContainerName);

                int noOfBlobs = container.ListBlobs().Count();

                filesInfo = new FileInfo[noOfBlobs];

                int count = 0;

                // Loop over blobs within the container and output the URI to each of them

                foreach (var blobItem in container.ListBlobs())

                {

                    FileInfo fileInfo = new FileInfo();

                    fileInfo.FilePath = blobItem.Uri.ToString();

                    var blob = blobStorage.GetBlobReference(blobItem.Uri.ToString());

                    fileInfo.Filename = blob.Name;

                    fileInfo.FileSize = ((Microsoft.WindowsAzure.StorageClient.CloudBlob)(blobItem)).Attributes.Properties.Length.ToString();

                    fileInfo.ContentType = ((Microsoft.WindowsAzure.StorageClient.CloudBlob)(blobItem)).Attributes.Properties.ContentType.ToString();

                    fileInfo.DateLastModified = ((Microsoft.WindowsAzure.StorageClient.CloudBlob)(blobItem)).Attributes.Properties.LastModifiedUtc.ToString();

                    filesInfo[count] = fileInfo;

                    count++;

                }

                return filesInfo.Cast<object>().ToArray();

            }

            catch (Exception ex)

            {

                throw ex;

                // return (“Error:” + ex.Message).ToString().ToArray();

            }

        }

    }

}

• Right click on the project, Go to Properties =>Build, and check the checkbox for “Register for COM  interop” as shown in below image.

 

• Got to Signing and import the key as it is required to register the assembly to GAC.

 

• Now we are ready with COM visible .net component.

Step 2: Now follow the below link to make use of our created component to the classic asp azure application.

          Using COM components in Azure ASP Application

        Above link has very nice explanation on how to make use of COM visible .net component to classic asp application.

• Test the code using below code in your asp page.

          <%

 dim filestorageop,uploadedfile, strContent

 set filestorageop=server.CreateObject(“StorageUtilities.FileStorageOperations”)

 dim accountname,accountkey, containerName

 accountname=”AzureStorageAccountName”

 accountkey=”AzureStorageAccountKey”

 containerName=”exportfile”

 ’Creating file

 strContent=filestorageop.CreateFile(accountname,accountkey,containerName,”testfile1.txt”, ”text to write into the file”, ”application/txt”)

 ’Reading Content from the file

 strContent=filestorageop.ReadFileContent(accountname,accountkey,containerName,”testfile1.txt”)

 Dim mobjFolderContents

 dim mobjFileItem

 ’List all the files from the container

 mobjFolderContents = filestorageop.Listfiles(accountname,accountkey,containerName)

 Response.Write(UBound(mobjFolderContents))

  Response.Write ”<BR>”

              For each mobjFileItem in mobjFolderContents

                Response.Write(“FileName: “ & mobjFileItem.Filename)

          Response.Write ”<BR>”

          Response.Write(“FilePath: “ & mobjFileItem.FilePath & ”  “)

          Response.Write ”<BR>”

          Response.Write(“FileSize: “ & mobjFileItem.FileSize & ”  “)

          Response.Write ”<BR>”

          Response.Write(“Content Type: “ & mobjFileItem.ContentType & ”  “)

          Response.Write ”<BR>”

          Response.Write(“Date Last Modified: “ & mobjFileItem.DateLastModified)

          Response.Write ”<BR>”

          Response.Write ”<BR>”

        next

 set filestorageop=nothing

%>

Note: Please feel free to write in case of any query you have.