Base solution for your next web application
Open Closed

Automated Tests Fail on App Service Using Custom Repository #9673


User avatar
0
wmaiuri created

Prerequisites

Please answer the following questions before submitting an issue. YOU MAY DELETE THE PREREQUISITES SECTION.

  • What is your product version?V.9.1.0(Latest)
  • What is your product type (Angular or MVC)?Angular
  • What is product framework type (.net framework or .net core)?.net core

If issue related with ABP Framework

  • What is ABP Framework version?

If issue is about UI

  • Which theme are you using?
  • What are the theme settings?

I created a custom repository that calls into stored procedures. I used the tutorial https://aspnetboilerplate.com/Pages/Documents/Articles/Using-Stored-Procedures,-User-Defined-Functions-and-Views/index.html to create the custom repository. All my methods work as expected when I call them thru the swagger api application, and they also work fine when used thru the angular client. When I setup automated tests the custom repository method fails with

It looks like the wrong DBCOntext is being used when the automated tests are running.  Here is my custom repository.  Is there a work-around for this issue?
 Message: 
    System.AggregateException : One or more errors occurred. (The CommandType 'StoredProcedure' is invalid.)
    ---- System.ArgumentException : The CommandType 'StoredProcedure' is invalid.
  Stack Trace: 
    Task.ThrowIfExceptional(Boolean includeTaskCanceledExceptions)
    Task`1.GetResultCore(Boolean waitCompletionNotification)
    Task`1.get_Result()
    PTStaticDataType_Tests.Should_Get_All_StaticItems() line 33
    ----- Inner Stack Trace -----
    SqliteCommand.set_CommandType(CommandType value)
    PTStaticDataTypeRepository.Get(PTGetPTStaticDataTypeInput input) line 96
    UnitOfWorkInterceptor.InternalInterceptAsynchronous[TResult](IInvocation invocation)
    PTStaticDataTypeAppService.Get(PTGetPTStaticDataTypeInput input) line 38
    AuthorizationInterceptor.InternalInterceptAsynchronous[TResult](IInvocation invocation)
    UnitOfWorkInterceptor.InternalInterceptAsynchronous[TResult](IInvocation invocation)
    AuditingInterceptor.InternalInterceptAsynchronous[TResult](IInvocation invocation)
    AuditingInterceptor.InternalInterceptAsynchronous[TResult](IInvocation invocation)
    ValidationInterceptor.InternalInterceptAsynchronous[TResult](IInvoca

namespace PieceTracker.EntityFrameworkCore.Repositories
{
    public class PTStaticDataTypeRepository : PieceTrackerRepositoryBase<PTStaticDataType, long>, IPTStaticDataTypeRepository
    {
        private readonly IActiveTransactionProvider _transactionProvider;
        public PTStaticDataTypeRepository(IDbContextProvider<PieceTrackerDbContext> dbContextProvider, IActiveTransactionProvider transactionProvider)
            : base(dbContextProvider)
        {
            _transactionProvider = transactionProvider;
        }
        
        public async Task<PTCreateOrEditPTStaticDataTypeDto> CreateOrEdit(PTCreateOrEditPTStaticDataTypeDto input)
        {
			var cn = Context.Database.GetDbConnection();
			if (cn.State != ConnectionState.Open)
				await cn.OpenAsync();
			using (var cmd = cn.CreateCommand())
			{
				cmd.CommandText = "usp_InsertUpdatePTStaticDataType";
				cmd.CommandType = CommandType.StoredProcedure;
				cmd.Transaction = GetActiveTransaction();
				SqlParameter retVal = new SqlParameter() { Direction = ParameterDirection.ReturnValue };
				cmd.Parameters.Add(retVal);
				cmd.Parameters.Add(new SqlParameter("@Id", SqlDbType.BigInt) { Value = input.Id, Direction = ParameterDirection.InputOutput });
				cmd.Parameters.Add(new SqlParameter("@CreatorUserId", SqlDbType.BigInt) { Value = input.EditUserId });
				cmd.Parameters.Add(new SqlParameter("@LastModifierUserId", SqlDbType.BigInt) { Value = input.EditUserId });
				cmd.Parameters.Add(new SqlParameter("@TenantId", SqlDbType.Int) { Value = input.TenantId });
				cmd.Parameters.Add(new SqlParameter("@TypeGUID", SqlDbType.UniqueIdentifier) { Value = input.TypeGUID });
				cmd.Parameters.Add(new SqlParameter("@TypeName", SqlDbType.NVarChar, 50) { Value = input.TypeName });
				cmd.Parameters.Add(new SqlParameter("@TypeDescription", SqlDbType.NVarChar, 254) { Value = input.TypeDescription });
				cmd.Parameters.Add(new SqlParameter("@ConstantType", SqlDbType.NVarChar, 1) { Value = input.ConstantType });
				cmd.Parameters.Add(new SqlParameter("@ConstantFormat", SqlDbType.NVarChar, 50) { Value = input.ConstantFormat });
				cmd.Parameters.Add(new SqlParameter("@LastChanged", SqlDbType.BigInt) { Value = input.LastChanged, Direction = ParameterDirection.InputOutput });
				try
				{
					await Task.Run(() => cmd.ExecuteNonQuery());
					input.LastChanged = (long)cmd.Parameters["@LastChanged"].Value;
					input.Id = (long)cmd.Parameters["@Id"].Value;
				}
				catch (Exception ex)
				{
					throw new Abp.UI.UserFriendlyException(ex.Message);
				}
				return input;
			}
		}

		public async Task Delete(PTDelPTStaticDataTypeInput input)
        {
			var cn = Context.Database.GetDbConnection();
			if (cn.State != ConnectionState.Open)
				await cn.OpenAsync();
			using (var cmd = cn.CreateCommand())
			{
				cmd.CommandText = "usp_DeletePTStaticDataType";
				cmd.CommandType = CommandType.StoredProcedure;
				cmd.Transaction = GetActiveTransaction();
				SqlParameter retVal = new SqlParameter() { Direction = ParameterDirection.ReturnValue };
				cmd.Parameters.Add(retVal);
				cmd.Parameters.Add(new SqlParameter("@Id", SqlDbType.Int) { Value = input.Id });
				cmd.Parameters.Add(new SqlParameter("@UserId", SqlDbType.BigInt) { Value = input.EditUserId });
				cmd.Parameters.Add(new SqlParameter("@TenantId", SqlDbType.Int) { Value = input.TenantId });
				try
				{
					await Task.Run(() => cmd.ExecuteNonQuery());
				}
				catch (Exception ex)
				{
					throw new Abp.UI.UserFriendlyException(ex.Message);
				}
			}
		}

		public async Task<List<PTGetPTStaticDataTypeForViewDto>> Get(PTGetPTStaticDataTypeInput input)
        {
			var data = new List<PTGetPTStaticDataTypeForViewDto>();
			var cn = Context.Database.GetDbConnection();
			if (cn.State != ConnectionState.Open)
				await cn.OpenAsync();
			using (var cmd = cn.CreateCommand())
			{
				cmd.CommandText = "usp_GetPTStaticDataType";
				cmd.CommandType = CommandType.StoredProcedure;
				cmd.Transaction = GetActiveTransaction();
				cmd.Parameters.Add(new SqlParameter("@Id", SqlDbType.Int) { Value = input.Id });
				cmd.Parameters.Add(new SqlParameter("@TenantId", SqlDbType.Int) { Value = input.TenantId });
				cmd.Parameters.Add(new SqlParameter("@IsDeleted", SqlDbType.Int) { Value = input.IsDeleted });
				using (var dr = await Task.Run(() => cmd.ExecuteReader()))
				{
					while (dr.Read())
					{
						PTGetPTStaticDataTypeForViewDto l = new PTGetPTStaticDataTypeForViewDto();
						l.Id = PieceTrackerDbContext.GetLong(dr, "Id");
						l.CreationTime = PieceTrackerDbContext.GetDateTime(dr, "CreationTime");
						l.CreatorUserId = PieceTrackerDbContext.GetLong(dr, "CreatorUserId");
						l.LastModificationTime = PieceTrackerDbContext.GetDateTime(dr, "LastModificationTime");
						l.LastModifierUserId = PieceTrackerDbContext.GetLong(dr, "LastModifierUserId");
						l.IsDeleted = PieceTrackerDbContext.GetBool(dr, "IsDeleted");
						l.DeleterUserId = PieceTrackerDbContext.GetLong(dr, "DeleterUserId");
						l.DeletionTime = PieceTrackerDbContext.GetDateTime(dr, "DeletionTime");
						l.TenantId = PieceTrackerDbContext.GetInt(dr, "TenantId");
						l.TypeGUID = PieceTrackerDbContext.GetGuid(dr, "TypeGUID");
						l.TypeName = PieceTrackerDbContext.GetString(dr, "TypeName");
						l.TypeDescription = PieceTrackerDbContext.GetString(dr, "TypeDescription");
						l.ConstantType = PieceTrackerDbContext.GetString(dr, "ConstantType");
						l.ConstantFormat = PieceTrackerDbContext.GetString(dr, "ConstantFormat");
						l.LastChanged = PieceTrackerDbContext.GetLong(dr, "LastChanged");
						data.Add(l);
					}
				}
			}
			return data;
		}

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

    }
}

2 Answer(s)
  • User Avatar
    0
    maliming created
    Support Team

    hi

    I remember that Sqlite does not support stored procedures.

  • User Avatar
    0
    wmaiuri created

    I created a workaround in my custom repository.

    	private DbConnection GetSqlConnection()
    	{
    		const string TESTINGDB = "memory";
    		const string PTCONNSTRING = "Server=(local); Database=PieceTrackerDb; Trusted_Connection=True;";
    		var cn = Context.Database.GetDbConnection();
    		if (cn.ConnectionString.Contains(TESTINGDB))
    		{
    			cn = new SqlConnection(PTCONNSTRING);
    		}
    		var logMsg = "PTStaticDataTypeRepository.Get Connection String = " + cn.ConnectionString;
    		Console.WriteLine(logMsg);
    		return cn;
    	}