In a previous blog, we explained CRUD Operations, Defining Model classes, implementing controllers, and Handling HTTP requests and responses. apart from that Coding the stored procedures will help us go through with the dapper ORM and NUMBER projects. We'll discuss, step by step, how to install Dapper via NuGet Package Manager and the definition of the models and execute stored procedures. Let's dive in!

Step 1: Installing Dapper via NuGet Package Manager  

  1. Open Visual Studio: Launch Visual Studio and open your project.  
  2. Access NuGet Package Manager: Go to "Tools" > "NuGet Package Manager" > "Manage NuGet Packages for Solution...".  
  3. Search for Dapper: In the NuGet Package Manager window, get Dapper by typing "Dapper" in the search area and pressing "Enter".  
  4. Install Dapper: Once the last update of Dapper is found, break for installation by clicking the "Install" button to include the library in the project basis.
  5. Verify Installation: To make sure that Dapper is installed successfully, verify the references that are in the project.

Step 2: Setting Up Database Stored Procedures  

  • Connect to Your Database: Use your chosen client which can be either SQL GUI or SQL query in the terminal to establish a connection with your database server.

  • Create a Stored Procedure: Design a stored procedure in your database. Let us take GetEmployees as a case, which will fetch employee data as a simple procedure defined.

  • Execute the SQL Script: Run the SQL script, which calls for the creation of a stored procedure of your database.

-- Connect to Your Database and execute the following SQL script
CREATE PROCEDURE GetEmployees
AS
BEGIN
    SELECT Id, Name, Department
    FROM Employees;
END 
  • This SQL script will perform the following operations involving the procedure with the name GetEmployees in the connected database. Let's break down each part of the script:
  1. CREATE PROCEDURE GetEmployees: In this line thus establishing an object StoredProcedure of a type GetEmployees. PL/SQL has a variety of different procedures the most commonly used is CREATE PROCEDURE. Developing the procedure which will be known as GetEmployees is the task at hand for us.
  2. AS: This keyword, along with the rest of the procedure's name, indicates that the procedure's definition is at a different location. In this slot's position, anything after AS refers to the procedure's obvious.
  3. BEGIN: In the body section of your writing, the introduction comes first. It contains the beginning mark of the SQL language block which expresses the procedure that should be run when calling the procedure.
  4. SELECT Id, Name, Department FROM Employees; Executing an SQL command is the core of the stored procedure. It is the data selection from the Employee table based on the given condition. I am using the columns 'Id', 'Name', and 'Department'.
  5. END: Marks the last line of the procedure's body. It shows the finish line of the block that is the culmination of the whole procedure's logic.

Step 3: Defining Model Classes

  •  Create Model Classes: Declare C# classes that will contain the data that will be returned by the stored procedures. For example, create an `Employee` class: 

public class Employee     
{         
        public int Id { get; set; }         
        public string Name { get; set; }         
        public string Department { get; set; }         
        // Additional properties...     
}
  1. public class: This declares a class named Employee that would be accessible outside its containing assembly, thus this.
  2. public int Id { get; set; }: This is an expression of a public property Id of type int in the Employee Class. This attribute is the defining element that signifies through a specific value who an employee is.
  3. public string Name { get; set; }: This line determines another public field, which is a string type member in the Employee class, is named Name. Having such a variable is setting the name of the employee.
  4. public string Department { get; set; }: It is the following next Public Attribute that is assigned as a value of type String Department within the Employee class. The above attribute can be understood as a designation of a person's department within the organization.
  5. // Additional properties...: This is a comment demonstrating the possibility of some properties not being explicitly mentioned in the current set of code snippets while being existent within the given Employee class.

Step 4: Accessing Database with Dapper ORM  

  • Create a Data Access Class: In this class, you should use a Dapper ORM for DLL to interact with the database. For example, let's create an `EmployeeRepository` class:

using System.Collections.Generic;     
using System.Data;     
using System.Data.SqlClient;     
using Dapper;      
public class EmployeeRepository     
{         
        private readonly string _connectionString;          
        public EmployeeRepository(string connectionString)         
        {             
              _connectionString = connectionString;         
        }          
        public IEnumerable<Employee> GetEmployees()         
        {             
              using (IDbConnection db = new SqlConnection(_connectionString))             
              {                 
                    return db.Query<Employee>("GetEmployees", commandType:                                                CommandType.StoredProcedure);             
              }         
        }     
}
  1. first 4 lines: These are the namespaces that are responsible for the functioning of the variables defined in the Employee Repository class. These namespaces contain generics such as System.Collections.Generic, ADO.NET Pieces like System.Data and Sql Server Specific data access are like System.Data.SqlClient and Dapper ORM are used by Dapper.
  2. public class: The namespace MyApplication declares the class EmployeeRepository which is defined in this assembly and therefore can be innely accessed.
  3. private readonly string _connectionString: It's within the EmployeeRepository class that this sentence declares a private readonly string field _connectionString. This is a specialized command line field used for putting the link connection required to log in to the database.
  4. public EmployeeRepository(string connectionString): This is the constructor method belonging to the EmployeeRepository class. The parameter name connectionString is of type 'string' that contains the connection string that we needed to connect to the database.
  5.  ConnectionString in the _connectionString field is assigned a value supplied upon instance creation of the EmployeeRepository class.
  6. public IEnumerable<Employee> GetEmployees(): This technique is considered to be an efficient method for picking employees from a database. It returns an IEnumerable<Employee> which represents a collection of Employee objects populated by the database.
  7. using (IDbConnection db = new SqlConnection(_connectionString)): This line will create a new SqlConnection object using the _connectionString field and wrap it in a using statement catching any possible disposal after this line. IDbConnection is an interface that represents a connection to a data source.
  8. return db.Query<Employee>("GetEmployees", commandType: (using (IDbConnection db = new SqlConnection(connectionString))): In the using block, this line employs Dapper's `Query` method to pass the stored procedure named `GetEmployees` that contain the results from the database and turned into a collection of Employee objects. The stored procedure of CommandType reflects that GetEmployees is a stored procedure.
  • Inject Connection String: Something to be done is that the `EmployeeRepository` must either be injected using the constructor or the configuration.

Step 5: Interacting with Dapper via the Stored Procedures

  • Invoke Stored Procedure: The employee repositor will do a select query and his arrays of employee data using the `GetEmployees` stored procedure.

var connectionString = "your_connection_string_here";     
var employeeRepository = new EmployeeRepository(connectionString);     
var employees = employeeRepository.GetEmployees(); 
  1. Here line creates a variable called connectionString and sets it to string value which is the database connection string needed to connect to the database. You can transform that phrase with "your_connection_string_here" by using the connection string to which you are trying to connect your database.
  2. This line indicates the creation of an EmployeeRepository class instance by employing the class constructor and passing it to the connectionString as a parameter. To satisfy the first requirement the EmployeeRepository object is ininitialized as the parameter required to connect the database is required.
  3. This line invokes the GetEmployees method of the employeeRepository object. This method retrieves a collection of employees from the database using the connection string provided while creating the EmployeeRepository object. The retrieved employees are then stored in the employees variable.
  • Process Query Results: Process the results produced in your application by the stored procedure as you require.

Congratulations! And now you have Dapper ORM with the stored procedure all setup and maintained with your .NET project. Therefore, by going through this extensive manual you can get a superior knowledge to set up Dapper, prescribe models, write stored procedures, and run it using Dapper ORM. The Dynamic Object Model – ORM and Stored Procedures – provides you with all the tools that you need to build applications in .NET that are both efficient and scalable.
 

Recent Posts