public override void InsertTable(System.Data.DataTable dt)
{
using (SqlCeCommand cmd = new SqlCeCommand())
{
cmd.Connection = (SqlCeConnection)conn;
cmd.CommandText = dt.TableName;
cmd.CommandType = CommandType.TableDirect;
using (SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable | ResultSetOptions.Scrollable))
{
try
{
foreach (DataRow r in dt.Rows)
{
SqlCeUpdatableRecord record = rs.CreateRecord();
foreach (DataColumn col in dt.Columns)
{
record.SetValue(dt.Columns.IndexOf(col), r[col]);
}
rs.Insert(record);
}
}
catch (SqlCeException ex)
{
Console.WriteLine("[SqlCeWrapper.InsertTable()] Exception: \r\n" + ex.Message);
}
}
}
}
/// <summary>
/// Procedure to update the expiry date of a product
/// </summary>
/// <param name="reference">the product's reference</param>
/// <param name="expiryData">the expiry date</param>
public void UpdateExipryDate(string reference, DateTime expiryData)
{
SqlCeConnection conn = null;
try
{
string sqlconnection = ("Data Source ="
+ (System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase) + "\\InventoryDB.sdf;"));
using (conn = new SqlCeConnection(sqlconnection))
{
conn.Open();
SqlCeCommand cmd = conn.CreateCommand();
cmd.CommandText = "select * from Products where Reference ='" + reference + "'";
SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable |
ResultSetOptions.Scrollable);
SqlCeUpdatableRecord rec = rs.CreateRecord();
if (true == rs.ReadAbsolute(0))
{
rs.SetDateTime(3, expiryData);
rs.Update();
}
}
}
finally
{
conn.Close();
}
}
public void Open(SqlCeConnection sqlConn)
{
_sqlCmd = sqlConn.CreateCommand();
_sqlCmd.CommandText = String.Concat("SELECT * FROM ", _tableName);
_resultSet = _sqlCmd.ExecuteResultSet(ResultSetOptions.Updatable | ResultSetOptions.Scrollable);
_record = _resultSet.CreateRecord();
}
/// <summary>
/// Adds the study.
/// </summary>
/// <param name="receive">The receive.</param>
/// <param name="PatientId">The patient id.</param>
/// <param name="ConnectionString">The connection string.</param>
/// <param name="AETitle">The AE title.</param>
/// <param name="dataset">The dataset.</param>
/// <returns></returns>
private static string AddStudy(DateTime receive, string PatientId, string ConnectionString, string AETitle, DicomDataSet dataset)
{
string studyInstance = dataset.GetValue <string>(DicomTag.StudyInstanceUID, string.Empty);
if (string.IsNullOrEmpty(studyInstance))
{
throw new ArgumentException("Missing dicom tag", "Study Instance UID");
}
_newStudy = false;
if (!RecordExists(ConnectionString, "Studies", "StudyInstanceUID = '" + studyInstance + "'"))
{
DateTime? sd = dataset.GetValue <DateTime?>(DicomTag.StudyDate, null, GetDate);
DateTime? st = dataset.GetValue <DateTime?>(DicomTag.StudyTime, null, GetDate);
SqlCeResultSet rs = SqlCeHelper.ExecuteResultSet(ConnectionString, "Studies");
SqlCeUpdatableRecord study = rs.CreateRecord();
study.SetValue(0, studyInstance);
string sDate = GetDateString(sd, st).Trim();
if (sDate.Length > 0)
{
study.SetValue(1, sDate);
}
study.SetValue(2, dataset.GetValue <string>(DicomTag.AccessionNumber, string.Empty));
study.SetValue(3, dataset.GetValue <string>(DicomTag.StudyID, string.Empty));
study.SetValue(4, dataset.GetValue <string>(DicomTag.ReferringPhysicianName, string.Empty));
study.SetValue(5, dataset.GetValue <string>(DicomTag.StudyDescription, string.Empty));
study.SetValue(6, dataset.GetValue <string>(DicomTag.AdmittingDiagnosesDescription, string.Empty));
string age = dataset.GetValue <string>(DicomTag.PatientAge, string.Empty);
if (age != string.Empty && age.Length > 0)
{
age = age.Substring(0, 4);
}
study.SetValue(7, age);
study.SetValue(8, dataset.GetValue <double>(DicomTag.PatientSize, 0));
study.SetValue(9, dataset.GetValue <double>(DicomTag.PatientWeight, 0));
study.SetValue(10, dataset.GetValue <string>(DicomTag.Occupation, string.Empty));
study.SetValue(11, dataset.GetValue <string>(DicomTag.AdditionalPatientHistory, string.Empty));
study.SetValue(12, dataset.GetValue <string>(DicomTag.InterpretationAuthor, string.Empty));
study.SetValue(13, PatientId);
sDate = GetDateString(receive, receive).Trim();
if (sDate.Length > 0)
{
study.SetValue(14, sDate);
}
study.SetValue(15, AETitle);
rs.Insert(study);
rs.Close();
_newStudy = true;
}
return(studyInstance);
}
/// <summary>
/// Copy Other Connection Table to SQLCE
/// So The DestConn is should be SqlCeConnection
/// </summary>
/// <param name="srcConn"></param>
/// <param name="destConn">Must Sqlce Connection Due to SqlCeResultSet Object</param>
/// <param name="queryString"></param>
/// <param name="destTableName"></param>
public static void CopyTable(
DbConnection srcConn,
SqlCeConnection destConn,
string queryString,
string destTableName)
{
IDbCommand srcCommand = srcConn.CreateCommand();
srcCommand.CommandText = queryString;
if (destConn.State == ConnectionState.Closed)
{
destConn.Open();
}
SqlCeCommand destCommand = destConn.CreateCommand();
destCommand.CommandType = CommandType.TableDirect; //基于表的访问，性能更好
destCommand.CommandText = destTableName;
IDataReader srcReader = srcCommand.ExecuteReader();
SqlCeResultSet resultSet = destCommand.ExecuteResultSet(
ResultSetOptions.Sensitive | //检测对数据源所做的更改
ResultSetOptions.Scrollable | //可以向前或向后滚动
ResultSetOptions.Updatable); //允许更新数据
object[] values;
SqlCeUpdatableRecord record;
//这个方法由于前面ADO读取的列信息已经被排序，所以和数据库中真实的RECORD排序冲突。
//所以使用下面的新的方法，使用列名寻找
//while (srcReader.Read())
//{
// // 从源数据库表读取记录
// values = new object[srcReader.FieldCount];
// srcReader.GetValues(values);
// // 把记录写入到目标数据库表
// record = resultSet.CreateRecord() ;
// record.SetValues(values);
// resultSet.Insert(record);
//}
while (srcReader.Read())
{
values = new object[srcReader.FieldCount];
srcReader.GetValues(values);
record = resultSet.CreateRecord();
for (int i = 0; i < srcReader.FieldCount; i++)
{
record[srcReader.GetName(i)] = srcReader.GetValue(i);
}
resultSet.Insert(record);
}
srcReader.Close();
resultSet.Close();
}
private void CreateRecord(SqlCeResultSet resultSet, int threadId, int parentId, int childId, int hits)
{
//a lock is not needed
var row = resultSet.CreateRecord();
row["ThreadId"] = threadId;
row["ParentId"] = parentId;
row["ChildId"] = childId;
row["HitCount"] = hits;
resultSet.Insert(row, DbInsertOptions.PositionOnInsertedRow);
}
public int StoreOutboxMessage(OutgoingTwilioMessage msgout)
{
int id = 0; // ID of the inserted SQL CE record
// This results to
// INSERT INTO User (FirstName, LastName) VALUES ('test','test'),('test','test'),... ;
SqlCeCommand cmd = dbConnection.CreateCommand();
cmd.CommandText = TableOutbox;
cmd.CommandType = CommandType.TableDirect;
SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable | ResultSetOptions.Scrollable);
SqlCeUpdatableRecord rec = rs.CreateRecord();
try
{
// DEBUG
/*
* App.logger.Log("DBStore.StoreDataEntries(): Storing new entry:\n" +
* "\ttimestamp: " + qlmActivation.Timestamp +
* "\tkey: " + qlmActivation.LicenseKey +
* "\texpiration: " + qlmActivation.ExpirationDateTime);
*/
rec.SetSqlDateTime(1, DateTime.Now);
rec.SetString(2, msgout.From);
rec.SetString(3, msgout.To);
rec.SetString(4, msgout.Action);
rec.SetString(5, msgout.Method);
rec.SetString(6, msgout.Body);
rec.SetString(7, msgout.Client);
rs.Insert(rec);
// Get this inserter record ID
cmd.CommandText = "SELECT @@IDENTITY";
cmd.CommandType = CommandType.Text;
id = Convert.ToInt32(cmd.ExecuteScalar());
msgout.id = id; // Assign id to this message for further referencing from message to its DBStore record
// DEBUG
App.logger.Log("DBStore.StoreOutboxMessage() storing outbox message from " + msgout.From + " to " + msgout.To + ", ID=" + id);
}
catch (Exception ex)
{
var message = "! Error in DBStore.StoreOutboxMessage(): " + ex.Message + "\n" + ex.TargetSite;
App.logger.Log(message);
}
cmd.Dispose();
return(id);
}
static void Main(string[] args)
{
// Arguments for update
int lookFor = 1;
string value = "AC/DC";
// Arguments for insert
lookFor = Int16.MaxValue;
value = "joedotnet";
using (SqlCeConnection conn = new SqlCeConnection(@"Data Source=C:\Users\xeej\Downloads\ChinookPart2\Chinook.sdf"))
{
conn.Open();
using (SqlCeCommand cmd = new SqlCeCommand("Artist"))
{
SqlCeUpdatableRecord myRec = null;
cmd.Connection = conn;
cmd.CommandType = System.Data.CommandType.TableDirect;
cmd.IndexName = "PK_Artist";
SqlCeResultSet myResultSet = cmd.ExecuteResultSet(ResultSetOptions.Updatable | ResultSetOptions.Scrollable);
bool found = myResultSet.Seek(DbSeekOptions.FirstEqual, new object[] { lookFor });
if (found)
{
myResultSet.Read();
}
else
{
myRec = myResultSet.CreateRecord();
}
foreach (KeyValuePair <int, object> item in CommonMethodToFillRowData(value))
{
if (found)
{
myResultSet.SetValue(item.Key, item.Value);
}
else
{
myRec.SetValue(item.Key, item.Value);
}
}
if (found)
{
myResultSet.Update();
}
else
{
myResultSet.Insert(myRec);
}
}
}
}
protected override bool InternalInsert(object[] row)
{
SqlCeUpdatableRecord record = _resultSet.CreateRecord();
for (int index = 0; index < row.Length; index++)
{
record.SetValue(index, NativeToStoreValue(row[index]));
}
_resultSet.Insert(record, DbInsertOptions.KeepCurrentPosition);
return(false);
}
public SqlCeWrapper(SqlCeResultSet resultSet)
{
_resultSet = resultSet;
_found = resultSet.Seek();
if (_found)
{
resultSet.Read();
}
else
{
_newRecord = resultSet.CreateRecord();
}
}
private Dictionary <PropertyInfo, int> GetAttributeToColumnOrdinalMapping(DbContext dbContext, Type entityType,
SqlCeResultSet resultSet, Dictionary <string, string> columnMapping)
{
var ordinalMapping = new Dictionary <PropertyInfo, int>();
SqlCeUpdatableRecord rec = resultSet.CreateRecord();
foreach (var mappingEntry in columnMapping)
{
ordinalMapping.Add(entityType.GetProperty(mappingEntry.Key), rec.GetOrdinal(mappingEntry.Value));
}
return(ordinalMapping);
}
static void RecordSample(SqlCeResultSet rs, IEnumerable <Sample> data)
{
var rec = rs.CreateRecord();
foreach (var smp in data)
{
if (smp.Log)
{
rec[smp.Name] = smp.Value;
}
}
rs.Insert(rec);
}
public static void CopyTable(
IDbConnection srcConnection,
SqlCeConnection destConnection,
string queryString,
string destTableName)
{
IDbCommand srcCommand = srcConnection.CreateCommand();
srcCommand.CommandText = queryString;
SqlCeCommand destCommand = destConnection.CreateCommand();
destCommand.CommandType = CommandType.Text;
destCommand.CommandText = "delete from " + destTableName;
destCommand.ExecuteNonQuery();
destCommand.CommandType = CommandType.TableDirect; //基于表的访问，性能更好
destCommand.CommandText = destTableName;
try
{
IDataReader srcReader = srcCommand.ExecuteReader();
SqlCeResultSet resultSet = destCommand.ExecuteResultSet(
ResultSetOptions.Sensitive | //检测对数据源所做的更改
ResultSetOptions.Scrollable | //可以向前或向后滚动
ResultSetOptions.Updatable); //允许更新数据
object[] values;
SqlCeUpdatableRecord record;
while (srcReader.Read())
{
// 从源数据库表读取记录
values = new object[srcReader.FieldCount];
srcReader.GetValues(values);
// 把记录写入到目标数据库表
record = resultSet.CreateRecord();
record.SetValues(values);
resultSet.Insert(record);
}
srcReader.Close();
resultSet.Close();
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.ToString());
}
}
private static DicomCommandStatusType AddImage(DateTime receive, string sopInstance, string StudyInstanceUid, string SeriesInstanceUid,
string ConnectionString, string AETitle, DicomDataSet dataset, string ImageDirectory)
{
if (string.IsNullOrEmpty(sopInstance))
{
throw new ArgumentException("Missing dicom tag", "SOP Instance UID");
}
if (!RecordExists(ConnectionString, "Images", "SOPInstanceUID = '" + sopInstance + "'"))
{
string fileName = ImageDirectory + sopInstance + ".dcm";
SqlCeResultSet rs = SqlCeHelper.ExecuteResultSet(ConnectionString, "Images");
SqlCeUpdatableRecord image = rs.CreateRecord();
image.SetValue(0, sopInstance);
image.SetValue(1, SeriesInstanceUid);
image.SetValue(2, StudyInstanceUid);
if (HasValue(dataset, DicomTag.InstanceNumber))
{
image.SetValue(3, dataset.GetValue <int>(DicomTag.InstanceNumber, 0));
}
image.SetValue(4, fileName);
image.SetValue(5, dataset.GetValue <string>(DicomTag.TransferSyntaxUID, DicomUidType.ImplicitVRLittleEndian));
image.SetValue(6, dataset.GetValue <string>(DicomTag.SOPClassUID, string.Empty));
image.SetValue(7, dataset.GetValue <string>(DicomTag.StationName, string.Empty));
image.SetValue(8, GetDateString(DateTime.Now, DateTime.Now));
image.SetValue(9, AETitle);
rs.Insert(image);
rs.Close();
//
// store the file
//
if (!Directory.Exists(ImageDirectory))
{
Directory.CreateDirectory(ImageDirectory);
}
dataset.Save(fileName, DicomDataSetSaveFlags.None);
}
else
{
return(DicomCommandStatusType.DuplicateInstance);
}
return(DicomCommandStatusType.Success);
}
/// <summary>
/// Adds the series.
/// </summary>
/// <param name="receive">The receive.</param>
/// <param name="StudyInstanceUid">The study instance uid.</param>
/// <param name="ConnectionString">The connection string.</param>
/// <param name="AETitle">The AE title.</param>
/// <param name="dataset">The dataset.</param>
/// <returns></returns>
private static string AddSeries(DateTime receive, string StudyInstanceUid, string ConnectionString, string AETitle, DicomDataSet dataset)
{
string seriesInstance = dataset.GetValue <string>(DicomTag.SeriesInstanceUID, string.Empty);
if (string.IsNullOrEmpty(seriesInstance))
{
throw new ArgumentException("Missing dicom tag", "Series Instance UID");
}
_newSeries = false;
if (!RecordExists(ConnectionString, "Series", "SeriesInstanceUID = '" + seriesInstance + "'"))
{
DateTime? sd = dataset.GetValue <DateTime?>(DicomTag.SeriesDate, null, GetDate);
DateTime? st = dataset.GetValue <DateTime?>(DicomTag.SeriesTime, null, GetDate);
SqlCeResultSet rs = SqlCeHelper.ExecuteResultSet(ConnectionString, "Series");
SqlCeUpdatableRecord series = rs.CreateRecord();
series.SetValue(0, seriesInstance);
series.SetValue(1, dataset.GetValue <string>(DicomTag.Modality, string.Empty));
series.SetValue(2, dataset.GetValue <string>(DicomTag.SeriesNumber, string.Empty));
string seriesDate = GetDateString(sd, st);
if (seriesDate.Length > 0)
{
series.SetValue(3, seriesDate);
}
series.SetValue(4, dataset.GetValue <string>(DicomTag.SeriesDescription, string.Empty));
series.SetValue(5, dataset.GetValue <string>(DicomTag.InstitutionName, string.Empty));
seriesDate = GetDateString(receive, receive);
if (seriesDate.Length > 0)
{
series.SetValue(6, seriesDate);
}
series.SetValue(7, AETitle);
series.SetValue(8, StudyInstanceUid);
rs.Insert(series);
rs.Close();
_newSeries = true;
}
return(seriesInstance);
}
private List <object> WalkObject(Object Input, ShipmentContext context, SqlCeResultSet resultSet)
{
bool firstOccurrence;
gen.GetId(Input, out firstOccurrence);
if (!firstOccurrence)
{
return(null);
}
CachedTableInfo metadata = new CachedTableInfo(Input.GetType().Name, context, resultSet);
List <string> processedFields = new List <string>();
List <object> keys = new List <object>();
SqlCeUpdatableRecord r = resultSet.CreateRecord();
// Match the database with memory object.
for (int i = 0; i < resultSet.FieldCount; i++)
{
if (!metadata.IsFieldKey(r.GetName(i)))
{
string fieldName = resultSet.GetName(i);
PropertyInfo fi = Input.GetType().GetProperty(fieldName);
if (fi != null)
{
r[i] = fi.GetValue(Input, null);
processedFields.Add(fieldName);
}
else
{
throw new InvalidOperationException(string.Format("Field {0} was not found in the class {1}",
fi.Name, Input.GetType().Name));
}
}
}
resultSet.Insert(r, DbInsertOptions.PositionOnInsertedRow);
foreach (var item in metadata.Keys)
{
keys.Add(r[item.Name]);
}
// keys now contain assigned autoincrement fields.
return(keys);
}
public void FastInsert(string table, List <DataRowAccessor> rows)
{
_cmd.CommandText = table;
_cmd.CommandType = CommandType.TableDirect;
using (SqlCeResultSet rs = (_cmd as SqlCeCommand).ExecuteResultSet(ResultSetOptions.Updatable))
{
SqlCeUpdatableRecord rec = rs.CreateRecord();
foreach (DataRowAccessor row in rows)
{
for (int i = 0; i < row.Columns.Count; i++)
{
DataColumn col = row.Columns[i];
SetData(rec, i, col.DataType, row[col.ColumnName]);
}
rs.Insert(rec);
}
}
}
public void MegaInsertCompactDirect()
{
var con = new SqlCeConnection(@"Data Source=Database1.sdf");
con.Open();
var cmd = new SqlCeCommand("SELECT s1,s2 FROM TablSpecial", con);
SqlCeResultSet rec = cmd.ExecuteResultSet(ResultSetOptions.Updatable);
DateTime d = DateTime.Now;
for (int i = 1; i <= 10000; ++i)
{
var r = rec.CreateRecord();
r["s1"] = "new";
r["s2"] = "n";
rec.Insert(r);
}
MessageBox.Show(DateTime.Now.Subtract(d).ToString());
rec.Close();
con.Close();
}
private void SaveHistory()
{
StringBuilder q = new StringBuilder();
connection.Open();
var transaction = connection.BeginTransaction();
SqlCeCommand sqlCeCommand = connection.CreateCommand();
sqlCeCommand.CommandType = CommandType.TableDirect;
sqlCeCommand.CommandText = "HistoryTransaction";
sqlCeCommand.Transaction = transaction;
SqlCeResultSet result = sqlCeCommand.ExecuteResultSet(ResultSetOptions.Updatable);
SqlCeUpdatableRecord rec = result.CreateRecord();
foreach (DataRow item in dtHistory.Rows)
{
string encrypt = Cryptography.RSA2.Encrypt(item["Body"].ToString());
// q.Append(@"INSERT INTO [HistoryTransaction] ([IsGroup], [AccountName],
// [ServerID], [GroupName], [Body], [DateTime], [PIC]) VALUES ");
// q.AppendFormat("({0}, '{1}', '{2}', '{3}', '{4}', '{5}', '{6}')",
// 1, _xmppClient.Username, _xmppClient.XmppDomain,
// _roomJid.Bare, encrypt, DateTime.Parse(item["Body"].ToString()), item["PIC"].ToString());
rec.SetValue(1, 1);
rec.SetValue(2, _xmppClient.Username);
rec.SetValue(3, _xmppClient.XmppDomain);
rec.SetValue(4, _roomJid.Bare);
rec.SetValue(5, encrypt);
rec.SetValue(6, DateTime.Parse(item["DateTime"].ToString()));
rec.SetValue(7, item["PIC"].ToString());
result.Insert(rec);
}
result.Close();
result.Dispose();
transaction.Commit();
connection.Close();
}
private void FlushSamples(SqlCeResultSet resultSet)
{
//now to update the samples table
foreach (KeyValuePair <int, SortedList <int, int> > sampleKvp in m_samples)
{
if (sampleKvp.Value.Count == 0)
{
continue;
}
int threadOrdinal = resultSet.GetOrdinal("ThreadId");
int functionOrdinal = resultSet.GetOrdinal("FunctionId");
int hitsOrdinal = resultSet.GetOrdinal("HitCount");
foreach (KeyValuePair <int, int> threadKvp in sampleKvp.Value)
{
if (!resultSet.Seek(DbSeekOptions.FirstEqual, threadKvp.Key, sampleKvp.Key))
{
//doesn't exist in the table, we need to add it
var row = resultSet.CreateRecord();
row[threadOrdinal] = threadKvp.Key;
row[functionOrdinal] = sampleKvp.Key;
row[hitsOrdinal] = threadKvp.Value;
resultSet.Insert(row, DbInsertOptions.PositionOnInsertedRow);
}
else
{
resultSet.Read();
resultSet.SetValue(hitsOrdinal, (int)resultSet[hitsOrdinal] + threadKvp.Value);
resultSet.Update();
}
}
sampleKvp.Value.Clear();
}
}
private void FlushTimings(SqlCeResultSet resultSet)
{
foreach(KeyValuePair<int, List<long>> timingKvp in m_timings)
{
if(timingKvp.Value.Count == 0)
continue;
int funcOrdinal = resultSet.GetOrdinal("FunctionId");
int minOrdinal = resultSet.GetOrdinal("RangeMin");
int maxOrdinal = resultSet.GetOrdinal("RangeMax");
int hitsOrdinal = resultSet.GetOrdinal("HitCount");
for(int t = 0; t < timingKvp.Value.Count; ++t)
{
bool foundBin = true;
long time = timingKvp.Value[t];
if(!resultSet.Seek(DbSeekOptions.BeforeEqual, timingKvp.Key, time))
{
foundBin = false;
}
if(foundBin)
{
resultSet.Read();
var id = resultSet.GetInt32(funcOrdinal);
if(id != timingKvp.Key)
{
if(!resultSet.Read())
{
foundBin = false;
}
}
if(foundBin)
{
var min = resultSet.GetInt64(minOrdinal);
var max = resultSet.GetInt64(maxOrdinal);
if(id != timingKvp.Key || time < min || time > max)
foundBin = false;
}
}
if(foundBin)
{
//we've got a usable bin, increment and move on
var hits = resultSet.GetInt32(hitsOrdinal);
resultSet.SetInt32(hitsOrdinal, hits + 1);
resultSet.Update();
continue;
}
//didn't find a bin, create a new one for this entry
var row = resultSet.CreateRecord();
row[funcOrdinal] = timingKvp.Key;
row[minOrdinal] = time;
row[maxOrdinal] = time;
row[hitsOrdinal] = 1;
resultSet.Insert(row, DbInsertOptions.KeepCurrentPosition);
//we need to bin-merge
//start by seeking to the first record for this function
if(!resultSet.Seek(DbSeekOptions.BeforeEqual, timingKvp.Key, 0.0f))
resultSet.ReadFirst();
else
resultSet.Read();
var mergeId = resultSet.GetInt32(funcOrdinal);
if(mergeId != timingKvp.Key)
resultSet.Read();
mergeId = resultSet.GetInt32(funcOrdinal);
//we know at least one exists, cause we just inserted one
Debug.Assert(mergeId == timingKvp.Key);
//Search for the merge that produces the smallest merged bucket
long lastMin = resultSet.GetInt64(minOrdinal);
int lastHits = resultSet.GetInt32(hitsOrdinal);
bool shouldMerge = resultSet.Read();
//these store all the data about the best merge so far
long smallestRange = long.MaxValue;
long bestMin = 0;
long bestMax = 0;
int mergedHits = 0;
for(int b = 0; b < kTimingBuckets && shouldMerge; ++b)
{
long max = resultSet.GetInt64(maxOrdinal);
long range = max - lastMin;
if(range < smallestRange)
{
smallestRange = range;
bestMin = lastMin;
bestMax = max;
mergedHits = lastHits + resultSet.GetInt32(hitsOrdinal);
}
lastMin = resultSet.GetInt64(minOrdinal);
lastHits = resultSet.GetInt32(hitsOrdinal);
//if this read fails, we have insufficient buckets to bother merging
shouldMerge = resultSet.Read();
}
if(shouldMerge)
{
//seek to the first (lower) bin
resultSet.Seek(DbSeekOptions.FirstEqual, timingKvp.Key, bestMin);
resultSet.Read();
//expand this bin to include the next one
resultSet.SetInt64(maxOrdinal, bestMax);
resultSet.SetInt32(hitsOrdinal, mergedHits);
//go to the now redundant bin
resultSet.Update();
resultSet.Read();
//delete the bin
resultSet.Delete();
}
}
#if FALSE
//DEBUG ONLY HACK: display buckets
if(!resultSet.Seek(DbSeekOptions.BeforeEqual, timingKvp.Key, 0.0f))
resultSet.ReadFirst();
else
resultSet.Read();
var tempId = resultSet.GetInt32(funcOrdinal);
if(tempId != timingKvp.Key)
resultSet.Read();
Console.WriteLine("Buckets for function {0}:", timingKvp.Key);
for(int b = 0; b < kTimingBuckets; ++b)
{
long min = resultSet.GetInt64(minOrdinal);
long max = resultSet.GetInt64(maxOrdinal);
int hits = resultSet.GetInt32(hitsOrdinal);
Console.WriteLine("[{0}, {1}]: {2}", min, max, hits);
resultSet.Read();
}
#endif
}
}
private void FlushSamples(SqlCeResultSet resultSet)
{
//now to update the samples table
foreach(KeyValuePair<int, SortedList<int, int>> sampleKvp in m_samples)
{
if(sampleKvp.Value.Count == 0)
continue;
int threadOrdinal = resultSet.GetOrdinal("ThreadId");
int functionOrdinal = resultSet.GetOrdinal("FunctionId");
int hitsOrdinal = resultSet.GetOrdinal("HitCount");
foreach(KeyValuePair<int, int> threadKvp in sampleKvp.Value)
{
if(!resultSet.Seek(DbSeekOptions.FirstEqual, threadKvp.Key, sampleKvp.Key))
{
//doesn't exist in the table, we need to add it
var row = resultSet.CreateRecord();
row[threadOrdinal] = threadKvp.Key;
row[functionOrdinal] = sampleKvp.Key;
row[hitsOrdinal] = threadKvp.Value;
resultSet.Insert(row, DbInsertOptions.PositionOnInsertedRow);
}
else
{
resultSet.Read();
resultSet.SetValue(hitsOrdinal, (int) resultSet[hitsOrdinal] + threadKvp.Value);
resultSet.Update();
}
}
sampleKvp.Value.Clear();
}
}
private void DoBulkInsert(DbContext dbContext, object parent, string childPropertyName, IEnumerable <object> objectsToInsert)
{
Type entityClrType = objectsToInsert.First().GetType();
string entityName = entityClrType.Name;
//Debug.Print(entityName);
PropertyInfo entityKeyPropertyInfo = GetEntityKeyPropertyInfo(entityClrType);
SqlCeCommand cmd = connection.CreateCommand();
cmd.CommandType = System.Data.CommandType.TableDirect;
cmd.CommandText = GetTableName(entityName);
SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable | ResultSetOptions.Scrollable);
// Scalar Properties
// AuotIncrement Property
PropertyInfo auotIncrementPropInfo = null;
Dictionary <PropertyInfo, int> ordinalMapping = null;
if (scalarMappingCache.ContainsKey(entityName))
{
ordinalMapping = scalarMappingCache[entityName].Item1;
auotIncrementPropInfo = scalarMappingCache[entityName].Item2;
}
else
{
Dictionary <string, string> scalarMappingDictionary = edmx.GetScalarPropertiesMappingDictionary(entityName);
Dictionary <string, string> revisedColumnMapping = RemoveAutoIncrementColumns(GetTableName(entityName), scalarMappingDictionary);
ordinalMapping = GetAttributeToColumnOrdinalMapping(dbContext, entityClrType, rs, revisedColumnMapping);
string auotIncrementPropertyName = scalarMappingDictionary.Keys.Except(revisedColumnMapping.Keys).SingleOrDefault();
if (!string.IsNullOrEmpty(auotIncrementPropertyName))
{
auotIncrementPropInfo = entityClrType.GetProperty(auotIncrementPropertyName);
}
scalarMappingCache.Add(entityName, Tuple.Create(ordinalMapping, auotIncrementPropInfo));
}
// Navigation Properties, Lookups
if (!ordinalLookupMappingCache.ContainsKey(entityClrType))
{
ordinalLookupMappingCache.Add(entityClrType,
GetAttributeToColumnOrdinalMapping(dbContext, entityClrType, rs, edmx.GetLookupPropertiesMappingDictionary(entityClrType)));
}
Dictionary <PropertyInfo, int> ordinalLookupMapping = ordinalLookupMappingCache[entityClrType];
// Navigation Properties, Lists
if (!listPropertiesCache.ContainsKey(entityClrType))
{
listPropertiesCache.Add(entityClrType, edmx.GetListProperties(entityClrType));
}
List <NavigationProperty> listNavigationProperties = listPropertiesCache[entityClrType];
// Parent
PropertyInfo parentKeyPropertyInfo = null;
int parentColumnOrdinal = 0;
if (parent != null)
{
Type parentType = parent.GetType();
string parentEntityName = parentType.Name;
string columnName = string.Empty;
if (parentChildRelationCache.ContainsKey(Tuple.Create(parentEntityName, childPropertyName)))
{
parentKeyPropertyInfo = parentChildRelationCache[Tuple.Create(parentEntityName, childPropertyName)].Item1;
columnName = parentChildRelationCache[Tuple.Create(parentEntityName, childPropertyName)].Item2;
}
else
{
EndProperty endProperty = edmx.GetParentChildRelationEndProperty(parentEntityName, childPropertyName);
columnName = endProperty.ScalarProperties.Single().ColumnName;
string parentEntityKeyPropertyName = edmx.GetEntityKeyPropertyName(parentEntityName);
parentKeyPropertyInfo = parentType.GetProperty(parentEntityKeyPropertyName);
parentChildRelationCache.Add(Tuple.Create(parentEntityName, childPropertyName), Tuple.Create(parentKeyPropertyInfo, columnName));
}
SqlCeUpdatableRecord recParent = rs.CreateRecord();
parentColumnOrdinal = recParent.GetOrdinal(columnName);
}
foreach (var element in objectsToInsert)
{
if (IsObjectAlreadyInserted(element, entityKeyPropertyInfo))
{
continue;
}
SqlCeUpdatableRecord rec = rs.CreateRecord();
// Scalar Properties
foreach (var ordinalMappingEntry in ordinalMapping)
{
object value = ordinalMappingEntry.Key.GetValue(element, null);
rec.SetValue(ordinalMappingEntry.Value, value);
}
// Navigation Properties, Lookups
foreach (var ordinalLookupMappingEntry in ordinalLookupMapping)
{
object lookupInstance = ordinalLookupMappingEntry.Key.GetValue(element, null);
if (lookupInstance != null)
{
DoBulkInsert(dbContext, null, string.Empty, new object[] { lookupInstance });
Type lookupType = ordinalLookupMappingEntry.Key.PropertyType;
string lookupPropertyName = ordinalLookupMappingEntry.Key.Name;
var dictKeyTuple = Tuple.Create(entityName, lookupPropertyName);
if (!lookupMappingCache.ContainsKey(dictKeyTuple))
{
string lookupKeyPropertyName = edmx.GetEntityKeyPropertyName(lookupType.Name);
lookupMappingCache.Add(dictKeyTuple,
Tuple.Create(ordinalLookupMappingEntry.Value, lookupType.GetProperty(lookupKeyPropertyName)));
}
int lookupOrdinal = lookupMappingCache[dictKeyTuple].Item1;
object value = lookupMappingCache[dictKeyTuple].Item2.GetValue(lookupInstance, null);
rec.SetValue(lookupOrdinal, value);
}
}
if (parent != null)
{
rec.SetValue(parentColumnOrdinal, parentKeyPropertyInfo.GetValue(parent, null));
}
rs.Insert(rec);
if (auotIncrementPropInfo != null)
{
auotIncrementPropInfo.SetValue(element, int.Parse(commandIdentity.ExecuteScalar().ToString()), null);
}
// Navigation Properties, Lists
foreach (NavigationProperty navigationProperty in listNavigationProperties)
{
object value = entityClrType.GetProperty(navigationProperty.Name).GetValue(element, null);
if (value != null && value is ICollection)
{
IEnumerable <object> childList = (IEnumerable <object>)value;
if (childList.Count() > 0)
{
DoBulkInsert(dbContext, element, navigationProperty.Name, childList);
}
}
}
}
}
/// <summary>
/// Returns generated Id.
/// </summary>
/// <returns></returns>
public static Int32 Write_Shipment(Shipment shipment, SqlCeConnection conn)
{
Dictionary <string, Int32> idBag = new Dictionary <string, int>();
// Shipment
using (SqlCeCommand cmd = new SqlCeCommand(shipment.GetType().Name, conn))
{
cmd.CommandType = System.Data.CommandType.TableDirect;
using (SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Updatable))
{
SqlCeUpdatableRecord r = rs.CreateRecord();
/*r.SetInt32(1, shipment.SourceId);
* r.SetInt32(2, shipment.CompanyId);
* r.SetInt32(3, shipment.NumberOfPackages);
* r.SetBoolean(4, shipment.RateRuleApplied);
* if (shipment.ShipDate.HasValue)
* r.SetDateTime(5, shipment.ShipDate.Value);
* r.SetString(6, shipment.Workstation); */
rs.Insert(r, DbInsertOptions.PositionOnInsertedRow);
idBag[shipment.GetType().Name] = rs.GetInt32(0);
}
}
/*foreach (var level2 in shipment.levels) // Payload
* {
* // Level2
* using (SqlCeCommand cmd = new SqlCeCommand(level2.GetType().Name, conn))
* {
* cmd.CommandType = System.Data.CommandType.TableDirect;
* using (SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Updatable))
* {
* SqlCeUpdatableRecord r = rs.CreateRecord();
* r.SetInt32(GetOrdinal(r, level2.GetType(), shipment.GetType()), idBag[shipment.GetType().Name]); // foreign key
* r.SetString(1, level2.Value); // payload
* rs.Insert(r, DbInsertOptions.PositionOnInsertedRow);
* idBag[level2.GetType().Name] = rs.GetInt32(0);
* }
* }
*
* foreach (var level3 in level2.levels)
* {
* using (SqlCeCommand cmd = new SqlCeCommand(level3.GetType().Name, conn))
* {
* cmd.CommandType = System.Data.CommandType.TableDirect;
* using (SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Updatable))
* {
* SqlCeUpdatableRecord r = rs.CreateRecord();
* r.SetInt32(GetOrdinal(r, level3.GetType(), level2.GetType()), idBag[level2.GetType().Name]);
* r.SetString(1, level3.Value);
* rs.Insert(r, DbInsertOptions.PositionOnInsertedRow);
* idBag[level3.GetType().Name] = rs.GetInt32(0);
* }
* }
*
* foreach (var level4 in level3.levels)
* {
* using (SqlCeCommand cmd = new SqlCeCommand(level4.GetType().Name, conn))
* {
* cmd.CommandType = System.Data.CommandType.TableDirect;
* using (SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Updatable))
* {
* SqlCeUpdatableRecord r = rs.CreateRecord();
* r.SetInt32(GetOrdinal(r, level4.GetType(), level3.GetType()), idBag[level3.GetType().Name]);
* r.SetString(1, level4.Value);
* rs.Insert(r, DbInsertOptions.PositionOnInsertedRow);
* idBag[level4.GetType().Name] = rs.GetInt32(0);
* }
* }
*
* foreach (var level5 in level4.levels)
* {
* using (SqlCeCommand cmd = new SqlCeCommand(level5.GetType().Name, conn))
* {
* cmd.CommandType = System.Data.CommandType.TableDirect;
* using (SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Updatable))
* {
* SqlCeUpdatableRecord r = rs.CreateRecord();
* r.SetInt32(GetOrdinal(r, typeof(Level5), typeof(Level4)), idBag[level4.GetType().Name]);
* r.SetString(1, level5.Value);
* rs.Insert(r, DbInsertOptions.PositionOnInsertedRow);
* }
* }
* }
* }
* }
* } */
return(idBag[shipment.GetType().Name]);
}
static void RecordSample(SqlCeResultSet rs, IEnumerable<Sample> data)
{
var rec = rs.CreateRecord();
foreach (var smp in data)
{
if (smp.Log)
{
rec[smp.Name] = smp.Value;
}
}
rs.Insert(rec);
}
public int WriteToServerNew(DataTable table, DataRowState rowState, string str)
{
this.rowState = rowState;
//CheckDestination();
int totalRows = 0;
int errorCount = 0;
if (this.mappings.Count < 1)
{
if (this.conn.State != ConnectionState.Open)
{
this.conn.Open();
}
using (SqlCeCommand cmd = new SqlCeCommand(this.destination, this.conn))
{
cmd.CommandType = CommandType.TableDirect;
using (SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable))
{
int idOrdinal = this.IdentityOrdinal();
// int offset = 0;
SqlCeUpdatableRecord rec = rs.CreateRecord();
// DataTable dt = rs.GetSchemaTable();
//this.mappings.ValidateCollection(rec, table.Columns);
int fieldCount = rec.FieldCount;
//if (idOrdinal > -1)
//{
// fieldCount = fieldCount - 1;
// offset = 1;
//}
//if (table.Columns.Count - 3 != rec.FieldCount)
//{
// throw new Exception("Field counts do not match");
//}
int rowCounter = 0;
//IdInsertOn();
//string[] colNames = new string[rec.FieldCount];
//for (int y = 0; y < rec.FieldCount; y++)
//{
// colNames[y] = rec.GetName(y);
//}
int[] colIndex = new int[rec.FieldCount];
string colName;
for (int y = 1; y < rec.FieldCount; y++)
{
colName = rec.GetName(y);
if (colName.ToLower() == "rowstatus")
{
colIndex[y] = -1;
}
else
{
colIndex[y] = table.Columns[rec.GetName(y)].Ordinal;
}
}
object value;
foreach (DataRow row in table.Rows)
{
try
{
if ((row["Is_Deleted"] == DBNull.Value) || ((Convert.ToInt32(row["Is_Deleted"]) == 0) && (Convert.ToInt32(row["Is_Active"]) == 1)))
{
// Never process deleted rows
//if (row.RowState == DataRowState.Deleted)
// continue;
//// if a specific rowstate is requested
//if (this.rowState != 0)
//{
// if (row.RowState != this.rowState)
// continue;
//}
for (int y = 1; y < rec.FieldCount; y++)
{
// Let the destination assign identity values
try
{
if (colIndex[y] == -1)
{
value = Convert.ToInt16(RowStatus.Synchronized);
}
else
{
value = row[colIndex[y]];
}
if (value != null && value != DBNull.Value)
{
rec.SetValue(y, value);
}
else
{
if (keepNulls)
{
// rec.SetValue(i, DBNull.Value);
rec.SetValue(y, DBNull.Value);
}
else
{
rec.SetDefault(y);
}
}
// Fire event if needed
if (this.notifyAfter > 0 && rowCounter == this.notifyAfter)
{
FireRowsCopiedEvent(totalRows);
rowCounter = 0;
}
}
catch (Exception iEx)
{
}
}
rowCounter++;
rs.Insert(rec);
totalRows++;
}
}
catch (SqlCeException sqlex)
{
errorCount++;
if (errorCount > 50)
{
throw sqlex;
// break;
}
}
catch (Exception ex)
{
errorCount++;
if (errorCount > 50)
{
throw ex;
//break;
}
}
}
// IdInsertOff();
}
}
}
return(totalRows);
}
public bool StoreInboxMessage(IncomingTwilioMessage msgin)
{
bool retVal = true;
int id = 0; // ID of the inserted SQL CE record
// This results to
// INSERT INTO User (FirstName, LastName) VALUES ('test','test'),('test','test'),... ;
SqlCeCommand cmd = dbConnection.CreateCommand();
cmd.CommandText = TableInbox;
cmd.CommandType = CommandType.TableDirect;
SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable | ResultSetOptions.Scrollable);
SqlCeUpdatableRecord rec = rs.CreateRecord();
try
{
// DEBUG
/*
* App.logger.Log("DBStore.StoreDataEntries(): Storing new entry:\n" +
* "\ttimestamp: " + qlmActivation.Timestamp +
* "\tkey: " + qlmActivation.LicenseKey +
* "\texpiration: " + qlmActivation.ExpirationDateTime);
*/
rec.SetSqlDateTime(1, msgin.Timestamp);
rec.SetString(2, msgin.AccountSid);
rec.SetString(3, msgin.ApiVersion);
rec.SetString(4, msgin.Body);
rec.SetString(5, msgin.From);
rec.SetString(6, msgin.FromCity);
rec.SetString(7, msgin.FromCountry);
rec.SetString(8, msgin.FromState);
rec.SetString(9, msgin.FromZip);
rec.SetString(10, msgin.MessageSid);
rec.SetString(11, msgin.NumMedia);
rec.SetString(12, msgin.NumSegments);
rec.SetString(13, msgin.SmsSid);
rec.SetString(14, msgin.SmsStatus);
rec.SetString(15, msgin.ToState);
rec.SetString(16, msgin.To);
rec.SetString(17, msgin.ToCity);
rec.SetString(18, msgin.ToCountry);
rec.SetString(19, msgin.ToZip);
rec.SetString(20, msgin.MediaURLs);
rs.Insert(rec);
// Get this inserter record ID
cmd.CommandText = "SELECT @@IDENTITY";
cmd.CommandType = CommandType.Text;
id = Convert.ToInt32(cmd.ExecuteScalar());
// DEBUG
App.logger.Log("DBStore.StoreInboxMessage() storing inbox message from '" + msgin.From + "' to '" + msgin.To + "', ID=" + id);
}
catch (Exception ex)
{
var message = "! Error in DBStore.StoreInboxMessage(): " + ex.Message + "\n" + ex.TargetSite;
App.logger.Log(message);
retVal = false;
}
cmd.Dispose();
return(retVal);
}
public void WriteToServer(IDataReader reader)
{
try
{
CheckDestination();
if (this.mappings.Count < 1)
{
if (this.conn.State != ConnectionState.Open)
{
this.conn.Open();
}
using (SqlCeCommand cmd = new SqlCeCommand(this.destination, this.conn))
{
cmd.CommandType = CommandType.TableDirect;
using (SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable))
{
int idOrdinal = this.IdentityOrdinal();
int offset = 0;
SqlCeUpdatableRecord rec = rs.CreateRecord();
//this.mappings.ValidateCollection(rec, table.Columns);
int fieldCount = rec.FieldCount;
if (idOrdinal > -1)
{
fieldCount = fieldCount - 1;
offset = 1;
}
if (reader.FieldCount != rec.FieldCount)
{
throw new Exception("Field counts do not match");
}
int rowCounter = 0;
int totalRows = 0;
// IdInsertOn();
while (reader.Read())
{
for (int i = 0; i < fieldCount; i++)
{
// Let the destination assign identity values
if (!keepIdentity && i == idOrdinal)
{
continue;
}
int y = i - offset;
if (reader[y] != null && reader[y].GetType() != typeof(System.DBNull))
{
rec.SetValue(i, reader[y]);
}
else
{
if (keepNulls)
{
rec.SetValue(i, DBNull.Value);
}
else
{
rec.SetDefault(i);
}
}
// Fire event if needed
if (this.notifyAfter > 0 && rowCounter == this.notifyAfter)
{
FireRowsCopiedEvent(totalRows);
rowCounter = 0;
}
}
rowCounter++;
totalRows++;
rs.Insert(rec);
}
// IdInsertOff();
}
}
}
}
finally
{
reader.Close();
}
}
public void WriteToServer(DataTable table, DataRowState rowState)
{
this.rowState = rowState;
CheckDestination();
if (this.mappings.Count < 1)
{
if (this.conn.State != ConnectionState.Open)
{
this.conn.Open();
}
using (SqlCeCommand cmd = new SqlCeCommand(this.destination, this.conn))
{
cmd.CommandType = CommandType.TableDirect;
using (SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable))
{
int idOrdinal = this.IdentityOrdinal();
int offset = 0;
SqlCeUpdatableRecord rec = rs.CreateRecord();
//this.mappings.ValidateCollection(rec, table.Columns);
int fieldCount = rec.FieldCount;
if (idOrdinal > -1)
{
fieldCount = fieldCount - 1;
offset = 1;
}
if (table.Columns.Count != fieldCount)
{
throw new Exception("Field counts do not match");
}
int rowCounter = 0;
int totalRows = 0;
IdInsertOn();
foreach (DataRow row in table.Rows)
{
// Never process deleted rows
if (row.RowState == DataRowState.Deleted)
{
continue;
}
// if a specific rowstate is requested
if (this.rowState != 0)
{
if (row.RowState != this.rowState)
{
continue;
}
}
for (int i = 0; i < rec.FieldCount; i++)
{
// Let the destination assign identity values
if (!keepIdentity && i == idOrdinal)
{
continue;
}
int y = i - offset;
if (row[y] != null && row[y].GetType() != typeof(System.DBNull))
{
rec.SetValue(i, row[y]);
}
else
{
if (keepNulls)
{
rec.SetValue(i, DBNull.Value);
}
else
{
rec.SetDefault(i);
}
}
// Fire event if needed
if (this.notifyAfter > 0 && rowCounter == this.notifyAfter)
{
FireRowsCopiedEvent(totalRows);
rowCounter = 0;
}
}
rowCounter++;
totalRows++;
rs.Insert(rec);
}
IdInsertOff();
}
}
}
}
private void CreateRecord(SqlCeResultSet resultSet, int threadId, int parentId, int childId, int hits)
{
//a lock is not needed
var row = resultSet.CreateRecord();
row["ThreadId"] = threadId;
row["ParentId"] = parentId;
row["ChildId"] = childId;
row["HitCount"] = hits;
resultSet.Insert(row, DbInsertOptions.PositionOnInsertedRow);
}
private void FlushTimings(SqlCeResultSet resultSet)
{
foreach (KeyValuePair <int, List <long> > timingKvp in m_timings)
{
if (timingKvp.Value.Count == 0)
{
continue;
}
int funcOrdinal = resultSet.GetOrdinal("FunctionId");
int minOrdinal = resultSet.GetOrdinal("RangeMin");
int maxOrdinal = resultSet.GetOrdinal("RangeMax");
int hitsOrdinal = resultSet.GetOrdinal("HitCount");
for (int t = 0; t < timingKvp.Value.Count; ++t)
{
bool foundBin = true;
long time = timingKvp.Value[t];
if (!resultSet.Seek(DbSeekOptions.BeforeEqual, timingKvp.Key, time))
{
foundBin = false;
}
if (foundBin)
{
resultSet.Read();
var id = resultSet.GetInt32(funcOrdinal);
if (id != timingKvp.Key)
{
if (!resultSet.Read())
{
foundBin = false;
}
}
if (foundBin)
{
var min = resultSet.GetInt64(minOrdinal);
var max = resultSet.GetInt64(maxOrdinal);
if (id != timingKvp.Key || time < min || time > max)
{
foundBin = false;
}
}
}
if (foundBin)
{
//we've got a usable bin, increment and move on
var hits = resultSet.GetInt32(hitsOrdinal);
resultSet.SetInt32(hitsOrdinal, hits + 1);
resultSet.Update();
continue;
}
//didn't find a bin, create a new one for this entry
var row = resultSet.CreateRecord();
row[funcOrdinal] = timingKvp.Key;
row[minOrdinal] = time;
row[maxOrdinal] = time;
row[hitsOrdinal] = 1;
resultSet.Insert(row, DbInsertOptions.KeepCurrentPosition);
//we need to bin-merge
//start by seeking to the first record for this function
if (!resultSet.Seek(DbSeekOptions.BeforeEqual, timingKvp.Key, 0.0f))
{
resultSet.ReadFirst();
}
else
{
resultSet.Read();
}
var mergeId = resultSet.GetInt32(funcOrdinal);
if (mergeId != timingKvp.Key)
{
resultSet.Read();
}
mergeId = resultSet.GetInt32(funcOrdinal);
//we know at least one exists, cause we just inserted one
Debug.Assert(mergeId == timingKvp.Key);
//Search for the merge that produces the smallest merged bucket
long lastMin = resultSet.GetInt64(minOrdinal);
int lastHits = resultSet.GetInt32(hitsOrdinal);
bool shouldMerge = resultSet.Read();
//these store all the data about the best merge so far
long smallestRange = long.MaxValue;
long bestMin = 0;
long bestMax = 0;
int mergedHits = 0;
for (int b = 0; b < kTimingBuckets && shouldMerge; ++b)
{
long max = resultSet.GetInt64(maxOrdinal);
long range = max - lastMin;
if (range < smallestRange)
{
smallestRange = range;
bestMin = lastMin;
bestMax = max;
mergedHits = lastHits + resultSet.GetInt32(hitsOrdinal);
}
lastMin = resultSet.GetInt64(minOrdinal);
lastHits = resultSet.GetInt32(hitsOrdinal);
//if this read fails, we have insufficient buckets to bother merging
shouldMerge = resultSet.Read();
}
if (shouldMerge)
{
//seek to the first (lower) bin
resultSet.Seek(DbSeekOptions.FirstEqual, timingKvp.Key, bestMin);
resultSet.Read();
//expand this bin to include the next one
resultSet.SetInt64(maxOrdinal, bestMax);
resultSet.SetInt32(hitsOrdinal, mergedHits);
//go to the now redundant bin
resultSet.Update();
resultSet.Read();
//delete the bin
resultSet.Delete();
}
}
#if FALSE
//DEBUG ONLY HACK: display buckets
if (!resultSet.Seek(DbSeekOptions.BeforeEqual, timingKvp.Key, 0.0f))
{
resultSet.ReadFirst();
}
else
{
resultSet.Read();
}
var tempId = resultSet.GetInt32(funcOrdinal);
if (tempId != timingKvp.Key)
{
resultSet.Read();
}
Console.WriteLine("Buckets for function {0}:", timingKvp.Key);
for (int b = 0; b < kTimingBuckets; ++b)
{
long min = resultSet.GetInt64(minOrdinal);
long max = resultSet.GetInt64(maxOrdinal);
int hits = resultSet.GetInt32(hitsOrdinal);
Console.WriteLine("[{0}, {1}]: {2}", min, max, hits);
resultSet.Read();
}
#endif
}
}
private void button1_Click(object sender, RoutedEventArgs e)
{
int recordCount = 10000;
using (PopupForm p = new PopupForm())
{
p.X_NotifyStr = "Record Count";
if (p.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
int outValue;
if (!int.TryParse(p.X_Result, out outValue))
{
return;
}
recordCount = outValue;
}
else
{
return;
}
}
string testDb = DateTime.Now.Second.ToString() + "test.sdf";
string testTable = "testTable";
if (!App.MainEngineer.CreateDatabase(new LoginInfo_SSCE()
{
DbName = testDb
}))
{
return;
}
App.MainEngineer.Open(new CoreEA.LoginInfo.LoginInfo_SSCE()
{
DbName = testDb, Pwd = "", IsEncrypted = false
});
if (!App.MainEngineer.IsOpened)
{
throw new Exception("Can't Open");
}
//List<CreateTableArgs> argsList=new List<CreateTableArgs>();
//CreateTableArgs args=new CreateTableArgs();
//args.allowNulls = false;
//args.dataLength = 0;
//args.dataType="int";
//args.fieldName="id";
//args.isUnique = false;
//args.isPrimaryKey = false;
//argsList.Add(args);
BaseTableSchema tableSchame = new BaseTableSchema();
tableSchame.Columns.Add(new BaseColumnSchema()
{
ColumnName = "id", ColumnType = "int"
});
try
{
App.MainEngineer.CreateTable(tableSchame);
string sqlCmd = string.Empty;
SqlCeConnection conn = new SqlCeConnection(string.Format("Data source={0}", testDb));
SqlCeCommand cmd = new SqlCeCommand();
cmd.Connection = conn;
conn.Open();
Stopwatch watch = new Stopwatch();
watch.Start();
for (int i = 0; i < recordCount; i++)
{
sqlCmd = string.Format("insert into {0} values ({1})", testTable, i);
cmd.CommandText = sqlCmd;
cmd.ExecuteNonQuery();
}
watch.Stop();
long sqlDirectTime = watch.ElapsedMilliseconds;
watch.Reset();
watch.Start();
cmd.CommandText = string.Format("INSERT INTO {0} VALUES (?)", testTable);
cmd.Parameters.Add("@id", SqlDbType.Int);
cmd.Prepare();
for (int i = 0; i < recordCount; i++)
{
cmd.Parameters[0].Value = i;
cmd.ExecuteNonQuery();
}
watch.Stop();
long sqlParaTime = watch.ElapsedMilliseconds;
watch.Reset();
watch.Start();
cmd.CommandText = testTable;
cmd.CommandType = CommandType.TableDirect;
SqlCeResultSet st = cmd.ExecuteResultSet(ResultSetOptions.Updatable);
SqlCeUpdatableRecord rec = st.CreateRecord();
for (int i = 0; i < recordCount; i++)
{
rec.SetInt32(0, i);
st.Insert(rec);
}
watch.Stop();
long sqlceResultSetTime = watch.ElapsedMilliseconds;
//watch.Start();
//cmd.CommandText = testTable;
//cmd.CommandType = CommandType.TableDirect;
//SqlCeResultSet st = cmd.ExecuteResultSet(ResultSetOptions.Updatable);
//SqlCeUpdatableRecord rec =
//for (int i = 0; i < recordCount; i++)
//{
// rec.SetInt32(0, i);
// st.Insert(rec);
//}
//watch.Stop();
long sqlceUpdateResultSetTime = 100;// watch.ElapsedMilliseconds;
cmd.Dispose();
conn.Close();
MessageBox.Show(string.Format("Test Result is \r\nDirect sql command used {0} \r\nUse parameters used{1}\r\nUse SqlceResultSet used{2}\r\nUpdate Sqlce ResultSet{3}\r\n", sqlDirectTime, sqlParaTime, sqlceResultSetTime, sqlceUpdateResultSetTime));
}
catch (Exception ee)
{
ProcessException.DisplayErrors(ee);
}
}
public void Open(SqlCeConnection sqlConn)
{
_sqlCmd = sqlConn.CreateCommand();
_sqlCmd.CommandText = String.Concat("SELECT * FROM ", _tableName);
_resultSet = _sqlCmd.ExecuteResultSet(ResultSetOptions.Updatable | ResultSetOptions.Scrollable);
_record = _resultSet.CreateRecord();
}
// Permite realizar la integración de datos de a partir de un dataset serializado
// El dataset debe contener tablas con nombres y campos iguales a los creados en la base de
// datos
public bool IntegrarDatos(string sSerializedData, bool bUpdateCurrentRows, IEstadoCarga Estado)
{
StringReader sr = new StringReader(sSerializedData);
string sLine = null;
string[] sFields = null;
string[] sFieldsTypes = null;
string[] sValues = null;
SqlCeResultSet rs = null;
SqlCeUpdatableRecord record = null;
int I = 0;
int J = 0;
int nIndex = 0;
int nTableCount = 0;
int nRowCount = 0;
int nTotalRowCount = 0;
int nTables = 0;
int nRows = 0;
int nTotalRows = 0;
int nProgresoTabla = 0;
int nProgresoTotal = 0;
DataTable dtNucleo = null;
DataRow row = null;
object FieldValue = null;
try
{
// Se lee la liena con el número de tablas serializadas y el numero total de filas a procesar
sLine = sr.ReadLine();
nTableCount = System.Convert.ToInt32(sLine.Substring(12));
sLine = sr.ReadLine();
nTotalRowCount = System.Convert.ToInt32(sLine.Substring(15));
nProgresoTotal = 0;
nTables = 0;
nTotalRows = 0;
this.OpenConnection();
while (!Estado.Cancelado)
{
// Se obtiene el nombre y cantidad de registros de cada tabla serializada
string sTableName = null;
sLine = sr.ReadLine();
if (sLine == null)
{
break;
}
sTableName = sLine.Substring(7);
sLine = sr.ReadLine();
nRowCount = System.Convert.ToInt32(sLine.Substring(10));
if (nRowCount > 0)
{
nProgresoTabla = 0;
nRows = 0;
Estado.IniciarTabla(sTableName);
// Se revisa si es una tabla del nucleo y se actualiza
// Revisar esto
dtNucleo = null;
if (bUpdateCurrentRows)
{
// Se filtra la información del indice de llave primario, para la busqueda de
// de las filas actuales
m_dvPK.RowFilter = "TABLE_NAME = '" + sTableName + "'";
}
else
{
// Si es una tabla del nucleo si eliminan las filas actuales
if (dtNucleo != null)
{
dtNucleo.Rows.Clear();
}
}
// Se obtiene el objeto ResultSet por medio del cual se hará la actualización
// especificando el indice de llave primaria de la tabla
SqlCeCommand cmd = new SqlCeCommand();
cmd.Connection = (SqlCeConnection)this.Connection;
cmd.CommandType = CommandType.TableDirect;
cmd.CommandText = sTableName;
if (bUpdateCurrentRows)
{
cmd.IndexName = System.Convert.ToString(m_dvPK[0]["CONSTRAINT_NAME"]);
rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable | ResultSetOptions.Sensitive | ResultSetOptions.Scrollable);
}
else
{
rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable);
}
// se obtienen los nombres de los campos
sLine = sr.ReadLine();
sFields = sLine.Split('|');
// se obtienen los tipos de datos de las columnas
sLine = sr.ReadLine();
sFieldsTypes = sLine.Split('|');
// Se procesa cada fila que venga serializada en la cadena
sLine = sr.ReadLine();
bool bInsertRecord = false;
while ((sLine != null) & (!Estado.Cancelado))
{
if (sLine.Trim() == string.Empty)
{
break;
}
// Se obtienen los valores que vienen en el registro
sValues = sLine.Split('|');
// Se obtienen los valores de llave primaria del registro
// Se crea la matriz de objetos para guardar los valores de la llave primaria de cada registro
bInsertRecord = true;
if (bUpdateCurrentRows)
{
// Se obtiene la llave primaria del registro
object[] RecordKey = new object[m_dvPK.Count];
for (I = 0; I < m_dvPK.Count; I++)
{
for (J = 0; J < sFields.GetUpperBound(0); J++)
{
if (System.Convert.ToString(m_dvPK[I]["COLUMN_NAME"]).ToUpper() == sFields[J])
{
RecordKey[I] = GetColumnValue(sFieldsTypes[J], sValues[J]);
}
}
}
// se busca el registro actual y luego se actualizan los datos
// si no se encuentra se inserta un nuevo registro
if (rs.Seek(DbSeekOptions.FirstEqual, RecordKey))
{
bInsertRecord = false;
// Se obtiene la fila a modificar
rs.Read();
if (dtNucleo != null)
{
row = dtNucleo.Rows.Find(RecordKey);
}
// Se actualizan los valores de cada columna en el registro en la base de datos y si
// se esta procesando una tabla del nucleo tambien se actualiza en memoria
if (dtNucleo != null && row != null)
{
for (I = 0; I < sFields.GetUpperBound(0); I++)
{
try
{
nIndex = rs.GetOrdinal(sFields[I]);
FieldValue = GetColumnValue(rs.GetFieldType(nIndex).ToString(), sValues[I]);
rs.SetValue(nIndex, FieldValue);
nIndex = row.Table.Columns.IndexOf(sFields[I]);
if (nIndex >= 0)
{
row[nIndex] = FieldValue;
}
}
catch (Exception ex)
{
throw new InvalidOperationException("Field: " + sFields[I] + "\r\n" + "Type: " + rs.GetFieldType(nIndex).ToString() + "\r\n" + "Value: " + sValues[I] + "\r\n" + ex.Message);
}
}
}
else
{
for (I = 0; I < sFields.GetUpperBound(0); I++)
{
try
{
nIndex = rs.GetOrdinal(sFields[I]);
FieldValue = GetColumnValue(rs.GetFieldType(nIndex).ToString(), sValues[I]);
rs.SetValue(nIndex, FieldValue);
}
catch (Exception ex)
{
throw new InvalidOperationException("Field: " + sFields[I] + "\r\n" + "Type: " + rs.GetFieldType(nIndex).ToString() + "\r\n" + "Value: " + sValues[I] + "\r\n" + ex.Message);
}
}
}
rs.Update();
}
}
if (bInsertRecord)
{
// Se crea el nuevo registro
record = rs.CreateRecord();
if (dtNucleo != null)
{
row = dtNucleo.NewRow();
}
else
{
row = null;
}
// Se actualizan los valores de cada columna en el registro
if (dtNucleo != null && row != null)
{
for (I = 0; I < sFields.GetUpperBound(0); I++)
{
try
{
nIndex = rs.GetOrdinal(sFields[I]);
FieldValue = GetColumnValue(rs.GetFieldType(nIndex).ToString(), sValues[I]);
record.SetValue(nIndex, FieldValue);
nIndex = row.Table.Columns.IndexOf(sFields[I]);
if (nIndex >= 0)
{
row[nIndex] = FieldValue;
}
}
catch (Exception ex)
{
throw new InvalidOperationException("Field: " + sFields[I] + "\r\n" + "Type: " + rs.GetFieldType(nIndex).ToString() + "\r\n" + "Value: " + sValues[I] + "\r\n" + ex.Message);
}
}
}
else
{
for (I = 0; I < sFields.GetUpperBound(0); I++)
{
try
{
nIndex = rs.GetOrdinal(sFields[I]);
FieldValue = GetColumnValue(rs.GetFieldType(nIndex).ToString(), sValues[I]);
record.SetValue(nIndex, FieldValue);
}
catch (Exception ex)
{
throw new InvalidOperationException("Field: " + sFields[I] + "\r\n" + "Type: " + rs.GetFieldType(nIndex).ToString() + "\r\n" + "Value: " + sValues[I] + "\r\n" + ex.Message);
}
}
}
// Se almacena el nuevo registro
try
{
rs.Insert(record, DbInsertOptions.KeepCurrentPosition);
if (dtNucleo != null && row != null)
{
dtNucleo.Rows.Add(row);
row.AcceptChanges();
}
}
catch (Exception ex)
{
object[] values = new object[rs.FieldCount + 1];
record.GetValues(values);
throw ex;
}
}
// Se registra el avance de la tabla
nRows += 1;
nTotalRows += 1;
if ((nRows % 100) == 0 || nRows == nRowCount)
{
Estado.ProgresoTabla = System.Convert.ToInt32((nRows * 100 / nRowCount));
Estado.ProgresoTotal = System.Convert.ToInt32(nTotalRows * 100 / nTotalRowCount);
}
// Se se lee el siguiente registro
sLine = sr.ReadLine();
}
rs.Close();
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (rs != null)
{
if (!rs.IsClosed)
{
rs.Close();
rs = null;
}
}
this.CloseConnection();
sr.Close();
}
return(true);
}
public void InsertPlacementActivity(XmlResponseGetDocPlacementResult aResultGetDocPlacement)
{
using (SqlCeConnection sqlConnection = new SqlCeConnection(MyClass.ConnectionString))
{
sqlConnection.Open();
using (SqlCeTransaction sqlTran = sqlConnection.BeginTransaction())
{
try
{
// Удаляем все позиции выбранного документа
using (SqlCeCommand sqlCommand = new SqlCeCommand("DELETE FROM PlacementActivityLine", sqlConnection, sqlTran))
{
sqlCommand.CommandType = CommandType.Text;
sqlCommand.ExecuteNonQuery();
}
// Удаляем выбранный документ
using (SqlCeCommand sqlCommand = new SqlCeCommand("DELETE FROM PlacementActivityHeader", sqlConnection, sqlTran))
{
sqlCommand.CommandType = CommandType.Text;
sqlCommand.ExecuteNonQuery();
}
if (aResultGetDocPlacement.Document.Count > 0)
{
// Вставляем новую запись выбранного документа
using (SqlCeCommand sqlCommand = new SqlCeCommand("INSERT INTO PlacementActivityHeader(No) VALUES(@No)", sqlConnection, sqlTran))
{
sqlCommand.CommandType = CommandType.Text;
sqlCommand.Parameters.Add(new SqlCeParameter("@No", aResultGetDocPlacement.Document.No));
sqlCommand.ExecuteNonQuery();
}
// Вставляем новые позиции выбранного документа
using (SqlCeCommand sqlCommand = new SqlCeCommand("PlacementActivityLine", sqlConnection, sqlTran))
{
sqlCommand.CommandType = CommandType.TableDirect;
using (SqlCeResultSet rs = sqlCommand.ExecuteResultSet(ResultSetOptions.Updatable))
{
SqlCeUpdatableRecord rec = rs.CreateRecord();
Int32 id_PlacementActivityHeader = ObjectFactory.PlacementActivityHeader(sqlTran).Id_PlacementActivityHeader;
rec["Id_PlacementActivityHeader"] = id_PlacementActivityHeader;
Int32 count = aResultGetDocPlacement.Document.Count;
for (Int32 k = 0; k < count; k++)
{
rec["ItemNo"] = aResultGetDocPlacement.Document.Line(k).ItemNo;
rec["ItemNo2"] = aResultGetDocPlacement.Document.Line(k).ItemNo2;
rec["Description"] = aResultGetDocPlacement.Document.Line(k).Description;
rec["UnitOfMeasure"] = aResultGetDocPlacement.Document.Line(k).UnitOfMeasure;
rec["QtyPlacement"] = aResultGetDocPlacement.Document.Line(k).QtyPlacement;
rec["ProcessedQty"] = aResultGetDocPlacement.Document.Line(k).ProcessedQty;
rec["BinCode"] = aResultGetDocPlacement.Document.Line(k).BinCode;
rec["BinContQty"] = aResultGetDocPlacement.Document.Line(k).BinContQty;
rec["LineNo"] = aResultGetDocPlacement.Document.Line(k).LineNo;
if (aResultGetDocPlacement.Document.Line(k).QuantityInPackage == 0)
{
rec["QuantityInPackage"] = 1;
}
else
{
rec["QuantityInPackage"] = aResultGetDocPlacement.Document.Line(k).QuantityInPackage;
}
rs.Insert(rec);
}
}
}
}
sqlTran.Commit();
}
catch
{
sqlTran.Rollback();
throw;
}
}
}
}