void SetRangeOfTableDirect <T>(T search, SqlCeCommand Command) where T : class, new()
{
object[] obj = new object[1];
foreach (PropertyInfo propertyOfT in PropertiesOfT)
{
object propertyValue = propertyOfT.GetValue(search, null);
if (propertyValue != null)
{
obj[0] = propertyValue;
}
}
Command.SetRange(DbRangeOptions.Match, obj, null);
}
static Upserts AddOrUpdate(Type tableType, SqlCeConnection sourceConn, SqlCeConnection destConn, IEnumerable<IntegerRange> srcDbIdRanges)
{
using (var srcCmd = new SqlCeCommand())
{
using (var destCmd = new SqlCeCommand())
{
srcCmd.CommandType = destCmd.CommandType = CommandType.TableDirect;
srcCmd.CommandText = destCmd.CommandText = TableName(tableType);
srcCmd.Connection = sourceConn;
destCmd.Connection = destConn;
srcCmd.IndexName = destCmd.IndexName = "PK_dbo." + srcCmd.CommandText;
bool wasSourceClosed;
bool wasDestClosed;
if (sourceConn.State == ConnectionState.Closed)
{
wasSourceClosed = true;
sourceConn.Open();
}
else
{
wasSourceClosed = false;
}
if (destConn.State == ConnectionState.Closed)
{
wasDestClosed = true;
destConn.Open();
}
else
{
wasDestClosed = false;
}
var returnUpdates = new List<int>();
var returnInserts = new List<int>();
foreach (var rng in srcDbIdRanges)
{
object[] startRng = new object[] { rng.Min };
object[] endRng = new object[] { rng.Max };
srcCmd.SetRange(DbRangeOptions.InclusiveStart | DbRangeOptions.InclusiveEnd, startRng, endRng);
destCmd.SetRange(DbRangeOptions.InclusiveStart | DbRangeOptions.InclusiveEnd, startRng, endRng);
using (var srcResult = srcCmd.ExecuteResultSet(ResultSetOptions.None))
{
using (var destResult = destCmd.ExecuteResultSet(ResultSetOptions.Updatable))
{
if (!srcResult.HasRows)
{
continue;
}
int idOrdinal = destResult.GetOrdinal("Id");
int updateOrdinal = destResult.GetOrdinal("RecordLastModified");
Debug.Assert(idOrdinal == srcResult.GetOrdinal("Id"), "different ordinals on Id");
Debug.Assert(updateOrdinal == srcResult.GetOrdinal("RecordLastModified"), "different ordinals on RecordLastModified");
object[] srcVals = new object[srcResult.FieldCount];
Action createRecord = new Action(() =>
{
Debug.WriteLine(tableType.Name);
srcResult.GetValues(srcVals);
var record = destResult.CreateRecord();
record.SetValues(srcVals);
destResult.Insert(record);
returnInserts.Add((int)srcVals[idOrdinal]);
});
while (destResult.Read() && srcResult.Read())
{
int srcId = srcResult.GetInt32(idOrdinal);
int destId = destResult.GetInt32(idOrdinal);
Debug.WriteLine(srcId + '\t' + destId);
if (srcId != destId)
{
while (srcId > destId)
{
LogManager.GetLogger("Mainwindow").WarnFormat("database IDs not in sync missing from data collection site Id:{0}\r\n{1}", destId,
AsSqlInsert(destResult, destCmd.CommandText)
);
if (!destResult.Read())
{
destId = 0;
break;
}
destId = destResult.GetInt32(idOrdinal);
}
while (srcId < destId)
{
//Debug.WriteLine($"srcId:{srcId} destId:{destId}");
createRecord();
if (!srcResult.Read())
{
srcId = 0;
break;
}
srcId = srcResult.GetInt32(idOrdinal);
}
if (srcId != destId) { break; }
}
DateTime srcLastUpdate = srcResult.GetDateTime(updateOrdinal);
DateTime destLastUpdate = destResult.GetDateTime(updateOrdinal);
if (srcLastUpdate == destLastUpdate)
{
continue;
}
if (srcLastUpdate < destLastUpdate)
{
LogManager.GetLogger("Mainwindow").Warn(string.Format("lastModified on destination later than src on table:{0}\r\n",
destCmd.CommandText) + AsCSVDif(srcResult, destResult, idOrdinal));
/*
LogManager.GetLogger("Mainwindow").Warn("lastModified on destination later than src: \r\n" + AsSqlUpdate(srcResult, srcCmd.CommandText)
+ "\r\ndest:\r\n"
+ AsSqlUpdate(destResult, destCmd.CommandText) );
*/
}
else // src > dest
{
srcResult.GetValues(srcVals);
destResult.SetValues(srcVals);
destResult.Update();
returnUpdates.Add(srcId);
}
}
while (srcResult.Read())
{
createRecord();
}
} // destResult
}//srcResult
}//foreach srcRng
if (wasDestClosed) { destConn.Close(); }
if (wasSourceClosed) { sourceConn.Close(); }
return new Upserts { Inserted = returnInserts, Updated = returnUpdates };
}
}
}
public void SqlCeReadAfterUpdateTest()
{
SqlCeEngine LEngine = new SqlCeEngine(@"Data Source=TestDatabase.sdf");
if (!File.Exists("TestDatabase.sdf"))
{
LEngine.CreateDatabase();
}
using (SqlCeConnection LConnection = new SqlCeConnection("Data Source=TestDatabase.sdf"))
{
LConnection.Open();
using (SqlCeCommand LCommand = LConnection.CreateCommand())
{
LCommand.CommandText = "select count(*) from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'Test'";
if ((int)LCommand.ExecuteScalar() != 0)
{
LCommand.CommandText = "drop table Test";
LCommand.ExecuteNonQuery();
}
LCommand.CommandText = "create table Test ( ID int not null, Name nvarchar(20), constraint PK_Test primary key ( ID ) )";
LCommand.ExecuteNonQuery();
LCommand.CommandText = "insert into Test ( ID, Name ) values ( 1, 'Joe' )";
LCommand.ExecuteNonQuery();
}
using (SqlCeTransaction LTransaction = LConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted))
{
try
{
using (SqlCeCommand LCommand = LConnection.CreateCommand())
{
LCommand.CommandType = System.Data.CommandType.TableDirect;
LCommand.CommandText = "Test";
LCommand.IndexName = "PK_Test";
LCommand.SetRange(DbRangeOptions.Default, null, null);
using (SqlCeResultSet LResultSet = LCommand.ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Sensitive | ResultSetOptions.Updatable))
{
if (!LResultSet.Read())
{
throw new Exception("Expected row");
}
if ((string)LResultSet[1] != "Joe")
{
throw new Exception("Expected Joe row");
}
LResultSet.SetValue(1, "Joes");
LResultSet.Update();
LResultSet.ReadFirst();
//if (!LResultSet.Read())
// throw new Exception("Expected row");
if ((string)LResultSet[1] != "Joes")
{
throw new Exception("Expected Joes row");
}
LResultSet.SetValue(1, "Joe");
LResultSet.Update();
}
}
LTransaction.Commit(CommitMode.Immediate);
}
catch
{
LTransaction.Rollback();
throw;
}
}
using (SqlCeTransaction LTransaction = LConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted))
{
}
}
}