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
As
BEGIN 
   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
        begin
                                   
            INSERT INTO [tbStudentPayment]
           ([QueryId]
           ,[StudentId]
           ,[PaymentId]          
           ,[ActionDate]
           ,[Status])
            VALUES
           (@QueryId
           ,@StudentId
           ,1
           ,GETDATE()
           ,'Admission Request')

    END
Close curTemp
Deallocate curTemp
    
END


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.empty();
                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");
            }
            else
                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;
            else
                isAuthorized = true;

            return isAuthorized;
        }

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

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

 [AuthorizeAdminAuthorize]
        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

Asp.net 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));
                dlUserdetails.DataBind();
            }
            TotalPaggingRecords = totalRows;
        }


// the property

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

            set
            {
                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)
                                            .ToList().Count;

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

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

            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