static void Main(string[] args)
{
// Get the Northwind OrderDetails data.
var orderDetails = GetDataTable("OrderDetails");
// Convert the DataTable to an XmlToCursor formatted xml string.
// ToXmlToCursorFormattedXml is an DataTable extension method in the VfpClient namespace.
var xml = orderDetails.ToXmlToCursorFormattedXml();
using (var connection = new VfpConnection(ConfigurationManager.ConnectionStrings["FreeTables"].ConnectionString)) {
connection.Open();
// Create cursor using XmlToCursor with the DataTable xml.
using (var command = connection.CreateCommand()) {
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "XmlToCursor";
command.Parameters.Add(new VfpParameter("xml", xml));
command.Parameters.Add(new VfpParameter("cursor", "curXmlTemp"));
command.ExecuteNonQuery();
}
// Use the cursor to insert records into the destination table.
using (var command = connection.CreateCommand()) {
command.CommandText = "INSERT INTO 'OrderDetailsArchive' SELECT * FROM curXmlTemp";
command.ExecuteNonQuery();
}
connection.Close();
}
}
protected void CreateTable(Table table)
{
using (var command = _connection.CreateCommand()) {
command.CommandText = VfpCodeTableConverter.GetVfpCode(table);
_connection.DoConnected(() => command.ExecuteNonQuery());
}
}
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 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();
}
}
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();
}
}
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);
}
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);
}
}
}
public void ZapTest()
{
var dbc = CreateTempDbc();
using (var connection = new VfpConnection(dbc)) {
connection.Open();
using (var command = connection.CreateCommand()) {
command.CommandText = "select count(*) from temp";
Assert.AreNotEqual(0, Convert.ToInt32(command.ExecuteScalar()));
connection.Zap("temp");
Assert.AreEqual(0, Convert.ToInt32(command.ExecuteScalar()));
}
connection.Close();
}
}
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 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);
}
}
}
public override DataTable GetSchema(VfpConnection connection, string[] restrictionValues)
{
ArgumentUtility.CheckNotNull("connection", connection);
var schema = CreateEmptyDataTable();
if (!connection.IsDbc)
{
return(schema);
}
var parameters = connection.GetSchema(VfpConnection.SchemaNames.ProcedureParameters, restrictionValues);
if (parameters.Rows.Count == 0)
{
return(schema);
}
var list = (from p in parameters.AsEnumerable()
group p by p.Field <string>(ProcedureParameter.ProcedureName) into g
select new {
ProcedureName = g.Key,
Parameters = g.Select(item => new {
ParameterName = item.Field <string>(ProcedureParameter.ParameterName),
VfpType = (VfpType)item.Field <int>(ProcedureParameter.VfpType)
}).ToList()
}).ToList();
foreach (var item in list)
{
using (var command = connection.CreateCommand()) {
command.CommandType = CommandType.StoredProcedure;
command.CommandText = item.ProcedureName;
command.CommandText += "(";
if (item.Parameters.Count > 0)
{
for (int index = 0, total = item.Parameters.Count; index < total; index++)
{
if (index > 0)
{
command.CommandText += ", ";
}
var parameter = item.Parameters[index];
var value = parameter.VfpType.GetDefaultValue();
command.CommandText += value;
}
}
command.CommandText += ")";
try {
var reader = command.ExecuteReader(CommandBehavior.SchemaOnly);
var schemaTable = reader.GetSchemaTable();
AddSchemaRows(item.ProcedureName, schema, schemaTable);
}
catch (VfpException ex) {
VfpClientTracing.Tracer.TraceError(ex);
}
}
}
FormatColumnValues(schema, new[] { ProcedureColumn.ProcedureName, ProcedureColumn.FieldName });
return(schema);
}
