/// <summary> /// Get data for replication since the last time it was modified /// </summary> /// <param name="tableName">Name of the table to get the replication data from</param> /// <param name="lastSyncDate">Value of the last time the data was syncronized</param> /// <param name="hasLastModifiedColumn">identifies whether of not the object contains the last modifed column</param> /// <returns></returns> public IEnumerable <DataEntity> GetReplicationDataRetrieve(string tableName, DateTime lastSyncDate, bool hasLastModifiedColumn) { //create the select query string query = "SELECT * FROM [" + tableName + "]"; //check if the last modified column name is specified if (hasLastModifiedColumn) { //round the milliseconds to the nearest second if (lastSyncDate.Millisecond > 0) { lastSyncDate = lastSyncDate.AddSeconds(1); } if (lastSyncDate.Kind != DateTimeKind.Utc) { lastSyncDate = lastSyncDate.ToUniversalTime(); } query += " WHERE [ModifiedOn] > convert(datetime, '" + lastSyncDate.ToString("s") + "')"; } //check if there is an open connection if (IsConnected) { //create the command to request data OleDbCommand oleDbCommand = new OleDbCommand(); oleDbCommand.CommandText = query; oleDbCommand.Connection = _connection; //execute the reader OleDbDataReader dataReader = oleDbCommand.ExecuteReader(); //check that the reader is not null and that rows were returned if (dataReader != null && dataReader.HasRows) { int fieldsReturned = dataReader.FieldCount; //perform reading of data while get rows from the reader while (dataReader.Read()) { //create a new data entity for each row that is being read DataEntity dataEntity = new DataEntity(tableName); //parse through each field in the row for (int i = 0; i < fieldsReturned; i++) { //get the name of the column var columnName = dataReader.GetName(i); //get the value of the column in its correct datatype var dataValue = dataReader.GetProviderSpecificValue(i); //add the column information to the data enitity dataEntity.Properties.Add(columnName, dataValue); } //yield the data entity, this allows for data to be processed while more data is being retrieved yield return(dataEntity); } } } }
/// <summary> /// Execute the query provided /// </summary> /// <param name="tableName">name of the parent table that the query is being executed for</param> /// <param name="query">query to execute</param> /// <param name="foreignKeyRelations">list of relations and foreign key values</param> /// <returns>enumerated list of data entities</returns> public IEnumerable <DataEntity> Execute(string tableName, string query, Dictionary <string, string> foreignKeyRelations) { //check if there is an open connection if (IsConnected) { //create the command to request data OleDbCommand oleDbCommand = new OleDbCommand(); oleDbCommand.CommandText = query; oleDbCommand.Connection = _connection; //execute the reader OleDbDataReader dataReader = oleDbCommand.ExecuteReader(); using (dataReader) { //check that the reader is not null and that rows were returned if (dataReader != null && dataReader.HasRows) { int fieldsReturned = dataReader.FieldCount; //perform reading of data while get rows from the reader while (dataReader.Read()) { //create a new data entity for each row that is being read DataEntity dataEntity = new DataEntity(tableName); //parse through each field in the row for (int i = 0; i < fieldsReturned; i++) { if (ParseRelationships(dataEntity, i, dataReader)) { continue; } //get the name of the column var columnName = dataReader.GetName(i); //get the value of the column in its correct datatype var dataValue = dataReader.GetProviderSpecificValue(i); //convert DB Null values to standard null values if (dataValue == DBNull.Value) { dataValue = null; } //add the column information to the data enitity dataEntity.Properties.Add(columnName, dataValue); } ValidateEntityRelations(dataEntity, foreignKeyRelations); //yield the data entity, this allows for data to be processed while more data is being retrieved yield return(dataEntity); } } } } }