clstable.cs

LLBLGen 1.21 Sourcecode

			// insert return value block if needed.
			if(m_alSPOutputParams.Count > 0)
			{
				// there are outputparams. 
				WriteSPOutputBlock(swSPOutput);
			}

			// write SP end
			swSPOutput.WriteLine("GO" + Environment.NewLine);

			/////////////
			// Then, if applicable, for each field in the PK, if the PK contains more than 1 field, 
			// generate a Delete stored proc using only that field in the where clause. Use the
			// knowledge that the inputlist contains only PK fields.
			/////////////
			if(m_alSPInputParams.Count > 1)
			{
				// more than 1 PK field. generate for each field a unique delete stored proc
				foreach(clsField ofCurrent in m_alSPInputParams)
				{
					// Generate stored procedure name
					sSPName = m_sSPNamePrefix + m_sTableName + "_DeleteW" + ofCurrent.sFieldName.Replace(" ","_") + "Logic";
				
					if(m_bSPIncludeComments)
					{
						// write header
						swSPOutput.WriteLine(Environment.NewLine + "-- //// Delete Stored procedure using PK field [" + ofCurrent.sFieldName + "]");
					}
					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 one or more  existing rows from the " + ((eSQLObjectType)m_iTableType).ToString().ToLower() + " '" + m_sTableName + "'");
						swSPOutput.WriteLine("-- using the Primary Key field [" + ofCurrent.sFieldName + "]. ");
						WriteSPArgumentComments(ofCurrent, swSPOutput, false);
						swSPOutput.WriteLine("---------------------------------------------------------------------------------");
					}
					swSPOutput.WriteLine("CREATE PROCEDURE [dbo].[" + sSPName + "]");
					// write arguments
					WriteSPArguments(ofCurrent, swSPOutput, false);
				
					// write body
					swSPOutput.WriteLine("AS");
					if(m_bSPIncludeNoCountStatements)
					{
						swSPOutput.WriteLine("SET NOCOUNT ON");
					}
					if(m_bSPIncludeComments)
					{
						swSPOutput.WriteLine("-- DELETE one or more existing rows from the " + ((eSQLObjectType)m_iTableType).ToString().ToLower() + ".");
					}
					swSPOutput.WriteLine("DELETE FROM [dbo].[" + m_sTableName + "]");
					// write where block
					swSPOutput.WriteLine("WHERE");
					swSPOutput.WriteLine("\t[" +ofCurrent.sFieldName + "] = " + ofCurrent.GetFieldAsTSQLParam(m_bSPPrefixParams));

					// insert return value block if needed.
					if(m_alSPOutputParams.Count > 0)
					{
						// there are outputparams. 
						WriteSPOutputBlock(swSPOutput);
					}

					// write SP end
					swSPOutput.WriteLine("GO" + Environment.NewLine);
				}
			}
		}


		/// <summary>
		/// Purpose: Generates a Select stored procedure for the given table, based on the primary key, which is
		/// added to the m_alSPInputParams list.
		/// </summary>
		/// <param name="swSPOutput">Open streamwriter object to write to.</param>
		private void GenerateSelectOnePKSP(StreamWriter swSPOutput)
		{
			// Generate stored procedure name
			string sSPName = m_sSPNamePrefix + m_sTableName + "_SelectOne";
			
			if(m_bSPIncludeComments)
			{
				// write header
				swSPOutput.WriteLine(Environment.NewLine + "-- //// Select Stored procedure, based on 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 select an existing row from the " + ((eSQLObjectType)m_iTableType).ToString().ToLower() + " '" + m_sTableName + "'");
				swSPOutput.WriteLine("-- based on 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("-- SELECT an existing row from the " + ((eSQLObjectType)m_iTableType).ToString().ToLower() + ".");
			}
			if(m_bSPUseFieldListSelectClause)
			{
				swSPOutput.WriteLine("SELECT");
				WriteSPSelectFieldList(swSPOutput);
				swSPOutput.WriteLine("FROM [dbo].[" + m_sTableName + "]");
			}
			else
			{
				// simply do a select * 
				swSPOutput.WriteLine("SELECT * FROM [dbo].[" + m_sTableName + "]");
			}
			// write where block
			WriteSPInputParamsWhereBlock(swSPOutput);

			// insert return value block if needed.
			if(m_alSPOutputParams.Count > 0)
			{
				// there are outputparams. 
				WriteSPOutputBlock(swSPOutput);
			}

			// write SP end
			swSPOutput.WriteLine("GO" + Environment.NewLine);
		}


		/// <summary>
		/// Purpose: Generates a Select All stored procedure for the given table, which is simply a procedure
		/// which will return all records in the table, ordered by the PK.
		/// </summary>
		/// <param name="swSPOutput">Open streamwriter object to write to.</param>
		private void GenerateSelectAllSP(StreamWriter swSPOutput)
		{
			// Generate stored procedure name
			string sSPName = m_sSPNamePrefix + m_sTableName + "_SelectAll";
			
			if(m_bSPIncludeComments)
			{
				// write header
				swSPOutput.WriteLine(Environment.NewLine + "-- //// Select All Stored procedure.");
			}
			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 select all rows from the " + ((eSQLObjectType)m_iTableType).ToString().ToLower() + " '" + m_sTableName + "'");
				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("-- SELECT all rows from the " + ((eSQLObjectType)m_iTableType).ToString().ToLower() + ".");
			}
			if(m_bSPUseFieldListSelectClause)
			{
				swSPOutput.WriteLine("SELECT");
				WriteSPSelectFieldList(swSPOutput);
				swSPOutput.WriteLine("FROM [dbo].[" + m_sTableName + "]");
			}
			else
			{
				// simply do a select * 
				swSPOutput.WriteLine("SELECT * FROM [dbo].[" + m_sTableName + "]");
			}
			// write order by block
			WriteSPOrderByBlock(swSPOutput);

			// insert return value block if needed.
			if(m_alSPOutputParams.Count > 0)
			{
				// there are outputparams. 
				WriteSPOutputBlock(swSPOutput);
			}

			// write SP end
			swSPOutput.WriteLine("GO" + Environment.NewLine);
		}



		/// <summary>
		/// Purpose: Generates a set of Select stored procedures for the given table, based on the foreign keys added
		/// to m_alSPInputParams. Per field in that arraylist, 1 stored procedure is generated.
		/// </summary>
		/// <param name="swSPOutput">Open streamwriter object to write to.</param>
		private void GenerateSelectForeignSP(StreamWriter swSPOutput)
		{
			foreach(clsField ofCurrent in m_alSPInputParams)
			{
				// generate a stored procedure with the current selected field as the
				// filter.
					
				// Generate stored procedure name
				string sSPName = m_sSPNamePrefix + m_sTableName + "_SelectAllW" + ofCurrent.sFieldName.Replace(" ","_") + "Logic";
				
				if(m_bSPIncludeComments)
				{
					// write header
					swSPOutput.WriteLine(Environment.NewLine + "-- //// Select Stored procedure, based on field [" + ofCurrent.sFieldName + "].");
				}
				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 select one or more existing rows from the " + ((eSQLObjectType)m_iTableType).ToString().ToLower() + " '" + m_sTableName + "'");
					swSPOutput.WriteLine("-- based on a foreign key field.");
					WriteSPArgumentComments(ofCurrent, swSPOutput, false);
					swSPOutput.WriteLine("---------------------------------------------------------------------------------");
				}
				swSPOutput.WriteLine("CREATE PROCEDURE [dbo].[" + sSPName + "]");
				// write arguments
				WriteSPArguments(ofCurrent, swSPOutput, false);
				
				// write body
				swSPOutput.WriteLine("AS");
				if(m_bSPIncludeNoCountStatements)
				{
					swSPOutput.WriteLine("SET NOCOUNT ON");
				}
				if(m_bSPIncludeComments)
				{
					swSPOutput.WriteLine("-- SELECT one or more existing rows from the " + ((eSQLObjectType)m_iTableType).ToString().ToLower() + ".");
				}
				if(m_bSPUseFieldListSelectClause)
				{
					swSPOutput.WriteLine("SELECT");
					WriteSPSelectFieldList(swSPOutput);
					swSPOutput.WriteLine("FROM [dbo].[" + m_sTableName + "]");
				}
				else
				{
					// simply do a select * 
					swSPOutput.WriteLine("SELECT * FROM [dbo].[" + m_sTableName + "]");
				}
				// write where block, if applicable
				swSPOutput.WriteLine("WHERE");
				swSPOutput.WriteLine("\t[" +ofCurrent.sFieldName + "] = " + ofCurrent.GetFieldAsTSQLParam(m_bSPPrefixParams));

				// insert return value block if needed.
				if(m_alSPOutputParams.Count > 0)
				{
					// there are outputparams. 
					WriteSPOutputBlock(swSPOutput);
				}

				// write SP end
				swSPOutput.WriteLine("GO" + Environment.NewLine);
			}
		}



		/// <summary>
		/// Purpose: Generates a set of Select stored procedures for the given table, based on the non PK fields
		/// which are unique per row, which are added to m_alSPInputParams. 
		/// Per field in that arraylist, 1 stored procedure is generated.
		/// </summary>
		/// <param name="swSPOutput">Open streamwriter object to write to.</param>
		private void GenerateSelectOneNonPKSP(StreamWriter swSPOutput)
		{
			foreach(clsField ofCurrent in m_alSPInputParams)
			{
				// generate a stored procedure with the current selected field as the
				// filter.
					
				// Generate stored procedure name
				string sSPName = m_sSPNamePrefix + m_sTableName + "_SelectOneW" + ofCurrent.sFieldName.Replace(" ","_") + "Logic";
				
				if(m_bSPIncludeComments)
				{
					// write header
					swSPOutput.WriteLine(Environment.NewLine + "-- //// Select Stored procedure, based on field [" + ofCurrent.sFieldName + "].");
				}
				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 + "]");