HomeActive Query BuilderAdd Table to metadata collection through code

Add Table to metadata collection through code


Hi,
I would like to add data tables to a metadata collection after it has been loaded through a connection.
The hard way will be to add the data tables to the database first, but I wonder if there is another way to simulate this process.
(we are talking for the .net version of the control)

By the way, right now the oledb provider supports only MS Access files up to 2003 but not 2007. I know that a new provider should be used: Microsoft.ACE.OLEDB.12.0
How can I incorporate this provider or should you include it in a future version ?
Thanks in advance
antonis | email
October 31 2007, 06:56 AM
Dear Antonis,

There is no problem to do so programmatically. The following code (as well as many other useful examples) is included in the Non-visual sample project that included in the full and trial versions of Active Query Builder .NET Edition:

// HOWTO: Fill metadata container with custom objects
public void FillMetadataContainer()
{
// create table
MetadataObject tableOrders = qb.MetadataContainer.AddTable("MySchema", "Orders", false, "MyDB");
tableOrders.Fields.AddField("OrderID");
tableOrders.Fields.AddField("OrderDate");
tableOrders.Fields.AddField("CustomerID");
tableOrders.Fields.AddField("ResellerID");

// create another table
MetadataObject tableCustomers = qb.MetadataContainer.AddTable("MySchema", "Customers", false, "MyDB");
tableCustomers.Fields.AddField("CustomerID");
tableCustomers.Fields.AddField("CustomerName");
tableCustomers.Fields.AddField("CustomerAddress");

// add a relation between these two tables
MetadataRelation relation = tableCustomers.Relations.Add();
relation.ChildSchemaStr = "MySchema";
relation.ChildDatabaseStr = "MyDB";
relation.ChildNameStr = "Orders";
relation.KeyFields.AddField("CustomerID");
relation.ChildFields.AddField("CustomerID");

//create view
MetadataObject viewResellers = qb.MetadataContainer.AddView("MySchema", "Resellers", false, "MyDB");
viewResellers.Fields.AddField("ResellerID");
viewResellers.Fields.AddField("ResellerName");
}

I will forward your question about new OLE DB Provider to the programmer.

Sincerely
Sergey Smagin
Sergey Smagin | email
9 hours, 36 mins since original post
If you have no office 2007 installed, please download the new Access Database Engine from this page:
http://www.microsoft.com/downloads/info.aspx?na=90&p=&SrcDisplayLang=en&SrcCategoryId=&SrcFamilyId=7554f536-8c28-4598-9b72-ef94e038c891&u=http%3a%2f%2fdownload.microsoft.com%2fdownload%2ff%2fd%2f8%2ffd8c20d8-e38a-48b6-8691-542403b91da1%2fAccessDatabaseEngine.exe
It installs the new OLEDB provider that supports *.accdb database format.

You can use the Query Builder with new database through ODBC or OLEDB Metadata Provider.

ODBC:
-----

OdbcConnection connection = new OdbcConnection();
connection.ConnectionString = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=c:\\Downloads\\Northwind 2007.accdb";

ODBCMetadataProvider metadataProvider = new ODBCMetadataProvider();
metadataProvider.Connection = connection;

queryBuilder1.MetadataProvider = metadataProvider;
queryBuilder1.SyntaxProvider = new MSAccessSyntaxProvider();
queryBuilder1.RefreshMetadata();


OLEDB:
------

OleDbConnection connection = new OleDbConnection();
connection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\\Downloads\\Northwind 2007.accdb";

OLEDBMetadataProvider metadataProvider = new OLEDBMetadataProvider();
metadataProvider.Connection = connection;

queryBuilder1.MetadataProvider = metadataProvider;
queryBuilder1.SyntaxProvider = new MSAccessSyntaxProvider();
queryBuilder1.RefreshMetadata();

Eugeny
Eugeny Starkov | email
20 hours, 3 mins since original post
This topic is closed