Hi ayyappadas608...,
Using the below article i have created the example.
Refer below sample code.
Helper
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
namespace Data_Layer
{
public static class Helper
{
//Connection string to the Database.
public static string ConString = "";
/// <summary>
/// Gets Single DTO object.
/// </summary>
/// <typeparam name="T">Dynamic object.</typeparam>
/// <param name="sql">SQL Query.</param>
/// <param name="parameters">Parameter collection.</param>
/// <param name="isStoredProc">Boolean value. True if Stored Procedure.</param>
/// <returns>DTO object.</returns>
public static T GetSingle<T>(this string sql, Dictionary<string, object> parameters = null, bool isStoredProc = false)
{
PropertyInfo[] info = typeof(T).GetProperties();
object t = Activator.CreateInstance(typeof(T));
using (SqlConnection con = new SqlConnection(Helper.ConString))
{
using (SqlCommand cmd = new SqlCommand(sql, con))
{
cmd.CommandType = isStoredProc ? CommandType.StoredProcedure : CommandType.Text;
if (parameters != null)
{
foreach (var p in parameters)
{
cmd.Parameters.AddWithValue(p.Key, p.Value);
}
}
con.Open();
using (SqlDataReader sdr = cmd.ExecuteReader())
{
if (sdr.Read())
{
t = GetSingleObject<T>(sdr);
}
sdr.Close();
}
con.Close();
}
}
return (T)t;
}
/// <summary>
/// Gets List of DTO object.
/// </summary>
/// <typeparam name="T">Dynamic object.</typeparam>
/// <param name="sql">SQL Query.</param>
/// <param name="parameters">Parameter collection.</param>
/// <param name="isStoredProc">Boolean value. True if Stored Procedure.</param>
/// <returns>List of DTO objects.</returns>
public static List<T> GetList<T>(this string sql, Dictionary<string, object> parameters = null, bool isStoredProc = false)
{
PropertyInfo[] info = typeof(T).GetProperties();
List<T> t = new List<T>();
using (SqlConnection con = new SqlConnection(Helper.ConString))
{
using (SqlCommand cmd = new SqlCommand(sql, con))
{
cmd.CommandType = isStoredProc ? CommandType.StoredProcedure : CommandType.Text;
if (parameters != null)
{
foreach (var p in parameters)
{
cmd.Parameters.AddWithValue(p.Key, p.Value);
}
}
con.Open();
using (SqlDataReader sdr = cmd.ExecuteReader())
{
while (sdr.Read())
{
t.Add(GetSingleObject<T>(sdr));
}
sdr.Close();
}
con.Close();
}
}
return t;
}
/// <summary>
/// Gets Single Scalar value.
/// </summary>
/// <param name="sql">SQL Query.</param>
/// <param name="parameters">Parameter collection.</param>
/// <param name="isStoredProc">Boolean value. True if Stored Procedure.</param>
/// <returns>Scalar object.</returns>
public static object GetScalar(this string sql, Dictionary<string, object> parameters = null, bool isStoredProc = false)
{
object t;
using (SqlConnection con = new SqlConnection(Helper.ConString))
{
using (SqlCommand cmd = new SqlCommand(sql, con))
{
cmd.CommandType = isStoredProc ? CommandType.StoredProcedure : CommandType.Text;
if (parameters != null)
{
foreach (var p in parameters)
{
cmd.Parameters.AddWithValue(p.Key, p.Value);
}
}
con.Open();
t = cmd.ExecuteScalar();
con.Close();
}
}
return t;
}
/// <summary>
/// Executes query.
/// </summary>
/// <param name="sql">SQL Query.</param>
/// <param name="parameters">Parameter collection.</param>
/// <param name="isStoredProc">Boolean value. True if Stored Procedure.</param>
/// <returns>Rows affected.</returns>
public static int Execute(this string sql, Dictionary<string, object> parameters = null, bool isStoredProc = false)
{
int rowsAffected = 0;
using (SqlConnection con = new SqlConnection(Helper.ConString))
{
using (SqlTransaction sqlTransaction = con.BeginTransaction())
{
using (SqlCommand cmd = new SqlCommand(sql, con))
{
cmd.CommandType = isStoredProc ? CommandType.StoredProcedure : CommandType.Text;
if (parameters != null)
{
foreach (var p in parameters)
{
cmd.Parameters.AddWithValue(p.Key, p.Value);
}
}
con.Open();
try
{
rowsAffected = cmd.ExecuteNonQuery();
sqlTransaction.Commit();
}
catch
{
sqlTransaction.Rollback();
}
con.Close();
}
}
}
return rowsAffected;
}
/// <summary>
/// Reads data from DataReader.
/// </summary>
/// <typeparam name="T">Dynamic object.</typeparam>
/// <param name="sdr">SqlDataReader object</param>
/// <returns>DTO object.</returns>
private static T GetSingleObject<T>(this SqlDataReader sdr)
{
PropertyInfo[] info = typeof(T).GetProperties();
object t = Activator.CreateInstance(typeof(T));
foreach (PropertyInfo field in info)
{
if (sdr.HasColumn(field.Name))
{
field.SetValue(t, sdr[field.Name]);
}
}
return (T)t;
}
/// <summary>
/// Checks whether Column present in DataRecord.
/// </summary>
/// <param name="dr">DataRcord object.</param>
/// <param name="columnName">Column Name.</param>
/// <returns>Boolean value.</returns>
public static bool HasColumn(this IDataRecord dr, string columnName)
{
for (int i = 0; i < dr.FieldCount; i++)
{
if (dr.GetName(i).Equals(columnName, StringComparison.InvariantCultureIgnoreCase))
{
return true;
}
}
return false;
}
}
}
CustomerDL
using DTO;
namespace Data_Layer
{
public class CustomerDL
{
///<summary>
/// Gets Customer Name.
///</summary>
///<param name="customerId">CustomerId object.</param>
///<returns>Customer Name.</returns>
public static string GetCustomerName(int customerId)
{
string sql = "SELECT Name FROM Customers WHERE CustomerId=@CustomerId";
Dictionary<string, object> parameters = new Dictionary<string, object>();
parameters.Add("@CustomerId", customerId);
return sql.GetScalar(parameters).ToString();
}
///<summary>
/// Gets Customer based on Id.
///</summary>
///<param name="customerId">CustomerId object.</param>
///<returns>Customer object.</returns>
public static Customer GetCustomer(int customerId)
{
string sql = "SELECT CustomerId, Name, Country, BirthDate FROM Customers WHERE CustomerId=@CustomerId";
Dictionary<string, object> parameters = new Dictionary<string, object>();
parameters.Add("@CustomerId", customerId);
Customer customer = sql.GetSingle<Customer>(parameters);
return customer;
}
///<summary>
/// Gets all Customers.
///</summary>
///<returns>List of Customers.</returns>
public static List<Customer> GetCustomers()
{
string sql = "SELECT CustomerId, Name, Country, BirthDate FROM Customers";
List<Customer> customers = sql.GetList<Customer>();
return customers;
}
///<summary>
/// Insert Customer data.
///</summary>
///<param name="customer">Customer object.</param>
///<returns>CustomerId object.</returns>
public static int InsertCustomer(Customer customer)
{
string sql = "INSERT INTO Customers(Name, Country, BirthDate) VALUES(@Name, @Country, @BirthDate)";
sql += " SELECT SCOPE_IDENTITY()";
Dictionary<string, object> parameters = new Dictionary<string, object>();
parameters.Add("@Name", customer.Name);
parameters.Add("@Country", customer.Country);
parameters.Add("@BirthDate", customer.BirthDate);
return Convert.ToInt32(sql.GetScalar(parameters));
}
}
}
ICustomerBL
using Data_Layer;
using DTO;
namespace Business_Layer
{
public interface ICustomerBL
{
///<summary>
/// Gets Customer Name.
///</summary>
///<param name="customerId">CustomerId object.</param>
///<returns>Customer Name.</returns>
string GetCustomerName(int customerId);
///<summary>
/// Gets Customer based on Id.
///</summary>
///<param name="customerId">CustomerId object.</param>
///<returns>Customer object.</returns>
Customer GetCustomer(int customerId);
///<summary>
/// Gets all Customers.
///</summary>
///<returns>List of Customers.</returns>
List<Customer> GetCustomers();
///<summary>
/// Insert Customer data.
///</summary>
///<param name="customer">Customer object.</param>
///<returns>CustomerId object.</returns>
public int InsertCustomer(Customer customer)
{
return CustomerDL.InsertCustomer(customer);
}
}
}
IndexModel
public class IndexModel : PageModel
{
public void OnGet()
{
Customer customer = new Customer();
customer.Name = "Dharmendra";
customer.Country = "India";
customer.BirthDate = DateTime.Now;
ICustomerBL customerBL = new CustomerBL();
int customerId = customerBL.InsertCustomer(customer);
}
}
