Blazor, Dapper - A connection was successfully established with the server, but then an error occurred during the pre-login handshake (Error)

-1

I'm fallowing on of the udemy tutorials to connect to MS SQL with Blazor and Dapper. Tutorial connects to localdb but I want to do it with online server that I have. My connection strings are all fine. I can connect to DB with my VS Studio. I dont understand where the problem might be. Here is error code what I have in Console when I try to insert into database. It also says something about line 39 and line 31. I have pasted the C# code and marked with line it is with comment. Was trying to find solution on web but could not find any. Maybe someone have an idea why I cant insert data into my database and what is causing this error.

I was thinking that my connection string might be the issue but I'm sure it's fine I have tested it in .net framework and I could insert into SQL without any problem with exactly the same string connection.

fail: Microsoft.AspNetCore.Components.Server.Circuits.CircuitHost[111]
      Unhandled exception in circuit 'TXbqG0ewGPtD7iEweWor5mR-q0P-zPZJ8EdkuoqLCkQ'.
Microsoft.Data.SqlClient.SqlException (0x80131904): A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: HTTP Provider, error: 0 - )
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.ConsumePreLoginHandshake(Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean& marsCapable, Boolean& fedAuthRequired)
   at Microsoft.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean withFailover, SqlAuthenticationMethod authType)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling, String accessToken, DbConnectionPool pool)
   at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.WaitForPendingOpen()
--- End of stack trace from previous location where exception was thrown ---
   at Dapper.SqlMapper.ExecuteImplAsync(IDbConnection cnn, CommandDefinition command, Object param) in /_/Dapper/SqlMapper.Async.cs:line 678
   at BlazorDapperCRUD.Data.VideoService.VideoInsert(Video video) in D:\VSrepos\repos\BlazorDapperCRUD\Data\VideoService.cs:line 31
   at BlazorDapperCRUD.Pages.VideoAdd.VideoInsert() in D:\VSrepos\repos\BlazorDapperCRUD\Pages\VideoAdd.razor:line 39
   at Microsoft.AspNetCore.Components.ComponentBase.CallStateHasChangedOnAsyncCompletion(Task task)
   at Microsoft.AspNetCore.Components.Forms.EditForm.HandleSubmitAsync()
   at Microsoft.AspNetCore.Components.ComponentBase.CallStateHasChangedOnAsyncCompletion(Task task)
   at Microsoft.AspNetCore.Components.RenderTree.Renderer.GetErrorHandledTask(Task taskToHandle)
ClientConnectionId:fad02929-0736-43c4-a7da-573a3219cd0e
@using BlazorDapperCRUD.Data
@page "/videoadd"
@inject IVideoService VideoService
@inject NavigationManager NavigationManager

<h1>Add Video</h1>

<body>
    <EditForm Model="@video" OnValidSubmit="@VideoInsert">
        <table>
            <tr>
                <td>Video Title:</td>
                <td><input type="text" @bind="video.Title" required /></td>
            </tr>
            <tr>
                <td>Date Published:</td>
                <td><input type="date" @bind="video.DatePublished" required /></td>
            </tr>
            <tr>
                <td>Is Active:</td>
                <td><input type="checkbox" @bind="video.IsActive" /></td>
            </tr>
            <tr>
                <td colspan="2" style="text-align:center">
                    <input type="submit" value="Add" />
                    <input type="button" value="Cancel" @onclick="@Cancel" />
                </td>
            </tr>
        </table>
    </EditForm>
</body>

@code {
    // Create new, empty video object
    Video video = new Video();

    protected async Task VideoInsert()
    {
//this is line 39
        await VideoService.VideoInsert(video);
        NavigationManager.NavigateTo("/");
    }
    
    void Cancel()
    {
        NavigationManager.NavigateTo("/");
    }
}
{
    public class VideoService : IVideoService
    {
        //Database connection
        private readonly SqlConnectionConfiguration _configuration;
        public VideoService(SqlConnectionConfiguration configuration)
        {
            _configuration = configuration;
        }

        public async Task<bool> VideoInsert(Video video)
        {
            using (var conn = new SqlConnection(_configuration.Value))
            {
                var parameters = new DynamicParameters();
                parameters.Add("Title", video.Title, DbType.String);
                parameters.Add("DatePublished", video.DatePublished, DbType.Date);
                parameters.Add("IsActive", video.IsActive, DbType.Boolean);
                const string query = @"INSERT INTO Video VALUES (@Title, @DatePublished, @IsActive)";
                await conn.ExecuteAsync(query, new { video.Title, video.DatePublished, video.IsActive }, commandType: CommandType.Text);
            }
//this is line 31
            return true;
        }
    }
}

My connection string is in appsettings.json I have copy pasted it after I connected with MSSQL via SQL Server Object Explorer in VS Studio 2019.

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "SqlDBcontextBlazorDapperCRUD": "Data Source=hostname;Initial Catalog=mycatalog;User ID=myusername;Password=mypw;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"
  }
}
blazor
dapper
asp.net-core-3.1
blazor-server-side
asked on Stack Overflow Jul 16, 2020 by rtuszynski • edited Jul 16, 2020 by rtuszynski

1 Answer

0

I don't know if any of this will help you, but I see a few issues:

  • Why are you adding parameters? You aren't using them. A parameter is added when you type @ in the query.
  • You don't need the CommandType either.
  • It's good style to specify the column names in the insert in case the table is reordered.
  • Checking the affectedRows afterwards is a good idea.
public async Task<bool> VideoInsert(Video video)
{
    using (var conn = new SqlConnection(_configuration.Value))
    {
        const string query = @"INSERT INTO Video (TitleCol, PublishedCol, IsActiveCol) VALUES (@Title, @DatePublished, @IsActive)";
        int affectedRows = await conn.ExecuteAsync(query, new { video.Title, video.DatePublished, video.IsActive });
        return affectedRows == 1;
    }
}
answered on Stack Overflow Jul 20, 2020 by Palle Due

User contributions licensed under CC BY-SA 3.0