Monday, August 1, 2022

Using Authorization with Swagger in ASP.NET Core

 Create Solution like below




























LoginModel.cs

using System.ComponentModel.DataAnnotations;

namespace UsingAuthorizationWithSwagger.Models
{
    public class LoginModel
    {
        [Required(ErrorMessage = "Email Required")]
        public string UserName { get; set; }
        [Required(ErrorMessage = "Password Required")]
        public string Password { get; set; }
    }
}

Product.cs

namespace UsingAuthorizationWithSwagger.Models
{
    public class Product
    {
        public int Id { get; set; }
        public string? Name { get; set; }

    }
}

ProductStore.cs

using UsingAuthorizationWithSwagger.Models;

namespace UsingAuthorizationWithSwagger.Data
{
    public static class ProductStore
    {
        private static Product[] products = new Product[]
        {
            new Product { Id = 1, Name = "Rubber duck"},
            new Product { Id = 2, Name = "Flip flop"},
            new Product { Id = 3, Name = "Magic Wand"},
            new Product { Id = 4, Name = "Glitter pen"}
        };

        public static IEnumerable<Product> GetProducts()
        {
            return products;
        }

        public static Product? GetProduct(int id)
        {
            foreach (var product in products)
            {
                if (product.Id == id)
                    return product;
            }
            return null;
        }
    }
}

WeatherForecast.cs

namespace UsingAuthorizationWithSwagger
{
    public class WeatherForecast
    {
        public DateTime Date { get; set; }

        public int TemperatureC { get; set; }

        public int TemperatureF => 32 + (int)(TemperatureC / 0.5556);

        public string? Summary { get; set; }
    }
}

AuthController.cs


using Microsoft.AspNetCore.Mvc;
using Microsoft.IdentityModel.Tokens;
using System.IdentityModel.Tokens.Jwt;
using System.Security.Claims;
using System.Text;
using UsingAuthorizationWithSwagger.Models;

namespace UsingAuthorizationWithSwagger.Controllers
{
    [Route("api/auth")]
    [ApiController]
    public class AuthController : ControllerBase
    {
        [HttpPost]
        public IActionResult Login(LoginModel model)
        {
            if (model == null)
            {
                return BadRequest("Invalid client request");
            }

            if (model.UserName == "johndoe" && model.Password == "johndoe2410")
            {
                var secretKey = new SymmetricSecurityKey(Encoding.UTF8.GetBytes("superSecretKey@2410"));
                var signinCredentials = new SigningCredentials(secretKey, SecurityAlgorithms.HmacSha256);

                var tokenOptions = new JwtSecurityToken(
                    issuer: "CodeMaze",
                    audience: "https://localhost:5001",
                    claims: new List<Claim>(),
                    expires: DateTime.Now.AddMinutes(5),
                    signingCredentials: signinCredentials
                );

                var tokenString = new JwtSecurityTokenHandler().WriteToken(tokenOptions);
                return Ok(new { Token = tokenString });
            }
            else
            {
                return Unauthorized();
            }
        }
    }
}

ProductController.cs

using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Mvc;
using UsingAuthorizationWithSwagger.Data;

namespace UsingAuthorizationWithSwagger.Controllers
{
    [Route("api/product")]
    [ApiController]
    public class ProductController : ControllerBase
    {
        [HttpGet, Authorize]
        public IActionResult GetAllProducts()
        {
            var products = ProductStore.GetProducts();
            return Ok(products);
        }

        [HttpGet("{id}")]
        public IActionResult GetAProduct(int id)
        {
            var product = ProductStore.GetProduct(id);

            if (product is null)
                return NotFound();

            return Ok(product);
        }
    }
}

WeatherForecastController.cs

using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Mvc;

namespace UsingAuthorizationWithSwagger.Controllers
{
    [ApiController]
    [Route("[controller]")]
    public class WeatherForecastController : ControllerBase
    {
        private static readonly string[] Summaries = new[]
        {
        "Freezing", "Bracing", "Chilly", "Cool", "Mild", "Warm", "Balmy", "Hot", "Sweltering", "Scorching"
    };

        private readonly ILogger<WeatherForecastController> _logger;

        public WeatherForecastController(ILogger<WeatherForecastController> logger)
        {
            _logger = logger;
        }

        [HttpGet(Name = "GetWeatherForecast"),Authorize]
        public IEnumerable<WeatherForecast> Get()
        {
            return Enumerable.Range(1, 5).Select(index => new WeatherForecast
            {
                Date = DateTime.Now.AddDays(index),
                TemperatureC = Random.Shared.Next(-20, 55),
                Summary = Summaries[Random.Shared.Next(Summaries.Length)]
            })
            .ToArray();
        }
    }
}

appsettings.json

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "AllowedHosts": "*"

}

Program.cs

using Microsoft.AspNetCore.Authentication.JwtBearer;
using Microsoft.IdentityModel.Tokens;
using Microsoft.OpenApi.Models;
using System.Text;

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.

builder.Services.AddControllers();

builder.Services.AddAuthentication(opt =>
{
    opt.DefaultAuthenticateScheme = JwtBearerDefaults.AuthenticationScheme;
    opt.DefaultChallengeScheme = JwtBearerDefaults.AuthenticationScheme;
})
    .AddJwtBearer(options =>
    {
        options.TokenValidationParameters = new TokenValidationParameters
        {
            ValidateIssuer = true,
            ValidateAudience = true,
            ValidateLifetime = true,
            ValidateIssuerSigningKey = true,

            ValidIssuer = "CodeMaze",
            ValidAudience = "https://localhost:5001",
            IssuerSigningKey = new SymmetricSecurityKey(Encoding.UTF8.GetBytes("superSecretKey@2410"))
        };
    });

builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen(opt =>
{
    opt.SwaggerDoc("v1", new OpenApiInfo { Title = "MyAPI", Version = "v1" });
    opt.AddSecurityDefinition("Bearer", new OpenApiSecurityScheme
    {
        In = ParameterLocation.Header,
        Description = "Please enter token",
        Name = "Authorization",
        Type = SecuritySchemeType.Http,
        BearerFormat = "JWT",
        Scheme = "bearer"
    });

    opt.AddSecurityRequirement(new OpenApiSecurityRequirement
    {
        {
            new OpenApiSecurityScheme
            {
                Reference = new OpenApiReference
                {
                    Type=ReferenceType.SecurityScheme,
                    Id="Bearer"
                }
            },
            new string[]{}
        }
    });
});

var app = builder.Build();

// Configure the HTTP request pipeline.
if (app.Environment.IsDevelopment())
{
    app.UseDeveloperExceptionPage();
    app.UseSwagger();
    app.UseSwaggerUI();
}

app.UseHttpsRedirection();

app.UseRouting();
app.UseAuthentication();
app.UseAuthorization();

app.UseEndpoints(endpoints =>
{
    endpoints.MapControllers();
});

app.Run();

public partial class Program { }

Run the project and login, then you will get token






















Expand "api/auth" , click on "Try it out" , enter login details and click on "Execute" to show output












































Copy "Token" and enter in Authorize popup. When click on "Authorize" , it will authorize all application.


























Thursday, July 28, 2022

ASP.NET Core Web API with Swagger Interface

The Swagger is a simple but powerful representation of the RESTful API. Nowadays most developers are using Swagger in almost every modern programming language and deployment environment to document. With a Swagger-enabled Web API, you will get interactive documentation, client SDK generation as well as discoverability.

  • Launch the Visual Studio 2022 IDE.
  • Click on “Create new project.”
  • In the “Create new project” window, select “ASP.NET Core Web API” from the list of templates displayed.
  • Click Next.
  • In the “Configure your new project” window, specify the name and location for the new project.
  • Optionally check the “Place solution and project in the same directory” check box, depending on your preferences.
  • Click Next.
  • In the “Additional Information” window shown next, select .NET 6.0 as the target framework from the drop-down list at the top. Set “Authentication Type” to “None” (default) and check the last two check boxes (Use controllers and Enable OpenAPI support).
  • Ensure that the “Enable Docker” and “Configure for HTTPS” check boxes are unchecked as we won’t be using those features here.
  • Click Create.
























When click on "Authorize", then will open window likes below











Enter value and click on Authorize then output shown as below












Click on "Try it out" then will expand window like below
























Enter the input values and click the "Execute" button to run the API. The response will be displayed as follows


























To add Swagger to  ASP.NET Core Web API project, you need to install an open-source project called Swashbuckle via NuGet as shown below. Add Packages as shown below

























launchSettings.json 


{
  "$schema": "https://json.schemastore.org/launchsettings.json",
  "iisSettings": {
    "windowsAuthentication": false,
    "anonymousAuthentication": true,
    "iisExpress": {
      "applicationUrl": "http://localhost:49270",
      "sslPort": 0
    }
  },
  "profiles": {
    "SwaggerDemo": {
      "commandName": "Project",
      "dotnetRunMessages": true,
      "launchBrowser": true,
      "launchUrl": "swagger",
      "applicationUrl": "http://localhost:5224",
      "environmentVariables": {
        "ASPNETCORE_ENVIRONMENT": "Development"
      }
    },
    "IIS Express": {
      "commandName": "IISExpress",
      "launchBrowser": true,
      "launchUrl": "swagger",
      "environmentVariables": {
        "ASPNETCORE_ENVIRONMENT": "Development"
      }
    }
  }
}

LoginController.cs


using Microsoft.AspNetCore.Mvc;
using Microsoft.IdentityModel.Tokens;
using System.IdentityModel.Tokens.Jwt;
using System.Security.Claims;
using System.Text;

namespace SwaggerDemo.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class LoginController : ControllerBase
    {
        [HttpPost, Route("login")]
        public IActionResult Login(LoginDTO loginDTO)
        {
            try
            {
                if (string.IsNullOrEmpty(loginDTO.UserName) || string.IsNullOrEmpty(loginDTO.Password))
                    return BadRequest("Username and/or Password not specified");
                if (loginDTO.UserName.Equals("joydip") && loginDTO.Password.Equals("joydip123"))
                {
                    var secretKey = new SymmetricSecurityKey(Encoding.UTF8.GetBytes("thisisasecretkey@123"));
                    var signinCredentials = new SigningCredentials(secretKey, SecurityAlgorithms.HmacSha256);
                    var jwtSecurityToken = new JwtSecurityToken(
                        issuer: "ABCXYZ",
                        audience: "http://localhost:51398",
                        claims: new List<Claim>(),
                        expires: DateTime.Now.AddMinutes(10),
                        signingCredentials: signinCredentials
                    );
                    Ok(new JwtSecurityTokenHandler().
                    WriteToken(jwtSecurityToken));
                }
            }
            catch
            {
                return BadRequest
                ("An error occurred in generating the token");
            }
            return Unauthorized();
        }
    }
}

WeatherForecastController.cs


using Microsoft.AspNetCore.Mvc;

namespace SwaggerDemo.Controllers
{
    [ApiController]
    [Route("[controller]")]
    public class WeatherForecastController : ControllerBase
    {
        private static readonly string[] Summaries = new[]
        {
        "Freezing", "Bracing", "Chilly", "Cool", "Mild", "Warm", "Balmy", "Hot", "Sweltering", "Scorching"
    };

        private readonly ILogger<WeatherForecastController> _logger;

        public WeatherForecastController(ILogger<WeatherForecastController> logger)
        {
            _logger = logger;
        }

        [HttpGet(Name = "GetWeatherForecast")]
        public IEnumerable<WeatherForecast> Get()
        {
            return Enumerable.Range(1, 5).Select(index => new WeatherForecast
            {
                Date = DateTime.Now.AddDays(index),
                TemperatureC = Random.Shared.Next(-20, 55),
                Summary = Summaries[Random.Shared.Next(Summaries.Length)]
            })
            .ToArray();
        }
    }
}

LoginDTO.cs

namespace SwaggerDemo
{
    public class LoginDTO
    {
        public string UserName { get; set; }
        public string Password { get; set; }
    }
}

WeatherForecast.cs


namespace SwaggerDemo
{
    public class WeatherForecast
    {
        public DateTime Date { get; set; }

        public int TemperatureC { get; set; }

        public int TemperatureF => 32 + (int)(TemperatureC / 0.5556);

        public string? Summary { get; set; }
    }
}

Program.cs


using Microsoft.OpenApi.Models;

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.

builder.Services.AddControllers();
// Learn more about configuring Swagger/OpenAPI at https://aka.ms/aspnetcore/swashbuckle
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen(option =>
{
    option.SwaggerDoc("v1", new OpenApiInfo { Title = "Demo API", Version = "v1" });
    option.AddSecurityDefinition("Bearer", new OpenApiSecurityScheme
    {
        In = ParameterLocation.Header,
        Description = "Please enter a valid token",
        Name = "Authorization",
        Type = SecuritySchemeType.Http,
        BearerFormat = "JWT",
        Scheme = "Bearer"
    });
    option.AddSecurityRequirement(new OpenApiSecurityRequirement
    {
        {
            new OpenApiSecurityScheme
            {
                Reference = new OpenApiReference
                {
                    Type=ReferenceType.SecurityScheme,
                    Id="Bearer"
                }
            },
            new string[]{}
        }
    });
});

var app = builder.Build();

// Configure the HTTP request pipeline.
if (app.Environment.IsDevelopment())
{
    app.UseSwagger();
    app.UseSwaggerUI();
}

app.UseAuthorization();

app.MapControllers();

app.Run();


Tuesday, July 26, 2022

ASP.NET Core CRUD Operations and login

Create the ASP.NET MVC Web Application

 Create New Project by selecting ASP.NET Core Web App (Model-View-Controller)
































Click on create , then will create application.

Solution looks like below

ASP.NET CORE 5.0 Project Structure

















  • Dependencies: It contains all the installed NuGet packages. We can manage the NuGet packages by right clicking on it.
  • Properties: It contains launchSettings.json file which has visual studio profiles, iis and debug setting.
  • wwwroot folder: It is the web root folder of asp.net core application where we can put all the static files such as  javascript , css , images.
  • Controllers: It contails all the controller class we create in our asp.net core mvc application.
  • Models: We can put all the model or view model classes inside this folder.
  • Views: We can add views for certain actions methods inside view folder. There will be seperate folder for each view we create inside Views folder.
  • appsettings.json: It is the application configuration file which is used to store configuration settings i.e connections strings of the database, global variables etc. 
  • Program.cs : Initially asp.net core application starts as a console application. In the Main method it calls the CreateWebHostBuilder() method that configures the asp.net core setting and launch it as asp.net core application.
  • Startup.cs:  It contains the ConfigureServices() and Configure methods. As the name implies ConfigureServices() method configures all the services which are going to used by the application. Configure method take care of all the request processing pipelines.

Create Database And Student Table

Create a new database named [StudentManagement] in sql-server and execute the below SQL query to create student table.

Student


CREATE TABLE [dbo].[Student](
[Id] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [varchar](50) NOT NULL,
[LastName] [varchar](50) NOT NULL,
[Email] [varchar](30) NOT NULL,
[Mobile] [varchar](20) NOT NULL,
[Address] [varchar](220) NULL,
[UserName] [varchar](50) NULL,
[Password] [varchar](50) NULL,
[CreatedOn] [datetime] NULL,
 CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED 
(
[Id] 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

ErrorLog


CREATE TABLE [dbo].[ErrorLog](
[ErrorId] [int] IDENTITY(1,1) NOT NULL,
[ErrorNumber] [varchar](50) NULL,
[ErrorSeverity] [varchar](50) NULL,
[ErrorState] [varchar](50) NULL,
[ErrorProcedure] [varchar](50) NULL,
[ErrorLine] [varchar](50) NULL,
[ErrorMessage] [varchar](250) NULL,
[CreatedOn] [datetime] NULL,
 CONSTRAINT [PK_ErrorLog] PRIMARY KEY CLUSTERED 
(
[ErrorId] 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

Stored Procedures

spAddErrorLog


CREATE PROCEDURE [dbo].[spAddErrorLog]          
(          
    @ErrorNumber VARCHAR(50),           
    @ErrorSeverity VARCHAR(50),          
    @ErrorState VARCHAR(50),          
    @ErrorProcedure VARCHAR(50),  
    @ErrorLine VARCHAR(50),
@ErrorMessage VARCHAR(250)
)          
AS           
BEGIN           
    INSERT INTO ErrorLog (ErrorNumber,ErrorSeverity,ErrorState, ErrorProcedure,ErrorLine,ErrorMessage,CreatedOn)           
    VALUES (@ErrorNumber,@ErrorSeverity,@ErrorState, @ErrorProcedure,@ErrorLine,@ErrorMessage,GETDATE())           
END  
GO

spAddStudent


CREATE PROCEDURE [dbo].[spAddStudent]          
(          
    @FirstName VARCHAR(50),           
    @LastName VARCHAR(50),          
    @Email VARCHAR(30),          
    @Mobile VARCHAR(20),  
    @Address VARCHAR(220),
@UserName VARCHAR(50),
@Password VARCHAR(50)
)          
AS           
BEGIN           
    INSERT INTO Student (FirstName,LastName,Email, Mobile,Address,UserName,Password)           
    VALUES (@FirstName,@LastName,@Email, @Mobile,@Address,@UserName,@Password)           
END  
GO

spDeleteStudent


CREATE PROCEDURE [dbo].[spDeleteStudent]           
(            
   @Id int            
)            
AS             
BEGIN            
   DELETE FROM Student WHERE Id=@Id            
END  
GO

spGetAllStudent


CREATE PROCEDURE [dbo].[spGetAllStudent]
(
@TransactionId INT,
@Id INT
)
AS        
BEGIN 
BEGIN TRANSACTION; 
BEGIN TRY
IF @TransactionId = 1
BEGIN
SELECT * FROM Student ORDER BY Id  
END
ELSE IF @TransactionId = 2
BEGIN
SELECT * FROM Student WHERE Id = @Id ORDER BY Id  
END
   

IF @@TRANCOUNT > 0  
    COMMIT TRANSACTION;  

END TRY
BEGIN CATCH

 DECLARE @ErrorNumber VARCHAR(50),
@ErrorSeverity VARCHAR(50),
@ErrorState VARCHAR(50),
@ErrorProcedure VARCHAR(50),
@ErrorLine VARCHAR(50),
@ErrorMessage VARCHAR(250)

SELECT   
         @ErrorNumber = ERROR_NUMBER() 
        ,@ErrorSeverity = ERROR_SEVERITY()  
        ,@ErrorState = ERROR_STATE()  
        ,@ErrorProcedure = ERROR_PROCEDURE() 
        ,@ErrorLine = ERROR_LINE()  
        ,@ErrorMessage = ERROR_MESSAGE()
EXECUTE dbo.[spAddErrorLog] @ErrorNumber,@ErrorSeverity,@ErrorState,@ErrorProcedure,@ErrorLine,@ErrorMessage;
  
    IF @@TRANCOUNT > 0  
        ROLLBACK TRANSACTION;
END CATCH;
END  
GO

spLogin


CREATE PROCEDURE [dbo].[spLogin]
(
@UserName VARCHAR(50),
@Password VARCHAR(50)
)
AS        
BEGIN 
BEGIN TRANSACTION; 
BEGIN TRY
SELECT * FROM Student WHERE UserName = @UserName AND Password = @Password

IF @@TRANCOUNT > 0  
    COMMIT TRANSACTION;  

END TRY
BEGIN CATCH

 DECLARE @ErrorNumber VARCHAR(50),
@ErrorSeverity VARCHAR(50),
@ErrorState VARCHAR(50),
@ErrorProcedure VARCHAR(50),
@ErrorLine VARCHAR(50),
@ErrorMessage VARCHAR(250)

SELECT   
         @ErrorNumber = ERROR_NUMBER() 
        ,@ErrorSeverity = ERROR_SEVERITY()  
        ,@ErrorState = ERROR_STATE()  
        ,@ErrorProcedure = ERROR_PROCEDURE() 
        ,@ErrorLine = ERROR_LINE()  
        ,@ErrorMessage = ERROR_MESSAGE()
EXECUTE dbo.[spAddErrorLog] @ErrorNumber,@ErrorSeverity,@ErrorState,@ErrorProcedure,@ErrorLine,@ErrorMessage;
  
    IF @@TRANCOUNT > 0  
        ROLLBACK TRANSACTION;
END CATCH;
END  
GO

spUpdateStudent


CREATE PROCEDURE [dbo].[spUpdateStudent]            
(            
    @Id INTEGER ,          
    @FirstName VARCHAR(50),           
    @LastName VARCHAR(50),          
    @Email VARCHAR(30),          
    @Mobile VARCHAR(20),  
    @Address VARCHAR(220),
@UserName VARCHAR(50),
@Password VARCHAR(50)
)            
AS            
BEGIN            
   UPDATE Student             
   SET FirstName = @FirstName,            
   LastName = @LastName,            
   Email = @Email,          
   Mobile = @Mobile,   
   Address = @Address,
   UserName = @UserName,
   Password = @Password
   WHERE Id=@Id            
END  
GO

Startup.cs


In the Startup.cs class add CompanyDBContext as a service inside ConfigureService() method as below. We will retrieve the connection string value from appsettings.json file through IConfiguration object's GetConnectionString() method.


using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.HttpsPolicy;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using IHostingEnvironment = Microsoft.AspNetCore.Hosting.IHostingEnvironment;

namespace StudentManagementSystem
{
    public class Startup
    {
        public Startup(IConfiguration configuration)
        {
            Configuration = configuration;
        }
        public Startup(IHostingEnvironment env)
        {
            Configuration = new ConfigurationBuilder().SetBasePath(env.ContentRootPath).AddJsonFile("appSettings.json").Build();
        }
        public IConfiguration Configuration { get; }
        public static string ConnectionString { get; private set; }

        // This method gets called by the runtime. Use this method to add services to the container.
        public void ConfigureServices(IServiceCollection services)
        {
            services.Configure<CookiePolicyOptions>(options =>
            {
                // This lambda determines whether user consent for non-essential cookies is needed for a given request.
                options.CheckConsentNeeded = context => true;
                options.MinimumSameSitePolicy = SameSiteMode.None;
            });


            services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_1);
        }

        // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
        public void Configure(IApplicationBuilder app, IHostingEnvironment env)
        {
            ConnectionString = Configuration["ConnectionStrings:DBConnectionString"];

            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
            }
            else
            {
                app.UseExceptionHandler("/Home/Error");
                app.UseHsts();
            }

            app.UseHttpsRedirection();
            app.UseStaticFiles();
            app.UseCookiePolicy();

            app.UseMvc(routes =>
            {
                routes.MapRoute(
                    name: "default",
                    template: "{controller=Login}/{action=Index}/{id?}");
            });
        }
    }
}

Program.cs

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.
builder.Services.AddControllersWithViews();

var app = builder.Build();

// Configure the HTTP request pipeline.
if (!app.Environment.IsDevelopment())
{
    app.UseExceptionHandler("/Home/Error");
    // The default HSTS value is 30 days. You may want to change this for production scenarios, see https://aka.ms/aspnetcore-hsts.
    app.UseHsts();
}

app.UseHttpsRedirection();
app.UseStaticFiles();

app.UseRouting();

app.UseAuthorization();

app.MapControllerRoute(
    name: "default",
    pattern: "{controller=Login}/{action=Index}/{id?}");

app.Run();

appsettings.json

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "DBConnectionString": "Server=.;Database=StudentManagement;Integrated Security=True;"
  }
}


Models

StudentModel.cs


using System.ComponentModel.DataAnnotations;

namespace StudentManagementSystem.Models
{
    public class StudentModel
    {
        public int Id { set; get; }
        [Required]
        public string FirstName { set; get; }
        [Required]
        public string LastName { set; get; }
        [Required]
        public string Email { set; get; }
        [Required]
        public string Mobile { set; get; }
        public string Address { set; get; }
        [Required]
        public string UserName { get; set; }
        [Required]
        [DataType(DataType.Password)]
        public string Password { set; get; }
        [Required]
        [DataType(DataType.Password)]
        [Compare("Password")]
        public string ConfirmPassword { get; set; }
    }
}

ErrorViewModel.cs

namespace StudentManagementSystem.Models
{
    public class ErrorViewModel
    {
        public string? RequestId { get; set; }

        public bool ShowRequestId => !string.IsNullOrEmpty(RequestId);
    }
}

Controllers

StudentController.cs


using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using SMS.DAL;
using SMS.Entity;
using StudentManagementSystem.Models;

namespace StudentManagementSystem.Controllers
{
    public class StudentController : Controller
    {
        StudentDataAccessLayer studentDataAccessLayer = null;
        public StudentController()
        {
            studentDataAccessLayer = new StudentDataAccessLayer();
        }

        // GET: Student
        public ActionResult Index()
        {
            IEnumerable<StudentModel> students = studentDataAccessLayer.GetAllStudent().Select(stud=>new StudentModel ()
            { 
                Id=stud.Id,
                Mobile=stud.Mobile,
                Address=stud.Address,
                Email=stud.Email,
                LastName=stud.LastName,
                FirstName=stud.FirstName
            }).ToList();
            return View(students);
        }

        // GET: Student/Details/5
        public ActionResult Details(int id)
        {
            var student = studentDataAccessLayer.GetStudentData(id);
            StudentModel studentDetail = new StudentModel();
            studentDetail.Id = student.Id;
            studentDetail.Address = student.Address;
            studentDetail.FirstName = student.FirstName;
            studentDetail.LastName = student.LastName;
            studentDetail.Mobile = student.Mobile;
            studentDetail.Email= student.Email;
            return View(studentDetail);

        }

        // GET: Student/Create
        public ActionResult Create()
        {
            return View();
        }

        // POST: Student/Create
        [HttpPost]
        [ValidateAntiForgeryToken]
        public ActionResult Create(StudentModel student)
        {
            try
            {
                // TODO: Add insert logic here
                Student objStudent = new Student();
                objStudent.Email = student.Email;
                objStudent.Address = student.Address;
                objStudent.FirstName = student.FirstName;
                objStudent.LastName = student.LastName;
                objStudent.Mobile = student.Mobile;
                objStudent.Id = student.Id;
                studentDataAccessLayer.AddStudent(objStudent);

                return RedirectToAction(nameof(Index));
            }
            catch (Exception ex)
            {
                return View();
            }
        }

        // GET: Student/Edit/5
        public ActionResult Edit(int id)
        {           
            var student = studentDataAccessLayer.GetStudentData(id);
            StudentModel studentDetail = new StudentModel();
            studentDetail.Id = student.Id;
            studentDetail.Address = student.Address;
            studentDetail.FirstName = student.FirstName;
            studentDetail.LastName = student.LastName;
            studentDetail.Mobile = student.Mobile;
            studentDetail.Email = student.Email;
            return View(studentDetail);
        }

        // POST: Student/Edit/5
        [HttpPost]
        [ValidateAntiForgeryToken]
        public ActionResult Edit(StudentModel student)
        {
            try
            {
                // TODO: Add update logic here
                Student objStudent = new Student();
                objStudent.Email = student.Email;
                objStudent.Address = student.Address;
                objStudent.FirstName = student.FirstName;
                objStudent.LastName = student.LastName;
                objStudent.Mobile = student.Mobile;
                objStudent.Id = student.Id;
                studentDataAccessLayer.UpdateStudent(objStudent);
                return RedirectToAction(nameof(Index));
            }
            catch
            {
                return View();
            }
        }

        // GET: Student/Delete/5
        public ActionResult Delete(int id)
        {
            var student = studentDataAccessLayer.GetStudentData(id);
            StudentModel studentDetail = new StudentModel();
            studentDetail.Id = student.Id;
            studentDetail.Address = student.Address;
            studentDetail.FirstName = student.FirstName;
            studentDetail.LastName = student.LastName;
            studentDetail.Mobile = student.Mobile;
            return View(studentDetail);
        }

        // POST: Student/Delete/5
        [HttpPost]
        [ValidateAntiForgeryToken]
        public ActionResult Delete(StudentModel student)
        {
            try
            {
                // TODO: Add delete logic here
                studentDataAccessLayer.DeleteStudent(student.Id);
                return RedirectToAction(nameof(Index));
            }
            catch
            {
                return View();
            }
        }
    }
}

LoginController.cs


using Microsoft.AspNetCore.Mvc;
using SMS.DAL;
using SMS.Entity;
using StudentManagementSystem.Models;

namespace StudentManagementSystem.Controllers
{
    public class LoginController : Controller
    {
        StudentDataAccessLayer studentDataAccessLayer = null;
        public LoginController()
        {
            studentDataAccessLayer = new StudentDataAccessLayer();
        }

        public IActionResult Index()
        {
            return View();
        }

        [HttpPost]
        public IActionResult Index([Bind] StudentModel student)
        {
            Student objStudent = new Student();
            objStudent.UserName = student.UserName;
            objStudent.Password = student.Password;

            int res = studentDataAccessLayer.LoginCheck(objStudent);
            if (res == 1)
            {
                TempData["msg"] = "You are welcome";
                return RedirectToAction("Index", "Student");
            }
            else
            {
                TempData["msg"] = "UserName or Password is wrong.!";
            }
            return View();
        }
    }
}

Views - Two Folders

Student - Folder

Create.cshtml

@model StudentManagementSystem.Models.StudentModel

@{
    ViewData["Title"] = "Create";
}

<h2>Create</h2>

<h4>Student</h4>
<hr />
<div class="row">
    <div class="col-md-4">
        <form asp-action="Create">
            <div asp-validation-summary="ModelOnly" class="text-danger"></div>
            <div class="form-group">
                <label asp-for="Id" class="control-label"></label>
                <input asp-for="Id" class="form-control" />
                <span asp-validation-for="Id" class="text-danger"></span>
            </div>
            <div class="form-group">
                <label asp-for="FirstName" class="control-label"></label>
                <input asp-for="FirstName" class="form-control" />
                <span asp-validation-for="FirstName" class="text-danger"></span>
            </div>
            <div class="form-group">
                <label asp-for="LastName" class="control-label"></label>
                <input asp-for="LastName" class="form-control" />
                <span asp-validation-for="LastName" class="text-danger"></span>
            </div>
            <div class="form-group">
                <label asp-for="Email" class="control-label"></label>
                <input asp-for="Email" class="form-control" />
                <span asp-validation-for="Email" class="text-danger"></span>
            </div>
            <div class="form-group">
                <label asp-for="Mobile" class="control-label"></label>
                <input asp-for="Mobile" class="form-control" />
                <span asp-validation-for="Mobile" class="text-danger"></span>
            </div>
            <div class="form-group">
                <label asp-for="Address" class="control-label"></label>
                <input asp-for="Address" class="form-control" />
                <span asp-validation-for="Address" class="text-danger"></span>
            </div>
             <div class="form-group">
                <label asp-for="UserName" class="control-label"></label>
                <input asp-for="UserName" class="form-control" />
                <span asp-validation-for="UserName" class="text-danger"></span>
            </div>
             <div class="form-group">
                <label asp-for="Password" class="control-label"></label>
                <input asp-for="Password" type="password" class="form-control" />
                <span asp-validation-for="Password" class="text-danger"></span>
            </div>
             <div class="form-group">
                <label asp-for="ConfirmPassword" class="control-label"></label>
                <input asp-for="ConfirmPassword" type="password" class="form-control" />
                <span asp-validation-for="ConfirmPassword" class="text-danger"></span>
            </div>
            <div class="form-group">
                <input type="submit" value="Create" class="btn btn-default" />
            </div>
        </form>
    </div>
</div>

<div>
    <a asp-action="Index">Back to List</a>
</div>

@section Scripts {
    @{await Html.RenderPartialAsync("_ValidationScriptsPartial");}
}

Delete.cshtml

@model StudentManagementSystem.Models.StudentModel

@{
    ViewData["Title"] = "Delete";
}

<h2>Delete</h2>

<h3>Are you sure you want to delete this?</h3>
<div>
    <h4>Student</h4>
    <hr />
    <dl class="dl-horizontal">
        <dt>
            @Html.DisplayNameFor(model => model.Id)
        </dt>
        <dd>
            @Html.DisplayFor(model => model.Id)
        </dd>
        <dt>
            @Html.DisplayNameFor(model => model.FirstName)
        </dt>
        <dd>
            @Html.DisplayFor(model => model.FirstName)
        </dd>
        <dt>
            @Html.DisplayNameFor(model => model.LastName)
        </dt>
        <dd>
            @Html.DisplayFor(model => model.LastName)
        </dd>
        <dt>
            @Html.DisplayNameFor(model => model.Email)
        </dt>
        <dd>
            @Html.DisplayFor(model => model.Email)
        </dd>
        <dt>
            @Html.DisplayNameFor(model => model.Mobile)
        </dt>
        <dd>
            @Html.DisplayFor(model => model.Mobile)
        </dd>
        <dt>
            @Html.DisplayNameFor(model => model.Address)
        </dt>
        <dd>
            @Html.DisplayFor(model => model.Address)
        </dd>
    </dl>
    
    <form asp-action="Delete">
        <input type="submit" value="Delete" class="btn btn-default" /> |
        <a asp-action="Index">Back to List</a>
    </form>
</div>

Details.cshtml

@model StudentManagementSystem.Models.StudentModel

@{
    ViewData["Title"] = "Details";
}

<h2>Details</h2>

<div>
    <h4>Student</h4>
    <hr />
    <dl class="dl-horizontal">
        <dt>
            @Html.DisplayNameFor(model => model.Id)
        </dt>
        <dd>
            @Html.DisplayFor(model => model.Id)
        </dd>
        <dt>
            @Html.DisplayNameFor(model => model.FirstName)
        </dt>
        <dd>
            @Html.DisplayFor(model => model.FirstName)
        </dd>
        <dt>
            @Html.DisplayNameFor(model => model.LastName)
        </dt>
        <dd>
            @Html.DisplayFor(model => model.LastName)
        </dd>
        <dt>
            @Html.DisplayNameFor(model => model.Email)
        </dt>
        <dd>
            @Html.DisplayFor(model => model.Email)
        </dd>
        <dt>
            @Html.DisplayNameFor(model => model.Mobile)
        </dt>
        <dd>
            @Html.DisplayFor(model => model.Mobile)
        </dd>
        <dt>
            @Html.DisplayNameFor(model => model.Address)
        </dt>
        <dd>
            @Html.DisplayFor(model => model.Address)
        </dd>
    </dl>
</div>
<div>
    @Html.ActionLink("Edit", "Edit", new {  id = Model.Id  }) |
    <a asp-action="Index">Back to List</a>
</div>

Edit.cshtml

@model StudentManagementSystem.Models.StudentModel

@{
    ViewData["Title"] = "Edit";
}

<h2>Edit</h2>

<h4>Student</h4>
<hr />
<div class="row">
    <div class="col-md-4">
        <form asp-action="Edit">
            <div asp-validation-summary="ModelOnly" class="text-danger"></div>
            <div class="form-group">
                <label asp-for="Id" class="control-label"></label>
                <input asp-for="Id" class="form-control" readonly/>
                <span asp-validation-for="Id" class="text-danger"></span>
            </div>
            <div class="form-group">
                <label asp-for="FirstName" class="control-label"></label>
                <input asp-for="FirstName" class="form-control" />
                <span asp-validation-for="FirstName" class="text-danger"></span>
            </div>
            <div class="form-group">
                <label asp-for="LastName" class="control-label"></label>
                <input asp-for="LastName" class="form-control" />
                <span asp-validation-for="LastName" class="text-danger"></span>
            </div>
            <div class="form-group">
                <label asp-for="Email" class="control-label"></label>
                <input asp-for="Email" class="form-control" />
                <span asp-validation-for="Email" class="text-danger"></span>
            </div>
            <div class="form-group">
                <label asp-for="Mobile" class="control-label"></label>
                <input asp-for="Mobile" class="form-control" />
                <span asp-validation-for="Mobile" class="text-danger"></span>
            </div>
            <div class="form-group">
                <label asp-for="Address" class="control-label"></label>
                <input asp-for="Address" class="form-control" />
                <span asp-validation-for="Address" class="text-danger"></span>
            </div>
            <div class="form-group">
                <input type="submit" value="Update" class="btn btn-default" />
            </div>
        </form>
    </div>
</div>

<div>
    <a asp-action="Index">Back to List</a>
</div>

@section Scripts {
    @{await Html.RenderPartialAsync("_ValidationScriptsPartial");}
}

Index.cshtml

@model IEnumerable<StudentManagementSystem.Models.StudentModel>

@{
    ViewData["Title"] = "Index";
}

<h2>Index</h2>

<p>
    <a asp-action="Create">Create New</a>
</p>
<table class="table">
    <thead>
        <tr>
            <th>
                @Html.DisplayNameFor(model => model.Id)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.FirstName)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.LastName)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.Email)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.Mobile)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.Address)
            </th>
            <th></th>
        </tr>
    </thead>
    <tbody>
@foreach (var item in Model) {
        <tr>
            <td>
                @Html.DisplayFor(modelItem => item.Id)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.FirstName)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.LastName)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.Email)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.Mobile)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.Address)
            </td>
            <td>
                @Html.ActionLink("Edit", "Edit", new { id=item.Id  }) |
                @Html.ActionLink("Details", "Details", new {  id=item.Id }) |
                @Html.ActionLink("Delete", "Delete", new {  id=item.Id  })
            </td>
        </tr>
}
    </tbody>
</table>

Login - Folder

Index.cshtml

@model StudentManagementSystem.Models.StudentModel

@{
    ViewData["Title"] = "Login";
}

<h2>Index</h2>

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Index</title>
</head>
<body>
    <div class="container">
        <div class="row">
            <div class="col-sm-4"></div>
            <div class="col-sm-4">
                <form class="form-horizontal" asp-action="Index">
                    <div class="form-group">
                        <label asp-for="UserName" class="control-label"></label>
                        <input asp-for="UserName" class="form-control" />
                    </div>
                    <div class="form-group">
                        <label asp-for="Password" class="control-label"></label>
                       <input asp-for="Password" type="password" class="form-control" />
                    </div>
                    <div class="form-group">
                        <input type="submit" value="Submit" />
                    </div>
                </form>
            </div>
            <div class="col-sm-4"></div>
        </div>
    </div>
</body>
</html>
@{
    if (TempData["msg"]!=null)
    {
        <script>
            alert('@TempData["msg"]')
        </script>
    }
}

Create Data Access Layer Class Library Project and add class
















AppConfiguration.cs

using Microsoft.Extensions.Configuration;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SMS.DAL.AppConfig
{
    public class AppConfiguration
    {
        public readonly string _connectionString = string.Empty;
        public AppConfiguration()
        {
            var configurationBuilder = new ConfigurationBuilder();
            var path = Path.Combine(Directory.GetCurrentDirectory(), "appsettings.json");
            configurationBuilder.AddJsonFile(path, false);

            var root = configurationBuilder.Build();
            _connectionString = root.GetSection("ConnectionStrings").GetSection("DBConnectionString").Value;
            var appSetting = root.GetSection("ApplicationSettings");
        }
        public string ConnectionString
        {
            get => _connectionString;
        }

    }

}

DALRepository.cs


using SMS.DAL.Common;
using ObjectExtensions;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Extensions.Configuration;
using SMS.DAL.AppConfig;

namespace SMS.DAL
{
    public class EntityRowCount
    {
        public int Count { get; set; }
    }

    // Author: jms
    /// <summary>
    /// Implements the data access layer onject. This class uses generic types, and therefore 
    /// assumes/requires that the following components match EXACTLY (case and spelling):
    ///     - Entity property names (in your app)
    ///     - Stored proc parameter names (in the database)
    ///     - Table column names (in the database)
    /// It is the job of the programmer to use transactions (ostensibly for setting data) in his
    /// stored procs if that behavior is desired. For text queries (which AEF code should not use),
    /// include all transaction related code (begin/end try, begin/end catch in your query.
    /// </summary>
    public partial class DALRepository
    {

        #region properties

        private IConfiguration _config;

        /// <summary>
        /// Get/set flag indicating whether the List&lt;T&gt; ExecuteXXX&lt;T&gt;() methods should <br/>
        /// throw an exception if the DataTable retrieved by the query does not match the model <br/>
        /// being created (it compares the number of datatable columns with the number of assigned <br/>
        /// values in the model). The default falue is false.
        /// </summary>
        public bool FailOnMismatch { get; set; }
        /// <summary>
        /// Get/set value indicating the SqlCommand object's timeout value (in seconds)
        /// </summary>
        public int TimeoutSecs { get; set; }
        /// <summary>
        /// Get/(protected) set the connection string.
        /// </summary>
        public string ConnectionString { get; protected set; }
        /// <summary>
        /// Get/set a flag indicating whether a return value parameter is added to the sql <br/>
        /// parameter list if it's missing. This only applies to the SetData method <br/>
        /// (insert/update/delete functionality). In order for this to work, you MUST return <br/>
        /// @@ROWCOUNT from your stored proc.
        /// </summary>
        public bool AddReturnParamIfMissing { get; set; }
        /// <summary>
        /// Get/set the bulk insert batch size
        /// </summary>
        public int BulkInsertBatchSize { get; set; }
        /// <summary>
        /// Get/set the number of seconds before the bulk copy times out
        /// </summary>
        protected int BulkCopyTimeout { get; set; }
        /// <summary>
        /// Get/set options flag for SqlBulkCopy operations
        /// </summary>
        protected SqlBulkCopyOptions BulkCopyOptions { get; set; }
        /// <summary>
        /// Get/set the external transaction that can be set for/used by SqlBlukCopy.
        /// </summary>
        protected SqlTransaction ExternalTransaction { get; set; }

        /// <summary>
        /// Get/set a flag indicating whether the database can be accessed from <br/>
        /// the GetData or SetData methods. Allows debugging of BLL without <br/>
        /// actually reading from or writing to the database. Sefault value <br/>
        /// is true.
        /// </summary>
        public bool CanReadWriteDB { get; set; }

        #endregion properties

        #region constructors
        /// <summary>
        /// Create instance of DALRepository, and set default values for properties.
        /// </summary>
        public DALRepository()
        {
            // this connection string *should* be base64 encoded
            //this.ConnectionString = System.Configuration.ConfigurationSettings.AppSettings["DBConnectionString"];
            //string connString = this._config.GetConnectionString("DBConnectionString");
            AppConfiguration objAppConfiguration = new AppConfiguration();
            this.ConnectionString = objAppConfiguration.ConnectionString;
            this.Init();
        }
        #endregion constructors

        #region protected data access methods
        /// <summary>
        /// Executes the named stored proc (using ExecuteReader) that gets data from the database. <br/> 
        /// </summary>
        /// <typeparam name="T">The type of the list item</typeparam>
        /// <param name="storedProc">The name of the stored procedure to execute</param>
        /// <param name="parameters">The parameters to pass to the stored procedure</param>
        /// <returns>A list of the specified object type (may be empty).</returns>
        /// <remarks>Useage: List&lt;MyObject&gt; list = this.ExecuteStoredProc&lt;MyObject&gt;(...)</remarks>
        /// <exception cref="ArgumentNullException">The storedProc parameter cannot be null/empty.</exception>
        public virtual List<T> ExecuteStoredProc<T>(string storedProc, SqlParameter[] parameters)
        {
            if (string.IsNullOrEmpty(storedProc))
            {
                throw new ArgumentNullException("storedProc");
            }
            // get the data from the database
            DataTable data = this.GetData(storedProc, parameters, CommandType.StoredProcedure);
            List<T> collection = this.MakeEntityFromDataTable<T>(data);
            return collection;
        }

        /// <summary>
        /// Executes the specified stored proc (using ExecuteNonQuery) that stores data (specified <br/>
        /// in the [parameters] parameter) in the database. 
        /// </summary>
        /// <param name="storedProc">The stored proc to execute</param>
        /// <param name="parameters">The parameters to pass to the stored procedure</param>
        /// <returns>The number of records affected</returns>
        /// <exception cref="ArgumentNullException">The [storedProc] parameter cannot be null/empty.</exception>
        /// <exception cref="ArgumentNullException">The [parameters] array parameter cannot be null.</exception>
        /// <exception cref="ArgumentNullException">The [parameters] array parameter cannot be empty.</exception>
        public virtual int ExecuteStoredProc(string storedProc, SqlParameter[] parameters)
        {
            if (string.IsNullOrEmpty(storedProc))
            {
                throw new ArgumentNullException("storedProc");
            }
            if (parameters == null)
            {
                throw new ArgumentNullException("parameters");
            }
            if (parameters.Length == 0)
            {
                throw new InvalidOperationException("The [parameters] array must contain at least one item.");
            }
            // You must SET NOCOUNT OFF at the top of your stored proc in order to automatically 
            // return the @@ROWCOUNT value (created and set by sql server).
            int result = this.SetData(storedProc, parameters, CommandType.StoredProcedure);

            return result;
        }

        /// <summary>
        /// Execute the specified stored proc to save the specified data item in the database.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="data">The data item to save</param>
        /// <param name="storedProc">The stored proc to execute</param>
        /// <param name="bulkType">How to build the SqlParameter array</param>
        /// <param name="precedence">Whether to ignore the discovered SqlParameter[] property</param>
        /// <param name="paramArrayPropName">Name of desired SqlParameter[] property</param>
        /// <returns>The number of records affected</returns>
        public virtual int ExecuteStoredProc<T>(T data,
                                                   string storedProc,
                                                   BulkInsertType bulkType,
                                                   ParamPrecedence precedence = ParamPrecedence.None,
                                                   string paramArrayPropName = "SqlParameters")
        {
            int result = 0;
            SqlParameter[] parameters = DALRepository.MakeSqlParameters(data, bulkType, precedence, paramArrayPropName);
            result = this.ExecuteStoredProc(storedProc, parameters);
            return result;
        }

        /// <summary>
        /// Executes the specified stored proc for an entity collection, using a persistent <br/>
        /// sql connection. This is intended to be used for data that needs to be merged <br/>
        /// (update or insert) into a table rather than simply inserted.
        /// </summary>
        /// <typeparam name="T">The type of entity represented by the specified collection</typeparam>
        /// <param name="data">The collection of entities</param>
        /// <param name="storedProc">The name of the stored proc (must be a stored proc)</param>
        /// <param name="bulkType">Indicates how properties should be retrieved from the entity items</param>
        /// <param name="precedence">Whether to ignore the discovered SqlParameter[] property</param>
        /// <param name="paramArrayPropName">Name of desired SqlParameter[] property</param>
        /// <exception cref="ArgumentNullException">If the data parameter is null</exception>
        /// <exception cref="ArgumentNullException">If the storedProc parameter is null/empty</exception>
        /// <exception cref="InvalidOperationException">If the data collection is empty.</exception>
        /// <returns>The number of records affected (inserted + updated).</returns>
        /// <remarks>Usage:  int result = ExecuteStoredProc(data.AsEnumerable(), "dbo.MyStoredProc", BulkInsertType.DBInsertAttribute);</remarks>
        protected virtual int ExecuteStoredProc<T>(IEnumerable<T> data,
                                                   string storedProc,
                                                   BulkInsertType bulkType,
                                                   ParamPrecedence precedence = ParamPrecedence.None,
                                                   string paramArrayPropName = "SqlParameters")
        {
            if (string.IsNullOrEmpty(storedProc))
            {
                throw new ArgumentNullException("storedProc");
            }
            if (data == null)
            {
                throw new ArgumentNullException("data");
            }
            if (data.Count() == 0)
            {
                throw new InvalidOperationException("Data collection must contain at least onme item");
            }

            int result = this.DoBulkMerge(data, storedProc, bulkType, CommandType.StoredProcedure, precedence, paramArrayPropName);
            return result;
        }

        /// <summary>
        /// Executes the specified query (using ExecuteReader) that gets data from the database.
        /// </summary>
        /// <typeparam name="T">The type of the list item</typeparam>
        /// <param name="query">The query text to execute</param>
        /// <param name="parameters">The data to pass to the query text</param>
        /// <returns>A list of the specified type.</returns>
        /// <remarks>Useage: List&lt;MyObject&gt; list = this.ExecuteStoredProc&lt;MyObject&gt;(...)</remarks>
        /// <exception cref="ArgumentNullException">If the query parameter is null/empty</exception>
        protected virtual List<T> ExecuteQuery<T>(string query, params SqlParameter[] parameters)
        {
            // If you have questions regarding the use of parameters in query text, google 
            // "c# parameterized queries". In short, parameterized queries prevent sql 
            // injection. This code does not (and cannot) validate the use of parameters 
            // because some queries simply don't need them. Therefore, it's completely 
            // on you - the developer - to make sure you're doing it right.

            if (string.IsNullOrEmpty(query))
            {
                throw new ArgumentNullException("query");
            }
            DataTable data = this.GetData(query, parameters, CommandType.Text);
            List<T> collection = this.MakeEntityFromDataTable<T>(data);
            return collection;
        }

        /// <summary>
        /// Executes the specified query text (using ExecuteNonQuery) that stores data in the <br/>
        /// database. 
        /// </summary>
        /// <param name="query"></param>
        /// <param name="parameters"></param>
        /// <returns>The number of records affected (if you didn't use SET NOCOUNT ON in 
        /// your batch)</returns>
        /// <exception cref="ArgumentNullException">If the query parameter is null/empty</exception>
        protected virtual int ExecuteQuery(string query, params SqlParameter[] parameters)
        {
            // If you have questions regarding the use of parameters in query text, google 
            // "c# parameterized queries". In short, parameterized queries prevent sql 
            // injection. This code does not (and cannot) validate the use of parameters 
            // because some queries simply don't need them. Therefore, it's completely 
            // on you - the developer - to make sure you're doing it right.
            if (string.IsNullOrEmpty(query))
            {
                throw new ArgumentNullException("query");
            }
            // Save the data to the database. If you use SET NOCOUNT ON in your query, the return 
            // value will always be -1, regardless of how many rows are actually affected.
            int result = this.SetData(query, parameters, CommandType.Text);
            return result;
        }

        /// <summary>
        /// Execute the specified sql query to save the specified data item. This method creates the <br/>
        /// parameters for you using the properties in the specified entity. While not required, you <br/>
        /// can "grease the skids" by implementing a public property in your entities that returns a <br/>
        /// SqlParameter[] array.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="data">The data item to save</param>
        /// <param name="query">The sql query text to execute</param>
        /// <param name="bulkType">Indicates how to build the SqlParameter array</param>
        /// <param name="precedence">Whether to ignore the discovered SqlParameter[] property</param>
        /// <param name="paramArrayPropName">Name of desired SqlParameter[] property</param>
        /// <returns>The number of records affected.</returns>
        protected virtual int ExecuteQuery<T>(T data,
                                              string query,
                                              BulkInsertType bulkType,
                                              ParamPrecedence precedence = ParamPrecedence.None,
                                              string paramArrayPropName = "SqlParameters")
        {
            int result = 0;
            SqlParameter[] parameters = DALRepository.MakeSqlParameters(data, bulkType);
            result = this.ExecuteQuery(query, parameters);
            return result;
        }

        /// <summary>
        /// Performs an insert of an entity collection, using a persistent connection (reduces <br/>
        /// processing time and memory consumption because we're not opening/closing a database <br/>
        /// connection for every item). This method is intended to be used for data that needs <br/>
        /// to be merged (update or insert) into a table rather than simply inserted.
        /// </summary>
        /// <typeparam name="T">The type of entity represented by the specified collection</typeparam>
        /// <param name="data">The collection of entities</param>
        /// <param name="storedProc">The name of the stored proc (must be a stored proc)</param>
        /// <param name="bulkType">Indicates how properties should be retrieved from the entity items</param>
        /// <param name="precedence">Whether to ignore the discovered SqlParameter[] property</param>
        /// <param name="paramArrayPropName">Name of desired SqlParameter[] property</param>
        /// <exception cref="ArgumentNullException">If the data parameter is null</exception>
        /// <exception cref="ArgumentNullException">If the query parameter is null/empty</exception>
        /// <exception cref="InvalidOperationException">If the data collection is empty.</exception>
        /// <returns>The number of records affected.</returns>
        protected virtual int ExecuteQuery<T>(IEnumerable<T> data,
                                              string query,
                                              BulkInsertType bulkType,
                                              ParamPrecedence precedence = ParamPrecedence.None,
                                              string paramArrayPropName = "SqlParameters")
        {
            if (string.IsNullOrEmpty(query))
            {
                throw new ArgumentNullException("query");
            }
            if (data == null)
            {
                throw new ArgumentNullException("data");
            }
            if (data.Count() == 0)
            {
                throw new InvalidOperationException("Data collection must contain at least onme item");
            }
            int result = this.DoBulkMerge(data, query, bulkType, CommandType.Text, precedence, paramArrayPropName);
            return result;
        }

        /// <summary>
        /// Performs a simply bulk insert into a table in the database. The schema MUST be part of <br/>
        /// the table name. Using a bulk insert is NOT suitable if you need to merge data into an <br/>
        /// existing table. Use the BulkMerge stored proc instead.
        /// </summary>
        /// <param name="dataTable">The datatable to bulk copy</param>
        /// <returns>The number of records affected.</returns>
        /// <exception cref="InvalidOperationException">If the table name hasn't been specified in the datatable</exception>
        /// <exception cref="InvalidOperationException">If the schema hasn't been specified as part of the tablename</exception>
        /// <exception cref="InvalidOperationException">If the dataTable is empty</exception>
        protected virtual int ExecuteBulkInsert(DataTable dataTable)
        {
            if (string.IsNullOrEmpty(dataTable.TableName))
            {
                throw new InvalidOperationException("The table name MUST be specified in the datatable (including the schema).");
            }
            if (!dataTable.TableName.Contains('.') || dataTable.TableName.StartsWith("."))
            {
                throw new InvalidOperationException("The schema MUST be specified with the table name.");
            }
            if (dataTable.Rows.Count == 0)
            {
                throw new InvalidOperationException("The dataTable must contain at least one item");
            }

            int recsBefore = this.BulkInsertTargetCount(dataTable.TableName);

            int recordsAffected = 0;
            SqlConnection conn = null;
            SqlBulkCopy bulk = null;

            using (conn = new SqlConnection(this.ConnectionString.Base64Decode()))
            {
                conn.Open();
                using (bulk = new SqlBulkCopy(conn, this.BulkCopyOptions, this.ExternalTransaction)
                {
                    BatchSize = this.BulkInsertBatchSize
                    ,
                    BulkCopyTimeout = this.BulkCopyTimeout
                    ,
                    DestinationTableName = dataTable.TableName
                })
                {
                    Debug.WriteLine("DoBulkInsert - inserting {0} rows", dataTable.Rows.Count);
                    bulk.WriteToServer(dataTable);
                }
            }

            int recsAfter = this.BulkInsertTargetCount(dataTable.TableName);
            recordsAffected = recsAfter - recsBefore;
            return recordsAffected;
        }

        /// <summary>
        /// Performs a simple bulk insert into a table in the database. The schema MUST be part of <br/>
        /// the table name.
        /// </summary>
        /// <typeparam name="T">The entity type being bulk inserted</typeparam>
        /// <param name="data">The list of entities to be inserted</param>
        /// <param name="tableName">The table name in which to insert the data</param>
        /// <param name="bulkType">Indicates how properties should be retrieved from the entity items</param>
        /// <param name="precedence">Whether to ignore the discovered SqlParameter[] property</param>
        /// <param name="paramArrayPropName">Name of desired SqlParameter[] property</param>
        /// <returns>Number of records affected.</returns>
        /// <exception cref="ArgumentNullException">If the data parameter is null.</exception>
        /// <exception cref="ArgumentNullException">If The tableName parameter cannot be null/empty.</exception>
        /// <exception cref="InvalidOperationException">If the data collection is empty.</exception>
        /// <exception cref="InvalidOperationException">If the table name doesn't include the schema.</exception>
        protected virtual int ExecuteBulkInsert<T>(IEnumerable<T> data,
                                              string tableName,
                                              BulkInsertType bulkType,
                                              ParamPrecedence precedence = ParamPrecedence.None,
                                              string paramArrayPropName = "SqlParameters")
        {
            // sanity checks
            if (data == null)
            {
                throw new ArgumentNullException("data");
            }
            if (data.Count() == 0)
            {
                throw new InvalidOperationException("The data collection must contain at least one item");
            }
            if (string.IsNullOrEmpty(tableName))
            {
                throw new ArgumentNullException("The tableName parameter cannot be null or empty.");
            }
            if (!tableName.Contains('.'))
            {
                throw new InvalidOperationException("The schema MUST be specified with the table name.");
            }

            int result = 0;
            DataTable dataTable = null;

            if (data.Count() > 0)
            {
                dataTable = this.MakeDataTable(data, tableName, bulkType, precedence, paramArrayPropName);
                result = this.ExecuteBulkInsert(dataTable);
            }
            return result;
        }


        #endregion protected data access methods

        #region Protected helper methods

        /// <summary>
        /// Set some reasonable defaults
        /// </summary>
        protected virtual void Init()
        {
            // this method is called from the constructor(s), and exists so that we can overload 
            // the constructor without duplicating code. 
            this.TimeoutSecs = 300;
            this.FailOnMismatch = false;
            this.AddReturnParamIfMissing = true;
            this.ExternalTransaction = null;
            this.BulkInsertBatchSize = 250;
            this.BulkCopyTimeout = 600;
            this.BulkCopyOptions = SqlBulkCopyOptions.Default;
            this.CanReadWriteDB = true;
        }

        /// <summary>
        /// Allows the programmer to test the database connection before trying to use it.
        /// </summary>
        /// <returns>If valid and empty string, otherwise, the message text from the ensuing <br/>exception.</returns>
        protected virtual string TestConnection()
        {
            // assume success
            string result = string.Empty;
            SqlConnection conn = null;
            try
            {
                using (conn = new SqlConnection(this.ConnectionString.Base64Decode()))
                {
                    conn.Open();
                    conn.Close();
                }
            }
            catch (Exception ex)
            {
                result = ex.Message;
            }
            return result;
        }

        /// <summary>
        /// Adds a try/catch block, as well as a transaction (with optional name) to the specified <br/>
        /// plain sql query text. This code does not checkt to see if transaction code is already <br/>
        /// part of the query.
        /// </summary>
        /// <param name="query">The query to encase</param>
        /// <param name="logQuery">The query that implements your logging mechanism.</param>
        /// <param name="transactionName">The desired transaction name (optional)</param>
        /// <returns></returns>
        protected virtual string AddTryCatchTranToQuery(string query, string logQuery, string transactionName = "")
        {
            transactionName = transactionName.Trim();
            logQuery = logQuery.Trim();

            StringBuilder text = new StringBuilder();
            text.AppendLine("BEGIN TRY");
            text.AppendFormat("    BEGIN TRAN {0};", transactionName).AppendLine();
            text.AppendLine(query).AppendLine();
            text.AppendFormat("    COMMIT TRAN {0};", transactionName).AppendLine();
            text.AppendLine("END TRY");
            text.AppendLine("BEGIN CATCH");
            text.AppendFormat("    IF @@TRANCOUNT > 0 ROLLBACK TRAN {0};", transactionName).AppendLine();
            text.AppendLine(logQuery);
            text.AppendLine("END CATCH");
            return text.ToString();
        }

        /// <summary>
        /// Normalizes the table name so there's no chance of sql injection. You can't be too careful.
        /// </summary>
        /// <param name="tableName">The table name (should have the schema included as well.</param>
        /// <returns>The tablename with square brackets around it</returns>
        protected virtual string NormalizeTableName(string tableName)
        {
            string[] parts = tableName.Split('.');
            tableName = string.Empty;
            foreach (string part in parts)
            {
                tableName = (string.IsNullOrEmpty(tableName))
                            ? string.Format("[{0}]", part)
                            : string.Format(".[{0}]", part);
            }
            return tableName.Replace("[[", "[").Replace("]]", "]");
        }

        /// <summary>
        /// Counts the number of records currently in the tarble targeted by the merge attempt.
        /// </summary>
        /// <param name="tableName">The table we're counting</param>
        /// <returns>The number of records in the specified table</returns>
        protected virtual int BulkInsertTargetCount(string tableName)
        {
            if (string.IsNullOrEmpty(tableName))
            {
                throw new ArgumentNullException("tableName");
            }
            if (!tableName.Contains('.') || tableName.StartsWith("."))
            {
                throw new InvalidOperationException("The [tableName] must include a schema. Example: 'dbo.tableName'");
            }

            int result = 0;
            string query = string.Format("SELECT COUNT(1) FROM {0}", this.NormalizeTableName(tableName));
            List<EntityRowCount> rowCount = this.ExecuteQuery<EntityRowCount>(query);
            if (rowCount != null && rowCount.Count > 0)
            {
                result = rowCount[0].Count;
            }
            return result;
        }

        /// <summary>
        /// Calls SqlCommand.ExecuteDataReader() to retrieve a dataset from the database.
        /// </summary>
        /// <param name="cmdText">The storedproc or query to execute</param>
        /// <param name="parameters">The parameters to use in the storedproc/query</param>
        /// <returns></returns>
        /// <exception cref="ArgumentNullException">The cmdText parameter cannot be null/empty.</exception>
        protected virtual DataTable GetData(string cmdText,
                                            SqlParameter[] parameters = null,
                                            CommandType cmdType = CommandType.StoredProcedure)
        {
            if (string.IsNullOrEmpty(cmdText))
            {
                throw new ArgumentNullException("cmdText");
            }

            //------------------------

            // by defining these variables OUTSIDE the using statements, we can evaluate them in 
            // the debugger even when the using's go out of scope.
            SqlConnection conn = null;
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            DataTable data = null;

            // create the connection
            using (conn = new SqlConnection(this.ConnectionString.Base64Decode()))
            {
                // open it
                conn.Open();
                // create the SqlCommand object
                using (cmd = new SqlCommand(cmdText, conn) { CommandTimeout = this.TimeoutSecs, CommandType = cmdType })
                {
                    // give the SqlCommand object the parameters required for the stored proc/query
                    if (parameters != null)
                    {
                        cmd.Parameters.AddRange(parameters);
                    }
                    //create the SqlDataReader
                    if (this.CanReadWriteDB)
                    {
                        using (reader = cmd.ExecuteReader())
                        {
                            // move the data to a DataTable
                            data = new DataTable();
                            data.Load(reader);
                        }
                    }
                }
            }
            // return the DataTable object to the calling method
            return data;
        }

        /// <summary>
        /// Calls SqlCommand.ExecuteNonQuery to save data to the database.
        /// </summary>
        /// <param name="cmdText">The query text to execute</param>
        /// <param name="parameters">The parameters to use</param>
        /// <param name="cmdType">The sql command type</param>
        /// <param name="useAdoTransaction">Flag indicating to wrap query with ado transaction</param>
        /// <returns>The number of records affected</returns>
        /// <exception cref="ArgumentNullException">The cmdText parameter cannot be null/empty.</exception>
        protected virtual int SetData(string cmdText,
                                      SqlParameter[] parameters,
                                      CommandType cmdType = CommandType.StoredProcedure,
                                      bool useAdoTransaction = false)
        {
            if (string.IsNullOrEmpty(cmdText))
            {
                throw new ArgumentNullException("cmdText");
            }

            int result = 0;
            SqlConnection conn = null;
            SqlCommand cmd = null;
            SqlTransaction transaction = null;
            using (conn = new SqlConnection(this.ConnectionString.Base64Decode()))
            {
                conn.Open();
                if (useAdoTransaction && cmdType != CommandType.StoredProcedure)
                {
                    transaction = conn.BeginTransaction();
                }

                using (cmd = new SqlCommand(cmdText, conn) { CommandTimeout = this.TimeoutSecs, CommandType = cmdType })
                {
                    SqlParameter rowsAffected = null;
                    if (parameters != null)
                    {
                        cmd.Parameters.AddRange(parameters);
                        // if this is a stored proc and we want to add a return param
                        if (cmdType == CommandType.StoredProcedure && this.AddReturnParamIfMissing)
                        {
                            // see if we already have a return parameter
                            rowsAffected = parameters.FirstOrDefault(x => x.Direction == ParameterDirection.ReturnValue);
                            // if we don't, add one.
                            if (rowsAffected == null)
                            {
                                rowsAffected = cmd.Parameters.Add(new SqlParameter("@rowsAffected", SqlDbType.Int) { Direction = ParameterDirection.ReturnValue });
                            }
                        }
                    }
                    try
                    {
                        if (this.CanReadWriteDB)
                        {
                            result = cmd.ExecuteNonQuery();
                        }
                    }
                    catch (SqlException ex)
                    {
                        if (transaction != null && cmdType != CommandType.StoredProcedure)
                        {
                            transaction.Rollback();
                        }
                        throw (ex);
                    }
                    result = (rowsAffected != null) ? (int)rowsAffected.Value : result;
                }
            }
            return result;
        }

        /// <summary>
        /// Base BulkMerge 
        /// </summary>
        /// <typeparam name="T">The entity type represented by the collection</typeparam>
        /// <param name="data">The entity collection</param>
        /// <param name="queryText">The query text to execute</param>
        /// <param name="bulkType">How to extract propertiues from the entity</param>
        /// <param name="cmdType">The sql command type</param>
        /// <returns>Number of records affected.</returns>
        protected virtual int DoBulkMerge<T>(IEnumerable<T> data,
                                             string queryText,
                                             BulkInsertType bulkType,
                                             CommandType cmdType,
                                             ParamPrecedence precedence = ParamPrecedence.None,
                                             string paramArrayPropName = "SqlParameters",
                                             bool useAdoTransaction = false)
        {
            if (string.IsNullOrEmpty(queryText))
            {
                throw new ArgumentNullException("queryText");
            }

            int result = 0;
            SqlConnection conn = null;
            SqlCommand cmd = null;
            SqlTransaction transaction = null;
            using (conn = new SqlConnection(this.ConnectionString.Base64Decode()))
            {
                conn.Open();
                if (useAdoTransaction && cmdType != CommandType.StoredProcedure)
                {
                    transaction = conn.BeginTransaction();
                }
                using (cmd = new SqlCommand(queryText, conn) { CommandTimeout = this.TimeoutSecs, CommandType = cmdType })
                {
                    try
                    {
                        foreach (T item in data)
                        {
                            SqlParameter[] parameters = DALRepository.MakeSqlParameters(item, bulkType, precedence, paramArrayPropName);
                            if (parameters != null)
                            {
                                cmd.Parameters.AddRange(parameters);
                                if (this.CanReadWriteDB)
                                {
                                    cmd.ExecuteNonQuery();
                                }
                                cmd.Parameters.Clear();
                                result++;
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        if (transaction != null)
                        {
                            transaction.Rollback();
                        }
                        throw (ex);
                    }
                }
            }
            return result;
        }

        /// <summary>
        /// Converts a value from its database value to something we can use (we need this because <br/>
        /// we're using reflection to populate our entities)
        /// </summary>
        /// <typeparam name="T">The object type</typeparam>
        /// <param name="obj">The object</param>
        /// <param name="defaultValue">The default value to be used if object is null</param>
        /// <returns>The object of the associated C# data type</returns>
        protected static T ConvertFromDBValue<T>(object obj, T defaultValue)
        {
            T result = (obj == null || obj == DBNull.Value) ? default(T) : (T)obj;
            return result;
        }

        /// <summary>
        /// Creates the list of entities from the specified DataTable object. We do this because we <br/>
        /// have two methods that both need to do the same thing.
        /// </summary>
        /// <typeparam name="T">The entity type represented by the collection</typeparam>
        /// <param name="data">The entity collection</param>
        /// <returns>The instantiated and populated list of entities.</returns>
        /// <exception cref="ArgumentNullException">The data parameter cannot be null.</exception>
        protected virtual List<T> MakeEntityFromDataTable<T>(DataTable data)
        {
            if (data == null)
            {
                throw new ArgumentNullException("data");
            }

            //----------------------------

            Type objType = typeof(T);
            List<T> collection = new List<T>();
            // if we got back data
            if (data != null && data.Rows.Count > 0)
            {
                // we're going to count how many properties in the model were assigned from the 
                // datatable.
                int matched = 0;

                foreach (DataRow row in data.Rows)
                {
                    // create an instance of our object
                    T item = (T)Activator.CreateInstance(objType);

                    // get our object type's properties
                    PropertyInfo[] properties = objType.GetProperties();

                    // set the object's properties as they are found.
                    foreach (PropertyInfo property in properties)
                    {
                        if (data.Columns.Contains(property.Name))
                        {
                            Type pType = property.PropertyType;
                            var defaultValue = pType.GetDefaultValue();
                            var value = row[property.Name];
                            value = DALRepository.ConvertFromDBValue(value, defaultValue);
                            property.SetValue(item, value);
                            matched++;
                        }
                    }
                    if (matched != data.Columns.Count && this.FailOnMismatch)
                    {
                        throw new Exception("Data retrieved does not match specified model.");
                    }
                    collection.Add(item);
                }
            }
            return collection;
        }

        /// <summary>
        /// Creates a DataTable and populates it with the specified data collection.
        /// </summary>
        /// <typeparam name="T">The entity type represented by the collection</typeparam>
        /// <param name="data">The collection of entties</param>
        /// <param name="tableName">The name of the table to insert the data into</param>
        /// <param name="bulkType">How to populate the columns from the entity</param>
        /// <returns>The created/populated DataTable object.</returns>
        public virtual DataTable MakeDataTable<T>(IEnumerable<T> data,
                                                     string tableName,
                                                     BulkInsertType bulkType,
                                                     ParamPrecedence precedence = ParamPrecedence.None,
                                                     string paramArrayPropName = "SqlParameters")
        {
            DataTable dataTable = null;

            Debug.WriteLine(string.Format("MakeDataTable - {0} data items specified.", data.Count()));

            using (dataTable = new DataTable() { TableName = tableName })
            {
                Type type = typeof(T);

                // Get the properties to send to the database. If byDBInsertAttribute is true, only object 
                // properties that are decorated with the CanDBInsert attribute will be retrieved. If 
                // byDBInsertAttribute is false, only properties that have a Set method will be retrieved
                PropertyInfo[] properties = DALRepository.GetEntityProperties(type, bulkType);

                Debug.WriteLine(string.Format("MakeDataTable - {0} item properties per item.", properties.Length));

                // create columns that match the retrieved properties
                foreach (PropertyInfo property in properties)
                {
                    //dataTable.Columns.Add(new DataColumn(property.Name, property.PropertyType));
                    dataTable.Columns.Add(new DataColumn(property.Name, Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType));
                }

                Debug.WriteLine(string.Format("MakeDataTable - {0} dataTable columns created.", dataTable.Columns.Count));

                // add the rows to the datatable
                foreach (T entity in data)
                {
                    DataRow row = dataTable.NewRow();
                    foreach (PropertyInfo property in properties)
                    {
                        row[property.Name] = property.GetValue(entity) == null ? DBNull.Value : property.GetValue(entity);
                    }
                    dataTable.Rows.Add(row);
                }
            }

            Debug.WriteLine(string.Format("MakeDataTable - {0} rows created.", dataTable.Rows.Count));

            return dataTable;
        }

        #endregion Protected helper methods

    }
}

DALStaticMethods.cs


using SMS.DAL.Common;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace SMS.DAL
{
    public partial class DALRepository
{
// If you want to make these methods public, simply uncomment the compiler definition at 
// the top of this file.

/// <summary>
/// Get properties for the specified type, as indicated by the bulkType
/// </summary>
/// <param name="type">The object type</param>
/// <param name="bulkType">How to get properties</param>
/// <returns>An array of PropertyInfo</returns>
#if __PUBLIC_STATIC__
public 
#else
protected
#endif
static PropertyInfo[] GetEntityProperties(Type type, BulkInsertType bulkType)
{
PropertyInfo[] properties = type.GetProperties();
switch (bulkType)
{
case BulkInsertType.DBInsertAttribute:
properties = type.GetProperties().Where(prop => Attribute.IsDefined(prop, typeof(CanDbInsertAttribute))).ToArray();
break;

case BulkInsertType.HaveSetMethod:
properties = type.GetProperties().Where(prop => prop.GetSetMethod() != null).ToArray();
break;
}
return properties;
}

/// <summary>
/// Gets the entity properties as directed by the specified bulkType.
/// </summary>
/// <typeparam name="T">The entity's type</typeparam>
/// <param name="entity">The entity object</param>
/// <param name="bulkType">The bulk type</param>
/// <returns>The properties indicated by the specified bulkType</returns>
#if __PUBLIC_STATIC__
public 
#else
protected
#endif
static PropertyInfo[] GetEntityProperties<T>(T entity, BulkInsertType bulkType)
{
PropertyInfo[] properties = entity.GetType().GetProperties();
properties = DALRepository.GetEntityProperties(properties, bulkType);
return properties;
}

/// <summary>
/// Gets the property entites as directed by the specified bulkType.
/// </summary>
/// <param name="properties"> The rpoerties to extract from</param>
/// <param name="bulkType">The bulk type</param>
/// <returns>The properties indicated by the specified bulkType</returns>
#if __PUBLIC_STATIC__
public 
#else
protected
#endif
static PropertyInfo[] GetEntityProperties(PropertyInfo[] properties, BulkInsertType bulkType)
{
// the "bulkType" (admittedly poorly named) indicates how we look for properties 
// in the entity.
switch (bulkType)
{
// general note: the accessibility doesn't really matter because you may want 
// to include a private property along with the public ones, so all of the 
// following cases pull all properties that match the inidcated conditions.
case BulkInsertType.ALL:
// hoover them all up
break;

case BulkInsertType.DBInsertAttribute:
// only get properties that are decorated with the CanDbInsert attribute 
// (a custom attribute that's part of this class library)
properties = properties.Where(prop => Attribute.IsDefined(prop, typeof(CanDbInsertAttribute))).ToArray();
break;

case BulkInsertType.HaveSetMethod:
// only get properties that have a "set" method. There are a few ways to 
// check for this, but I chose this one.
properties = properties.Where(prop => prop.SetMethod != null).ToArray();
break;
// feel free to more items in the BulkInsertType enumerator, and handle 
// them here.
};

return properties;
}

/// <summary>
/// Creates a SqlParameter array from the specified entity, and based on the specified <br/>
/// bulk insert type
/// </summary>
/// <typeparam name="T">The entity type</typeparam>
/// <param name="entity">The entity object</param>
/// <param name="bulkType">The bulk insert type.</param>
/// <param name="precedence">How to treat the discovered SqlParameter[] property (if any)</param>
/// <param name="propertyName">Case-sensitive name of desired SqlParameter[] property</param>
/// <returns>An appropriate SqlParameter array </returns>
/// <exception cref="ArgumentNullException">The entity cannot be null</exception>
/// <exception cref="InvalidOperationException">The propertyName should never be null.</exception>
#if __PUBLIC_STATIC__
public 
#else
protected
#endif
static SqlParameter[] MakeSqlParameters<T>(T entity,
BulkInsertType bulkType,
ParamPrecedence precedence = ParamPrecedence.None,
string propertyName = "SqlParameters")
{
if (entity == null)
{
throw new ArgumentNullException("entity");
}
if (string.IsNullOrEmpty(propertyName))
{
throw new InvalidOperationException("It makes no sense to specify a null propertyName. Ever.");
}

SqlParameter[] parameters = null;
PropertyInfo[] properties = entity.GetType().GetProperties();

// see if we can find the specified propertyName that returns a SqlParameter[] (with the right name)
PropertyInfo sqlParams = sqlParams = properties.FirstOrDefault(x => x.PropertyType.Name == "SqlParameter[]" &&
x.Name == propertyName);

// if the entity has a property that returns a SqlParameter array AND the calling 
// method did not specify to ignore it, set the parameters var to that property's 
// value, and our job is done here
if (sqlParams != null && precedence != ParamPrecedence.UseBulkType)
{
parameters = (SqlParameter[])sqlParams.GetValue(entity);
}
else
// looks like we gotta finger it out on our own - NOBODY EXPECTED THE MANUAL DETECTION!!
{
List<SqlParameter> list = new List<SqlParameter>();

properties = DALRepository.GetEntityProperties(properties, bulkType);

// populate the list of SqlPrameters from the properties we gatherd in the switch statment.
foreach (PropertyInfo property in properties)
{
list.Add(new SqlParameter(string.Format("@{0}", property.Name), property.GetValue(entity)));
}
parameters = list.ToArray();
}

#if DEBUG
Global.WriteLine("-----------------------------");
if (properties.Length == 0)
{
Global.WriteLine("No properties found.");
}
else
{
// satisfy my fanatical desire to line stuff up.
int length = parameters.Max(x => x.ParameterName.Length) + 1;
string format = string.Concat("    {0,-", length.ToString(), "}{1}");

//// i thought this was providing redundant info, but only commented it out so I can 
//// easily get it back if needed.
//Global.WriteLine("Discovered properties:");
//foreach(PropertyInfo item in properties)
//{
// string text = string.Format(format, item.Name, item.GetValue(entity).ToString());
// Global.WriteLine(text); 
//}
Global.WriteLine("Resulting parameters:");
foreach (SqlParameter item in parameters)
{
string text = string.Format(format, item.ParameterName, item.SqlValue);
Global.WriteLine(text);
}
}
#endif

// and return them to the calling method
return parameters;
}
}
}


StudentDataAccessLayer.cs


using SMS.Entity;
using SMS.Utility;
using System.Data;
using System.Data.SqlClient;

namespace SMS.DAL
{
    public class StudentDataAccessLayer
    {

        string connectionString = ConnectionString.CName;


        public IEnumerable<Student> GetAllStudent()
        {
            List<Student> lstStudent = new List<Student>();
            Student student = new Student();
            DALRepository objDALRepository = new DALRepository();
            var parameters = new List<SqlParameter>
            {
                new SqlParameter ("@TransactionId",1),
                new SqlParameter("@Id", 1)
            };
            lstStudent = objDALRepository.ExecuteStoredProc<Student>("spGetAllStudent", parameters.ToArray());
            return lstStudent;
        }


        public void AddStudent(Student student)
        {
            DALRepository objDALRepository = new DALRepository();

            var parameters = new List<SqlParameter>
            {
                new SqlParameter("@FirstName", student.FirstName ),
                new SqlParameter("@LastName",student.LastName),
                new SqlParameter("@Email", student.Email),
                new SqlParameter("@Mobile",student.Mobile),
                new SqlParameter("@Address", student.Address),
            };
            int result = objDALRepository.ExecuteStoredProc("spAddStudent", parameters.ToArray());
        }

        public void UpdateStudent(Student student)
        {
            DALRepository objDALRepository = new DALRepository();

            var parameters = new List<SqlParameter>
            {
                new SqlParameter("@FirstName", student.FirstName ),
                new SqlParameter("@LastName",student.LastName),
                new SqlParameter("@Email", student.Email),
                new SqlParameter("@Mobile",student.Mobile),
                new SqlParameter("@Address", student.Address),
            };
            int result = objDALRepository.ExecuteStoredProc("spAddStudent", parameters.ToArray());
        }

        public Student GetStudentData(int? id)
        {
            Student student = new Student();
            DALRepository objDALRepository = new DALRepository();
            var parameters = new List<SqlParameter>
            {
                new SqlParameter ("@TransactionId",2),
                new SqlParameter("@Id", id)
            };
            student = objDALRepository.ExecuteStoredProc<Student>("spGetAllStudent", parameters.ToArray()).FirstOrDefault();
            return student;
        }

        public void DeleteStudent(int? id)
        {
            DALRepository objDALRepository = new DALRepository();

            var parameters = new List<SqlParameter>
            {
                new SqlParameter("@Id", id )
            };
            int result = objDALRepository.ExecuteStoredProc("spDeleteStudent", parameters.ToArray());
        }
        public int LoginCheck(Student objStudent)
        {
            int result = 0;
            Student student = new Student();
            DALRepository objDALRepository = new DALRepository();
            var parameters = new List<SqlParameter>
            {
                new SqlParameter ("@UserName",objStudent.UserName),
                new SqlParameter("@Password", objStudent.Password)
            };
            student = objDALRepository.ExecuteStoredProc<Student>("spLogin", parameters.ToArray()).FirstOrDefault();
            if (student == null)
            {
                result = 0;
            }
            else
            {
                result = 1;
            }
            return result;
        }
    }
}

Create Entity Project and add Student Class

Student.cs

using System.ComponentModel.DataAnnotations;

namespace SMS.Entity
{
    public class Student
    {
        public int Id { set; get; }
        public string FirstName { set; get; }
        public string LastName { set; get; }
        public string Email { set; get; }
        public string Mobile { set; get; }
        public string Address { set; get; }
        public string UserName { get; set; }
        public string Password { set; get; }
    }
}

Run Project and check below screenshots






Click on login, if login success then will redirect to listing screen



























Using Authorization with Swagger in ASP.NET Core

 Create Solution like below LoginModel.cs using System.ComponentModel.DataAnnotations; namespace UsingAuthorizationWithSwagger.Models {     ...