/// <summary> /// 更新数据集中的某数据表 /// </summary> /// <param name="selectSQL">生成数据集的 SELECT SQL 语句</param> /// <param name="sqlParameterCollection">SQL 参数集合</param> /// <param name="dataTableName">数据表名,不必与数据库中的一致</param> /// <param name="dataSet">需要更新的数据集</param> /// <param name="commandTimeout">数据库更新的超时时间,0或负数表示采用缺省设置</param> public abstract bool Update(string selectSQL, SqlParameterCollection sqlParameterCollection, string dataTableName, DataSet dataSet, int commandTimeout);
/// <summary> /// 执行 SELECT SQL 语句,分页提取数据,返回 DataTable /// </summary> /// <param name="selectSQL">执行的 Select SQL 语句,注意:不要带 order by 语句</param> /// <param name="sqlParameterCollection">SQL 参数集合</param> /// <param name="dataTableName">数据表名,不必与数据库的一致</param> /// <param name="orderby">排序字段,注意:不要带 order by 关键字,如: order1 desc,order2 asc</param> /// <param name="pageSize">每页最大记录数</param> /// <param name="pageIndex">提取第几页的数据,从 0 开始。</param> /// <returns>生成数据集</returns> public DataTable ExeSqlForDataTable(string selectSQL, SqlParameterCollection sqlParameterCollection, string dataTableName, string orderby, int pageSize, int pageIndex) { int allRecordCount; return(this.ExeSqlForDataTable(selectSQL, sqlParameterCollection, dataTableName, orderby, pageSize, pageIndex, out allRecordCount)); }
/// <summary> /// 更新数据表 /// </summary> /// <param name="selectSQL">生成数据集的 SELECT SQL 语句</param> /// <param name="sqlParameterCollection">SQL 参数集合</param> /// <param name="dataTable">需要更新的数据集</param> public bool Update(string selectSQL, SqlParameterCollection sqlParameterCollection, DataTable dataTable) { return(this.Update(selectSQL, sqlParameterCollection, dataTable, 0)); }
/// <summary> /// 更新数据集中的某数据表 /// </summary> /// <param name="selectSQL">生成数据集的 SELECT SQL 语句</param> /// <param name="sqlParameterCollection">SQL 参数集合</param> /// <param name="dataTableName">数据表名,不必与数据库中的一致</param> /// <param name="dataSet">需要更新的数据集</param> public virtual bool Update(string selectSQL, SqlParameterCollection sqlParameterCollection, string dataTableName, DataSet dataSet) { return(this.Update(selectSQL, sqlParameterCollection, dataTableName, dataSet, 0)); }
/// <summary> /// 执行 SELECT SQL 语句,得到一个 string 类型的数组 /// </summary> /// <param name="selectSQL">执行的 Select SQL 语句</param> /// <param name="sqlParameterCollection">SQL 参数集合</param> /// <returns>得到一个字符串数组</returns> public string[] ExeSqlForStringArray(string selectSQL, SqlParameterCollection sqlParameterCollection) { ArrayList al = this.ExeSqlForArrayList(selectSQL, sqlParameterCollection); return((string[])al.ToArray(typeof(string))); }
/// <summary> /// 注意,本函数不对commandSQL进行安全处理,以允许批量执行sql语句 /// </summary> /// <param name="commandSQL"></param> /// <param name="sqlParameterCollection"></param> /// <returns></returns> public abstract int ExecuteNonQuerySql2(string commandSQL, SqlParameterCollection sqlParameterCollection);
/// <summary> /// 执行 SELECT SQL 语句,将新数据表加入到 DataSet 中. /// </summary> /// <param name="selectSQL">生成数据集的 Select SQL</param> /// <param name="sqlParameterCollection">SQL 参数集合</param> /// <param name="dataTableName">数据表名,不必与数据库中的一致</param> /// <param name="dataSet">目标数据集</param> public abstract void AddTableToDataSet(string selectSQL, SqlParameterCollection sqlParameterCollection, string dataTableName, DataSet dataSet);
/// <summary> /// 将 IDataItem 转换成 查询条件 SQL + SqlParameterCollection /// </summary> /// <param name="dataitem">数据项集合</param> /// <param name="spc">返回 SQL 参数的集合</param> /// <param name="searchSQL">返回查询条件 SQL 语句</param> /// <param name="isAndRelation">dataitem与dataitem之间是否是And关系.true表示And关系,false表示Or关系.</param> /// <param name="isAndRelation2">dataitem集合与其它集合之间是否是And关系.true表示And关系,false表示Or关系.</param> /// <returns></returns> public static void GetSearchClause(this List <IDataItem> dataitem, SqlParameterCollection spc, ref string searchSQL, bool isAndRelation, bool isAndRelation2) { GetSearchClause(dataitem, DataBaseType.SQLSERVER2008, spc, ref searchSQL, isAndRelation, isAndRelation2); }
/// <summary> /// 执行 SELECT SQL 语句,返回 DataSet /// </summary> /// <param name="selectSQL">生成数据集的 Select SQL</param> /// <param name="sqlParameterCollection">SQL 参数集合</param> /// <param name="dataTableName">数据表名,不必与数据库的一致</param> /// <returns>生成数据集</returns> public abstract DataSet ExeSqlForDataSet(string selectSQL, SqlParameterCollection sqlParameterCollection, string dataTableName);
/// <summary> /// 执行 SELECT SQL 语句,返回 DataSet,其中可以同时包含多个DataTable /// </summary> /// <param name="selectSQL">生成数据集的 Select SQL</param> /// <param name="sqlParameterCollection">SQL 参数集合</param> /// <returns>生成数据集</returns> public abstract DataSet ExeSqlForDataSetWithMultiDataTable(string selectSQL, SqlParameterCollection sqlParameterCollection);
/// <summary> /// 未实现 /// </summary> /// <returns></returns> public override object GetDataReader(string commandSQL, SqlParameterCollection sqlParameterCollection, CommandBehavior cmdBehavior) { return(null); }
/// <summary> /// 执行 INSERT/DELETE/UPDATE SQL 语句, 返回受影响的行数,当前为 SELECT SQL 语句时,返回 -1 /// </summary> /// <param name="commandSQL">INSERT/DELETE/UPDATE SQL 语句</param> /// <param name="sqlParameterCollection">SQL 参数集合</param> /// <returns>返回受影响的行数</returns> public override int ExecuteNonQuerySql(string commandSQL, SqlParameterCollection sqlParameterCollection) { commandSQL = Bigdesk8.Security.AntiSqlInjection.GetSafeSql(commandSQL); return(this.ExecuteNonQuerySql2(commandSQL, sqlParameterCollection)); }
public static void GetSearchClause(this List <IDataItem> dataitem, DataBaseType dbType, SqlParameterCollection spc, ref string searchSQL, bool isAndRelation, bool isAndRelation2) { if (dataitem == null) { return; } if (spc == null) { throw new Exception("参数:spc 不能为 null!"); } string sql = ""; foreach (IDataItem di in dataitem) { if (di.ItemName.IsEmpty() || di.ItemData.IsEmpty()) { continue; } /* 动态查询条件中的字段名往往也是前端输入的,特别是当查询条件拼凑成一个json串传到后台(例如:webservice)函数中的时候。 * 故需要对字段名的合法性进行基本的校验。2016-5-31 缪卫华 */ if (di.ItemName.IndexOfAny(new char[] { ' ', ';' }) >= 0 || di.ItemName.IndexOf("char(", StringComparison.OrdinalIgnoreCase) >= 0) { throw new Exception("非法的字段名:" + di.ItemName); } string data = di.ItemData; if (di.ItemType == DataType.Date) { switch (di.ItemRelation) { default: data = data.ToDate2(); break; case DataRelation.LessThan: case DataRelation.LessThanOrEqual: data = data.ToDate().ToString("yyyy-MM-dd 23:59:59"); break; } } string parameterName; switch (dbType) { case DataBaseType.ORACLE11G: parameterName = ":p" + (spc.Count + 1); break; default: parameterName = "@p" + (spc.Count + 1); break; } string temp = ""; switch (di.ItemRelation) { case DataRelation.Equal: { temp = di.ItemName + " = " + parameterName; spc.Add(parameterName, data); } break; case DataRelation.NotEqual: { temp = di.ItemName + " <> " + parameterName; spc.Add(parameterName, data); } break; case DataRelation.GreaterThan: { temp = di.ItemName + " > " + parameterName; spc.Add(parameterName, data); } break; case DataRelation.GreaterThanOrEqual: { temp = di.ItemName + " >= " + parameterName; spc.Add(parameterName, data); } break; case DataRelation.LessThan: { temp = di.ItemName + " < " + parameterName; spc.Add(parameterName, data); } break; case DataRelation.LessThanOrEqual: { temp = di.ItemName + " <= " + parameterName; spc.Add(parameterName, data); } break; case DataRelation.Like: { temp = di.ItemName + " like " + parameterName; spc.Add(parameterName, "%" + data + "%"); } break; case DataRelation.LeftLike: { temp = di.ItemName + " like " + parameterName; spc.Add(parameterName, "%" + data); } break; case DataRelation.RightLike: { temp = di.ItemName + " like " + parameterName; spc.Add(parameterName, data + "%"); } break; } if (temp == "") { continue; } sql = (sql == "") ? temp : (isAndRelation ? sql + " and " + temp : sql + " or " + temp); } searchSQL += (sql == "") ? string.Empty : (isAndRelation2 ? " and (" + sql + ")" : " or (" + sql + ")"); }
/// <summary> /// 返回一个DataReader,调用者自行负责转换成具体的DataReader /// </summary> /// <returns></returns> public abstract object GetDataReader(string commandSQL, SqlParameterCollection sqlParameterCollection, CommandBehavior cmdBehavior);
/// <summary> /// 执行 SELECT SQL 语句,得到一个字符串值 /// </summary> /// <param name="selectSQL">执行的 Select SQL 语句</param> /// <param name="sqlParameterCollection">SQL 参数集合</param> /// <returns>得到一个字符串值</returns> public string ExeSqlForString(string selectSQL, SqlParameterCollection sqlParameterCollection) { return(this.ExeSqlForObject(selectSQL, sqlParameterCollection).ToString2()); }
public static void GenerateSearchClauseAndSPC(Control cl, ref string strSqlCondition, Bigdesk8.Data.SqlParameterCollection spc) { List <IDataItem> list = cl.GetControlValue(); list.GetSearchClause(spc, ref strSqlCondition); }
public static void GetSearchClause(this List <IDataItem> dataitem, DataBaseType dbType, SqlParameterCollection spc, ref string searchSQL) { GetSearchClause(dataitem, dbType, spc, ref searchSQL, true, true); }