1

I have a class that inserts and loads any model from a SQL Server:

public class SqlDataAccess : ISqlDataAccess
{
    private readonly IConfiguration _configuration;

    public SqlDataAccess(IConfiguration configuration)
    {
        _configuration = configuration;
    }

    public async Task<IEnumerable<T>> LoadData<T, U>(string storedProcedure,
                                                     U parameters,
                                                     string connectionId = "Default")
    {
        using IDbConnection connection = new SqlConnection(_configuration.GetConnectionString(connectionId));

        return await connection.QueryAsync<T>(storedProcedure,
                                              parameters,
                                              commandType: CommandType.StoredProcedure);
    }

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

        await connection.ExecuteAsync(storedProcedure,
                                      parameters,
                                      commandType: CommandType.StoredProcedure);
    }
}

They are all functions calling stored procedures, for example:

CREATE PROCEDURE [dbo].[spAuthors_Insert]
    @FirstName NVARCHAR(46),
    @LastName NVARCHAR(46)
AS
BEGIN
    INSERT INTO [dbo].[Authors] (FirstName, LastName)
    VALUES (@FirstName, @LastName);
END

Which uses the following table:

CREATE TABLE [dbo].[Authors]
(
    [Id] INT NOT NULL PRIMARY KEY IDENTITY,
    [FirstName] NVARCHAR(46),
    [LastName] NVARCHAR(46)
)

To insert an author, I use the following function:

public Task InsertAuthor(AuthorModel author) => _db.SaveData("dbo.spAuthors_Insert", new { author.FirstName, author.LastName });

Now I need to change so that when I insert an author it returns the register with the id, I've tried to change for QueryFirstAsync but it doesn't return the model back. What I can change to make this generic function return the saved register?

Also on a side note, is it possible on the LoadData function pass multiple models so I can split them when I use a stored procedure with joins like:

CREATE PROCEDURE [dbo].[spArts_GetByUserId]
    @Id NVARCHAR(256)
AS
BEGIN
    SELECT *
    FROM [dbo].[Arts]
    INNER JOIN [dbo].[Registrations] ON [dbo].[Arts].id = [dbo].[Registrations].ArtId
    INNER JOIN [dbo].[Arts_details] ON [dbo].[Arts].Arts_details_id = [dbo].[Arts_details].Id
    INNER JOIN [dbo].[Authors] ON [dbo].[Arts_details].AuthorId = [dbo].[Authors].Id
    WHERE UserId = @Id;
END

An example of a simple get:

public async Task<ArtsModel?> GetArt(int id)
{
    var result_art = await _db.LoadData<ArtsModel, dynamic>("dbo.spArts_Get", new { Id = id });
    var art = result_art.FirstOrDefault();

    return art;
}
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Paulo Henrique
  • 100
  • 9
  • I have no idea about your questions, but you have multiple columns named `Id` and a `SELECT *` query returns all of them. How does the consuming code know which one is which? Suggestions: (a) [Don't use `SELECT *`](https://sqlblog.org/2009/10/10/bad-habits-to-kick-using-select-omitting-the-column-list#insert). (b) Don't give identifier columns in your data model ambiguous names like `Id`. Is it a `UserId`? Cool. Call it that everywhere. (c) `WHERE UserId = @Id` <-- what table does that come from? Alias your tables, then use the alias on _all_ column references. Think about future maintainers. – Aaron Bertrand Jul 28 '22 at 21:36
  • To get back inserted data you can use the [`output`](https://learn.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql?view=sql-server-ver16) clause. This can return mulitple rows if desired, so you can read it back into a `List`. To insert multiple rows at once, or read multiple rows at once from a parameter and `join` operation, use a [table valued parameter](https://learn.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters-database-engine?view=sql-server-ver16) in the stored procedure. – allmhuran Jul 28 '22 at 21:38
  • Agreed. spArts_GetByUserId , you should ambiguously list out EACH column you want. and not use *. – granadaCoder Jul 28 '22 at 21:45
  • I suggest putting your "side note" as a new and different question. – granadaCoder Jul 28 '22 at 21:56

1 Answers1

1

(Because your 'spAuthors_Insert' is SINGLE ROW based (and not set based), you can do the below).

Your stored procedure needs to perform a SELECT. (as its very last instruction). A select using one of the three "identity" functions below.

ONE of the three below : (and probably in order of preference, see : What is the difference between Scope_Identity(), Identity(), @@Identity, and Ident_Current()? )

SELECT SCOPE_IDENTITY()

or

SELECT IDENT_CURRENT('tablename')

or

SELECT @@IDENTITY

(where your table name is 'Authors')

..

Then your ExecuteAsync

probably needs to be

https://learn.microsoft.com/en-us/dotnet/api/system.data.common.dbcommand.executescalarasync?view=net-6.0

ExecuteScalar(Async)

Because you need to get that single column/row value.

From the documentation:

An asynchronous version of ExecuteScalar(), which executes the command and returns the first column of the first row in the first returned result set. All other columns, rows and result sets are ignored.

Note the "first column of the first row". (and do not miss the "first returned result set" as well).

(Aka, make sure the ONLY "Select" in the stored procedure is one of the three IDENTITY functions above.

And to "dot the i" on the c# code.

Task SaveData<T>(

I would change to something like:

Task<long> SaveSingle<T>(

or maybe

Task<K> SaveSingle<T,K>(

(the above is the intention, I haven't tried out the generics definition on the method.

where K is "int" or "long" (INT,BIGINT in ms-sql-server)....and you return the new PrimaryKey.

And cast your result of .ExecuteScalarAsync as long or K.

granadaCoder
  • 26,328
  • 10
  • 113
  • 146