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