GetOleDbSchemaTable(OleDbSchemaGuid.Columns,...

Prev: Multiple Entries
Next: Depacco

From: Stefan Hoffmann on 22 Jan 2009 10:21

---

hi Markus,

Markus Ohlenroth wrote:
> which link do you mean?
> I could not find one piece of documentation on the values the returned field
> named
> COLUMN_FLAGS can take.

http://msdn.microsoft.com/en-us/library/bb677266.aspx


mfG
--> stefan <--

From: Markus Ohlenroth on 23 Jan 2009 02:55

---

hi Stefan,
thank you for your answer

Forgive me, maybe I am just too knew to ms access but I cannot see the
connection between the links you are offering and the question I have

For instance the last link you offer:
> http://msdn.microsoft.com/en-us/library/bb677266.aspx

It refers to SQL Server Date/Time and Schema Rowsets - as fas I can see -
not to MS access. Can you give me a clue how your links have to do with the
values the field COLUMN_FLAGS can take

I figured out that the
The values COLUMN_FLAGS - as one example - can take when reading from access
via GetOledbSchema has to to with autoincrement and type properteis of a
field.

But where is the documentation from Microsoft for these values?
Or how do I connect the information you offer with my question?

thanks
Markus
This thread does not seem to get us anywhere




"Stefan Hoffmann" wrote:

> hi Markus,
>
> Markus Ohlenroth wrote:
> > which link do you mean?
> > I could not find one piece of documentation on the values the returned field
> > named
> > COLUMN_FLAGS can take.
>
> http://msdn.microsoft.com/en-us/library/bb677266.aspx
>
>
> mfG
> --> stefan <--
>

From: Stefan Hoffmann on 23 Jan 2009 05:07

---

hi Markus,

Markus Ohlenroth wrote:
> Forgive me, maybe I am just too knew to ms access but I cannot see the
> connection between the links you are offering and the question I have
And I don't see the connection to Access. Can you provide a complete
code example with a brief description of your goal(s)?


mfG
--> stefan <--

From: Markus Ohlenroth on 23 Jan 2009 06:22

---

hi Stefan,
I work with visual studio, C# and read out metadata from access databases
via oledb

one method I use is : GetOledbSchemaTable
cf:
http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbconnection.getoledbschematable.aspx


the returned values are stored in a datatable:
DataTable schemaTable;
schemaTable = con.GetOleDbSchemaTable(OleDbSchemaGuid.Columns,....


if you query an access database and write the datatable to xml you get
something like:

<field TABLE_CATALOG="" TABLE_SCHEMA="" TABLE_NAME="articles"
COLUMN_NAME="ID" COLUMN_GUID="" COLUMN_PROPID="" ORDINAL_POSITION="1"
COLUMN_HASDEFAULT="False" COLUMN_DEFAULT="" COLUMN_FLAGS="90"
IS_NULLABLE="False" DATA_TYPE="3" TYPE_GUID="" CHARACTER_MAXIMUM_LENGTH=""
CHARACTER_OCTET_LENGTH="" NUMERIC_PRECISION="10" NUMERIC_SCALE=""
DATETIME_PRECISION="" CHARACTER_SET_CATALOG="" CHARACTER_SET_SCHEMA=""
CHARACTER_SET_NAME="" COLLATION_CATALOG="" COLLATION_SCHEMA=""
COLLATION_NAME="" DOMAIN_CATALOG="" DOMAIN_SCHEMA="" DOMAIN_NAME=""
DESCRIPTION="" />


now my question once again. Where can I find documentation on the values
each field can take?
thanks Markus


You wanted a complete code example:

Below I have added my first try on reading metadata from an access database.
It builds xml from all tables and fields in an access database. It is quick
and dirty, based on a number of assumptions (e.g. dbmetadata.xml file should
exist, linq namespace, xml namespace, etc.).


private void btnGetOledbSchemaTable_Click(object sender, EventArgs e)
{

XElement newTables = new XElement("tables", null);
DataTable schemaTable;
String ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data
Source='d:\\temp\\Database.mdb'";
OleDbConnection con = new OleDbConnection(ConnectionString);
con.Open();

// add all table names to newTables
schemaTable = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
new Object[] { null, null, null, "TABLE" });


for (int i = 0; i < schemaTable.Rows.Count; i++)
{
newTables.Add(new XElement("table",new
XAttribute("Name",schemaTable.Rows[i].ItemArray[2].ToString())));
}

schemaTable.Clear();
XNode node;
XNode fieldNode;
node = newTables.FirstNode;
do {

//retrieve all field names to the schemaTable
schemaTable = con.GetOleDbSchemaTable(OleDbSchemaGuid.Columns,
new Object[] { null, null, (node as
XElement).Attribute("Name").Value, null });
(node as XElement).Add(new XElement("fields",null));

//get the node just added
fieldNode = (node as XElement).LastNode;

schemaTable.WriteXmlSchema("D:\\temp\\schemaTable.xsd");


for (int k = 0; k < schemaTable.Rows.Count; k++)
{
((node as XElement).LastNode as XElement).Add(new
XElement("field",null));

for (int l = 0; l < schemaTable.Columns.Count; l++)
{
((fieldNode as XElement).LastNode as XElement).
Add(new
XAttribute(schemaTable.Columns[l].ColumnName,schemaTable.Rows[k].ItemArray[l].ToString()));
};
}
schemaTable.WriteXml("d:\\temp\\schemaTable.xml");
schemaTable.Clear();

}
while ((node = node.NextNode)!= null );
con.Close();
XDocument po = XDocument.Load("d:\\temp\\dbMetaData.xml");
XElement oldTables = new XElement("tables", null);

try
{
oldTables =
po.Root.DescendantsAndSelf().Elements("tables").First();
oldTables.ReplaceWith(newTables);
}
catch (System.InvalidOperationException)
{
po.Root.Add(newTables);
}
finally
{
XmlWriter writer = XmlWriter.Create("d:\\temp\\dbMetaData.xml");
po.WriteTo(writer);
writer.Flush();
writer.Close();
}

}
}





"Stefan Hoffmann" wrote:

> hi Markus,
>
> Markus Ohlenroth wrote:
> > Forgive me, maybe I am just too knew to ms access but I cannot see the
> > connection between the links you are offering and the question I have
> And I don't see the connection to Access. Can you provide a complete
> code example with a brief description of your goal(s)?
>
>
> mfG
> --> stefan <--
>

From: Stefan Hoffmann on 23 Jan 2009 07:56

---

hi Markus,

Markus Ohlenroth wrote:
> I work with visual studio, C# and read out metadata from access databases
> via oledb
Now, your source gives me the clues I was missing, take a look at these:

http://msdn.microsoft.com/en-us/library/ms723052(VS.85).aspx

and

http://msdn.microsoft.com/en-us/library/ms722704(VS.85).aspx


mfG
--> stefan <--

First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: Multiple Entries
Next: Depacco