How To Use Dynamic Parameters In Dapper 2.0 In C# .Net Core?

In this post, we will create a sample Web API project in dot net core and Stored Procedures in the database to perform CRUD operations (i.e., Create, Update, Retrieve and Delete) using the Dapper package.

Dapper is an open-source, lightweight, micro-ORM tool that deals directly with the class object to perform database-related operations, read more…

In this Dapper example we will create and implement:

• a new table and Store Procedures in the database
• API methods – get, add, modify, delete
• dapper code that executes procedure with dynamic parameters

1)
Let’s first create a new table called Product that stores basic product information in the database.

CREATE TABLE PRODUCTS 
(
	ID INT PRIMARY KEY IDENTITY(1,1),
	CODE INT NOT NULL,
	[NAME] VARCHAR(30) NOT NULL,
	PRICE DECIMAL (4,2)
)

Insert some records into the Table.

INSERT INTO PRODUCTS (CODE, [NAME], PRICE) 
VALUES (100, 'Pen', 1.00 )

INSERT INTO PRODUCTS (CODE, [NAME], PRICE) 
VALUES (200, 'Book', 5.00 ) 

Execute the ‘Select’ query to ensure the Table and its data is available in the database.

2)
Create a new WebAPI project called “MyStore”.

Install these two packages – Dapper and System.Data.SqlClient

3)
Configure ConnectionStrings in the appsettings.json file.

ConnectionStrings: {
    //Local DB
    "MyStore-db": "Server=.; Database=LocalDB; Trusted_Connection=True; MultipleActiveResultSets=true"
    
    //Your server
    //"MyStore-db": "Server=YourServerName; Initial Catalog=DBName; Persist Security Info=False;  User ID= User_Id; Password=YourPassord; MultipleActiveResultSets=False; Encrypt=True; TrustServerCertificate=False; Connection Timeout=30;"
  },

4)
Add a new Controller and save it as “ProductController.cs”

Add the following namespaces

using Microsoft.Extensions.Configuration;
using System.Data.SqlClient;
using Dapper;

Add Constructor to get the connection string value from “appsettings.json” and this will be assigned to ‘readonly’ variable, during runtime.

public class ProductController : ControllerBase
{
	private readonly string _conn;

	public ProductController(IConfiguration _configuration)
	{
		_conn = _configuration.GetConnectionString("MyStore-db");
	}
}

5)
Add a new Property class – ProductObject.cs
This sample uses JsonPropertyName attribute to change the names of properties when they are serialized to JSON.

public class ProductObject
{
	[JsonPropertyName("product_id")]
	public int Id { get; set; }

	[JsonPropertyName("product_code")]
	public int Code { get; set; }

	[JsonPropertyName("product_name")]
	public string Name { get; set; }

	[JsonPropertyName("product_price")]
	public decimal Price { get; set; }
}

Till this, the required configuration and code are completed and now we will see how to perform CRUD operations with the help of the Dapper extension code.

“Get” example using Dapper and Stored Procedure

a. Create a stored procedure that returns all records.

CREATE PROCEDURE SP_GetAllProducts
As
Begin
	Select * from Products
End

Execute the stored procedure to make sure it returns the correct data.

exec SP_GetAllProducts

b. Add a Get() API method code as shown below, it will execute the store procedure using dapper and return the result in JSON format.

[HttpGet]
public List<ProductObject> Get()
{
	using (SqlConnection conn = new SqlConnection(_conn))
	{
		conn.Open();
		string spName = "SP_GetAllProducts";                
		return conn.Query<ProductObject>(spName, commandType: CommandType.StoredProcedure).ToList();
	}
}

Note: Instead of using property class, you can use “dynamic” keyword which will return the dynamic list. example – public List<dynamic> Get()
But it will display & expose the retrieved stored procedure’s column names as property names in the JSON output. So, either give an alias name for each column while selecting the final query in the stored procedure or rename it in the Property class as shown in ProductObject.cs file with the help of JsonPropertyName attribute.

c. Run the application and executed the Get API method from Swagger. (You can also use Postman or a similar tool to test any API)

Output:

“Insert or Update” example using Dapper and Stored Procedure

a. Create a stored procedure that inserts or updates a record. This single procedure will perform both operations based on the condition. If ‘id’ is passed as zero (0) then it will be considered a new record and inserted into the table. If ‘id’ is greater than zero (0) then it will update the existing record based on the primary key.

CREATE PROCEDURE SP_InsertOrUpdatetProduct
(
	@Id INT,
	@Code INT,
	@Name VARCHAR(30),
	@Price DECIMAL(4,2)
)
AS
BEGIN
	IF (@Id = 0)
	BEGIN
		INSERT INTO Products (CODE,[NAME],PRICE) VALUES (@Code,@Name,@Price)
	END
	ELSE
	BEGIN
		UPDATE Products SET CODE=@Code, [NAME]=@Name, PRICE=@Price
		WHERE ID = @Id
	END
END 

b. Add an API method that will pass the required input parameters and execute the stored procedure, as shown in the below code.

[HttpPut]
public void InsertOrUpdate([FromBody] ProductObject product)
{
	//Dynamic parameters
	var inputparameters = new DynamicParameters();
	inputparameters.Add("@Id", Convert.ToInt32(product.Id));
	inputparameters.Add("@Code", product.Code);
	inputparameters.Add("@Name", product.Name);
	inputparameters.Add("@Price", product.Price);

	using (SqlConnection conn = new SqlConnection(_conn))
	{
		conn.Open();
		string spName = "SP_InsertOrUpdatetProduct";
		conn.Query(spName, param: inputparameters, commandType: CommandType.StoredProcedure);
		conn.Close();
	}
}

c. Give valid JSON input in Swagger and execute this method.

Confirm the record is successfully inserted or updated in the database or execute the Get() API method again to check the updated data. The below screenshots shows both results.

Insert JSON Input:

{
  "product_code": 300,
  "product_name": "Bag",
  "product_price": 10.00
}

Update JSON Input: This will modify the name and price of the product for ‘id’ 3.

{
  "product_id": 3,
  "product_code": 300,
  "product_name": "Bag 2",
  "product_price": 15.00
}

“Delete” example using Dapper and Stored Procedure

a. Create a stored procedure that deletes a record by ‘id’

CREATE PROCEDURE SP_ProductById
(
	@Id INT
)
AS
BEGIN
	DELETE Products WHERE ID = @Id
END 

b. Create a delete API method that accepts ‘id’ as a parameter.

[HttpDelete("{id}")]
public void Delete(int id)
{
	//Dynamic parameters
	var inputparameters = new DynamicParameters();
	inputparameters.Add("@Id", id);

	using (SqlConnection conn = new SqlConnection(_conn))
	{
		conn.Open();
		string spName = "SP_DeleteProductById";
		conn.Query(spName, param: inputparameters, commandType: CommandType.StoredProcedure);
		conn.Close();
	}
}

c. Execute the delete API from swagger. Provide input parameter (‘id’) as 3.

Execute ‘the Select’ query in the database or run the Get() API again to confirm it’s deleted.

Thanks for reading!! Happy coding 🙂