当我使用 WebSecurity.InitializeDatabaseConnection(parameters) 时收到错误( Npgsql.PostgresException： '42601：语

Question

我可以使用WebSecurity吗?用PostgreSQL初始化数据库?我得到一个错误(Npgsql。PostgresException: '42601:语法错误在"["]当我使用WebSecurity.InitializeDatabaseConnection("PGConn"， "userlogin"， "user"， "username"， true);我的网络。配置文件:

<configuration>
<configSections>
<!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
<section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
</configSections>
<appSettings>
<add key="webpages:Version" value="3.0.0.0" />
<add key="webpages:Enabled" value="false" />
<add key="ClientValidationEnabled" value="true" />
<add key="UnobtrusiveJavaScriptEnabled" value="true" />
</appSettings>
<!--
For a description of web.config changes see http://go.microsoft.com/fwlink/?LinkId=235367.
The following attributes can be set on the <httpRuntime> tag.
<system.Web>
<httpRuntime targetFramework="4.8" />
</system.Web>
-->
<system.web>
<authentication mode="Forms">
<forms loginUrl="~/Home/Login" timeout="10" />
</authentication>
<compilation debug="true" targetFramework="4.7.2">
<assemblies>
<add assembly="CrystalDecisions.Web, Version=13.0.4000.0, Culture=neutral, PublicKeyToken=692FBEA5521E1304" />
<add assembly="CrystalDecisions.Shared, Version=13.0.4000.0, Culture=neutral, PublicKeyToken=692FBEA5521E1304" />
<add assembly="log4net, Version=1.2.10.0, Culture=neutral, PublicKeyToken=692FBEA5521E1304" />
<add assembly="CrystalDecisions.ReportSource, Version=13.0.4000.0, Culture=neutral, PublicKeyToken=692FBEA5521E1304" />
<add assembly="CrystalDecisions.ReportAppServer.Controllers, Version=13.0.4000.0, Culture=neutral, PublicKeyToken=692FBEA5521E1304" />
<add assembly="CrystalDecisions.ReportAppServer.DataDefModel, Version=13.0.4000.0, Culture=neutral, PublicKeyToken=692FBEA5521E1304" />
<add assembly="CrystalDecisions.CrystalReports.Engine, Version=13.0.4000.0, Culture=neutral, PublicKeyToken=692FBEA5521E1304" />
</assemblies>
</compilation>
<httpRuntime targetFramework="4.7.2" />
</system.web>
<connectionStrings>
<add name="PGConn" connectionString="Server=localhost; Port=5432; Database=DBpostgre; User ID=system;Password=password;" providerName="Npgsql" />
<add name="SQLConn" connectionString="Password=password;Persist Security Info=True;User ID=testDb;Initial Catalog=WeighingProject;Data Source=192.168.20.2SQLEXPRESS" providerName="System.Data.SqlClient" />
</connectionStrings>
<runtime>
<assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
<dependentAssembly>
<assemblyIdentity name="Antlr3.Runtime" publicKeyToken="eb42632606e9261f" />
<bindingRedirect oldVersion="0.0.0.0-3.5.0.2" newVersion="3.5.0.2" />
</dependentAssembly>
<dependentAssembly>
<assemblyIdentity name="Newtonsoft.Json" publicKeyToken="30ad4fe6b2a6aeed" />
<bindingRedirect oldVersion="0.0.0.0-13.0.0.0" newVersion="13.0.0.0" />
</dependentAssembly>
<dependentAssembly>
<assemblyIdentity name="System.Web.Optimization" publicKeyToken="31bf3856ad364e35" />
<bindingRedirect oldVersion="1.0.0.0-1.1.0.0" newVersion="1.1.0.0" />
</dependentAssembly>
<dependentAssembly>
<assemblyIdentity name="WebGrease" publicKeyToken="31bf3856ad364e35" />
<bindingRedirect oldVersion="0.0.0.0-1.6.5135.21930" newVersion="1.6.5135.21930" />
</dependentAssembly>
<dependentAssembly>
<assemblyIdentity name="System.Web.Helpers" publicKeyToken="31bf3856ad364e35" />
<bindingRedirect oldVersion="0.0.0.0-3.0.0.0" newVersion="3.0.0.0" />
</dependentAssembly>
<dependentAssembly>
<assemblyIdentity name="System.Web.WebPages" publicKeyToken="31bf3856ad364e35" />
<bindingRedirect oldVersion="0.0.0.0-3.0.0.0" newVersion="3.0.0.0" />
</dependentAssembly>
<dependentAssembly>
<assemblyIdentity name="System.Web.Mvc" publicKeyToken="31bf3856ad364e35" />
<bindingRedirect oldVersion="0.0.0.0-5.2.7.0" newVersion="5.2.7.0" />
</dependentAssembly>
<dependentAssembly>
<assemblyIdentity name="System.Runtime.CompilerServices.Unsafe" publicKeyToken="b03f5f7f11d50a3a" culture="neutral" />
<bindingRedirect oldVersion="0.0.0.0-6.0.0.0" newVersion="6.0.0.0" />
</dependentAssembly>
<dependentAssembly>
<assemblyIdentity name="System.Buffers" publicKeyToken="cc7b13ffcd2ddd51" culture="neutral" />
<bindingRedirect oldVersion="0.0.0.0-4.0.3.0" newVersion="4.0.3.0" />
</dependentAssembly>
<dependentAssembly>
<assemblyIdentity name="Npgsql" publicKeyToken="5d8b90d52f46fda7" culture="neutral" />
<bindingRedirect oldVersion="0.0.0.0-6.0.3.0" newVersion="6.0.3.0" />
</dependentAssembly>
<dependentAssembly>
<assemblyIdentity name="System.Diagnostics.DiagnosticSource" publicKeyToken="cc7b13ffcd2ddd51" culture="neutral" />
<bindingRedirect oldVersion="0.0.0.0-6.0.0.0" newVersion="6.0.0.0" />
</dependentAssembly>
<dependentAssembly>
<assemblyIdentity name="System.Web.WebPages.Razor" publicKeyToken="31bf3856ad364e35" culture="neutral" />
<bindingRedirect oldVersion="0.0.0.0-3.0.0.0" newVersion="3.0.0.0" />
</dependentAssembly>
</assemblyBinding>
</runtime>
<system.codedom>
<compilers>
<compiler language="c#;cs;csharp" extension=".cs" type="Microsoft.CodeDom.Providers.DotNetCompilerPlatform.CSharpCodeProvider, Microsoft.CodeDom.Providers.DotNetCompilerPlatform, Version=2.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" warningLevel="4" compilerOptions="/langversion:default /nowarn:1659;1699;1701" />
<compiler language="vb;vbs;visualbasic;vbscript" extension=".vb" type="Microsoft.CodeDom.Providers.DotNetCompilerPlatform.VBCodeProvider, Microsoft.CodeDom.Providers.DotNetCompilerPlatform, Version=2.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" warningLevel="4" compilerOptions="/langversion:default /nowarn:41008 /define:_MYTYPE=&quot;Web&quot; /optionInfer+" />
</compilers>
</system.codedom>
<entityFramework>
<providers>
<provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
<provider invariantName="Npgsql" type="Npgsql.NpgsqlServices, EntityFramework6.Npgsql" />
</providers>
<defaultConnectionFactory type="Npgsql.NpgsqlConnectionFactory, EntityFramework6.Npgsql" />
</entityFramework>
<system.data>
<DbProviderFactories>
<remove invariant="Npgsql" />
<add name="Npgsql Provider" invariant="Npgsql" description=".NET Framework Data Provider for PostgreSQL" type="Npgsql.NpgsqlFactory, Npgsql, Version=4.1.3.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7" />
<add name="dotConnect for PostgreSQL" invariant="Devart.Data.PostgreSql" description="Devart dotConnect for PostgreSQL" type="Devart.Data.PostgreSql.PgSqlProviderFactory, Devart.Data.PostgreSql, Version= 7.24.2066.0, Culture=neutral, PublicKeyToken=09af7300eec23701" />
</DbProviderFactories>
</system.data>
</configuration>

和我global .asax.cs代码为:

using DapperExtensions;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.Optimization;
using System.Web.Routing;
using System.Web.Security;
using WebMatrix.WebData;
namespace PayrollContractual
{
public class MvcApplication : System.Web.HttpApplication
{
protected void Application_Start()
{
AreaRegistration.RegisterAllAreas();
FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);
RouteConfig.RegisterRoutes(RouteTable.Routes);
BundleConfig.RegisterBundles(BundleTable.Bundles);
InitializeAuthenticationProcess();
}
private void InitializeAuthenticationProcess()
{
if(!WebMatrix.WebData.WebSecurity.Initialized)
{
WebMatrix.WebData.WebSecurity.InitializeDatabaseConnection("PGConn", "userlogin", "user", "username", true); // Error generates here. PGConn is the connection string name of Postgre Database,Compiler execute this statement as SQL server database string and appending "[" and "]" that's why generates error. But it works well if we connect it to the SQL server connection string that is SQLConn...
// WebSecurity.InitializeDatabaseConnection("SQLConn", "Users", "UserId", "UserName", autoCreateTables: false);
WebSecurity.CreateUserAndAccount("admin", "admin123");
if (!Roles.RoleExists("Administrator"))
{
Roles.CreateRole("Administrator");
}
if (!Roles.RoleExists("Manager"))
{
Roles.CreateRole("Manager");
}
if (!Roles.RoleExists("User"))
{
Roles.CreateRole("User");
}

//    WebSecurity.InitializeDatabaseConnection(connectionStringName: "PGConn", userTableName: "public].[UserLogin", userIdColumn: "user_id", userNameColumn: "user_name", autoCreateTables: true);

}
}
}
}

我需要做什么改变，编译器执行此代码为postgreSQL而不是SQL Server语句?请帮我解决这个问题。我想现在我已经分享了所有的代码。提前感谢。也请参考截图。输入图片描述

全栈跟踪:

at Npgsql.Internal.NpgsqlConnector.<<ReadMessage>g__ReadMessageLong|211_0>d.MoveNext()
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Threading.Tasks.ValueTask`1.get_Result()
at Npgsql.NpgsqlDataReader.<NextResult>d__47.MoveNext()
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Npgsql.NpgsqlDataReader.NextResult()
at Npgsql.NpgsqlCommand.<ExecuteReader>d__116.MoveNext()
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at Npgsql.NpgsqlCommand.<ExecuteReader>d__116.MoveNext()
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Npgsql.NpgsqlCommand.<ExecuteNonQuery>d__104.MoveNext()
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Npgsql.NpgsqlCommand.ExecuteNonQuery()
at WebMatrix.Data.Database.Execute(String commandText, Object[] args)
at WebMatrix.WebData.DatabaseWrapper.Execute(String commandText, Object[] parameters)
at WebMatrix.WebData.SimpleMembershipProvider.CreateTablesIfNeeded()
at WebMatrix.WebData.WebSecurity.InitializeMembershipProvider(SimpleMembershipProvider simpleMembership, DatabaseConnectionInfo connect, String userTableName, String userIdColumn, String userNameColumn, Boolean createTables, SimpleMembershipProviderCasingBehavior casingBehavior)
at WebMatrix.WebData.WebSecurity.InitializeProviders(DatabaseConnectionInfo connect, String userTableName, String userIdColumn, String userNameColumn, Boolean autoCreateTables, SimpleMembershipProviderCasingBehavior casingBehavior)
at WebMatrix.WebData.WebSecurity.InitializeDatabaseConnection(String connectionStringName, String userTableName, String userIdColumn, String userNameColumn, Boolean autoCreateTables, SimpleMembershipProviderCasingBehavior casingBehavior)
at WebMatrix.WebData.WebSecurity.InitializeDatabaseConnection(String connectionStringName, String userTableName, String userIdColumn, String userNameColumn, Boolean autoCreateTables)
at PayrollContractual.MvcApplication.InitializeAuthenticationProcess() in C:UsersAdministratorsourcereposPayrollContractualPayrollContractualGlobal.asax.cs:line 35

Answer 1

默认的ASP。. NET Identity项目包含为SQL Server生成的现成迁移;尝试在PostgreSQL上应用它们将失败。你需要删除迁移目录，重新为PostgreSQL迁移。

详情见https://github.com/npgsql/efcore.pg/issues/463