Wednesday, May 29, 2013

Schedule Job with store procedure, store procedure with cursor

This is an example of  scheduling a sql job with a store procedure, also writing a cursor with in a store procedure. 

alter procedure uspInitiateAdmission
   declare @QueryId bigint, @StudentId bigint, @PaymentId bigint, 
   @RequestStatus int , @PaymentStatus int 
     declare curTemp cursor for
        fetch data based on your query and where clause
     SELECT  t2.StudentRequestId, t2.RequestStatus, t2.RequestStatus   
        FROM tbStudentPayment t1 right outer JOIN tbSchoolManagementBoard t2
                      ON t1.QueryId = t2.StudentRequestId
                      where t2.RequestStatus <> 4 and
                      DATEADD(dd, 3, t2.RequestDateTime) >= GETDATE() and
                      t2.StudentRequestId not in (select QueryId from tbStudentPayment)
  Open  curTemp     
        fetch next from curTemp into @QueryId, @PaymentStatus, @RequestStatus

    while @@FETCH_STATUS =0
            INSERT INTO [tbStudentPayment]
           ,'Admission Request')

Close curTemp
Deallocate curTemp

populating child dropdownlist using JSON Jquery in MVC4 Razor

here i am sharing an example of populating child dropdownlist when changing some value parent dropdownlist

Step 1:
Write two dropdownlist country and state

@Html.DropDownListFor(m => m.CountryId,
            new SelectList(ViewBag.Countries, "Value", "Text"),
            "Select Country", new { data_url = Url.Action("GetStates") })

 @Html.DropDownListFor(m => m.StateId,
            new SelectList(ViewBag.States, "Value", "Text"),
            "Select State")

Step 2:
Write a method in controller which will return JsonResult of states, so that can be consumed using jquery , and populate the state list

public JsonResult GetStates(int countryId)
            IEnumerable< tbState > states = _SecurityService.GetStates(countryId);
            return Json(states, JsonRequestBehavior.AllowGet);


Step : 3
now lets look at the script, where we get the JSON result and populate the state dropdownlist

< script src="~/Scripts/jquery-1.7.1.min.js" >< /script >

< script >
    $(document).ready(function () {

        $('#CountryId').change(function () {

            var url = $(this).data('url');

            var data = { countryId: $(this).val() };

            $.getJSON(url, data, function (GetStates) {
                var ddlState = $('#StateId');
                ddlState.append($('< option/ >', {
                    value: 0,
                    text: "Select State"

                $.each(GetStates, function (index, StateObj) {
                    ddlState.append($('< option/ >', {
                        value: StateObj.StateId,
                        text: StateObj.State

< /script >

Tuesday, May 28, 2013

how to set httpContext.User.Identity.Name, form authentication

My friend asked me how to set httpContext.User.Identity.Name, because he was getting the object is null, so to answer his questing i have shared the this piece of implementation, and sharing the same on this article.

Step 1 :
In your authentication method make sure you have added  FormsAuthentication.SetAuthCookie , which actually set the httpContext.User object, the following code shows how easily you can do that.

tbAdminUser adminUser = _AdminAcctService.Authenticate(model.UserName, model.Password);

            if (adminUser != null)
                Session["AdminUser"] = adminUser;

              FormsAuthentication.SetAuthCookie(model.UserName, true);

                return RedirectToAction("Index", "JewelAdmin");
                ViewBag.ErrorMessage = "Username or password is incorrect";

Step 2:
Now before performing any task, to check if the user is authenticated or not you can check
httpContext.User.Identity.IsAuthenticated  or  if (httpContext.User.Identity.Name == null)

Step 3:
While logging out, make sure you have set the following line in your logout method
FormsAuthentication.SignOut(); this will clear off the cookie which was been set by SetAuthCookie, so the User Name will become null in your HttpContext object.

Namespace required using System.Web.Security;

Custom AuthorizeAttribute example in mvc4 razor

Here i am sharing an example of creating a custom AuthorizeAttribute in mvc4 application, and its implementation.

Step 1 :
Create a class

 [AttributeUsage(AttributeTargets.Class | AttributeTargets.Method, Inherited = true, AllowMultiple = true)]
    public class AuthorizeAdminAuthorizeAttribute : AuthorizeAttribute
protected override bool AuthorizeCore(HttpContextBase httpContext)
            var isAuthorized = base.AuthorizeCore(httpContext);
            if (!isAuthorized)
                isAuthorized = false;
            if (httpContext.User.Identity.Name == null)
                isAuthorized = false;
                isAuthorized = true;

            return isAuthorized;

        protected override void HandleUnauthorizedRequest(AuthorizationContext filterContext)
            if (!filterContext.HttpContext.User.Identity.IsAuthenticated)
                filterContext.Result = new RedirectToRouteResult(new
                RouteValueDictionary(new { controller = "AdminAccount", action = "Index" }));
                filterContext.Result = new RedirectToRouteResult(new
                RouteValueDictionary(new { controller = "JewelAdmin", action = "Index" }));

Step 2:
Now we see how to call the authorize attribute in action

        public ActionResult AddProduct()
 return View();

So, now in whichever action you want authorization to be checked , just put [AuthorizeAdminAuthorize] attribute on top of that ActionResult

Sunday, March 17, 2013 datalist pagging with LINQ SQL

Code Behind :

// Page load implementation :

protected void Page_Load(object sender, System.EventArgs e)
            if (!Page.IsPostBack)
                string pagenumber = Request.QueryString["pagenumber"] as string;
                if (!string.IsNullOrEmpty(pagenumber))
                    CurrentPageNumber = Convert.ToInt32(pagenumber);

 void LoadUserDetails()
            int totalRows = 0;
            using (AdminDTO dto = new AdminDTO())
                dlUserdetails.DataSource = dto.GetAllUsers(CurrentPageNumber, defaultPageSize, ref totalRows, Convert.ToInt32(dlType.SelectedItem.Value));
            TotalPaggingRecords = totalRows;

// the property

public int CurrentPageNumber
                // look for current page in ViewState
                object o = this.ViewState["_CurrentPage"];
                if (o == null)
                    return 1; // default page index of 0
                    return (int)o;

                this.ViewState["_CurrentPage"] = value;

now lets look at how to page data using LINQ, the following method calls at linq  , the pagging tricks is in  .Skip((pageNumber - 1) * pageSize).Take(pageSize)

dto.GetAllUsers(CurrentPageNumber, defaultPageSize, ref totalRows, Convert.ToInt32(dlType.SelectedItem.Value)

public List GetAllUsers(int pageNumber, int pageSize, ref int TotalRows, int dType)
            List users = new List();

            using (var context = new DBETNetEntities())
                    TotalRows = context.UserInfoes
                        .OrderByDescending(ui => ui.CreateDate)

                    users = context.UserInfoes
                        .OrderByDescending(ui => ui.CreateDate)
                         .Skip((pageNumber - 1) * pageSize).Take(pageSize)

now create a function that will page the data based on page number and rowindex, probably you can make it more genric

 public string GetPageNavigation(int currentPageNumber, int pageSize, int totalRecords)

            decimal _pages = (decimal)totalRecords / (decimal)pageSize;
            int pagecount = Convert.ToInt32(Math.Ceiling(_pages)); //Math.Max(_pages);

            StringBuilder pageStr = new StringBuilder();
            pageStr.Append("< table >< tr>");
            pageStr.Append(string.Format("< td >Page {0} of< /td >", CurrentPageNumber));
            pageStr.Append(string.Format("< td >Records {0} < /td >", TotalPaggingRecords));
            pageStr.Append("< td style='background-color:#ccc;width:18px;'> < /td>");

            for (int i = 1; i <= pagecount; i++)
                if (i == currentPageNumber)
                    pageStr.Append(string.Format("< td style='background-color:#000;color:#fff;border:1px solid #e7e7e7;' >{0}< /td>", i));
                    pageStr.Append(string.Format("< td style='background-color:#fff;border:1px solid #e7e7e7;' > < a href='ControlPanel.aspx?pagenumber={0}' >{1}< /a>< /td>", i, i));

                if (i >= 25)
                    pageStr.Append(string.Format("< td style='background-color:#fff;border:1px solid #e7e7e7;'> < a href='ControlPanel.aspx?pagenumber={0}' >Next< /a >< /td >", (currentPageNumber + 1)));

            pageStr.Append("< /tr>< /table >");
            return pageStr.ToString();

Pagging is done, now lets look how to call the pagging from html implementation

< div style="margin: 2px; padding: 4px; border: 1px solid #ccc; background-color: #f5f5f5;" >
            < %=GetPageNavigation(CurrentPageNumber, defaultPageSize, TotalPaggingRecords)% >
        < /div >

ETG Consultancy

Web Designing Development Analysis & Promotion
Asp.Net 2.0 SQL WWF WCF SEO Marketing Ajax JQuery NHibernate MVC