SQLProcedureDAO 2.3.1

SQLProcedureDAO

SQL Data access object to execute SQL Stored Procedures.

Note : Support User Defined Tables

Installation

Use the NuGet Package Manager to install SQLProcedureDAO.

PM> Install-Package SQLProcedureDAO

Usage

1 - Create a class extends DAOContext

public class ProductDAODbContext: DAOContext
{
    public ProductDAODbContext() : base("ConnectionString")
    {
    }
}

2 - Create an object for ProductDAODbContext

ProductDAODbContext daoContext = new ProductDAODbContext();

• Create a ProductModel

// product model
public class ProductModel {
    public int Id {get; set;}
    public string ProductName {get; set;}
    public decimal Price{get; set;}
}

• If property name is different from column name you can use Parameter Attribute Parameter attribute has two parameter

  1 - Parameter name 2 - Parameter type (Use ParameterType enum)

• Default parameter is ParameterType.Normal

• If parameter is UDT type use ParameterType.UDT

• If you want to ignore parameter use ParameterType.Ignore example :

// product model
public class ProductModel {

    [Parameter("id")]
    public int Id {get; set;}

    public string ProductName {get; set;}

    public decimal Price {get; set;}

    [Parameter("", parameterType: ParameterType.Ignore)]
    public decimal SalePrice {get; set;}
}

3 - Execute Insert/Update procedure

• First parameter ⇒ Procedure name
• Second parameter ⇒ Argument names as string array
• Third parameter ⇒ Values as object array

Argument array and values array order should match

int res = daoContext.ExecuteStoredProcedure("InsertProduct", 
          new string[]{"ProductName", "Price"}, 
          new object[]{"Rice", 54.2});

4- Execute Select procedure by retrieving only first value of first row

• Return value as type parameter

int res = daoContext.ExecuteStoredProcedure<int>("GetSumOfProduct", 
          new string[]{"ProductId"}, 
          new object[]{1});

//without parameters
bool isNewOrderArrived = daoContext.ExecuteStoredProcedure<bool>("IsNewOrderArrived");


//type parameter as ProductModel, (Get first row only)
ProductModel productModel = daoContext.ExecuteStoredProcedure<ProductModel>("GetAllProducts");

//type parameter as List<ProductModel>
List<ProductModel> productModel = daoContext.ExecuteStoredProcedureAsList<ProductModel>("GetAllProducts");

4- Execute Procedure by User Defined Table (UDT)

List<ProductModel> productList = new List<ProductModel>()
{
  new ProductModel{ProductName = "Cashew nut", Price = 450},
  new ProductModel{ProductName = "Chilly Powder", Price = 40},
};

// you can also send udt list as anonymous class
List<dynamic> dynamicList = new List<dynamic>()
{
  new {ProductName = "Cashew nut", Price = 450},
  new {ProductName = "Chilly Powder", Price = 40},
};

//specify UDT parameter name inside 3rd parameter as string[], should incluede in parameter names array
//return rows affected
int res = program.ExecuteStoredProcedureWithUDT(
                procedureName: "InsertProducts",
                parameterNames: new string[] { "CreatedAt", "IsActive", "Item" },
                udtParameterNames: new string[] { "Item" },
                values: new object[] { DateTime.Now, 1, productList });

int dynamicListRes = program.ExecuteStoredProcedureWithUDT(
                procedureName: "InsertProducts",
                parameterNames: new string[] { "CreatedAt", "IsActive", "Item" },
                udtParameterNames: new string[] { "Item" },
                values: new object[] { DateTime.Now, 1, dynamicList });

/* return parameter as first column value from first row(If insert procedure end query is select statement) 
or you can use custom models as return according to your procedure select statement */
int id = program.ExecuteStoredProcedureWithUDT<int>(
                procedureName: "InsertProductASUDT",
                parameterNames: new string[] { "CreatedAt", "IsActive", "Item" },
                udtParameterNames: new string[] { "Item" },
                values: new object[] { DateTime.Now, 1, productList });

License

MIT