clstable.cs

LLBLGen 1.21 Sourcecode

					swSPOutput.WriteLine("GO" + Environment.NewLine);							
				}
				// write SP header
				if(m_bSPIncludeComments)
				{
					swSPOutput.WriteLine("---------------------------------------------------------------------------------");
					swSPOutput.WriteLine("-- Stored procedure that will select one existing row from the " + ((eSQLObjectType)m_iTableType).ToString().ToLower() + " '" + m_sTableName + "'");
					swSPOutput.WriteLine("-- based on the field [" + ofCurrent.sFieldName + "], which is unique per row.");
					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 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, 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 Update stored procedures for the given table
		/// </summary>
		/// <param name="swSPOutput">Open streamwriter object to write to.</param>
		/// <remarks>LLBLGen will generate a stored procedure and accompanying C# method
		/// if there are fields updatable, i.e. non-primary key, non-computed, non-identity and non-excluded
		/// fields. Otherwise LLBLGen will not generate an UPDATE stored procedure.
		/// Furthermore, if one or more of the updateable fields is a foreign key, for each foreign key
		/// there will be one update stored procedure. This way deleted foreign keys can be updated easily.</remarks>
		private void GenerateUpdateSP(StreamWriter swSPOutput)
		{
			string sSPName;
			
			///////////////
			// First the regular update one row stored procedure
			///////////////
			// Generate stored procedure name
			sSPName = m_sSPNamePrefix + m_sTableName + "_Update";
			
			if(m_bSPIncludeComments)
			{
				// write header
				swSPOutput.WriteLine(Environment.NewLine + "-- //// Update Stored procedure for updating one single row.");
			}
			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 update an existing row in 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("-- UPDATE an existing row in the " + ((eSQLObjectType)m_iTableType).ToString().ToLower() + ".");
			}
			swSPOutput.WriteLine("UPDATE [dbo].[" + m_sTableName + "]");
			// write fields/values block
			WriteSPUpdateFieldValueBlock(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);
			
			//////////////
			// ... then for all foreign keys in the inputlist, generate a unique stored procedure which 
			// will update only that particular field. 
			//////////////
			foreach(clsField ofCurrent in m_alSPInputParams)
			{
				if(ofCurrent.bIsForeignKey && !ofCurrent.bIsPrimaryKey)
				{
					// generate a stored procedure for this field.
					sSPName = m_sSPNamePrefix + m_sTableName + "_UpdateAllW" + ofCurrent.sFieldName.Replace(" ","_") + "Logic";
			
					if(m_bSPIncludeComments)
					{
						// write header
						swSPOutput.WriteLine(Environment.NewLine + "-- //// Update Stored procedure for updating one or more rows using 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 update one or more existing rows in the " + ((eSQLObjectType)m_iTableType).ToString().ToLower() + " '" + m_sTableName + "'.");
						swSPOutput.WriteLine("-- Will reset field [" + ofCurrent.sFieldName + "] with value " + ofCurrent.GetFieldAsTSQLParam(m_bSPPrefixParams) + "Old " +
											 " to value " + ofCurrent.GetFieldAsTSQLParam(m_bSPPrefixParams));
						WriteSPArgumentComments(ofCurrent, swSPOutput, true);
						swSPOutput.WriteLine("---------------------------------------------------------------------------------");
					}
					swSPOutput.WriteLine("CREATE PROCEDURE [dbo].[" + sSPName + "]");
					// write arguments
					WriteSPArguments(ofCurrent, swSPOutput, true);
			
					// write body
					swSPOutput.WriteLine("AS");
					if(m_bSPIncludeNoCountStatements)
					{
						swSPOutput.WriteLine("SET NOCOUNT ON");
					}
					if(m_bSPIncludeComments)
					{
						swSPOutput.WriteLine("-- UPDATE an existing row in the " + ((eSQLObjectType)m_iTableType).ToString().ToLower() + ".");
					}
					swSPOutput.WriteLine("UPDATE [dbo].[" + m_sTableName + "]");
					// write fields/values block
					swSPOutput.WriteLine("SET");
					swSPOutput.WriteLine("\t[" +ofCurrent.sFieldName + "] = " + ofCurrent.GetFieldAsTSQLParam(m_bSPPrefixParams));
					swSPOutput.WriteLine("WHERE");
					swSPOutput.WriteLine("\t[" +ofCurrent.sFieldName + "] = " + ofCurrent.GetFieldAsTSQLParam(m_bSPPrefixParams) + "Old");

					// 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 an Insert stored procedure for the given table
		/// </summary>
		/// <param name="swSPOutput">Open streamwriter object to write to.</param>
		private void GenerateInsertSP(StreamWriter swSPOutput)
		{
			// Generate stored procedure name
			string sSPName = m_sSPNamePrefix + m_sTableName + "_Insert";
			
			if(m_bSPIncludeComments)
			{
				// write header
				swSPOutput.WriteLine(Environment.NewLine + "-- //// Insert 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 insert 1 row in 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("-- INSERT a new row in the " + ((eSQLObjectType)m_iTableType).ToString().ToLower() + ".");
			}
			swSPOutput.WriteLine("INSERT [dbo].[" + m_sTableName + "]");
			// write fields/values block
			WriteSPInsertFieldValueBlock(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: writes the code to fill the outputparams with values.
		/// </summary>
		/// <param name="swSPOutput">An open streamwriter object to the T-SQL file being written to.</param>
		private void WriteSPOutputBlock(StreamWriter swSPOutput)
		{
			// We first have to retrieve the errorcode, since this block will be written right
			// after the INSERT statement.
			if(m_bSPIncludeErrorCodeSupport)
			{
				// walk the outputlist. if ErrorCode object is found, it's used and the output is written.
				foreach(clsField ofCurrent in m_alSPOutputParams)
				{
					if(ofCurrent.sFieldName == "ErrorCode")
					{
						// found it
						if(m_bSPIncludeComments)
						{
							swSPOutput.WriteLine("-- Get the Error Code for the statement just executed.");
						}
						swSPOutput.WriteLine("SELECT " + ofCurrent.GetFieldAsTSQLParam(m_bSPPrefixParams) + "=@@ERROR");
					}
				}
			}
			// walk the outputlist. Skip errorcode, since we've already written that. Other fields are written
			// using special logic.
			foreach(clsField ofCurrent in m_alSPOutputParams)
			{
				if(!(ofCurrent.sFieldName == "ErrorCode"))
				{
					if(ofCurrent.bIsIdentity)
					{
						// return the identity value
						if(m_bSPIncludeComments)
						{
							swSPOutput.WriteLine("-- Get the IDENTITY value for the row just inserted.");
						}
						if(m_iSQLServerVersion > 7)
						{
							swSPOutput.WriteLine("SELECT " + ofCurrent.GetFieldAsTSQLParam(m_bSPPrefixParams) + "=SCOPE_IDENTITY()");
						}
						else
						{
							// special case, SQLServer 7.0 doesn't support SCOPE_IDENTITY()
							swSPOutput.WriteLine("SELECT " + ofCurrent.GetFieldAsTSQLParam(m_bSPPrefixParams) + "=@@IDENTITY");
						}
					}
					// add other selections of other possible fieldtypes here
				}
			}
		}


		/// <summary>
		/// Purpose: writes the ORDER BY clause based on the primary key fields in the fields list.
		/// </summary>
		/// <param name="swSPOutput">An open streamwriter object to the T-SQL file being written to.</param>
		private void WriteSPOrderByBlock(StreamWriter swSPOutput)
		{
			string	sCommaPrefix;
			bool	bOrderByClauseEmitted=false;

			if(m_arrfFields.Length > 0)
			{
				for(int i = 0, iEmittedClauses = 0;i < m_arrfFields.Length;i++)
				{
					clsField ofCurrent = m_arrfFields[i];
					sCommaPrefix = ", ";

					if(iEmittedClauses == 0)
					{
						sCommaPrefix="";
					}
					if(ofCurrent.bIsPrimaryKey)