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
And then the code file:
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