Forum

Absolutely and thanks for helping out.  I have been crawling through the web and there isn't a lot of info out there to work with related to DAL2.  This makes it hard to progress when things don't go exactly as expected.  Hopefully whatever is discovered here will help others. 

While trying to track down this problem I have minimized my code in an effort to isolate the problem to the smallest space possible.  I have removed a ton of the fields originally in the Product.cs and have removed all functions from the ProductRepository.cs except for the single function you see below.  Some of this code may have been included earlier, but I thought it would be helpful to get it all in a single post.

Currently here is what I am working with:

Product.cs:

using System.Web.Caching;
using DotNetNuke.ComponentModel.DataAnnotations;
namespace MyModule.Components
{
    [TableName("Product")]
    [PrimaryKey("productCode")]
    [Cacheable("MYMODULE_Product_", CacheItemPriority.Default, 20)]
    [Scope("productCode")]    //tried different values here and nothing changed but excluding this caused more problems
    public class Product
    {
        public string productCode;
    }
}

ProductRepository.cs:

using DotNetNuke.Data;
namespace MyModule.Components
{
    public class ProductRepository
    {
        private const string EXTERNAL_DB_CONNECTION_STRING = "MY_DB_CONNECTIONSTRING_NAME";

        public Product GetProduct(string productCode)
        {
            Product t;
            using (IDataContext ctx = DataContext.Instance(EXTERNAL_DB_CONNECTION_STRING))
            {
                var rep = ctx.GetRepository<Product>();
                t = rep.GetById(productCode);
            }
            return t;
        }
    }
} 

Code in View.ascx.cs:

ProductRepository productRepo = new ProductRepository();
Product product = (Product)productRepo.GetProduct("MYCODE");

Resulting SQL as seen from SQL Server Profiler:

exec sp_executesql N'SELECT NULL FROM [Product] WHERE [productCode]=@0',N'@0 nvarchar(4000)',@0=N'MYCODE'

Here is a script I generated using MS SQL Server Management Studio and the Product table.  It shows the field names.  There are additional constraints for Foreign Keys, but I haven't included them in here.  If you need that info I can include the full script produced for the table.  Here is just the table field definitions:

CREATE TABLE [dbo].[Product](
    [productCode] [varchar](50) NOT NULL,
    [productTypeId] [int] NOT NULL,
    [title] [varchar](250) NOT NULL,
    [subtitle] [varchar](250) NULL,
    [displayTitle] [varchar](500) NOT NULL,
    [displayAuthor] [varchar](500) NULL,
    [year] [int] NULL,
    [publicationDate] [datetime] NULL,
    [quickSearchKeywords] [varchar](2000) NULL,
    [advancedSearchKeywords] [varchar](2000) NULL,
    [searchableISBN] [varchar](250) NULL,
    [description] [ntext] NULL,
    [grabber] [ntext] NULL,
    [narrative] [ntext] NULL,
    [keynote] [ntext] NULL,
    [features] [ntext] NULL,
    [highlights] [ntext] NULL,
    [volumes] [int] NULL,
    [pages] [int] NULL,
    [size] [varchar](50) NULL,
    [imprintId] [int] NOT NULL,
    [topic] [varchar](50) NULL,
    [subtopic] [varchar](50) NULL,
    [isbn] [varchar](17) NULL,
    [isbn10] [varchar](17) NULL,
    [paperbackISBN] [varchar](17) NULL,
    [paperbackISBN10] [varchar](17) NULL,
    [hardcoverISBN] [varchar](17) NULL,
    [hardcoverISBN10] [varchar](17) NULL,
    [ebookISBN] [varchar](17) NULL,
    [ebookISBN10] [varchar](17) NULL,
    [price] [float] NULL,
    [pricePound] [float] NULL,
    [priceEuro] [float] NULL,
    [salePrice] [float] NULL,
    [salePricePound] [float] NULL,
    [salePriceEuro] [float] NULL,
    [paperbackPrice] [float] NULL,
    [paperbackPricePound] [float] NULL,
    [paperbackPriceEuro] [float] NULL,
    [hardcoverPrice] [float] NULL,
    [hardcoverPricePound] [float] NULL,
    [hardcoverPriceEuro] [float] NULL,
    [paperbackSalePrice] [float] NULL,
    [paperbackSalePricePound] [float] NULL,
    [paperbackSalePriceEuro] [float] NULL,
    [hardcoverSalePrice] [float] NULL,
    [hardcoverSalePricePound] [float] NULL,
    [hardcoverSalePriceEuro] [float] NULL,
    [saleStartDate] [datetime] NULL,
    [saleEndDate] [datetime] NULL,
    [hardcoverActive] [bit] NULL,
    [paperbackActive] [bit] NULL,
    [ebookActive] [bit] NULL,
    [hasReview] [bit] NULL,
    [hasAward] [bit] NULL,
    [hasDownload] [bit] NULL,
    [gradeLevel] [varchar](50) NULL,
    [authorBio] [ntext] NULL,
    [tableOfContents] [ntext] NULL,
    [relatedProducts] [varchar](2000) NULL,
    [noShipping] [bit] NULL,
    [noTax] [bit] NULL,
    [seriesId] [int] NULL,
    [active] [bit] NULL,
    [purchasable] [bit] NULL,
    [dateCreated] [datetime] NULL,
 CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
(
    [productCode] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

If there is any other information that would be helpful, let me know.