在Entity框架中设置应用程序角色。 [英] Setting application role in Entity framework.
问题描述
我需要WPF桌面我的应用程序使用应用程序角色连接到数据库。我试图使用DBInterceptor来做到这一点。以下是我使用的代码：
public class EFDBConnectionApplicationRoleInterception：IDbInterceptor
  {
      private string _sqlServerApplicationRoleName;
     私人字符串_password;
     私人字符串_dbname;
      private byte [] _cookie;
      public EFDBConnectionApplicationRoleInterception（）{}
      public EFDBConnectionApplicationRoleInterception（string sqlAppRoleName，string password，string dbname）
      {
          _sqlServerApplicationRoleName = sqlAppRoleName;
          _password =密码;
          _dbname = dbname;
      }
      public void Opened（DbConnection connection，System.Data.Entity.Infrastructure.Interception.DbInterceptionContext interceptionContext）
      {
          if（connection.State！= ConnectionState.Open）返回;
          if（！connection.Database.Equals（_dbname））返回;
          ActivateApplicationRole（connection，this._sqlServerApplicationRoleName，_password）;
      }
      public void Closing（DbConnection connection，DbInterceptionContext interceptionContext）
      {
          if（connection.State！= ConnectionState.Open）返回;
          if（！connection.Database.Equals（_dbname））返回;
          DeActivateApplicationRole（connection，_cookie）;
      }
      public void Disposing（DbConnection connection，DbInterceptionContext interceptionContext）
      {
          if（connection.State！= ConnectionState.Open）返回;
          if（！connection.Database.Equals（_dbname））返回;
          DeActivateApplicationRole（connection，_cookie）;
      }
      public virtual void ActivateApplicationRole（DbConnection dbConn，string appRoleName，string password）
      {
          if（dbConn == null）
             抛出新的ArgumentNullException（" DbConnection"）;
          if（ConnectionState.Open！= dbConn.State）
             抛出新的InvalidOperationException（"在激活应用程序角色之前必须打开DBConnection"）;
          if（string.IsNullOrWhiteSpace（appRoleName））
             抛出新的ArgumentNullException（" appRoleName"）;
          if（密码== null）
             抛出新的ArgumentNullException（" password"）;
         使用（DbCommand cmd = dbConn.CreateCommand（））
          {
              cmd.CommandType = CommandType.StoredProcedure;
              cmd.CommandText =" sp_setapprole";
              cmd.Parameters.Add（new SqlParameter（" @ rolename"，appRoleName））;
              cmd.Parameters.Add（new SqlParameter（" @ password"，password））;
              cmd.Parameters.Add（new SqlParameter（" @fCreateCookie"，SqlDbType.Bit）{Value = true}）;
              SqlParameter cookie = new SqlParameter（" @ cookie"，System.Data.SqlDbType.Binary，50）{Direction = System.Data.ParameterDirection.InputOutput};
                cmd.Parameters.Add（cookie）;
              cmd.ExecuteNonQuery（）;
$
              if（cookie.Value == null）
              {
                 抛出新的InvalidOperationException（"无法设置应用程序角色。"）;
              }¥b $ b               _cookie =（byte []）cookie.Value;
          }¥b $ b       }
      public virtual void DeActivateApplicationRole（DbConnection dbConn，byte [] cookie）
      {
         使用（DbCommand cmd = dbConn.CreateCommand（））
          {
              cmd.CommandText =" sp_unsetapprole";
              cmd.CommandType = CommandType.StoredProcedure;
              cmd.Parameters.Add（new SqlParameter（" @ cookie"，SqlDbType.VarBinary，50）{Value = cookie}）;
              cmd.ExecuteNonQuery（）;
          }¥b $ b       }
在我们使用数据库上下文之前注册它。
DbInterception.Add（new EFDBConnectionApplicationRoleInterception（" TestROLE"，" fdsad896＃） gfdbfdagh700mM"，" Dbname"））;
             使用（实体ent =新实体（））
              {
                  var temp = ent.tblUsers.ToList（）; }
然而，开放或者不调用拦截器中的闭合方法。我没有给应用程序角色任何权限，所以它不应该为 ent.tblUsers.ToList（）;返回任何值。但是，该应用程序没有任何问题。
$
我注意到的另一件事是 （（（System.Data.Entity.DbContext）（ent））。数据库.Connection）。在初始化实体后，State被关闭;服务器版本显示错误"（（（System.Data.Entity.DbContext）（ent））。Database.Connection）.ServerVersion'抛出类型为'System.InvalidOperationException'的
异常。但我仍然可以连接到数据库，所有执行都没有任何问题。请告诉我这里缺少的内容。
使用实体框架设置应用程序角色的任何其他建议也会有所帮助。
你好，
请查看此链接IDbInterceptor的详细说明： https://entityframework.codeplex.com/wikipage?title=Interception
I need WPF desktop my application to connect to the database using application role. I am trying to do this using DBInterceptor. Following is the code i have used:
public class EFDBConnectionApplicationRoleInterception : IDbInterceptor
{
private string _sqlServerApplicationRoleName;
private string _password;
private string _dbname;
private byte[] _cookie;
public EFDBConnectionApplicationRoleInterception() { }
public EFDBConnectionApplicationRoleInterception(string sqlAppRoleName, string password, string dbname)
{
_sqlServerApplicationRoleName = sqlAppRoleName;
_password = password;
_dbname = dbname;
}
public void Opened(DbConnection connection, System.Data.Entity.Infrastructure.Interception.DbInterceptionContext interceptionContext)
{
if (connection.State != ConnectionState.Open) return;
if (!connection.Database.Equals(_dbname)) return;
ActivateApplicationRole(connection, this._sqlServerApplicationRoleName, _password);
}
public void Closing(DbConnection connection, DbInterceptionContext interceptionContext)
{
if (connection.State != ConnectionState.Open) return;
if (!connection.Database.Equals(_dbname)) return;
DeActivateApplicationRole(connection, _cookie);
}
public void Disposing(DbConnection connection, DbInterceptionContext interceptionContext)
{
if (connection.State != ConnectionState.Open) return;
if (!connection.Database.Equals(_dbname)) return;
DeActivateApplicationRole(connection, _cookie);
}
public virtual void ActivateApplicationRole(DbConnection dbConn, string appRoleName, string password)
{
if (dbConn == null)
throw new ArgumentNullException("DbConnection");
if (ConnectionState.Open != dbConn.State)
throw new InvalidOperationException("DBConnection must be opened before activating application role");
if (string.IsNullOrWhiteSpace(appRoleName))
throw new ArgumentNullException("appRoleName");
if (password == null)
throw new ArgumentNullException("password");
using (DbCommand cmd = dbConn.CreateCommand())
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_setapprole";
cmd.Parameters.Add(new SqlParameter("@rolename", appRoleName));
cmd.Parameters.Add(new SqlParameter("@password", password));
cmd.Parameters.Add(new SqlParameter("@fCreateCookie", SqlDbType.Bit) { Value = true });
SqlParameter cookie = new SqlParameter("@cookie", System.Data.SqlDbType.Binary, 50) { Direction = System.Data.ParameterDirection.InputOutput };
cmd.Parameters.Add(cookie);
cmd.ExecuteNonQuery();
if (cookie.Value == null)
{
throw new InvalidOperationException("Failed to set application role.");
}
_cookie = (byte[])cookie.Value;
}
}
public virtual void DeActivateApplicationRole(DbConnection dbConn, byte[] cookie)
{
using (DbCommand cmd = dbConn.CreateCommand())
{
cmd.CommandText = "sp_unsetapprole";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@cookie", SqlDbType.VarBinary, 50) { Value = cookie });
cmd.ExecuteNonQuery();
}
}
Register it before we use our database context.
DbInterception.Add(new EFDBConnectionApplicationRoleInterception("TestROLE", "fdsad896#gfdbfdagh700mM", "Dbname"));
using (Entities ent = new Entities())
{
var temp = ent.tblUsers.ToList();}
However, the open or closed methods in the interceptor is not called. I have not given any permission to the application role, so it shouldnt return any values for ent.tblUsers.ToList(); However, the application works without any issue.
Another thing i noticed is (((System.Data.Entity.DbContext)(ent)).Database.Connection).State is Closed, after initializing Entities; Server Version shows an error "(((System.Data.Entity.DbContext)(ent)).Database.Connection).ServerVersion' threw an
exception of type 'System.InvalidOperationException". But i am still able to connect to the database and all the executions takes place without any issue. Please let me know what I am missing here.
Any other suggestion to set the application role using entity framework will also help.
Hello,
Please check this link for a detail description for IDbInterceptor:https://entityframework.codeplex.com/wikipage?title=Interception
这篇关于在Entity框架中设置应用程序角色。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
