Working With Parameters In Dapper ORM

Introduction 

In this article, I'm going to explain how we can work with parameters while working with Dapper. In this article, I'll create a table and some procedures with IN, OUT, and Return parameters, and we will fetch values from those parameters in our .NET 7 API. So let's start it.

First I'm going to create a table with the name BookStore with fields book_Id, book_title, and book_description 

CREATE TABLE BookStore (book_Id INT IDENTITY PRIMARY KEy,book_title VARCHAR(20)
, book_description VARCHAR(255));

Let's insert some test values

INSERT INTO BookStore(book_title,book_description) VALUES('C#','Everything about C-sharp');
INSERT INTO BookStore(book_title,book_description) VALUES('Python','Everything about Python');
INSERT INTO BookStore(book_title,book_description)  VALUES('java','learn Java in3 months');
INSERT INTO BookStore(book_title,book_description)  VALUES('js','Become Master in javascript');
INSERT INTO BookStore(book_title,book_description)  VALUES('C','clear basics of C');

We are going to work with parameters so, I am going to create 3 procedures that will return, insert and remove data from the table and throw the status parameters. First I'm going to create a procedure that selects all the data from the table.

CREATE PROCEDURE Get_books
AS
BEGIN
SELECT *FROM BookStore
END

Another procedure to add books with the OUT parameter, if a book is added then the @status will be 1, if the book already exists then the @status will be 2 

CREATE PROCEDURE Add_Book(
@Book_name VARCHAR(20),
@Book_DESC VARCHAR(255),
@Status INT OUT
)
AS
BEGIN
IF NOT EXISTS(SELECT book_title  FROM BookStore WHERE book_title=@Book_name)
BEGIN
INSERT INTO BookStore(book_title,book_description)  VALUES(@Book_name,@Book_DESC);
SET @Status=1
END
ELSE
BEGIN
SET @Status=2
END
END

And another Procedure to remove the existing book with the Return Parameter. It will delete the book if the book_Id exists in the database and return @Status=1 after deletion otherwise returns @Status=2.

CREATE PROCEDURE Remove_Book(
@Book_id INT,
@Status INT=0
)
AS
BEGIN
IF  EXISTS(SELECT book_Id  FROM BookStore WHERE book_Id=@Book_id)
BEGIN
DELETE FROM  BookStore WHERE book_Id=@Book_id
SET @Status=1
RETURN @Status
END
ELSE
BEGIN
SET @Status=2
RETURN @Status
END
END

Now let's create an API project to fetch the values with Dapper.

Open Visual Studio and click on create a new project. Currently, I'm using Visual Studio 2022 you can use what you have installed on your machine.

Figure-Creating new Project

Next just choose the API as a project template you can use the same approach for others. NET templates, I'm using API because I'm not too good with designing UI and entering the project name based on your interests, but I'll always suggest using some meaningful names.

Figure-Project name

Next chose the .net version, I'll suggest using the latest one available. I have used the.NET 7 and click on create button and your project is ready with the default controllers. We are going to use dapper. First we need to add dapper to our project. There are two packages required when we use the first one Dapper and the second System.Data.SqlClient. Right-click on the project name and click on manage NuGet Package manager and add both packages

Figure-Required packages

Our Project is now ready to work with Dapper ORM, let's add a controller. Right-click on the controller folder >> then Add >> Controller.

Figure-Adding controller

Then choose the empty controller and enter the project name and click on add button

Figure- Add new Controller

Now we need to establish the connection with the database, So I'm creating a method that returns a connection

private IDbConnection CreateConnection() => new SqlConnection("server=YOUR SERVER NAME;Initial Catalog=YOUR DATABASE;User ID=YOUR USER NAME;Password= YOUR Password");
}

In this code, you have to use your credentials. Now our connection is ready so let's create our first call to get all books 

[HttpGet][Route("GetAllBooks")]
public async Task < IActionResult > GetAllBooks() {
    var books = await GetBooks();
    return Ok(books);
}

I have created a method with the name Getbooks(), That will return all the books from the table.

private async Task < IEnumerable < Books >> GetBooks() {
    IEnumerable < Books > BOOKS;
    try {
        using(var connection = CreateConnection()) {
            var procedure = "Get_books";
            BOOKS = await connection.QueryAsync < Books > (procedure, null, commandType: CommandType.StoredProcedure);
            return BOOKS;
        }
    } catch (Exception) {
        throw;
    };
}

This is an async method that returns all books to the controller.

Second I have created a call to add a new book

[HttpPost][Route("AddNewBooks")]
public async Task < IActionResult > AddBooks(Books Book) {
    var status = await AddBook(Book);
    if (status == 1) {
        return Ok("New book Added successfully status is " + status);
    } else if (status == 2) {
        return Ok("This book is alrady exist status is " + status);
    } else {
        return Ok("somthing went wrong");
    }
}

Fetching values with Out Parameter

Here I have used the method AddBook() which will add a new book to the database and return the status to the user with the Out parameter.

private async Task < Int16 > AddBook(Books book) {
    try {
        using(var connection = CreateConnection()) {
            var procedure = "Add_Book";
            var dynamicParameters = new DynamicParameters();
            dynamicParameters.Add("@Book_name", book.book_title, DbType.String, direction: ParameterDirection.Input);
            dynamicParameters.Add("@Book_DESC", book.book_description, DbType.String, direction: ParameterDirection.Input);
            dynamicParameters.Add("@Status", 1, DbType.Int16, direction: ParameterDirection.Output);
            await connection.ExecuteScalarAsync(procedure, dynamicParameters, commandType: CommandType.StoredProcedure);
            var status = dynamicParameters.Get < Int16 > ("@Status");
            return status;
        }
    } catch (Exception) {
        throw;
    };
}

Here we have used dynamic parameters with direction: ParameterDirection.Output, it will indicate that the parameter is used as an OUT parameter then we have just used Get<> method to get the value, but remember that the type must be similar to the database within the <>. 

Figure-Dapper out parameter

Here.

Step 1

We made a request to add a new book with a title and description.

Step 2

The request comes to the controller and the controller sends it to the add book method here we have set the parameter type as output.

Step 3

We have executed the query with the help of connection and ExicuteScalarAsync.

Step 4

Throw the connection our query executes in the database and the OUT parameter will notify us back.

Step 5

Here we can see that we got our @Status.

Step 6

In the last step, we can see that based on @Status controller send a success message.

Next, I created a call to remove the book from the database.

[HttpPost]
[Route("RemoveBook")]
public async Task < IActionResult > RemoveBook(int bookId) {
    var status = await remove_Book(bookId);
    if (status == 1) {
        return Ok("Book Delerted successfully status is " + status);
    } else if (status == 2) {
        return Ok("Invalid book id status is " + status);
    } else {
        return Ok("somthing went wrong");
    }
}

Fetching values with Return Parameter

Here I have used a method remove_Book() that will return the status to the user with the Return Parameter

private async Task < int > remove_Book(int bookId) {
  try {
    using(var connection = CreateConnection()) {
      var procedure = "Remove_Book";
      var dynamicParameters = new DynamicParameters();
      dynamicParameters.Add("@Book_id", bookId, DbType.Int16, direction: ParameterDirection.Input);
      dynamicParameters.Add("@Status", 0, DbType.Int32, direction: ParameterDirection.ReturnValue);
      await connection.ExecuteScalarAsync(procedure, dynamicParameters, commandType: CommandType.StoredProcedure);
      var status = dynamicParameters.Get < int > ("@Status");
      return status;
    }
  } catch (Exception) {
    throw;
  };
}

Here I have defined @Status as a return value, "direction: ParameterDirection.ReturnValue" will indicate that the parameter will behave like a return parameter then simply we can get values back in the same way. After successful execution, the controller generates a message for the response based on the status return by the parameter.

Conclusion

In this article, I have explained how we can work very easily with the IN, OUT, and Return parameters. For your better understanding, I have attached the source code of the project. I have used a simple project architecture for your practice and understanding but I will suggest you use a repository pattern if you are going to use it on a major project.