//public bool UpdateDbData(string command, DataSet ds)
//{
// if (UpdateDbData(ds.Tables[0], "SystemOption") >= 0)
// return true;
// else
// return false;
//}
public bool UpdateDbData(string commandText, DataTable table)
{
int affect = -1;
try
{
OleDbConnection connection = new OleDbConnection(Url);
using (connection)
{
// Create the select command.
OleDbCommand command = new OleDbCommand(commandText, connection);
// Create the DbDataAdapter.
OleDbDataAdapter adapter = new OleDbDataAdapter(command);
// Create the DbCommandBuilder.
OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter);
builder.RefreshSchema();
// Get the insert, update and delete commands.
adapter.InsertCommand = builder.GetInsertCommand();
adapter.UpdateCommand = builder.GetUpdateCommand();
adapter.DeleteCommand = builder.GetDeleteCommand();
var dd = table.GetChanges();
affect = adapter.Update(table);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
return(affect > 0 ? true : false);
}
/// <summary>
/// 更新数据库
/// </summary>
public static void UpdateSourceTable(OleDbConnection conn, DataTable tb)
{
OleDbDataAdapter data = new OleDbDataAdapter("Select * From [" + tb.TableName + "]", conn);
OleDbCommandBuilder cb = new OleDbCommandBuilder(data);
cb.RefreshSchema();
data.Update(tb);
}
private static OleDbCommandBuilder PrepareCommandBuilder(OleDbDataAdapter dataAdapter)
{
OleDbCommandBuilder commandBuilder = new OleDbCommandBuilder(dataAdapter);
commandBuilder.QuotePrefix = "[";
commandBuilder.QuoteSuffix = "]";
commandBuilder.RefreshSchema();
return(commandBuilder);
}
/// <summary>
/// 批量操作表
/// </summary>
/// <param name="cmdType"></param>
/// <param name="dt"></param>
/// <param name="strTblName">数据库表名</param>
/// <param name="where">除主键外的条件</param>
/// <returns></returns>
private int UpdateDbData(DataTable dt, string strTblName, string where = null)
{
int affect = -1;
StringBuilder cmdFields = new StringBuilder();
foreach (DataColumn column in dt.Columns)
{
cmdFields.Append(column.ColumnName + ",");
}
if (cmdFields.Length > 0)
{
cmdFields = cmdFields.Remove(cmdFields.Length - 1, 1);
}
using (OleDbConnection oldCon = new OleDbConnection(Url))
{
OleDbCommand myCommand = new OleDbCommand(string.Format("select {0} from {1}", cmdFields, strTblName), oldCon);
try
{
OleDbDataAdapter myAdapter = new OleDbDataAdapter();
myAdapter.SelectCommand = myCommand;
OleDbCommandBuilder myCommandBuilder = new OleDbCommandBuilder
{
DataAdapter = myAdapter,
SetAllValues = true,
ConflictOption = ConflictOption.OverwriteChanges,
};
myCommandBuilder.RefreshSchema();
myAdapter.InsertCommand = myCommandBuilder.GetInsertCommand(true);
myAdapter.InsertCommand.Connection = oldCon;
myAdapter.UpdateCommand = myCommandBuilder.GetUpdateCommand(true);
myAdapter.DeleteCommand = myCommandBuilder.GetDeleteCommand(true);
if (!string.IsNullOrEmpty(where))
{
myAdapter.UpdateCommand.CommandText = myAdapter.UpdateCommand.CommandText.Replace("WHERE", "WHERE " + where + " and ");
}
var dd = dt.GetChanges();
affect = myAdapter.Update(dt);
}
catch (Exception ex)
{
throw ex;
}
finally
{
}
}
return(affect);
}
/// <summary>
/// 更新表中的数据至数据库，仅能更新由该实例获取的DataTable对象
/// </summary>
/// <param name="dataTable">要执行更新的表</param>
public void Update(DataTable dataTable)
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
int key = dataTable.GetHashCode();
if (!sqlList.ContainsKey(key))
{
throw new Exception("该DataTable不是由本" + ToString() + "创建，无法更新！");
}
string sql = sqlList[key];
command.CommandText = sql;
adapter.SelectCommand = command;
commandBuilder.RefreshSchema();
adapter.Update(dataTable);
if (odt == null && isdisposeConn)
{
connection.Close();
}
}
/// <summary>
/// 更新表，带事务
/// </summary>
/// <param name="dt">表</param>
/// <param name="con">数据库连接</param>
public void Update(DataTable dt, IDbConnection con, IDbTransaction ts)
{
OleDbDataAdapter sda = new OleDbDataAdapter();
OleDbCommandBuilder scb = new OleDbCommandBuilder(sda);
try
{
sda.SelectCommand = new OleDbCommand("SELECT * FROM " + dt.TableName, con as OleDbConnection);
sda.SelectCommand.Transaction = ts as OleDbTransaction;
scb.RefreshSchema();
sda.Update(dt);
}
catch (OleDbException ex)
{
throw (ex);
}
}
