/// <summary>
/// 更新表格
/// </summary>
/// <param name="query">sql语句</param>
/// <param name="dt">需要更新的表</param>
/// <param name="setAllValues">指定 update 语句中是包含所有列值还是仅包含更改的列值。</param>
/// <param name="conflictOption">指定将如何检测和解决对数据源的相互冲突的更改。</param>
/// <returns>对表进行更新操作</returns>
public bool UpdateTable(string query, DataTable dt, bool setAllValues, ConflictOption conflictOption)
{
DbDataAdapter adapter = null;
DbCommand command = null;
try
{
command = CreateCommand(query, CommandType.Text);
adapter = DbProvider.CreateDataAdapter();
adapter.SelectCommand = command;
DbCommandBuilder builder = DbProvider.CreateCommandBuilder();
builder.DataAdapter = adapter;
builder.SetAllValues = setAllValues;
builder.ConflictOption = conflictOption;
adapter.Update(dt);
dt.AcceptChanges();
return(true);
}
catch (Exception ex)
{
ErroMsg = ex.Message;
return(false);
}
finally
{
this.parameters.Clear();
DisposeCommand(command);
}
}
/// <summary>
/// Updates the specified criteria.
/// </summary>
/// <param name="criteria">The criteria.</param>
/// <param name="modifiedTable">The modified table.</param>
/// <param name="conflictDetection">The conflict detection.</param>
/// <returns></returns>
public int Update(Criteria criteria, DataTable modifiedTable, ConflictOption conflictDetection)
{
if (criteria == null)
{
throw new ArgumentNullException("criteria");
}
using (var adapter = new QueryCommandFactory(criteria).GetUpdatableQueryDataAdapter(conflictDetection))
{
var connection = adapter.SelectCommand.Connection;
try
{
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
return(adapter.Update(modifiedTable));
}
finally
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
connection.Dispose();
}
}
}
/// <summary>
/// 更新表格数据到数据库
/// </summary>
/// <param name="dt">数据</param>
/// <param name="SelectSQL">查询语句</param>
/// <param name="SetAllValues">更新所有值，True时更新所有字段，False时只更新有改变的字段</param>
/// <param name="ConflictOption">指定将如何检测和解决对数据源的相互冲突的更改。</param>
/// <returns></returns>
public bool Update(DataTable dt, string SelectSQL, bool SetAllValues, ConflictOption ConflictOption)
{
using (var connection = CreateConnection())
{
var db = GetCommandDataBase(connection);
return(db.Update(dt, SelectSQL, SetAllValues, ConflictOption));
}
}
internal static ArgumentOutOfRangeException InvalidConflictOptions(ConflictOption value)
{
#if DEBUG
switch (value)
{
case ConflictOption.CompareAllSearchableValues:
case ConflictOption.CompareRowVersion:
case ConflictOption.OverwriteChanges:
Debug.Assert(false, "valid ConflictOption " + value.ToString());
break;
}
#endif
return(InvalidEnumerationValue(typeof(ConflictOption), (int)value));
}
/// <summary>
/// 更新表格数据到数据库
/// </summary>
/// <param name="dt">数据</param>
/// <param name="SelectSQL">查询语句</param>
/// <param name="SetAllValues">更新所有值，True时更新所有字段，False时只更新有改变的字段</param>
/// <param name="ConflictOption">指定将如何检测和解决对数据源的相互冲突的更改。</param>
/// <param name="DbDataAdapterHelper"></param>
/// <returns></returns>
public bool Update(DataTable dt, string SelectSQL, bool SetAllValues, ConflictOption ConflictOption, Action <DbDataAdapter> DbDataAdapterHelper)
{
using (var Command = PrepareCommand(SelectSQL, null, CommandType.Text))
{
try
{
using (var DataAdapter = CreateDataAdapter())
{
DataAdapter.SelectCommand = Command;
using (var cmdBuilder = _ProviderFactory.CreateCommandBuilder())
{
cmdBuilder.DataAdapter = DataAdapter;
DbDataAdapterHelper?.Invoke(DataAdapter);
cmdBuilder.SetAllValues = SetAllValues;
cmdBuilder.ConflictOption = ConflictOption;
cmdBuilder.GetDeleteCommand(true);
cmdBuilder.GetInsertCommand(true);
cmdBuilder.GetUpdateCommand(true);
//var deleteDataAdapter = cmdBuilder.GetDeleteCommand(true);
//var insertDataAdapter = cmdBuilder.GetInsertCommand(true);
//var updateDataAdapter = cmdBuilder.GetUpdateCommand(true);
//if (cmdBuilder.DataAdapter.DeleteCommand == null)
// cmdBuilder.DataAdapter.DeleteCommand = deleteDataAdapter;
//if (cmdBuilder.DataAdapter.InsertCommand == null)
// cmdBuilder.DataAdapter.InsertCommand = insertDataAdapter;
//if (cmdBuilder.DataAdapter.UpdateCommand == null)
// cmdBuilder.DataAdapter.UpdateCommand = updateDataAdapter;
int count = cmdBuilder.DataAdapter.Update(dt.Select("", "", DataViewRowState.Added)) //增加
+ cmdBuilder.DataAdapter.Update(dt.Select("", "", DataViewRowState.Deleted)) //删除
+ cmdBuilder.DataAdapter.Update(dt.Select("", "", DataViewRowState.ModifiedCurrent)); //修改
if (count > 0)
{
dt.AcceptChanges();
}
return(true);
}
}
}
catch (Exception e)
{
throw e;
}
}
}
/// <summary>
/// Gets the updatable query data adapter.
/// </summary>
/// <param name="option">The option.</param>
/// <returns></returns>
public DbDataAdapter GetUpdatableQueryDataAdapter(ConflictOption option)
{
var adapter = _queryCommandBuilder.GetDbProviderFactory().CreateDataAdapter();
adapter.SelectCommand = GetQueryCommand();
var cmdBuilder = _queryCommandBuilder.GetDbProviderFactory().CreateCommandBuilder();
cmdBuilder.DataAdapter = adapter;
cmdBuilder.ConflictOption = option;
cmdBuilder.GetInsertCommand();
cmdBuilder.GetUpdateCommand();
cmdBuilder.GetDeleteCommand();
return(adapter);
}
public override void AppendTo(StringBuilder sb, ISqlDialect dialect)
{
sb.AppendLine("UPDATE");
if (ConflictOption != OnConflictOption.Default)
{
sb.AppendLine("OR " + ConflictOption.ToString());
}
sb.AppendLine(TableName);
if (Values != null)
{
bool first = true;
foreach (var pair in Values)
{
if (!first)
{
sb.AppendLine(",");
}
sb.Append("SET ").Append(pair.Key).Append(" = ").Append(pair.Value.ToString());
}
}
else
{
for (int i = 0; i < ColumnNames.Count; i++)
{
if (i == 0)
{
sb.AppendLine("SET");
}
else
{
sb.Append(", ");
}
sb.AppendLine(ColumnNames[i] + " = " + ValueExpressions[i]);
}
}
if (Where != null)
{
Where.AppendTo(sb);
sb.AppendLine();
}
}
public ConflictOptionViewModel(ConflictOption conflictOption)
{
this.ConflictOption = conflictOption;
}
internal static ArgumentOutOfRangeException InvalidConflictOptions(ConflictOption value)
{
return InvalidEnumerationValue(typeof(ConflictOption), (int) value);
}
/// <summary>
/// 更新表格数据到数据库
/// </summary>
/// <param name="dt">数据</param>
/// <param name="SelectSQL">查询语句</param>
/// <param name="SetAllValues">更新所有值，True时更新所有字段，False时只更新有改变的字段</param>
/// <param name="ConflictOption">指定将如何检测和解决对数据源的相互冲突的更改。</param>
/// <returns></returns>
public bool Update(DataTable dt, string SelectSQL, bool SetAllValues, ConflictOption ConflictOption)
{
return(Update(dt, SelectSQL, SetAllValues, ConflictOption, CustomerDbDataAdapter));
}
/// <summary>
/// 事务更新
/// </summary>
/// <param name="dt">更新表</param>
/// <param name="query">查询语句</param>
/// <param name="setAllValues"></param>
/// <param name="conflictOption"></param>
/// <returns></returns>
public bool TransactionUpdateDataTable(ref DataTable dt, string query, bool setAllValues = false, ConflictOption conflictOption = ConflictOption.OverwriteChanges)
{
bool result;
try
{
SetTransCommand(query);
DbDataAdapter adapter = DbProvider.CreateDataAdapter();
adapter.SelectCommand = this.transCommand;
DbCommandBuilder builder = DbProvider.CreateCommandBuilder();
builder.DataAdapter = adapter;
builder.SetAllValues = setAllValues;
builder.ConflictOption = conflictOption;
adapter.Update(dt);
result = true;
}
catch (Exception ex)
{
result = false;
this.ErroMsg = ex.Message;
this.TransctionRollBack();
}
return(result);
}
public static DbCommandBuilder CreateCommandBuilder([NotNull] this DbProviderFactory thisValue, IDbDataAdapter adapter = null, ConflictOption conflictOption = ConflictOption.OverwriteChanges, bool setAllValues = true)
{
DbCommandBuilder builder = thisValue.CreateCommandBuilder();
if (builder == null)
{
return(null);
}
builder.DataAdapter = (DbDataAdapter)adapter;
builder.RefreshSchema();
builder.ConflictOption = conflictOption;
builder.SetAllValues = setAllValues;
return(builder);
}
public static DbCommandBuilder CreateCommandBuilder([NotNull] this DbProviderFactory thisValue, [NotNull] IDbCommand selectCommand, MissingSchemaAction missingSchemaAction = MissingSchemaAction.AddWithKey, MissingMappingAction missingMappingAction = MissingMappingAction.Passthrough, ConflictOption conflictOption = ConflictOption.OverwriteChanges, bool setAllValues = true)
{
IDbDataAdapter adapter = CreateSelectDataAdapter(thisValue, selectCommand, missingSchemaAction, missingMappingAction);
return(adapter == null ? null : CreateCommandBuilder(thisValue, adapter, conflictOption, setAllValues));
}
public override void AppendTo(StringBuilder sb, ISqlDialect dialect)
{
sb.Append("INSERT");
if (ConflictOption != OnConflictOption.Default)
{
sb.Append(" OR " + ConflictOption.ToString());
}
sb.Append(" INTO " + TableName).Append(" ");
if (Values != null)
{
sb.AppendLine("(");
var count = 0;
foreach (var pair in Values)
{
if (count++ > 0)
{
sb.Append(",");
}
if (count > 0 && count % 4 == 0)
{
sb.AppendLine();
}
else
{
sb.Append(" ");
}
sb.Append(pair.Key);
}
sb.Append(") VALUES (");
count = 0;
foreach (var pair in Values)
{
if (count++ > 0)
{
sb.Append(",");
}
if (count > 0 && count % 4 == 0)
{
sb.AppendLine();
}
else
{
sb.Append(" ");
}
sb.Append(pair.Value);
}
sb.Append(")");
}
else
{
if (ColumnNames != null)
{
sb.AppendLine("(");
bool first = true;
foreach (string colName in ColumnNames)
{
if (first)
{
sb.Append(" " + colName);
first = false;
}
else
{
sb.Append(", " + colName);
}
}
sb.Append(")");
}
if (ValueExpressions != null)
{
sb.AppendLine("VALUES");
sb.Append("(");
var first = true;
foreach (string valExpr in ValueExpressions)
{
if (first)
{
sb.Append(valExpr);
first = false;
}
else
{
sb.Append("," + valExpr);
}
}
sb.Append(")");
}
else if (SelectExpression != null)
{
sb.AppendLine();
SelectExpression.AppendTo(sb, dialect);
}
else
{
sb.AppendLine().Append("DEFAULT VALUES");
}
}
}
