Monday, February 10, 2014

ASP .Net Membership and LINQ

I am going to demonstrate working with Asp .Net Membership and LINQ with a simple Bank comprising of only Deposit, Withdraw and Balance checking functionality.

The application is having two roles: Admin and Secured users.  The Admin will be the Bank official who will be performing depositing and withdraw functionality for the user.




The Secured used can login and view their Balance.

The User Information  is stored in User Info  table which is integrated into Membership and Roles tables.


CREATE TABLE [UserInfo](
    [UserId] [int] IDENTITY(1,1),
    [UserName] [varchar],
    [Balance] [numeric](18, 2))

<connectionStrings>
    <add name="DefaultConnection" providerName="System.Data.SqlClient" 
connectionString="Data Source=.\sqlexpress;InitialCatalog=ShalvinBankSimple;Integrated Security=True"/>
</connectionStrings>

 In the class I am defining the class corresponding to the table. Functions for retrieving generic list of  UserInfo is GetUserInfo. Likewise I am defining functions for retieving User Information, Deposit and Withdraw functionalities.
UserInfo.cs
public class UserInfo
{
 public int UserId { get; set; }
 public string UserName { get; set; }
 public double Balance { get; set; }

 SqlConnection cnn;
 public List<UserInfo> GetUserInfo()
 {
  using (cnn = new SqlConnection(
   ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
  {
   cnn.Open();

   List<UserInfo> glUserInfo = new List<UserInfo>();

   SqlCommand cmd = new SqlCommand("select * from UserInfo", cnn);
   SqlDataReader dr;
   dr = cmd.ExecuteReader();
   while (dr.Read())
   {
    UserInfo ui = new UserInfo
    {
     UserId = Int32.Parse(dr["UserId"].ToString()),
     UserName = dr["UserName"].ToString(),
     Balance = double.Parse(dr["Balance"].ToString())
    };
    glUserInfo.Add(ui);
   }

   return glUserInfo;
  }
 }

 public void InsertUser(UserInfo u)
 {
  using (cnn = new SqlConnection(
   ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
  {
   cnn.Open();
   SqlCommand cmd = new SqlCommand("Insert into UserInfo values (@UserName, @Balance)", cnn);
   cmd.Parameters.AddWithValue("@UserName", u.UserName);
   cmd.Parameters.AddWithValue("@Balance", u.Balance);
   cmd.ExecuteNonQuery();
  }
 }

 public UserInfo GetInfoByName(string UserName)
 {
  var UserI = (from p in GetUserInfo()
               where p.UserName == UserName
               select p).SingleOrDefault();
  return UserI;
 }

 public void Deposit(UserInfo u)
 {
  using (cnn = new SqlConnection(
  ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
  {
   cnn.Open();

   SqlCommand cmd = new SqlCommand("update Userinfo set Balance = Balance + @Balance where UserName=@UserName", cnn);
   cmd.Parameters.AddWithValue("@UserName", u.UserName);
   cmd.Parameters.AddWithValue("@Balance", u.Balance);

   cmd.ExecuteNonQuery();
  }
 }
 public void Withdraw(UserInfo u)
 {
  using (cnn = new SqlConnection(
  ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
  {
   cnn.Open();

   SqlCommand cmd = new SqlCommand("update Userinfo set Balance=Balance-@balance where UserName=@UserName", cnn);
   cmd.Parameters.AddWithValue("@username", u.UserName);
   cmd.Parameters.AddWithValue("@balance", u.Balance);

   cmd.ExecuteNonQuery();
  }
 }
}

Register.aspx.cs
UserInfo objUserInfo = new UserInfo();
protected void CreateUserWizard1_CreatedUser(object sender, EventArgs e)
{
 UserInfo ui = new UserInfo{
  UserName = CreateUserWizard1.UserName,  Balance = 0 };
 objUserInfo.InsertUser(ui);
}

Default.aspx.cs
protected void Page_Load(object sender, EventArgs e)
{
 if (User.IsInRole("Admin"))
  Response.Redirect("Admin/AdminHome.aspx");
 else if (User.Identity.IsAuthenticated)
  Response.Redirect("Secured/SecuredHome.aspx");
}

AdminHome.aspx.cs
using System.Linq;
public partial class Admin_AdminHome : System.Web.UI.Page
{
 UserInfo objUser = new UserInfo();
    protected void Page_Load(object sender, EventArgs e)
    {
     if (!IsPostBack)
     {
      ddlUsers.DataSource = objUser.GetUserInfo();
      ddlUsers.DataTextField = "UserName";
      DataBind();
     }
    }
    protected void ddlUsers_SelectedIndexChanged(object sender, EventArgs e)
    {
     ShowBalanceForUser();
    }
    protected void btnDeposit_Click(object sender, EventArgs e)
    {
     objUser.Deposit(new UserInfo { UserName = ddlUsers.SelectedItem.ToString(), Balance = Double.Parse(txtAmount.Text) });
     ShowBalanceForUser();
    }

    private void ShowBalanceForUser()
    {
     var User = objUser.GetInfoByName(ddlUsers.Text);
     lblCurBal.Text = User.Balance.ToString();
    }
    protected void btnWithdraw_Click(object sender, EventArgs e)
    {
     objUser.Withdraw(new UserInfo { UserName = ddlUsers.SelectedItem.ToString(), Balance = Double.Parse(txtAmount.Text) });
     ShowBalanceForUser();
    }
} 

SecureHome.aspx.cs
UserInfo objUserInfo = new UserInfo();
    protected void Page_Load(object sender, EventArgs e)
    {
     var User = objUserInfo.GetInfoByName(Membership.GetUser().ToString());

     lblCurBal.Text = User.Balance.ToString();
    }

No comments:

Post a Comment