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(); } } } }
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); }
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); } }