Tuesday, February 11, 2014

Asp .Net MVC with Ado .Net

Though using an ORM as the Model with Asp .net MVC is the preferred approach to follow,  it is possible to use ADO .Net along with Asp .Net MVC.

Here I am creating a class in Models  folder which contains properties corresponding to the table and  also the CRUD code.



public class ContactGroups
{
SqlConnection cnn;


public int GroupId { get; set; }
public string GroupName { get; set; }

public void InsertGroup(string GroupName)
{
 using (cnn = new SqlConnection(ConfigurationManager.AppSettings.Get("AppCnn")))
 {
     cnn.Open();
     SqlCommand cmd = new SqlCommand("insert into  ContactGroups values ('" + GroupName + "')", cnn);
     cmd.ExecuteNonQuery();
 }
}
public List<ContactGroups> GetGroups()
{
 using (cnn = new SqlConnection(ConfigurationManager.AppSettings.Get("AppCnn")))
 {
  cnn.Open();
  SqlCommand cmd = new SqlCommand("select * from ContactGroups", cnn);
  SqlDataReader dr;
  dr = cmd.ExecuteReader();
  List<ContactGroups> Cg = new List<ContactGroups>();
  while (dr.Read())
  {
   int intGroupId = Int32.Parse(dr["GroupId"].ToString());
   ContactGroups C = new ContactGroups
   {
    GroupId = intGroupId,
    GroupName = dr["GroupName"].ToString()
   };
   Cg.Add(C);
  }
  dr.Close();
  return Cg;
 }
}
}
}
}

HomeController.cs
using MvcAdoDotNetShalvin.Models;

namespace MvcAdoDotNetShalvin.Controllers
{
public class HomeController : Controller
{
public ActionResult Index()
{
  ContactGroups CG = new ContactGroups();
  ViewBag.ContactGroups = CG.GetGroups();

  return View();
}

[HttpPost]
public ActionResult Index(FormCollection coll)
{
  ContactGroups CG = new ContactGroups();
  ViewBag.ContactGroups = CG.GetGroups();
          

  CG.InsertGroup(Request.Form["txtName"]);
  return View();
}

}
}

Index.cshtml
@using MvcAdoDotNetShalvin.Models

@{
    ViewBag.Title = "Index";
    var CG = ViewBag.ContactGroups;
    
    foreach(ContactGroups C in CG)
    {
     <div>@C.GroupId &nbsp @C.GroupName  </div>   
    }
}

<form method ="post">
    <div><input type ="text" name ="txtName" /> </div>
    <div><input type ="submit" /> </div>
</form>
<h2>Index</h2>

}

Multiple Tables
USE [ContactManagement]
GO
/****** Object:  Table [dbo].[Contacts]    Script Date: 4/5/2020 6:16:26 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Contacts](
    [ContactId] [int] IDENTITY(100,1) NOT NULL,
    [GroupId] [int] NULL,
    [ContactName] [varchar](40) NULL,
    [Location] [varchar](40) NULL,
PRIMARY KEY CLUSTERED 
(
    [ContactId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Groups]    Script Date: 4/5/2020 6:16:26 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Groups](
    [GroupId] [int] IDENTITY(1,1) NOT NULL,
    [GroupName] [varchar](40) NULL,
PRIMARY KEY CLUSTERED 
(
    [GroupId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Contacts]  WITH CHECK ADD FOREIGN KEY([GroupId])
REFERENCES [dbo].[Groups] ([GroupId])
GO
/****** Object:  StoredProcedure [dbo].[spAdd]    Script Date: 4/5/2020 6:16:26 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[spAdd](@p1 int, @p2 int)
as
    print @p1 + @p2
GO
/****** Object:  StoredProcedure [dbo].[spAllContacts]    Script Date: 4/5/2020 6:16:26 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[spAllContacts]
as
select C.ContactId, G.GroupName, C.ContactName, C.Location from Groups G, Contacts C where G.GroupId = C.GroupId
GO
/****** Object:  StoredProcedure [dbo].[spAllGroups]    Script Date: 4/5/2020 6:16:26 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[spAllGroups] as
select * from Groups
GO
/****** Object:  StoredProcedure [dbo].[spGetContactById]    Script Date: 4/5/2020 6:16:26 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[spGetContactById](@id int)
as
select C.ContactId, G.GroupName, C.ContactName, C.Location 
from Groups G, Contacts C where G.GroupId = C.GroupId 
and C.ContactId = @id
GO
/****** Object:  StoredProcedure [dbo].[spHello]    Script Date: 4/5/2020 6:16:26 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[spHello]
as 
print 'Hello Stored Procedures'
GO
/****** Object:  StoredProcedure [dbo].[spHelloName]    Script Date: 4/5/2020 6:16:26 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[spHelloName] (@Name varchar(20))
as 
print 'Hello ' + @Name
GO
/****** Object:  StoredProcedure [dbo].[spInsertGroup]    Script Date: 4/5/2020 6:16:26 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[spInsertGroup] (@GroupName varchar(40))
as
insert into Groups values (@GroupName)
GO
Models
using System.ComponentModel.DataAnnotations;

namespace DomainModel
{
    public class Group
    {
        public int GroupId { get; set; }
        [Display(Name = "Group Name")]
        public string GroupName { get; set; }
    }
}

using System.ComponentModel.DataAnnotations;

namespace DomainModel
{
    public class Contact
    {
        public int ContactId { get; set; }
        [Display(Name = "Group Name")]
        public int GroupId { get; set; }

        [Display(Name = "Contact Name")]
        public string ContactName { get; set; }
        public string Location { get; set; }
    }
}

using System.ComponentModel.DataAnnotations;

namespace DomainModel.ViewModels
{
    public class ContactViewModel
    {
        public int ContactId { get; set; }
        [Display(Name = "Group Name")]
        public string GroupName { get; set; }
        [Display(Name = "Contact Name")]
        public string ContactName { get; set; }
        public string Location { get; set; }
    }
}

GroupsRepository
using DomainModel;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;

namespace RepositoryLayer
{
    public class GroupsRepository
    {
        SqlConnection cnn = new SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=ContactManagement;Integrated Security=True");
        SqlCommand cmd = null;
        public List<Group> GetGroups()
        {
            cnn.Open();

            cmd = new SqlCommand("spAllGroups", cnn);
            SqlDataReader dr = cmd.ExecuteReader();
            List<Group> GroupsList = new List<Group>();

            while(dr.Read())
            {
                int GId = Int32.Parse(dr["GroupId"].ToString());
                Group g = new Group
                {
                    GroupId = GId,
                    GroupName = dr["GroupName"].ToString()
                };
                GroupsList.Add(g);
            }

            cnn.Close();
            return GroupsList;
        }

        public void Insert(Group g)
        {
            cnn.Open();
            cmd = new SqlCommand("spAllGroups", cnn);
            cmd.Parameters.AddWithValue("@GroupName", g.GroupName);
            cmd.ExecuteNonQuery();

        }
    }
}


ContactsRepository

using System;
using System.Collections.Generic;
using System.Data.SqlClient;

using DomainModel;
using DomainModel.ViewModels;

namespace RepositoryLayer
{
    public class ContactsRepository
    {
        SqlConnection cnn = new SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=ContactManagement;Integrated Security=True");
        SqlCommand cmd = null;
        public List<ContactViewModel> GetContacts()
        {
            cnn.Open();
             cmd = new SqlCommand("spAllContacts", cnn);
            
            List<ContactViewModel> ContactsList = new List<ContactViewModel>();
            using (SqlDataReader dr = cmd.ExecuteReader())
            {
                while (dr.Read())
                {
                    int CId = Int32.Parse(dr["ContactId"].ToString());
                    ContactViewModel cvm = new ContactViewModel
                    {
                        ContactId = CId,

                        GroupName = dr["GroupName"].ToString(),
                        ContactName = dr["ContactName"].ToString(),
                        Location = dr["Location"].ToString()
                    };
                    ContactsList.Add(cvm);
                }
                
             
            }
            return ContactsList;
        }

        public ContactViewModel GetContactById(int id)
        {
            cnn.Open();
            string strSql = "spGetContactById " + id;

            cmd = new SqlCommand(strSql, cnn);
           // cmd.Parameters.AddWithValue("@id", id);
           // cmd.CommandType = System.Data.CommandType.StoredProcedure;


            ContactViewModel Contact = null;
            SqlDataReader dr = cmd.ExecuteReader();

            while (dr.Read())
            {
                 int CId = Int32.Parse(dr["ContactId"].ToString());
                Contact = new ContactViewModel
                {
                     ContactId = CId,

                    GroupName = dr["GroupName"].ToString(),
                    ContactName = dr["ContactName"].ToString(),
                    Location = dr["Location"].ToString()
                };
            }
             return Contact;
        }

        public void InsertContact(Contact c)
        {
            cnn.Open();

            cmd = new SqlCommand("insert into Contacts values (@GroupId, @ContactName, @Location)", cnn);
            cmd.Parameters.AddWithValue("@GroupId", c.GroupId);
            cmd.Parameters.AddWithValue("@ContactName", c.ContactName);
            cmd.Parameters.AddWithValue("@Location", c.Location);
            cmd.ExecuteNonQuery();
            cnn.Close();
        }
    }
}

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();
    }

Tuesday, February 4, 2014

Kerala Microsoft User Group - Kochi DevCon 2014

Saturday, 15 February 2014
Venue :InfoPark Hall, Athulya Cafeterial Block, Kakkanad

Agenda
08:30 - 9:00 - Registration Confirmation
09:00 - 9:15 - Welcome and Community update
Hands On Lab: Hybrid Mobile Applications in HTML5
Hands On Lab: Developing iOS and Android applications in C# using Xamarin
09:15 - 10:15 - Building Cloud Connected Native iOS and Android Apps in C# - Nish Anil
10:15 - 11:15 - Develop ASP.NET MVC Application Faster with Kendo UI - Lohith G Nagaraj
11:15 - 11:30 - Tea Break (15 min)
11:30 - 12:15 - Visual Studio 2013 & C# vNext - Deep Dive by Anoop Madhusudanan
12:15 - 01:00 - What is new in .NET 4.5.1 - Praveen Nair
01:00 - 01:45 - Lunch Break (45 min)
01:45 - 02:30 - Developing Apps with Windows Phone App Studio - Amal Dev
02:30 - 03:15 - ASP.NET Web API - Bijith K B
03:15 - 04:00 - A Deep Dive Into Windows Azure Mobile Services - Shiju Varghese
04:00 - 04:15 - Tea Break (15 min)
04:15 - 05:00 - Windows Azure Media Services - Yanesh Tyagi
05:00 - 05:30 - Closing Ceremony