private static void VfpClientVersion()
{
using (var connection = new VfpConnection(ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString)) {
using (var command = connection.CreateCommand()) {
command.CommandText = @"
SELECT CAST('Supplier' as v(254)) Type, CompanyName Name
FROM Suppliers WHERE UPPER(ALLTRIM(CompanyName)) like @Name OR SupplierId = @Id
UNION SELECT CAST('Shipper' as v(254)), CompanyName
FROM Shippers WHERE UPPER(ALLTRIM(CompanyName)) like @Name OR ShipperId = @Id
UNION SELECT CAST('Customer' as v(254)), CompanyName
FROM Customers WHERE UPPER(ALLTRIM(CompanyName)) like @Name
UNION SELECT CAST('Product' as v(254)), ProductName
FROM Products WHERE UPPER(ALLTRIM(ProductName)) like @Name OR ProductId = @Id
UNION SELECT CAST('Category' as v(254)), CategoryName
FROM Categories WHERE UPPER(ALLTRIM(CategoryName)) like @Name OR CategoryId = @Id
UNION SELECT CAST('Employee' as v(254)), ALLTRIM(FirstName) + ' ' + ALLTRIM(LastName)
FROM Employees WHERE UPPER(ALLTRIM(FirstName)) like @Name OR UPPER(ALLTRIM(LastName)) like @Name OR EmployeeId = @Id
Order by 2
";
command.Parameters.AddWithValue("@Name", "%AB%");
command.Parameters.AddWithValue("@Id", 1);
var dataAdapter = new VfpDataAdapter(command);
var dataTable = new DataTable();
dataAdapter.Fill(dataTable);
}
}
}
public void VfpDataAdapterTests_UpdateTest()
{
using (var connection = this.fixture.CreateConnection()) {
var transaction = connection.BeginTransaction();
using (var command = connection.CreateCommand()) {
command.Transaction = transaction;
command.CommandText = "select * from customers";
var dataAdapter = new VfpDataAdapter(command);
new VfpCommandBuilder(dataAdapter);
var dataTable = new DataTable();
dataAdapter.Fill(dataTable);
dataTable.Rows[0][0] = "y";
var vfpCode = command.ToVfpCode();
var parameters = command.Parameters.Cast <VfpParameter>().ToList();
dataAdapter.Update(dataTable);
var dataTable2 = new DataTable();
dataAdapter.Fill(dataTable2);
Assert.Equal("y", dataTable.Rows[0][0]);
}
transaction.Rollback();
}
}
public void NamedParameterTest()
{
using (var connection = GetConnection()) {
using (var command = connection.CreateCommand()) {
command.CommandText = @"
SELECT CAST('Supplier' as v(254)) Type, CompanyName Name
FROM suppliers WHERE UPPER(ALLTRIM(CompanyName)) like @Name
UNION SELECT CAST('Shipper' as v(254)) Entity, CompanyName
FROM Shippers WHERE UPPER(ALLTRIM(CompanyName)) like @Name
UNION SELECT CAST('Customer' as v(254)) Entity, CompanyName
FROM Customers WHERE UPPER(ALLTRIM(CompanyName)) like @Name
UNION SELECT CAST('Product' as v(254)) Entity, ProductName
FROM Products WHERE UPPER(ALLTRIM(ProductName)) like @Name
UNION SELECT CAST('Category' as v(254)) Entity, CategoryName
FROM Categories WHERE UPPER(ALLTRIM(CategoryName)) like @Name
UNION SELECT CAST('Employee' as v(254)) Entity, ALLTRIM(FirstName) + ' ' + ALLTRIM(LastName)
FROM Employees WHERE UPPER(ALLTRIM(FirstName)) like @Name OR UPPER(ALLTRIM(LastName)) like @Name
Order by 2";
command.Parameters.AddWithValue("Name", "%AB%");
var dataAdapter = new VfpDataAdapter(command);
var dataTable = new DataTable();
dataAdapter.Fill(dataTable);
Assert.AreEqual(14, dataTable.Rows.Count);
}
}
}
private static void UpdateAutoIncAndCandidate(VfpConnection connection, DataTable schema)
{
var tableNames = schema.AsEnumerable().Select(x => x.Field <string>(Index.TableName)).Distinct();
var inserts = string.Join(Environment.NewLine, tableNames.Select(x => string.Format("INSERT INTO curTables VALUES('{0}')", x)).ToArray());
var vfpCode = string.Format(Resources.IndexSchemaAutoIncCandidate, inserts);
schema.AsEnumerable().ToList().ForEach(row => {
row[Index.AutoInc] = false;
row[Index.Candidate] = false;
});
connection.ExecuteScript(vfpCode);
using (var command = connection.CreateCommand()) {
command.CommandType = CommandType.Text;
command.CommandText = "SELECT * FROM curOutput";
#if DEBUG
var dt = new DataTable();
var da = new VfpDataAdapter(command);
da.Fill(dt);
#endif
using (var reader = command.ExecuteReader()) {
while (reader.Read())
{
var tableName = reader.GetString(0);
var fieldName = reader.GetString(1);
var isAutoInc = !string.IsNullOrEmpty(fieldName);
var indexName = reader.GetString(2);
var rows = schema.AsEnumerable().Where(x => x.Field <string>(Index.TableName) == tableName);
if (string.IsNullOrEmpty(fieldName))
{
rows = rows.Where(x => x.Field <string>(Index.IndexName).ToUpper() == indexName);
}
else
{
rows = rows.Where(x => x.Field <string>(Index.FieldName).ToUpper() == fieldName);
}
rows.ToList().ForEach(row => {
row[Index.AutoInc] = isAutoInc;
row[Index.Candidate] = !isAutoInc;
});
}
}
command.CommandText = "USE";
command.ExecuteNonQuery();
}
}
public void Test()
{
using (var connection = GetConnection()) {
var dataAdapter = new VfpDataAdapter("Select * from orders", connection);
var dataTable = new DataTable();
dataAdapter.Fill(dataTable);
var commandBuilder = new VfpCommandBuilder(dataAdapter);
dataTable.Rows[0]["shipname"] = "test";
var command = commandBuilder.GetUpdateCommand(true);
var vfpCode = command.ToVfpCode();
var parameters = command.Parameters.Cast <VfpParameter>().ToList();
dataAdapter.Update(dataTable);
}
}
private static DataTable GetDataTable(string tableName)
{
var dataTable = new DataTable(tableName);
using (var connection = new VfpConnection(ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString)) {
using (var command = connection.CreateCommand()) {
command.CommandText = tableName;
command.CommandType = CommandType.TableDirect;
var adapter = new VfpDataAdapter(command);
adapter.Fill(dataTable);
}
}
return(dataTable);
}
public void TestAllTypes()
{
var actual = CreateDataTable();
//DataTableHelper.WriteDataTableCode("Expected", actual);
var creator = new DataTableDbcCreator(this.fixture.DbcPath);
creator.Add(actual);
var expected = new DataTable(actual.TableName);
using (var connection = this.fixture.CreateConnection()) {
var sql = string.Format("select * from '{0}'", actual.TableName);
var adapter = new VfpDataAdapter(sql, connection);
adapter.Fill(expected);
}
}
private static void QueryByLargeNumberOfIdsWithoutXmlToCursor(int[] orderIds)
{
Console.WriteLine("Running: {0}", MethodBase.GetCurrentMethod().Name);
using (var connection = new VfpConnection(ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString)) {
using (var command = connection.CreateCommand()) {
command.CommandText = @"
select o.OrderId, c.CustomerId, c.CompanyName
from Orders o
inner join Customers c on upper(allt(o.CustomerId)) == upper(allt(c.CustomerId))
where o.OrderId in (" + string.Join(",", orderIds) + ")";
var dataAdapter = new VfpDataAdapter(command);
var dataTable = new DataTable("Table1");
dataAdapter.Fill(dataTable);
}
}
}
private static DataTable GetProps(VfpConnection connection, DataTable schema)
{
var tableNames = schema.AsEnumerable().Select(x => x.Field <string>("TABLE_NAME")).Distinct();
var inserts = string.Join(Environment.NewLine, tableNames.Select(x => string.Format("INSERT INTO curViews VALUES('{0}')", x)).ToArray());
var vfpCode = string.Format(Resources.ViewProps, inserts);
connection.ExecuteScript(vfpCode);
using (var command = connection.CreateCommand()) {
command.CommandType = CommandType.Text;
command.CommandText = "SELECT * FROM curOutput";
var props = new DataTable();
var adapter = new VfpDataAdapter(command);
adapter.Fill(props);
return(props);
}
}
public void TestAllTypes()
{
var actual = CreateDataTable();
//DataTableHelper.WriteDataTableCode("Expected", actual);
var dbcPath = Path.Combine(TestContext.TestDeploymentDir, @"Data\MyTest.dbc");
var creator = new DataTableDbcCreator(dbcPath);
creator.Add(actual);
var expected = new DataTable(actual.TableName);
using (var connection = GetConnection(dbcPath)) {
var sql = string.Format("select * from '{0}'", actual.TableName);
var adapter = new VfpDataAdapter(sql, connection);
adapter.Fill(expected);
}
Console.WriteLine(dbcPath);
}
private DataTable GetDataTable(ArrayXmlToCursor arrayXmlToCursor)
{
var table = new DataTable();
this.ExecuteCommand(command => {
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "XmlToCursor";
command.Parameters.Add(new VfpParameter("xml", arrayXmlToCursor.Xml));
command.Parameters.Add(new VfpParameter("cursor", "curXmlTemp"));
command.ExecuteNonQuery();
command.CommandType = CommandType.Text;
command.Parameters.Clear();
command.CommandText = "SELECT * FROM curXmlTemp";
var adapter = new VfpDataAdapter(command);
adapter.Fill(table);
});
return(table);
}
private DataTable GetDataTable(ArrayXmlToCursor arrayXmlToCursor)
{
using (var connection = GetConnection(TestContext.TestDeploymentDir)) {
using (var command = connection.CreateCommand()) {
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "XmlToCursor";
command.Parameters.Add(new VfpParameter("xml", arrayXmlToCursor.Xml));
command.Parameters.Add(new VfpParameter("cursor", "curXmlTemp"));
command.ExecuteNonQuery();
command.CommandType = CommandType.Text;
command.Parameters.Clear();
command.CommandText = "SELECT * FROM curXmlTemp";
var table = new DataTable();
var adapter = new VfpDataAdapter(command);
adapter.Fill(table);
return(table);
}
}
}
public DataTable CreateDataTable(string commandText, VfpParameterCollection parameters = null)
{
Rewrite(ref commandText);
var dataTable = new DataTable("SchemaManagerResults");
using (var cmd = _connection.CreateCommand()) {
cmd.CommandText = commandText;
if (parameters != null)
{
foreach (VfpParameter parameter in parameters)
{
cmd.Parameters.Add(new VfpParameter(parameter.ParameterName, parameter.Value));
}
}
using (var da = new VfpDataAdapter(cmd)) {
try {
da.Fill(dataTable);
if (dataTable.Rows.Count > 0)
{
dataTable = dataTable.DefaultView.ToTable(dataTable.TableName, true);
}
#if DEBUG
_dbcCreator.Add(dataTable);
#endif
}
catch (Exception ex) {
VfpClientTracing.Tracer.TraceError(ex);
throw;
}
}
}
return(dataTable);
}
private static void QueryByLargeNumberOfIdsWithXmlToCursor(int[] orderIds)
{
Console.WriteLine("Running: {0}", MethodBase.GetCurrentMethod().Name);
// ToXmlToCursorFormattedXml is an Array extension method in the VfpClient namespace.
var xml = orderIds.ToXmlToCursorFormattedXml();
using (var connection = new VfpConnection(ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString)) {
using (var command = connection.CreateCommand()) {
command.CommandText = @"
select o.OrderId, c.CustomerId, c.CompanyName
from Orders o
inner join Customers c on upper(allt(o.CustomerId)) == upper(allt(c.CustomerId))
where o.OrderId in (select Id from (iif(XmlToCursor(@OrderIdsXml, 'curTempIdList') > 0, 'curTempIdList', '')))";
command.Parameters.AddWithValue("@OrderIdsXml", xml);
var dataAdapter = new VfpDataAdapter(command);
var dataTable = new DataTable("Table1");
dataAdapter.Fill(dataTable);
}
}
}
