我正在使用Entity Framework开发大型政府应用程序。一开始我遇到了一个关于启用SQL应用角色的问题。使用ADO.NET,我使用以下代码:
SqlCommand cmd = new SqlCommand("sys.sp_setapprole");
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = _sqlConn;
SqlParameter paramAppRoleName = new SqlParameter();
paramAppRoleName.Direction = ParameterDirection.Input;
paramAppRoleName.ParameterName = "@rolename";
paramAppRoleName.Value = "AppRole";
cmd.Parameters.Add(paramAppRoleName);
SqlParameter paramAppRolePwd = new SqlParameter();
paramAppRolePwd.Direction = ParameterDirection.Input;
paramAppRolePwd.ParameterName = "@password";
paramAppRolePwd.Value = "123456";
cmd.Parameters.Add(paramAppRolePwd);
SqlParameter paramCreateCookie = new SqlParameter();
paramCreateCookie.Direction = ParameterDirection.Input;
paramCreateCookie.ParameterName = "@fCreateCookie";
paramCreateCookie.DbType = DbType.Boolean;
paramCreateCookie.Value = 1;
cmd.Parameters.Add(paramCreateCookie);
SqlParameter paramEncrypt = new SqlParameter();
paramEncrypt.Direction = ParameterDirection.Input;
paramEncrypt.ParameterName = "@encrypt";
paramEncrypt.Value = "none";
cmd.Parameters.Add(paramEncrypt);
SqlParameter paramEnableCookie = new SqlParameter();
paramEnableCookie.ParameterName = "@cookie";
paramEnableCookie.DbType = DbType.Binary;
paramEnableCookie.Direction = ParameterDirection.Output;
paramEnableCookie.Size = 1000;
cmd.Parameters.Add(paramEnableCookie);
try
{
cmd.ExecuteNonQuery();
SqlParameter outVal = cmd.Parameters["@cookie"];
// Store the enabled cookie so that approle can be disabled with the cookie.
_appRoleEnableCookie = (byte[]) outVal.Value;
}
catch (Exception ex)
{
result = false;
msg = "Could not execute enable approle proc." + Environment.NewLine + ex.Message;
}
但无论我怎么搜索,都找不到在EF上实现的方法。
另一个问题是:如何将应用程序角色添加到实体数据模型设计器中?
我正在使用以下代码执行EF参数:
AEntities ar = new AEntities();
DbConnection con = ar.Connection;
con.Open();
msg = "";
bool result = true;
DbCommand cmd = con.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = con;
var d = new DbParameter[]{
new SqlParameter{ ParameterName="@r", Value ="AppRole",Direction = ParameterDirection.Input}
, new SqlParameter{ ParameterName="@p", Value ="123456",Direction = ParameterDirection.Input}
};
string sql = "EXEC " + procName + " @rolename=@r,@password=@p";
var s = ar.ExecuteStoreCommand(sql, d);
当运行ExecuteStoreCommand时,该行会返回错误:
应用程序角色只能在临时级别激活。
'EXEC sys.sp_setapprole @rolename, @password, @fCreateCookie, ...'
,并传递SqlParameters以提供命令中参数占位符的值。 - Pawel