Crudity

CRUD Builder for Data Access Layer C#/Vb.net/MySql


Keywords
Crudity, crudformysql
Install
Install-Package Crudity -Version 1.2.0

Documentation

CRUDIgniter

CRUDIgniter helps developer to create a less CRUD operations using MySql database, it is very easy to use and makes your code more readable also lessen the codes in your Data Access Layer

Dependencies

MySql Connector .Net 6.9.9/MySql.Data https://downloads.mysql.com/archives/get/file/mysql-connector-net-6.9.9.msi

Installation

Via NuGet Package Manager (Console)

  1. Create new project.
  2. Save project.
  3. Go to Tools->Nuget Package Manager->Package Manager Console.
  4. Enter the command: Install-Package CrudIgniter then press enter.
  5. It will install the latest version and your done.

Via NuGet Package Manager (GUI)

  1. Create new project.
  2. Save project.
  3. Go to Tools->NuGet Package Manager->Manage NuGet Packager for Solution...
  4. Search for CrudIgniter then click Install
  5. If you see green check beside the package then you successfully installed it.
  6. It will install the latest version and your done.

Example

Table

CREATE TABLE  `employee` (
  `Id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `Name` varchar(45) NOT NULL,
  `Address` varchar(45) NOT NULL,
  `Email` varchar(45) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

C# Example

Model

namespace Models 
{
  public class Employee 
  {
    #region Constructor
    public Employee() {}
    
    public Employee(int id, string name, strin address, string email) 
    {
      this.Id = id;
      this.Name = name;
      this.Address = address;
      this.Email = email;
    }
     public Employee(string name, strin address, string email) 
    {
      this.Name = name;
      this.Address = address;
      this.Email = email;
    }
    #endregion

    #region Property
    public int Id { get;set; }
    public string Name { get;set; }
    public string Address { get;set; }
    public string Email { get;set; }
    #endregion
  }
}

Data Access Layer

using System.Data;
using CrudIgniter.Helper;

namespace DAL 
{
  private CRUD crud = new CRUD("change your connection string here..");
  
  // Get all employee
  public DataTable GetAllEmployee()
  {
    return crud.Select("employee");
  }
  
  // Get employee using id
  public DataTable GetEmployeeById(int id)
  {
    string sql = "SELECT * FROM employee WHERE Id=?";
    
    return crud.GetData(sql, new object[] {id});
  }
  
  // Insert employee record
  public void Create(Models.Employee employeeModel)
  {
    crud.Table = "employee";
    crud.Columns = new string[] { "Name", "Address", "Email" };
    crud.Values = new object[] { employeeModel.Name, employeeModel.Address, employeeModel.Email };
    crud.Insert();
  }
  
  // Update employee record
  public void Update(Models.Employee employeeModel)
  {
    crud.Table = "employee";
    crud.Columns = new string[] { "Name", "Address", "Email" };
    crud.Values = new object[] { employeeModel.Name, employeeModel.Address, employeeModel.Email };
    crud.Where = new string[] { "Id" };
    crud.Where = new object[] { employeeModel.Id };
    crud.Update();
  }
  
  // Delete employee record
  public void Delete(int id)
  {
    crud.Table = "employee";
    crud.Where = new string[] { "Id" };
    crud.Where = new object[] { id};
    crud.Delete();
  }
}

Business Logic Layer

using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using CrudIgniter.Helper;

namespace BLL
{
  private DAL.Employee employeeDAL = new DAL.Employee();
  
  // Convert datatable to list of object 'Employee'
  public List<Models.Employee> GetAllEmployee() 
  {
    return employeeDAL.GetAllEmployee().ToList<Models.Employee>();
  }
  
  // Convert datarow to object
  public Models.Employee GetEmployeeById(int id)
  {
    Models.Employee employeeModel = new Models.Employee();
    
    foreach (DataRow row in employeeDAL.GetEmployeeById(id).Rows)
    {
      employeeModel = row.ToObject<Models.Employee>();
    }
    
    return employeeModel;
  }
  
  public void Create(Models.Employee employeeModel)
  {
    // You can add validation here
  
    employeeDAL.Create(employeeModel);
  }
  
  public void Update(Models.Employee employeeModel)
  {
    // You can add validation here
    
    employeeDAL.Update(employeeModel);
  }
  
  public void Delete(int id)
  {
    employeeDAL.Delete(id);
  }
}

UI/Console

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace MyApp
{
  class Program
  {
        static void Main(string[] args)
        {

            BLL.Employee employeeBLL = new BLL.Employee();

            // Get all employee
            foreach (Models.Employee employeeModel in employeeBLL.GetAllEmployee())
            {
                Console.WriteLine("___________________________________");
                Console.WriteLine("ID: " + employeeModel.Id);
                Console.WriteLine("Name: " + employeeModel.Name);
                Console.WriteLine("Address: " + employeeModel.Address);
                Console.WriteLine("Email: " + employeeModel.Email);
                Console.WriteLine("___________________________________\n");
            }

            Console.ReadKey();
        }
  }
}