clstable.cs

LLBLGen 1.21 Sourcecode

//////////////////////////////////////////////////////////////////////
// Part of LLBLGen sourcecode. See version information
//////////////////////////////////////////////////////////////////////
// COPYRIGHTS:
// Copyright (c)2002 Solutions Design. All rights reserved.
// 
// Released under the following license: (BSD2)
// -------------------------------------------
// Redistribution and use in source and binary forms, with or without modification, 
// are permitted provided that the following conditions are met: 
//
// 1) Redistributions of source code must retain the above copyright notice, this list of 
//    conditions and the following disclaimer. 
// 2) Redistributions in binary form must reproduce the above copyright notice, this list of 
//    conditions and the following disclaimer in the documentation and/or other materials 
//    provided with the distribution. 
// 
// THIS SOFTWARE IS PROVIDED BY SOLUTIONS DESIGN ``AS IS'' AND ANY EXPRESS OR IMPLIED WARRANTIES, 
// INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A 
// PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL SOLUTIONS DESIGN OR CONTRIBUTORS BE LIABLE FOR 
// ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT 
// NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR 
// BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, 
// STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE 
// USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. 
//
// The views and conclusions contained in the software and documentation are those of the authors 
// and should not be interpreted as representing official policies, either expressed or implied, 
// of Solutions Design. 
//
//////////////////////////////////////////////////////////////////////
// Contributers to the code:
//		- Frans Bouma [FB]
//////////////////////////////////////////////////////////////////////

using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Text;
using System.Collections;

namespace LLBLGen
{
	/// <summary>
	/// Purpose: public enum to be used as the generation type specification of the
	/// stored procedure generated.
	/// </summary>
	public enum eSPGenType:int
	{
		InsertSP,					// Insert Stored Procedure
		UpdateSP,					// Update Stored Procedure
		DeleteSP,					// Delete Stored Procedure
		SelectOnePKSP,				// Select Stored Procedure based on primary key (1 row)
		SelectFKSP,					// Select Stored Procedure based on foreign key(s) (1 row/multiple rows)
		SelectAllSP,				// Select Stored Procedure which will retrieve simply all records ordered by the PK
		SelectOneNonPKSP			// Select Stored Procedures based on all non-PK unique row type fields (unique constraint, identity fields, GUID rows)
	}


	/// <summary>
	/// Purpose: table class. Represents a table from the database.
	/// </summary>
	public class clsTable
	{
		#region Class Member Declarations
		private SqlConnection	m_scoActiveConnection;
		private clsField[]		m_arrfFields;
		private	bool			m_bNETIncludeAutoComplete, m_bNETIncludeJITSupport, 
								m_bNETIncludeLoadBalancingSupport, m_bNETIncludeObjectPoolingSupport, 
								m_bSPGenerateSelectAllSPs, m_bSPPrefixParams, 
								m_bSPGenerateDeleteSPs, m_bSPGenerateInsertSPs,	m_bSPGenerateUpdateSPs, 
								m_bSPGenerateSelectSPs, m_bSPIncludeComments, m_bSPIncludeDrops, 
								m_bSPIncludeErrorCodeSupport, m_bNETIncludeNullValueSupport, 
								m_bSPUseFieldListSelectClause, m_bNETIncludeTransactionSupport, 
								m_bNETIncludeCOMPlusSupport, m_bNETPrefixProperties, m_bNETStyleHungarian, 
								m_bNETIncludeComments, m_bSPIncludeNoCountStatements, m_bNETIncludeConnectionProviderSupport;
		private string			m_sNETNamespace, m_sSPNamePrefix, m_sNETOutputPath, m_sNETClassPrefix,
								m_sTableName, m_sNETClassName, m_sAppVersion;
		private SortedList		m_slTypeToPrefix, m_slTypeToNETType, m_slTypeToSqlType, m_slTypeToCSCastType, 
								m_slTypeToVBCastType;
		private ArrayList		m_alSPInputParams, m_alSPOutputParams, m_alExcludedFields;
		private int				m_iNETConnectionStringRetrieval, m_iAmountPrimaryKeyFields, m_iNETOutputLanguage, m_iSQLServerVersion,
								m_iTableType;
		#endregion

		
		/// <summary>
		/// Purpose: constructor
		/// </summary>
		/// <param name="sTableName">Name of table in database.</param>
		/// <param name="sAppVersion">Version of current running instance, determined in frmMain's constructor.</param>
		public clsTable(string sTableName, string sAppVersion)
		{
			m_sTableName = sTableName;
			m_alSPInputParams = new ArrayList();
			m_alSPOutputParams = new ArrayList();
			m_sAppVersion = sAppVersion;
			m_iAmountPrimaryKeyFields=0;
		}
		
		/// <summary>
		/// Purpose: retrieves the fields of the table represented by this class.
		/// </summary>
		/// <remarks>Throws exception if error occures.<br /><br />
		/// Uses the following query to retrieve <i>all</i> field information in one single
		/// request:<br /><br />
		/// <code>
		/// <pre>
		///		SELECT INFORMATION_SCHEMA.COLUMNS.*,
		///		(
		///			SELECT COLUMNPROPERTY(OBJECT_ID(@sTableName), INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, 'IsComputed')
		///		) AS IsComputed,
		///		(
		/// 		SELECT COL_LENGTH(@sTableName, INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME)
		///		) AS ColumnLength,
		///		(
		/// 		SELECT COLUMNPROPERTY(OBJECT_ID(@sTableName), INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, 'IsIdentity')
		///		) AS IsIdentity,
		///		(
		/// 		SELECT COLUMNPROPERTY(OBJECT_ID(@sTableName), INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, 'IsRowGuidCol')
		///		) AS IsRowGuidColumn,
		///		(
		///			ISNULL(	
		///			(
		///				SELECT 	CASE INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE
		///							WHEN 'PRIMARY KEY' THEN 1
		///						END
		///				FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE INNER JOIN 
		///					INFORMATION_SCHEMA.TABLE_CONSTRAINTS ON 
		///					INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME=
		///					INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME
		///				WHERE INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME = @sTableName
		///					AND INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME = 
		///					INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME
		///					AND INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE = 'PRIMARY KEY'
		///			), 0)
		///		) AS IsPrimaryKey,
		///		(
		///			ISNULL(	
		///			(	
		///				SELECT 	CASE INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE
		///							WHEN 'FOREIGN KEY' THEN 1
		///						END
		///				FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE INNER JOIN 
		///					INFORMATION_SCHEMA.TABLE_CONSTRAINTS ON 
		///					INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME=
		///					INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME
		///				WHERE INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME = @sTableName
		///					AND INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME = 
		///					INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME
		///					AND INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE = 'FOREIGN KEY'
		///			), 0)
		///		) AS IsForeignKey, 
		///		(
		///			ISNULL(	
		///			(	
		///				SELECT	CASE INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE
		///							WHEN 'UNIQUE' THEN 1
		///						END
		///				FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE INNER JOIN 
		///					INFORMATION_SCHEMA.TABLE_CONSTRAINTS ON 
		///					INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME=
		///					INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME
		///				WHERE INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME = @sTableName
		///					AND INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME = 
		///					INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME
		///					AND INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE = 'UNIQUE'
		///			), 0)
		///		) AS HasUniqueConstraint
		///		FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @sTableName
		/// </pre>
		/// </code>
		/// </remarks>
		public void GetFields()
		{
			SqlDataAdapter	sdaFieldRetrieval;
			SqlCommand		scmFieldRetrieval;
			DataTable		dtFields = new DataTable();

			// we'll retrieve ALL fieldinformation with 1 single query. this query is quite long, but
			// doesn't use any systemspecific tables at all. 
			string sSQL = "SELECT INFORMATION_SCHEMA.COLUMNS.*,	(SELECT COLUMNPROPERTY(OBJECT_ID(@sTableName), INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME" +
				", 'IsComputed')) AS IsComputed, (SELECT COL_LENGTH(@sTableName, INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME)" +
				") AS ColumnLength, (SELECT COLUMNPROPERTY(OBJECT_ID(@sTableName), INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME" +
				", 'IsIdentity')) AS IsIdentity, (SELECT COLUMNPROPERTY(OBJECT_ID(@sTableName), INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME" +
				", 'IsRowGuidCol')) AS IsRowGuidColumn, (ISNULL((SELECT CASE INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE" +
				" WHEN 'PRIMARY KEY' THEN 1	END	FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS" +
				" ON INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME=INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME" +
				" WHERE INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME = @sTableName AND INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME =" +
				" INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME AND INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE = 'PRIMARY KEY'" +
				"), 0)) AS IsPrimaryKey,(ISNULL((SELECT  CASE INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE WHEN 'FOREIGN KEY' THEN 1" +
				" END FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS ON" +
				" INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME=INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME" +
				" WHERE INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME = @sTableName AND INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME =" +
				" INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME AND INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE = 'FOREIGN KEY'" +
				"), 0)) AS IsForeignKey, (ISNULL((SELECT CASE INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE WHEN 'UNIQUE' THEN 1" +
				" END FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS ON" +
				" INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME=INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME WHERE" + 
				" INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME = @sTableName AND INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME =" +
				" INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME AND INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE = 'UNIQUE'" +
				"), 0)) AS HasUniqueConstraint FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @sTableName";
			scmFieldRetrieval = new SqlCommand(sSQL,m_scoActiveConnection);
			sdaFieldRetrieval = new SqlDataAdapter(scmFieldRetrieval);
			try
			{
				scmFieldRetrieval.Parameters.Add(new SqlParameter("@sTableName", SqlDbType.VarChar, 250, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, m_sTableName));
				
				// Fill the datatable
				sdaFieldRetrieval.Fill(dtFields);
				
				// Create field objects
				m_arrfFields = new clsField[dtFields.Rows.Count];

				// for each row, add a clsField object to the array.
				for(int i = 0;i < m_arrfFields.Length;i++)
				{
					DataRow drCurrent = dtFields.Rows[i];
				
					m_arrfFields[i] = new clsField(drCurrent["COLUMN_NAME"].ToString());
					// fill properties.
					m_arrfFields[i].bIsComputed = (Convert.ToInt32(drCurrent["IsComputed"]) == 1);
					m_arrfFields[i].bIsForeignKey = (Convert.ToInt32(drCurrent["IsForeignKey"]) == 1);
					m_arrfFields[i].bIsPrimaryKey = (Convert.ToInt32(drCurrent["IsPrimaryKey"]) == 1);
					m_arrfFields[i].bIsIdentity = (Convert.ToInt32(drCurrent["IsIdentity"]) == 1);
					m_arrfFields[i].bIsRowGUIDColumn = (Convert.ToInt32(drCurrent["IsRowGuidColumn"]) == 1);
					m_arrfFields[i].bHasUniqueConstraint = (Convert.ToInt32(drCurrent["HasUniqueConstraint"]) == 1);
					m_arrfFields[i].sDataType = drCurrent["DATA_TYPE"].ToString();
					m_arrfFields[i].iLength = Convert.ToInt32(drCurrent["ColumnLength"]);
					if(drCurrent["CHARACTER_MAXIMUM_LENGTH"].ToString().Length > 0)
					{
						// has a value
						int iCharacterMaxLength = Convert.ToInt32(drCurrent["CHARACTER_MAXIMUM_LENGTH"]);
						
						if((m_arrfFields[i].sDataType.ToLower() == "nvarchar") || 
								(m_arrfFields[i].sDataType.ToLower() == "nchar"))
						{
							// set length to length mentioned in CHARACTER_MAXIMUM_LENGTH
							m_arrfFields[i].iLength = iCharacterMaxLength;
						}
					}
					m_arrfFields[i].iOrdinalPosition = Convert.ToInt32(drCurrent["ORDINAL_POSITION"]);
					if(drCurrent["NUMERIC_PRECISION"].ToString().Length > 0)
					{
						m_arrfFields[i].iPrecision = Convert.ToInt32(drCurrent["NUMERIC_PRECISION"]);
					}
					if(drCurrent["NUMERIC_SCALE"].ToString().Length > 0)
					{
						m_arrfFields[i].iScale = Convert.ToInt32(drCurrent["NUMERIC_SCALE"]);
					}
					m_arrfFields[i].slTypeToPrefix = m_slTypeToPrefix;
					m_arrfFields[i].slTypeToNETType = m_slTypeToNETType;
					m_arrfFields[i].slTypeToVBCastType = m_slTypeToVBCastType;
					m_arrfFields[i].slTypeToSqlType = m_slTypeToSqlType;
					m_arrfFields[i].slTypeToCSCastType = m_slTypeToCSCastType;
					m_arrfFields[i].bIsNullable = (drCurrent["IS_NULLABLE"].ToString().ToLower() == "yes");
					m_arrfFields[i].sDefaultValue = drCurrent["COLUMN_DEFAULT"].ToString();
					if(m_arrfFields[i].bIsPrimaryKey)
					{
						m_iAmountPrimaryKeyFields++;
					}

					// check if this field is excluded (i.e., in the list of excluded fields)
					m_arrfFields[i].bIsExcluded = (m_alExcludedFields.IndexOf(m_arrfFields[i].sFieldName.ToLower()) >= 0);
				}
			}
			catch(Exception ex)
			{
				// bubble error.
				throw new Exception("clsTable::GetFields:Error occured: " + ex.Message, ex);
			}
			finally
			{
				sdaFieldRetrieval.Dispose();
				sdaFieldRetrieval = null;
			}
		}


		/// <summary>
		/// Purpose: generates all stored procedures for this table, based on the settings in the
		/// membervariables, and generate the class file for this table.
		/// </summary>
		/// <param name="swSPOutput">Open streamwriter object to the file wherein the stored procedures should be
		/// writen.</param>
		/// <remarks>This routine will create a new file based on the tablename, for the .NET class.</remarks>
		public void GenerateCode(StreamWriter swSPOutput)
		{
			bool			bDoGenerateNETClass = false;
			StreamWriter	swNETClassOutput = null;			// pre-init the variable otherwise it won't compile
			string			sNETClassFile = "";
			
			// Check if the table has any fields.
			if(m_arrfFields.Length <= 0)
			{
				// no, no fields in the table
				swSPOutput.WriteLine("-- ========================================================================================================");
				swSPOutput.WriteLine("-- No Stored Procedures generated for " + ((eSQLObjectType)m_iTableType).ToString().ToLower() + ":  " + m_sTableName + ", because no fields could be read, ");
				swSPOutput.WriteLine("-- probably because of an error in the " + ((eSQLObjectType)m_iTableType).ToString().ToLower() + " definition (like 2 FK constraints on 1 field etc.)");
				swSPOutput.WriteLine("PRINT 'No stored procedures generated for " + ((eSQLObjectType)m_iTableType).ToString().ToLower() + " [" + m_sTableName + "] due to an error. See comments.'");
				swSPOutput.WriteLine("GO");
				swSPOutput.WriteLine("-- ========================================================================================================");
				swSPOutput.WriteLine(Environment.NewLine);
				return;
			}
			
			// First determine the SQLServer version for the generation routines.
			string[] arrsVersionElements = m_scoActiveConnection.ServerVersion.Split('.');
			m_iSQLServerVersion = Int32.Parse(arrsVersionElements[0]);
			
			// Check first IF there will be any stored procedure generated for this table. If so, we need
			// the .NET class for this table.
			if(m_bSPGenerateSelectSPs || m_bSPGenerateInsertSPs || m_bSPGenerateUpdateSPs || 
					m_bSPGenerateDeleteSPs || m_bSPGenerateSelectAllSPs)
			{
				// Do generate .NET class for this table
				bDoGenerateNETClass = true;
				
				string sClassNameTmp = m_sTableName.Replace(" ","");
				m_sNETClassName = m_sNETClassPrefix + sClassNameTmp.Substring(0,1).ToUpper() + sClassNameTmp.Substring(1, sClassNameTmp.Length-1);