/// <summary>
/// 使用EF框架获取数据，返回DataTable
/// </summary>
/// <param name="type"></param>
/// <param name="strSql"></param>
/// <param name="parameterNames"></param>
/// <param name="parameterValues"></param>
/// <returns></returns>
public DataTable GetDataTableWithEF(Type type, string strSql, string[] parameterNames, string[] parameterValues)
{
var table = new DataTable("DataTableWithEF");
DbRawSqlQuery datas =
dbContext.Database.SqlQuery(type, strSql, DBFactory.ChangeToDbParams(parameterNames, parameterValues));
foreach (var data in datas)
{
var row = table.NewRow();
foreach (var info in type.GetProperties())
{
if (table.Rows.Count <= 0)
{
table.Columns.Add(info.Name);
}
var value = info.GetValue(data, null) ?? DBNull.Value;
row[info.Name] = SafeConverter.SafeToStr(value);
}
table.Rows.Add(row);
}
return(table);
}
/// <summary>
/// 使用EF框架获取第一行第一列数据
/// </summary>
/// <param name="strSql"></param>
/// <param name="parameterNames"></param>
/// <param name="parameterValues"></param>
/// <returns></returns>
public List <string> GetFirstRowWithEF(Type type, string strSql, string[] parameterNames, string[] parameterValues)
{
List <string> result = new List <string>();
if (type == null)
{
return(result);
}
DbRawSqlQuery datas = dbContext.Database.SqlQuery(type, strSql, DBFactory.ChangeToDbParams(parameterNames, parameterValues));
foreach (var data in datas)
{
foreach (var info in type.GetProperties())
{
var value = info.GetValue(data, null) ?? DBNull.Value;
result.Add(SafeConverter.SafeToStr(value));
}
return(result);
}
return(result);
}
/// <summary>
/// 使用EF框架根据SQL语句获取记录总数
/// SQL语句为记录的总数
/// </summary>
/// <param name="strSql">获取记录总数的SQL语句 SELECT COUNT(*)</param>
/// <param name="parameterNames"></param>
/// <param name="parameterValues"></param>
/// <returns></returns>
public int RowCountWithEF(string strSql, string[] parameterNames, string[] parameterValues)
{
return(dbContext.Database.SqlQuery <int>(strSql, DBFactory.ChangeToDbParams(parameterNames, parameterValues)).FirstOrDefault());
}
public bool ExecuteSql(string strSql, string[] parameterNames, string[] parameterValues)
{
return(SqlHelper.ExecuteNonQuery(strSql, DBFactory.ChangeToDbParams(parameterNames, parameterValues)) == "OK");
}
public DataTable GetDataTable(string strSql, string[] parameterNames, string[] parameterValues)
{
return(SqlHelper.GetDataTable(strSql, DBFactory.ChangeToDbParams(parameterNames, parameterValues)));
}
public List <string> GetFirstRow(string strSql, string[] parameterNames, string[] parameterValues)
{
return(SqlHelper.GetFirstRow(strSql, DBFactory.ChangeToDbParams(parameterNames, parameterValues)));
}
public string GetScalar(string strSql, string[] parameterNames, string[] parameterValues)
{
string str = SqlHelper.GetFirstValue(strSql, DBFactory.ChangeToDbParams(parameterNames, parameterValues));
return(str);
}
/// <summary>
/// 使用EF框架执行SQL语句
/// </summary>
/// <param name="strSql"></param>
/// <param name="parameterNames"></param>
/// <param name="parameterValues"></param>
/// <returns></returns>
public bool ExecuteSqlWithEF(string strSql, string[] parameterNames, string[] parameterValues)
{
var result = dbContext.Database.ExecuteSqlCommand(strSql, DBFactory.ChangeToDbParams(parameterNames, parameterValues));
return(result > 0);
}
