Jayaraman Jayaraman - 1 year ago 78
C# Question

How can I retrieve the query definition (SQL text) of an Access query and store back a changed definition

I have a requirement where I need to read queries from Access DB in c# and check if the access db query has any keyword like "KEY" if it has keywords I need to enclose that in square brackets"[]".just like how it is done in SQL.

Could someone suggest me how to do that?

Answer Source

You can retrieve the query text like this:

string connString =  @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\...\myDB.mdb";
using (var conn = new OleDbConnection(connString )) {
    string[] restrictions = new string[] { null, null, "myQuery" };
    DataTable schema = conn.GetSchema("Views", restrictions);
    if (schema.Rows.Count > 0) {
        DataRow row = schema.Rows[0];
        string queryText = (string)row["VIEW_DEFINITION"];

If you drop the restrictions argument with the query name, conn.GetSchema("Views") returns one row for each query. If you query conn.GetSchema("Procedures") other types of queries like insert, update and DDL statements that are not considered as queries are returned in row["PROCEDURE_DEFINITION"].

View (query) names are returned in row["TABLE_NAME"] and procedure names in row["PROCEDURE_NAME"].

And you can update the query like this:

using (var conn = new OleDbConnection(connString)) {
    var cmd = new OleDbCommand("DROP PROCEDURE myQuery", conn);
    cmd = new OleDbCommand("CREATE PROCEDURE myQuery AS SELECT * FROM myTable", conn);

Strangely enough the OleDb CREATE DDL (Data Definition Language) designates the queries as 'procedures' but the schema table returns a 'VIEW_DEFINITION' and the query name is returned in the column 'TABLE_NAME'. SELECT queries must be retrieved as "Views", other types of queries as "Procedures"; however, both types are created as PROCEDUREs.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download