/// <summary>
/// For Update Hotel Information
/// </summary>
/// <param name="hotelDS"></param>
/// <returns>Status of the edit operation on a hotel</returns>
public bool EditHotel(HappyTrip.Model.Entities.Hotel.Hotel hotel)
{
bool isUpdated = true;
try
{
DataSet hotelDS = this.GetHotelById(hotel.HotelId);
DataRow row = hotelDS.Tables[0].NewRow();
row["HotelId"] = hotel.HotelId;
row["Address"] = hotel.Address;
row["BriefNote"] = hotel.BriefNote;
row["CityId"] = hotel.CityID;
row["PhotoURL"] = hotel.PhotoUrl;
row["ContactNo"] = hotel.ContactNo;
row["EMail"] = hotel.Email;
row["Pincode"] = hotel.Pincode;
row["StarRanking"] = hotel.StarRanking;
row["WebsiteURL"] = hotel.WebsiteURL;
hotelDS.Tables[0].Rows.Add(row);
SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(dataAdapter as SqlDataAdapter);
dataAdapter.Update(hotelDS);
}
catch (SqlException ex)
{
throw new HotelDAOException("Unable to edit hotel info", ex);
}
catch (Exception e)
{
throw new HotelDAOException("Unable to edit hotel info", e);
}
return(isUpdated);
}
public static bool Insert(IDbDataAdapter adapter, DataSet ds, BindingSource bds, string tabela, string campoId, bool tabelaID_DateTime)
{
try
{
bds.Position = bds.Count - 1;
if (tabelaID_DateTime)
{
if (((DataRowView)bds.Current).Row[campoId] == DBNull.Value)
{
((DataRowView)bds.Current).Row[campoId] = DateTime.Now;
}
}
else
{
((DataRowView)bds.Current).Row[campoId] = GetID(tabela, campoId);
}
bds.EndEdit();
adapter.Update(ds);
ds.Clear();
adapter.Fill(ds);
bds.DataSource = ds;
bds.DataMember = ds.Tables[0].TableName;
bds.Position = bds.Count - 1;
return(true);
}
catch (Exception ex)
{
Mensagem.Excecao(null, "Erro ao inserir os dados.", ex);
return(false);
}
}
/// <summary>
/// Creates the necessary rows for a message in the MSGS table
/// </summary>
/// <param name="msgTag">The message name</param>
/// <param name="srcUnitId">The source identifier</param>
/// <param name="dstUnitId">The destination identifier</param>
/// <param name="configDs">The message configuration</param>
/// <param name="msgBody">The text of the message</param>
public void CreateFromConfig(string msgTag, decimal srcUnitId, decimal dstUnitId,
DataSet configDs, string msgBody)
{
if (configDs != null && configDs.Tables.Count > 0 &&
configDs.Tables[0].Rows != null &&
configDs.Tables[0].Rows.Count > 0)
{
DataSet msgsDs = null;
IDbDataAdapter adapter = null;
try
{
/// Create messages with configuration values
adapter = GetAdapter();
msgsDs = new DataSet();
adapter.FillSchema(msgsDs, SchemaType.Mapped);
int count = configDs.Tables[0].Rows.Count;
CmpMsgsDB msgDb = new CmpMsgsDB();
decimal id = msgDb.LastPKValue + 1;
Util.CreateMessageRows(msgBody, configDs, msgsDs, ref id);
adapter.Update(msgsDs);
}
catch (Exception ex)
{
Debug.WriteLine("Messages.CreateMessages - " + ex.Message);
throw;
}
}
}
public static bool Delete(IDbDataAdapter adapter, DataSet ds, BindingSource bds)
{
try
{
if (Mensagem.Pergunta(null, "Deseja realmente deletar o registro corrente?", DialogResult.No))
{
return(false);
}
bds.RemoveCurrent();
bds.EndEdit();
adapter.Update(ds);
ds.Clear();
adapter.Fill(ds);
bds.DataSource = ds;
bds.DataMember = ds.Tables[0].TableName;
Commit();
return(true);
}
catch (Exception ex)
{
Mensagem.Excecao(null, "Erro ao deletar o registro corrente.", ex);
RefreshData(adapter, ds, bds);
return(false);
}
}
/// <summary>
/// Saves Room types into database
/// </summary>
/// <param name="roomTypesDS"></param>
/// <returns>Status of the update operation</returns>
public bool SaveRoomTypes(DataSet roomTypesDS)
{
bool isUpdated = true;
try
{
conn = this.GetConnection();
cmd = conn.CreateCommand();
cmd.CommandText = "SELECT * FROM ROOMTYPES";
cmd.CommandType = CommandType.Text;
dataAdapter = new SqlDataAdapter(cmd as SqlCommand);
SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(dataAdapter as SqlDataAdapter);
dataAdapter.Update(roomTypesDS);
}
catch (SqlException ex)
{
throw new HotelDAOException("Unable to save room types", ex);
}
catch (Exception e)
{
throw new HotelDAOException("Unable to save room types", e);
}
return(isUpdated);
}
/// <summary>
/// 更新数据表的数据，为每个已经新增，修改，删除的行执行相应的INSERT,UPDATE,DELETE语句
/// </summary>
/// <param name="selectSql">数据表查询Sql</param>
/// <param name="table">数据表</param>
/// <param name="parms">查询Sql参数</param>
/// <returns></returns>
public int Update(string selectSql, DataTable table, IDbDataParameter[] parms)
{
try
{
if (DBConnector.Connection.State == ConnectionState.Closed)
{
DBConnector.Connection.Open();
}
IDbCommand cmd = DBConnector.GetCommand(selectSql, parms);
IDbDataAdapter adapter = connector.GetAdapter(cmd);
table.TableName = "Table";
DataSet ds = new DataSet();
ds.Tables.Add(table);
return(adapter.Update(ds));
}
catch (DBConcurrencyException e)
{
throw e;
}
finally
{
if (!isTranscation)
{
DBConnector.Connection.Close();
}
}
}
/// <see cref="NI.Data.IDalc.Update(System.Data.DataTable)"/>
public virtual void Update(DataTable t)
{
var tableName = t.TableName;
IDbDataAdapter adapter = DbFactory.CreateDataAdapter(OnRowUpdating, OnRowUpdated);
CommandGenerator.ComposeAdapterUpdateCommands(adapter, t);
adapter.InsertCommand.Connection = Connection;
adapter.UpdateCommand.Connection = Connection;
adapter.DeleteCommand.Connection = Connection;
try {
if (adapter is DbDataAdapter)
{
((DbDataAdapter)adapter).Update(t.DataSet, tableName);
}
else
{
adapter.Update(t.DataSet);
}
} finally {
DisposeAdapter(adapter);
}
}
public virtual void Update(IDbConnection conn, IDbTransaction transaction)
{
ExcpHelper.ThrowIf(this.Table == null, "TableSpec is not specified for type {0}", this.GetType());
using (DataTable dtUpdate = DataCopyTables.GetEmptyDataTableByName(conn, transaction, this.Table.TableName))
{
using (IDbCommand cmdUpdate = DataCopy.GenerateUpdateCommand(conn, transaction, dtUpdate, this.Table))
{
using (IDisposable dsp = SqlObjectFactory.CreateDbAdapter(null) as IDisposable)
{
IDbDataAdapter daUpdate = dsp as IDbDataAdapter;
Debug.Assert(daUpdate != null);
daUpdate.UpdateCommand = cmdUpdate;
DataRow dr = this.CreateDataRow(dtUpdate);
dtUpdate.Rows.Add(dr);
dtUpdate.AcceptChanges();
dr.SetModified();
using (DataSet ds = new DataSet())
{
ds.Tables.Add(dtUpdate);
daUpdate.Update(ds);
}
}
}
}
}
/// <summary>
/// Updates a dataset using insert, update and delete comands, specified by indices
/// </summary>
/// <param name="dataSet">The data set.</param>
public void Update(DataSet dataSet)
{
TraceStart("Update");
try {
this.Prepare();
IDbDataAdapter adapter = DataAccess.Provider.CreateDataAdapter();
if (dataSet.HasChanges(DataRowState.Added))
{
adapter.InsertCommand = Command;
}
if (dataSet.HasChanges(DataRowState.Modified))
{
adapter.UpdateCommand = Command;
}
if (dataSet.HasChanges(DataRowState.Deleted))
{
adapter.DeleteCommand = Command;
}
adapter.Update(dataSet);
}
catch (Exception ex) {
this.HandleException(ex);
//this.Rollback();
throw; // not handled here
}
finally {
this.CloseConnection(false);
TraceEnd("Update");
}
}
/// <summary>
/// 更新一个包含名为"Table"的数据表的数据集，为每个已经新增，修改，
/// 删除的行执行相应的INSERT,UPDATE,DELETE语句
/// </summary>
/// <param name="selectSql">数据表查询Sql</param>
/// <param name="dataSet">包含名为"Table"的数据表的数据集</param>
/// <param name="parms">查询Sql参数</param>
/// <exception cref="DBConcurrencyException"></exception>
/// <returns>影响的行数</returns>
public int Update(string selectSql, DataSet dataSet, IDbDataParameter[] parms)
{
try
{
if (DBConnector.Connection.State == ConnectionState.Closed)
{
DBConnector.Connection.Open();
}
IDbCommand cmd = DBConnector.GetCommand(selectSql, parms);
if (isTranscation)
{
cmd.Transaction = transaction;
}
IDbDataAdapter adapter = connector.GetAdapter(cmd);
return(adapter.Update(dataSet));
}
catch (DBConcurrencyException e)
{
throw e;
}
finally
{
if (!isTranscation)
{
DBConnector.Connection.Close();
}
}
}
private int UpdateDataset(SqlStatement insertSql, SqlStatement updateSql, SqlStatement deleteSql, DataSet ds, int updateBatchSize, UpdateRowSource updateRowSource, bool throwException)
{
int ret = 0;
DbEntry.UsingConnection(delegate
{
IDbDataAdapter d = InnerDriver.GetDbAdapter();
if (insertSql != null)
{
d.InsertCommand = GetDbCommandForUpdate(insertSql, updateRowSource);
}
if (updateSql != null)
{
d.UpdateCommand = GetDbCommandForUpdate(updateSql, updateRowSource);
}
if (deleteSql != null)
{
d.DeleteCommand = GetDbCommandForUpdate(deleteSql, updateRowSource);
}
var adapter = d as DbDataAdapter;
if (adapter != null)
{
adapter.UpdateBatchSize = updateBatchSize;
}
else if (throwException)
{
throw new DataException("The DbDataAdapter doesn't support UpdateBatchSize feature.");
}
ret = d.Update(ds);
ds.AcceptChanges();
});
return(ret);
}
private void button1_Click(object sender, EventArgs e)
{
bsEmpresa.EndEdit();
internalAdapter.Update(internalData);
internalData.AcceptChanges();
MessageBox.Show("Datos Salvados con exito");
}
private void btnSave_Click(object sender, EventArgs e)
{
Cursor = Cursors.WaitCursor;
try
{
adapter.Update(adapterDataSet);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "Save", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
Cursor = Cursors.Default;
}
public int Update(CommandType commandType, string
commandText, DataSet ds)
{
this.idbCommand = DBManagerFactory.GetCommand(this.ProviderType);
PrepareCommand(idbCommand, this.Connection, this.Transaction,
commandType,
commandText, this.Parameters);
IDbDataAdapter dataAdapter = DBManagerFactory.GetDataAdapter
(this.ProviderType);
dataAdapter.UpdateCommand = idbCommand;
int affectedRows = dataAdapter.Update(ds);
idbCommand.Parameters.Clear();
return(affectedRows);
}
public object ExecuteBatchUpdate(ConfigSettings configuration, DataSet ds)
{
Connection(configuration);
((IDbCommand)configuration.DBCommand).Connection = GenericConnection;
switch (configuration.DataStore)
{
case DataProviderType.Sql: GenericDataAdapter = new SqlDataAdapter((SqlCommand)configuration.DBCommand); break;
case DataProviderType.OleDb: GenericDataAdapter = new OleDbDataAdapter((OleDbCommand)configuration.DBCommand); break;
case DataProviderType.Odbc: GenericDataAdapter = new OdbcDataAdapter((OdbcCommand)configuration.DBCommand); break;
}
GenericDataAdapter.UpdateCommand = (IDbCommand)configuration.DBCommand;
int i = GenericDataAdapter.Update(ds);
GenericConnection.Close();
GenericConnection.Dispose();
return (object)i;
}
public void BulkUpdateByDataSet(CommandType commandType, string commandText, DataTable ds, CommandBuilderType commandBuilderType = CommandBuilderType.NONE)
{
try
{
IDbDataAdapter dataAdapter = CreateCommandAndDataAdapter();
GetTheCommandBuilder(dataAdapter);
dataAdapter.SelectCommand = this.idbCommand;
PrepareCommand(idbCommand, this.Connection, this.Transaction, commandType, commandText, this.Parameters, commandBuilderType);
BuildInsertCommandForCommandBuilder();
dataAdapter.SelectCommand = idbCommand;
DataSet dataSet = new DataSet();
dataSet.Tables.Add(ds.Copy());
dataAdapter.Update(dataSet);
idbCommand.Parameters.Clear();
}
catch (Exception)
{
throw;
}
}
public bool Example()
{
var dataTable = DS.Tables [0];
// Delete
dataTable.Rows [0].Delete();
// Update
dataTable.Rows [1] ["FieldA"] = DateTime.Now.ToString();
// Insert
var newRow = dataTable.NewRow();
newRow ["FieldA"] = DateTime.Now.ToString();
dataTable.Rows.Add(newRow);
// Persist
Adapter.Update(DS);
return(true);
}
/// <summary>
/// Delete all data in a table, and update using data adapter
/// </summary>
/// <param name="dataAdapter">Adapter to use</param>
/// <param name="dataTable"></param>
public void DeleteData(IDbDataAdapter dataAdapter, DataTable dataTable)
{
if (dataAdapter == null || dataTable == null || dataTable.DataSet == null)
{
return;
}
// call the Fill method on the dataset, which loads the select statement
// as a command
dataAdapter.Fill(dataTable.DataSet);
// now delete each row in the datatable (in memory)
foreach (DataRow d in dataTable.Rows)
{
d.Delete();
}
// now call Update() which will sync the database with the now cleared table
dataAdapter.Update(dataTable.DataSet);
}
/// <summary>
/// 执行数据的批量导入功能
/// </summary>
/// <param name="srcdt">数据源</param>
/// <param name="destTableName">要导入的目的数据表</param>
/// <returns>导入的数据行数</returns>
protected override int DataBatchImportPart(DataTable srcdt, string destTableName)
{
this.Open();
DataSet myDS = new DataSet();
IDbCommand command = SetCommand(string.Format("select * from {0} where 1=0", destTableName), null, true);
IDbDataAdapter sqlDA = CreateDbDataAdapter();
sqlDA.SelectCommand = command;
sqlDA.TableMappings.Add(destTableName, destTableName);
sqlDA.Fill(myDS);
//DbCommandBuilder 自动生成用于协调 DataSet 的更改与关联数据库的单表命令
//此对象用于填充SqlDataAdapter 的InsertCommand、DeleteCommand、UpdateCommand对象
DbCommandBuilder sqlcommandbuilder = CreateDbCommandBuilder(sqlDA);
for (int j = 0; j < srcdt.Rows.Count; j++)
{
myDS.Tables[0].Rows.Add(srcdt.Rows[j].ItemArray);
}
sqlDA.Update(myDS);
return(srcdt.Rows.Count);
}
public int UpdateDataSet(DataSet dataSet, ICommand insertCommand, ICommand updateCommand, ICommand deleteCommand, string tableName, ITransaction transaction)
{
if (transaction == null)
{
throw new ArgumentNullException("transaction");
}
if (dataSet == null)
{
throw new ArgumentNullException("dataSet");
}
IDbDataAdapter dbAdapter = this.GetDataAdapter();
if (insertCommand != null)
{
insertCommand.Transaction = transaction;
dbAdapter.InsertCommand = insertCommand.DbCommand;
}
if (updateCommand != null)
{
updateCommand.Transaction = transaction;
dbAdapter.UpdateCommand = updateCommand.DbCommand;
}
if (deleteCommand != null)
{
deleteCommand.Transaction = transaction;
dbAdapter.DeleteCommand = deleteCommand.DbCommand;
}
if (tableName != null)
{
dbAdapter.TableMappings.Add("Table", tableName);
}
return(dbAdapter.Update(dataSet));
}
public void Save(System.Collections.IDictionary dict)
{
if (!IsValidState)
{
return;
}
if (dict != null && dict.Count > 0)
{
//if (_saveTable == null)
_saveTable = LoadConfigData(_dsSave, _daSave);
foreach (string key in dict.Keys)
{
AddOrUpdateRow(_saveTable, key, dict[key] as string);
}
if (_configElement != null && _configElement.WatchChange)
{
if (!_dbGenericKeyValueConfigStorageParams.LAST_UPDATE_DATETIME_KEY_NAME.IsNullOrWhiteSpace())
{
AddOrUpdateRow(_saveTable, _dbGenericKeyValueConfigStorageParams.LAST_UPDATE_DATETIME_KEY_NAME, DateTime.Now.ToString());
}
}
try
{
_daSave.Update(_dsSave); //, _dbGenericKeyValueConfigStorageParams.TABLE_NAME);
_dsSave.AcceptChanges();
}
catch
{
_dsSave.RejectChanges();
throw;
}
}
}
public virtual void Insert(IDbConnection conn, IDbTransaction transaction)
{
ExcpHelper.ThrowIf(this.Table == null, "TableSpec is not specified for type {0}", this.GetType());
using (DataTable dtInsert = DataCopyTables.GetEmptyDataTableByName(conn, transaction, this.Table.TableName))
{
using (IDbCommand cmdInsert = DataCopy.GenerateInsertCommand(conn, transaction, dtInsert, this.Table))
{
using (IDisposable dsp = SqlObjectFactory.CreateDbAdapter(null) as IDisposable)
{
IDbDataAdapter daInsert = dsp as IDbDataAdapter;
Debug.Assert(daInsert != null);
daInsert.InsertCommand = cmdInsert;
dtInsert.AcceptChanges();
DataRow dr = this.CreateDataRow(dtInsert);
dtInsert.Rows.Add(dr);
using (DataSet ds = new DataSet())
{
ds.Tables.Add(dtInsert);
daInsert.Update(ds);
}
if (this.Table.IsAutoGeneratedIdentity)
{
Debug.Assert(this.Table.IdentityNames.Count > 0);
m_objJustInsertedIdentity = ConnectionManager.GetLastInsertId(conn, transaction, this.Table.IdentityNames[0]);
}
}
}
}
}
private string ExportMdb(string sql, DataTable exportTable, int incidOrdinal,
List <int> fieldCountList, out int exportRowCount)
{
exportRowCount = -1;
DbOleDb dbOut = null;
string tempPath = String.Empty;
try { tempPath = Path.GetTempPath(); }
catch { tempPath = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData); }
tempPath = Path.Combine(tempPath, Path.GetFileNameWithoutExtension(Path.GetRandomFileName()) + ".mdb");
try
{
if (File.Exists(tempPath))
{
File.Delete(tempPath);
}
OdbcCP32 odbc = new OdbcCP32();
odbc.CreateDatabase(tempPath);
string connString = String.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};", tempPath);
string defaultSchema = "";
bool promptPwd = false;
dbOut = new DbOleDb(ref connString, ref defaultSchema, ref promptPwd,
Properties.Resources.PasswordMaskString, Settings.Default.UseAutomaticCommandBuilders,
true, Settings.Default.DbIsUnicode, Settings.Default.DbUseTimeZone, 255,
Settings.Default.DbBinaryLength, Settings.Default.DbTimePrecision,
Settings.Default.DbNumericPrecision, Settings.Default.DbNumericScale);
dbOut.CreateTable(exportTable);
DataSet datasetOut = new DataSet("Export");
IDbDataAdapter adapterOut = dbOut.CreateAdapter(exportTable);
adapterOut.Fill(datasetOut);
int[] pkOrdinals = exportTable.PrimaryKey.Select(c => c.Ordinal).ToArray();
exportTable.PrimaryKey = pkOrdinals.Select(o => exportTable.Columns[o]).ToArray();
adapterOut.TableMappings.Clear();
adapterOut.TableMappings.Add(exportTable.TableName, datasetOut.Tables[0].TableName);
exportTable = datasetOut.Tables[0];
DataRow exportRow = null;
bool rowAdded = false;
using (IDataReader reader = _viewModelMain.DataBase.ExecuteReader(sql,
_viewModelMain.DataBase.Connection.ConnectionTimeout, CommandType.Text))
{
int runTotal = 0;
int[][] fieldMapTemplate = fieldCountList.Select((i, index) =>
new int[] { index, index + runTotal, i, (runTotal += i - (i > 0 ? 1 : 0)) })
.Select(e => new int[] { e[0], e[1], e[1] + e[2] }).ToArray();
int[] dupsAllowed = (from e in exportTable.Columns.Cast <DataColumn>()
let q = from c in _viewModelMain.HluDataset.incid_sources.Columns.Cast <DataColumn>()
where !Regex.IsMatch(c.ColumnName,
@"(\Aincid\z|_(importance|id)\z)", RegexOptions.IgnoreCase)
select c.ColumnName
where q.Count(n => Regex.IsMatch(e.ColumnName,
n + @"(_[0-9]+)*\z", RegexOptions.IgnoreCase)) == 1
select e.Ordinal).ToArray();
int[][] fieldMap = new int[fieldMapTemplate.Length][];
string currIncid = String.Empty;
string prevIncid = String.Empty;
while (reader.Read())
{
currIncid = reader.GetString(incidOrdinal);
if (currIncid != prevIncid)
{
prevIncid = currIncid;
fieldMap = fieldMapTemplate.Select(a => new int[] { a[0], a[1], a[2] }).ToArray();
if (exportRow != null)
{
exportTable.Rows.Add(exportRow);
rowAdded = true;
}
exportRow = exportTable.NewRow();
rowAdded = false;
for (int i = 0; i < fieldMap.GetLength(0); i++)
{
object item = reader.GetValue(fieldMap[i][0]);
if (item != DBNull.Value)
{
exportRow[fieldMap[i][1]] = reader.GetValue(fieldMap[i][0]);
}
fieldMap[i][1]++;
}
}
else
{
for (int i = 0; i < fieldMap.GetLength(0); i++)
{
if (fieldMap[i][1] < fieldMap[i][2])
{
object item = reader.GetValue(fieldMap[i][0]);
if ((item != DBNull.Value) && (!item.Equals(exportRow[fieldMap[i][1] - 1]) ||
(Array.IndexOf(dupsAllowed, fieldMap[i][1]) != -1)))
{
exportRow[fieldMap[i][1]++] = item;
}
}
}
}
}
}
if (!rowAdded && (exportRow != null))
{
exportTable.Rows.Add(exportRow);
}
exportRowCount = adapterOut.Update(datasetOut);
return(exportRowCount != -1 ? tempPath : null);
}
catch
{
if (File.Exists(tempPath))
{
try { File.Delete(tempPath); }
catch { _viewModelMain.ExportMdbs.Add(tempPath); }
}
return(null);
}
finally
{
if ((dbOut != null) && (dbOut.Connection.State != ConnectionState.Closed))
{
try { dbOut.Connection.Close(); }
catch { }
}
}
}
/// <summary>
/// 数据导入.
/// </summary>
/// <param name="tableName"></param>
public void DoImp(string tableName)
{
Console.WriteLine("从 XML 文件中，读取数据到 DataTable 里面。");
DataTable dtXml = new DataTable();
dtXml.ReadXmlSchema(GetXmlSchema(tableName));
dtXml.ReadXml(GetXml(tableName));
using (IDbConnection conn = GetDbConnection(ConnString))
{
// 创建一个适配器
IDbDataAdapter adapter = GetDbDataAdapter(GetExpSql(tableName), conn);
// 创建DataSet，用于存储数据.
DataSet resultDataSet = new DataSet();
// 表结构定义导入DataSet.
adapter.FillSchema(resultDataSet, SchemaType.Source);
// 数据导入DataSet.
adapter.Fill(resultDataSet);
// 这些数据此时作为 DataSet 的 Tables 集合内独立的 DataTable 对象来提供。
// 如果在对 FillSchema 和 Fill 的调用中指定了一个表名，
// 则可以使用该名称访问您需要的特定表。
DataTable dtDatabase = resultDataSet.Tables[0];
Console.WriteLine("将数据写入到数据库中...");
// 遍历 xml 文件中的每一行.
foreach (DataRow xmlRow in dtXml.Rows)
{
// 从 DataTable 获取新的 DataRow 对象。
DataRow drCurrent = dtDatabase.NewRow();
// 每一列依次赋值.
for (int i = 0; i < dtDatabase.Columns.Count; i++)
{
// 取得列名.
string colName = dtDatabase.Columns[i].ColumnName;
// 赋值.
drCurrent[colName] = xmlRow[colName];
}
// 将新的对象传递给 DataTable.Rows 集合的 Add 方法。
dtDatabase.Rows.Add(drCurrent);
}
// 通过 SqlCommandBuilder 设置 DataAdapter 对象的 InsertCommand、UpdateCommand 和 DeleteCommand 属性。
// 注意：表必须有主键信息
DoSqlCommandBuilder(adapter);
// 更新原始数据库，可将 DataSet 传递到 DataAdapter 对象的 Update 方法。
adapter.Update(resultDataSet);
}
Console.WriteLine("处理完毕！");
}
public static void WriteSelectionScratchTable(DataColumn[] targetColumns, DataTable idList)
{
try
{
int incidOrdinal = -1;
if (idList.Columns.Contains(_incidTable.incidColumn.ColumnName))
{
if (idList.Columns[_incidTable.incidColumn.ColumnName].DataType == _hluLayerStructure.incidColumn.DataType)
{
incidOrdinal = idList.Columns[_incidTable.incidColumn.ColumnName].Ordinal;
}
else
{
return;
}
}
else
{
var q = idList.Columns.Cast <DataColumn>().Where(c => c.ColumnName.EndsWith(
Resources.ColumnTableNameSeparator + _incidTable.incidColumn.ColumnName) &&
c.DataType == _incidTable.incidColumn.DataType);
if (q.Count() == 1)
{
incidOrdinal = q.ElementAt(0).Ordinal;
}
else
{
return;
}
}
// incid column always has the same name as in the GIS layer structure
if (idList.Columns[incidOrdinal].ColumnName != _hluLayerStructure.incidColumn.ColumnName)
{
idList.Columns[incidOrdinal].ColumnName = _hluLayerStructure.incidColumn.ColumnName;
}
try
{
_scratchDb.ExecuteNonQuery(String.Format("DROP TABLE {0}", _scratchSelTable),
_scratchDb.Connection.ConnectionTimeout, CommandType.Text);
}
catch { }
if (String.IsNullOrEmpty(idList.TableName))
{
idList.TableName = _scratchSelTable;
}
if ((idList.PrimaryKey == null) || (idList.PrimaryKey.Length == 0))
{
idList.PrimaryKey = new DataColumn[] { idList.Columns[incidOrdinal] }
}
;
if (!_scratchDb.CreateTable(idList))
{
return;
}
DataTable scratchTable = idList.Clone();
DataSet datasetOut = new DataSet(_scratchSelTable);
IDbDataAdapter adapterOut = _scratchDb.CreateAdapter(scratchTable);
adapterOut.Fill(datasetOut);
adapterOut.TableMappings.Clear();
adapterOut.TableMappings.Add(scratchTable.TableName, datasetOut.Tables[0].TableName);
scratchTable = datasetOut.Tables[0];
foreach (DataRow r in idList.Rows)
{
scratchTable.LoadDataRow(r.ItemArray, false);
}
adapterOut.Update(datasetOut);
}
catch { }
finally
{
if ((_scratchDb != null) && (_scratchDb.Connection.State != ConnectionState.Closed))
{
try { _scratchDb.Connection.Close(); }
catch { }
}
}
}
public void InsertOrUpdate <T>(LineObjectCollection <T> locLineObjects, TableSpecification ts, UpdatesLn updatesLn) where T : ILineObject <T>
{
CheckTime ct = new CheckTime(false, "InsertOrUpdate for '{0}' entered", ts.TableName);
List <object> lInserted = new List <object>();
List <object> lUpdated = new List <object>();
m_diInserted.Add(ts.TableName, lInserted);
m_diUpdated.Add(ts.TableName, lUpdated);
if (locLineObjects == null)
{
return;
}
UpdateStatistic us = m_uss.EnsureStatistic(ts.TableName);
string sInfo = string.Format("{0} table [{1}] {2}; ", m_sLiveBet, ts.TableName, locLineObjects);
#if DEBUG
int iInsertCount = 0;
int iUpdateCount = 0;
#endif
try
{
ct.AddEvent("Empty DataTables created.");
foreach (string sKey in locLineObjects.Keys)
{
using (DataCopyTables dct = DataCopyTables.GetDataCopyTables(m_conn, m_transaction, ts.TableName))
{
T obj = locLineObjects[sKey];
obj.UpdateId = updatesLn.UpdateId;
if (obj.IsNew)
{
DataRow drNew = obj.CreateDataRow(dct.InsertDataTable);
dct.InsertDataTable.Rows.Add(drNew);
lInserted.Add(obj);
}
else
{
DataRow drNew = obj.CreateDataRow(dct.UpdateDataTable);
dct.UpdateDataTable.Rows.Add(drNew);
lUpdated.Add(obj);
}
#if DEBUG
iInsertCount = dct.InsertDataTable.Rows.Count;
iUpdateCount = dct.UpdateDataTable.Rows.Count;
#endif
if (dct.InsertDataTable.Rows.Count > 0)
{
using (IDbCommand cmdInsert = GenerateInsertCommand(m_conn, m_transaction, dct.InsertDataTable, ts))
{
using (IDisposable dsp = SqlObjectFactory.CreateDbAdapter(null) as IDisposable)
{
IDbDataAdapter daInsert = dsp as IDbDataAdapter;
Debug.Assert(daInsert != null);
daInsert.InsertCommand = cmdInsert;
dct.InsertDataTable.AcceptChanges();
foreach (DataRow dr in dct.InsertDataTable.Rows)
{
dr.SetAdded();
}
using (DataSet ds = new DataSet())
{
ds.Tables.Add(dct.InsertDataTable);
daInsert.Update(ds);
}
}
}
us.InsertCount = dct.InsertDataTable.Rows.Count;
ct.AddEvent("Insert completed ({0})", dct.InsertDataTable.Rows.Count);
}
if (dct.UpdateDataTable.Rows.Count > 0)
{
using (IDbCommand cmdUpdate = GenerateUpdateCommand(m_conn, m_transaction, dct.UpdateDataTable, ts))
{
using (IDisposable dsp = SqlObjectFactory.CreateDbAdapter(null) as IDisposable)
{
IDbDataAdapter daUpdate = dsp as IDbDataAdapter;
Debug.Assert(daUpdate != null);
daUpdate.UpdateCommand = cmdUpdate;
dct.UpdateDataTable.AcceptChanges();
foreach (DataRow dr in dct.UpdateDataTable.Rows)
{
dr.SetModified();
}
using (DataSet ds = new DataSet())
{
ds.Tables.Add(dct.UpdateDataTable);
daUpdate.Update(ds);
}
}
}
us.UpdateCount = dct.UpdateDataTable.Rows.Count;
ct.AddEvent("Update completed ({0})", dct.UpdateDataTable.Rows.Count);
}
ct.AddEvent("Insert/Update filled up (I.Cnt={0}; U.Cnt={1})", dct.InsertDataTable.Rows.Count, dct.UpdateDataTable.Rows.Count);
}
}
//Debug.Assert(us.Count == arrObjects.Length);
//m_elInfo.AddFormat("{0} Result: Succeeded; Inserted: {1}; Updated: {2}; Skipped; {3}", sInfo, us.InsertCount, us.UpdateCount, us.SkipCount);
}
catch (Exception excp)
{
m_elInfo.AddFormat("{0} Result: FAILED; Inserted: {1}; Updated: {2};", sInfo, us.InsertCount, us.UpdateCount);
#if DEBUG
if (typeof(T) == typeof(TaggedStringLn))
{
FindDuplucates(locLineObjects);
}
int iCount = 0;
string sObjectList = string.Format("ERROR objects (Count={0})\r\n", locLineObjects.Count);
foreach (T obj in locLineObjects.Values)
{
sObjectList += obj.ToString() + "\r\n";
if (++iCount > MAX_ERROR_LIST_COUNT)
{
sObjectList += string.Format("And More {0} objects not listed", locLineObjects.Count - iCount);
break;
}
}
m_logger.Error(sObjectList, excp);
#endif
ExcpHelper.ThrowUp(excp, "ERROR InsertOrUpdate() for {0}", locLineObjects);
}
finally
{
ct.AddEvent("InsertOrUpdate for '{0}' completed", ts.TableName);
ct.Info(m_logger);
}
}
/// /////////////////////////////////////////////////////////////////
public int Update(DataSet ds)
{
DataTable table = ds.Tables[CContexteDonnee.GetNomTableForType(m_typeDonnees)];
ArrayList listeRelationsAutoReference = new ArrayList();
foreach (DataRelation relation in table.ParentRelations)
{
if (relation.ParentTable == table &&
relation.ChildTable == table)
{
listeRelationsAutoReference.Add(relation);
if (relation.ParentColumns.Length != 1 ||
relation.ChildColumns.Length != 1)
{
throw new Exception(I.T("Auto referred tables on several keys aren't allowed|102"));
}
}
}
if (listeRelationsAutoReference.Count == 0)
{
return(m_adapter.Update(ds));
}
if (table == null)
{
return(0);
}
int nNb = 0;
//Trie les éléments pour modifs et ajouts
//Il faut modifier en premier les lignes qui sont dépendantes des autres
/*Pour trier : On regarde les fils de chaque ligne à modifier. On place
* Chaque ligne après ses fils dans la liste. A la fin, on inverse la liste
* */
ArrayList listeToAddOrUpdate = new ArrayList();
CArbreDependance arbreTotal = new CArbreDependance(null);
Hashtable tableRowToArbre = new Hashtable();
foreach (DataRow row in table.Rows)
{
if (row.RowState == DataRowState.Added || row.RowState == DataRowState.Modified)
{
foreach (DataRelation relation in listeRelationsAutoReference)
{
CArbreDependance arbre = tableRowToArbre[row] as CArbreDependance;
if (arbre == null)
{
arbre = new CArbreDependance(row);
}
tableRowToArbre[row] = arbre;
//Cherche l'arbreParent;
DataRow rowParente = row.GetParentRow(relation);
CArbreDependance arbreParent = arbreTotal;
if (rowParente != null)
{
arbreParent = (CArbreDependance)tableRowToArbre[rowParente];
if (arbreParent == null)
{
arbreParent = arbreTotal;
}
}
arbre.ArbreParent = arbreParent;
foreach (DataRow rowFille in row.GetChildRows(relation))
{
CArbreDependance arbreFille = (CArbreDependance)tableRowToArbre[rowFille];
if (arbreFille != null)
{
arbreFille.ArbreParent = arbre;
}
}
}
}
}
arbreTotal.InsertFillesIntoArrayList(listeToAddOrUpdate);
foreach (DataRow row in listeToAddOrUpdate)
{
string strLastId = row[0].ToString();
IDbCommand command = null;
if (row.RowState == DataRowState.Added)
{
command = InsertCommand;
}
if (row.RowState == DataRowState.Modified)
{
command = UpdateCommand;
}
if (command != null)
{
bool bForceOpen = false;
if (command.Connection.State != ConnectionState.Open)
{
bForceOpen = true;
command.Connection.Open();
}
int nUpdateOrAdd = 0;
if (row.RowState == DataRowState.Added)
{
if (command.CommandText.ToUpper().Contains("INSERT"))
{
nUpdateOrAdd = ExecuteAdd(command, row);
}
else
{
nUpdateOrAdd++;
}
}
else
{
if (command.CommandText.ToUpper().Contains("UPDATE"))
{
nUpdateOrAdd = ExecuteUpdate(command, row);
}
else
{
nUpdateOrAdd++;
}
}
if (nUpdateOrAdd != 1)
{
StringBuilder bl = new StringBuilder();
bl.Append(CObjetDonnee.GetMessageAccesConccurentiel(row));
//I.T("Another program has modified the data.|103"));
bl.Append("\r\n");
if (row.HasVersion(DataRowVersion.Original))
{
foreach (DataColumn col in row.Table.Columns)
{
try
{
bl.Append(row[col, DataRowVersion.Original].ToString());
bl.Append(" / ");
bl.Append(row[col].ToString());
bl.Append("\r\n");
}
catch
{
}
}
}
throw new Exception(bl.ToString());
}
nNb += nUpdateOrAdd;
if (bForceOpen)
{
command.Connection.Close();
}
}
}
//traite les suppression
//Il faut supprimer en premier les lignes qui ne sont pas dépendantes des autres
/*Pour trier : On regarde pour les fils de chaque ligne à supprimer. On place
* Chaque ligne après ses fils dans la liste
* */
/*Stef 25/03 : changement de l'algo : utilisation d'un arbre
* */
ArrayList listeToDelete = new ArrayList();
arbreTotal = new CArbreDependance(null);
tableRowToArbre = new Hashtable();
foreach (DataRow row in table.Rows)
{
if (row.RowState == DataRowState.Deleted)
{
foreach (DataRelation relation in listeRelationsAutoReference)
{
CArbreDependance arbre = tableRowToArbre[row] as CArbreDependance;
if (arbre == null)
{
arbre = new CArbreDependance(row);
}
tableRowToArbre[row] = arbre;
//Cherche l'arbreParent;
DataRow rowParente = row.GetParentRow(relation, DataRowVersion.Original);
CArbreDependance arbreParent = arbreTotal;
if (rowParente != null)
{
arbreParent = (CArbreDependance)tableRowToArbre[rowParente];
if (arbreParent == null)
{
arbreParent = arbreTotal;
}
}
arbre.ArbreParent = arbreParent;
foreach (DataRow rowFille in row.GetChildRows(relation, DataRowVersion.Original))
{
CArbreDependance arbreFille = (CArbreDependance)tableRowToArbre[rowFille];
if (arbreFille != null)
{
arbreFille.ArbreParent = arbre;
}
}
}
}
}
arbreTotal.InsertFillesIntoArrayList(listeToDelete);
listeToDelete.Reverse();
/*foreach ( DataRow row in table.Rows )
* {
* if ( row.RowState == DataRowState.Deleted )
* {
* Hashtable tableFilles = new Hashtable();
* foreach ( DataRelation relation in listeRelationsAutoReference )
* {
* DataRow[] childs = table.Select ( relation.ChildColumns[0]+"="+
* row[relation.ParentColumns[0], DataRowVersion.Original], null, DataViewRowState.Deleted );
* foreach ( DataRow rowFille in childs )
* tableFilles[rowFille] = true;
* }
* int nPosInsert = 0;
* for ( int nLook = 0; nLook < listeToDelete.Count && tableFilles.Count > 0; nLook++ )
* {
* if ( tableFilles[listeToDelete[nLook]] != null )
* {
* nPosInsert = nLook+1;
* tableFilles.Remove(listeToDelete[nLook]);
* }
* }
* listeToDelete.Insert ( nPosInsert, row );
* }
* }*/
IDbCommand deleteCommand = DeleteCommand;
if (deleteCommand.CommandText.ToUpper().Contains("DELETE"))
{
foreach (DataRow row in listeToDelete)
{
if (row.RowState == DataRowState.Deleted)
{
nNb += ExecuteDelete(deleteCommand, row);
}
}
}
else
{
nNb += listeToDelete.Count;
}
return(nNb);
}
public object DoInDataAdapter(IDbDataAdapter dataAdapter)
{
//TODO - did not make copies of parameters...
if (insertCommand == null && updateCommand == null && deleteCommand == null)
{
throw new ArgumentException("All commands for DataSet Update operation are null");
}
if (insertCommand != null)
{
dataAdapter.InsertCommand = insertCommand;
ApplyConnectionAndTx(dataAdapter.InsertCommand, dataAdapter.SelectCommand);
}
if (updateCommand != null)
{
dataAdapter.UpdateCommand = updateCommand;
ApplyConnectionAndTx(dataAdapter.UpdateCommand, dataAdapter.SelectCommand);
}
if (deleteCommand != null)
{
dataAdapter.DeleteCommand = deleteCommand;
ApplyConnectionAndTx(dataAdapter.DeleteCommand, dataAdapter.SelectCommand);
}
foreach (DataTableMapping dataTableMapping in mappingCollection)
{
dataAdapter.TableMappings.Add(((ICloneable)dataTableMapping).Clone());
}
if (dataAdapterSetter != null)
{
dataAdapterSetter.SetValues(dataAdapter);
}
if (containsDataSet)
{
return dataAdapter.Update(dataSet);
}
else
{
//TODO should query metadata to see if supports filling dataTable directly.
if (dataAdapter is DbDataAdapter)
{
return ((DbDataAdapter)dataAdapter).Update(dataTable);
}
else
{
//TODO could create DataSet and extract DataTable... for now just throw
throw new DataException("Provider does not support filling DataTable directly");
}
}
}
/// <summary>
/// 添加或更新邮件通知参数
/// </summary>
/// <param name="session"></param>
/// <param name="lstParams"></param>
/// lstParams[0] : 是否启用邮件通知
/// lstParams[1] : SMTP
/// lstParams[2] : SMTP Port
/// lstParams[3] : 需要SSL
/// lstParams[4] : 账号
/// lstParams[5] : 密码
/// lstParams[6] : 需要身份认证
/// lstParams[7] : Email Address
/// <returns></returns>
private OperationReturn UpdateEmailInfo(SessionInfo session, List <string> lstParams)
{
OperationReturn optReturn = new OperationReturn();
try
{
string rentToken = session.RentInfo.Token;
string strSql = string.Empty;
lstParams[5] = S2400EncryptOperation.EncryptWithM002(lstParams[5]);
IDbConnection objConn = null;
IDbDataAdapter objAdapter = null;
DbCommandBuilder objCmdBuilder = null;
switch (session.DBType)
{
case 2:
strSql = string.Format("SELECT * FROM T_11_001_{0} WHERE C001 = '{1}' and C002 =24 and C004 = 24041 ORDER BY C005", rentToken, session.RentID);
optReturn = MssqlOperation.GetDataSet(session.DBConnectionString, strSql);
if (!optReturn.Result)
{
return(optReturn);
}
objConn = MssqlOperation.GetConnection(session.DBConnectionString);
objAdapter = MssqlOperation.GetDataAdapter(objConn, strSql);
objCmdBuilder = MssqlOperation.GetCommandBuilder(objAdapter);
break;
case 3:
strSql = string.Format("SELECT * FROM T_11_001_{0} WHERE C001 = '{1}' AND C002 =24 AND C004 = 24041 ORDER BY C005", rentToken, session.RentID);
optReturn = OracleOperation.GetDataSet(session.DBConnectionString, strSql);
if (!optReturn.Result)
{
return(optReturn);
}
objConn = OracleOperation.GetConnection(session.DBConnectionString);
objAdapter = OracleOperation.GetDataAdapter(objConn, strSql);
objCmdBuilder = OracleOperation.GetCommandBuilder(objAdapter);
break;
}
if (objConn == null || objAdapter == null || objCmdBuilder == null)
{
optReturn.Result = false;
optReturn.Code = Defines.RET_OBJECT_NULL;
optReturn.Message = string.Format("Db object is null");
return(optReturn);
}
objCmdBuilder.ConflictOption = ConflictOption.OverwriteChanges;
objCmdBuilder.SetAllValues = false;
DataSet objDataSet = new DataSet();
objAdapter.Fill(objDataSet);
List <string> listMsg = new List <string>();
if (lstParams[0] == "0")
{
DataRow[] drs = objDataSet.Tables[0].Select("c003 = 240401");
if (drs.Count() > 0)
{
drs[0]["C006"] = lstParams[0];
drs[0]["C018"] = DateTime.Now.ToUniversalTime().ToString("yyyy-MM-dd HH:mm:ss");
drs[0]["C019"] = session.UserID;
listMsg.Add("Update Email Enable : 0");
}
}
else
{
for (int i = 0; i < lstParams.Count; i++)
{
string strParamID = "24040" + (i + 1);
DataRow[] drs = objDataSet.Tables[0].Select(string.Format("c003 = {0}", strParamID));
//如果存在 则修改
if (drs.Count() > 0)
{
drs[0]["C006"] = lstParams[i];
drs[0]["C018"] = DateTime.Now.ToUniversalTime().ToString("yyyy-MM-dd HH:mm:ss");
drs[0]["C019"] = session.UserID;
listMsg.Add(string.Format("Update {0}", lstParams[i]));
}
//如果不存在 则插入
else
{
DataRow row = objDataSet.Tables[0].NewRow();
row["C001"] = session.RentID;
row["C002"] = 24;
row["C003"] = "24040" + (i + 1);
row["C004"] = "24041";
row["C005"] = i;
row["C006"] = lstParams[i];
row["C007"] = 13;
row["C009"] = 0;
row["C010"] = 0;
row["C011"] = 0;
row["C015"] = 0;
row["C018"] = DateTime.Now.ToUniversalTime().ToString("yyyy-MM-dd HH:mm:ss");
row["C019"] = session.UserID;
row["C020"] = -1;
row["C021"] = DateTime.Now.ToUniversalTime().ToString("yyyy-MM-dd HH:mm:ss");
objDataSet.Tables[0].Rows.Add(row);
listMsg.Add(string.Format("Add {0}", lstParams[i]));
}
}
}
objAdapter.Update(objDataSet);
objDataSet.AcceptChanges();
optReturn.Data = listMsg;
optReturn.Result = true;
optReturn.Code = Defines.RET_SUCCESS;
}
catch (Exception ex)
{
optReturn.Result = false;
optReturn.Code = Defines.RET_FAIL;
optReturn.Message = ex.Message;
optReturn.Exception = ex;
}
return(optReturn);
}
public object DoInDataAdapter(IDbDataAdapter dataAdapter)
{
dataAdapter.SelectCommand.CommandType = selectCommandType;
dataAdapter.SelectCommand.CommandText = selectSql;
ParameterUtils.CopyParameters(dataAdapter.SelectCommand, selectParameters);
foreach (DataTableMapping dataTableMapping in mappingCollection)
{
dataAdapter.TableMappings.Add(((ICloneable)dataTableMapping).Clone());
}
if (dataAdapterSetter != null)
{
dataAdapterSetter.SetValues(dataAdapter);
}
//TODO consider refactoring to put this inside IDbMetadata
PropertyInfo selectCommandProperty = commandBuilder.GetType().GetProperty("DataAdapter",
BindingFlags.DeclaredOnly |
BindingFlags.GetProperty |
BindingFlags.Public |
BindingFlags.Instance
);
selectCommandProperty.SetValue(commandBuilder, dataAdapter, null);
ParameterUtils.CopyParameters(selectParameters, dataAdapter.SelectCommand);
if (containsDataSet)
{
return dataAdapter.Update(dataSet);
}
else
{
//TODO should query metadata to see if supports filling dataTable directly.
if (dataAdapter is DbDataAdapter)
{
return ((DbDataAdapter)dataAdapter).Update(dataTable);
}
else
{
//TODO could create DataSet and extract DataTable... for now just throw
throw new DataException("Provider does not support filling DataTable directly");
}
}
}
