public string GetCommandString(string connectionString, string tableName)
{
StringBuilder sb = new StringBuilder();
sb.Append("select ");
Dictionary <String, OleDbColumnDefinition> schema = new OleDbSchemaProvider().GetSchema(connectionString, tableName);
String comma = String.Empty;
foreach (KeyValuePair <String, OleDbColumnDefinition> kvp in schema)
{
sb.Append(comma + kvp.Value.Name);
comma = ", ";
// When I had the IIF(EMPTY(msClDate),null,msClDate) as msClDate
// VFP blew chow with Error # 1890
// And you get the same thing in the VFP Dev Edition if you issue
// SELECT IIF(EMPTY(mscldate),null,mscldate) FROM in_msg using
// the Laptop
//if (kvp.Value.IsDate)
//{
// String s = String.Format("IIF(EMPTY({0}),null,{0}) as {0}", kvp.Value.Name);
// sb.Append(s);
//}
//else
//{
// sb.Append(kvp.Value.Name);
//}
}
/*
* The saga of deleted().... 1st I tried the following
*
* sb.Append(comma + "IIF(DELETED(),1,0) AS " + Constants.DILayer.DeletedColumnName);
*
* A problem with the above is the the column was preceived as having a Decimal type - which
* makes sense. While the values where either 0 or 1 nothing coerced the type to boolean
*
* So I tried the following ....
*
* sb.Append(comma + " DELETED() AS " + Constants.DILayer.DeletedColumnName);
*
* This comand resulted in a "correct" DataTable in that it had an SqlDeleted column
* of type boolean
*
* However, it still didn't work. The following is the bulk insert command and it knows nothing about SqlDeleted c
*
* insert bulk IN_MSG ([MSNUMB] Char(6) COLLATE Latin1_General_BIN, [MSTYPE] Char(1) COLLATE Latin1_General_BIN, [MSGNUM] Char(6) COLLATE Latin1_General_BIN,
* [MSRNUM] Char(6) COLLATE Latin1_General_BIN, [MSDATE] Date, [MSTIME] Char(5) COLLATE Latin1_General_BIN, [MSUSER] Char(2) COLLATE Latin1_General_BIN, [MSCLDATE] Date,
* [MSCLTIME] Char(5) COLLATE Latin1_General_BIN, [MSCLUSER] Char(2) COLLATE Latin1_General_BIN, [MSPRINT] Char(1) COLLATE Latin1_General_BIN,
* [MSTO] Char(3) COLLATE Latin1_General_BIN, [MSFLAG] Char(1) COLLATE Latin1_General_BIN, [MSNOTTYP] Char(1) COLLATE Latin1_General_BIN, [MSBEGIN] Date,
* [MSDAY] Decimal(2,0), [MSBEGTIME] Char(5) COLLATE Latin1_General_BIN, [MSENDTIME] Char(5) COLLATE Latin1_General_BIN,
* [MSTXT] Text COLLATE SQL_Latin1_General_CP1_CI_AS, [MSPRI] Decimal(2,0), [MSSTAT] Char(1) COLLATE Latin1_General_BIN, [MSIPADDR] Char(15) COLLATE Latin1_General_BIN, [MSAUTOCLS] Bit)
*
*/
sb.Append(" from " + tableName);
return(sb.ToString());
}
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 TestBasicCommandStringProvider()
{
const string tableName = "IN_MSG";
ICommandStringProvider csp = new SelectCommandStringProvider();
String actual = csp.GetCommandString(VfpConnectionString, tableName);
String upperActual = actual.ToUpper();
Assert.IsTrue(upperActual.StartsWith("SELECT "));
Assert.IsTrue(upperActual.Replace(" ", String.Empty).EndsWith("FROM" + tableName));
Dictionary <String, OleDbColumnDefinition> schema = new OleDbSchemaProvider().GetSchema(VfpConnectionString, tableName);
foreach (KeyValuePair <String, OleDbColumnDefinition> kvp in schema)
{
Assert.IsTrue(upperActual.Contains(kvp.Value.Name + ",") || upperActual.Contains(kvp.Value.Name + " "));
}
// Can we run it? Turns out - we can't. VFP raises Error # 1890
// if you issued SELECT IIF(EMPTY(mscldate),null,mscldate) FROM in_msg
// on the Laptop
DataTable dt = Helper.GetOleDbDataTable(VfpConnectionString, actual);
TestContext.WriteLine(actual);
}
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;
}
