Uom-Nettiers

Quoting from http://www.31a2ba2a-b718-11dc-8314-0800200c9a66.com/2008_03_01_archive.html “If you create your own procs, prefix those procs to something that you won’t confuse with any other library’s procs, such as ‘cust’ for custom. Of cource, IncludeCustoms is set to true and CustomProcedureStartsWith should be set to ‘cust_{0}_’ so that a table name of Profile should have a custom proc of cust_Profile_SelectAll.” if you set CustomProcedureStartsWith to just ‘cust_’ as we have done in the past, a method is created for each stored procedure for each table, whereas using ‘cust_{0}_’ only creates one method for each stored procedure – that method is linked to the table its name is based on. Far more sensible.

Entity tracking is clever and manages to keep a cache of recently used entities (and lists of entities) and, if it doesn’t see a change to an entity, it will return the cached version without going to the database – that’s what caching is after all. The problem is that ALL changes to the database MUST be made by the same project using .netTiers methods. If you’re developing an application and change data directly in the database then .netTiers won’t spot that. Pretty obvious really but do remember whether you have enableEntityTracking="true" or enableEntityTracking="false" in the SqlNetTiersProvider bit of your web.config

.netTiers can be configured to only use stored procedures, which is a great way to reduce the possibility of the database being attacked as the user account can be given access to only execute stored procedures and not have any direct access to the tables. To do this, just set useStoredProcedure=true in the netTiers <providers> section of the web.config If you do this, some pages (methods) will still need SELECT access to tables. By default, netTiers creates entitygridviews that use paging and so use the get_paged method, which calls the myTable_GetPaged stored procedure. This stored procedure builds a couple of SQL strings (SELECT statements) that are executed, which means that the user account needs to have access to run SELECT statements. To give SELECT access to all tables in the database, you can add the user account to the db_datareader role.

SQL Server allows each field in the database to have a description, so this description seems like the most useful place to put friendly names for fields that will appear on a netTiers generated admin page. When netTiers first runs, a config file is generated as specified in 01 MappingFile This XML file lists all of the tables and for each table it lists all of the fields e.g. <Column Id="title" CSType="System.String" PropertyName="Title" FieldName="_title" FriendlyName="Title" IncludeInOutput="true" /> netTiers will automatically convert the database field name of “title” to a FriendlyName of “Title” It’s quite clever too and will convert a field name of "title_or_salutation" to a FriendlyName of "Title Or Salutation" ...

The normal <asp:SqlDataSource/> can use stored procedures as the select method and even for insert/update/delete commands. netTiers will create a strongly typed data source for each table. The inserts, updates and deletes are handled automatically but there are options for which records to select. Get_Paged is the default but you can also get by any foreign key. Sometimes this isn’t enough and you want a custom filter for your entities. To do this, write a stored procedure that returns all of the columns in the base table and no other columns. The SP can have joins to other tables, so you can use those for filtering. The SP can have whatever parameters you like. Because the SP returns all of the columns of the base table, netTiers recognises that it can be used to get a list of entities and so allows it as a SelectMethod e.g. ...

After creating a record, you might want to automatically create a related child record, which uses the ID of the record you have just created. In your datasource <data:XyzDataSource> you can put in OnInserted="Xyz_Inserted": <data:XyzDataSource ID="XyzDataSource" runat="server" SelectMethod="GetById" OnInserted="Xyz_Inserted"> protected void Xyz\_Inserted(Object sender, ObjectDataSourceStatusEventArgs e) { if (e.Exception == null) { if (FormView1.CurrentMode == FormViewMode.Insert) { Xyz myXyz = (Xyz)e.ReturnValue; int ID = myXyz .Id; // now create the child entity, set the parent id and insert it } } } The problem with this is that netTiers by default uses transactions and Xyz_Inserted is called in the middle of the transaction. So, whilst an ID has been returned for the new record, it hasn’t been committed to the database, so you can’t create the child record yet. The way around this is for the datasource to not use transactions. ...

Problem: After using a netTiers powered website for a while, the database connections would start to time out and you’d get a Stack Trace like: at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error) Solution: NetTiers was using an EntityTransactionModule which was incorrectly configured. It was opening database connections and not closing them (I think) and so SQL Server eventually stops allowing connections. IIS 7.5 needs the following code in the web.config to correctly configure the module. ...

If you need your netTiers gridview (EntityGridView) to have a column that just displays a read only value from a lookup table then the data source will need to deepload but <asp:BoundField> won’t be able to access the lookup value. NetTiers’ own controls are only dropdowns, hyperlinks and radiobuttons, so to display the lookup value (rather than the ID), you need to add a template field <asp:TemplateField> <ItemTemplate> <%# Eval("ApplicIdSource.Applic")%> </ItemTemplate> </asp:TemplateField> as described here: ...

Don’t name a field ID, id or Id If you ask NetTiers to generate an admin website, then it will show warnings when compiled but these will result in errors when you try to run the site. Even if you don’t want an admin website to start with, you may want one later, so just don’t name any fields ID

You may want your custom stored procedures to contain conditional statements such as IF. These can cause problems when NetTiers inspects the procedure to determine its outputs – it may normally return a table but an IF statement traps for an exception and conditionally returns before the select statement. You can force NetTiers to follow one route through the code e.g. the statement below will never return for NetTiers IF user_name() <> 'NetTiersUserAccount' AND ThingToTest = ValueThatMakesItReturn RETURN Just make sure that the user account in the connection string used by NetTiers is not used by anything other than NetTiers i.e. not in you web.config