public void TestSchemaBuilder() { //ITableProcessor tp = new NumericScrubProcessor(); //tp.Process(LaptopHostConnectionString, "RS_POLCY",null,null); OleDbSchemaProvider sp = new OleDbSchemaProvider(); Dictionary <String, OleDbColumnDefinition> schema = sp.GetSchema(LaptopHostConnectionString, "RS_POLCY"); foreach (KeyValuePair <String, OleDbColumnDefinition> kvp in schema) { TestContext.WriteLine(String.Format("{0} - {1}", kvp.Key, kvp.Value.ToString())); } TestContext.WriteLine("Here boss"); }
public void Process(string sourceConnectionString, string sourceTableName, string destinationConnectionString, string destinationTableName) { OleDbSchemaProvider schemaProvider = new OleDbSchemaProvider(); Dictionary <String, OleDbColumnDefinition> schema = schemaProvider.GetSchema(sourceConnectionString, sourceTableName); IEnumerable <OleDbColumnDefinition> numericColDefs = schema.Values.Where(colDef => colDef.Type == System.Data.OleDb.OleDbType.Numeric).ToList(); if (numericColDefs.Count() == 0) { return; } VfpConnectionStringBuilder vfpConnStrBldr = new VfpConnectionStringBuilder(sourceConnectionString); sourceConnectionString = vfpConnStrBldr.ConnectionString; int batchSize = BatchSizeProvider.GetBatchSize(sourceTableName); int recordCount = Convert.ToInt32(Helper.GetOleDbScaler(sourceConnectionString, "SELECT COUNT(*) FROM " + sourceTableName)); using (OleDbConnection sourceConnection = new OleDbConnection(sourceConnectionString)) { sourceConnection.Open(); int minRecno, maxRecno, recsProcessed; recsProcessed = 0; String comma = String.Empty; String columnList = String.Empty; StringBuilder sb = new StringBuilder().Append("SELECT "); foreach (OleDbColumnDefinition colDef in numericColDefs) { sb.Append(comma + colDef.Name); comma = ","; } sb.Append(" FROM " + sourceTableName + " WHERE "); String selectAllColsCmdStr = sb.ToString(); while (true) { minRecno = recsProcessed; maxRecno = minRecno + batchSize; // SELECT <all numeric cols> FROM <> RECNO() > 0 and RECNO() <= 25000 if (!TryRead(sourceConnectionString, String.Format(selectAllColsCmdStr + GetRecNoWhereClause(minRecno, maxRecno)))) { // if we cant read all the numerics we're good-to-go. Otherwise we'll go column-by-column foreach (OleDbColumnDefinition colDef in numericColDefs) { String recnoWhereClause = GetRecNoWhereClause(minRecno, maxRecno); String cmdStr = String.Format("SELECT {0} FROM {1} WHERE {2}", colDef.Name, sourceTableName, recnoWhereClause); if (!TryRead(sourceConnectionString, cmdStr)) { String maxVal; if (colDef.NumericScale > 0) { // 4,2 - -9.99 - 99.99 maxVal = new String('9', (int)(colDef.NumericPrecision - colDef.NumericScale)) + "." + new String('9', (int)colDef.NumericScale); } else { maxVal = new String('9', (int)(colDef.NumericPrecision)); } cmdStr = String.Format("UPDATE {0} SET {1} = 0 WHERE NOT BETWEEN({1},-{2},{2}) AND {3}", sourceTableName, colDef.Name, maxVal, recnoWhereClause); CommandStrings.Add(cmdStr); Helper.ExecuteOleDbNonQuery(sourceConnectionString, cmdStr); } } } recsProcessed = recsProcessed + batchSize; if (recsProcessed >= recordCount) { break; } } sourceConnection.Close(); } return; }