public ExpandoObject ExecuteExpandoObject(string sql)
{
if (SQLWordFilte.CheckSql(sql))
{
throw new Exception("您提供的关键字有可能危害数据库，已阻止执行");
}
DataSet ds = ExecuteQuery(string.Format(sql));
if (ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
{
return(null);
}
foreach (DataTable table in ds.Tables)
{
foreach (DataRow row in table.Rows)
{
IDictionary <string, object> expando = new ExpandoObject();
foreach (DataColumn column in table.Columns)
{
expando.Add(column.Caption, row[column]);
}
return((ExpandoObject)expando);
}
}
return(null);
}
public IEnumerable <ExpandoObject> ExecuteExpandoObjects(string sql, params SqlParameter[] cmdParms)
{
if (SQLWordFilte.CheckSql(sql))
{
throw new Exception("您提供的关键字有可能危害数据库，已阻止执行");
}
DataSet ds = ExecuteQuery(string.Format(sql), cmdParms);
if (ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
{
yield break;
}
foreach (DataTable table in ds.Tables)
{
foreach (DataRow row in table.Rows)
{
IDictionary <string, object> expando = new ExpandoObject();
foreach (DataColumn column in table.Columns)
{
expando.Add(column.Caption, row[column]);
}
yield return((ExpandoObject)expando);
}
}
}
public T GetResult <T>(string sql, SqlParameter[] para)
{
if (SQLWordFilte.CheckSql(sql))
{
throw new Exception("您提供的关键字有可能危害数据库，已阻止执行");
}
try
{
DataSet ds = ExecuteQuery(sql, para);
if (ds.Tables.Count == 0)
{
return(default(T));
}
if (ds.Tables[0].Rows.Count == 0)
{
return(default(T));
}
object v = ds.Tables[0].Rows[0][0];
return((T)Convert.ChangeType(v, typeof(T)));
}
catch (Exception)
{
return(default(T));
}
}
/// <summary>
/// 查询一系列实体对象
/// </summary>
/// <param name="where"></param>
/// <returns></returns>
public List <T> Query(string @where, IDictionary <string, object> paras, string order = "")
{
if (SQLWordFilte.CheckKeyWord(@where))
{
throw new Exception("您提供的关键字有可能危害数据库，已阻止执行");
}
var entity = new T();
string sql = entity.GetQuerySQL(@where);
if (!order.IsNullOrEmpty())
{
sql += " order by " + order;
}
ISQLContext sqlContext = new SQLContext(Session);
var paramters = new List <SqlParameter>();
if (paras != null)
{
foreach (var para in paras)
{
paramters.Add(new SqlParameter(para.Key, para.Value));
}
}
var reader = sqlContext.ExecuteQueryReader(sql, paramters.ToArray());
return(DataReaderHelper.ReaderToList <T>(reader));
}
public static List <T> QueryOrder <T, TP, TO>(this IDBContext <T> context, Expression <Func <T, TP> > expression, string where,
IDictionary <string, object> paras, Expression <Func <TP, TO> > orderexpression) where T : BaseEntity <T>, new()
{
var props = ExpressionHelper.GetProps(expression);
var order = orderexpression == null ? "" : ExpressionHelper.GetProps(orderexpression)[0];
if (SQLWordFilte.CheckKeyWord(@where))
{
throw new Exception("您提供的关键字有可能危害数据库，已阻止执行");
}
var entity = new T();
string sql = entity.GetQuerySQL(@where);
var cols = string.Join(",", props);
sql = string.Format("select {0} from ( {1} ) Tab ", cols, sql);
if (!order.IsNullOrEmpty())
{
sql += " order by " + order;
}
ISQLContext sqlContext = new SQLContext(context.Session);
var paramters = new List <SqlParameter>();
if (paras != null)
{
foreach (var para in paras)
{
paramters.Add(new SqlParameter(para.Key, para.Value));
}
}
var reader = sqlContext.ExecuteQueryReader(sql, paramters.ToArray());
return(DataReaderHelper.ReaderToList <T>(reader));
}
public object GetResult(string sql)
{
if (SQLWordFilte.CheckSql(sql))
{
throw new Exception("您提供的关键字有可能危害数据库，已阻止执行");
}
try
{
DataSet ds = ExecuteQuery(sql);
if (ds.Tables.Count == 0)
{
return(null);
}
if (ds.Tables[0].Rows.Count == 0)
{
return(null);
}
object v = ds.Tables[0].Rows[0][0];
return(v);
}
catch (Exception ex)
{
throw new Exception(ex.Message + "\r\n" + sql);
}
}
public DataSet ExecuteQuery(string sql, params SqlParameter[] cmdParms)
{
if (SQLWordFilte.CheckSql(sql))
{
throw new Exception("您提供的关键字有可能危害数据库，已阻止执行");
}
try
{
Session.Open();
var ds = new DataSet();
var cmd = new SqlCommand();
cmd = PrepareCommand(cmd, sql, cmdParms);
cmd.Connection = Session.Connection;
var command = new SqlDataAdapter(cmd);
command.Fill(ds);
return(ds);
}
catch (SqlException ex)
{
throw new Exception(ex.Message + "\r\n" + sql);
}
finally
{
//session.Connection.Close();
}
}
/// <summary>
/// 更新一个对象
/// </summary>
/// <param name="entity"></param>
public void Update(T entity, string where = "", IDictionary <string, object> paras = null)
{
if (SQLWordFilte.CheckKeyWord(@where))
{
throw new Exception("您提供的关键字有可能危害数据库，已阻止执行");
}
DbCommand cmd = CreateSqlCommand(entity.GetUpdateCmd(where, paras));
Session.AddCommands(cmd, entity);
}
public void ExecuteSQL(string sql)
{
if (SQLWordFilte.CheckSql(sql))
{
throw new Exception("您提供的关键字有可能危害数据库，已阻止执行");
}
var cmd = new SqlCommand(sql);
Session.AddCommands(cmd);
//int rows = Session.Commit();
//return rows;
}
/// <summary>
/// 分页查询
/// </summary>
/// <param name="where"></param>
/// <param name="order"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="total"></param>
/// <returns></returns>
public PageResult <T> QueryPage(string @where, IDictionary <string, object> paras, string order, int pageIndex,
int pageSize)
{
if (SQLWordFilte.CheckKeyWord(@where))
{
throw new Exception("您提供的关键字有可能危害数据库，已阻止执行");
}
var parasList = new List <SqlParameter>();
var parasListData = new List <SqlParameter>();
if (paras != null)
{
foreach (var para in paras)
{
parasList.Add(new SqlParameter(para.Key, para.Value));
parasListData.Add(new SqlParameter(para.Key, para.Value));
}
}
ISQLContext sqlContext = new SQLContext(Session);
var entity = new T();
string sql = entity.GetQuerySql(@where);
var total = sqlContext.GetResult <int>(string.Format("SELECT COUNT(1) FROM ({0}) a", sql), parasList.ToArray());
int start = (pageIndex - 1) * pageSize;
var tempsql = "select *,ROW_NUMBER() OVER(ORDER BY " + order + ") rn from ({0}) a ";
sql = string.Format(tempsql, sql);
sql = "SELECT TOP " + pageSize + " * FROM (" + sql + ") query WHERE rn > " + start + " ORDER BY rn";
var reader = (DbDataReader)sqlContext.ExecuteQueryReader(sql, parasListData.ToArray());
List <T> listdata = new List <T>();
using (reader)
{
var tuple = reader.GetDeserializerState <T>();
while (reader.Read())
{
listdata.Add((T)tuple.Func(reader));
}
}
/*foreach (var data in entities)
* {
* data.ClearStack();
* }*/
return(new PageResult <T>(listdata, total));
}
public IDataReader ExecuteQueryReader(string sql)
{
if (SQLWordFilte.CheckSql(sql))
{
throw new Exception("您提供的关键字有可能危害数据库，已阻止执行");
}
try
{
Session.Open();
var cmd = new SqlCommand(sql);
cmd.Connection = Session.Connection;
return(cmd.ExecuteReader());
}
catch (SqlException ex)
{
throw new Exception(ex.Message + "\r\n" + sql);
}
}
public void Delete(string @where = "", IDictionary <string, object> paras = null)
{
if (SQLWordFilte.CheckKeyWord(where))
{
throw new Exception("您提供的关键字有可能危害数据库，已阻止执行");
}
var entity = new T();
string cmdtext = entity.GetDelSQL(null, where);
var cmd = new SqlCommand(cmdtext);
if (paras != null)
{
foreach (var para in paras)
{
cmd.Parameters.Add(new SqlParameter(para.Key, para.Value));
}
}
Session.AddCommands(cmd);
}
/// <summary>
/// 查询分页数据-部分字段
/// </summary>
/// <param name="where"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="total"></param>
/// <returns></returns>
public static PageResult <T> QueryPage <T, TP, TO>(this IDBContext <T> context, Expression <Func <T, TP> > expression, string where,
IDictionary <string, object> paras,
Expression <Func <TP, TO> > orderexpression, int pageIndex, int pageSize) where T : BaseEntity <T>, new()
{
if (SQLWordFilte.CheckKeyWord(@where))
{
throw new Exception("您提供的关键字有可能危害数据库，已阻止执行");
}
var props = ExpressionHelper.GetProps(expression);
var order = orderexpression == null ? "" : ExpressionHelper.GetProps(orderexpression)[0];
var parasList = new List <SqlParameter>();
var parasListData = new List <SqlParameter>();
if (paras != null)
{
foreach (var para in paras)
{
parasList.Add(new SqlParameter(para.Key, para.Value));
parasListData.Add(new SqlParameter(para.Key, para.Value));
}
}
ISQLContext sqlContext = new SQLContext(context.Session);
var entity = new T();
string sql = entity.GetQuerySQL(@where);
var cols = string.Join(",", props);
sql = string.Format("select {0} from ( {1} ) Tab ", cols, sql);
var total = sqlContext.GetResult <int>(string.Format("SELECT COUNT(1) FROM ({0}) a", sql), parasList.ToArray());
int start = (pageIndex - 1) * pageSize;
var tempsql = "select *,ROW_NUMBER() OVER(ORDER BY " + order + ") rn from ({0}) a ";
sql = string.Format(tempsql, sql);
sql = "SELECT TOP " + pageSize + " * FROM (" + sql + ") query WHERE rn > " + start + " ORDER BY rn";
var reader = sqlContext.ExecuteQueryReader(sql, parasListData.ToArray());
var entities = DataReaderHelper.ReaderToList <T>(reader);
return(new PageResult <T>(entities, total));
}
/// <summary>
/// 分页查询
/// </summary>
/// <param name="where"></param>
/// <param name="order"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="total"></param>
/// <returns></returns>
public PageResult <T> QueryPage(string @where, IDictionary <string, object> paras, string order, int pageIndex,
int pageSize)
{
if (SQLWordFilte.CheckKeyWord(@where))
{
throw new Exception("您提供的关键字有可能危害数据库，已阻止执行");
}
var parasList = new List <SqlParameter>();
var parasListData = new List <SqlParameter>();
if (paras != null)
{
foreach (var para in paras)
{
parasList.Add(new SqlParameter(para.Key, para.Value));
parasListData.Add(new SqlParameter(para.Key, para.Value));
}
}
ISQLContext sqlContext = new SQLContext(Session);
var entity = new T();
string sql = entity.GetQuerySQL(@where);
var total = sqlContext.GetResult <int>(string.Format("SELECT COUNT(1) FROM ({0}) a", sql), parasList.ToArray());
int start = (pageIndex - 1) * pageSize;
var tempsql = "select *,ROW_NUMBER() OVER(ORDER BY " + order + ") rn from ({0}) a ";
sql = string.Format(tempsql, sql);
sql = "SELECT TOP " + pageSize + " * FROM (" + sql + ") query WHERE rn > " + start + " ORDER BY rn";
var reader = sqlContext.ExecuteQueryReader(sql, parasListData.ToArray());
var entities = DataReaderHelper.ReaderToList <T>(reader);
return(new PageResult <T>(entities, total));
}
/// <summary>
/// 查询一系列实体对象
/// </summary>
/// <param name="where"></param>
/// <param name="paras"></param>
/// <param name="order"></param>
/// <returns></returns>
public List <T> Query(string @where, IDictionary <string, object> paras, string order = "")
{
if (SQLWordFilte.CheckKeyWord(@where))
{
throw new Exception("您提供的关键字有可能危害数据库，已阻止执行");
}
var entity = new T();
string sql = entity.GetQuerySql(@where);
if (!order.IsNullOrEmpty())
{
sql += " order by " + order;
}
ISQLContext sqlContext = new SQLContext(Session);
var paramters = new List <SqlParameter>();
if (paras != null)
{
foreach (var para in paras)
{
paramters.Add(new SqlParameter(para.Key, para.Value));
}
}
var reader = sqlContext.ExecuteQueryReader(sql, paramters.ToArray());
List <T> listdata = new List <T>();
using (reader)
{
var tuple = reader.GetDeserializerState <T>();
while (reader.Read())
{
listdata.Add((T)tuple.Func(reader));
}
}
return(listdata);
}
public IEnumerable <T> GetRowsResults <T>(string sql)
{
if (SQLWordFilte.CheckSql(sql))
{
throw new Exception("您提供的关键字有可能危害数据库，已阻止执行");
}
DataSet ds = ExecuteQuery(sql);
if (ds.Tables.Count == 0)
{
yield return(default(T));
}
if (ds.Tables[0].Rows.Count == 0)
{
yield return(default(T));
}
foreach (DataRow r in ds.Tables[0].Rows)
{
object v = r[0];
yield return((T)Convert.ChangeType(v, typeof(T)));
}
}
public DataSet ExecuteQuery(string sql)
{
if (SQLWordFilte.CheckSql(sql))
{
throw new Exception("您提供的关键字有可能危害数据库，已阻止执行");
}
try
{
Session.Open();
var ds = new DataSet();
var cmd = new SqlCommand(sql)
{
Connection = Session.Connection
};
var command = new SqlDataAdapter(cmd);
command.Fill(ds);
return(ds);
}
catch (SqlException ex)
{
throw new Exception(ex.Message);
}
}
