Click here to Skip to main content
14,880,638 members
Articles / Desktop Programming / WPF
Article
Posted 9 Dec 2020

Stats

6K views
212 downloads
12 bookmarked

Entity Factory - Get Your ORM-less Freak On!

Rate me:
Please Sign up or sign in to vote.
5.00/5 (7 votes)
2 Jan 2021CPOL30 min read
A tool to generate model and viewmodel classes directly from your selected database
The purpose of this application is to generate source code files for entity models, and optionally entity viewmodels (ostensibly for WPF apps), based on the contents of your database

Introduction

Entity Factory is a tool that is intended to generate model, and (optionally) viewmodel classes for use in your applications. There was no "grand design" when I started to write it, and most of the stuff in the application was included as I thought about it. I'm sure most of you have been head-down writing code like a mad man, and get to a point where you think, "Maybe I should add this or that to the program."

Entity Factory was developed almost entirely that way. In today's modern agile/scrum paradigm, most of you will probably recoil in horror, envisioning all manner of chaos, and expressing near outrage at the lack of task scoring or con/bon charts, not to mention the abandonment of anything that might resemble a "sprint". Well, welcome to the 70's, 80's, and 90's style of development, where someone has a bunch of ideas, and ad-libs those ideas into an application without interference from sales nazis or some bizarre agenda imposed on them by management. I suppose I'm the last of this dying breed of developer. The world of development will be a far worse place without guys like me. You'll see. No, really.

This application started out as a tool for my coworkers to use when we start the rewrite of our multitude of web applications. Every programmer reading this longs for tools that reduce the tedium of model/viewmodel entity creation, especially if they have a fanatical - yet understandable - aversion to using ORMs. I've personally NEVER encountered a tangible reason for preferring an ORM over simpler, faster, and MORE FLEXIBLE home-grown ADO code. In order to make this application more appropriate for public consumption outside of work, I had to kinda rewrite the whole thing a couple of times, which actually helped make it better for use at work at the same time.

This article includes a LOT of screenshots. To keep the article to a reasonable visual size, and because even at the max size suggested by CodeProject's best practices makes them too small to really be able to read text on them, I provided them as clickable thumbnails, which will display the full-size image in a new tab. Innovator. That's my middle name.

Note - if I publish an update to the application, I will probably not publish new screen shots unless something significant has been changed. Lazy. That's also my middle name.

Burn-out? Apathy? Cowboy?

In the interest of full disclosure, my heart wasn't really in the writing of this article. I've been working on this code for over a year, and suffered a catastrophic code loss about eight months ago, and finally worked up enough interest to try to recover from that. As with most of my articles, the act of actually writing the article prompted several last-minute design changes and the seemingly constant back and forth between writing the article, changing the code, and generating new screen shots became onerous.

Honestly, I hope I didn't hopelessly break the code in the process. If nothing else, this process enforces my belief that the developer should be involved in the production of end user documentation, and project schedules should be flexible enough to absorb late-breaking changes whenever possible.

Finally, this is not a teaching article. It doesn't explore the latest programmming theory, push any coding boundaries,or present any coding that forces me outside of my oh-so-cozy comfort zones. None of that stuff has really ever interested me anyway. This is just another in my long-running series of real-world problem-solving articles, so don't expect any epiphanies, realizations, wonderous revelations, or even the slightest ah-hah moment. I don't generally have time to experiment, so most of the coding is done simply because it's necessary.

The Download

The download includes compiled binaries for both the debug and release versions, as well as the source code. While I maintain the project in a private GitHub repository (lesson learned as a result of the massive source code loss in May 2020), I removed the GitHub source control files from the solution. At work, we use TFS, so the git files would probably conflict anyway.

For My Job

There is no work-related proprietary code or data contained in this article or associated project file(s).

The Application

The purpose of this application is to generate source code files for entity models, and optionally entity viewmodels (ostensibly for WPF apps), based on the contents of your database. If you're at all familiar with the built-in ADO.Net project template, this app is somewhat similar, except that a) it actually works, and b) I assume that you're going to want to somehow augment the generated source code, which means all of the generated entities are defined as partial classes, and critical properties are generated as virtual. I also enable a certain amount of configurability regarding what code is generated, and how.

In order to make sure the user performs certain prerequisite steps in the appropriate sequence, the app's main window is presented as a wizard (see the referenced link in the Introduction section above).

Disclaimer: I cannot - and more importantly WILL not - be held responsible if your SQL formatting sucks donkey balls and causes data to be corrupted in your database. My advice is to create a backup of your database before using this application, or AT LEAST inspect stored procs before trying to generate code.

Features

There are a number of convenient features implemented in the app. (This is not an exhaustive list.)

  • The generated code uses alias types, such as int, and string. EntityFactory allows you to aternately select the use of System types, such as System.Int32 or System.String, as well as being able to generate nullable types.
     
  • Toggle the use of #region (usually used around blocks of fields and properties.
     
  • Toggle the generation of comments (including intellisense comments).
     
  • Specify your own namespace for generated models and viewmodels. In my experience, models and viewmodels are usually all found in a given folder (with an appropriate name) in the source code hierarchy.
     
  • Specify the class name prefix for generated models and viewmodels. The default prefix for models is "Entity", and the default prefix for viewmodels is "VM". So, a generated model class name will look like "EntityClassName", and the associated viewmodel (if generated) class name will look like "VMEntityClassName". File names associated with these classes will reflect the generated class name
     
  • Toggle the generation of CRUD properties for tables which represent query text for get, insert, update, and delete operations.
     
  • Toggle the creation of the SqlParameter[] property in model classes. This property is intended for use with the optional CRUD properties, but can be used elsewhere in your code.
     
  • Specify the field name prefix for viewmodel classes.
     
  • Allows editing of interface implementations for INotifyPropertyChanged and IDataErrorInfo
     
  • Supports generation data annotations. At this time, the only data annotation that can be generated automagically is StringLength for string properties. However, since the properties can optionally be generated as virtual, it should be easy to override them with appropriate annotations in the ineriting class.
     

Basic Wizard Layout

Most of the interface is presented in the form of a wizard. The reason is that I wanted to make sure that certain criteria were met before the user jumped into the act of generating code. The Settings page contains a tab control, and I went so far as to make sure the user is aware of that because I didn't feel that it jumped off the page visually.

The Introduction Page

Intro Page

This page provides a brief orientation regarding what the app is for and what the user should expect. Beyond that, the most interesting part of the page is the "Organization Info" panel. The info displayed in this panel is contained in the app.config file, and is manually inserted into this file. This is because it's intended to display code generation info specific to your organization. The info displayed in the screen shot is for my organization. If you don't want to display this panel, simply don't provide any text in the CompanyNotice field in the app.config file.

The Setttings Page

This page allows the user to set criteria for connecting to the database server, and when generating classes and files. This page presents several tabs.

At any time, you can elect to reset a given tab (or all tabs) to the application's default values.

Settings Page, Database

Database Server Tab

This settings tab allows the user to specify database server login criteria. As with SSMS, the user specifies the server name (or IP), whether or not to use Windows login authentication, and if using sql server authetication, the account name and password required to login to the specified server instance. Credentials are saved *unencrypted* in the settings file.

After the necessary information has been specified, the user can test the database connection by clicking the Test Connection button.

The connection criteria specified here are used throughout the remainder of the application's execution.

Settings Page, General

General Settings Tab

This settings tab allows the user to specify settings that are generally common between the table/view and stored procedure code generation processes.

Include code generation comments - Causes the code generator to include comments in the generated files. Default value is checked.

Use regions around fields and properties - Causes the code generator to surround blocks of fields and properties with #region/#endregion notations. Default value is true.

Use nullable types - Causes the code generator to use nullable types for fields and properties. Default value is unchecked.

Use system types - Causes the code generator to use system types (System.String) as opposed to alias types (string). Default value is unchecked.

Settings Page, Model

Model Settings Tab

This tab allows the user to specify model-specific settings.

Namespace - This is the namespace in which your generated model entities will reside. Default value is "Models".

Class name prefix - This is the prefix used for every model entity. Default value is "Entity".

Inherited class(es) - This is the comma-delimited list of classes which all of your generated entity model classes inherit. Default value is empty.

Inherited class namespace(s) - This is the comma-delimited list of namespaces in which the specified inherited classes reside. A using statment will be generated for each of the specified namespaces. Default value is empty.

Add CRUD properties (for tables) - This causes the code generatior to create CRUD properties for the generated entity. CRUD properties that are generated include get, insert, update, and delete. Default value is unchecked. (CRUD properties are only generated for tables.)

Separate insert and update CRUD properties - By default, creating CRUD properties will generate an "upsert" statement, which combines the update and insert query into a single query. Checking this box will cause the update and insert queries to be separated into their own discreet properties. Default value is unchecked.

Make properties virtual - In the interest of making the generated model code as flexible as possible, this checkbox allows the user to make all discovered schema properties as virtual. This will allow you to inherit the generated class, ostensibly to override the discovered properties with data annotation attributes. Default is unchecked

Annotate properties where possible - This checkbox allows the generated code to include data annotaions for strings, so that the property value can be constrained to a specific length. If the property is virtual, you can even override the data annotation to specify a minimum length, although this would be infinitely more useful on the viewmodel side. Default is checked.

Add SQLParameter[] property - This is a property which returns a SqlParameter array that includes all discovered properties. This precludes you from having to write this code yourself, and can be especially helpful when using the CRUD query properties below. Default value is checked.

SqlParameter[] property name - This is the desired name of the SqlParameter[] property. Default value is "AsSqlParameters".

Settings Page, Viewmodel

Viewmodel Settings Tab

This tab allows the user to specify viewmodel-specific settings. It probably needs to be said that viewmodels (that use INotifyPropertyChanged and IDataErrorInfo) are generally only found n WPF applications, BUT without those two interfaces, a "viewmodel" is still a viable construct in web apps, especially if you're concerned about separation of concerns, or want a line of debarkation between what you get from the database, and how it's used/modified in your web app.

Namespace - This is the namespace in which your generated model entities will reside. Default value is "Viewmodels".

Class name prefix - This is the prefix used for every model entity. Default value is "VM". This prefix is pre-pended to the generated model classname, so "EntityClassName" would become "VMEntityClassname".

Inherited class(es) - This is the comma-delimited list of classes which all of your entity model classes inherit. Default value is empty.

Inherited class namespace(s) - This is the comma-delimited list of namespaces in which the specified inherited classes reside. A using statment will be generated for each of the specified namespaces. Default value is empty.

Field name prefix - This is the prefix to be used on generated field names. Generally, you can't count on all column names returned in a dataset to be consistently cased, so the only viable solution was to allow the user to specify a prefix for field names in a MVVM scenario. Default valus is "vm_".

Make properties virtual - In the interest of making the generated viewmodel code as flexible as possible, this checkbox allows the user to make all discovered schema properties as virtual. This will allow you to inherit the generated class, ostensibly to override the discovered properties with data annotation attributes. Default is unchecked.

Annotate properties where possible - This checkbox allows the generated code to include data annotaions for strings, so that the property value can be constrained to a specific length. If the property is virtual, you can even override the data annotation to specify a minimum length, although this would be infinitely more useful on the viewmodel side. Default is checked.

Settings Page, Snippets

Code Snippets Tab

This tab allows you to specify specific code snippets used in the code generation process.

Standard Model Usings - This code snippet contains the standard using statements that are included in a generated model file.

Standard Viewmodel Usings - This code snippet contains the standard using statements that are included in a generated viewmodel file.

IDataErrorinfo Implementation - This code snippet represents the implementation for the IDataErrorInfo interface.

INotifyPropertyChanged Implementation - This code snippet represents the implementation of the INotifyPropertyChanged interface.

The Select Database Page

Settings Page, Snippets
Settings Page, Snippets

This page allows/requires the user to select the database from which he will be generating code. The first thing the user needs to do is to click the Discover Databases button. This will probe the specified server, and cause the combo box to be populated.

When a database is selected, the Next button will be enabled. When you click the Next button, the user can actually start generating entity classes.

Discovering Database Components

There are three types of database components that produce a DataTable result set via ADO - tables, views, and stored procedures. Tables and views are essentially the same thing, but stored procedures can, and often do, utilized parameters to refine the returned dataset, so you will have an opportunity to specify stored proc parameters when generating code for one store proc at a time. To a certain extent, the remaining wizard pages and windows present similar info, with stored procedures having a couple more controls to allow reasonable processing with regards to their parameters. The following screen shots and supporting narrative will feature stored procedures, but will also show the table/view versions of those wizards/windows in the interest of completeness.

Keep in mind that the pages and windows presented here may not accurately reflect the latest and greatest version of the code, but the differences should be reasonably minimal.


Settings Page, Snippets
Settings Page, Snippets

The only real difference between discovering tabes/views, and discovering stored procs is the contents of the list view that displays the discovered components. For tables/views, I figured it was important to show the component type (table or view), and for stored procs, it was important to show whether or not the stored proc returned a data set result, and whether or not the stored proc accepted parameters. Regardless of the component type, it was necessary to show the status of the code generation status.

For both wizard pages, the user can tooggle the display of the help panel on/off using the checkbox at the top/right corner of the page. The toggle status remains in effect until the user either resets the app settings to default, or checks the checkbox.


Settings Page, Snippets
Settings Page, Snippets

After clicking the Discover button, the listview is populated. Once populated, list view items provide a context menu that permits viewing certain aspects of the item. For all components the user view the generated code (once the code is generated in the current session). For stored procs, the user can also inspect the item to see how the Ret Data and Has Params columns were populated.

Essentially, the stored proc's body is "normalized", removing white space, blank lines, and comments, and then the application looks for certain SQL commands that select or otherwise manipulate data. If there is any instance of the commands DROP, DELETE, INSERT, UPDATE, or MERGE, the application will show that stored proc as not returning data. The reason is that you probably don't want to be running stored procs that modify the database with fake data in the parameters. This is merely a protective measure.

Generating Code For Selected Component

Settings Page, Snippets
Settings Page, Snippets

When the user elects to generate code for a single component, this window is presented. It allows the user to generate both the model and the viewmodel (if desired) for the associated component. Most of the settings exist on both sides of the form for models and viewmodels.

Create model class - When checked, the model class will be generated when the user clicks the Generate button. Default is checked.

Save files here - The path where the user wishes to save the generated model file. You can use the associated browse button to avoid having to type the path name.

Overwrite existing model files - If checked, causes the app to overwrite an existing generated file. Default is checked.

Decorate with attribute - Decorates all discovered properties with the specified attributes. This field requires that you specify syntactically correct code.

Generate Files button - Causes the selected entity code to be generated and saved according to the criteria specified above.


Settings Page, Snippets

When the user clicks the Set Stored Proc Parameters... button, this window is presented. You've probably already noticed that it is similar to the window you see when you execute a stored proc from the component tree in SSMS. It works the same way as well.

You can set each parameter individually, and fter you've done that, click the Execute Stored Proc button. When the stored proc is finished executing, the schema of the dataset it returns will be displayed in the Returned Dataset Schema list view on the right side of the window. This does NOT cause code to be generated, it is only intended as an informational display. However, the parameters you specify here will be used when you generate code in the prior window.


Settings Page, Snippets
Settings Page, Snippets

When the code is generated, the files are created in the the specified path, and the generated code is presented in the area below the Generate Files button. The code presented can be highlighted and copied to the clipboard if desired. The blue bar between the two source listings is  splitter which allows you to widen the desired listing.


Settings Page, Snippets
Settings Page, Snippets

When you dismiss the form, you are returned to the wizard page, and the list view is updated to reflect the status of the generated code. After you've generated code for a given item, you can inspect the generated code by right-clicking the desired item and clicking Inspect Generated Code in the subsequent context menu. This is context menu item is only enabled if the code was been generated successfully.

Generating Code For All Components

Settings Page, Snippets
Settings Page, Snippets

When the user elects to generate code for all discovered components, this window is presented. Due to the fact that the many components are being processed, it's not feasible to present the same interface that we saw when generating code for a single selected component. To make the process as unobtrusive and generic as possible, the classes that are created are presented as viewable file names for both models and viewmodels. Simply click on the desired file, and click the associated View File button.

For stored procs, the user won't be able to specify parameter values, so the results may be a bit wonky if the result set depends on specific parameter values. Of course, that depends entirely on how the stored proc is written. Note that only stored procs that were determined to return data are included in list of components to be processed.


Settings Page, Snippets
Settings Page, Snippets

Once the code is generated, the left-most listbox will be updated to show the generation status of each component. A green background indicates success, and a red background indicates failure.


Settings Page, Snippets
Settings Page, Snippets

When the user dismisses the Generate All window, the wizard page will be updated to reflect the generation status of each of the processed components.

Inspecting Items and Code

Settings Page, Snippets

Stored proc source code is evaluated AFTER the code has been normalized. The normalization process removes all leading whitespace, comments, and blank lines. This means source code displayed in this window will not retain it's formatting, so be prepared to see it that way (don't worry, the app doesn't modify the source code in any way as it exists in the database). In the name of convenience, the displayed code can be copied into the clipboard.

Keep in mind that the application's ability to evaluate the stored proc code depends almost entirely on the way the stored proc is written. In other words, using reasonable formatting practices in your stored proc code will almost always result in a better evaluation experience.

Settings Page, Snippets

The user can also inspect the generated code. This is especially useful when all of the discovered components are processed at once. Simply right click the desired component, and then click Inspect Generated Code. This window will be presented. This is merely a method for checking what was generated, and beyond being able to copy the code to the clipboard, there's nothing special about this window.

Generating Code For Queries (New Feature, 2021.01.01)

Specify query
Specify query

After posting the article, it occurred to me that there may be times when the user may want to generate entities for a plain text query. To implement this feature, a new wizard page was inserted as the first generation page. Beyond the general characteristics that are identical on the other generation pages, the notable features are:

  • Class name - Specify the entity class name. The class name will be used as the file name (if the user elects to save the query to a file), so the specificed class name must only contain characters that comply with valid file naming rules, as well as c# naming rules (fortunately, these two requirements don't conflict). To make it easy on the user, the name is validated as he types.
     
  • SQL Query Text - This field is where you actually specify the query. There is no built-in WYSIWYG valiation that occurs as you type, and there's not built-in verification that all of the tabels, views, or columns do indeed exist in the selected database. It would takes months to fully develop and debug, and I simply don't care enough to do it.M

    My advice is to write your query in SSMS, because it has all the syntax highlighting you might want, not to mention the built-in ability to determine the validity of table/view names, as well as their column names. Once you've finished your query in SSMS, save it as a file, and load that file into Entity Factory.
     
  • Load Button - Allows the user to load a sql query from a .SQL file.
     
  • Save Button - Allows the user to save the currently specified query to a .SQL file. This button is disabled until both the entity name and SQL query contain valid text.
     
  • Validate SQL button - Performs syntax and database validation of the specified SQL query. This button is disabled until both the entity name and SQL query contain valid text.
     
  • Generate button - Presents the code generation window. This button is disabled until the specified data are validated (using the Vlaidate SQL button).
     
Settings Page, Snippets

This window is identical to the window you see for tables/views. I won't bother going over the minor differences because they don't have any affect on what the window does.

Some Examples of Generated Code

The following are examples of a model and viewmodel code generated by the application. In the following examples, comments, regions, and CRUD properties are turned on. Furthermore, data annotations and virtiualized properties are also turned on.

The model:

C#
//==================================================================================================
// Source object: AEF2.dbo.Alerts (table)
// File generated: 2020.12.09, 07:32:10

// This file was generated by EntityFactory. Do not modify this file. When regenerated, the file    
// may be overwritten if it already exists and EntityFactory was configured to overwrite existing   
// files). Note that the generated class is "partial", so if you need to augment the generated    
// code, create a new partial extension file and put your custom code in that new file.             

// The class name indicates the model (or viewmodel) class name prefix followed by the name of the  
// original database object from which the class was generated.                                     
//==================================================================================================

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

// Auto-incrementing column detected - add the following using to support standard C# attributes.
// If you haven't already, add a reference to System.ComponbentModel.DataAnnotations to the 
// applicable assembly in your application.
using System.ComponentModel.DataAnnotations;

namespace Models
{
	public partial class EntityAlerts
	{
		#region entity properties

		[Key]
		public virtual int      ID               { get; set; }
		[StringLength(32)]
		public virtual string   AlertName        { get; set; }
		[StringLength(128)]
		public virtual string   AlertTitle       { get; set; }
		[StringLength(2147483647)]
		public virtual string   AlertMsg         { get; set; }
		public virtual DateTime AlertStartDate   { get; set; }
		public virtual DateTime AlertExpireDate  { get; set; }
		public virtual int      AlertType        { get; set; }
		public virtual int      AlertClassLevel  { get; set; }
		[StringLength(512)]
		public virtual string   AlertApps        { get; set; }
		[StringLength(64)]
		public virtual string   ActionName       { get; set; }
		[StringLength(64)]
		public virtual string   ControllerName   { get; set; }
		public virtual DateTime DateAdded        { get; set; }
		public virtual int      AddedByUMSUserID { get; set; }

		#endregion entity properties

		public SqlParameter[] SqlParameters
		{
			get
			{
				return new SqlParameter[]
				{
					new SqlParameter("@ID"              , ID              ),
					new SqlParameter("@AlertName"       , AlertName       ),
					new SqlParameter("@AlertTitle"      , AlertTitle      ),
					new SqlParameter("@AlertMsg"        , AlertMsg        ),
					new SqlParameter("@AlertStartDate"  , AlertStartDate  ),
					new SqlParameter("@AlertExpireDate" , AlertExpireDate ),
					new SqlParameter("@AlertType"       , AlertType       ),
					new SqlParameter("@AlertClassLevel" , AlertClassLevel ),
					new SqlParameter("@AlertApps"       , AlertApps       ),
					new SqlParameter("@ActionName"      , ActionName      ),
					new SqlParameter("@ControllerName"  , ControllerName  ),
					new SqlParameter("@DateAdded"       , DateAdded       ),
					new SqlParameter("@AddedByUMSUserID", AddedByUMSUserID),
				};
			}
		}

		#region database properties

		public virtual string CRUDGet
		{
			get
			{
				return "SELECT [ID], [AlertName], [AlertTitle], [AlertMsg], [AlertStartDate],"
						+" [AlertExpireDate], [AlertType], [AlertClassLevel], [AlertApps], [ActionName],"
						+" [ControllerName], [DateAdded], [AddedByUMSUserID] FROM [dbo].[Alerts]"
						+" WITH(NOLOCK);";
			}
		}

		public virtual string CRUDUpsert
		{
			get
			{
				return "UPDATE [dbo].[Alerts] SET [AlertName] = @AlertName , [AlertTitle] ="
						+" @AlertTitle , [AlertMsg] = @AlertMsg , [AlertStartDate] = @AlertStartDate ,"
						+" [AlertExpireDate] = @AlertExpireDate , [AlertType] = @AlertType ,"
						+" [AlertClassLevel] = @AlertClassLevel , [AlertApps] = @AlertApps , [ActionName]"
						+" = @ActionName , [ControllerName] = @ControllerName , [DateAdded] = @DateAdded"
						+" , [AddedByUMSUserID] = @AddedByUMSUserID WHERE [ID] = @ID;"
						+" IF @@ROWCOUNT = 0"
						+" INSERT INTO [dbo].[Alerts] ( [AlertName] , [AlertTitle] , [AlertMsg] ,"
						+" [AlertStartDate] , [AlertExpireDate] , [AlertType] , [AlertClassLevel] ,"
						+" [AlertApps] , [ActionName] , [ControllerName] , [DateAdded] ,"
						+" [AddedByUMSUserID]) VALUES ( @AlertName , @AlertTitle , @AlertMsg ,"
						+" @AlertStartDate , @AlertExpireDate , @AlertType , @AlertClassLevel ,"
						+" @AlertApps , @ActionName , @ControllerName , @DateAdded , @AddedByUMSUserID);";
			}
		}

		public virtual string CRUDDelete
		{
			get
			{
				return "DELETE FROM [dbo].[Alerts] WHERE [ID] = @ID;";
			}
		}


		#endregion database properties

	} // end class

} // end namespace

The viewmodel:

C#
//==================================================================================================
// Source object: AEF2.dbo.Alerts (table)
// File generated: 2020.12.09, 07:39:31

// This file was generated by EntityFactory. Do not modify this file. When regenerated, the file    
// may be overwritten if it already exists and EntityFactory was configured to overwrite existing   
// files). Note that the generated class is "partial", so if you need to augment the generated    
// code, create a new partial extension file and put your custom code in that new file.             

// The class name indicates the model (or viewmodel) class name prefix followed by the name of the  
// original database object from which the class was generated.                                     
//==================================================================================================

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

// The following using was added to support the INotifyPropertyChanged and/or IDataErrorInfo interfaces.
using System.ComponentModel;

// The following using was specified in the Model Namespace setting.
using Models;

// The following usings were specified in the Inerited Namespaces setting.
using WpfCommon;

//----------- Generated from database object dbo.Alerts --------------//

namespace ViewModels
{

	public partial class VMEntityAlerts : INotifyPropertyChanged, IDataErrorInfo
	{
		#region INotifyPropertyChanged implementation

		/// <summary>
		/// Occurs when a property value changes.
		/// </summary>
		public event PropertyChangedEventHandler PropertyChanged;
		
		/// <summary>
		/// Notifies that the property changed, and sets IsModified to true.
		/// </summary>
		/// <param name="propertyName">Name of the property.</param>
		protected void NotifyPropertyChanged([CallerMemberName] String propertyName = "")
		{
			if (this.PropertyChanged != null)
			{
				this.PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
				if (propertyName != "IsModified")
				{
					this.IsModified = true;
				}
			}
		}
		

		#endregion INotifyPropertyChanged implementation

		#region fields

		private int      vm_ID;
		private string   vm_AlertName;
		private string   vm_AlertTitle;
		private string   vm_AlertMsg;
		private DateTime vm_AlertStartDate;
		private DateTime vm_AlertExpireDate;
		private int      vm_AlertType;
		private int      vm_AlertClassLevel;
		private string   vm_AlertApps;
		private string   vm_ActionName;
		private string   vm_ControllerName;
		private DateTime vm_DateAdded;
		private int      vm_AddedByUMSUserID;

		#endregion fields

		#region properties

		/// <summary>
		/// Model (generated from database source object)
		/// </summary>
		protected EntityAlerts Model { get; set; }

		/// <summary>
		/// Get/set ID
		/// </summary>
		public virtual int ID
		{
			get { return this.vm_ID; }
			set
			{
				if (value != this.vm_ID)
				{
					this.vm_ID = value;
					this.NotifyPropertyChanged();
				}
			}
		}

		/// <summary>
		/// Get/set AlertName
		/// </summary>
		[StringLength(32)]
		public virtual string AlertName
		{
			get { return this.vm_AlertName; }
			set
			{
				if (value != this.vm_AlertName)
				{
					this.vm_AlertName = value;
					this.NotifyPropertyChanged();
				}
			}
		}

		/// <summary>
		/// Get/set AlertTitle
		/// </summary>
		[StringLength(128)]
		public virtual string AlertTitle
		{
			get { return this.vm_AlertTitle; }
			set
			{
				if (value != this.vm_AlertTitle)
				{
					this.vm_AlertTitle = value;
					this.NotifyPropertyChanged();
				}
			}
		}

		/// <summary>
		/// Get/set AlertMsg
		/// </summary>
		[StringLength(2147483647)]
		public virtual string AlertMsg
		{
			get { return this.vm_AlertMsg; }
			set
			{
				if (value != this.vm_AlertMsg)
				{
					this.vm_AlertMsg = value;
					this.NotifyPropertyChanged();
				}
			}
		}

		/// <summary>
		/// Get/set AlertStartDate
		/// </summary>
		public virtual DateTime AlertStartDate
		{
			get { return this.vm_AlertStartDate; }
			set
			{
				if (value != this.vm_AlertStartDate)
				{
					this.vm_AlertStartDate = value;
					this.NotifyPropertyChanged();
				}
			}
		}

		/// <summary>
		/// Get/set AlertExpireDate
		/// </summary>
		public virtual DateTime AlertExpireDate
		{
			get { return this.vm_AlertExpireDate; }
			set
			{
				if (value != this.vm_AlertExpireDate)
				{
					this.vm_AlertExpireDate = value;
					this.NotifyPropertyChanged();
				}
			}
		}

		/// <summary>
		/// Get/set AlertType
		/// </summary>
		public virtual int AlertType
		{
			get { return this.vm_AlertType; }
			set
			{
				if (value != this.vm_AlertType)
				{
					this.vm_AlertType = value;
					this.NotifyPropertyChanged();
				}
			}
		}

		/// <summary>
		/// Get/set AlertClassLevel
		/// </summary>
		public virtual int AlertClassLevel
		{
			get { return this.vm_AlertClassLevel; }
			set
			{
				if (value != this.vm_AlertClassLevel)
				{
					this.vm_AlertClassLevel = value;
					this.NotifyPropertyChanged();
				}
			}
		}

		/// <summary>
		/// Get/set AlertApps
		/// </summary>
		[StringLength(512)]
		public virtual string AlertApps
		{
			get { return this.vm_AlertApps; }
			set
			{
				if (value != this.vm_AlertApps)
				{
					this.vm_AlertApps = value;
					this.NotifyPropertyChanged();
				}
			}
		}

		/// <summary>
		/// Get/set ActionName
		/// </summary>
		[StringLength(64)]
		public virtual string ActionName
		{
			get { return this.vm_ActionName; }
			set
			{
				if (value != this.vm_ActionName)
				{
					this.vm_ActionName = value;
					this.NotifyPropertyChanged();
				}
			}
		}

		/// <summary>
		/// Get/set ControllerName
		/// </summary>
		[StringLength(64)]
		public virtual string ControllerName
		{
			get { return this.vm_ControllerName; }
			set
			{
				if (value != this.vm_ControllerName)
				{
					this.vm_ControllerName = value;
					this.NotifyPropertyChanged();
				}
			}
		}

		/// <summary>
		/// Get/set DateAdded
		/// </summary>
		public virtual DateTime DateAdded
		{
			get { return this.vm_DateAdded; }
			set
			{
				if (value != this.vm_DateAdded)
				{
					this.vm_DateAdded = value;
					this.NotifyPropertyChanged();
				}
			}
		}

		/// <summary>
		/// Get/set AddedByUMSUserID
		/// </summary>
		public virtual int AddedByUMSUserID
		{
			get { return this.vm_AddedByUMSUserID; }
			set
			{
				if (value != this.vm_AddedByUMSUserID)
				{
					this.vm_AddedByUMSUserID = value;
					this.NotifyPropertyChanged();
				}
			}
		}


		#endregion properties

		#region constructors

		/// <summary>
		/// Default constructor
		/// </summary>
		public VMEntityAlerts()
		{
		}

		/// <summary>
		/// Default constructor
		/// </summary>
		/// <summary>
		/// Default constructor
		/// </summary>
		public VMEntityAlerts(EntityAlerts model)
		{
			this.Model = model;
		}

		#endregion constructors

	} // end class

} // end namespace

The Code, Such As It Is

Originally, I was going to make this a two-part article, but truthfully, who wants to read details about code that you can find in your average run-of-the-mill desktop application? Afterall, it's all there in orange and white, and presented as a link. However, I will go ahead and talk about the technology stack a little so you can determine if you want to go over the source code on your own time.

This isn't necessarily a complex app in terms of .Net, WPF, and ADO implementations, but I do expect you to have a certain amount of experience these things. Be it known, that this article will NOT strive to teach basic concepts. It merely describes the techniques used, and requires you to be a self-starter if you need further help understanding these techniques. (If I tried to explain everything in such detail for the rank beginner, ths would probably end up being a 20-part article series, and I'm simply not interested in doing that.)

I consider the code to be reasonably well commented, but there may be lapses in coverage because there are a LOT of files, and I may have missed some (or a lot).

As far as code quality, I admit that things could probably be organized better, or implemented differently, and I didn't necesarily go to heroic efforts to adhere to "best practice". Once again, most of the code is knee-jerk reactions to my ideas of what the program should contain, and my techniques evolved quite a bit during the process of writing the code. Add to that the source code loss catastrophe, and implementations may have changed wildly from one day to the next.

Technology Stack

  • Visual Studio 2017 - When I started the code for this project, VS2017 was the latest release version available. VS2019 became available as a release candidate shortly afterwards, but we all know better than to commit a code base to a new piece of software from Microsoft. At this time, VS2019 is the latest and greatest (actually having been offcially released), and if you want to convert this app to .Net Core, you really should use VS2019 and just move to .Net 5.0.
     
  • .Net Framework 4.72 - I am not a fan of .Net Core, and WPF support was dicey at the time I started this code. From what I understand, WPF support has stabilized under .Net 5, but I have no experience in that area. Yet.
     
  • ADO.Net - I use ADO because I like it, and I understand it. I have a lot of code written around it, and I didn't want to give it up in exchange for inflexible generic convenience code. Just say no to ORMs
     
  • Windows Presentation Foundation (WPF) - when WPF was released in 2009, I was resistent to its adoption in my own code, but now, in 2020, coupled with the adoption of MVVM, I can't imagine any other desktop development solution if you need an attractive and robust UI.
     
  • Model View Viewmodel (MVVM) pattern - while being more tedious to implement in your applications, it certainly enforces separation of concerns. It's just a good idea.
     
  • Singleton pattern - singletons are instantiate objects that are statically available throughout your application. They're called singletons because they're only instantiated one time during the life of the appliacation session. For ease of access, I have properties defined in whatever objects need access to these singleon objects. This allows me to refer to them as follows:
    C#
    if (this.AppSettings.SomeProperty == someValue){}
  • Reflection - Reflection is used to inspect and manipulate objects defined in your application. With reflection, you can determine property names, types, accessibility, or even inject funtionality into an obect at runtime (this app doesn't do any injection). This technique is used throughout the code. To see it in action, look for instances of PropertyInfo in the solution.
     
  • Generic DAL for ADO.Net - A Revisit  [ ^ ] - This article discusses my generic DAL object, and how to use it.
     
  • Another Take on a WPF Wizard  [ ^ ] - This article describes the code used to create the wizard used in this article.
     
  • Customizable WPF MessageBox  [ ^ ] - This article describes a customizable version of the standard WPF MessageBox.
     

Code Organization

Code is organized into folders containing model objects, viewmodel objects, windows, and wizard pages. There are very few files that actually live in the root project folder, so everything is pretty tidy.

WPF

I am of the belief that just because you can do something doesn't mean you have to, or even should. There are all kinds of things you can do in XAML, but in my humble opinion, it just makes things harder to figure out when you're in forensics mode, bacause you can't stop the debugger in a chuck of XAML code. I like to do a lot of stuff in the C# code for this very reason.

All of the windows are derived from a base NotifiableWindow class (found in the WPFCommon assembly). This is a Window class that inherits the INotifyPropertyChanged interface, which makes it easy to implement properties that a window can use to update the UI. I do this a lot in my WPF apps. When you inherit from a based class in WPF, you have to change your XAML to account for it.

XAML
<wpfctrl:WizardWindowBase x:Class="EntityFactory.MainWindow"
    xmlns...
    xmlns:wpfctrl="clr-namespace:WpfCommon.Controls;assembly=WpfCommon"

You have to add a xml namespace (xmlns) entry that declares the assembly (referenced in your application), and change the main tag to reflect the custom base class (qiualified by the xml namespace name you assigned).

Other WPF techniques include custom templates, use of static and dynamic resources, converters, and binding properties in one element to a property in another (named) element. For instance, I often bind the button Width to the ActualWidth of the button that will be the widest (based on the text in the button's Content. That technique can be seen in the WizPgTablesViews class.

The Work of Generating Entities

This is the reason the app exists - to generate code from a dataset returned from a table, view, stored procedure, or user query. Before we can generate any code, we have to know what data we're going to be retrieving from the database. The mechanism for doing so is the ADO DAL (for more info on how this works, refer to the DAL article cited above), and is handled via the BLL object, found in the database folder in the project.

The first step is to discover the database components that are contained in the database. For tables and views, that's where the discovery process ends, but stored procedures require more work. First, we have to discover any parameters that might be speicfied for a given stored procedure. Next, we have to etsablish whether or not the stored procedure actually returns a dataset.

Essentially, we query the database in order to create/return a DataTable that represents the returned dataset. For tables and views, a simple SELECT TOP(0)* FROM [table/view] query is used to retrieve data from a table or view. A stored procedure requires a more nuanced approach.

A stored procedure can perform many actions in a database. If there's any chance that a given stored procedure will somehow alter a table (using DROP, DELETE, INSERT, UPDATE, MERGE, or TRUNCATE), it is marked as not returning data. This is to protect the data in the database from erroneous data or removing data altogether. If a discovered stored procedure is deermined to return data, it's actually executed in order to create the necessary DataTable that can then be parsed.

Once we have a DataTable, the app can use that DataTable to determine column names, data types, and other data regarding the column. It uses this information to create property definitions in the generated code.

In the interest of full displosure, support for tables and views was an afterthought because where I work (and in my own perf\sonal approach to database access), we only use stored procedures to retrieve data because our selection criteria is very nucanced and specific to a particular need. We don't have any code that directly queries a table or view. The decision to write an article for CodeProject caused me to rethink the entire design of the application, and many of the features you see today are a direct result of this change in deisgn direction. You're welcome.

UPDATE 2021.01.02 - In the interest of even fuller disclosure, support for user queries was an after afterthought. I figured some folks might simply want to hand-craft queries for specific results. Once again, you're welcome.

Points of Interest

Once again, this application is intended for use by programmers that do not use an ORM framework. However, that doesn't preclude its use to augment such a system. I can't think of any use case that would benefit from it, but I'm only one person with a very narrow (and biased) view of the world. Who knows? This application could actually even push more programmers into using an ORM. I suppose stranger things have happened.

If you see a problem with something I've done, let me know in the comment section. I'll try to address it one way or the other in a fairly timely fashion. Look for a quick update to fix spelling errors I'm sure to notice after this article goes live.

UPDATE 2021.01.02

I toyed with adding AvalonEdit to the app to provide syntax highlighting, but it simply added too much work, and I determined that the juice simply wasn't worth the squeeze. If you're in the mood to wrangle this task, be my guess. HEADS UP - If you're not going to convert the code to .Net Core, you will be limited to using some version of the control that is EARLIER than 6.0.

I tried for a week to get IDataErrorInfo to work for the new user query wiazrd page, but there is some hidden (and very wonky) interaction going on that involves my wizard page code that prevents it from workinging reliably, and I didn't feel like tracking it down, so I went around the problem and got something reasonable to work. If you're interested, the code is in WizPgQuery.*.

History

  • 2021.01.02 - Update to add user query generation as well as numerous spelling corrections in the article
     
  • 2020.12.09 - Initial publication
     

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

#realJSOP
Software Developer (Senior) Paddedwall Software
United States United States
I've been paid as a programmer since 1982 with experience in Pascal, and C++ (both self-taught), and began writing Windows programs in 1991 using Visual C++ and MFC. In the 2nd half of 2007, I started writing C# Windows Forms and ASP.Net applications, and have since done WPF, Silverlight, WCF, web services, and Windows services.

My weakest point is that my moments of clarity are too brief to hold a meaningful conversation that requires more than 30 seconds to complete. Thankfully, grunts of agreement are all that is required to conduct most discussions without committing to any particular belief system.

Comments and Discussions

 
QuestionWhy this instead of SqlMetal? Pin
Member 149344644-Jan-21 10:17
MemberMember 149344644-Jan-21 10:17 
AnswerRe: Why this instead of SqlMetal? Pin
#realJSOP4-Jan-21 12:18
mva#realJSOP4-Jan-21 12:18 
GeneralRe: Why this instead of SqlMetal? Pin
Member 149344644-Jan-21 12:36
MemberMember 149344644-Jan-21 12:36 
QuestionTO-DO... Pin
#realJSOP23-Dec-20 2:39
mva#realJSOP23-Dec-20 2:39 
GeneralNice Job Pin
bcostell0200013-Dec-20 5:13
Memberbcostell0200013-Dec-20 5:13 
GeneralRe: Nice Job Pin
#realJSOP13-Dec-20 8:17
mva#realJSOP13-Dec-20 8:17 
GeneralMy vote of 5 Pin
Mark Miller11-Dec-20 10:15
MemberMark Miller11-Dec-20 10:15 
GeneralRe: My vote of 5 Pin
#realJSOP13-Dec-20 23:23
mva#realJSOP13-Dec-20 23:23 
QuestionAdapting for ASP.NET Pin
DumpsterJuice11-Dec-20 6:54
MemberDumpsterJuice11-Dec-20 6:54 
AnswerRe: Adapting for ASP.NET Pin
#realJSOP11-Dec-20 7:38
mva#realJSOP11-Dec-20 7:38 
QuestionBinding Error Pin
#realJSOP11-Dec-20 3:18
mva#realJSOP11-Dec-20 3:18 
QuestionConverting to Core Pin
#realJSOP11-Dec-20 2:25
mva#realJSOP11-Dec-20 2:25 
QuestionFormatting and images Pin
#realJSOP9-Dec-20 12:28
mva#realJSOP9-Dec-20 12:28 
AnswerRe: Formatting and images Pin
Chris Maunder9-Dec-20 14:23
cofounderChris Maunder9-Dec-20 14:23 
GeneralRe: Formatting and images Pin
#realJSOP9-Dec-20 20:36
mva#realJSOP9-Dec-20 20:36 
GeneralRe: Formatting and images Pin
Sacha Barber4-Jan-21 7:24
mvaSacha Barber4-Jan-21 7:24 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.