public void PackTest()
{
var dbc = CreateTempDbc();
var builder = new VfpConnectionStringBuilder(dbc);
builder.Deleted = false;
using (var connection = new VfpConnection(builder.ConnectionString)) {
connection.Open();
using (var command = connection.CreateCommand()) {
command.CommandText = "select count(*) from temp";
Assert.AreEqual(13, Convert.ToInt32(command.ExecuteScalar()));
command.CommandText = "delete from temp where upper(allt(TableName)) == 'CUSTOMERS'";
command.ExecuteNonQuery();
command.CommandText = "select count(*) from temp";
Assert.AreEqual(13, Convert.ToInt32(command.ExecuteScalar()));
connection.Pack("temp");
command.CommandText = "select count(*) from temp";
Assert.AreEqual(12, Convert.ToInt32(command.ExecuteScalar()));
}
connection.Close();
}
}
public void Process(string sourceConnectionString, string sourceTableName, string destinationConnectionString, string destinationTableName)
{
const String recnoParm = "@recno";
destinationTableName = Helper.GetDestinationTableName(destinationTableName);
String vfpConnStr = new VfpConnectionStringBuilder(sourceConnectionString).ConnectionString;
DataTable dataTable = Helper.GetOleDbDataTable(vfpConnStr, String.Format("SELECT RECNO() AS RecNo FROM {0} WHERE DELETED()", sourceTableName));
if (dataTable.Rows.Count != 0)
{
using (SqlConnection destinationConnection = new SqlConnection(destinationConnectionString))
{
destinationConnection.Open();
foreach (DataRow row in dataTable.Rows)
{
String cmdStr = String.Format("UPDATE {0} SET {1} = 1 WHERE {2} = {3}", destinationTableName, Constants.DILayer.DeletedColumnName, Constants.DILayer.RecnoColumnName, recnoParm);
using (SqlCommand cmd = new SqlCommand(cmdStr, destinationConnection))
{
cmd.Parameters.AddWithValue(recnoParm, row[0].ToString());
cmd.ExecuteNonQuery();
}
}
destinationConnection.Close();
}
}
}
public ConversionActionProcessor()
{
String hostConnStr = Helper.GetConnectionString(Constants.ConnectionNames.Host);
VfpConnectionStringBuilder vfpConnStrBldr = new VfpConnectionStringBuilder(hostConnStr);
HostPath = vfpConnStrBldr.DataSource;
}
public void ConnectionStringTest()
{
var vfpConnectionStringBuilder = new VfpConnectionStringBuilder();
Assert.AreEqual(string.Empty, vfpConnectionStringBuilder.ConnectionString, false);
vfpConnectionStringBuilder.ConnectionString = "Data Source=Northwind.dbc";
Assert.AreEqual("Data Source=Northwind.dbc", vfpConnectionStringBuilder.ConnectionString, true);
Assert.AreEqual("Northwind.dbc", vfpConnectionStringBuilder.DataSource, false);
Assert.AreEqual("Northwind.dbc", vfpConnectionStringBuilder.Database, false);
vfpConnectionStringBuilder.ConnectionString = String.Empty;
Assert.AreEqual(string.Empty, vfpConnectionStringBuilder.ConnectionString, true);
Assert.AreEqual(string.Empty, vfpConnectionStringBuilder.DataSource, false);
Assert.AreEqual(string.Empty, vfpConnectionStringBuilder.Database, false);
vfpConnectionStringBuilder.ConnectionString = "Provider=FoxPro";
Assert.AreEqual("Provider=FoxPro", vfpConnectionStringBuilder.ConnectionString, true);
Assert.AreEqual(string.Empty, vfpConnectionStringBuilder.DataSource, false);
Assert.AreEqual(string.Empty, vfpConnectionStringBuilder.Database, false);
vfpConnectionStringBuilder.DataSource = "Test.dbc";
Assert.AreEqual(vfpConnectionStringBuilder.Database, vfpConnectionStringBuilder.DataSource);
vfpConnectionStringBuilder.DataSource = "Test2.dbc";
Assert.AreEqual(vfpConnectionStringBuilder.DataSource, vfpConnectionStringBuilder.Database);
}
public void Null_ShouldSetNull(bool @null)
{
var builder = new VfpConnectionStringBuilder {
Null = @null
};
Assert.Equal(@null, builder.Null);
}
public void CollatingSequence_ShouldSetCollatingSequence(Collation collatingSequence)
{
var builder = new VfpConnectionStringBuilder {
CollatingSequence = collatingSequence
};
Assert.Equal(collatingSequence, builder.CollatingSequence);
}
public void Ansi_ShouldSetAnsi(bool ansi)
{
var builder = new VfpConnectionStringBuilder {
Ansi = ansi
};
Assert.Equal(ansi, builder.Ansi);
}
public void Deleted_ShouldSetDeleted(bool deleted)
{
var builder = new VfpConnectionStringBuilder {
Deleted = deleted
};
Assert.Equal(deleted, builder.Deleted);
}
public void DbcOnlyTest()
{
var vfpConnectionStringBuilder = new VfpConnectionStringBuilder("Northwind.dbc");
Assert.AreEqual("Data Source=Northwind.dbc", vfpConnectionStringBuilder.ConnectionString, true);
Assert.AreEqual("Northwind.dbc", vfpConnectionStringBuilder.DataSource, false);
Assert.AreEqual("Northwind.dbc", vfpConnectionStringBuilder.Database, false);
}
public void FreeTableTest()
{
var vfpConnectionStringBuilder = new VfpConnectionStringBuilder(@"c:\");
Assert.AreEqual(@"Data Source=c:\", vfpConnectionStringBuilder.ConnectionString, true);
Assert.AreEqual(@"c:\", vfpConnectionStringBuilder.DataSource, false);
Assert.AreEqual(string.Empty, vfpConnectionStringBuilder.Database, false);
}
public void ZeroArgumentContructorTest()
{
var vfpConnectionStringBuilder = new VfpConnectionStringBuilder();
Assert.AreEqual(string.Empty, vfpConnectionStringBuilder.ConnectionString, true);
Assert.AreEqual(string.Empty, vfpConnectionStringBuilder.DataSource, false);
Assert.AreEqual(string.Empty, vfpConnectionStringBuilder.Database, false);
Assert.AreEqual(0, vfpConnectionStringBuilder.Count);
}
public void VfpOleDbConnectionStringTest()
{
var connectionString = "provider=vfpoledb;data source=northwind.dbc;deleted=false;ansi=true;";
var vfpConnectionStringBuilder = new VfpConnectionStringBuilder(connectionString);
Assert.AreEqual("provider=vfpoledb;data source=northwind.dbc;deleted=false;ansi=true", vfpConnectionStringBuilder.ConnectionString, true);
Assert.AreEqual("northwind.dbc", vfpConnectionStringBuilder.DataSource, false);
Assert.AreEqual("northwind.dbc", vfpConnectionStringBuilder.Database, false);
Assert.AreEqual(4, vfpConnectionStringBuilder.Count);
}
public void Constructor_Default_ShouldHaveEmptyProperties()
{
var builder = new VfpConnectionStringBuilder();
Assert.Equal(Empty, builder.ConnectionString, true);
Assert.Equal(Empty, builder.DataSource);
Assert.Equal(Empty, builder.Database);
Assert.False(builder.IsDbc);
Assert.Equal(Empty, builder.Provider);
}
public void Constructor_WithConnectionString_ShouldSetProperties(ConnectionStringsTestCase testCase)
{
var builder = new VfpConnectionStringBuilder(testCase.ConnectionString);
Assert.Equal(testCase.ExpectedConnectionString, builder.ConnectionString, true);
Assert.Equal(testCase.DataSource, builder.DataSource);
Assert.Equal(testCase.Database, builder.Database);
Assert.Equal(testCase.IsDbc, builder.IsDbc);
Assert.Equal(testCase.Provider, builder.Provider);
}
public static void Using(string connectionString, Action <SchemaManager> action)
{
var builder = new VfpConnectionStringBuilder(connectionString);
builder.Ansi = true;
var connection = new VfpConnection(builder.ConnectionString);
using (var manager = new SchemaManager(connection, new DataTableDbcCreator())) {
action(manager);
};
}
protected override IVsDataReader SelectObjects(string typeName, object[] restrictions, string[] properties, object[] parameters)
{
VfpClientTracing.Tracer.TraceVerbose(String.Format("DataSourceSelector.SelectObjects typeName={0}", typeName));
var dataTable = new DataTable();
dataTable.Columns.Add("Name");
var connectionStringBuilder = new VfpConnectionStringBuilder(Site.DisplayConnectionString);
dataTable.Rows.Add(connectionStringBuilder.DataSource);
return(new AdoDotNetReader(dataTable.CreateDataReader()));
}
public void VfpConnectionStringBuilderPropertiesTest()
{
var vfpConnectionStringBuilder = new VfpConnectionStringBuilder("northwind.dbc")
{
Ansi = true, Deleted = false, CollatingSequence = Collation.GENERAL
};
Assert.AreEqual("data source=northwind.dbc;Ansi=True;Deleted=False;Collating Sequence=GENERAL", vfpConnectionStringBuilder.ConnectionString, true);
Assert.AreEqual("northwind.dbc", vfpConnectionStringBuilder.DataSource, false);
Assert.AreEqual("northwind.dbc", vfpConnectionStringBuilder.Database, false);
Assert.AreEqual(true, vfpConnectionStringBuilder.Ansi);
Assert.AreEqual(false, vfpConnectionStringBuilder.Deleted);
Assert.AreEqual(Collation.GENERAL, vfpConnectionStringBuilder.CollatingSequence);
Assert.AreEqual(4, vfpConnectionStringBuilder.Count);
}
private void InsertEmptyValues(bool allowNulls)
{
using (var context = GetAllTypesDataContext()) {
var item = new AllTypesTable();
var builder = new VfpConnectionStringBuilder(context.Database.Connection.ConnectionString);
builder.Null = allowNulls;
context.Database.Connection.ConnectionString = builder.ConnectionString;
item.Guid = Guid.NewGuid();
context.AllTypes.Add(item);
context.SaveChanges();
}
}
public void Process(string sourceConnectionString, string sourceTableName, string destinationConnectionString, string destinationTableName)
{
destinationTableName = Helper.GetDestinationTableName(destinationTableName);
VfpConnectionStringBuilder vfpConnStrBldr = new VfpConnectionStringBuilder(sourceConnectionString);
String vfpFileName = Path.Combine(vfpConnStrBldr.DataSource, Path.ChangeExtension(sourceTableName, "DBF"));
SqlConnectionStringBuilder sqlConStrBldr = new SqlConnectionStringBuilder(destinationConnectionString);
Ivfptosqlbulkcopy com = new vfptosqlbulkcopyClass();
String result = com.UploadMemos(vfpFileName, destinationTableName, sqlConStrBldr.DataSource, sqlConStrBldr.InitialCatalog);
if (!String.IsNullOrEmpty(result))
{
throw new Exception(result);
}
}
public void OneStringArgumentConstructorTest()
{
var vfpConnectionStringBuilder = new VfpConnectionStringBuilder(null);
Assert.AreEqual(string.Empty, vfpConnectionStringBuilder.ConnectionString, true);
Assert.AreEqual(string.Empty, vfpConnectionStringBuilder.DataSource, false);
Assert.AreEqual(string.Empty, vfpConnectionStringBuilder.Database, false);
vfpConnectionStringBuilder = new VfpConnectionStringBuilder(String.Empty);
Assert.AreEqual(string.Empty, vfpConnectionStringBuilder.ConnectionString, true);
Assert.AreEqual(string.Empty, vfpConnectionStringBuilder.DataSource, false);
Assert.AreEqual(string.Empty, vfpConnectionStringBuilder.Database, false);
vfpConnectionStringBuilder = new VfpConnectionStringBuilder("Data Source=Northwind.dbc");
Assert.AreEqual("Data Source=Northwind.dbc", vfpConnectionStringBuilder.ConnectionString, true);
Assert.AreEqual("Northwind.dbc", vfpConnectionStringBuilder.DataSource, false);
Assert.AreEqual("Northwind.dbc", vfpConnectionStringBuilder.Database, false);
}
private AllTypesTable Insert_WithEmptyValues(bool allowNulls)
{
var item = new AllTypesTable();
Execute(context => {
var connection = context.Database.GetDbConnection();
var builder = new VfpConnectionStringBuilder(connection.ConnectionString)
{
Null = allowNulls
};
connection.ConnectionString = builder.ConnectionString;
item.Guid = Guid.NewGuid();
context.AllTypes.Add(item);
context.SaveChanges();
});
return(item);
}
public void TestEssexInWMailUpload()
{
VfpConnectionStringBuilder vfpConnStrBldr = new VfpConnectionStringBuilder(VfpConnectionString);
vfpConnStrBldr.DataSource = @"D:\Essex\Hostdema\";
String vfpConnStr = vfpConnStrBldr.ConnectionString;
String sqlConnStr = "Data Source = (local); Initial Catalog = Essex_22_000211; Integrated Security = True";
const String tableName = "IN_WMAIL";
Helper.ExecuteSqlNonQuery(sqlConnStr, "DELETE FROM " + tableName);
string expectedRecordCountCmdStr = "SELECT COUNT(*) FROM " + tableName;
int expectedRecordCount = Convert.ToInt32(Helper.GetOleDbScaler(vfpConnStr, expectedRecordCountCmdStr));
TableUploader tu = new TableUploader();
tu.Process(vfpConnStr, tableName, sqlConnStr, tableName);
int actualRecordCount = Convert.ToInt32(Helper.GetSqlScaler(sqlConnStr, expectedRecordCountCmdStr));
Assert.AreEqual(expectedRecordCount, actualRecordCount);
}
public void TestConnStringHasDeleted()
{
IList <String> clauses = new List <String>()
{
String.Format("Provider={0}", Constants.ConnectionStringTerms.VfpOleDbProvider),
@"Data Source=D:\VfpToSql\vhost",
};
String delim = ";";
StringBuilder sb = new StringBuilder();
foreach (String clause in clauses)
{
sb.Append(delim + clause);
delim = ";";
}
String connStr = sb.ToString();
OleDbConnectionStringBuilder oleDbBldr = new OleDbConnectionStringBuilder(connStr);
String ds = oleDbBldr.DataSource;
Assert.IsFalse(String.IsNullOrEmpty(ds));
VfpConnectionStringBuilder vfpBldr = new VfpConnectionStringBuilder(connStr);
String actual = vfpBldr.ConnectionString.Replace(" ", String.Empty);
clauses.Add("DELETED = False");
foreach (String clause in clauses)
{
Assert.IsTrue(actual.ToUpper().Contains(clause.ToUpper().Replace(" ", String.Empty)));
}
Assert.IsTrue(actual.ToUpper().Replace(" ", String.Empty).Contains(CollatingSequenceClause));
TestContext.WriteLine(actual);
}
public void Deleted_ShouldDefaultToTrue()
{
var builder = new VfpConnectionStringBuilder();
Assert.True(builder.Deleted);
}
public void Ansi_ShouldDefaultToTrue()
{
var builder = new VfpConnectionStringBuilder();
Assert.True(builder.Ansi);
}
public void CollatingSequence_ShouldDefaultToInput()
{
var builder = new VfpConnectionStringBuilder();
Assert.Equal(Collation.MACHINE, builder.CollatingSequence);
}
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 override string GetConnectionDescription(IConnectionInfo connectionInfo)
{
var builder = new VfpConnectionStringBuilder(connectionInfo.DatabaseInfo.CustomCxString);
return(builder.DataSource);
}
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;
}
