Tech Blech

Sunday, May 03, 2009

C# code to get schema of an Access table

I just wanted to dump the schema of a table in Microsoft Access. There is a lot of code on the web which purports to do this, but most of it didn't actually work, and most of it was not in C# (my current preferred language). So I am posting this here for anyone that needs it. Just change the path to the database file in the first line of code, and the name of the table ("taxa" in my example) in the second line of code. This program dumps the table schema to a file created by the LogFile object (also attached below) located in the application folder. You'll need to add "using System.Data.OleDb;" to the top of the file. Or, just download the code here. The code:




OleDbConnection conn =
 new OleDbConnection(
    "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
    "C:\\phycoaide\\phycoaide.mdb;Persist Security Info=False;");

// retrieving schema for a single table
OleDbCommand cmd = new OleDbCommand("taxa", conn);
cmd.CommandType = CommandType.TableDirect;
conn.Open();
OleDbDataReader reader =
 cmd.ExecuteReader(CommandBehavior.SchemaOnly);
DataTable schemaTable = reader.GetSchemaTable();
reader.Close();
conn.Close();

LogFile.WriteLine(" ");
foreach (DataRow r in schemaTable.Rows)
{
 LogFile.WriteLine(" ");
 foreach (DataColumn c in schemaTable.Columns)
 {
    LogFile.WriteLine(c.ColumnName + ": " + r[c.ColumnName]);
 }
}
MessageBox.Show("done");


The LogFile class creates a file in the application folder from which your program runs:




// 
// No copyright; free for reuse by anyone
// 
// Pat G. Palmer
// ppalmer AT harbormist.com
// 2009-05-04
// Opens a single-threaded log file to trace execution
namespace Ansp
{
    using System;
    using System.IO;            // file readers and writers
    using System.Windows.Forms; // Application object

    /// 
    /// Singleton that appends log entries to a text file
    /// in the application folder.  If the file grows
    /// to be too large, it deletes itself and starts over.
    /// The file is kept open until the application ends
    /// and implements the "dispose" pattern in case things
    /// do not end gracefully.
    /// 
    public class LogFile : IDisposable
    {
        private static int maxsize = 470000;
        private static string fileSuffix = "_log.txt";
        private static string fileSpecification;
        private static StreamWriter filewriter;
        private static LogFile instance;

        private LogFile()
        {
        }

        ~LogFile()
        {
            this.Dispose(false);
        }

        public static void InitLogFile()
        {
            if (instance == null)
            {
                instance = new LogFile();
            }

            string stringMe = "InitLogFile: ";
            try
            {
                if (Application.ProductName.Length == 0)
                {
                    fileSpecification = Application.StartupPath + "\\" +
                       "Test" + fileSuffix;
                }
                else
                {
                    fileSpecification = Application.StartupPath + "\\" +
                       Application.ProductName + fileSuffix;
                }

                // restart file if too big
                if (File.Exists(fileSpecification))
                {
                    FileInfo myFileInfo = new FileInfo(fileSpecification);
                    if (myFileInfo.Length > maxsize)
                    {
                        File.Delete(fileSpecification);
                    }

                    myFileInfo = null;
                }

                // restart file with appending
                filewriter = new StreamWriter(
                   fileSpecification, true, System.Text.Encoding.UTF8);

                // start log with standard info
                WriteLine("\r\n---------------------------------------------");
                string tempString = stringMe +
                    Application.ProductName + " " +
                    Application.ProductVersion +
                    "log opened at " + 
                    DateTime.Now;
                WriteLine(tempString);
                WriteLine(stringMe + "username=" + SystemInformation.UserName);
                WriteLine(stringMe + Application.StartupPath);
            }
            catch
            {
            }
        }

        public static void WriteLine(string myInputLine)
        {
            try
            {
                if (instance == null)
                {
                   InitLogFile(); // first time only
                }
                if (myInputLine.Length != 0)
                {
                   filewriter.WriteLine(myInputLine);
                   filewriter.Flush(); // update file
                }
            }
            catch
            {
            }
        }

        public static void Close()
        {
            instance.Dispose();
        }

        /// 
        /// Implement IDisposable.
        /// Do not make this method virtual.
        /// A derived class must not override this method.
        /// 
        public void Dispose()
        {
            this.Dispose(true);
            //// Now, we call GC.SupressFinalize to take this object
            //// off the finalization queue and prevent finalization
            //// code for this object from executing a second time.
            GC.SuppressFinalize(this);
        }

        private void Dispose(bool disposing)
        {
            if (disposing)
            {
                // no managed resources to clean up
            }
            if (instance != null)
            {
                if (filewriter != null)
                {
                    try
                    {
                        filewriter.Flush();
                        filewriter.Close();
                    }
                    catch
                    {
                    }

                    filewriter = null;
                } // end if filewriter not null
            } // end if instance not null
        }

    } // end class LogFile()
} // end namespace

Labels: ,

5 Comments:

  • What if you'd like to compare a table schema against another table in another database?

    You should have to create readers for each of the tables you want to compare.

    Any ideas?

    By Anonymous manos, at 8:39 AM  

  • Here's another way. This mehtod returns a DataTable. Use this as your datasource for a dataGridView.
    -JohnM
    -----------------------------------

    public static DataTable GetTableSchema_V1(string tableName)
    {
    OleDbConnection cxn = GetAccessDBConn();
    cxn.Open();
    try
    {
    //// retrieving schema for a single table
    Guid guid = OleDbSchemaGuid.Columns;
    object[] restrictions = { null, null, tableName, null };
    DataTable schemaTable = cxn.GetOleDbSchemaTable(guid, restrictions);
    cxn.Close();
    return schemaTable;
    }
    catch (Exception e)
    {
    throw new Exception("An error occured getting database schema: " + e.Message, e);
    }
    finally
    {
    cxn.Close();
    }
    }

    By Anonymous Anonymous, at 9:05 PM  

  • BTW, neither of these methods yields constraints (table relationships). They don't even identify Primary Keys correctly.

    Do you know how to get these?

    -JohnM

    By Anonymous Anonymous, at 9:07 PM  

  • John,

    Sorry, I don't know (off hand) how to get constraints and things like that. I could figure it out, with time, which I have little of.

    Thanks for your comment! I will try your code when I get a chance.

    - Pat

    By Blogger Harbor Sparrow, at 6:58 AM  

  • This is a nice article..
    Its easy to understand ..
    And this article is using to learn something about it..

    c#, dot.net, php tutorial, Ms sql server

    Thanks a lot..!
    ri80

    By Blogger Muhammad Azeem, at 9:57 PM  

Post a Comment

<< Home