clstable.cs

LLBLGen 1.21 Sourcecode

					{	
						if(!bOrderByClauseEmitted)
						{
							// Write ORDER BY clause
							swSPOutput.WriteLine("ORDER BY ");
							bOrderByClauseEmitted=true;
						}

						// Field is primary key. Emit clause. 
						swSPOutput.WriteLine("\t" + sCommaPrefix + "[" +ofCurrent.sFieldName + "] ASC");
						iEmittedClauses++;
					}
				}
			}
		}


		/// <summary>
		/// Purpose: writes the WHERE clause based on all inputparams. Only PK fields are emitted.
		/// </summary>
		/// <param name="swSPOutput">An open streamwriter object to the T-SQL file being written to.</param>
		private void WriteSPInputParamsWhereBlock(StreamWriter swSPOutput)
		{
			string sANDClause;

			if(m_alSPInputParams.Count > 0)
			{
				int iEmittedClauses = 0;
				
				foreach(clsField ofCurrent in m_alSPInputParams)
				{
					sANDClause = "AND ";

					if(ofCurrent.bIsPrimaryKey)
					{	
						if(iEmittedClauses==0)
						{
							// Write WHERE clause, use only the fields in the inputparams list
							swSPOutput.WriteLine("WHERE");
							
							// reset AND clause
							sANDClause="";
						}

						// Field is primary key. Emit clause. Primary key is always an input param. 
						swSPOutput.WriteLine("\t" + sANDClause + "[" +ofCurrent.sFieldName + "] = " + ofCurrent.GetFieldAsTSQLParam(m_bSPPrefixParams));
						iEmittedClauses++;
					}
				}
			}
		}


		/// <summary>
		/// Purpose: writes the SET list and the WHERE clause for an UPDATE stored procedure
		/// </summary>
		/// <param name="swSPOutput">An open streamwriter object to the T-SQL file being written to.</param>
		/// <remarks>The routine assumes there are fields that should be updated. <br /><br />
		/// Routine contains code by Colin Young of GB RetailExchange, which fixed a bug in v1.1.
		/// </remarks>
		private void WriteSPUpdateFieldValueBlock(StreamWriter swSPOutput)
		{
			string	sCommaSuffix, sANDClause;
			bool	bFirst = true;
			
			// First write the SET statement.
			swSPOutput.Write("SET ");

			// Now walk all fields in the table. Exclude all Primary Key, Computed and Identity fields.
			for(int i = 0;i < m_arrfFields.Length;i++)
			{
				sCommaSuffix = ",";
				if((!m_arrfFields[i].bIsIdentity)&&(!m_arrfFields[i].bIsComputed)&&(!m_arrfFields[i].bIsPrimaryKey)&&(!m_arrfFields[i].bIsTimeStamp))
				{
					if(bFirst)
					{
						// first field to emit, no commasuffix is written, since there is no field written yet.
						bFirst = false;
						sCommaSuffix = "";
					}
					
					// Field is updatable
					// Determine value. This is OR an inputparam OR a default value.
					if(m_alSPInputParams.IndexOf(m_arrfFields[i]) >= 0)
					{
						// it's an inputparam. Write first a comma suffix on the current line, then a new line and then the current field.
						swSPOutput.Write("{0}{1}\t[{2}] = {3}", sCommaSuffix, Environment.NewLine, m_arrfFields[i].sFieldName, m_arrfFields[i].GetFieldAsTSQLParam(m_bSPPrefixParams));
					}
					else
					{
						// it's not an inputparam, use default value as value
						if(m_arrfFields[i].sDefaultValue.Length > 0)
						{
							// emit the statement which will insert the default value for this field.
							swSPOutput.Write("{0}{1}\t[{2}] = DEFAULT", sCommaSuffix, Environment.NewLine, m_arrfFields[i].sFieldName);
						}
						else
						{
							// Write first a comma suffix on the current line, then a new line and then the current field.
							swSPOutput.Write("{0}{1}\t[{2}] = {3}", sCommaSuffix, Environment.NewLine, m_arrfFields[i].sFieldName, m_arrfFields[i].GetDefaultSPValueAsString());
						}
					}
				}
			}
			
			// Write WHERE clause, use only Primary key fields
			// Because the previous line isn't closed yet, write a newline.
			swSPOutput.WriteLine(Environment.NewLine + "WHERE");
			for(int i = 0, iEmittedClauses = 0;i < m_arrfFields.Length;i++)
			{
				sANDClause = "AND ";
				
				if(iEmittedClauses == 0)
				{
					sANDClause="";
				}
				if(m_arrfFields[i].bIsPrimaryKey)
				{	
					// Field is primary key. Emit clause. Primary key is always an input param. 
					swSPOutput.WriteLine("\t" + sANDClause + "[" + m_arrfFields[i].sFieldName + "] = " + m_arrfFields[i].GetFieldAsTSQLParam(m_bSPPrefixParams));
					iEmittedClauses++;
				}
			}
		}


		/// <summary>
		/// Purpose: writes the list of fields which is specified in an INSERT command, plus a VALUES block for these
		/// fields.
		/// </summary>
		/// <param name="swSPOutput">An open streamwriter object to the T-SQL file being written to.</param>
		/// <remarks>Routine contains code by Colin Young of GB RetailExchange, which fixed a bug in v1.1.</remarks>
		private void WriteSPInsertFieldValueBlock(StreamWriter swSPOutput)
		{
			string	sCommaSuffix;
			bool	bFirst=true;
			
			// all fields
			swSPOutput.Write("(");
			
			for(int i = 0;i < m_arrfFields.Length;i++)
			{
				sCommaSuffix = ",";

				if((!m_arrfFields[i].bIsIdentity)&&(!m_arrfFields[i].bIsComputed)&&(!m_arrfFields[i].bIsTimeStamp))
				{
					if(bFirst)
					{
						// first field to emit, no commasuffix is written, since there is no field written yet.
						bFirst = false;
						sCommaSuffix = "";
					}
					swSPOutput.Write("{0}{1}\t[{2}]", sCommaSuffix, Environment.NewLine, m_arrfFields[i].sFieldName);
				}
			}
			
			// write end of this block plus start of values block
			// Because the previous line isn't closed yet, write a newline.
			swSPOutput.Write(Environment.NewLine + ")" + Environment.NewLine + "VALUES" + Environment.NewLine + "(");

			bFirst=true;
			// write values
			for(int i = 0;i < m_arrfFields.Length;i++)
			{	
				sCommaSuffix = ",";
				if((!m_arrfFields[i].bIsIdentity)&&(!m_arrfFields[i].bIsComputed)&&(!m_arrfFields[i].bIsTimeStamp))
				{
					if(bFirst)
					{
						// first field to emit, no commasuffix is written, since there is no field written yet.
						bFirst = false;
						sCommaSuffix = "";
					}
				
					// check if this field is in the inputfield list. If so, use the parameter representation value,
					// otherwise use the default value reported by the clsField object.
					if(m_alSPInputParams.IndexOf(m_arrfFields[i]) >= 0)
					{
						// it is in the parameterlist, use it. Check for default value first, plus nullability
						if(!m_arrfFields[i].bIsNullable && (m_arrfFields[i].sDefaultValue.Length > 0))
						{
							// has default value and is not nullable. Emit extra check
							swSPOutput.Write("{0}{1}\tISNULL({2}, {3})", sCommaSuffix, Environment.NewLine,
								 m_arrfFields[i].GetFieldAsTSQLParam(m_bSPPrefixParams), m_arrfFields[i].sDefaultValue);
						}
						else
						{
							// just emit the parameter value. 
							// Write first a comma suffix on the current line, then a new line and then the current field.
							swSPOutput.Write("{0}{1}\t{2}", sCommaSuffix, Environment.NewLine, m_arrfFields[i].GetFieldAsTSQLParam(m_bSPPrefixParams));
						}
					}
					else
					{
						// use default value. 
						if(m_arrfFields[i].sDefaultValue.Length > 0)
						{
							// emit the statement which will insert the default value for this field.
							swSPOutput.Write("{0}{1}\tDEFAULT", sCommaSuffix, Environment.NewLine);
						}
						else
						{
							// field doesn't have a default value. 
							// Write first a comma suffix on the current line, then a new line and then the current field.
							swSPOutput.Write("{0}{1}\t{2}", sCommaSuffix, Environment.NewLine, m_arrfFields[i].GetDefaultSPValueAsString());
						}
					}
				}
			}
			swSPOutput.WriteLine(Environment.NewLine + ")");
		}


		/// <summary>
		/// Purpose: writes the fieldlist for the SELECT fieldlist FROM table stored procedures
		/// </summary>
		/// <param name="swSPOutput">An open streamwriter object to the T-SQL file being written to.</param>
		private void WriteSPSelectFieldList(StreamWriter swSPOutput)
		{
			string	sCommaSuffix;
			
			for(int i = 0;i < m_arrfFields.Length;i++)
			{
				sCommaSuffix = ",";
				if(i==m_arrfFields.Length-1)
				{
					sCommaSuffix = "";
				}
				swSPOutput.WriteLine("\t[" + m_arrfFields[i].sFieldName + "]" + sCommaSuffix);
			}

		}


		/// <summary>
		/// Purpose: writes a parameter list for the current stored procedure generated, which contains the
		/// input and output parameters, currently stored in m_alSPInputParams and m_alSPOutputParams.
		/// </summary>
		/// <param name="swSPOutput">An open streamwriter object to the T-SQL file being written to.</param>
		private void WriteSPArguments(StreamWriter swSPOutput)
		{
			string	sCommaSuffix;
			int		iIndx;
			
			// input params
			iIndx=0;
			foreach(clsField ofCurrent in m_alSPInputParams)
			{
				sCommaSuffix="";
				if(iIndx < (m_alSPInputParams.Count-1) || m_alSPOutputParams.Count > 0)
				{
					sCommaSuffix = ",";
				}
				swSPOutput.WriteLine("\t" + ofCurrent.GetFieldAsTSQLParam(m_bSPPrefixParams) + " " + ofCurrent.GetFieldTypeAsTSQLType() + sCommaSuffix);
				iIndx++;
			}
			// output params
			iIndx=0;
			foreach(clsField ofCurrent in m_alSPOutputParams)
			{
				sCommaSuffix="";
				if(iIndx < (m_alSPOutputParams.Count-1))
				{
					sCommaSuffix = ",";
				}
				swSPOutput.WriteLine("\t" + ofCurrent.GetFieldAsTSQLParam(m_bSPPrefixParams) + " " + ofCurrent.GetFieldTypeAsTSQLType() + " OUTPUT" + sCommaSuffix);
				iIndx++;
			}
		}


		/// <summary>
		/// Purpose: Generates a complete list of fields in m_alSPInputParams, which can be used
		/// as inputparams list for the SP generation routines.
		/// </summary>
		/// <param name="ofCurrent">Fieldobject which is the solely input param</param>
		/// <param name="swSPOutput">An open streamwriter object to the T-SQL file being written to.</param>
		/// <param name="bEmitAlsoFieldAlsOldValue">True if the field ofCurrent also should be emitted as
		/// extra argument with extension 'Old', used for UpdateAll Stored Procs.</param>
		private void WriteSPArguments(clsField ofCurrent, StreamWriter swSPOutput, bool bEmitAlsoFieldAlsOldValue)
		{
			string	sCommaSuffix;
			int		iIndx;
			
			// input params
			sCommaSuffix = ",";
			if(m_alSPOutputParams.Count<=0 && !bEmitAlsoFieldAlsOldValue)
			{
				sCommaSuffix = "";
			}
			swSPOutput.WriteLine("\t" + ofCurrent.GetFieldAsTSQLParam(m_bSPPrefixParams) + " " + ofCurrent.GetFieldTypeAsTSQLType() + sCommaSuffix);
			if(bEmitAlsoFieldAlsOldValue)
			{
				swSPOutput.WriteLine("\t" + ofCurrent.GetFieldAsTSQLParam(m_bSPPrefixParams) + "Old " + ofCurrent.GetFieldTypeAsTSQLType() + sCommaSuffix);
			}
			// output params
			iIndx=0;
			foreach(clsField ofOutputCurrent in m_alSPOutputParams)
			{
				sCommaSuffix="";
				if(iIndx < (m_alSPOutputParams.Count-1))
				{
					sCommaSuffix = ",";
				}
				swSPOutput.WriteLine("\t" + ofOutputCurrent.GetFieldAsTSQLParam(m_bSPPrefixParams) + " " + ofOutputCurrent.GetFieldTypeAsTSQLType() + " OUTPUT" + sCommaSuffix);
				iIndx++;
			}
		}

		
		/// <summary>
		/// Purpose: writes a commentblock for the current stored procedure generated, which contains the
		/// input and output parameters, currently stored in m_alSPInputParams and m_alSPOutputParams.
		/// </summary>
		/// <param name="swSPOutput">An open streamwriter object to the T-SQL file being written to.</param>
		private void WriteSPArgumentComments(StreamWriter swSPOutput)
		{
			foreach(clsField ofCurrent in m_alSPInputParams)
			{
				swSPOutput.WriteLine("-- Gets: " + ofCurrent.GetFieldAsTSQLPar