public List<Dictionary<String, Object>> QueryData(string Sql, Dictionary<string, dynamic> parmas = null)
{
lock (exeLock)
{
List<Dictionary<String, object>> list = new List<Dictionary<string, object>>();
Dictionary<String, object> dic = null;
using (var cmd = _context.Database.GetDbConnection().CreateCommand())
{
cmd.CommandText = Sql;
if (cmd.Connection.State != ConnectionState.Open)
{
cmd.Connection.Open();
}
DbParameter[] parameters = null;
if (parmas != null && parmas.Count > 0)
{
parameters = new DbParameter[parmas.Count];
int index = -1;
foreach (var item in parmas)
{
index = index + 1;
parameters[index] = new SqlParameter(item.Key, (object)item.Value);
}
cmd.Parameters.Add(parameters);
}
using (var dataReader = cmd.ExecuteReader())
{
string colName = "";
while (dataReader.Read())
{
dic = new Dictionary<string, object>(StringComparer.OrdinalIgnoreCase);
for (int i = 0; i < dataReader.FieldCount; i++)
{
colName = dataReader.GetName(i);
if (!dic.ContainsKey(colName))
{
dic.Add(colName, dataReader[colName]);
}
}
if (dic.Keys.Count > 0)
{
list.Add(dic);
}
}
cmd.Parameters.Clear();
dataReader.Close();
}
}
return list;
}
}
public List<Dictionary<string, Object>> QueryPage(string sql, string orderBy, int pageIndex, int pageSize, out int count, Dictionary<string, dynamic> param = null)
{
count = 0;
List<Dictionary<String, Object>> list = new List<Dictionary<string, object>>();
string strSQL = "select * from (select t.*,row_number() over(order by " + orderBy + ") rownum from (" + sql + ") t ) t0 where rownum>" + ((pageIndex - 1) * pageSize).ToString() + " and rownum<=" + (pageIndex * pageSize).ToString() + "";
string strCount = "SELECT count(*) as count FROM (" + sql + ") tcount";
list = QueryData(strSQL, param);
var data = QueryData(strCount);
if (data != null && data.Count > 0)
{
count = Convert.ToInt32(data.FirstOrDefault()["count"]);
}
return list;
}