Skip to main content

Automatic class or object maker for Access tables in C#

The entity framework is all very well when you run SQL. But what about all of us that are running Access, or are porting old access systems to the web and need to keep legacy systems in place?

I was keen to us the object data source to work with data and could not find anything that was able make a class for me. So I wrote my own that listed the tables in the database, then created the class file and stored it in the temp directory.

I have a separate code folder to dump these in so that the project does not play with them, rather than dumping them into the App_Code  folder, but you can change this.

I placed this code in the test folder of my app which is called TrackerDotNet

So here is the code:

First the HTML file
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="AutoClassMaker.aspx.cs" Inherits="TrackerDotNet.test.AutoClassMaker" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Access Auto class maker</title>
</head>
<body>
    <form id="frmAutoClassMaker" runat="server">
    <div>
      <asp:ScriptManager ID="smgrORMClass" runat="server">
      </asp:ScriptManager>
      <asp:UpdateProgress ID="uprgORMCLass" runat="server">
        <ProgressTemplate>updating...<img src="../images/animi/BlueArrowsUpdate.gif" alt="please wait" /></ProgressTemplate>
      </asp:UpdateProgress>
      <p>
        Select a table that you want to make a Automatic Class:</p>
      <table border="0" cellpadding="3" style="line-height:2em; font-size: large; font-family: Calibri">
        <tr>
          <td>Table Names:</td>
          <td><asp:DropDownList ID="ddlTables" AutoPostBack="true" runat="server"
              OnSelectedIndexChanged="ddlTables_SelectedIndexChanged" style="padding: 2px; border: 1px solid #EEE; height: 2em; vertical-align: middle" /></td>
        </tr>
        <tr>
          <td>File name to save to:</td>
          <td>
            <asp:UpdatePanel ID="upnlClassFileName" runat="server">
              <ContentTemplate>
                <asp:TextBox ID="tbxORMClassFileName" runat="server" Text="MyORMClass.cs" style="padding: 2px; border: 1px solid #EEE; height: 2em; vertical-align: middle; width:25em" />
              </ContentTemplate>
              <Triggers>
                <asp:AsyncPostBackTrigger ControlID="ddlTables"
                  EventName="SelectedIndexChanged" />
              </Triggers>
            </asp:UpdatePanel>
           </td>

        </tr>
        <tr>
           <td align="center" colspan="2"><asp:Button ID="btnGo" Text="Go" runat="server"
               onclick="btnGo_Click" /></td>
        </tr>
      </table>
    </div>
    </form>
</body>
</html>



And then the code file:

using System;
using System.Collections.Generic;
using System.IO;
using System.Data.OleDb;
using System.Configuration;
using System.Data;

namespace TrackerDotNet.test
{
  public partial class AutoClassMaker : System.Web.UI.Page
  {
    const string CONST_CONSTRING = "Tracker08ConnectionString";

    private OleDbConnection OpenTrackerOleDBConnection()
    {
      OleDbConnection pConn = null;
      string _connectionString;

      if (ConfigurationManager.ConnectionStrings[CONST_CONSTRING] == null ||
          ConfigurationManager.ConnectionStrings[CONST_CONSTRING].ConnectionString.Trim() == "")
      {
        throw new Exception("A connection string named " + CONST_CONSTRING + " with a valid connection string " +
                            "must exist in the <connectionStrings> configuration section for the application.");
      }
      _connectionString = ConfigurationManager.ConnectionStrings[CONST_CONSTRING].ConnectionString;
      pConn = new OleDbConnection(_connectionString);

      return pConn;
    }

    protected void Page_Load(object sender, EventArgs e)
    {
      if (!IsPostBack)
      {
        // load the drop down list with table names
        OleDbConnection _TableNamesConn = OpenTrackerOleDBConnection();
        if (_TableNamesConn != null)
        {
          try
          {
            _TableNamesConn.Open();

            DataTable _TableNamesSchema = _TableNamesConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

            ddlTables.DataSource = _TableNamesSchema;
            ddlTables.DataTextField = "TABLE_NAME";
            ddlTables.DataBind();
          }
          catch (Exception ex)
          {
            throw new Exception("Error: " + ex.Message);
          }
          finally
          {
            _TableNamesConn.Close();
          }
        }

      }

    }

    protected void ddlTables_SelectedIndexChanged(object sender, EventArgs e)
    {
      // set the name of the class file
      tbxORMClassFileName.Text = ddlTables.SelectedValue + "Data.cs";
    }
   
    public struct dbTypesDef
    {
      public string typeName;
      public string typeNil;
      public string typeConvert;
    }

    protected OleDbType GetOleDBType(string pRowDBType)
    {
      return (OleDbType)Int32.Parse(pRowDBType);
    }

    protected void btnGo_Click(object sender, EventArgs e)
    {
   
      // create a Dictionary of types using the common DBType, add more if required
      Dictionary<OleDbType, dbTypesDef> _ColDBTypes = new Dictionary<OleDbType, dbTypesDef>();
      _ColDBTypes.Add(OleDbType.Binary, new dbTypesDef { typeName = "bool", typeNil = "false" , typeConvert = "Convert.ToBoolean" });
      _ColDBTypes.Add(OleDbType.Boolean, new dbTypesDef { typeName = "bool", typeNil = "false" , typeConvert = "Convert.ToBoolean" });
      _ColDBTypes.Add(OleDbType.BigInt, new dbTypesDef { typeName = "long", typeNil = "0", typeConvert = "Convert.ToInt64" });
      _ColDBTypes.Add(OleDbType.UnsignedBigInt, new dbTypesDef { typeName = "long", typeNil = "0", typeConvert = "Convert.ToInt64" });
      _ColDBTypes.Add(OleDbType.UnsignedTinyInt, new dbTypesDef { typeName = "byte", typeNil = "0", typeConvert = "Convert.ToInt64" });
      _ColDBTypes.Add(OleDbType.TinyInt, new dbTypesDef { typeName = "int16", typeNil = "0", typeConvert = "Convert.ToInt16" });
      _ColDBTypes.Add(OleDbType.Integer, new dbTypesDef { typeName = "int", typeNil = "0", typeConvert = "Convert.ToInt32" });
      _ColDBTypes.Add(OleDbType.Currency, new dbTypesDef { typeName = "double", typeNil = "0.0", typeConvert = "Convert.ToDouble" });
      _ColDBTypes.Add(OleDbType.Date, new dbTypesDef { typeName = "DateTime", typeNil = "System.DateTime.Now", typeConvert = "Convert.ToDateTime" });
      _ColDBTypes.Add(OleDbType.DBDate, new dbTypesDef { typeName = "DateTime", typeNil = "System.DateTime.Now", typeConvert = "Convert.ToDateTime" });
      _ColDBTypes.Add(OleDbType.DBTime, new dbTypesDef { typeName = "DateTime", typeNil = "System.DateTime.Now", typeConvert = "Convert.ToDateTime" });
      _ColDBTypes.Add(OleDbType.Double, new dbTypesDef { typeName = "double", typeNil = "0.0", typeConvert = "Convert.ToDouble" });
      _ColDBTypes.Add(OleDbType.Guid, new dbTypesDef { typeName = "long", typeNil = "0", typeConvert = "Convert.ToInt64" });
      _ColDBTypes.Add(OleDbType.Char, new dbTypesDef { typeName = "string", typeNil = "string.Empty" , typeConvert = "" });
      _ColDBTypes.Add(OleDbType.WChar, new dbTypesDef { typeName = "string", typeNil = "string.Empty", typeConvert = "" });
      _ColDBTypes.Add(OleDbType.VarChar, new dbTypesDef { typeName = "string", typeNil = "string.Empty", typeConvert = "" });
      _ColDBTypes.Add(OleDbType.LongVarChar, new dbTypesDef { typeName = "string", typeNil = "string.Empty", typeConvert = "" });
      _ColDBTypes.Add(OleDbType.LongVarWChar, new dbTypesDef { typeName = "string", typeNil = "string.Empty", typeConvert = "" });
      _ColDBTypes.Add(OleDbType.Single, new dbTypesDef { typeName = "double", typeNil = "0.0", typeConvert = "Convert.ToDouble" });
      _ColDBTypes.Add(OleDbType.SmallInt, new dbTypesDef { typeName = "int16", typeNil = "0", typeConvert = "Convert.ToInt16" });
      _ColDBTypes.Add(OleDbType.Numeric, new dbTypesDef { typeName = "double", typeNil = "0.0", typeConvert = "Convert.ToDouble" });
      _ColDBTypes.Add(OleDbType.Decimal, new dbTypesDef { typeName = "double", typeNil = "0.0", typeConvert = "Convert.ToDouble" });
      _ColDBTypes.Add(OleDbType.IUnknown, new dbTypesDef { typeName = "var", typeNil = "null", typeConvert = "" });
      _ColDBTypes.Add(OleDbType.Empty, new dbTypesDef { typeName = "var", typeNil = "null", typeConvert = "" });
   
      // open a file
      string _fName = "c:\\temp\\" + tbxORMClassFileName.Text  ;
      StreamWriter _ColsStream = new StreamWriter(_fName, false);  // create new file
      // first Write Header information
      _ColsStream.WriteLine("/// --- auto generated class for table: " + ddlTables.SelectedValue);
      _ColsStream.WriteLine("using System;   // for DateTime variables");
      _ColsStream.WriteLine("using System.Collections.Generic;      // for data stuff");
      _ColsStream.WriteLine("using System.Data.OleDb;");
      _ColsStream.WriteLine("using System.Configuration;");

      _ColsStream.WriteLine();
      _ColsStream.WriteLine("namespace TrackerDotNet.classes");     // modify this if you gonna store in different location
      _ColsStream.WriteLine("{");

      _ColsStream.WriteLine("  public class " + ddlTables.SelectedValue + "Data");
      _ColsStream.WriteLine("  {");
      _ColsStream.WriteLine("    // internal variable declarations");

      // ExportTableColumns to file
      OleDbConnection _ColsConn = OpenTrackerOleDBConnection();
      try
      {
        _ColsConn.Open();
        DataTable _ColsSchema = _ColsConn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null , ddlTables.SelectedValue, null });
        // sort the schema ordinally so that the column names are in the same order as they appear in the database
        DataRow[] _rows = _ColsSchema.Select(null, "ORDINAL_POSITION", DataViewRowState.CurrentRows);
        foreach (DataRow _row in _rows)
        {
          // for do the private definitions
          OleDbType _thisType = GetOleDBType(_row["DATA_TYPE"].ToString());
          _ColsStream.Write("    private ");
          if (_ColDBTypes.ContainsKey(_thisType))
          {
            _ColsStream.Write(_ColDBTypes[_thisType].typeName);
          }
          else
            _ColsStream.Write(_thisType.ToString());

          _ColsStream.WriteLine(" _" + _row["COLUMN_NAME"].ToString() + ";");
        }
        // now define the class initializer
        _ColsStream.WriteLine("    // class definition");
        _ColsStream.WriteLine("    public " + ddlTables.SelectedValue + "Data()");
        _ColsStream.WriteLine("    {");
        // now do the intialization class;
        foreach (DataRow _row in _rows)
        {
          // for do the private definitions
          OleDbType _thisType = GetOleDBType(_row["DATA_TYPE"].ToString());

          if (_ColDBTypes.ContainsKey(_thisType))
            _ColsStream.WriteLine(String.Format("      _{0} = {1};", _row["COLUMN_NAME"].ToString(), _ColDBTypes[_thisType].typeNil));
          else
            _ColsStream.WriteLine(String.Format("      _{0} = {1};", _row["COLUMN_NAME"].ToString(), "1"));
        }
        _ColsStream.WriteLine("    }");
        // now each get and set
        _ColsStream.WriteLine("    // get and sets of public");
        foreach (DataRow _row in _rows)
        {
          // for do the private definitions
          OleDbType _thisType = GetOleDBType(_row["DATA_TYPE"].ToString());
          _ColsStream.Write("    public ");
          if (_ColDBTypes.ContainsKey(_thisType))
          {
            _ColsStream.Write(_ColDBTypes[_thisType].typeName);
          }
          else
            _ColsStream.Write(_thisType.ToString());

          _ColsStream.Write(" "+_row["COLUMN_NAME"].ToString());
          _ColsStream.Write(" { get { return _");
          _ColsStream.Write(_row["COLUMN_NAME"].ToString());
          _ColsStream.Write(";}  set { _");
          _ColsStream.Write(_row["COLUMN_NAME"].ToString());
          _ColsStream.WriteLine(" = value;} }");
        }
        // close class
        _ColsStream.WriteLine("  }");
        // now a list all class with constants defining the SQL
        _ColsStream.WriteLine("  public class " + ddlTables.SelectedValue + "DAL");
        _ColsStream.WriteLine("  {");
        _ColsStream.WriteLine("  #region ConstantDeclarations");
        _ColsStream.WriteLine("    const string CONST_CONSTRING = \"" + CONST_CONSTRING + "\";");
        _ColsStream.Write    ("    const string CONST_SQL_SELECT = \"SELECT ");
        // add each line
        string _selectRows = "";
        foreach (DataRow _row in _rows)
          _selectRows +=  _row["COLUMN_NAME"].ToString() + ", ";

        _selectRows = _selectRows.Remove(_selectRows.Length -2,2);
        _selectRows += " FROM " + ddlTables.SelectedValue + "\";";
        _ColsStream.WriteLine(_selectRows);
        _ColsStream.WriteLine("  #endregion");
        _ColsStream.WriteLine();
        _ColsStream.WriteLine("    public List<" + ddlTables.SelectedValue + "Data> GetAll(string SortBy)");
        _ColsStream.WriteLine("    {");
        _ColsStream.WriteLine("      List<" + ddlTables.SelectedValue + "Data> _DataItems = new List<" + ddlTables.SelectedValue +"Data>();");
        _ColsStream.WriteLine("      string _connectionStr = ConfigurationManager.ConnectionStrings[CONST_CONSTRING].ConnectionString;");
        _ColsStream.WriteLine();
        _ColsStream.WriteLine("      using (OleDbConnection _conn = new OleDbConnection(_connectionStr))");
        _ColsStream.WriteLine("      {");
        _ColsStream.WriteLine("        string _sqlCmd = CONST_SQL_SELECT;");
        _ColsStream.WriteLine("        if (!String.IsNullOrEmpty(SortBy)) _sqlCmd += \" ORDER BY \" + SortBy;     // Add order by string");
        _ColsStream.WriteLine("        OleDbCommand _cmd = new OleDbCommand(_sqlCmd, _conn);                    // run the qurey we have built");
        _ColsStream.WriteLine("        _conn.Open();");
        _ColsStream.WriteLine("        OleDbDataReader _DataReader = _cmd.ExecuteReader();");
        _ColsStream.WriteLine("        while (_DataReader.Read())");
        _ColsStream.WriteLine("        {");
        _ColsStream.WriteLine("          " + ddlTables.SelectedValue + "Data _DataItem = new " + ddlTables.SelectedValue + "Data();");
        _ColsStream.WriteLine();
        // for each item assign the value
        foreach (DataRow _row in _rows)
        {
          OleDbType _thisType = GetOleDBType(_row["DATA_TYPE"].ToString());
          _ColsStream.Write("          _DataItem." + _row["COLUMN_NAME"].ToString() + " = (_DataReader[\"" + _row["COLUMN_NAME"].ToString() + "\"");
          _ColsStream.Write("] == DBNull.Value) ? " + _ColDBTypes[_thisType].typeNil + " : ");
          if (String.IsNullOrEmpty(_ColDBTypes[_thisType].typeConvert))
            _ColsStream.WriteLine("_DataReader[\"" + _row["COLUMN_NAME"].ToString() + "\"].ToString();");
          else
            _ColsStream.WriteLine(_ColDBTypes[_thisType].typeConvert  + "(_DataReader[\"" + _row["COLUMN_NAME"].ToString() + "\"]);");
        }
        _ColsStream.WriteLine("          _DataItems.Add(_DataItem);");
          //// ---- change Items _DataItems
        _ColsStream.WriteLine("        }");
        _ColsStream.WriteLine("      }");
        _ColsStream.WriteLine("      return _DataItems;");
        _ColsStream.WriteLine("    }");
        _ColsStream.WriteLine("  }");
        _ColsStream.WriteLine("}");
        _ColsStream.Close();
      }
      catch (Exception ex)
      {
     
        throw new Exception("Error: " + ex.Message);
      }
      finally
      {
        _ColsConn.Close();
      }

    }
  }
}

Comments

Popular posts from this blog

Migrating QuickBooks to Sage One Cloud accounting - Part 1 Exporting the data

Some notes Sage means sage one online accounts, wherever we say Sage we mean Sage one. The QuickBooks we used was version 2012 Professional. But most of the information is similar. What you need > You need the templates from Sage and the data from QuickBooks, see below for how to do these. Get Import Templates from Sage To get the templates for the items go to help.accounting.sageone.co.za/en_za/accounting/from-your-previous-accounting-system.html Although you can construct the templates from the information in this post you can download samples of the templates need. Below are the links they provide: Use the following downloads which are referenced in the guide: General Ledger Accounts Import Template Customer Import Template Customer Outstanding Invoices Import Template Supplier Import Template Supplier Outstanding Invoices Import Template Item Import Template   Another useful page that is hard to find on their system is: help.accounting.sageone.co.za/en_za/ac

Bitcoin / Cryptocurrency – what is it and how can I benefit

What is it I started investigating Bitcoin when it was worth just over $1000 a bitcoin. I was interested in what it was and how it worked. A lot of people are saying we missed the boat, but I believe that everyone should at least try put a little money in now, or at least use a faucet (see below) to make a little micro-currency. You can read a Wiki article about bitcoin and its history etc. But what you need to know is that it is a currency, that is independent of country. No one really knows who invented the concept of a cryptocurrency since the person who published the paper used a nom de plume. All new cryptocurrencies work more or less the same way as Bitcoin. So as I explain below I interchange these terms. Bitcoin is the original cryptocurrency. How Bitcoin works The currency releases a coin based on a mathematical formula. There will never be more than 21 million bitcoins (other cryptocurrencies do not work like this). Each bitcoin can have divided into one hundred mil

Mindfullness Meditation and Depression - in a pod cast

Over the last 3 years I have found that meditation has really assisted me, in resolving the depression I have had. I have planned to write about my experience for a while, and hopefully will get around to doing that. However today having listen to the latest podcast by Dan Harris on 10% happier, so many things just clicked in to place, so I want to share it. To understand what is covered in the podcast I would recommend you understand what mindfulness meditation is, and what the default mode network in the brain is (see links below). Listen to the pod cast - but here are some extracts (which I do not have permission to publish - and will remove if asked). Link to podcast: tumello.com/listen/H11a5NYJf; or itunes.apple.com/us/podcast/10-happier-with-dan-harris/id1087147821 Chuck Raison, a psychiatrist and a professor of psychiatry at the University of Wisconsin-Madison School of Medicine and Public Health, and Vlad Maletic, a clinical professor of neuropsychiatry and behavior sc