让我们假设我有一个有效的需要直接执行实体框架中的sql命令。我很难弄清楚如何在我的sql语句中使用参数。下面的例子(不是我的真实例子)不起作用。
var firstName = "John";
var id = 12;
var sql = @"Update [User] SET FirstName = @FirstName WHERE Id = @Id";
ctx.Database.ExecuteSqlCommand(sql, firstName, id);
ExecuteSqlCommand方法不允许像ADO那样传入命名参数。Net和此方法的文档没有给出如何执行参数化查询的任何示例。
如何正确地指定参数?
当前回答
public static class DbEx {
public static IEnumerable<T> SqlQueryPrm<T>(this System.Data.Entity.Database database, string sql, object parameters) {
using (var tmp_cmd = database.Connection.CreateCommand()) {
var dict = ToDictionary(parameters);
int i = 0;
var arr = new object[dict.Count];
foreach (var one_kvp in dict) {
var param = tmp_cmd.CreateParameter();
param.ParameterName = one_kvp.Key;
if (one_kvp.Value == null) {
param.Value = DBNull.Value;
} else {
param.Value = one_kvp.Value;
}
arr[i] = param;
i++;
}
return database.SqlQuery<T>(sql, arr);
}
}
private static IDictionary<string, object> ToDictionary(object data) {
var attr = System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Instance;
var dict = new Dictionary<string, object>();
foreach (var property in data.GetType().GetProperties(attr)) {
if (property.CanRead) {
dict.Add(property.Name, property.GetValue(data, null));
}
}
return dict;
}
}
用法:
var names = db.Database.SqlQueryPrm<string>("select name from position_category where id_key=@id_key", new { id_key = "mgr" }).ToList();
其他回答
你可以:
1)传递原始参数并使用{0}语法。例句:
DbContext.Database.SqlQuery("StoredProcedureName {0}", paramName);
2)传递DbParameter子类参数并使用@ParamName语法。
DbContext.Database.SqlQuery("StoredProcedureName @ParamName",
new SqlParameter("@ParamName", paramValue);
如果使用第一种语法，EF实际上会用dbparameter类包装参数，为它们分配名称，并用生成的参数名称替换{0}。
首选第一种语法，因为您不需要使用工厂或知道要创建什么类型的dbparameters (SqlParameter, oracleparameter，等等)。
public static class DbEx {
public static IEnumerable<T> SqlQueryPrm<T>(this System.Data.Entity.Database database, string sql, object parameters) {
using (var tmp_cmd = database.Connection.CreateCommand()) {
var dict = ToDictionary(parameters);
int i = 0;
var arr = new object[dict.Count];
foreach (var one_kvp in dict) {
var param = tmp_cmd.CreateParameter();
param.ParameterName = one_kvp.Key;
if (one_kvp.Value == null) {
param.Value = DBNull.Value;
} else {
param.Value = one_kvp.Value;
}
arr[i] = param;
i++;
}
return database.SqlQuery<T>(sql, arr);
}
}
private static IDictionary<string, object> ToDictionary(object data) {
var attr = System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Instance;
var dict = new Dictionary<string, object>();
foreach (var property in data.GetType().GetProperties(attr)) {
if (property.CanRead) {
dict.Add(property.Name, property.GetValue(data, null));
}
}
return dict;
}
}
用法:
var names = db.Database.SqlQueryPrm<string>("select name from position_category where id_key=@id_key", new { id_key = "mgr" }).ToList();
试试这个:
var sql = @"Update [User] SET FirstName = @FirstName WHERE Id = @Id";
ctx.Database.ExecuteSqlCommand(
sql,
new SqlParameter("@FirstName", firstname),
new SqlParameter("@Id", id));
var firstName = "John";
var id = 12;
ctx.Database.ExecuteSqlCommand(@"Update [User] SET FirstName = {0} WHERE Id = {1}"
, new object[]{ firstName, id });
这太简单了!!
了解参数参考的图像
事实证明这是可行的。
var firstName = "John";
var id = 12;
var sql = "Update [User] SET FirstName = {0} WHERE Id = {1}";
ctx.Database.ExecuteSqlCommand(sql, firstName, id);
