|
hi,
Great article Smile . it helps me a lot.
I wish
1) you should create a standalone application (Exe) and upload it. so it it can be used similar to an code generator application.
2)it should allow one more column optional to create enum description to map to enum description.
Rama Charan Prasad
"Be happy and Keep smiling.Thats what u want be always.. "
|
|
|
|
|
Following improvements done by me (I am not the author of the original project)
- Support for table schemas, you are no longer bound to the default DBO schema
- Fixed bug with some tables showing ID in the name of the ListView rather than the other column.
- Generated code is now wrapped in a namespace. The namespace ends with the name of the table schema
- User can specify the root namespace that will prefix the schema name in the finale namespace wrapper
- Added tooltips with info
- The ListView is now SORTED and the generated enumeration is also sorted in ascending order
--- CODE FOLLOWS MainForm.cs ---
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using MSDASC;
using ADODB;
using System.Data.OleDb;
using System.CodeDom;
namespace EnumCodeGenerator
{
///
/// DEGT added the following:
/// - Support for schemas other than the default (DBO)
/// - Fixed bug with IDs shown in the wrong column of list view
/// - Sort the values of the list view (ascending)
/// - Added Tooltip to controls
/// - Added Root namespace user input
/// - Code is now generated wrapped in a namespace
///
public struct TableInfo
{
public const string DefaultSchema = "dbo";
private string catalog;
private string schemaName;
private string tableName;
public string Schema
{
get { return this.schemaName; }
set { this.schemaName = (String.IsNullOrEmpty(value) ? DefaultSchema : value); }
}
public string TableName
{
get { return this.tableName; }
set { this.tableName = String.IsNullOrEmpty(value) ? String.Empty : value; }
}
public TableInfo(string schema, string tname)
{
this.catalog = String.Empty;
this.schemaName = (String.IsNullOrEmpty(schema) ? DefaultSchema : schema.Trim().ToLower());
this.tableName = tname;
}
public TableInfo(DataRow row) : this((string)row["TABLE_SCHEMA"], (string)row["TABLE_NAME"])
{
this.catalog = (string)row["TABLE_CATALOG"];
}
public TableInfo(string table)
{
this.catalog = String.Empty;
table = table.Trim();
int dot = table.IndexOf(".");
if (dot > 0)
{
string tmp = table.Substring(0, dot);
this.schemaName = String.IsNullOrEmpty(tmp) ? DefaultSchema : tmp;
this.tableName = table.Substring(dot + 1);
}
else
{
this.schemaName = DefaultSchema;
this.tableName = table;
}
}
///
/// The fully qualified table name (minus database name)
///
/// <returns>schema.table i.e. dbo.MyTable
public override string ToString()
{
return String.Format("{0}.{1}", this.schemaName, this.tableName);
}
}
public partial class MainForm : Form
{
OleDbConnection oleDbConn = new OleDbConnection();
TableInfo tableInfo = new TableInfo();
public MainForm()
{
InitializeComponent();
}
private void btnConnect_Click(object sender, EventArgs e)
{
try
{
DataLinksClass dl = new DataLinksClass();
_Connection conn = dl.PromptNew() as _Connection;
if (conn != null)
{
PopulateTables(conn.ConnectionString);
}
}
catch (OleDbException ex)
{
MessageBox.Show(ex.Message);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void PopulateTables(string connString)
{
try
{
tableList.Items.Clear();
ResetControls();
if ((connString != null) && (connString.Length > 0))
{
oleDbConn.ConnectionString = connString;
oleDbConn.Open();
//Get table names
DataTable schemaTable = oleDbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
// Display the table name from each row in the schema
foreach (DataRow row in schemaTable.Rows)
{
// DEGT: added this to make it work with schemas other than DBO
string fullTableName = String.Format("{0}.{1}", row["TABLE_SCHEMA"], row["TABLE_NAME"]);
tableList.Items.Add(fullTableName);
//tableList.Items.Add(row["TABLE_NAME"]);// add TABLE_SCHEMA
}
// Also
}
}
finally
{
if (oleDbConn != null)
oleDbConn.Close();
}
}
private void tableList_SelectedValueChanged(object sender, EventArgs e)
{
this.tableInfo = new TableInfo(tableList.SelectedItem.ToString());
//tableName = tableList.SelectedItem.ToString();
try
{
if (this.tableInfo.TableName.Length > 0)
{
ResetControls();
oleDbConn.Open();
//Get column names (catalog, schema, table)
DataTable schemaTable = oleDbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, this.tableInfo.Schema, this.tableInfo.TableName });
// Display the table name from each row in the schema
foreach (DataRow row in schemaTable.Rows)
{
fieldList.Items.Add(row["COLUMN_NAME"]);
}
}
}
finally
{
if (oleDbConn != null)
oleDbConn.Close();
}
}
private void ResetControls()
{
fieldList.Items.Clear();
dataView.Clear();
codeView.Text = "";
}
private void btnGenCSharpCode_Click(object sender, EventArgs e)
{
this.codeView.Text = String.Empty;
if (PopulateData())
this.codeView.Text = GenerateCode(false);
}
private void btnGenVBDotNetCode_Click(object sender, EventArgs e)
{
this.codeView.Text = String.Empty;
if (PopulateData())
this.codeView.Text = GenerateCode(true);
}
///
/// Generate the code from the sorted values in the ListView. The 1st item
/// in the listview is the ID and the 2nd is the name. Code is now generated
/// with a namespace wrapper. The namespace contains the name of the schema
/// to which the table belongs
///
/// <param name="isVBDotNet" />true for generating VB.NET, otherwise C#
/// <returns>Generated code
private string GenerateCode(bool isVBDotNet)
{
StringBuilder sb = new StringBuilder();
// Wrap the enumeration in a namespace that contains the schema name
// of the table and emit the enumeration declaration
if (isVBDotNet)
{
sb.AppendFormat("Namespace {0}.{1}\n", this.textBoxRootNamespace.Text, this.tableInfo.Schema); // DEGT
sb.AppendLine("Public Enum " + this.tableInfo.TableName);
}
else
{
sb.AppendFormat("namespace {0}.{1}\n", this.textBoxRootNamespace.Text, this.tableInfo.Schema); // DEGT
sb.AppendLine("{"); // DEGT
sb.AppendLine("\tpublic enum " + this.tableInfo.TableName);
sb.AppendLine("\t{");
}
// enumerate all the items
int idColumnIndex = 0; // ListView subitem with ID
int nameColumnIndex = 1; // ListView subitem with Name
foreach (ListViewItem lvi in this.dataView.Items)
{
string enumName = MakeEnumItem(lvi.SubItems[nameColumnIndex].Text);
sb.AppendFormat("\t\t{0} = {1}", enumName, lvi.SubItems[idColumnIndex].Text);
if (!isVBDotNet)
{
sb.Append(",");
}
sb.Append("\n");
}
// Close de enumeration and namespace declarations
if (isVBDotNet)
{
sb.AppendLine("End Enum");
sb.AppendLine("End Namespace"); // DEGT
}
else
{
sb.Remove(sb.Length - 2, 1);
sb.AppendLine("\t}");
sb.AppendLine("}"); // DEGT namespace ends
}
return sb.ToString();
}
///
/// The actual name on the DB may not be a valid identifier in the chosen
/// generation language. We transform it so that the code is correct.
///
/// <param name="fieldNameValue" />raw name
/// <returns>a valid enumeration identifier
private string MakeEnumItem(string fieldNameValue)
{
string enumName;
enumName = fieldNameValue.Replace(' ', '_');
enumName = enumName.Replace('/', '_');
enumName = enumName.Replace('.', '_');
return enumName;
}
private bool PopulateData()
{
bool ok = false;
try
{
if (fieldList.SelectedItems.Count == 2)
{
oleDbConn.Open();
string strSql = GetDynamicSql();
//Get table names
OleDbCommand command = new OleDbCommand(strSql, oleDbConn);
OleDbDataReader dataReader = command.ExecuteReader(CommandBehavior.CloseConnection);
int nFields = dataReader.FieldCount;
int idColumnIndex = -1; // DEGT
int nameColumnIndex = -1; // DEGT
if (nFields == 2)
{
// Setup the columns (2) in the listview using the fields in the table
dataView.Clear();
if (dataReader.GetFieldType(0).ToString().Equals("System.String"))
{
idColumnIndex = 1;
nameColumnIndex = 0;
//dataView.Columns.Add(dataReader.GetName(1), 100, HorizontalAlignment.Left);
//dataView.Columns.Add(dataReader.GetName(0), 100, HorizontalAlignment.Left);
}
else
{
idColumnIndex = 0;
nameColumnIndex = 1;
//dataView.Columns.Add(dataReader.GetName(0), 100, HorizontalAlignment.Left);
//dataView.Columns.Add(dataReader.GetName(1), 100, HorizontalAlignment.Left);
}
dataView.Columns.Add(dataReader.GetName(idColumnIndex), 100, HorizontalAlignment.Left);
dataView.Columns.Add(dataReader.GetName(nameColumnIndex), 100, HorizontalAlignment.Left);
dataView.Sorting = SortOrder.Ascending;
// Fill the rows in the listview using the data in the rows
while (dataReader.Read())
{
// Create an array of subitems for quick insertion
// The subitems will be all fields in the row except for
// the first field
String[] subitems = new String[nFields];
int val;
if (Int32.TryParse(dataReader[0].ToString(), out val))
{
//subitems[0] = dataReader[1].ToString();
//subitems[1] = dataReader[0].ToString();
subitems[nameColumnIndex] = dataReader[1].ToString();
subitems[idColumnIndex] = dataReader[0].ToString();
}
else if (Int32.TryParse(dataReader[1].ToString(), out val))
{
//subitems[0] = dataReader[0].ToString();
//subitems[1] = dataReader[1].ToString();
subitems[nameColumnIndex] = dataReader[1].ToString();
subitems[idColumnIndex] = dataReader[0].ToString();
}
else
{
MessageBox.Show("There are no ID columns with integer values in the selection");
dataReader.Close();
return false;
}
// Insert a new item into the listview, and add the subitems at
// the same time. The item will be the first field in the row
ListViewItem item = new ListViewItem(subitems, -1);
dataView.Items.Add(item);
}
dataReader.Close();
// DEGT with one query alone we can't know which column is the
// ID column to sort in SQL so we simply get the result set and
// let the ListView do the sorting
dataView.Sort(); // DEGT sort it
ok = true;
}
}
else
{
MessageBox.Show("Please select two(and only two fields) from the field list");
dataView.Clear();
ok = false;
}
}
finally
{
if ((oleDbConn != null) && (oleDbConn.State == ConnectionState.Open))
oleDbConn.Close();
}
return ok;
}
private string GetDynamicSql()
{
// create dynamic sql
StringBuilder sb = new StringBuilder();
sb.Append("Select ");
int i = 0;
foreach (string fieldName in fieldList.SelectedItems)
{
i++;
sb.Append("[");
sb.Append(fieldName);
sb.Append("]");
if (fieldList.SelectedItems.Count != i)
sb.Append(", ");
}
sb.Append(" from ");
sb.Append(this.tableInfo.ToString()); // DEGT contains schema and table name
return sb.ToString();
}
}
}
----- CODE FOLLOWS MainForm.designer.cs ----
namespace EnumCodeGenerator
{
partial class MainForm
{
///
/// Required designer variable.
///
private System.ComponentModel.IContainer components = null;
///
/// Clean up any resources being used.
///
/// <param name="disposing" />true if managed resources should be disposed; otherwise, false.
protected override void Dispose(bool disposing)
{
if (disposing && (components != null))
{
components.Dispose();
}
base.Dispose(disposing);
}
#region Windows Form Designer generated code
///
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
///
private void InitializeComponent()
{
this.components = new System.ComponentModel.Container();
this.btnConnect = new System.Windows.Forms.Button();
this.tableList = new System.Windows.Forms.ListBox();
this.fieldList = new System.Windows.Forms.ListBox();
this.btnGenCSharpCode = new System.Windows.Forms.Button();
this.dataView = new System.Windows.Forms.ListView();
this.codeView = new System.Windows.Forms.RichTextBox();
this.label3 = new System.Windows.Forms.Label();
this.label4 = new System.Windows.Forms.Label();
this.label5 = new System.Windows.Forms.Label();
this.label6 = new System.Windows.Forms.Label();
this.btnGenVBDotNetCode = new System.Windows.Forms.Button();
this.labelNamespace = new System.Windows.Forms.Label();
this.toolTip1 = new System.Windows.Forms.ToolTip(this.components);
this.textBoxRootNamespace = new System.Windows.Forms.TextBox();
this.SuspendLayout();
//
// btnConnect
//
this.btnConnect.Location = new System.Drawing.Point(17, 15);
this.btnConnect.Margin = new System.Windows.Forms.Padding(4, 4, 4, 4);
this.btnConnect.Name = "btnConnect";
this.btnConnect.Size = new System.Drawing.Size(147, 28);
this.btnConnect.TabIndex = 0;
this.btnConnect.Text = "&Connect to DB...";
this.toolTip1.SetToolTip(this.btnConnect, "Build a connection string");
this.btnConnect.UseVisualStyleBackColor = true;
this.btnConnect.Click += new System.EventHandler(this.btnConnect_Click);
//
// tableList
//
this.tableList.FormattingEnabled = true;
this.tableList.ItemHeight = 16;
this.tableList.Location = new System.Drawing.Point(17, 101);
this.tableList.Margin = new System.Windows.Forms.Padding(4, 4, 4, 4);
this.tableList.Name = "tableList";
this.tableList.Size = new System.Drawing.Size(208, 260);
this.tableList.TabIndex = 1;
this.tableList.SelectedValueChanged += new System.EventHandler(this.tableList_SelectedValueChanged);
//
// fieldList
//
this.fieldList.FormattingEnabled = true;
this.fieldList.ItemHeight = 16;
this.fieldList.Location = new System.Drawing.Point(248, 101);
this.fieldList.Margin = new System.Windows.Forms.Padding(4, 4, 4, 4);
this.fieldList.Name = "fieldList";
this.fieldList.SelectionMode = System.Windows.Forms.SelectionMode.MultiSimple;
this.fieldList.Size = new System.Drawing.Size(187, 260);
this.fieldList.TabIndex = 1;
this.toolTip1.SetToolTip(this.fieldList, "Please select TWO fields");
//
// btnGenCSharpCode
//
this.btnGenCSharpCode.Location = new System.Drawing.Point(444, 145);
this.btnGenCSharpCode.Margin = new System.Windows.Forms.Padding(4, 4, 4, 4);
this.btnGenCSharpCode.Name = "btnGenCSharpCode";
this.btnGenCSharpCode.Size = new System.Drawing.Size(128, 47);
this.btnGenCSharpCode.TabIndex = 0;
this.btnGenCSharpCode.Text = "&Generate C# Code";
this.btnGenCSharpCode.UseVisualStyleBackColor = true;
this.btnGenCSharpCode.Click += new System.EventHandler(this.btnGenCSharpCode_Click);
//
// dataView
//
this.dataView.FullRowSelect = true;
this.dataView.GridLines = true;
this.dataView.Location = new System.Drawing.Point(580, 101);
this.dataView.Margin = new System.Windows.Forms.Padding(4, 4, 4, 4);
this.dataView.Name = "dataView";
this.dataView.Size = new System.Drawing.Size(264, 260);
this.dataView.TabIndex = 3;
this.dataView.UseCompatibleStateImageBehavior = false;
this.dataView.View = System.Windows.Forms.View.Details;
//
// codeView
//
this.codeView.Anchor = ((System.Windows.Forms.AnchorStyles)(((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Left)
| System.Windows.Forms.AnchorStyles.Right)));
this.codeView.Location = new System.Drawing.Point(853, 101);
this.codeView.Margin = new System.Windows.Forms.Padding(4, 4, 4, 4);
this.codeView.Name = "codeView";
this.codeView.Size = new System.Drawing.Size(344, 260);
this.codeView.TabIndex = 4;
this.codeView.Text = "";
//
// label3
//
this.label3.AutoSize = true;
this.label3.Location = new System.Drawing.Point(13, 76);
this.label3.Margin = new System.Windows.Forms.Padding(4, 0, 4, 0);
this.label3.Name = "label3";
this.label3.Size = new System.Drawing.Size(51, 17);
this.label3.TabIndex = 2;
this.label3.Text = "Tables";
//
// label4
//
this.label4.AutoSize = true;
this.label4.Location = new System.Drawing.Point(244, 76);
this.label4.Margin = new System.Windows.Forms.Padding(4, 0, 4, 0);
this.label4.Name = "label4";
this.label4.Size = new System.Drawing.Size(45, 17);
this.label4.TabIndex = 2;
this.label4.Text = "Fields";
//
// label5
//
this.label5.AutoSize = true;
this.label5.Location = new System.Drawing.Point(576, 76);
this.label5.Margin = new System.Windows.Forms.Padding(4, 0, 4, 0);
this.label5.Name = "label5";
this.label5.Size = new System.Drawing.Size(71, 17);
this.label5.TabIndex = 2;
this.label5.Text = "Data View";
//
// label6
//
this.label6.AutoSize = true;
this.label6.Location = new System.Drawing.Point(849, 76);
this.label6.Margin = new System.Windows.Forms.Padding(4, 0, 4, 0);
this.label6.Name = "label6";
this.label6.Size = new System.Drawing.Size(74, 17);
this.label6.TabIndex = 2;
this.label6.Text = "Code View";
//
// btnGenVBDotNetCode
//
this.btnGenVBDotNetCode.Location = new System.Drawing.Point(444, 230);
this.btnGenVBDotNetCode.Margin = new System.Windows.Forms.Padding(4, 4, 4, 4);
this.btnGenVBDotNetCode.Name = "btnGenVBDotNetCode";
this.btnGenVBDotNetCode.Size = new System.Drawing.Size(128, 48);
this.btnGenVBDotNetCode.TabIndex = 0;
this.btnGenVBDotNetCode.Text = "&Generate VB.NET Code";
this.btnGenVBDotNetCode.UseVisualStyleBackColor = true;
this.btnGenVBDotNetCode.Click += new System.EventHandler(this.btnGenVBDotNetCode_Click);
//
// labelNamespace
//
this.labelNamespace.AutoSize = true;
this.labelNamespace.Location = new System.Drawing.Point(580, 25);
this.labelNamespace.Name = "labelNamespace";
this.labelNamespace.Size = new System.Drawing.Size(119, 17);
this.labelNamespace.TabIndex = 5;
this.labelNamespace.Text = "Root namespace:";
this.toolTip1.SetToolTip(this.labelNamespace, "Root namespace of generated code");
//
// textBoxRootNamespace
//
this.textBoxRootNamespace.Location = new System.Drawing.Point(706, 20);
this.textBoxRootNamespace.MaxLength = 200;
this.textBoxRootNamespace.Name = "textBoxRootNamespace";
this.textBoxRootNamespace.Size = new System.Drawing.Size(491, 22);
this.textBoxRootNamespace.TabIndex = 6;
this.textBoxRootNamespace.Text = "Application.Data";
this.toolTip1.SetToolTip(this.textBoxRootNamespace, "The schema name will be appended automatically");
//
// MainForm
//
this.AutoScaleDimensions = new System.Drawing.SizeF(8F, 16F);
this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
this.ClientSize = new System.Drawing.Size(1215, 384);
this.Controls.Add(this.textBoxRootNamespace);
this.Controls.Add(this.labelNamespace);
this.Controls.Add(this.codeView);
this.Controls.Add(this.dataView);
this.Controls.Add(this.label6);
this.Controls.Add(this.label5);
this.Controls.Add(this.label4);
this.Controls.Add(this.label3);
this.Controls.Add(this.fieldList);
this.Controls.Add(this.tableList);
this.Controls.Add(this.btnGenVBDotNetCode);
this.Controls.Add(this.btnGenCSharpCode);
this.Controls.Add(this.btnConnect);
this.Margin = new System.Windows.Forms.Padding(4, 4, 4, 4);
this.Name = "MainForm";
this.Text = "Enum Code Generator";
this.ResumeLayout(false);
this.PerformLayout();
}
#endregion
private System.Windows.Forms.Button btnConnect;
private System.Windows.Forms.ListBox tableList;
private System.Windows.Forms.ListBox fieldList;
private System.Windows.Forms.Button btnGenCSharpCode;
private System.Windows.Forms.ListView dataView;
private System.Windows.Forms.RichTextBox codeView;
private System.Windows.Forms.Label label3;
private System.Windows.Forms.Label label4;
private System.Windows.Forms.Label label5;
private System.Windows.Forms.Label label6;
private System.Windows.Forms.Button btnGenVBDotNetCode;
private System.Windows.Forms.ToolTip toolTip1;
private System.Windows.Forms.Label labelNamespace;
private System.Windows.Forms.TextBox textBoxRootNamespace;
}
}
http://www.PanamaSights.com/
http://www.coralys.com/
http://www.virtual-aviation.info/
|
|
|
|
|
When I try the Connect DB the application fails:
1. Select the OLEDB for SQL Server
2. Entered server name I had created with the Data Source management tool (microsoft)
3. Selected SQL authentication
4. Entered username (sa) and password (won't tell you) (works with Management Studio)
5. Selected the correct database from the drop down list (so it can read it from the server)
6. Clicked on "Test Connection" and got "connection succeeded"
7. Pressed OK given that the connection is correct
Got "Login failed for sa". So how it is that it can get a connection, read the list of databases and then say it login failed with the credentials that ARE valid (as I say I use them with Management Studio)?
I tried by using in #2 the data source name I created weeks ago, then built the connection string and got a different error, something about OLEDB not supported.
http://www.PanamaSights.com/
http://www.coralys.com/
http://www.virtual-aviation.info/
|
|
|
|
|
Come on, there is so much that these things can do for you. Live updates is the responsability of the person doing the delivery. The best practice is to have enumeration columns that only change at most on every new release and not "live".
http://www.PanamaSights.com/
http://www.coralys.com/
http://www.virtual-aviation.info/
|
|
|
|
|
This is a really good resource, and will be most useful - especially with large lookup tables.
However, it only seems to handle tables in the dbo schema. If I select a table in a different schema it throws the error "Invalid object name " and whatever the table name is. Any idea on what I would need to tweak to get this to work?
Thanks.
|
|
|
|
|
Certainly. Infact, that is a better approach when you want to generate as well as compile the code for multiple look up tables. But for a single table, using codedom would be an overkill.
|
|
|
|
|
The real cookie reward is only provided if you can come up with a way to "autocompile" and "autodeploy" the code into a library.
normal usage steps:
1. developer inserts new enum values in the database table
2. developer executes this app
3. app generates code, compiles it into a DLL
4. app deploys the DLL.
5. systems using the DLL have immediate access to the new enum array - in the IDE and in the production call execution.
You do that and you got my vote.Thanks.
|
|
|
|