Inserting a row with .NET 6 and Dapper

Posted on: Wednesday, Dec 15, 2021

Interface

namespace DataAccess.DBAccess;

public interface IArtistSqlDataAccess
{
    Task<int> SaveDataWithReturn<T>(
        string storedProcedure,
        T parameters,
        string connectionId = "Default");
}

Implementation

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

namespace DataAccess.DBAccess;

public class ArtistSqlDataAccess : IArtistSqlDataAccess
{
    private readonly IConfiguration _config;

    public ArtistSqlDataAccess (IConfiguration config)
    {
        _config = config;
    }

    public async Task<int> SaveDataWithReturn<T>(string storedProcedure,
                                  T parameters,
                                  string connectionId = "Default")
    {
        using IDbConnection connection = new SqlConnection(
            _config.GetConnectionString(connectionId));

        int id = await connection.ExecuteScalarAsync<int>(
            storedProcedure,
            parameters,
            commandType: CommandType.StoredProcedure);
        return id;
    }
}

Interface

namespace DataAccess.Data;

public interface IArtistCrud
{
    Task<int> InsertArtist(ArtistModel model);
}

Implementation

using DataAccess.DBAccess;
using DataAccess.Models;

namespace DataAccess.Data;

public class ArtistCrud: IArtistCrud
{
    private readonly IArtistSqlDataAccess _db;

    public ArtistCrud(IArtistSqlDataAccess db)
    {
        _db = db;
    }

    public async Task<int> InsertArtist(ArtistModel model) 
    {
        int id = await _db.SaveDataWithReturn(
            "dbo.artist_Insert",
            model);
        return id;        
    }        
}

Stored procedure

CREATE PROCEDURE [dbo].[Artist_Insert]
@Id int,
@Name nvarchar(100)
AS
BEGIN
    SET NOCOUNT ON
    INSERT INTO [dbo].[Artist] 
    (
[Name]
    ) 
    VALUES(
@Name
    );
END
SELECT CAST(SCOPE_IDENTITY() as int) as 'id'
GO    

Code to insert row

private static async Task<IResult> InsertArtist(
    ArtistModel model,
    IArtistCrud data,
    HttpResponse resp)
{
    try
    {
        var results = await data.GetArtist(model.Id);
        if (results != null)
        {
            resp.Headers.Add(
                "App-Response-Info",
                $"Row with key '{model.Id}' already exists.");
            return Results.Conflict(results);
        }

        int id = await data.InsertArtist(model);
        resp.Headers.Add("App-New-Row-Id", id.ToString()); ;
        return Results.Ok(id);
    }
    catch (Exception ex)
    {
        return Results.Problem(ex.Message);
    }
}



Add your comment
You email is never shared with anyone else.

© Copyright 2017 by Roger Pence. All rights reserved.