📄 codestructure_tsql.htm
字号:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 7.0">
<TITLE>LLBLGen::Structure of the T-SQL code</TITLE>
<link rel="STYLESHEET" type="text/css" href="general.css">
</HEAD>
<BODY>
<br>
<h2>LLBLGen::Structure of the T-SQL code</h2>
<p>
LLBLGen generates two types of source code: .NET code (in C# or VB.NET) and T-SQL code. The T-SQL code contains
the generated stored procedures, the .NET code the classes which call and use these stored procedures. Because
LLBLGen first generates all possible stored procedures and then the .NET class based on which stored procedures
are generated, it's necessary to first read this page and then the <a href="codestructure_net.htm">Structure of .NET code</a>
page.
</p>
<h3>The T-SQL code generator</h3>
<p>
LLBLGen will process each table and view in the database which is checked on the
<a href="gui_tables.htm">Tables / Views tab</a> individually. Per table and view, each field definition is read
and stored in a list. LLBLGen tries to determine if a field is part of the Primary Key, if it's an Identity field,
if it's a Foreign Key, if it's a computed field or if it has a UNIQUE constraint. This information, besides the
type, precision and other field information, is used to determine which stored procedures should be generated and
which fields should be passed as parameters and if the stored procedure should return a value, f.e. an error code or
the value of the new Primary Key which is for example an Identity field.
</p>
<h4>The generated procedures</h4>
<p>
Per table or view, LLBLGen tries to generate all stored procedures build in the generator. Below are detailed descriptions
for each stored procedure, when they're generated, which fields are used as input parameters and other useful information.
<br><br>
<b class="SmallHdr">[SP prefix]<i>TableName</i>_Insert</b>
<table width="100%" cellpadding="2" cellspacing="0" border="1">
<tr>
<td valign="top" width="140"><b>When generated</b></td>
<td>Always.</td>
</tr>
<tr>
<td valign="top" width="140"><b>Purpose</b></td>
<td>Inserts one new row in the database, using the specified values.</td>
</tr>
<tr>
<td valign="top" width="140"><b>Input parameters</b></td>
<td>All fields of the table / view, except excluded fields, Identity fields, Timestamp fields or computed fields.</td>
</tr>
<tr>
<td valign="top" width="140"><b>Output parameters</b></td>
<td>The value of the Identity column, if present, and 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>Nothing.</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>_Delete</b>
<table width="100%" cellpadding="2" cellspacing="0" border="1" ID="Table1">
<tr>
<td valign="top" width="140"><b>When generated</b></td>
<td>When the table has a Primary Key defined.</td>
</tr>
<tr>
<td valign="top" width="140"><b>Purpose</b></td>
<td>Deletes one single row from the table. The Primary Key is used to locate the row which should be deleted.</td>
</tr>
<tr>
<td valign="top" width="140"><b>Input parameters</b></td>
<td>All fields of the Primary Key.</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>Nothing.</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>_DeleteW<i>PrimaryKeyField</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 Primary Key of the table consists of more than one field. LLBLGen generates one stored procedure
for each field of the multi-field Primary Key. </td>
</tr>
<tr>
<td valign="top" width="140"><b>Purpose</b></td>
<td>Deletes all rows where the <i>PrimaryKeyField</i> has the value specified.</td>
</tr>
<tr>
<td valign="top" width="140"><b>Input parameters</b></td>
<td>A value for the field <i>PrimaryKeyField</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>Nothing.</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>_Update</b>
<table width="100%" cellpadding="2" cellspacing="0" border="1">
<tr>
<td valign="top" width="140"><b>When generated</b></td>
<td>When there are fields in the table which are <i>not</i> a:
<ul>
<li>Part of the Primary Key</li>
<li>Identity field</li>
<li>Computed field</li>
<li>Excluded field</li>
<li>Timestamp field</li>
</ul>
plus the table has a Primary Key.
</td>
</tr>
<tr>
<td valign="top" width="140"><b>Purpose</b></td>
<td>Updates one single row in the database. It updates the fields which are not part of the Primary Key, are not an
Identity field, are not a Timestamp field, are not a computed field with the values specified or with a
<a href="ref_types.htm">default value</a>
when the field is an Excluded field. It uses the Primary Key specified to locate the row which should be updated. It doesn't use an
'oldvalue' check for old-style concurrency checking. In n-tier stateless multi-user applications, developers should
use a higher level functional locking mechanism which is part of the application to prevent multi-user overwrites
of the same row if that's not wanted.
</td>
</tr>
<tr>
<td valign="top" width="140"><b>Input parameters</b></td>
<td>All fields which are updated plus the Primary Key fields, except the excluded fields.</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>Nothing.</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>_UpdateAllW<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 which is not part of the Primary Key. LLBLGen generates
one stored procedure for each Foreign Key field.</td>
</tr>
<tr>
<td valign="top" width="140"><b>Purpose</b></td>
<td>Updates <i>ForeignKeyField</i> with the given 'new' value in all rows with the given
'old' value for <i>ForeignKeyField</i>. This stored procedure is necessary to reset Foreign Keys to
avoid Foreign Key constraint voilations when the corresponding Primary Key value is deleted.
</td>
</tr>
<tr>
<td valign="top" width="140"><b>Input parameters</b></td>
<td>The old and new values for <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>Nothing.</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>_SelectOne</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 a Primary Key.</td>
</tr>
<tr>
<td valign="top" width="140"><b>Purpose</b></td>
<td>Selects one single row from the table. It uses the Primary Key to locate the row which should be returned.</td>
</tr>
<tr>
<td valign="top" width="140"><b>Input parameters</b></td>
<td>The fields of the Primary Key</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>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -