private bool DeleteObjects <T>(LineObjectCollection <T> locLineObjects, TableSpecification ts) where T : IRemovableLineObject <T> { // DK - At the moment this method works ONLY with BIGINT type primary keys if (locLineObjects != null) { IdentityList il = new IdentityList(); try { foreach (IRemovableLineObject <T> obj in locLineObjects.Values) { il.AddUnique(obj.RemoveId); } DataCopy.ExecuteScalar(m_conn, m_transaction, "DELETE FROM {0} WHERE {1} IN ({2})", ts.TableName, ts.IdentityNames[0], il.FormatIds()); UpdateStatistic us = m_uss.EnsureStatistic(ts.TableName); us.DeleteCount = il.Count; return(true); } catch (Exception excp) { m_logger.Excp(excp, "DeleteObjects<{0}> ERROR. ObjectIds: {1}", typeof(T), il.FormatIds()); throw; } } return(false); }
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 static IDbCommand GenerateUpdateCommand(IDbConnection connection, IDbTransaction transaction, DataTable dt, TableSpecification ts) { string sTemplate = @" UPDATE [{0}] _SET_ {1} WHERE {2} "; IDbCommand dbc = SqlObjectFactory.CreateDbCommand(connection, transaction, string.Empty); dbc.CommandTimeout = 1000; List <string> lSetStrings = new List <string>(); List <string> lIdentityStrings = new List <string>(); //sIdentityName = sIdentityName.ToLowerInvariant(); foreach (DataColumn dc in dt.Columns) { if (ts.IdentityNames.Contains(dc.ColumnName.ToLowerInvariant())) { lIdentityStrings.Add(string.Format(" [{0}] = @{0}", dc.ColumnName)); } else { lSetStrings.Add(string.Format(" [{0}] = @{0}", dc.ColumnName)); } dbc.Parameters.Add(SqlObjectFactory.CreateParameter(dc.ColumnName, null, dc.ColumnName)); } string sCmdQuery = string.Format(sTemplate, ts.TableName, string.Join(",\r\n", lSetStrings.ToArray()), string.Join("\r\nAND\r\n", lIdentityStrings.ToArray())); dbc.CommandText = DataCopy.CheckQueryByDialect(sCmdQuery); return(dbc); }
public static IDbCommand GenerateInsertCommand(IDbConnection connection, IDbTransaction transaction, DataTable dt, TableSpecification ts) { string sTemplate = @" INSERT INTO [{0}] ({1}) VALUES ({2}) "; IDbCommand dbc = SqlObjectFactory.CreateDbCommand(connection, transaction, string.Empty); dbc.CommandTimeout = 1000; List <string> lColStrings = new List <string>(); List <string> lSetStrings = new List <string>(); foreach (DataColumn dc in dt.Columns) { if (ts.IsAutoGeneratedIdentity && ts.IdentityNames.Contains(dc.ColumnName.ToLowerInvariant())) { continue; } switch (ConnectionManager.Dialect) { case DatabaseDialect.MsSql: case DatabaseDialect.PgSql: lColStrings.Add(string.Format("[{0}]", dc.ColumnName)); lSetStrings.Add(string.Format("@{0}", dc.ColumnName)); dbc.Parameters.Add(SqlObjectFactory.CreateParameter(dc.ColumnName, null, dc.ColumnName)); break; case DatabaseDialect.MySql: case DatabaseDialect.LtSql: lColStrings.Add(dc.ColumnName); lSetStrings.Add(string.Format("@{0}", dc.ColumnName)); dbc.Parameters.Add(SqlObjectFactory.CreateParameter(dc.ColumnName, null, dc.ColumnName)); break; default: Debug.Assert(false); break; } } string sCmdQuery = string.Format(sTemplate, ts.TableName, string.Join(",", lColStrings.ToArray()), string.Join(",", lSetStrings.ToArray())); dbc.CommandText = DataCopy.CheckQueryByDialect(sCmdQuery); return(dbc); }