public int GetBatchSize(string tableName)
{
int batchSize = ditableBatchSizeProvider.GetBatchSize(tableName);
if (batchSize == 0)
{
batchSize = defaultBatchSizeProvider.GetBatchSize(tableName);
}
return(batchSize);
}
public void Process(String sourceConnectionString, String sourceTableName, String destinationConnectionString, String destinationTableName)
{
destinationTableName = Helper.GetDestinationTableName(destinationTableName);
int recordCount = Convert.ToInt32(Helper.GetOleDbScaler(sourceConnectionString, "SELECT COUNT(*) FROM " + sourceTableName));
BatchSize = BatchSizeProvider.GetBatchSize(sourceTableName);
DataTable dataTable = null;
using (SqlConnection destinationConnection = new SqlConnection(destinationConnectionString))
{
destinationConnection.Open();
#region Upload
VfpConnectionStringBuilder vfpConnStrBldr = new VfpConnectionStringBuilder(sourceConnectionString);
sourceConnectionString = vfpConnStrBldr.ConnectionString;
String vfpFolderName = vfpConnStrBldr.DataSource;
using (OleDbConnection sourceConnection = new OleDbConnection(sourceConnectionString))
{
sourceConnection.Open();
int minRecno, maxRecno, recsUploaded;
recsUploaded = 0;
while (true)
{
minRecno = recsUploaded;
maxRecno = minRecno + BatchSize;
// Pull rows from VFP
String cmdStr = String.Format("SELECT * FROM {0} WHERE RECNO() > {1} AND RECNO() <= {2}", sourceTableName, Convert.ToString(minRecno), Convert.ToString(maxRecno));
dataTable = Helper.GetOleDbDataTable(sourceConnectionString, cmdStr);
recsUploaded = recsUploaded + dataTable.Rows.Count;
// Push rows to SQL
// D # 19055 As instantiated, our SqlBulkCopy won't fire triggers. And doing what we need to do
// isn't as simple as firing triggers - because as part of the overall upload we TRUNCATE TABLE <> -
// which does not fire the DELETE trigger...
using (SqlBulkCopy copier = new SqlBulkCopy(destinationConnection))
{
DataTableReader dtReader = dataTable.CreateDataReader();
copier.BulkCopyTimeout = 0;
copier.DestinationTableName = destinationTableName;
copier.WriteToServer(dataTable);
dtReader.Close();
}
if (recsUploaded >= recordCount)
{
break;
}
}
sourceConnection.Close();
}
#endregion
destinationConnection.Close();
}
}
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;
}
