/// <summary>
/// 解析Update命令定义集合
/// </summary>
/// <param name="upcmd">更新命令结构</param>
/// <returns>返回解析后的sql集合</returns>
public static List<string> __ParseUpdateDataTable(UpdateCommandCondition upcmd, ConnectionModes cnnmode)
{
var sqlText = new List<string>();
var upFields = new UpdateFieldConditionCollections();
switch (upcmd.UpdateCommandType)
{
case UpdateCommandType.Insert:
//组合insert sql
var insertData = upcmd.DataSource.GetChanges(DataRowState.Added);
if (insertData != null)
foreach (DataRow dr in insertData.Rows)
{
upFields.Clear();
foreach (var tbCol in tabColumns[upcmd.DataSource.TableName.ToUpper()])
upFields.Add(tbCol.ColumnName, dr[tbCol.ColumnName]);
sqlText.Add(__ParseInsertSQL(upcmd.DataSource.TableName.ToUpper(), upFields, cnnmode));
}
break;
case UpdateCommandType.Update:
//组合update sql
var updateData = upcmd.DataSource.GetChanges(DataRowState.Modified);
if (updateData != null)
foreach (DataRow dr in updateData.Rows)
{
upFields.Clear();
foreach (var tbCol in tabColumns[upcmd.DataSource.TableName.ToUpper()])
upFields.Add(tbCol.ColumnName, dr[tbCol.ColumnName]);
//获取主键
sqlText.Add(
__ParseUpdateSQL(upcmd.DataSource.TableName.ToUpper(), upFields,
GetKeyColumnToQueryGroup(upcmd.DataSource, dr), cnnmode));
}
break;
case UpdateCommandType.Delete:
//组合delete sql
var deleteData = upcmd.DataSource.GetChanges(DataRowState.Deleted);
if (deleteData != null)
{
deleteData.RejectChanges();
sqlText.AddRange(from DataRow dr in deleteData.Rows
select
__ParseDeleteSQL(upcmd.DataSource.TableName,
GetKeyColumnToQueryGroup(upcmd.DataSource, dr), cnnmode));
}
break;
default:
sqlText.Clear();
var allData = upcmd.DataSource.GetChanges();
if (allData != null)
foreach (DataRow dr in allData.Rows)
{
switch (dr.RowState)
{
case DataRowState.Added:
upFields.Clear();
foreach (var tbCol in tabColumns[upcmd.DataSource.TableName.ToUpper()])
upFields.Add(tbCol.ColumnName, dr[tbCol.ColumnName]);
sqlText.Add(__ParseInsertSQL(upcmd.DataSource.TableName.ToUpper(), upFields, cnnmode));
break;
case DataRowState.Deleted:
sqlText.Add(__ParseDeleteSQL(upcmd.DataSource.TableName,
GetKeyColumnToQueryGroup(upcmd.DataSource, dr), cnnmode));
break;
case DataRowState.Modified:
upFields.Clear();
foreach (var tbCol in tabColumns[upcmd.DataSource.TableName.ToUpper()])
upFields.Add(tbCol.ColumnName, dr[tbCol.ColumnName]);
//获取主键
sqlText.Add(
__ParseUpdateSQL(upcmd.DataSource.TableName.ToUpper(), upFields,
GetKeyColumnToQueryGroup(upcmd.DataSource, dr), cnnmode));
break;
}
}
break;
}
return sqlText;
}
/// <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>
/// 解析insert
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="updateFields">更新字段结构集合</param>
/// <returns>返回解析后的sql</returns>
public static string __ParseInsertSQL(string tableName, UpdateFieldConditionCollections updateFields, ConnectionModes cnnmode)
{
var sqlBuilder = new StringBuilder();
if (string.IsNullOrEmpty(tableName))
throw new Exception("无效的表名称！");
sqlBuilder.AppendFormat("Insert Into {0} ", tableName);
if (updateFields == null || updateFields.Count < 1)
throw new Exception("无效的更新字段参数!");
sqlBuilder.Append("(");
for (var n = 0; n < updateFields.Count; n++)
{
var upField = updateFields[n];
sqlBuilder.AppendFormat(" {0} ", upField.FieldName);
if (n < updateFields.Count - 1)
sqlBuilder.Append(" , ");
}
sqlBuilder.Append(")values(");
for (var n = 0; n < updateFields.Count; n++)
{
var upField = updateFields[n];
sqlBuilder.AppendFormat(" {0} ", FormatSQLValue(upField.Value, cnnmode));
if (n < updateFields.Count - 1)
sqlBuilder.Append(" , ");
}
sqlBuilder.Append(")");
Console.WriteLine("create sql:" + sqlBuilder);
return sqlBuilder.ToString();
}
///<summary>
/// 更新全部数据
///</summary>
///<param name = "tableName">表名称</param>
///<param name = "updateFields">更新字段</param>
public void UpdateTableAllRows(string tableName, UpdateFieldConditionCollections updateFields)
{
ExecuteSql(AddSQLBuffer(__ParseUpdateSQL(tableName, updateFields, null, this._ConnectionMode)));
}
///<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>
/// 更新数据
///</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 = "updateFields">插入字段</param>
///<exception cref = "Exception">InsertIntoTable 方法插入数据必须参数更新字段</exception>
public void InsertIntoTable(string tableName, UpdateFieldConditionCollections updateFields)
{
if (updateFields == null || updateFields.Count < 1)
throw new Exception("InsertIntoTable 方法插入数据必须参数更新字段！");
ExecuteSql(AddSQLBuffer(__ParseInsertSQL(tableName, updateFields, this._ConnectionMode)));
}