///<summary> /// 删除数据 ///</summary> ///<param name = "tableName">表名称</param> ///<param name = "queryGroup">查询组集合</param> ///<exception cref = "Exception">DeleteFromTable 方法删除数据必须传入查询组参数!</exception> public void DeleteFromTable(string tableName, QueryGroupConditionCollections queryGroup) { if (queryGroup == null || queryGroup.Count < 1) throw new Exception("DeleteFromTable 方法删除数据必须传入查询组参数!"); ExecuteSql(AddSQLBuffer(__ParseDeleteSQL(tableName, queryGroup, this._ConnectionMode))); }
///<summary> /// 删除数据 ///</summary> ///<param name = "tableName">表名称</param> ///<param name = "query">查询组</param> public void DeleteFromTable(string tableName, QueryGroupCondition query) { var queryGroup = new QueryGroupConditionCollections { query }; //return DeleteFromTable(tableName, queryGroup); }
/// <summary> /// 解析Sql /// </summary> /// <param name = "tableName">物理表名称</param> /// <param name = "queryGroup">查询组</param> /// <param name = "orderBy">排序组</param> /// <returns>返回解析后的sql</returns> public static string __ParseSelectSQL(string tableName, QueryGroupConditionCollections queryGroup, OrderCollections orderBy, ConnectionModes cnnmode) { var sqlBuilder = new StringBuilder(); if (string.IsNullOrEmpty(tableName)) throw new Exception("无效的表名称!"); sqlBuilder.AppendFormat("select * from {0} ", tableName); if (queryGroup != null && queryGroup.Count > 0) { var queryCount = queryGroup.Sum(qry => qry.FieldConditions.Count); if (queryCount > 0) { sqlBuilder.AppendFormat(" where "); for (var i = 0; i < queryGroup.Count; i++) { var qry = queryGroup[i]; sqlBuilder.AppendFormat(" ("); for (var j = 0; j < qry.FieldConditions.Count; j++) { var field = qry.FieldConditions[j]; //支持sql 拼接 if (field.Operator == OperatorType.SqlText) sqlBuilder.AppendFormat(" {0} ", field.Value); else sqlBuilder.AppendFormat(" {0} {1} {2} ", field.FieldName, GetOperatorType(field.Operator), FormatSQLValue(field.Value, cnnmode)); if (j < qry.FieldConditions.Count - 1) sqlBuilder.AppendFormat(" {0} ", GetWhereUnionType(field.NextUnionType)); } sqlBuilder.AppendFormat(") "); if (i < (queryGroup.Count - 1)) sqlBuilder.AppendFormat(" {0} ", GetWhereUnionType(qry.NextUnionType)); } } } if (orderBy != null && orderBy.Count > 0) { sqlBuilder.Append(" order by "); for (var n = 0; n < orderBy.FieldNames.Length; n++) { var fieldName = orderBy.FieldNames[n]; sqlBuilder.AppendFormat(" {0} {1} ", fieldName, GetOrderByType(orderBy[fieldName])); if (n < (orderBy.FieldNames.Length - 1)) sqlBuilder.Append(","); } } Console.WriteLine("create sql:" + sqlBuilder); return sqlBuilder.ToString(); }
/// <summary> /// 解析update /// </summary> /// <param name = "tableName">物理表名</param> /// <param name = "updateFields">更新字段</param> /// <param name = "queryGroup">查询集合</param> /// <returns>解析后的sql</returns> public static string __ParseUpdateSQL(string tableName, UpdateFieldConditionCollections updateFields, QueryGroupConditionCollections queryGroup, ConnectionModes cnnmode) { var sqlBuilder = new StringBuilder(); if (string.IsNullOrEmpty(tableName)) throw new Exception("无效的表名称!"); sqlBuilder.AppendFormat("Update {0} ", tableName); if (updateFields == null || updateFields.Count < 1) throw new Exception("无效的更新字段参数!"); sqlBuilder.Append(" set "); for (var n = 0; n < updateFields.Count; n++) { var upField = updateFields[n]; sqlBuilder.AppendFormat(" {0}{1}{2} ", upField.FieldName, GetOperatorType(upField.Operator), FormatSQLValue(upField.Value, cnnmode)); if (n < updateFields.Count - 1) sqlBuilder.AppendFormat(" , "); } if (queryGroup != null && queryGroup.Count > 0) { sqlBuilder.AppendFormat(" where "); for (var i = 0; i < queryGroup.Count; i++) { var qry = queryGroup[i]; sqlBuilder.AppendFormat(" ("); for (var j = 0; j < qry.FieldConditions.Count; j++) { var field = qry.FieldConditions[j]; sqlBuilder.AppendFormat("( {0} {1} {2} )", field.FieldName, GetOperatorType(field.Operator), FormatSQLValue(field.Value, cnnmode)); if (j < qry.FieldConditions.Count - 1) sqlBuilder.AppendFormat(" {0} ", GetWhereUnionType(field.NextUnionType)); } sqlBuilder.AppendFormat(") "); if (i < (queryGroup.Count - 1)) sqlBuilder.AppendFormat(" {0} ", GetWhereUnionType(qry.NextUnionType)); } } Console.WriteLine("create sql:" + sqlBuilder); return sqlBuilder.ToString(); }
///<summary> /// 更新数据 ///</summary> ///<param name = "tableName">表名称</param> ///<param name = "updateFields">更新字段</param> ///<param name = "query">查询组</param> public void UpdateTable(string tableName, UpdateFieldConditionCollections updateFields, QueryGroupCondition query) { var queryGroup = new QueryGroupConditionCollections { query }; //return UpdateTable(tableName, updateFields, queryGroup); }
/// <summary> /// 从dataTable 内获取行记录转化为QueryGroup /// </summary> /// <param name = "dataTable">DataTable</param> /// <param name = "dr">行记录</param> /// <returns><see cref = "QueryGroupConditionCollections" /></returns> protected static QueryGroupConditionCollections GetKeyColumnToQueryGroup(DataTable dataTable, DataRow dr) { var queryGroup = new QueryGroupConditionCollections(); var fields = new FieldConditionCollections(); foreach (var dataCol in dataTable.PrimaryKey) { if (dr.RowState == DataRowState.Deleted) { fields.Add(dataCol.ColumnName, OperatorType.Equal, dr[dataCol.ColumnName, DataRowVersion.Original]); } else { if (dr[dataCol.ColumnName].GetType() == typeof(string)) { if (!string.IsNullOrEmpty(dr[dataCol.ColumnName, DataRowVersion.Original].ToString())) fields.Add(dataCol.ColumnName, OperatorType.Equal, dr[dataCol.ColumnName, DataRowVersion.Original]); else if (!string.IsNullOrEmpty(dr[dataCol.ColumnName, DataRowVersion.Current].ToString())) fields.Add(dataCol.ColumnName, OperatorType.Equal, dr[dataCol.ColumnName, DataRowVersion.Current]); else if (!string.IsNullOrEmpty(dr[dataCol.ColumnName, DataRowVersion.Default].ToString())) fields.Add(dataCol.ColumnName, OperatorType.Equal, dr[dataCol.ColumnName, DataRowVersion.Default]); } else { if (dr[dataCol.ColumnName, DataRowVersion.Original] != null) fields.Add(dataCol.ColumnName, OperatorType.Equal, dr[dataCol.ColumnName, DataRowVersion.Original]); else if (dr[dataCol.ColumnName, DataRowVersion.Current] != null) fields.Add(dataCol.ColumnName, OperatorType.Equal, dr[dataCol.ColumnName, DataRowVersion.Current]); else if (dr[dataCol.ColumnName, DataRowVersion.Default] != null) fields.Add(dataCol.ColumnName, OperatorType.Equal, dr[dataCol.ColumnName, DataRowVersion.Default]); } } } queryGroup.Add(fields); return queryGroup; }
///<summary> /// 更新数据 ///</summary> ///<param name = "tableName">表名称</param> ///<param name = "updateFields">更新字段</param> ///<param name = "queryGroup">查询组</param> ///<exception cref = "Exception">UpdateTable 方法删除数据必须传入查询组参数!</exception> public void UpdateTable(string tableName, UpdateFieldConditionCollections updateFields, QueryGroupConditionCollections queryGroup) { if (queryGroup == null || queryGroup.Count < 1) throw new Exception("UpdateTable 方法更新数据必须传入查询组参数!"); if (updateFields == null || updateFields.Count < 1) throw new Exception("UpdateTable 方法更新数据必须参数更新字段!"); ExecuteSql(AddSQLBuffer(__ParseUpdateSQL(tableName, updateFields, queryGroup, this._ConnectionMode))); }
///<summary> /// 获取表数据 ///</summary> ///<param name = "tableName">表名称</param> ///<param name = "queryGroup">查询组</param> ///<returns>DataTable</returns> public DataTable SelectFromTable(string tableName, QueryGroupConditionCollections queryGroup) { return SelectFromTable(tableName, queryGroup, null, null); }
///<summary> /// 获取表数据 ///</summary> ///<param name = "tableName">表名称</param> ///<param name = "queryGroup">查询组</param> ///<param name = "orderBy">排序</param> ///<returns>DataTable</returns> public DataTable SelectFromTable(string tableName, QueryGroupConditionCollections queryGroup, OrderCollections orderBy) { return SelectFromTable(tableName, queryGroup, orderBy, null); }
/// <summary> /// 获取表数据 /// </summary> /// <param name="tableName">表名称</param> /// <param name="query">单组查询</param> /// <param name="orderBy">排序</param> /// <param name="pagination">分页结构</param> /// <returns> /// DataTable /// </returns> public DataTable SelectFromTable(string tableName, QueryGroupCondition query, OrderCollections orderBy, Pagination pagination) { var queryGroup = new QueryGroupConditionCollections { query }; return SelectFromTable(tableName, queryGroup, orderBy, pagination); }
/// <summary> /// 获取表数据 /// </summary> /// <param name="tableName">表名称</param> /// <param name="queryGroup">查询组</param> /// <param name="orderBy">排序</param> /// <param name="pagination">分页结构</param> /// <returns> /// DataTable /// </returns> /// <exception cref="Exception">所有错误信息</exception> public DataTable SelectFromTable(string tableName, QueryGroupConditionCollections queryGroup, OrderCollections orderBy, Pagination pagination) { var sql = __ParseSelectSQL(tableName, queryGroup, orderBy, this._ConnectionMode); return SelectFromTable(tableName, sql, pagination); }