Blazor DataGrid with Dapper and SQL Parameter

[Michael Caditz]

I followed this blog and created a Blazor CRUD Application with Dapper.

https://www.syncfusion.com/blogs/post/build-blazor-crud-application-with-dapper

I have one problem. On my data access layer (MemberDataAccessLayer.cs) I need to pass a parameter to SQL Server which is the Id of the current logged on ASPNet user. The code I have always produces NULL for authenticationState.

How can I get information about authenticated user?

using Dapper;

using System.Data;

using System.Data.SqlClient;

using Syncfusion;

using Microsoft.AspNetCore.Components.Authorization;

using Microsoft.AspNetCore.Components;

using DevExpress.ExpressApp.Security;

using Microsoft.AspNetCore.Identity;

using Microsoft.Identity.Client;

using Microsoft.AspNetCore.Mvc;

using SSPWebsite.Classes;

namespace SSPWebsite.Data

{

    public class DonationDataAccessLayer

    {

        public IConfiguration Configuration;

        private const string MEMBER_DATABASE = "SPPSWebWiz";

        public DonationDataAccessLayer(IConfiguration configuration)

        {

            Configuration = configuration; //Inject configuration to access Connection string from appsettings.json.

        }

        public async Task<List<DonationModel>> GetDonationAsync()

        {

            using (IDbConnection db = new SqlConnection(Configuration.GetConnectionString(MEMBER_DATABASE)))

            {

                string cUserID = await getUserId();

                var p = new DynamicParameters();

                p.Add(cUserID);

                //p.Add("@cASPUserID", "481d30d5-cd91-4f9b-9559-24ddcc9cfdb5");

                //p.Add("@b", dbType: DbType.Int32, direction: ParameterDirection.Output);

                //p.Add("@c", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);

                db.Open();

                IEnumerable<DonationModel> result = await db.QueryAsync<DonationModel>("USP_getOneMemberDonations", p, commandType: CommandType.StoredProcedure);

                return result.ToList();

            }

        }

        public async Task<int> GetDonationCountAsync()

        {

            using (IDbConnection db = new SqlConnection(Configuration.GetConnectionString(MEMBER_DATABASE)))

            {

                //db.Open();

                //int result = await db.ExecuteScalarAsync<int>("select count(*) from MRCADITZ_Website.donations");

                //return result;

                var p = new DynamicParameters();

                p.Add("@cASPUserID", "481d30d5-cd91-4f9b-9559-24ddcc9cfdb5");

                //p.Add("@b", dbType: DbType.Int32, direction: ParameterDirection.Output);

                //p.Add("@c", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);

                db.Open();

                int result = await db.ExecuteScalarAsync<int>("USP_getOneMemberDonationsCount", p, commandType: CommandType.StoredProcedure);

                return result;

            }

        }

        public async Task AddDonationAsync(DonationModel donationModel)

        {

            using (IDbConnection db = new SqlConnection(Configuration.GetConnectionString(MEMBER_DATABASE)))

            {

                db.Open();

                await db.ExecuteAsync("insert into bugs (Summary, BugPriority, Assignee, BugStatus) values (@Summary, @BugPriority, @Assignee, @BugStatus)", donationModel);

            }

        }

        public async Task UpdateDonationAsync(DonationModel donationModel)

        {

            using (IDbConnection db = new SqlConnection(Configuration.GetConnectionString(MEMBER_DATABASE)))

            {

                db.Open();

                await db.ExecuteAsync("update bugs set Summary=@Summary, BugPriority=@BugPriority, Assignee=@Assignee, BugStatus=@BugStatus where id=@Id", donationModel);

            }

        }

        public async Task RemoveDonationAsync(int bugid)

        {

            using (IDbConnection db = new SqlConnection(Configuration.GetConnectionString(MEMBER_DATABASE)))

            {

                db.Open();

                await db.ExecuteAsync("delete from bugs Where id=@BugId", new { BugId = bugid });

            }

        }

        private string authMessage = "The user is NOT authenticated.";

        [CascadingParameter]

        private Task<AuthenticationState>? authenticationState { get; set; }

        private async Task<string?> getUserId()

        {

            var userid = "";

            if (authenticationState is not null) //ALWAYS NULL

            {

                var authState = await authenticationState;

                var user = authState?.User;

                if (user?.Identity is not null && user.Identity.IsAuthenticated)

                {

                    userid = user.FindFirst(u => u.Type.Contains("nameidentifier"))?.Value;

                    authMessage = $"{user.Identity.Name} is authenticated.";

                }

            }

            return userid;

        }

    }

}

[Sanjay Kumar Suresh]

Hi Micheal,

 

Thank you for reaching out regarding the issue you are experiencing with authenticationState. To assist you further and provide an appropriate solution, we kindly request a few additional details:

 

1. Could you please confirm if the issue occurs specifically when rendering the Grid component or if it also happens in other scenarios?
2. If applicable, could you share the relevant Grid code to help us better understand the context?
3. A video demonstration of the issue would be greatly helpful in understanding the behavior you are facing.
4. If feasible, could you provide a simple sample that replicates the issue observed on your end?
5. we request you to share the NuGet version you are currently using.

 

Above-requested details will be very helpful in validating the reported query at our end and providing a solution as early as possible. Thanks for your understanding.

 

We also recommend referring to our documentation for guidance on rendering the DataGrid using Dapper. You can find the relevant information in the link below
https://blazor.syncfusion.com/documentation/datagrid/connecting-to-database/dapper

 

Regards,

Sanjay Kumar Suresh