LINQ: add association for database views

The LINQ to SQL Designer automatically create association for tables that have foreign keys defined. However for database views, it won’t because there is no foreign keys relationship in views. Manually adding association in the Designer will not create “private EntitySet” collection.

The key is to set Primary key in the Designer, and BOTH tables/views need to have a primary key, then adding association will create the appropriate child collection!

ListView paging with dynamic datasource

Binding a Listview with a design time datasource is very convenient,  and you can utilize the default paging function of DataPager. To use a dynamically assigned datasource with minimum code for paging, do this:

In Page.aspx:

<asp:DataPager ID=”DataPager1″ runat=”server” PageSize=”20″ onprerender=”DataPager1_PreRender”>

In Page.aspx.cs:

protected void Page_Load(object sender, EventArgs e)
{
if (this.IsPostBack == false)
{
ListView1.DataSource = DB.GetPremiumsByCompany(Security.SessionCompanyID);
ListView1.DataBind();
}
}

protected void DataPager1_PreRender(object sender, EventArgs e)
{
if (IsPostBack)
{
ListView1.DataSource = DB.GetPremiumsByCompany(Security.SessionCompanyID);
ListView1.DataBind();
}
}

That’s it!

No database-like triggers in LINQ, and things to look out

In my LINQ application, I was looking for a UPDATE trigger implementation (in LINQ of course, not in DB), so that whenever a record is taken offline, I can record the time and user who does that. This actually can NOT be done in database trigger since I’m not passing over the logged in user info to DB.

It turned out that it’s impossible to do it in DataContext Level as I originally hoped.

First of all, the OnxxxChanged and OnxxxChanging events are mainly for validation and logging purpose. Modification to the value won’t be saved to DB.

Another option is to override partial UpdateXXXX in DataContext class.  However you will have to implement the complete UPDATE yourself. This is not what I want. I wanted something exactly like database trigger – something you do AFTER the DB transaction, without changing the default update behavior. Besides, LINQ doesn’t provide the old/new value in that partial function call.

What I had to do is at the UI level – handling ItemUpdating of my ListView control:

if (((bool)e.OldValues["Active"]) == true && ((bool)e.NewValues["Active"]) == false)
{
e.NewValues["RemoveDate"] = DateTime.Now;
e.NewValues["RemovedBy"] = Security.GetCurrentUserName();
}
else if (((bool)e.OldValues["Active"]) == false && ((bool)e.NewValues["Active"]) == true)
{
e.NewValues["RemoveDate"] = null;
e.NewValues["RemovedBy"] = String.Empty;
}

This is disappointing.

Another thing to note is that if there is DB side INSERT/UPDATE trigger that changes table value, then the DB value might be different from the LINQ in-memory value, and will cause update error next time LINQ updates. Of course there won’t be any problem if there is a reload before the update (which will be 99% of the case).

LINQ: cache with (child) collection properties

By default, because of the “Lazy Loading” setting, LINQ won’t load the child properties collection data until they are first accessed. So for the caching purpose, we have to force it to load everything before releasing the DataContext.  This is what you should do:

public static List<ParentObject> GetPremiumsByCompany(int companyID)
{
using (PolicyDataSourceDataContext db = new PolicyDataSourceDataContext())
{
db.ObjectTrackingEnabled = false; //optional. only if the data is readonly
// have to set options otherwise the collection will not be populated
DataLoadOptions dlo = new DataLoadOptions();
dlo.LoadWith<ParentObject>(p => p.ChildObject);
db.LoadOptions = dlo;
List<ParentObject> ret = (from pre in db.ParentObjects
select pre).ToList();
return ret;
}
}

Crystal Report viewer Reditributable for Visual Studio 2008

I developed a Crystal Report viewer windows application however I can’t find the correct redistribution package for client installation (BTW the SAP’s website really really S**ks!).

Finally I found it here:

C:\Program Files\Microsoft SDKs\Windows\v6.0A\Bootstrapper\Packages\CrystalReports10_5\

And it worked like a charm.

Roundup of ASP.NET Dynamic Data

Dynamic Data is a very nice tool comes with .NET 3.5 SP1. It offers CRUD support out of box (scaffolding) and is also very easy to customize!

Add to an existing web applicataion: for many web applications (especially those existing LOB apps), using Dynamic Data on backend admin pages is probably the most common use of it. MSDN has an article here to cover the steps to add Dynamic Data to your existing ASP.NET app.  It worked perfected for me. This article also tells you how to add Dynamic Data to a sub-folder.

Another trick on securing the \DynamicData folder is to add <authorization> policies in web.config file for\DynamicData folder.

http://www.asp.net has a good video series about Dynamic Data. Below are my roundups of the main knowledge points, and the things I learned after playing with it:

• URL routing: “List|Details|Edit|Insert” and ListDetails.aspx can co-exist. No need to comment one out.  Enabling both of them allows the app to support both ways ( separate page editing or in-place editing) – all determined by the way you request the page.  Actually I think this is better.
• CustomPages: just create a folder under it named after the plural format of the table name (Products).
• ScaffoldAllTables = false: for security reasons this should be turned off.  However for any table you want to scaffold you have to do one of these 2 things
  • extend the table class and decorate it with [ScaffoldTable(true)]
  • create custom pages in \CustomPages folder
• Validation:
  • Declarative: [Required] [Range(0, 2000)]
  • Imperative: extend those partial OnXXX event handlers, and throw Exceptions with error message.
• Hide a column from scaffolding: [ScaffoldColumn(false)]
• Change display: [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}")]
• Customized edit control: [UIHint("ReadOnly")], then in \FieldTemplates folder, add a ReadOnly_Edit.ascx

Update( FIELD_NAME) in SQL trigger

In SQL trigger programming, sometimes people put

if ( update( FIELD_NAME ) )
BEGIN
END

to determine if a field is being changed or not. However the SQL server doesn’t seems to really check the previous and new value. As long as the field is being updated in the UPDATE statement, this update() function will return true.

So if you want to record the real value change, you have to compare the value from deleted and inserted table yourself.

Sortable property in SalesLogix DataGrid control and few other tricks

• When checked, the sort happens in client but very slow (2+ seconds on 1500 records)
• When unchecked, sorting still supported but it’s on server side (SQL query). So the name of the property should really be ‘Client Sorting’
• If the ‘Client Sorting’ is unchecked, the summary footer will not be automatically updated whenever Refresh() is called.
• MultiSelect: besides the obvious function of it, it will also remove the auto-select first row behavior.

MachineKey in web.config

There are few scenarios that you want to add MachineKey into your web application’s web.config:

1. You have a web farm and in each server the machine.config set to auto generate machinekey.
2. You want to use “Encyrpted” password format in ASP.NET membership provider.
3. You get intermittent error of  “Validation of viewstate MAC failed…..”

To fix problem #3,  you can also set one of all these in the <page settings:

<page enableEventValidation=”false” viewStateEncryptionMode=”Never” enableViewStateMac=”false”

However you have to be aware of the risk of this change, as it opens door for ViewState value tampering. (Joteke has an intersting finding on large pages having gridview control. Basically in this case, you have to set above values for avoid the error, which is casued by the position of a hidden field containing some encrypted  information.)

Here are 2 online tools to generate the random machine key for you:  this and this. A sample MachineKey node:

<machineKey
validationKey=”56AB7132992003EE87F74AE4D9675D65EED8018D3528C0B8874905B51940DEAF6B85F1D922D19AB8F69781B2326A2F978A064708822FD8C54ED74CADF8592E17″
decryptionKey=”A69D80B92A16DFE1698DFE86D4CED630FA56D7C1661C8D05744449889B88E8DC”
validation=”SHA1″ decryption=”AES”
/>

The <machineKey> should be put inside <system.web> section.

Please refer to MSDN for documentation:  syntax and overview.

Add a link to DataGrid control

To add a functional link (i.e. Edit/Delete etc),:

1. Add any columns to the grid.
2. Change the type to Hyperlink.
3. May toggle ‘Read-Only’ (since you don’t really want to change the value), but do NOT check ‘Disable Editor’  (otherwise the link event won’t be fired).
4. Put the name of the link in Format String.  Make sure Format is not ftNone otherwise the FormatString will be ignored)
5. Check “Single Click to Launch”
6. Uncheck “Row Select” property for the grid, otherwise no OnEdit or OnHyperlinkStart event will fire. (Note: “Row Select” will automatically disable in-place editing. So if you don’t want in-place editing you need to toggle “ReadOnly”. With “ReadOnly”, the editor will still be invoked but values are not changable. )
7. Handle OnHyperlinkStart Event

What happens is that when user clicks on that blue link, the text in that cell will turn to the original value of the bound field. As soon as the OnHyperlinkStart event returns, it will switch back to the format string you specify in Columns properties.