1
ALTER TABLE [dbo].[Order Details] WITH NOCHECK ADD CONSTRAINT [FK_Order_Details_Orders] FOREIGN KEY([OrderID])
2 REFERENCES [dbo].[Orders] ([OrderID])
3 GO
4
5 Alter TABLE [dbo].[Order Details] WITH NOCHECK ADD CONSTRAINT [FK_Order_Details_Products] FOREIGN KEY([ProductID])
6 REFERENCES [dbo].[Products] ([ProductID])
7 GO
1 // Order Entity:
2
3
/**//// <summary>
4
/// Holds a collection of ProductsFromOrderDetails objects
5 /// which are related to this object with junction table OrderDetails
6
/// </summary>
7 [BindableAttribute()]
8 public TList<Products> ProductsCollection_From_OrderDetails
9 ...{
10
get ...{ return entityData.ProductsCollection_From_OrderDetails; }
11 set ...{ entityData.ProductsCollection_From_OrderDetails = value; }
12 }
13
14
15 //Product Entity:
16
17 /**//// <summary>
18 /// Holds a collection of OrdersFromOrderDetails objects
19 /// which are related to this object through junction table OrderDetails
20 /// </summary>
21 [BindableAttribute()]
22 public TList<Orders> OrdersCollection_From_OrderDetails
23 ...{
24 get ...{ return entityData.OrdersCollection_From_OrderDetails; }
25 set ...{ entityData.OrdersCollection_From_OrderDetails = value; }
26 }
.
SQL:
1 CREATE UNIQUE NONCLUSTERED INDEX [IX_Person] ON [dbo].[Person]
2 (
3 [Name] ASC
4 )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
OUTPUT:
1 /**//// <summary>
2 /// Gets rows from the datasource based on IX_Person index.
3 /// </summary>
4 /// <param name="name"></param>
5 /// <returns>Returns an instance of the <see cref="Person"/></returns>
6 public Person GetByName(System.String name)
7 ...{
8 int count = -1;
9 //Calls Provider specific Implementation
10
11 return GetByName(null,name, 0, int.MaxValue, out count);
12 }
EditCustom Stored Procedures
There are several times when you want to extend the data access API, but you still want to leverage much of the generated approach to the data layer. .netTiers offeres the ability to write your own procedures and be able to do things that .netTiers can not do out of the box or are specialized to your application.
For example, if you wanted to create a custom stored procedure to handle getting all products below a certain inventory. When you begin the generation process, the CodeSmith SchemaExplorer will attempt to discover all of the rich meta data provided to determine of this procedure returns a resultset, which parameters it takes (input & output).
1 -- Get the products that have less units in stock than the @UnitsInStock parameter.
2 CREATE PROCEDURE dbo._Product_GetWithStockBelow
3 @UnitsInStock smallint
4 AS
5
6 SELECT
7 [ProductID],
8 [ProductName],
9 [SupplierID],
10 [CategoryID],
11 [QuantityPerUnit],
12 [UnitPrice],
13 [UnitsInStock],
14 [UnitsOnOrder],
15 [ReorderLevel],
16 [Discontinued]
17 FROM
18 [dbo].[Product]
19 WHERE
20 [UnitsInStock] < @UnitsInStock
21 GO
Output:
1 public TList<Product> GetWithStockBelow(System.Int16 unitsInStock)
2 ...{
3
4 int count = -1;
5 //Calls Provider specific Implementation
6
7 return GetWithStockBelow(
8 null, unitsInStock, 0, int.MaxValue, out count);
9
10 }
NOTE:
There are some situations where the procedure will not return results as expected. The Custom Stored procedures don't work when using temp tables within the custom stored procedure. This is because when CodeSmith's SchemaExplorer is discovering this information, it doesn't have necessary priveldges to create a temp table. One workaround is to use a table variable in the stored procedure instead of a temp table. Warning: Table variables are held in memory on the server so do not load too many rows in the table variable or the servers performance will be impacted.
IMPORTANT:
In order for .netTiers to return an entity that maps to the table you've created the Custom Stored Procedure for; all of the columns being returned must match type and be in the correct order. Therefore it is recommended to SELECT * from the source table where possible instead of specifying each column in the select.
If you notice that the generated code for your csp starts returning "void" instead of what you expect check that you csp is still valid
EditEnum Tables
.netTiers will create enums based on table data for the tables that you designate. This is useful for fairly static data or type tables.
Rules:
The tables you would like to generate as enums must meet the following rules.
1. The first column must be a primary key (typically this would be an int Identity column),
2. The second column must have a unique column constraint index, the optional third column will be the description of the generated enum.
3. You must also select this table in the SourceTable as well in order to generate the enum.
Example:
1CREATE TABLE [dbo].[BankAccountType](
2 [BankAccountTypeId] [int] IDENTITY(1,1) NOT NULL,
3 [BankAccountTypeName] [varchar](50) NOT NULL,
4 [BankAccountTypeDescription] [varchar](250)NULL,
5 CONSTRAINT [BankAccountType_PK] PRIMARY KEY CLUSTERED
6(
7 [BankAccountTypeId] ASC
8)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],
9 CONSTRAINT [BankAccountType_UC1] UNIQUE NONCLUSTERED
10(
11 [BankAccountTypeName] ASC
12)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
13) ON [PRIMARY]
14
15-- Add Table Description
16EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'All allowable Checking Account types for my ABC System' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'BankAccountType'
17
18
19Some Enum Items:
20INSERT INTO [dbo].[BankAccountType] VALUES ('Checking', 'A Valid Checking Account')
21INSERT INTO [dbo].[BankAccountType] VALUES ('Savings', 'A Valid Savings Account')
Generated Enumeration:
1 /**//// <summary>
2 /// All allowable Checking Account types for my ABC System
3 /// </summary>
4 /// <remark>Enum that contains the items in BankAccountType</remark>
5 [Serializable]
6 public enum BankAccountTypeList
7 ...{
8
9 /**//// <summary>
10 /// A Valid Checking Account
11
/// </summary>
12 [EnumTextValue("A Valid Checking Account")]
13 Checking = 1,
14
15
16
17 /**//// <summary>
18 /// A Valid Savings Account
19 /// </summary>
20 [EnumTextValue("A Valid Savings Account")]
21 Savings = 2
22 }