How to Get Data From Database with gRPC Protobuf in ASP.NET Core 7.0

Introduction

gRPC (Google Remote Procedure Call) is an open-source framework developed by Google for building high-performance and efficient inter-service communication. It uses Protocol Buffers (Protobuf) as its default data serialization format. When it comes to fetching data from a database in an ASP.NET Core application, gRPC combined with Protobuf offers an exceptionally efficient solution. In this tutorial, we will explore how to leverage gRPC and Protobuf to optimize data fetching in your ASP.NET Core projects.

Setting Up Your Development Environment

To get started, make sure you have Visual Studio 2022 installed on your development machine. If you don't have it yet, you can download it from the official Visual Studio website.

Creating Your ASP.NET Core gRPC Project

• Open Visual Studio 2022.
• Click on "Create a new project."
• Select "ASP.NET Core gRPC Service."

• Choose a project name and location, then click "Create."

You now have the foundation of your ASP.NET gRPC Service project ready to go.

Defining Your gRPC Service Methods

In this example you have to add .proto file into the Protos folder as like below

In the .proto file, define the service methods you need. For fetching data, you might have a method like this:

syntax = "proto3";

option csharp_namespace = "GrpcService";

package Employee;

message EmployeeRequest{
	int32 ID =1;
}

message EmployeeResponse{
	string Email = 1;
	string Emp_Name = 2;
	string Designation =3;
}

message EmployeeListResponse{
	repeated EmployeeResponse employees = 1;
}

service EmployeeDetails{
	rpc GetEmployee(EmployeeRequest) returns (EmployeeListResponse);
}

In above .proto option csharp_namespace is the namespace name in which code is generated after build project.

After building the project, you can see the generated classes under below folder.

Defining Your Data Model and Fetch Data

In this example, let's assume you want to fetch data from a database containing information about Employee. You'll need a data model to represent this information and a method to fetch data from database.

SQL script to create table

CREATE TABLE [dbo].[tbl_Employee] (
    [ID]           INT     Primary Key  ,
    [Email]        NVARCHAR (200) ,
    [Emp_Name]     NVARCHAR (200) ,
    [Designation]  NVARCHAR (200) ,
    [Created_date] DATE DEFAULT (getdate()) 
);

Stored procedure to fetch data from table

CREATE proc Sp_EmployeeGet  
@Id int 
as  
begin  
  SELECT ID, Email, Emp_Name, Designation FROM tbl_Employee where ID = @Id  ORDER BY ID desc       
End

Create a class for your Employee model, like so:

using System.Data;
using System.Data.SqlClient;

namespace GrpcService.data
{
    public class EmployeeRepository
    {
        private string connectionString = "Data Source=LAPTOP-DBC342FV;Initial Catalog=CsharpSpace;Integrated Security=True"; // Replace with your connection string

        public List<Employee> GetEmployees(int ID)
        {
            List<Employee> employees = new List<Employee>();
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

                using (SqlCommand command = new SqlCommand("Sp_EmployeeGet", connection))
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.AddWithValue("@Id", ID);

                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Employee employee = new Employee
                            {
                                ID = Convert.ToInt32(reader["ID"]),
                                Email = reader["Email"].ToString(),
                                Emp_Name = reader["Emp_Name"].ToString(),
                                Designation = reader["Designation"].ToString()
                            };
                            employees.Add(employee);
                        }
                    }
                }
            }
            return employees;
        }
    }

    public class Employee
    {
        public int ID { get; set; }
        public string? Email { get; set; }
        public string? Emp_Name { get; set; }
        public string? Designation { get; set; }
    }
}

Implementing Your gRPC Service

In the generated service class, implement the methods you defined in the .proto file. For fetching data from a database, you'll need to call GetEmployees method from EmployeeRepository class and return the results.

Here's an example of how your service might look:

using Grpc.Core;
using GrpcService.data;

namespace GrpcService.Services
{
    public class EmployeeService : EmployeeDetails.EmployeeDetailsBase
    {
        EmployeeRepository employeeRepo = new EmployeeRepository();

        public override Task<EmployeeListResponse> GetEmployee(EmployeeRequest request, ServerCallContext context)
        {
            var employess = employeeRepo.GetEmployees(request.ID);

            var employeeReponses = new List<EmployeeResponse>();
            foreach (var employee in employess)
            {
                employeeReponses.Add(new EmployeeResponse
                {
                    Email = employee.Email,
                    EmpName = employee.Emp_Name,
                    Designation = employee.Designation
                });
            }
            var employeeListresponse =  new EmployeeListResponse
            {
                Employees  = { employeeReponses}
            };
            return Task.FromResult(employeeListresponse);
        }
    }
}

Testing Your gRPC Service

To test your gRPC service, you can create a gRPC client or use tools like BloomRPC. Make a request to your service and check if you receive the data from your database correctly.

To test gRPC service with ASP.NET Core client, Add project for ASP.NET Core

Creating ASP.NET Core Project

• Click on "Add a new project."
• Select "ASP.NET Core Web API."

For test Employee service you need to add controller EmployeeController and call EmployeeService to fetch the data as like below

using GrpcService;
using GrpcService.Services;
using Microsoft.AspNetCore.Mvc;

namespace TestGRPC.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class EmployeeController : ControllerBase
    {
        EmployeeService employeeService = new EmployeeService();
        [HttpGet("GetEmployeeList")]
        public EmployeeListResponse GetEmployeeList(int id)
        {
            EmployeeRequest employeeRequest = new EmployeeRequest();
            employeeRequest.ID = id;
            return employeeService.GetEmployee(employeeRequest, null).Result;
        }
    }
}

Set the startup as ASP.NET Core Web API project and the run the application

Conclusion

By combining the power of gRPC and Protobuf in your ASP.NET Core project, you can efficiently fetch data from a database. This setup not only improves data transfer speed but also ensures compatibility across various platforms and languages.

Explore more possibilities with gRPC and Protobuf in your ASP.NET Core projects, and stay tuned for more tutorials and tips here at Voidgeeks.com. Happy coding!