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

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

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 

 

Register