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(); } }
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 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); } } }