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"
}
}
Maybe this will help someone. This is not a complete solution but I managed to fix it. the problem was error line: "(provider: HTTP Provider, error: 0 - )". I have started the project with premade HTTPS configuration and this was causing this error. When I started from begging with HTTP not HTTPS it was all working fine.
I could then in options change to https and it was still working. Just premade settings of https was not working with blazor.