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

12 Answer(s)

• 

  0

  aaron created 7 years ago

  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

  Save Cancel
• 

  0

  kwanp created 7 years ago

  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

  Save Cancel
• 

  0

  aaron created 7 years ago

  Support Team

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

  Save Cancel
• 

  0

  kwanp created 7 years ago

  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

  Save Cancel
• 

  0

  aaron created 7 years ago

  Support Team

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

  Save Cancel
• 

  0

  kwanp created 7 years ago

  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.<>c__DisplayClass4_0.<GetSwagger>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.<Invoke>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.<Invoke>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.<Invoke>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.<>c__DisplayClass6_0.<<AdaptWebSockets>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.<Invoke>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

  Save Cancel
• 

  0

  aaron created 7 years ago

  Support Team

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

  Save Cancel
• 

  0

  kwanp created 7 years ago

  Hi arron how can i fix this issue

  Thank you

  Save Cancel
• 

  0

  aaron created 7 years ago

  Support Team

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

  Save Cancel
• 

  0

  kwanp created 7 years ago

  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

  Save Cancel
• 

  0

  aaron created 7 years ago

  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();
      }
  }
  

  Save Cancel
• 

  0

  kwanp created 7 years ago

  Thank you aaron

  Save Cancel