Table Wrapper Classes

We use Subsonic to generate DAL classes for our main ASP.NET project, and we are transitioning to LINQ where possible.  One thing we haven't figured out yet is how to return a collection of LINQ "rows" from a web method, and Subsonic is apparently having problems with complex data too, so that means we need to create our own classes to return complex data or collections of data from a web method.  I have written collection classes before as well as DAL classes but it is SUCH a pain to create all the public variables, public properties and all their get/set methods.  I found this on SqlServerCentral today and will try it out next time I have to create a wrapper class for a table:

CREATE PROCEDURE usp_TableToClass
/*
Created by Cade Bryant.
Generates C# class code for a table
and fields/properties for each column.

Run as "Results to Text" or "Results to File" (not Grid)

Example: EXEC usp_TableToClass 'MyTable'
*/

@table_name SYSNAME

AS

SET NOCOUNT ON

DECLARE @temp TABLE
(
sort INT,
code TEXT
)

INSERT INTO @temp
SELECT 1, 'public class ' + @table_name + CHAR(13) + CHAR(10) + '{'

INSERT INTO @temp
SELECT 2, CHAR(13) + CHAR(10) + '#region Constructors' + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 3, CHAR(9) + 'public ' + @table_name + '()'
+ CHAR(13) + CHAR(10) + CHAR(9) + '{'
+ CHAR(13) + CHAR(10) + CHAR(9) + '}'

INSERT INTO @temp
SELECT 4, '#endregion' + CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT 5, '#region Private Fields' + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 6, CHAR(9) + 'private ' +
CASE
WHEN DATA_TYPE LIKE '%CHAR%' THEN 'string '
WHEN DATA_TYPE LIKE '%INT%' THEN 'int '
WHEN DATA_TYPE LIKE '%DATETIME%' THEN 'DateTime '
WHEN DATA_TYPE LIKE '%BINARY%' THEN 'byte[] '
WHEN DATA_TYPE = 'BIT' THEN 'bool '
WHEN DATA_TYPE LIKE '%TEXT%' THEN 'string '
ELSE 'object '
END + '_' + COLUMN_NAME + ';' + CHAR(9)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
ORDER BY ORDINAL_POSITION

INSERT INTO @temp
SELECT 7, '#endregion' +
CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT 8, '#region Public Properties' + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 9, CHAR(9) + 'public ' +
CASE
WHEN DATA_TYPE LIKE '%CHAR%' THEN 'string '
WHEN DATA_TYPE LIKE '%INT%' THEN 'int '
WHEN DATA_TYPE LIKE '%DATETIME%' THEN 'DateTime '
WHEN DATA_TYPE LIKE '%BINARY%' THEN 'byte[] '
WHEN DATA_TYPE = 'BIT' THEN 'bool '
WHEN DATA_TYPE LIKE '%TEXT%' THEN 'string '
ELSE 'object '
END + COLUMN_NAME +
CHAR(13) + CHAR(10) + CHAR(9) + '{' +
CHAR(13) + CHAR(10) + CHAR(9) + CHAR(9) +
'get { return _' + COLUMN_NAME + '; }' +
CHAR(13) + CHAR(10) + CHAR(9) + CHAR(9) +
'set { _' + COLUMN_NAME + ' = value; }' +
CHAR(13) + CHAR(10) + CHAR(9) + '}'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
ORDER BY ORDINAL_POSITION

INSERT INTO @temp
SELECT 10, '#endregion' +
CHAR(13) + CHAR(10) + '}'

SELECT code FROM @temp
ORDER BY sort

Using Telerik RadGrid - Codebehind - Subsonic

The following code is required in to populate the grid using subsonic:

        using Telerik.Web.UI;
...
        protected void rgCatalogs_NeedDataSource(object source, GridNeedDataSourceEventArgs e)
        {
            Query qry = SalesCatalog.CreateQuery();
            rgCatalogs.DataSource = qry.ExecuteDataSet();
        }

Using Telerik's RadGrid - aspx code

The following html code is needed to create a table with 4 visible columns:

    <asp:ScriptManager ID="ScriptManager1" runat="server">
    </asp:ScriptManager>
    <telerik:RadGrid ID="rgCatalogs" runat="server" GridLines="None"  
      AutoGenerateDeleteColumn="false" AutoGenerateEditColumn="false" AutoGenerateColumns="false"
      OnNeedDataSource="rgCatalogs_NeedDataSource" >
        <MasterTableView DataKeyNames="rid">
        <Columns>
            <telerik:GridBoundColumn HeaderText="rid" UniqueName="rid" DataField="rid" Visible = "false" ReadOnly ="true"></telerik:GridBoundColumn>
            <telerik:GridBoundColumn HeaderText="Description" UniqueName="Description" DataField="Description" ReadOnly ="false"></telerik:GridBoundColumn>
            <telerik:GridBoundColumn HeaderText="StartDate" UniqueName="StartDate" DataField="StartDate" ReadOnly ="false"></telerik:GridBoundColumn>
            <telerik:GridBoundColumn HeaderText="EndDate" UniqueName="EndDate" DataField="EndDate" ReadOnly ="false"></telerik:GridBoundColumn>
            <telerik:GridHyperLinkColumn HeaderText="ATS"  Text="ATS" UniqueName="ATS" DataNavigateUrlFields="rid" DataNavigateUrlFormatString="~/ATS/CatalogATS.aspx?catid={0}" /> 
        </Columns>
        <RowIndicatorColumn Visible="False">
        <HeaderStyle Width="20px"></HeaderStyle>
        </RowIndicatorColumn>

        <ExpandCollapseColumn Visible="False" Resizable="False">
        <HeaderStyle Width="20px"></HeaderStyle>
        </ExpandCollapseColumn>

        <EditFormSettings>
        <PopUpSettings ScrollBars="None"></PopUpSettings>
        </EditFormSettings>
        </MasterTableView>
    </telerik:RadGrid>

Setting up SQL server 2008 (to host Dynamics NAV)

I installed SQL 2008 Developer edition because the SQL 2005 Express edition I had been using isn't able to be accessed from web applications running outside the VS IIS instance and I needed to test an IIS hosted web service application.

The install took nearly 2 hours!  Becuase I was installing SSIS, SSRS, and the other tools.  It required an upgrade to .Net 3.5 SP1, VS 2008 SP1, in addition to 2.5 gb of HD space.

After the install, SQL 2005 Express could no longer access the databases so I had a momentary freak-out until I could mount them into SQL 2008, at which point I uninstalled SQL 2005 Express.

Upon attempting to connect from Dynamics NAV, I was reminded of the SQL setup you have to do for Dynamics NAV.  I always seem to forget that, and I am not the only one as I have been onsite to implement behind another analyst a few times and found that the setup hadn't been completed.  Here are the SQL modifications required for Dynamics NAV:

You have to set a trace flag by adding "-T 4616" to the startup parameter string:

http://tricksnavision.blogspot.com/2007/10/error-traceflag-4616-at-start-nav-in.html 

You have to add two extended stored procedures (xp_ndo_enumusersids and xp_ndo_enumusergrous) to the master database and grant execute permissions to the public role  as explained here:

http://blogs.msdn.com/nav_developer/archive/2008/11/05/basic-sql-creating-extended-stored-procedure-xp-ndo-dll.aspx

As a side note, all clients from 4.0 SP3 and later are compatible with SQL 2008, so there is no reason to use SQL 2005 with new installs.

Inserting into SQL table with Identity field

Sometimes you want to insert records into a table that has an Identity field but you need to insert a value into that field (normally the system assigns identity values).

The following code allows you to do that:

SET IDENTITY_INSERT BasicAttribute ON

insert into BasicAttribute (rid, typeid, code, description) values (0,0,'DEFAULT','Default')

SET IDENTITY_INSERT BasicAttribute OFF

How to add a foreign key constraint in t-sql

The WITH NOCHECK clause is not recommended since future table updates could fail as the contstraint is re-validated, but I needed it to get it to work on some tables and will have to deal with the missing data later.

ALTER TABLE dbo.SKU_Price WITH NOCHECK ADD CONSTRAINT
    FK_SKU_Price_PriceGroupID FOREIGN KEY
     (
        PriceGroupID
     ) 
     REFERENCES dbo.SKU
     (
         rid
     ) 
    ON UPDATE  NO ACTION 
    ON DELETE  NO ACTION 

Thinking in Layers

When creating a web application, it is very important to think in Layers.  Your web application may only have 1 layer because it is so simple, but if you plan to have more than one developer, or permit web-service access to the applicaiton, or just plan on allowing the application to grow, you should have 3-5 layers:

1. Client - Scripts that enhance the usability of the site and run on the client interface
   
2. Presentation - Objects that display the data in HTML or in a Winform
   
3. Communication - Objects that expose or consume services so the application can be spread across multiple physical or virtual servers.
   
4. Business Logic - Objects that contain the working data and enforce the business rules.
   
5. Data Access - Objects that interact with the database
   
6. Data - The database server
   

The manditory layers for a web application are Presentation, and Data but if you neglect to include layers in between them you will probably regret it.  

If you have a single object that interacts with the web page, enforces the business rules and interacts with the database:

• What are you going to do when you need to expose that information via a web service?
  
• What will you do if you need to optimize for performance and cache the information in a session variable?
  
• Or what if you need to add another web server to handle the volume of user demand?