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