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