Base solution for your next web application
Open Closed

How can i call function or store procedure from SQL server #3954

User avatar
0
kwanp created

How can i call function or store procedure from SQL server with this sample <a class="postlink" href="https://aspnetzero.com/Documents/Developing-Step-By-Step-Angular">https://aspnetzero.com/Documents/Develo ... ep-Angular</a>

Please give some guideline or sample coding to call function or store procedure from SQL server

my version: ASP.NET Core & Angular

Thank you

Go to accepted answer
12 Answer(s)
  • User Avatar
    0
    aaron created
    Support Team

    Here is an example that sends a parameter to a stored procedure to delete a user:

    public async Task DeleteUser(EntityDto input)
{
   await Context.Database.ExecuteSqlCommandAsync(
       "EXEC DeleteUserById @id",
       default(CancellationToken),
       new SqlParameter("id", input.Id)
   );
}

    Using Stored Procedure, User Defined Function and Views in a Custom Repository with ASP.NET Boilerplate: https://www.codeproject.com/Articles/1199648/Using-Stored-Procedure-User-Defined-Function-and-V

    Source code is published on Github: https://github.com/aspnetboilerplate/aspnetboilerplate-samples/tree/master/StoredProcedureDemo

  • User Avatar
    0
    kwanp created

    Hi aaron after i follow on this sample link: <a class="postlink" href="https://www.codeproject.com/Articles/1199648/Using-Stored-Procedure-User-Defined-Function-and-V">https://www.codeproject.com/Articles/11 ... tion-and-V</a>

    The service to call store procedure that i just create not show on swagger

    how can i fix this problem

    my version for my project: ASP.NET Core & Angular

    Thank you

  • User Avatar
    0
    aaron created
    Support Team

    Can you show your AppService? Remember to add the methods to your interface too.

  • User Avatar
    0
    kwanp created

    Hi aaron This is my sample Code

    using Abp.Application.Services;
using Abp.Application.Services.Dto;
using Abp.Domain.Repositories;
using KWANP.Dbmtab.Dto;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace KWANP.Dbmtab
{
    public interface IDbmtabStorePAppService : IApplicationService
    {
        Task<string> GetUserNames();
    }
}

    using Abp.Data;
using System.Data.Common;
using Microsoft.EntityFrameworkCore;
using System.Data;
using System.Data.SqlClient;
using Abp.Application.Services.Dto;
using KWANP.Dbmtab.Dto;
using AutoMapper;

namespace KWANP.Dbmtab
{
    public class DbmtabStorePAppService : KWANPRepositoryBase<Dbmtab00>, IDbmtabStorePAppService
    {
        private readonly IActiveTransactionProvider _transactionProvider;
        public DbmtabStorePAppService(IDbContextProvider<KWANPDbContext> dbContextProvider, IActiveTransactionProvider transactionProvider) : base(dbContextProvider)
        {
            _transactionProvider = transactionProvider;
        }


        public async Task<string> GetUserNames()
        {
            EnsureConnectionOpen();

            using (var command = CreateCommand("SELECT dbo.GetUsernameById(@tabtb1)", CommandType.Text, new SqlParameter("@tabtb1", "BUDGETTYPE")))
            {
                var username = (await command.ExecuteScalarAsync()).ToString();
                return username;
            }
        }

        private DbCommand CreateCommand(string commandText, CommandType commandType, params SqlParameter[] parameters)
        {
            var command = Context.Database.GetDbConnection().CreateCommand();

            command.CommandText = commandText;
            command.CommandType = commandType;
            command.Transaction = GetActiveTransaction();

            foreach (var parameter in parameters)
            {
                command.Parameters.Add(parameter);
            }

            return command;
        }

        private void EnsureConnectionOpen()
        {
            var connection = Context.Database.GetDbConnection();

            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }
        }

        private DbTransaction GetActiveTransaction()
        {
            return (DbTransaction)_transactionProvider.GetActiveTransaction(new ActiveTransactionProviderArgs
            {
                {"ContextType", typeof(KWANPDbContext) },
                {"MultiTenancySide", MultiTenancySide }
            });
        }
    }
}

    after start the service error show: 500 : <a class="postlink" href="http://localhost:22742/swagger/v1/swagger.json">http://localhost:22742/swagger/v1/swagger.json</a>

    Thank you

  • User Avatar
    0
    aaron created
    Support Team

    Can you check the error log in *.Web.Mvc\App_Data\Logs?

  • User Avatar
    0
    kwanp created

    Hi aaron This is message from Logs.txt

    INFO  2017-10-08 21:42:43,063 [10   ] frastructure.NopAuthenticationMiddleware - Bearer was not authenticated. Failure message: No token found.
DEBUG 2017-10-08 21:42:43,063 [10   ] NetCore.StaticFiles.StaticFileMiddleware - The request path /swagger/images/logo_small.png does not match an existing file
DEBUG 2017-10-08 21:42:43,063 [10   ] Microsoft.AspNetCore.Routing.RouteBase   - Request successfully matched the route with name 'defaultWithArea' and template '{area}/{controller=Home}/{action=Index}/{id?}'.
DEBUG 2017-10-08 21:42:43,063 [10   ] .AspNetCore.Mvc.Internal.MvcRouteHandler - No actions matched the current request
DEBUG 2017-10-08 21:42:43,063 [10   ] Microsoft.AspNetCore.Routing.RouteBase   - Request successfully matched the route with name 'default' and template '{controller=Home}/{action=Index}/{id?}'.
DEBUG 2017-10-08 21:42:43,063 [10   ] .AspNetCore.Mvc.Internal.MvcRouteHandler - No actions matched the current request
DEBUG 2017-10-08 21:42:43,063 [10   ] soft.AspNetCore.Builder.RouterMiddleware - Request did not match any routes.
INFO  2017-10-08 21:42:43,063 [10   ] NetCore.StaticFiles.StaticFileMiddleware - The file /images/logo_small.png was not modified
DEBUG 2017-10-08 21:42:43,063 [10   ] NetCore.StaticFiles.StaticFileMiddleware - Handled. Status code: 304 File: /images/logo_small.png
DEBUG 2017-10-08 21:42:43,063 [10   ] Microsoft.AspNetCore.Server.Kestrel      - Connection id "0HL8EBBADBU3O" completed keep alive response.
INFO  2017-10-08 21:42:43,063 [10   ] soft.AspNetCore.Hosting.Internal.WebHost - Request finished in 0.7505ms 304 image/png
INFO  2017-10-08 21:42:43,065 [14   ] soft.AspNetCore.Hosting.Internal.WebHost - Request starting HTTP/1.1 GET http://localhost:22742/swagger/css/print.css  
DEBUG 2017-10-08 21:42:43,065 [14   ] n.Cookies.CookieAuthenticationMiddleware - AuthenticationScheme: Identity.Application was not authenticated.
DEBUG 2017-10-08 21:42:43,065 [14   ] IdentityServer4.Hosting.EndpointRouter   - No endpoint entry found for request path: /swagger/css/print.css
INFO  2017-10-08 21:42:43,065 [14   ] frastructure.NopAuthenticationMiddleware - Bearer was not authenticated. Failure message: No token found.
DEBUG 2017-10-08 21:42:43,065 [14   ] NetCore.StaticFiles.StaticFileMiddleware - The request path /swagger/css/print.css does not match an existing file
DEBUG 2017-10-08 21:42:43,065 [14   ] Microsoft.AspNetCore.Routing.RouteBase   - Request successfully matched the route with name 'defaultWithArea' and template '{area}/{controller=Home}/{action=Index}/{id?}'.
DEBUG 2017-10-08 21:42:43,065 [14   ] .AspNetCore.Mvc.Internal.MvcRouteHandler - No actions matched the current request
DEBUG 2017-10-08 21:42:43,065 [14   ] Microsoft.AspNetCore.Routing.RouteBase   - Request successfully matched the route with name 'default' and template '{controller=Home}/{action=Index}/{id?}'.
DEBUG 2017-10-08 21:42:43,065 [14   ] .AspNetCore.Mvc.Internal.MvcRouteHandler - No actions matched the current request
DEBUG 2017-10-08 21:42:43,065 [14   ] soft.AspNetCore.Builder.RouterMiddleware - Request did not match any routes.
INFO  2017-10-08 21:42:43,065 [14   ] NetCore.StaticFiles.StaticFileMiddleware - The file /css/print.css was not modified
DEBUG 2017-10-08 21:42:43,065 [14   ] NetCore.StaticFiles.StaticFileMiddleware - Handled. Status code: 304 File: /css/print.css
DEBUG 2017-10-08 21:42:43,065 [14   ] Microsoft.AspNetCore.Server.Kestrel      - Connection id "0HL8EBBADBU3P" completed keep alive response.
INFO  2017-10-08 21:42:43,065 [14   ] soft.AspNetCore.Hosting.Internal.WebHost - Request finished in 0.6264ms 304 text/css
INFO  2017-10-08 21:42:43,183 [12   ] soft.AspNetCore.Hosting.Internal.WebHost - Request starting HTTP/1.1 GET http://localhost:22742/swagger/v1/swagger.json  
DEBUG 2017-10-08 21:42:43,183 [12   ] n.Cookies.CookieAuthenticationMiddleware - AuthenticationScheme: Identity.Application was not authenticated.
DEBUG 2017-10-08 21:42:43,183 [12   ] IdentityServer4.Hosting.EndpointRouter   - No endpoint entry found for request path: /swagger/v1/swagger.json
INFO  2017-10-08 21:42:43,183 [12   ] frastructure.NopAuthenticationMiddleware - Bearer was not authenticated. Failure message: No token found.
DEBUG 2017-10-08 21:42:43,183 [12   ] NetCore.StaticFiles.StaticFileMiddleware - The request path /swagger/v1/swagger.json does not match an existing file
DEBUG 2017-10-08 21:42:43,183 [12   ] Microsoft.AspNetCore.Routing.RouteBase   - Request successfully matched the route with name 'defaultWithArea' and template '{area}/{controller=Home}/{action=Index}/{id?}'.
DEBUG 2017-10-08 21:42:43,183 [12   ] .AspNetCore.Mvc.Internal.MvcRouteHandler - No actions matched the current request
DEBUG 2017-10-08 21:42:43,183 [12   ] Microsoft.AspNetCore.Routing.RouteBase   - Request successfully matched the route with name 'default' and template '{controller=Home}/{action=Index}/{id?}'.
DEBUG 2017-10-08 21:42:43,183 [12   ] .AspNetCore.Mvc.Internal.MvcRouteHandler - No actions matched the current request
DEBUG 2017-10-08 21:42:43,183 [12   ] soft.AspNetCore.Builder.RouterMiddleware - Request did not match any routes.
ERROR 2017-10-08 21:42:43,515 [12   ] Microsoft.AspNetCore.Server.Kestrel      - Connection id "0HL8EBBADBU3Q": An unhandled exception was thrown by the application.
System.NotSupportedException: HTTP method "GET" & path "api/services/app/DbmtabStoreP/GetAllListAsync" overloaded by actions - KWANP.Dbmtab.DbmtabStorePAppService.GetAllListAsync (KWANP.Application),KWANP.Dbmtab.DbmtabStorePAppService.GetAllListAsync (KWANP.Application). Actions require unique method/path combination for Swagger
   at Swashbuckle.AspNetCore.SwaggerGen.SwaggerGenerator.CreatePathItem(IEnumerable`1 apiDescriptions, ISchemaRegistry schemaRegistry)
   at Swashbuckle.AspNetCore.SwaggerGen.SwaggerGenerator.&lt;&gt;c__DisplayClass4_0.&lt;GetSwagger&gt;b__5(IGrouping`2 group)
   at System.Linq.Enumerable.ToDictionary[TSource,TKey,TElement](IEnumerable`1 source, Func`2 keySelector, Func`2 elementSelector, IEqualityComparer`1 comparer)
   at System.Linq.Enumerable.ToDictionary[TSource,TKey,TElement](IEnumerable`1 source, Func`2 keySelector, Func`2 elementSelector)
   at Swashbuckle.AspNetCore.SwaggerGen.SwaggerGenerator.GetSwagger(String documentName, String host, String basePath, String[] schemes)
   at Swashbuckle.AspNetCore.Swagger.SwaggerMiddleware.&lt;Invoke&gt;d__6.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.GetResult()
   at Microsoft.AspNetCore.Builder.RouterMiddleware.&lt;Invoke&gt;d__4.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.GetResult()
   at Microsoft.Owin.Mapping.MapMiddleware.&lt;Invoke&gt;d__0.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.GetResult()
   at Microsoft.AspNetCore.Owin.WebSocketAcceptAdapter.&lt;&gt;c__DisplayClass6_0.&lt;&lt;AdaptWebSockets&gt;b__0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.GetResult()
   at Microsoft.AspNetCore.Localization.RequestLocalizationMiddleware.&lt;Invoke&gt;d__4.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.GetResult()
   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware`1.<Invoke>d__18.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware`1.&lt;Invoke&gt;d__18.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.GetResult()
   at IdentityServer4.AccessTokenValidation.IdentityServerAuthenticationMiddleware.&lt;Invoke&gt;d__7.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.GetResult()
   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware`1.<Invoke>d__18.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware`1.&lt;Invoke&gt;d__18.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.GetResult()
   at IdentityServer4.Hosting.IdentityServerMiddleware.&lt;Invoke&gt;d__3.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.GetResult()
   at IdentityServer4.Hosting.FederatedSignOutMiddleware.&lt;Invoke&gt;d__6.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.GetResult()
   at IdentityServer4.Hosting.AuthenticationMiddleware.&lt;Invoke&gt;d__2.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.GetResult()
   at Microsoft.AspNetCore.Cors.Infrastructure.CorsMiddleware.&lt;Invoke&gt;d__7.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.GetResult()
   at IdentityServer4.Hosting.BaseUrlMiddleware.&lt;Invoke&gt;d__2.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.GetResult()
   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware`1.<Invoke>d__18.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware`1.&lt;Invoke&gt;d__18.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.GetResult()
   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware`1.<Invoke>d__18.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware`1.&lt;Invoke&gt;d__18.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.GetResult()
   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware`1.<Invoke>d__18.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware`1.&lt;Invoke&gt;d__18.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.GetResult()
   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware`1.<Invoke>d__18.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware`1.&lt;Invoke&gt;d__18.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.GetResult()
   at Microsoft.AspNetCore.Cors.Infrastructure.CorsMiddleware.&lt;Invoke&gt;d__7.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.GetResult()
   at Microsoft.AspNetCore.Server.IISIntegration.IISMiddleware.&lt;Invoke&gt;d__8.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.GetResult()
   at Microsoft.AspNetCore.Hosting.Internal.RequestServicesContainerMiddleware.&lt;Invoke&gt;d__3.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.ConfiguredTaskAwaitable.ConfiguredTaskAwaiter.GetResult()
   at Microsoft.AspNetCore.Server.Kestrel.Internal.Http.Frame`1.<RequestProcessingAsync>d__2.MoveNext()
DEBUG 2017-10-08 21:42:43,515 [12   ] Microsoft.AspNetCore.Server.Kestrel      - Connection id "0HL8EBBADBU3Q" completed keep alive response.
INFO  2017-10-08 21:42:43,515 [12   ] soft.AspNetCore.Hosting.Internal.WebHost - Request finished in 342.1245ms 500

    and i don't why show this error "api/services/app/DbmtabStoreP/GetAllListAsync" cannot find this GetAllListAsync method

    Thank you

  • User Avatar
    0
    aaron created
    Support Team

    It says KWANP.Dbmtab.DbmtabStorePAppService.GetAllListAsync is overloaded - unrelated to GetUserNames.

  • User Avatar
    0
    kwanp created

    Hi arron how can i fix this issue

    Thank you

  • User Avatar
    0
    aaron created
    Support Team

    Don't overload GetAllListAsync - you have 2 or more methods named that, but you can only have 1.

  • User Avatar
    0
    kwanp created

    Hi arron i cannot find that i create the overload to GetAllListAsync

    using Abp.Application.Services;
using Abp.Application.Services.Dto;
using Abp.Domain.Repositories;
using KWANP.Dbmtab.Dto;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace KWANP.Dbmtab
{
    public interface IDbmtabStorePAppService : IApplicationService
    {
        Task<string> GetUserNames();
    }
}

    using Abp.Data;
using System.Data.Common;
using Microsoft.EntityFrameworkCore;
using System.Data;
using System.Data.SqlClient;
using Abp.Application.Services.Dto;
using KWANP.Dbmtab.Dto;
using AutoMapper;

namespace KWANP.Dbmtab
{
    public class DbmtabStorePAppService : KWANPRepositoryBase<Dbmtab00>, IDbmtabStorePAppService
    {
        private readonly IActiveTransactionProvider _transactionProvider;
        public DbmtabStorePAppService(IDbContextProvider<KWANPDbContext> dbContextProvider, IActiveTransactionProvider transactionProvider) : base(dbContextProvider)
        {
            _transactionProvider = transactionProvider;
        }


        public async Task<string> GetUserNames()
        {
            EnsureConnectionOpen();

            using (var command = CreateCommand("SELECT dbo.GetUsernameById(@tabtb1)", CommandType.Text, new SqlParameter("@tabtb1", "BUDGETTYPE")))
            {
                var username = (await command.ExecuteScalarAsync()).ToString();
                return username;
            }
        }

        private DbCommand CreateCommand(string commandText, CommandType commandType, params SqlParameter[] parameters)
        {
            var command = Context.Database.GetDbConnection().CreateCommand();

            command.CommandText = commandText;
            command.CommandType = commandType;
            command.Transaction = GetActiveTransaction();

            foreach (var parameter in parameters)
            {
                command.Parameters.Add(parameter);
            }

            return command;
        }

        private void EnsureConnectionOpen()
        {
            var connection = Context.Database.GetDbConnection();

            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }
        }

        private DbTransaction GetActiveTransaction()
        {
            return (DbTransaction)_transactionProvider.GetActiveTransaction(new ActiveTransactionProviderArgs
            {
                {"ContextType", typeof(KWANPDbContext) },
                {"MultiTenancySide", MultiTenancySide }
            });
        }
    }
}

    Thank you

  • User Avatar
    0
    aaron created
    Support Team

    Oh, an AppService should not inherit RepositoryBase.

    Create a new class DbmtabKWANPRepository:

    public class DbmtabKWANPRepository : KWANPRepositoryBase<Dbmtab00>
{
    private readonly IActiveTransactionProvider _transactionProvider;
    public DbmtabStorePAppService(IDbContextProvider<KWANPDbContext> dbContextProvider, IActiveTransactionProvider transactionProvider) : base(dbContextProvider)
    {
        _transactionProvider = transactionProvider;
    }

    public async Task<string> GetUserNames()
    {
        EnsureConnectionOpen();

        using (var command = CreateCommand("SELECT dbo.GetUsernameById(@tabtb1)", CommandType.Text, new SqlParameter("@tabtb1", "BUDGETTYPE")))
        {
            var username = (await command.ExecuteScalarAsync()).ToString();
            return username;
        }
    }

    private DbCommand CreateCommand(string commandText, CommandType commandType, params SqlParameter[] parameters)
    {
        // ...
    }

    private void EnsureConnectionOpen()
    {
        // ...
    }

    private DbTransaction GetActiveTransaction()
    {
        // ...
    }
}

    Inject DbmtabKWANPRepository in DbmtabStorePAppService:

    public class DbmtabStorePAppService : IDbmtabStorePAppService
{
    private readonly DbmtabKWANPRepository _repository;
    public DbmtabStorePAppService(DbmtabKWANPRepository repository)
    {
        _repository = repository;
    }

    public async Task<string> GetUserNames()
    {
        return _repository.GetUserNames();
    }
}
  • User Avatar
    0
    kwanp created

    Thank you aaron

Assignee
No one
Labels
None yet