clstable.cs

LLBLGen 1.21 Sourcecode

				
				// Open the NET class output streamwriter
				switch(m_iNETOutputLanguage)
				{
					case (int)eDotNetLanguages.CSharp:
						sNETClassFile = m_sNETOutputPath + m_sNETClassName + ".cs";
						break;
					case (int)eDotNetLanguages.VBDotNet:
						sNETClassFile = m_sNETOutputPath + m_sNETClassName + ".vb";
						break;
				}
			
				if(File.Exists(sNETClassFile))
				{
					// remove the file.
					File.Delete(sNETClassFile);
				}
			
				// Open the file
				swNETClassOutput = new StreamWriter(sNETClassFile);
			}
			if(m_bSPIncludeComments)
			{
				// Write descriptive header.
				swSPOutput.WriteLine("");
				swSPOutput.WriteLine("-- ========================================================================================================");
				swSPOutput.WriteLine("-- [Stored Procedures generated for " + ((eSQLObjectType)m_iTableType).ToString().ToLower() + ":  " + m_sTableName + "]");
				swSPOutput.WriteLine("GO");
			}
			if(bDoGenerateNETClass)
			{
				// Write class header
				switch(m_iNETOutputLanguage)
				{
					case (int)eDotNetLanguages.CSharp:
						WriteCSClassHeader(swNETClassOutput);
						break;
					case (int)eDotNetLanguages.VBDotNet:
						WriteVBClassHeader(swNETClassOutput);
						break;
				}
			}
			if(m_bSPGenerateInsertSPs)
			{
				// Generate the input params list and the outputparams list.
				GenerateSPInputParamsList(eSPGenType.InsertSP);
				GenerateSPOutputParamsList(eSPGenType.InsertSP);

				// Generate insert stored procedure for this table
				GenerateInsertSP(swSPOutput);
				
				// Generate insert method in .NET class. Re-use the calculated input/output paramlists.
				switch(m_iNETOutputLanguage)
				{
					case (int)eDotNetLanguages.CSharp:
						WriteCSInsertMethod(swNETClassOutput);
						break;
					case (int)eDotNetLanguages.VBDotNet:
						WriteVBInsertMethod(swNETClassOutput);
						break;
				}
			}
			if(m_bSPGenerateUpdateSPs)
			{
				// Generate the input params list and the outputparams list.
				GenerateSPInputParamsList(eSPGenType.UpdateSP);
				GenerateSPOutputParamsList(eSPGenType.UpdateSP);

				// Update stored procedures are only necessary when there are NON-primary key fields
				// to update. If there ain't any fields to update, we skip this procedure entirely.
				if(DetermineIfUpdateSPIsNecessary())
				{
					// there are updatable fields found, generate an update stored procedure
					// generate update stored procedure for this table
					GenerateUpdateSP(swSPOutput);

					// Generate update method in .NET class. Re-use the calculated input/output paramlists.
					switch(m_iNETOutputLanguage)
					{
						case (int)eDotNetLanguages.CSharp:
							WriteCSUpdateMethod(swNETClassOutput);
							break;
						case (int)eDotNetLanguages.VBDotNet:
							WriteVBUpdateMethod(swNETClassOutput);
							break;
					}
				}
			}
			if(m_bSPGenerateDeleteSPs)
			{
				// Generate the input params list and the outputparams list.
				GenerateSPInputParamsList(eSPGenType.DeleteSP);
				GenerateSPOutputParamsList(eSPGenType.DeleteSP);

				// check if there are fields in the inputparamlist. If no PK is defined on the table, do not
				// create a stored proc.
				if(m_alSPInputParams.Count > 0)
				{
					// generate delete stored procedures for this table
					GenerateDeleteSP(swSPOutput);
					
					// Generate select method in .NET class. Re-use the calculated input/output paramlists.
					switch(m_iNETOutputLanguage)
					{
						case (int)eDotNetLanguages.CSharp:
							WriteCSDeleteMethod(swNETClassOutput);
							break;
						case (int)eDotNetLanguages.VBDotNet:
							WriteVBDeleteMethod(swNETClassOutput);
							break;
					}
				}
			}
			if(m_bSPGenerateSelectSPs)
			{
				// First the primary key based selection stored proc
				// Generate the input params list and the outputparams list.
				GenerateSPInputParamsList(eSPGenType.SelectOnePKSP);
				GenerateSPOutputParamsList(eSPGenType.SelectOnePKSP);

				// check if there are fields in the inputparamlist. If no PK is defined on the table, do not
				// create a stored proc.
				if(m_alSPInputParams.Count > 0)
				{
					// There is a PK defined, generate the SelectOne using the PK stored procedure for this table.
					GenerateSelectOnePKSP(swSPOutput);
					
					// Generate select method in .NET class. Re-use the calculated input/output paramlists.
					switch(m_iNETOutputLanguage)
					{
						case (int)eDotNetLanguages.CSharp:
							WriteCSSelectOnePKMethod(swNETClassOutput);
							break;
						case (int)eDotNetLanguages.VBDotNet:
							WriteVBSelectOnePKMethod(swNETClassOutput);
							break;
					}
				}
				
				// Then the, if applicable, SelectOne stored procedures for the different fields which 
				// are not part of the PK, but will be unique in each row. (Identity columsn, GUID colums, columns
				// with a UNIQUE constaint)
				GenerateSPInputParamsList(eSPGenType.SelectOneNonPKSP);
				GenerateSPOutputParamsList(eSPGenType.SelectOneNonPKSP);

				// check if there are fields in the inputparamlist. If there are no fields in this table which justify
				// a separate SelectOne routine, skip the generation process of this stored procedure
				if(m_alSPInputParams.Count > 0)
				{
					// There is at least 1 field, generate select stored procedures for each field in the 
					// inputlist for this table.
					GenerateSelectOneNonPKSP(swSPOutput);
					
					// Generate select method in .NET class. Re-use the calculated input/output paramlists.
					switch(m_iNETOutputLanguage)
					{
						case (int)eDotNetLanguages.CSharp:
							WriteCSSelectOneNonPKMethod(swNETClassOutput);
							break;
						case (int)eDotNetLanguages.VBDotNet:
							WriteVBSelectOneNonPKMethod(swNETClassOutput);
							break;
					}
				}
				
			}
			if(m_bSPGenerateSelectAllSPs)
			{
				// Select All stored procedures are simple routines which retrieve all records in a 
				// table without a where. These records are ordered by keyfields.
				GenerateSPInputParamsList(eSPGenType.SelectAllSP);
				GenerateSPOutputParamsList(eSPGenType.SelectAllSP);

				// generate selectall stored procedures for this table.
				GenerateSelectAllSP(swSPOutput);
				
				// Generate Select All method in .NET class. Re-use the calculated input/output paramlists.
				switch(m_iNETOutputLanguage)
				{
					case (int)eDotNetLanguages.CSharp:
						WriteCSSelectAllMethod(swNETClassOutput);
						break;
					case (int)eDotNetLanguages.VBDotNet:
						WriteVBSelectAllMethod(swNETClassOutput);
						break;
				}

				// ... then the foreign key based selection stored proc. (if there are any foreign keys).
				// Generate the input params list and the outputparams list.
				GenerateSPInputParamsList(eSPGenType.SelectFKSP);
				GenerateSPOutputParamsList(eSPGenType.SelectFKSP);

				if(m_alSPInputParams.Count > 0)
				{
					// generate select stored procedures for this table.
					GenerateSelectForeignSP(swSPOutput);
					
					// Generate select foreign method in .NET class. Re-use the calculated input/output paramlists.
					switch(m_iNETOutputLanguage)
					{
						case (int)eDotNetLanguages.CSharp:
							WriteCSSelectForeignMethod(swNETClassOutput);
							break;
						case (int)eDotNetLanguages.VBDotNet:
							WriteVBSelectForeignMethod(swNETClassOutput);
							break;
					}
				}
			}
			if(m_bSPIncludeComments)
			{
				// Write descriptive header in SP code.
				swSPOutput.WriteLine("");
				swSPOutput.WriteLine("-- [End of Stored Procedures for " + ((eSQLObjectType)m_iTableType).ToString().ToLower() + ":     " + m_sTableName + "]");
				swSPOutput.WriteLine("-- ========================================================================================================");
				swSPOutput.WriteLine("GO");
			}
			
			if(bDoGenerateNETClass)
			{
				switch(m_iNETOutputLanguage)
				{
					case (int)eDotNetLanguages.CSharp:
						// write properties
						WriteCSProperties(swNETClassOutput);
						// Write closing brackets
						swNETClassOutput.WriteLine("\t}");		// class
						swNETClassOutput.WriteLine("}");		// namespace
						swNETClassOutput.Close();
						break;
					case (int)eDotNetLanguages.VBDotNet:
						// write properties
						WriteVBProperties(swNETClassOutput);
						// Write closing statements
						swNETClassOutput.WriteLine("\tEnd Class");			// class
						swNETClassOutput.WriteLine("End Namespace");		// namespace
						swNETClassOutput.Close();
						break;
				}
			}
		}
		
		
		/// <summary>
		/// Purpose: Determines when there should be an UPDATE stored procedure generated for the current table.
		/// An update stored procedure is NOT necessary if there are only primary key fields to update and/or 
		/// only identity fields/computed fields/excluded fields or there are NO primary key fields to find.
		/// </summary>
		/// <returns>True if there are fields to update, so there should be an UPDATE stored procedure, or
		/// False if there are no fields to update, so the UPDATE stored procedure should be skipped.</returns>
		private bool DetermineIfUpdateSPIsNecessary()
		{
			bool	bIsNecessary=false, bPKFieldPresent=false;

			foreach(clsField ofCurrent in m_alSPInputParams)
			{
				bIsNecessary |= (!ofCurrent.bIsPrimaryKey && !ofCurrent.bIsComputed && !ofCurrent.bIsExcluded && !ofCurrent.bIsIdentity);
				bPKFieldPresent |= ofCurrent.bIsPrimaryKey;
			}
			return bIsNecessary && bPKFieldPresent;
		}
		
		
		/// <summary>
		/// Purpose: Generates a set of Delete stored procedures for the given table
		/// </summary>
		/// <param name="swSPOutput">Open streamwriter object to write to.</param>
		/// <remarks>This routine will generate 1 delete stored procedure using the complete
		/// primary key plus for each field in the PK, if the PK contains more than 1 field, 
		/// one stored procedure to delete all rows containing that field. These SP's, together
		/// with the Update using FK fields, will be necessary to avoid FK constraint voilations.
		/// </remarks>
		private void GenerateDeleteSP(StreamWriter swSPOutput)
		{
			string sSPName;
			
			/////////////
			// First the PK delete stored proc
			/////////////

			// Generate stored procedure name
			sSPName = m_sSPNamePrefix + m_sTableName + "_Delete";
			
			if(m_bSPIncludeComments)
			{
				// write header
				swSPOutput.WriteLine(Environment.NewLine + "-- //// Delete Stored procedure using Primary Key.");
			}
			if(m_bSPIncludeDrops)
			{
				// write drop call
				swSPOutput.WriteLine("if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[" + sSPName + "]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[" + sSPName + "]");
				swSPOutput.WriteLine("GO" + Environment.NewLine);							
			}
			// write SP header
			if(m_bSPIncludeComments)
			{
				swSPOutput.WriteLine("---------------------------------------------------------------------------------");
				swSPOutput.WriteLine("-- Stored procedure that will delete an existing row from the " + ((eSQLObjectType)m_iTableType).ToString().ToLower() + " '" + m_sTableName + "'");
				swSPOutput.WriteLine("-- using the Primary Key. ");
				WriteSPArgumentComments(swSPOutput);
				swSPOutput.WriteLine("---------------------------------------------------------------------------------");
			}
			swSPOutput.WriteLine("CREATE PROCEDURE [dbo].[" + sSPName + "]");
			// write arguments
			WriteSPArguments(swSPOutput);
			
			// write body
			swSPOutput.WriteLine("AS");
			if(m_bSPIncludeNoCountStatements)
			{
				swSPOutput.WriteLine("SET NOCOUNT ON");
			}
			if(m_bSPIncludeComments)
			{
				swSPOutput.WriteLine("-- DELETE an existing row from the " + ((eSQLObjectType)m_iTableType).ToString().ToLower() + ".");
			}
			swSPOutput.WriteLine("DELETE FROM [dbo].[" + m_sTableName + "]");
			// write where block
			WriteSPInputParamsWhereBlock(swSPOutput);