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);
}
}
}
}
}
public static object ExecuteScalarWithSqlParams(IDbConnection conn, IDbTransaction transaction, string sQuery,
List <IDbDataParameter> lParams)
{
try
{
EnsureConnection(conn);
sQuery = CheckQueryByDialect(sQuery);
using (IDbCommand cmd = SqlObjectFactory.CreateDbCommand(conn, transaction, sQuery))
{
cmd.CommandTimeout = 300;
foreach (IDbDataParameter prm in lParams)
{
cmd.Parameters.Add(prm);
}
return(cmd.ExecuteScalar());
}
}
catch (Exception excp)
{
m_logger.ErrorFormat("ExecuteScalarWithSqlParams() ERROR:{0}\r\n{1}", excp, excp.Message, excp.StackTrace);
throw;
}
}
public static object ExecuteScalar(IDbConnection conn, IDbTransaction transaction, string sFormatQuery,
params object[] args)
{
string sQuery = sFormatQuery;
try
{
EnsureConnection(conn);
sQuery = string.Format(sFormatQuery, args);
sQuery = CheckQueryByDialect(sQuery);
using (IDbCommand cmd = SqlObjectFactory.CreateDbCommand(conn, transaction, sQuery))
{
cmd.CommandTimeout = 300;
return(cmd.ExecuteScalar());
}
}
catch (Exception excp)
{
m_logger.Excp(excp, "ExecuteScalar() ERROR:\r\nQuery:\r\n{0}\r\n", sQuery);
return(null);
}
}
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);
}
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 DataTable GetDataTableWithSqlParams(IDbConnection dcn, IDbTransaction dtn, string sQuery, List <IDbDataParameter> lParams)
{
try
{
sQuery = CheckQueryByDialect(sQuery);
using (IDbCommand cmd = SqlObjectFactory.CreateDbCommand(dcn, dtn, sQuery))
{
cmd.CommandTimeout = 300;
foreach (IDbDataParameter prm in lParams)
{
cmd.Parameters.Add(prm);
}
using (IDisposable dsp = SqlObjectFactory.CreateDbAdapter(cmd) as IDisposable)
{
IDbDataAdapter da = dsp as IDbDataAdapter;
using (DataSet ds = new DataSet())
{
da.Fill(ds);
DataTable dtResult = ds.Tables[0];
ds.Tables.Clear();
return(dtResult);
}
}
}
}
catch (Exception excp)
{
m_logger.ErrorFormat("GetDataTableWithSqlParams() ERROR:{0}\r\n{1}\r\nfor query:\r\n{2}", excp, excp.Message,
excp.StackTrace, sQuery);
}
return(null);
}
public static DataTable GetDataTable(IDbConnection dcn, IDbTransaction dtn, string sFormatQuery,
params object[] args)
{
string sQuery = sFormatQuery;
try
{
EnsureConnection(dcn);
sQuery = string.Format(sFormatQuery, args);
sQuery = CheckQueryByDialect(sQuery);
using (IDbCommand cmd = SqlObjectFactory.CreateDbCommand(dcn, dtn, sQuery))
{
using (IDisposable dsp = SqlObjectFactory.CreateDbAdapter(cmd) as IDisposable)
{
IDbDataAdapter da = dsp as IDbDataAdapter;
using (DataSet ds = new DataSet())
{
da.Fill(ds);
DataTable dtResult = ds.Tables[0];
ds.Tables.Clear();
return(dtResult);
}
}
}
}
catch (Exception excp)
{
m_logger.Excp(excp, "GetDataTable() ERROR:\r\nQuery:\r\n{0}\r\n", sQuery);
}
return(new DataTable());
}
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]);
}
}
}
}
}
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);
}
}