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); } }