codestructure_tsql.htm

LLBLGen 1.21 Sourcecode

	</tr>
	<tr>
		<td valign="top" width="140"><b>Returns</b></td>
		<td>The row with the corresponding values for the Primary Key fields or nothing if there is no row with the specified
			Primary Key values.</td>
	</tr>
	<tr>
		<td valign="top" width="140"><b>Table / View</b></td>
		<td>Table.</td>
	</tr>
	</table>
	<br clear="all"><br>
	<b class="SmallHdr">[SP prefix]<i>TableName</i>_SelectOneW<i>UniqueField</i>Logic</b>
	<table width="100%" cellpadding="2" cellspacing="0" border="1">
	<tr>
		<td valign="top" width="140"><b>When generated</b></td>
		<td>When the table has at least one field with a UNIQUE constraint or which is an Identity field but not part of the
			Primary Key or is a GUID column. LLBLGen generates one stored procedure for each field which matches the criteria.</td>
	</tr>
	<tr>
		<td valign="top" width="140"><b>Purpose</b></td>
		<td>Selects one single row from the table. It uses <i>UniqueField</i> to locate the row which should be returned.</td>
	</tr>
	<tr>
		<td valign="top" width="140"><b>Input parameters</b></td>
		<td>A value for the field <i>UniqueField</i></td>
	</tr>
	<tr>
		<td valign="top" width="140"><b>Output parameters</b></td>
		<td>The value of @@ERROR if selected as an option in the GUI.</td>
	</tr>
	<tr>
		<td valign="top" width="140"><b>Returns</b></td>
		<td>The row with the corresponding values for <i>UniqueField</i> or nothing if there is no row with the specified
			<i>UniqueField</i> value.</td>
	</tr>
	<tr>
		<td valign="top" width="140"><b>Table / View</b></td>
		<td>Table.</td>
	</tr>
	</table>
	<br clear="all"><br>
	<b class="SmallHdr">[SP prefix]<i>TableName</i>_SelectAll</b>
	<table width="100%" cellpadding="2" cellspacing="0" border="1">
	<tr>
		<td valign="top" width="140"><b>When generated</b></td>
		<td>Allways.</td>
	</tr>
	<tr>
		<td valign="top" width="140"><b>Purpose</b></td>
		<td>Selects all rows from the table or view. When the table has a Primary Key, the rows are sorted on that
			Primary Key, ascending, using all fields of the Primary Key.</td>
	</tr>
	<tr>
		<td valign="top" width="140"><b>Input parameters</b></td>
		<td>None.</td>
	</tr>
	<tr>
		<td valign="top" width="140"><b>Output parameters</b></td>
		<td>The value of @@ERROR if selected as an option in the GUI.</td>
	</tr>
	<tr>
		<td valign="top" width="140"><b>Returns</b></td>
		<td>All rows in the table or view, sorted when applicable, or nothing when the table or view is empty.</td>
	</tr>
	<tr>
		<td valign="top" width="140"><b>Table / View</b></td>
		<td>Table and View</td>
	</tr>
	</table>
	<br clear="all"><br>
	<b class="SmallHdr">[SP prefix]<i>TableName</i>_SelectAllW<i>ForeignKeyField</i>Logic</b>
	<table width="100%" cellpadding="2" cellspacing="0" border="1">
	<tr>
		<td valign="top" width="140"><b>When generated</b></td>
		<td>When the table has at least one Foreign Key field which is not the same as the Primary Key when the Primary Key consists
			of just one field.</td>
	</tr>
	<tr>
		<td valign="top" width="140"><b>Purpose</b></td>
		<td>Selects all rows which have the given value for <i>ForeignKeyField</i>. The rows are not sorted.</td>
	</tr>
	<tr>
		<td valign="top" width="140"><b>Input parameters</b></td>
		<td>A value for the field <i>ForeignKeyField</i>.</td>
	</tr>
	<tr>
		<td valign="top" width="140"><b>Output parameters</b></td>
		<td>The value of @@ERROR if selected as an option in the GUI.</td>
	</tr>
	<tr>
		<td valign="top" width="140"><b>Returns</b></td>
		<td>All rows in the table which have the given value for the field <i>ForeignKeyField</i>, or nothing if no rows are found
			with the specified value for <i>ForeignKeyField</i>.</td>
	</tr>
	<tr>
		<td valign="top" width="140"><b>Table / View</b></td>
		<td>Table.</td>
	</tr>
	</table>
	<br clear="all">
</p>
<p>
	<b class="SmallHdr">Database prerequisits</b><br>
	To be able to determine which Foreign Key - Primary Key relations are present, LLBLGen needs a database where
	Foreign Key constraints and Primary Key's are already defined, plus when appropriate, UNIQUE constraints. 
	Also Default Value constraints should be in place with the fields who should have default values, plus these
	default values should be of the right value.
	It's good practise to create these,	just after the table layouts are defined. Databases with no Primary Keys 
	nor Foreign Key constraints can't be used to generate a solid data-access layer because LLBLGen doesn't know which 
	fields are the Primary Key nor will it be able to determine which fields in which tables are Foreign Keys. 
	If you use 'shadow keys', which are fields which are also used to select particular rows from the table, check 
	if a UNIQUE constraint can be applied to these fields, so LLBLGen can generate a SelectOne type stored procedure 
	for these fields.
</p>
<p>
	<b class="SmallHdr">Database NULL values</b><br>
	Allthough NULL values in databases should be avoided for most purposes, some people say they need them, so LLBLGen supports
	NULL values for fields. The generated stored procedures use T-SQL Parameters, so are not aware of NULL values and work
	the same with NULL values or real values for a given field. The only exception to this are fields which are not NULLable
	but have a default value. LLBLGen generates code which inserts the default value when NULL is passed in the corresponding
	parameter for that field, or the value passed if it's not NULL.
</p>
<h4>Exclusion of fields</h4>
<p>
	LLBLGen supports exclusion of one or more fields, so these fields will not turn up in input parameter lists for
	stored procedures for insertion and update of rows. For example a field 'CreateDate' which represents a
	datetime field with the date/time on which the record was created, can be filled directly with a default
	value in the Stored Procedure. By excluding this field 'CreateDate' from insert and update queries, LLBLGen
	will assign to this field the default value for a datetime field, which is GETDATE(). All rows updated or inserted
	will then have the accurate value for CreateDate automatically. When a field has a default value defined in
	SQLServer, LLBLGen will use that default value as the value to insert in for that field.
	See <a href="ref_types.htm">SQLServer, .NET type relations</a> for a complete list of default
	values per supported type.
</p>	
<h4>T-SQL code format</h4>
<p>
	The T-SQL Code generated is placed in one file, to make it easier to import the Stored Procedure code into a
	database. All T-SQL code is specified using SQLServer 7 '[' and ']' markers for parameters and Stored Procedure names,
	plus all names are prefixed with '[dbo].', so all users will be able to use the generated stored procedures plus 
	these prefixes will prevent unnecessary recompilations of stored procedures. Identity values are determined using
	SCOPE_IDENTITY() (SQLServer 2000) or @@IDENTITY (SQLServer 7), so insert triggers defined on tables are not affecting 
	the retrieval of Identity values (SQLServer 2000). Users of SQLServer 7 should be aware that Insert triggers still can 
	change the @@IDENTITY parameter and can result in wrong return values. This is unavoidable in SQLServer 7, when using 
	Insert triggers on tables with an Identity column when these triggers insert rows in tables with an Identity column.
	Identity values are returned by the stored procedures. When selected in the <a href="gui_tsqlcode.htm">GUI</a>, LLBLGen 
	will also generate an ErrorCode parameter as output parameter for every Stored Procedure generated. The value of 
	@@ERROR is returned in this variable. 
</p>
<h4>Naming scheme of stored procedures</h4>
<p>
	LLBLGen will name the stored procedures generated in a standarized way, so you can find back the correct stored procedure
	easily in the generated set. The following table describes the different stored procedures using an example table 'Foo'
	with field 'My Field'. Take note of the space in 'My Field'. 
	<br clear="all"><br>
	<table border="1" cellpadding="2" cellspacing="0">
		<tr bgcolor="LightGrey">
			<td class="TDSmallerfont"><b>SP Naming syntax</b></td>
			<td class="TDSmallerfont"><b>SP Example</b></td>
		</tr>
		<tr>
			<td class="TDSmallerFont">[SP prefix]<i>TableName</i>_Insert</td>
			<td class="TDSmallerFont">pr_Foo_Insert</td>
		</tr>
		<tr>
			<td class="TDSmallerFont">[SP prefix]<i>TableName</i>_Delete</td>
			<td class="TDSmallerFont">pr_Foo_Delete</td>
		</tr>
		<tr>
			<td class="TDSmallerFont">[SP prefix]<i>TableName</i>_DeleteAllW<i>PrimaryKeyField</i>Logic</td>
			<td class="TDSmallerFont">pr_Foo_DeleteAllWMy_FieldLogic</td>
		</tr>
		<tr>
			<td class="TDSmallerFont">[SP prefix]<i>TableName</i>_Update</td>
			<td class="TDSmallerFont">pr_Foo_Update</td>
		</tr>
		<tr>
			<td class="TDSmallerFont">[SP prefix]<i>TableName</i>_UpdateAllW<i>ForeignKeyField</i>Logic</td>
			<td class="TDSmallerFont">pr_Foo_UpdateAllWMy_FieldLogic</td>
		</tr>
		<tr>
			<td class="TDSmallerFont">[SP prefix]<i>TableName</i>_SelectOne</td>
			<td class="TDSmallerFont">pr_Foo_SelectOne</td>
		</tr>
		<tr>
			<td class="TDSmallerFont">[SP prefix]<i>TableName</i>_SelectOneW<i>UniqueField</i>Logic</td>
			<td class="TDSmallerFont">pr_Foo_SelectOneWMy_FieldLogic</td>
		</tr>
		<tr>
			<td class="TDSmallerFont">[SP prefix]<i>TableName</i>_SelectAll</td>
			<td class="TDSmallerFont">pr_Foo_SelectAll</td>
		</tr>
		<tr>
			<td class="TDSmallerFont">[SP prefix]<i>TableName</i>_SelectAllW<i>ForeignKeyField</i>Logic</td>
			<td class="TDSmallerFont">pr_Foo_SelectAllWMy_FieldLogic</td>
		</tr>
	</table>
	<br clear="all">
	You shouldn't use spaces in table names, and you should avoid spaces in fieldnames. LLBLGen will
	generate code that will handle names with spaces correctly, however it's more cumbersome to
	write T-SQL code using tablenames and fieldnames with spaces, due to the requirement of '[' and ']' delimiters.
</p>
<div align="right" class="SmallFontTOC">
	<hr size="1" width="60%" align="right">
	LLBLGen v1.2 documentation. &copy; 2002 <a href="http://www.sd.nl/" target="_blank">Solutions Design</a>
</div>
<br><br><br>
</BODY>
</HTML>