我写了这样一个静态类来生成智能实体:
public static class DBHelperExtension
{
public static List<Dictionary<string, string>> FillEntities(string sql, DbContext dbContext, object inputParams)
{
if (dbContext.CurrentCommand == null)
{
dbContext.CreateCommand(sql, CommandType.Text);
}
var pattern = @"@(\w+)";
MatchCollection collections = Regex.Matches(sql, pattern, RegexOptions.IgnoreCase);
foreach (System.Text.RegularExpressions.Match str in collections)
{
System.Data.SqlClient.SqlParameter parameter = new System.Data.SqlClient.SqlParameter(str.Value.Substring(1), DBNull.Value);
dbContext.CurrentCommand.Parameters.Add(parameter);
}
IDictionary dictionary = inputParams as IDictionary;
if (dictionary != null)
{
foreach (DbParameter parameter2 in dbContext.CurrentCommand.Parameters)
{
if ((parameter2.Direction == ParameterDirection.Input) || (parameter2.Direction == ParameterDirection.InputOutput))
{
parameter2.ParameterName = parameter2.ParameterName.Substring(1);
parameter2.Value = dictionary[parameter2] ?? DBNull.Value;
}
}
}
else
{
PropertyInfo[] propertys = inputParams.GetType().GetProperties();
foreach (PropertyInfo p in propertys)
{
if (dbContext.CurrentCommand.Parameters.Contains(p.Name))
{
if (p.GetValue(inputParams, null) != null)
dbContext.CurrentCommand.Parameters[p.Name].Value = p.GetValue(inputParams, null);
else
dbContext.CurrentCommand.Parameters[p.Name].Value = DBNull.Value;
}
}
}
List<Dictionary<string, string>> list = new List<Dictionary<string, string>>();
if (dbContext.Connection.State == ConnectionState.Closed)
{
dbContext.Connection.Open();
}
DbDataReader dbDataReader = dbContext.CurrentCommand.ExecuteReader();
while (dbDataReader.Read())
{
Dictionary<string, string> dictionary2 = new Dictionary<string, string>();
int ordinal = 0;
while (ordinal < dbDataReader.VisibleFieldCount)
{
string name = dbDataReader.GetName(ordinal);
object obj = dbDataReader.GetValue(ordinal);
dictionary2.Add(name, obj.ToString());
checked { ++ordinal; }
}
list.Add(dictionary2);
}
dbDataReader.Close();
return list;
}
}
然后这样调用:
var list = DBHelperExtension.FillEntities(sql, dbContext, new {PageSize = 0});
return list.ToJSON(); //返回: [{"ProductName":"三星9300" , "Unit" : "台"} , {"ProductName":"三星9100" , "Unit" : "台"}]