Navigation

Home
Documentation
Download
Forums
Samples
FAQ
Team
Support
Project
Quick Search

Advanced Search »
Help Wanted! If you are using .netTiers and find it as invaluable as we do, please consider giving back to the .netTiers team by helping with our effort to fully document .netTiers. To help, simply create an account and you will then be able to edit this wiki.
Back History
Database and SQL

{TOC}

===Database Model and Sql:===
How does .netTiers figure out what to generate?
.netTiers uses the rich meta data CodeSmith provides that is found from database in order to create a complete object domain based on your data relationships.  The SchemaExplorer provides information on Database, Table, View, Indexes, StoredProcedures, and more.  There are several important aspects to keep in mind when creating or using an existing database.   In a perfect world, you would try and utilize these best practices. 

===Best Practices:===

((( 
* Table names are singular and Pascal Case. ex. Order, Product, File
* Fields are Pascal Case, ex. FirstName, LastName, MiddleInitial
* Description is provided for Tables, Columns, and Keys as an Extended Attribute.  
** For MSSQL, it is "MS_Description" key for the extended attribute.
* Using the ParseDbColDefaultVal option it is possible to get .netTiers to default your entity properties using your database defaults. Bear in mind that only simple constants and a few functions are supported (getdate(), getutcdate()). User defined schema defaults are not supported
*.netTiers can not assume relationships, it's imperative that you actually create Foreign keys associated to the parent table's primary key.  )))

====Relationships====
Using Composition in your Model.
Given the following Tables, we will create a One to One relationship,  meaning for every person there can be a single unique Contact record for that Person.    
<code lang="SQL" linenumbers="true">
-- Create the Base Relationship, has a unique primary key amongst both tables.
CREATE TABLE [dbo].[Person](
    [PersonId] [int] NOT NULL,
    [Name] [varchar](50) NOT NULL,
CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED 
(
    [PersonId] ASC
) WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]


--Create the other side of the 1:1 relationship   
CREATE TABLE [dbo].[Contact](
    [ContactPersonId] [int] NOT NULL,
    [ContactNumber] [varchar](30) ,
CONSTRAINT [PK_Contact] PRIMARY KEY CLUSTERED 
(
    [ContactPersonId] ASC
) WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]


-- Example of 1:1 relationship
-- The contact primary key is the is also a foreign key relationship to the PersonId.  
-- Therefore creating a 1:1 relationship.
 
ALTER TABLE [dbo].[Contact]  WITH CHECK ADD  CONSTRAINT [FK_Contact_Person]     
FOREIGN KEY([ContactPersonId])
REFERENCES [dbo].[Person] ([PersonId])
GO 
</code>

    Your generated entities would look like this.
    For every Person entity, you would have a composite property to the Contact entity.

<code lang="C#" linenumbers="true">
/// <summary>
///    Holds a Contact object
///    which is related to this object through the relation Contact
/// </summary>
[BindableAttribute()]
public Contact Contact
{
    get { return entityData.Contact; }
    set { entityData.Contact = value; }    
}
</code>

For every Contact entity, since you are working with the actual foreign key, that is your primary key, you would get a PersonId and a PersonIdSource of type Parent.

<code lang="C#" linenumbers="true">       
private Person _contactPersonIdSource = null;

/// <summary>
/// Gets or sets the source <see cref="Person"/>.
/// </summary>
/// <value>The source Person for ContactPersonId.</value>
[Browsable(false), BindableAttribute()]
public virtual Person ContactPersonIdSource
{
    get { return this._contactPersonIdSource; }
    set { this._contactPersonIdSource = value; }
}
</code>

====Using Collection Relationships.====

Given a modified for brevity version of some of the familiar Northwind Database.

<code lang="SQL" linenumbers="true">
    -- ORDER
    -- A familiar pattern in database design, which is the Header/Details pattern in which 
    -- one table contains the the overall summary of all the 
    -- detail items contained within a particular detail Table.
    -- Think of a single order always *could* potentially have many order items.  
    -- Just like on your grocery receipt. 
    
    -- the Header Table
    CREATE TABLE [dbo].[Orders](
        [OrderID] [int] IDENTITY(1,1) NOT NULL,
        [CustomerID] int NOT NULL,
        [EmployeeID] [int] NULL,
     CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED 
    (
        [OrderID] ASC
    ) ON [PRIMARY]
    ) ON [PRIMARY]


    -- the Details Table
    CREATE TABLE [dbo].[Order Details](
        [OrderID] [int] NOT NULL,
        [ProductID] [int] NOT NULL,
     CONSTRAINT [PK_Order_Details] PRIMARY KEY CLUSTERED 
    (
        [OrderID] ASC,
        [ProductID] ASC
    ) ON [PRIMARY]
    ) ON [PRIMARY]

    -- the Customer Table
    CREATE TABLE [dbo].[Customer](
        [CustomerID] [int] NOT NULL,
        [ContactName] [nvarchar](30) NULL,
     CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED 
    (
        [CustomerID] ASC
    ) ON [PRIMARY]
    ) ON [PRIMARY]



    Example of Many To One relationship:
    -- This creates a simple relationship between the Orders Table and the Customer, so that in your 
    -- for every OrderEntity entity, you will have a CustomerId foreign key integer, and a 
    -- CustomerIdSource entity of type Customer nested composite entity in your Order class.    

ALTER TABLE [dbo].[Order]  WITH NOCHECK ADD  CONSTRAINT [FK_Order_Customer] 
    FOREIGN KEY([CustomerID])
    REFERENCES [dbo].[Customer] ([CustomerID])
    GO 

    Example of One to Many Relationship:
    -- This creates a one to many relationship between the Order and the Order Detail tables.  
    -- Meaning, in your generated code, 
    -- for every Order entity, you will have an OrderDetailCollection of type TList<OrderDetail>.  
    -- This means you can have an entire
    -- list of OrderDetail entities within your Order entity.
    
ALTER TABLE [dbo].[Order Details]  WITH NOCHECK ADD  CONSTRAINT [FK_Order_Details_Orders]             FOREIGN KEY([OrderID])
    REFERENCES [dbo].[Orders] ([OrderID])
    GO
</code>



====Many To Many Relationship====
The last of the intrinsic relationships are the Many to Many relationships.  The m:m relationship consists of 3 tables, the left table, the junction table and the right table and is essentially a flexible 1:m relationship but with two tables using a junction to facilitate the relationship.  An example of this, is the Orders, Order Details, and the Products table.  Where the order can have many Order Details that all have Products, while at the same time any one Product can be in any Order Detail which is associated with many orders.  This junction table holds both references to the left and right tables.  The nice thing about this relationship is that we can infer from the Order entity that the Order Entity will have a collection of type TList<Products> and similarly will see an Orders collection for every Product because of the junction table of Order Datails.

<code lang="SQL" linenumbers="true">
    ALTER TABLE [dbo].[Order Details]  WITH NOCHECK ADD  CONSTRAINT [FK_Order_Details_Orders]         FOREIGN KEY([OrderID])
    REFERENCES [dbo].[Orders] ([OrderID])
    GO

    Alter TABLE [dbo].[Order Details]  WITH NOCHECK ADD  CONSTRAINT [FK_Order_Details_Products]     FOREIGN KEY([ProductID])
    REFERENCES [dbo].[Products] ([ProductID])
    GO
</code>


<code lang="C#" linenumbers="true">
    // Order Entity:
    
    /// <summary>
    ///    Holds a collection of ProductsFromOrderDetails objects
    ///    which are related to this object with junction table OrderDetails
    /// </summary>    
    [BindableAttribute()]
    public TList<Products> ProductsCollection_From_OrderDetails
    {
        get { return entityData.ProductsCollection_From_OrderDetails; }
        set { entityData.ProductsCollection_From_OrderDetails = value; }    
    }


    //Product Entity:

    /// <summary>
    /// Holds a collection of OrdersFromOrderDetails objects
    /// which are related to this object through junction table OrderDetails
    /// </summary>    
    [BindableAttribute()]
    public TList<Orders> OrdersCollection_From_OrderDetails
    {
       get { return entityData.OrdersCollection_From_OrderDetails; }
       set { entityData.OrdersCollection_From_OrderDetails = value; }    
    }
</code>

====Indexes====
    
Indexes are not only a powerful feature to help improve your query execution become more performant, it's also used to create handy data access API methods based on those indexes.  The important features of an index is that it can contain 1-n columns and has has the ability to be set as a UNIQUE index.

For example, in the Person Table we created above, if we added an index to the Name column, and said that column was Unique.  Then in my data access API I would get a method called Person GetByName(string name). If i had not added that the index was unique, then the method would return a collection of type TList<Person>.

'''SQL:
'''<code lang="SQL" linenumbers="true">
 CREATE UNIQUE NONCLUSTERED INDEX [IX_Person] ON [dbo].[Person] 
    (
        [Name] ASC
    )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
</code>
'''OUTPUT:'''
<code lang="C#" linenumbers="true">
        /// <summary>
        ///    Gets rows from the datasource based on IX_Person index.
        /// </summary>
        /// <param name="name"></param>
        /// <returns>Returns an instance of the <see cref="Person"/></returns>
        public Person GetByName(System.String name)
        {
            int count = -1;
            //Calls Provider specific Implementation 

            return GetByName(null,name, 0, int.MaxValue, out count);
        }
</code>

===Custom 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). 

<code lang="SQL" linenumbers="true">
 -- Get the products that have less units in stock than the @UnitsInStock parameter.
 CREATE PROCEDURE dbo._Product_GetWithStockBelow
    @UnitsInStock smallint
 AS

   SELECT
        [ProductID],
        [ProductName],
        [SupplierID],
        [CategoryID],
        [QuantityPerUnit],
        [UnitPrice],
        [UnitsInStock],
        [UnitsOnOrder],
        [ReorderLevel],
        [Discontinued]
   FROM
   [dbo].[Product]
   WHERE
        [UnitsInStock] < @UnitsInStock
 GO
</code>              

'''Output:'''

<code lang="C#" linenumbers="true">
        public TList<Product> GetWithStockBelow(System.Int16 unitsInStock)
        {

            int count = -1;
            //Calls Provider specific Implementation 

            return GetWithStockBelow(
                null, unitsInStock, 0, int.MaxValue, out count);

        }
</code>

(((''' 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


===Enum 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:'''
<code lang="SQL" linenumbers="true">
CREATE TABLE [dbo].[BankAccountType](
    [BankAccountTypeId] [int] IDENTITY(1,1) NOT NULL,
    [BankAccountTypeName] [varchar](50) NOT NULL,
    [BankAccountTypeDescription] [varchar](250)NULL,
 CONSTRAINT [BankAccountType_PK] PRIMARY KEY CLUSTERED 
(
    [BankAccountTypeId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [BankAccountType_UC1] UNIQUE NONCLUSTERED 
(
    [BankAccountTypeName] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

-- Add Table Description
EXEC 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'


Some Enum Items:
INSERT INTO [dbo].[BankAccountType] VALUES ('Checking', 'A Valid Checking Account')
INSERT INTO [dbo].[BankAccountType] VALUES ('Savings', 'A Valid Savings Account')
</code>

'''Generated Enumeration:'''

<code lang="C#" linenumbers="true">
    /// <summary>
    /// All allowable Checking Account types for my ABC System
    /// </summary>
    /// <remark>Enum that contains the items in BankAccountType</remark>
    [Serializable]
    public enum BankAccountTypeList
    {

        /// <summary> 
        /// A Valid Checking Account
        /// </summary>
        [EnumTextValue("A Valid Checking Account")]
        Checking = 1, 

 

        /// <summary> 
        /// A Valid Savings Account
        /// </summary>
        [EnumTextValue("A Valid Savings Account")]
        Savings = 2
    }
</code>