ADOCRUD

Lightweight ORM thats built on top of ADO.NET and is partly an extension of Dapper. Handles automatic insert, update, and removal of objects without having to write any sql statements for Create, Update, and Remove functionality. The query part of this ORM is an extension of Dapper which means you still need to write sql statements to retrieve data, but that data will automatically be mapped to your C# objects. Most of this application was written primarily using reflection.

This ORM comes with an object class generator tool. This tool allows you to connect to a Sql Server database, grabs all the tables, and generates C# objects as .cs files and outputs them to the folder you specify.

ADOCRUDContext (ORM)

Model Example:

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


namespace ADOCRUDExamples.Models
{
    [Table("Product", "dbo")]
    public class Product
    {
        [PrimaryKey]
        [Member]
        public int Id { get; set; }

        [Member]
        public int CategoryId { get; set; }

        [Member]
        public string Name { get; set; }

        [Member]
        public decimal Price { get; set; }

        [Member]
        public string Description { get; set; }

    }
}

For your objects to work with the ADOCRUD ORM, attributes are required. Table attribute specifies which database table the object maps to. The first argument in the table attribute is the table name. The 2nd argument specifies the schema the table belongs to. For example, the attribute [Table("Product", "dbo")] looks for the table "dbo.Product". Member attribute specifies that the property corresponds to a column in the table. Properties that do not have this member attribute will be ignored and excluded from any database manipulation (insert, update, remove). PrimaryKey attribute specifies that the property corresponds with the identity/primary key column of the table.

Insert Example:

public void AddProduct(Product p)
{
  using (ADOCRUDContext context = new ADOCRUDContext(connectionString))
  {
    context.Insert<Product>(p);
    context.Commit();
  }
}

Using statement opens up a connection to the database specified in the connection string and also starts up a transaction. The insert does not finalize unless you call the commit method of the context. ADOCRUDContext closes the connection and disposes the transaction on Dispose() which means the connection gets closed and the transaction gets disposed at the end of the using statement.

Update Example:

public void UpdateProduct(Product p)
{
  using (ADOCRUDContext context = new ADOCRUDContext(connectionString))
  {
      context.Update<Product>(p);
      context.Commit();
  }
}

Update behaves the same way as the insert. It starts up a transaction and opens a connection in the beginning of the using statement. Completes the transaction on the commit method. Closes the connection and disposes the transaction on Dispose() which is the final bracket of the using statement.

public void Remove(Product p)
{
  using (ADOCRUDContext context = new ADOCRUDContext(connectionString))
  {
      context.Remove<Product>(p);
      context.Commit();
  }
}

Remove behaves the same way as the previous 2 methods.

Query Example:

public Product GetProductById(int productId)
{
  Product p = null;

  using (ADOCRUDContext context = new ADOCRUDContext(connectionString))
  {
    p = context.QueryItems<Product>("select * from dbo.Product where Id = @id", new { id = productId }).FirstOrDefault();
  }
  
  return p;
}

QueryItems is a wrapper around Dapper's "query" method. QueryItems executes the select statement you pass in, grabs the results of the query and automatically maps it the C# object(s) and returns that/those object. The QueryItems function by default returns a list of objects, but you can limit it to a single object by using the "First()" or "FirstOrDefault()" method. Notice that to keep the query parameterized, you pass the parameters in the 2nd argument as a single object. To pass in extra parameters, you just add in comma separated values (i.e new { Id = productId, name = "Basketball", price = 11.99 })

###Limitations:
#1. Does not support nested transactions, but does support nested connections. In other words, you can create a using context within another using context, but if both contexts call their respective commmit method and the outer context fails and the inner context succeeds, the changes in that inner context will still be applied to the database while the outer commit will roll back whatever it tried to do.

#2. Spelling and letter casing between C# properties and their corresponding sql columns the properties maps to must be exactly the same.
Example that works: SQL Column: ProductId, C# Property: public int ProductId { get; set; }
Example that does not work: SQL Column: product_id, C# Property: public int ProductId { get; set; }

##ADOCRUD Object Class Generator

The object class generator tool provides a graphical user interface that is pretty straight forward to use. This generator scans all the tables in the Sql Server database entered and generates C# objects/classes for each of those tables.
Sql Server Datasource: Name of the server
Database: Name of the database
User Id: Sql server user id that has access to the database
Password: Sql server password of the user
C# Namespace: Namespace the C# class should be under
Output Path: File path where all the C# classes should be generated
Generate Objects: Clicking this button generates all the C# objects in the file path entered.

##License License is under Apache. This means that this software is free for personal or commercial use.

Issues

If you find an issue with this software, please click here to submit a ticket.

##Author Name: Daniel Li
My Twitter Handle: https://twitter.com/DanielDavidLi
LinkedIn Profile: https://www.linkedin.com/in/danieldli?trk=hp-identity-name