Azure Functions CRUD operation using Dapper Service

-


 Azure Functions CRUD operation using Dapper Service


We are using Dapper for CRUD operation in our azure function. We have the CronDapperService
class which is implementing the ICronDapperService

The interface ICronDapperService has the following declarations.

using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Text;
using System.Threading.Tasks;

namespace DemofunctionApp.Services.DapperService
{
    public interface ICronDapperService : IDisposable
    {
        DbConnection GetDbconnection();
        T Get<T>(string sp, DynamicParameters parms, CommandType commandType = CommandType.StoredProcedure);
        Task<IEnumerable<T>> GetAll<T>(string sp, DynamicParameters parms, CommandType commandType = CommandType.StoredProcedure);
        Task<int> Execute(string sp, DynamicParameters parms, CommandType commandType = CommandType.StoredProcedure);
        T Insert<T>(string sp, DynamicParameters parms, CommandType commandType = CommandType.StoredProcedure);
        Task<IEnumerable<T>> Update<T>(string sp, DynamicParameters parms, CommandType commandType = CommandType.StoredProcedure);

    }
}







And CronDapperService is implementing the ICronDapperService  interface

using Dapper;
using Microsoft.Extensions.Configuration;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DemofunctionApp.Services.DapperService
{
    public class CronDapperService : ICronDapperService
    {
        private readonly IConfiguration _config;
        private string Connectionstring = "SyncConnectionString";

        public CronDapperService(IConfiguration config)
        {
            _config = config;
        }
        public void Dispose()
        {

        }

        public async Task<int> Execute(string sp, DynamicParameters parms, CommandType commandType = CommandType.StoredProcedure)
        {
            int result = 0;
            using (IDbConnection db = new SqlConnection(_config.GetConnectionString(Connectionstring)))
            {
                if (db.State == ConnectionState.Closed)
                    db.Open();

                result = await db.ExecuteAsync(sp, parms, commandType: commandType);
            }
            return result;
        }

        public T Get<T>(string sp, DynamicParameters parms, CommandType commandType = CommandType.Text)
        {
            using IDbConnection db = new SqlConnection(_config.GetConnectionString(Connectionstring));
            return db.Query<T>(sp, parms, commandType: commandType).FirstOrDefault();
        }

        public async Task<IEnumerable<T>> GetAll<T>(string sp, DynamicParameters parms, CommandType commandType = CommandType.StoredProcedure)
        {
            using IDbConnection db = new SqlConnection(_config.GetConnectionString(Connectionstring));
            return await db.QueryAsync<T>(sp, parms, commandType: commandType);
        }

        public DbConnection GetDbconnection()
        {
            return new SqlConnection(_config.GetConnectionString(Connectionstring));
        }

        public T Insert<T>(string sp, DynamicParameters parms, CommandType commandType = CommandType.StoredProcedure)
        {
            T result;
            using IDbConnection db = new SqlConnection(_config.GetConnectionString(Connectionstring));
            try
            {
                if (db.State == ConnectionState.Closed)
                    db.Open();

                using var tran = db.BeginTransaction();
                try
                {
                    result = db.Query<T>(sp, parms, commandType: commandType, transaction: tran).FirstOrDefault();
                    tran.Commit();
                }
                catch (Exception ex)
                {
                    tran.Rollback();
                    throw ex;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (db.State == ConnectionState.Open)
                    db.Close();
            }

            return result;
        }

        public async Task<IEnumerable<T>> Update<T>(string sp, DynamicParameters parms, CommandType commandType = CommandType.StoredProcedure)
        {
            IEnumerable<T> result;
            using IDbConnection db = new SqlConnection(_config.GetConnectionString(Connectionstring));
            try
            {
                if (db.State == ConnectionState.Closed)
                    db.Open();

                using var tran = db.BeginTransaction();
                try
                {
                    result = await db.QueryAsync<T>(sp, parms, commandType: commandType, transaction: tran);
                    tran.Commit();
                }
                catch (Exception ex)
                {
                    tran.Rollback();
                    throw ex;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (db.State == ConnectionState.Open)
                    db.Close();
            }

            return result;
        }
    }
}

SyncConnectionString is the conectionstring mentioned in the local.setting.json file

{
  "IsEncrypted": false,
  "Values": {
    "AzureWebJobsStorage": "UseDevelopmentStorage=true",
    "FUNCTIONS_WORKER_RUNTIME": "dotnet",
    "Time": "0 */1 * * * *"
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "SyncConnectionString": "Server=DIPENJIT-PC;Initial Catalog=DEMO_CMS_AZURE_FUNCTION;MultipleActiveResultSets=true;integrated security=true;ConnectRetryCount=0"
   

  }
}




Comments

Post a Comment

Popular posts from this blog

Top Microsoft Technology Trends 2022

-
Image
    Top Microsoft Technology Trends 2022     In today's challenging business landscape, it is constantly evolving technology. According to Forbes, after 2018, more than 50% of global IT spending towards cloud-based.  Microsoft also offers various certification courses to train professionals  in these new technologies.  Organizations, including governments, enterprises, and small businesses, are modernizing and innovating using cloud services. The pandemic, in a way, has demonstrated that digitally fortified firms are more adaptable, resilient, and capable of transforming when confronted with a crisis. Simulation, automation, and remote everything is becoming a reality with the move to a digital first world.  The first trend is that every business is becoming a digital business and building your own digital capability will be crucial. Getting certified in these new technologies can give us the skills needed to create a highly scalab...
Read more

Dependency injection in .NET Azure Functions

-
Image
  Dependency injection in .NET Azure Functions Azure Functions supports the dependency injection (DI) software design pattern, which is a technique to achieve  Inversion of Control (IoC)  between classes and their dependencies. Dependency injection in Azure Functions is built on the .NET Core Dependency Injection features. 1.Register services To register services, create a method to configure and add components to an  IFunctionsHostBuilder  instance. The Azure Functions host creates an instance of  IFunctionsHostBuilder  and passes it directly into your method. To register the method, add the  FunctionsStartup  assembly attribute that specifies the type name used during startup. using DemofunctionApp.Services.CronService; using DemofunctionApp.Services.DapperService; using Microsoft.Azure.Functions.Extensions.DependencyInjection; using Microsoft.Extensions.DependencyInjection; using System; using System.Collections.Generic; using System.Text;...
Read more