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); }
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> /// 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); }
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); }
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(); }
/// <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"); } 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); study.SetValue(1, GetDateString(sd, st)); 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); study.SetValue(14, GetDateString(receive, receive)); study.SetValue(15, AETitle); rs.Insert(study); rs.Close(); } return(studyInstance); }
// 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 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 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 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 fieldValueSet(Dictionary<string, int> fieldIndexByFieldNameDic, SqlCeUpdatableRecord rec, string DbField, object value) { rec.SetValue(fieldIndexByFieldNameDic[DbField.ToLower()], (object) value); }
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); } } } } }
private void WriteToServer(ISqlCeBulkCopyInsertAdapter adapter) { CheckDestination(); if (conn.State != ConnectionState.Open) { conn.Open(); } List <KeyValuePair <int, int> > mappings = null; if (Mappings.Count > 0) { //mapping are set, and should be validated mappings = Mappings.ValidateCollection(conn, adapter, options, destination); } else { //create default column mappings mappings = SqlCeBulkCopyColumnMappingCollection.Create(conn, adapter, options, destination); } SqlCeTransaction localTrans = trans ?? conn.BeginTransaction(); using (SqlCeCommand cmd = new SqlCeCommand(destination, conn, localTrans)) { cmd.CommandType = CommandType.TableDirect; using (SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable)) { int idOrdinal = SqlCeBulkCopyTableHelpers.IdentityOrdinal(conn, options, destination); SqlCeUpdatableRecord rec = rs.CreateRecord(); int rowCounter = 0; int totalRows = 0; IdInsertOn(); //Converting to an array removed the perf issue of a list and foreach statement. var cm = mappings.ToArray(); while (adapter.Read()) { if (adapter.SkipRow()) { continue; } for (int i = 0; i < cm.Length; i++) { //caching the values this way do not cause a perf issue. var sourceIndex = cm[i].Key; var destIndex = cm[i].Value; // Let the destination assign identity values if (!keepIdentity && destIndex == idOrdinal) { continue; } //determine if we should ever allow this in the map. if (sourceIndex < 0) { continue; } var value = sourceIndex > -1 ? adapter.Get(sourceIndex) : null; if (value != null && value.GetType() != DbNullType) { rec.SetValue(destIndex, value); } else { //we can't write to an auto number column so continue if (keepNulls && destIndex == idOrdinal) { continue; } if (keepNulls) { rec.SetValue(destIndex, DBNull.Value); } else { rec.SetDefault(destIndex); } } // Fire event if needed if (notifyAfter > 0 && rowCounter == notifyAfter) { FireRowsCopiedEvent(totalRows); rowCounter = 0; } } rowCounter++; totalRows++; rs.Insert(rec); } IdInsertOff(); } } //if we have our own transaction, we will commit it if (trans == null) { localTrans.Commit(CommitMode.Immediate); } }