Click here to Skip to main content
Click here to Skip to main content
Add your own
alternative version

Populating a business logical layer from Stored Procedures

, 29 Oct 2013
A brief look at a technique mapping tables to a basic business layer.
doPersistence-noexe.zip
doPersistence
doPersistence.csproj.user
doPersistence.csproj.vspscc
obj
Debug
Release
Properties
doLogicCode
bin
Debug
doLogicCode.vshost.exe.manifest
doLogicCode.csproj.vspscc
obj
x86
Debug
doLogicCode.frmCodeGenerator.resources
doLogicCode.Properties.Resources.resources
Properties
Settings.settings
doPersistence.zip
Test
doLogic
bin
Debug
doLogic.dll
doLogic.pdb
doPersistence.dll
doPersistence.pdb
Release
doLogic.dll
doLogic.pdb
doPersistence.dll
doPersistence.pdb
obj
Debug
DesignTimeResolveAssemblyReferencesInput.cache
doLogic.dll
doLogic.pdb
ResolveAssemblyReference.cache
TempPE
Release
DesignTimeResolveAssemblyReferencesInput.cache
doLogic.dll
doLogic.pdb
ResolveAssemblyReference.cache
TempPE
Properties
doLogicCode
bin
Debug
doLogicCode.exe
doLogicCode.pdb
doLogicCode.vshost.exe
doLogicCode.vshost.exe.manifest
doPersistence.dll
doPersistence.pdb
obj
x86
Debug
DesignTimeResolveAssemblyReferencesInput.cache
doLogicCode.exe
doLogicCode.frmCodeGenerator.resources
doLogicCode.pdb
doLogicCode.Properties.Resources.resources
GenerateResource.read.1.tlog
GenerateResource.write.1.tlog
ResolveAssemblyReference.cache
TempPE
Properties.Resources.Designer.cs.dll
Properties
Settings.settings
doPersistence
bin
Debug
doPersistence.dll
doPersistence.pdb
Release
doPersistence.dll
doPersistence.pdb
doPersistence.csproj.vspscc
obj
Debug
DesignTimeResolveAssemblyReferencesInput.cache
doPersistence.dll
doPersistence.pdb
TempPE
Release
DesignTimeResolveAssemblyReferencesInput.cache
doPersistence.dll
doPersistence.pdb
TempPE
Properties
Test.suo
TestSite
Account
App_Data
bin
doLogic.dll
doLogic.pdb
doPersistence.dll
doPersistence.pdb
TestSite.dll
TestSite.pdb
Global.asax
obj
Debug
DesignTimeResolveAssemblyReferencesInput.cache
ResolveAssemblyReference.cache
TempPE
TestSite.dll
TestSite.pdb
Release
CSAutoParameterize
original
Account
transformed
Account
Database
DesignTimeResolveAssemblyReferencesInput.cache
Package
PackageTmp
Account
bin
doLogic.dll
doLogic.pdb
doPersistence.dll
doPersistence.pdb
TestSite.dll
TestSite.pdb
Global.asax
Scripts
Styles
ResolveAssemblyReference.cache
TempPE
TestSite.dll
TestSite.pdb
TransformWebConfig
original
transformed
Account
Properties
Scripts
Styles
TestSite.csproj.user
dopersistenceexample.zip
doLogic.dll
doPersistence.dll
doPersistence.dll
doLogic.dll
doPersistence.dll
TestSite.dll
Global.asax
TestSite.csproj.user
dopersistencepocodynamicsqlexample.zip
doLogic.dll
doLogic.pdb
doPersistence.dll
doPersistence.pdb
doLogic.dll
doLogic.pdb
doPersistence.dll
doPersistence.pdb
DesignTimeResolveAssemblyReferencesInput.cache
doLogic.dll
doLogic.pdb
ResolveAssemblyReference.cache
DesignTimeResolveAssemblyReferencesInput.cache
doLogic.dll
doLogic.pdb
ResolveAssemblyReference.cache
doPersistence.dll
doPersistence.pdb
doPersistence.dll
doPersistence.pdb
DesignTimeResolveAssemblyReferencesInput.cache
doPersistence.dll
doPersistence.pdb
DesignTimeResolveAssemblyReferencesInput.cache
doPersistence.dll
Test.suo
doLogic.dll
doLogic.pdb
doPersistence.dll
doPersistence.pdb
TestSite.dll
TestSite.pdb
Global.asax
DesignTimeResolveAssemblyReferencesInput.cache
ResolveAssemblyReference.cache
TestSite.dll
TestSite.pdb
DesignTimeResolveAssemblyReferencesInput.cache
ResolveAssemblyReference.cache
TestSite.dll
TestSite.pdb
TestSite.csproj.user
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

using System.Configuration;

using doPersistence;

namespace doLogicCode
{
	public partial class frmCodeGenerator : Form
	{
		public frmCodeGenerator()
		{
			InitializeComponent();
			prefix = ConfigurationManager.AppSettings["doPersistenceSpPrefix"];
		}

		DataTable codeTable = null;
		string prefix = "";

		private void btnSQLCode_Click(object sender, EventArgs e)
		{
			try
			{
				codeTable = Database.ExecuteDataTable("sys_CodeForTable", new { TableName = txtTableName.Text });

				if (codeTable.Rows.Count == 0)
					throw new ApplicationException("Table not found: " + txtTableName.Text);

				string code = "";
				code += DeleteSP();
				code += "\r\nGO\r\n\r\n";
				code += ListSP();
				code += "\r\nGO\r\n\r\n";
				code += LoadSP();
				code += "\r\nGO\r\n\r\n";
				code += StoreSP();
				code += "\r\nGO\r\n";

				txtCode.Text = code;
				Clipboard.SetText(code);
			}
			catch (System.Data.SqlClient.SqlException se)
			{
				MessageBox.Show(se.Message + "\r\n\r\nThe script to create the database is located in the solution root.");
			}
			catch (Exception ex)
			{
				MessageBox.Show(ex.Message);
			}
		}

		private void btnCSCode_Click(object sender, EventArgs e)
		{
			try
			{
				codeTable = Database.ExecuteDataTable("sys_CodeForTable", new { TableName = txtTableName.Text });

				if (codeTable.Rows.Count == 0)
					throw new ApplicationException("Table not found: " + txtTableName.Text);

				string code = CSCode();

				txtCode.Text = code;
				Clipboard.SetText(code);
			} 
			catch (System.Data.SqlClient.SqlException se)
			{
				MessageBox.Show(se.Message + "\r\n\r\nThe script to create the database is located in the solution root.");
			} 
			catch (Exception ex)
			{
				MessageBox.Show(ex.Message);
			}
		}

		private string DeleteSP()
		{
			string code = "";
			code += "create procedure " + prefix + "delete_" + txtTableName.Text + "\r\n";
			code += "\t" + Convert.ToString(codeTable.Rows[0]["SQLParms"]);

			code = code.Substring(0, code.Length - 1) + "\r\n";	//remove the last comma

			code += "as\r\n";
			code += "\tdelete from\r\n";
			code += "\t\t" + txtTableName.Text + "\r\n";
			code += "\twhere\r\n";
			code += "\t\t" + Convert.ToString(codeTable.Rows[0]["UpdateList"]);

			code = code.Substring(0, code.Length - 1) + "\r\n";	//remove the last comma

			return code;
		}

		private string ListSP()
		{
			string code = "";
			code += "create procedure " + prefix + "list_" + txtTableName.Text + "\r\n";
			code += "as\r\n";
			code += "\tselect\r\n";
			code += "\t\t*\r\n";
			code += "\tfrom\r\n";
			code += "\t\t" + txtTableName.Text + "\r\n";
			
			return code;
		}

		private string LoadSP()
		{
			string code = "";
			code += "create procedure " + prefix + "load_" + txtTableName.Text + "\r\n";
			code += "\t" + Convert.ToString(codeTable.Rows[0]["SQLParms"]);

			code = code.Substring(0, code.Length - 1) + "\r\n";	//remove the last comma

			code += "as\r\n";
			code += "\tselect\r\n";
			code += "\t\t*\r\n";
			code += "\tfrom\r\n";
			code += "\t\t" + txtTableName.Text + "\r\n";
			code += "\twhere\r\n";
			code += "\t\t" + Convert.ToString(codeTable.Rows[0]["UpdateList"]);

			code = code.Substring(0, code.Length - 1) + "\r\n";	//remove the last comma

			return code;
		}

		private string StoreSP()
		{
			string code = "";
			code += "create procedure " + prefix + "store_" + txtTableName.Text + "\r\n";

			foreach (DataRow row in codeTable.Rows)
			{
				code += "\t" + Convert.ToString(row["SQLParms"]) + "\r\n";
				if (row == codeTable.Rows[0])
					code = code.Insert(code.Length - 3, " output");
			}
			code = code.Substring(0, code.Length - 3) + "\r\n";	//remove the last comma

			code += "as\r\n";
			code += "\tif exists(select 1 from " + txtTableName.Text + " ";
			code += "where " + codeTable.Rows[0]["ColumnName"] + " = @" + codeTable.Rows[0]["ColumnName"] + ") begin\r\n";
			code += "\t\tupdate " + txtTableName.Text + " set\r\n";

			foreach (DataRow row in codeTable.Rows)
				if (row != codeTable.Rows[0] && !Convert.ToBoolean(row["AutoIncrement"]))	//skip the first row and nonupdateable
					code += "\t\t\t" + Convert.ToString(row["UpdateList"]) + "\r\n";
			code = code.Substring(0, code.Length - 3) + "\r\n";	//remove the last comma

			code += "\t\twhere\r\n";
			code += "\t\t\t" + codeTable.Rows[0]["ColumnName"] + " = @" + codeTable.Rows[0]["ColumnName"] + "\r\n";
			code += "\tend else begin\r\n";
			code += "\t\tinsert into " + txtTableName.Text + " (\r\n";

			foreach (DataRow row in codeTable.Rows)
				if (row != codeTable.Rows[0] && !Convert.ToBoolean(row["AutoIncrement"]))	//skip the first row and nonupdateable
					code += "\t\t\t" + row["FieldList"] + "\r\n";
			code = code.Substring(0, code.Length - 3) + "\r\n";	//remove the last comma

			code += "\t\t) values (\r\n";

			foreach (DataRow row in codeTable.Rows)
				if (row != codeTable.Rows[0] && !Convert.ToBoolean(row["AutoIncrement"]))	//skip the first row and nonupdateable
					code += "\t\t\t" + row["ParmList"] + "\r\n";
			code = code.Substring(0, code.Length - 3) + "\r\n";	//remove the last comma

			code += "\t\t)\r\n";
			code += "\r\n";
			code += "\t\tset @" + codeTable.Rows[0]["ColumnName"] + " = scope_identity()\r\n";
			code += "\tend\r\n";
			code += "\r\n";
			code += "return @" + codeTable.Rows[0]["ColumnName"] + "\r\n";

			return code;
		}

		private void btnCopyToClipboard_Click(object sender, EventArgs e)
		{
			Clipboard.SetText(txtCode.Text);
		}

		private string CSCode()
		{
			string code = "";

			code += "using System;\r\n";
			code += "using System.Collections.Generic;\r\n";
			code += "using System.Linq;\r\n";
			code += "using System.Text;\r\n";
			code += "\r\n";
			code += "using doPersistence;\r\n";
			code += "\r\n";
			code += "namespace doLogic\r\n";
			code += "{\r\n";
			code += "\t[Persistable(\"" + txtTableName.Text + "\", \"" + codeTable.Rows[0]["ColumnName"] + "\", false)]\r\n";
			code += "\tpublic class " + txtTableName.Text + "\r\n";
			code += "\t{\r\n";
			code += "\t\t//constructors\r\n";
			code += "\t\tpublic " + txtTableName.Text + "() { }\r\n";
			code += "\r\n";
			code += "\t\tpublic " + txtTableName.Text + "(int id)\r\n";
			code += "\t\t{\r\n";
			code += "\t\t\t" + codeTable.Rows[0]["ColumnName"] + " = id;\r\n";
			code += "\t\t\tthis.Load();\r\n";
			code += "\t\t}\r\n";
			code += "\r\n";
			code += "\t\t//persistent properties\r\n";
			foreach (DataRow row in codeTable.Rows)
				code += "\t\t" + row["CSharp"] + "\r\n";

			code += "\r\n";
			code += "\t\tpublic void Load() { Database.Load(this); }\r\n";
			code += "\t\tpublic int Store() { return Database.Store(this); }\r\n";
			code += "\t\tpublic bool Delete() { return Database.Delete(this); }\r\n";
			code += "\t}\r\n";
			code += "}\r\n";

			return code;
		}
	}
}

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

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

About the Author

andrewbb@gmail.com
Architect
United States United States
No Biography provided

| Advertise | Privacy | Mobile
Web02 | 2.8.140721.1 | Last Updated 29 Oct 2013
Article Copyright 2012 by andrewbb@gmail.com
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid