Data scrambling in SQL

When developing an application, it can often be quite useful to utilise the production data for testing. However, the danger of this is that production data is then being stored on the relatively less-secure development devices. The answer to this solution is to scramble the data in such a way that the original information is no longer recoverable, but it still takes a similar form.

For simplicity, random 0-9a-zA-Z strings are the core of this, using the function RAND().

Note: RAND() is not cryptographically-secure, but that's ok in this scenario as we're generating these values independently of the original data. If you need cryptographically-secure random values, use CRYPT_GEN_RANDOM()).

Script Architecture

Yes - unfortunately due to SQL constrains there needs to be mild consideration of architecture. Namely, we want to use scalar functions as the generators of random values as these will fit in easily to an UPDATE script. However, SQL does not permit the use of RAND() inside a scalar function, as running RAND() has side-effects on the DB (due to the deterministic nature I presume). We can bypass this by creating a minimal view which returns a RAND() value:

CREATE VIEW [dbo].[vw_RandomNumber]

AS

SELECT RAND() AS RandomNumber

GO

This forms the basis of our random data functions.

Functions

RandomString

CREATE function [dbo].[udf_RandomString](@length INTEGER)   

    RETURNS varchar(MAX) AS BEGIN

DECLARE @charPool VARCHAR(MAX) =

                 'abcdefghijkmnopqrstuvwxyzABCDEFGHIJKLMNPQRSTUVWXYZ23456789'

DECLARE @poolLength INTEGER = Len(@charPool)

DECLARE @loopCount INTEGER = 0

DECLARE @randomString VARCHAR(MAX) = ''

WHILE (@loopCount < @Length) BEGIN

SELECT @randomString = @randomString +
            SUBSTRING(@charPool, CONVERT(int, 
            (SELECT TOP 1 RandomNumber FROM vw_RandomNumber) * @poolLength), 1)

SELECT @loopCount = @loopCount + 1

END

    RETURN (@randomString)  

END 

This script does not generate the lowercase letter "l" and number "1", and capital letter "O" and number "0" due to their similar appearance. Maybe slightly overkill, but hey!

RandomEmail

CREATE function [dbo].[udf_RandomEmail](@length INTEGER)   

    RETURNS varchar(MAX) AS BEGIN

    RETURN LOWER((dbo.udf_RandomString(@length) + '@email.com')  )

END  

Full script example

UPDATE WEB_Booking

SET JobName = dbo.udf_RandomString(12),

DeliveryAddress = '123 ' + dbo.udf_RandomString(6) + ' Rd',

DeliverySuburb = dbo.udf_RandomString(8),

DeliveryState = 'NSW',

DeliveryPostcode = '2000',

PickupAddress = '123 ' + dbo.udf_RandomString(6) + ' Rd',

PickupSuburb = dbo.udf_RandomString(8),

PickupState = 'NSW',

PickupPostcode = '2000',

SiteContactName = dbo.udf_RandomString(4) + ' ' + dbo.udf_RandomString(6),

SiteContactNumber =  '04 1234 5678'

Numbers have not yet been scrambled, this is a potential future improvement though it was considered low value for this project, as there was no need for addresses or contact numbers to be unique and diverse.

Troubleshooting: .NET MVC RedirectToAction chopping out domain

Problem:

After using RedirectToAction({action}, {controller}, {parameters}), the new URL has the domain chopped out. E.g.:

expected: http://localhost:5050/gift-certificates/success

actual: http://gift-certificates/success

This subsequently causes a failed page load, as it's become a redirect to an external (and likely non-existent) page.

Solution:

Ensure that the Route is fully and correctly mapped in the global.asax setup

Integrate new Web API with old WebForms Cookies

To make Web API 2 with Identity 2.0 and OWIN accept default cookies from WebForms you need to configure CookieAuthentication in the following way:

public partial class Startup
{
 public void ConfigureAuth(IAppBuilder app)
 {
  app.UseCookieAuthentication(new CookieAuthenticationOptions()
  {
   CookieName = FormsAuthentication.FormsCookieName,
   CookieDomain = FormsAuthentication.CookieDomain,
   CookiePath = FormsAuthentication.FormsCookiePath,
   CookieSecure = CookieSecureOption.SameAsRequest,
   AuthenticationMode = AuthenticationMode.Active,
   ExpireTimeSpan = FormsAuthentication.Timeout,
   SlidingExpiration = true,
   AuthenticationType = CustomAuthenticationTypes.FormsAuthenticationType,
   TicketDataFormat = new SecureDataFormat(
    new FormsAuthTicketSerializer(),
    new FormsAuthTicketDataProtector(),
    new HexEncoder())
  });
 }
}

First you have to add a reference to "System.Web.Security" to be able to reference the FormsAuthentication object with has static fields that expose the default cookie name, domain, path and timeout. Note if you have set a non default value for these options in your WebForms Project you need to set them to your new settings.

The AuthenticationType is just a string to identify between types of authentication in your OWIN pipeline

The SecureDataFormat is an object allocates how the incoming data (a FormsAuthenticationTicket from WebForms  in this case) should be deserialized, unprotected and decoded or the opposite for an outgoing Identity

The FormsAuthTicketSerializer, FormsAuthTicketDataProtector, and HexEncoder are all custom classes which are described below

HexEncoder
public class HexEncoder : ITextEncoder
{
 public string Encode(byte[] data)
 {
  return ToHexadecimal(data);
 }

 public byte[] Decode(string text)
 {
  return ToBytesFromHexadecimal(text);
 }

 public static string ToHexadecimal(byte[] ba)
 {
  StringBuilder hex = new StringBuilder(ba.Length * 2);
  foreach (byte b in ba)
   hex.AppendFormat("{0:x2}", b);
  return hex.ToString();
 }

 public static byte[] ToBytesFromHexadecimal(string hex)
 {
  int NumberChars = hex.Length;
  byte[] bytes = new byte[NumberChars / 2];
  for (int i = 0; i < NumberChars; i += 2)
   bytes[i / 2] = Convert.ToByte(hex.Substring(i, 2), 16);
  return bytes;
 }
} 

The HexEncoder is a class that is used by CookieAuthentication and the FormsAuthTicketDataProtector to simply convert a hexadecimal string to bytes and vice versa


FormsAuthTicketDataProtector
public class FormsAuthTicketDataProtector : IDataProtector
{
 public byte[] Protect(byte[] userData)
 {
  FormsAuthenticationTicket ticket;
  using (var memoryStream = new MemoryStream(userData))
  {
   var binaryFormatter = new BinaryFormatter();
   ticket = binaryFormatter.Deserialize(memoryStream) as FormsAuthenticationTicket;
  }

  if (ticket == null)
  {
   return null;
  }

  try
  {
   return HexEncoder.ToBytesFromHexadecimal(FormsAuthentication.Encrypt(ticket));
  }
  catch
  {
   return null;
  }
 }

 public byte[] Unprotect(byte[] protectedData)
 {
  FormsAuthenticationTicket ticket;
  try
  {
   ticket = FormsAuthentication.Decrypt(HexEncoder.ToHexadecimal(protectedData));
  }
  catch
  {
   return null;
  }

  if (ticket == null)
  {
   return null;
  }

  using (var memoryStream = new MemoryStream())
  {
   var binaryFormatter = new BinaryFormatter();
   binaryFormatter.Serialize(memoryStream, ticket);

   return memoryStream.ToArray();
  }
 }
}

The FormsAuthTicketDataProtector is a class that encrypts a FormsAuthenticationTicket to a byte stream and decrypts a FormsAuthenticationTicket from a byte stream


FormsAuthTicketSerializer
public class FormsAuthTicketSerializer : IDataSerializer
{
 public AuthenticationTicket Deserialize(byte[] data)
 {
  using (var dataStream = new MemoryStream(data))
  {
   var binaryFormatter = new BinaryFormatter();
   var ticket = binaryFormatter.Deserialize(dataStream) as FormsAuthenticationTicket;
   if (ticket == null)
   {
    return null;
   }

   var identity = AccountService.CreateIdentity(ticket.Name, CustomAuthenticationTypes.FormsAuthenticationType);
   var authTicket = new AuthenticationTicket(identity, new AuthenticationProperties());

   authTicket.Properties.IssuedUtc = new DateTimeOffset(ticket.IssueDate);
   authTicket.Properties.ExpiresUtc = new DateTimeOffset(ticket.Expiration);
   authTicket.Properties.IsPersistent = ticket.IsPersistent;
   return authTicket;
  }
 }

 public byte[] Serialize(AuthenticationTicket model)
 {
  var userTicket = new FormsAuthenticationTicket(
    2,
    model.Identity.Claims.Single(c => c.Type == ClaimTypes.Name).Value,
    new DateTime(model.Properties.IssuedUtc.Value.UtcDateTime.Ticks, DateTimeKind.Utc),
    new DateTime(model.Properties.ExpiresUtc.Value.UtcDateTime.Ticks, DateTimeKind.Utc),
    model.Properties.IsPersistent,
    "",
    FormsAuthentication.FormsCookiePath);

  using (var dataStream = new MemoryStream())
  {
   var binaryFormatter = new BinaryFormatter();
   binaryFormatter.Serialize(dataStream, userTicket);

   return dataStream.ToArray();
  }
 }
}

Finally the FormsAuthTicketSerializer serialises the FormsAuthenticationTicket from bytes to an OWIN based AuthenticationTicket and vice versa. This new AuthenticationTicket is what OWIN will use to create the users identity further down the process.

This technique is useful if you are wanting to migrate a large WebForms site to the newer MVC and Web API standards incrementally because it allows you to move all the business logic to a WebAPI while still maintaining the WebForms front end interface

Having a standalone RadImageManager

The Telerik RadImageManager is a handy control for uploading / managing and selecting images, however it is embedded into the Rich Text editor control (RadEditor). To use it as a standalone tool you can use the following method:

In the front end view do the following:

1. Add into Web.Config the telerik dialog handler declaration
2. Declare a RadDialogOpener
3. Declare a hidden field to store the domain path to be used in the URL
4. Declare a textbox to store the URL
5. Create an image icon to launch the Image Manager.

Add the following into the Web.config file:

<system.web>
    <httpHandlers>
     ....
      <add path="Telerik.Web.UI.DialogHandler.aspx" verb="*" type="Telerik.Web.UI.DialogHandler, Telerik.Web.UI"/>
    </httpHandlers>
</system.web>


Additional code in the aspx file:

<telerik:RadDialogOpener runat="server" ID="dop1" />
<asp:HiddenField ID="hfImageServer" runat="server" ClientIDMode="Static" />

<asp:TextBox ID="tbxImageURL" runat="server" Width="500" Text='<%#Bind("ImageURL")%>' MaxLength="256" ClientIDMode="Static" />
<img src="/images/icon/photo.png" onclick="$find('<%= dop1.ClientID %>').open('ImageManager', {CssClasses: []});return false;" width="16" height="16" alt="Image Manager for Image URL" title="Image Manager" />


In the code behind set up a function to initialise the Image Manager with appropriate property values. This can be reused for multiple Dialog Openers

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
  SetupImageManager(dop1)
End Sub

Private Sub SetupImageManager(ByVal dop As RadDialogOpener)
  hfImageServer.Value = "http://www.mydomain.com"

  Dim imageManagerParameters As New FileManagerDialogParameters()
  imageManagerParameters.ViewPaths = New String() {"~/UploadFolderName"}
  imageManagerParameters.UploadPaths = New String() {"~/UploadFolderName"}
  imageManagerParameters.DeletePaths = New String() {"~/UploadFolderName"}
  'imageManagerParameters.MaxUploadFileSize = 102400
  'imageManagerParameters.SearchPatterns = new string[] { "*.jpg" };

  Dim imageManager As New DialogDefinition(GetType(ImageManagerDialog), imageManagerParameters)
  imageManager.ClientCallbackFunction = "ImageManagerFunction" & dop.ID
  imageManager.Width = Unit.Pixel(600)
  imageManager.Height = Unit.Pixel(500)
  imageManager.Parameters("ExternalDialogsPath") = "~/ExternalDialogs/"

  dop.DialogDefinitions.Add("ImageManager", imageManager)

  Dim imageEditorParameters As New FileManagerDialogParameters()
  imageEditorParameters.ViewPaths = New String() {"~/UploadFolderName"}
  imageEditorParameters.UploadPaths = New String() {"~/UploadFolderName"}
  imageEditorParameters.DeletePaths = New String() {"~/UploadFolderName"}
  'imageEditorParameters.MaxUploadFileSize = 102400

  Dim imageEditor As New DialogDefinition(GetType(ImageEditorDialog), imageEditorParameters)
  imageEditor.Width = Unit.Pixel(600)
  imageEditor.Height = Unit.Pixel(500)
  dop.DialogDefinitions.Add("ImageEditor", imageEditor)
End Sub

In the Javascript file have a callback function to populate the text field value

function ImageManagerFunctiondop1(sender, args) {
    if (!args) {
        alert('No file was selected!');
        return false;
    }
    var txt = $get('tbxImageURL');
    var path = args.value.getAttribute("src", 2);
    txt.value = $('#hfImageServer').val() + path;
}

How to manage Local Asp.Net Membership Users easily

During development you can manage local Asp.Net Membership Users easily through the .Net Users section in Internet Information Services Manager (IIS Manager).

Prerequisites

• IIS Manager is installed on your local machine. Search for IIS Manager in your programs or follow this to install (Install IIS Manager)
• Visual Studio Project that uses Membership and has a valid connection to your local database
• Valid database setup to store Membership Users

Steps

1. Create a new Website (Right Click Sites > Add a Website)
2. Set the application path to the folder containing your target project in the pop up
3. Navigate to the .Net Users section (see below)
4. From here you can add, edit and delete all the Membership Users in the database

 

iOS Development Signing identities in a tangle?

If you're using a developer account on only one Mac, you can get away with clicking "Fix issues" and letting XCode do hacky patch-ups to get things sorted.

Otherwise, you need to properly sort your certs out.

How to sort it out, the slash and burn way:

1. From now on, stop using the "Fix issue" buttons and inform everyone on the team likewise - a single "Fix issues" can break everything if it resets the certs.
2. Clear Keychain of old dev account keys on all Macs
1. Open Keychain Access
2. In top left sidebar, login
3. In bottom left sidebar, Keys
4. Find keys relating to dev account - these are based on the original certificate request naming, so it could be anything! There'll be a private key and an associated certificate, and possibly a public key. Likely a set of these for both Development and Distribution. Delete these.
3. Open https://developer.apple.com/account/ios/certificate/ and revoke all certificates
4. Generate certificate requests for both Development and Distribution on one Mac (this one will be the only one with public keys, from what I've found)
1. Open Keychain Access
2. Go to Keychain Access > Certificate Assistant > Request Certificate from a Certificate Authority
3. Fill in email address
4. Fill in Common Name with something like "{dev account email} - {distribution/development}"
5. Select Save to disk instead of "Emailed to the CA"
5. Create new certificates via https://developer.apple.com/account/ios/certificate/ for both Development & Distribution, following the fairly self-explanatory wizard. Download and double-click on the "master" Mac so that they're saved via Keychain Access.
6. Export the private key/certificate pairs
1. Open Keychain Access
2. In top left sidebar, select login
3. In bottom left sidebar, Keys
4. Find the keys, export with some one-time use password
7. Import the private key/certificate pairs onto the rest of the Macs
1. Get key files onto rest of Macs, double-click in Finder and save via Keychain Access
8. Create required provisioning profiles via https://developer.apple.com/account/ios
9. Set up dev accounts on all Macs
1. XCode > Preferences > Accounts
2. Add account, log in via credentials
3. Select the Agent user, view details
4. Download All - this not only does provisioning profiles, but seems to fix something up with the signing identities

Should I use WebGL?

WebGL is yet another browser technology that shrinks the gap between websites and native applications - it allows a website to have proper 3D animations with fairly impressive performance (due to being powered by the GPU). This is done via the native WebGL JavaScript API - no need for any plugins! As an example of what's possible, check out this car animation:

Who can use WebGL?

Since the first stable release in 2013, WebGL has been picking up great support recently, with Firefox, Safari, Microsoft Edge and IE11 all jumping on board. As of May 2016, roughly 91% of users in Australia and 84% globally can use this technology. Along with IE10 & below, the chief laggards are old Android stock browsers and Opera Mini (source - caniuse.com) - the latter being primarily an issue in Europe. This means that if you incorporate WebGL, expect vast majority of users (desktop & mobile) to enjoy your snazzy animation. However, you’re still going to need a fallback for years to come if you want to support 99%+ users.

What can I do with WebGL?

An amazing variety of things!

• Depict some real life object more comprehensively (the car example above)
• 3-axis data presentation (example)
• Games (example)
• Outrageous immersive experiences (example - this one’s from Disney, it’s very polished and probably cost a massive amount to develop!)

Challenges for the users

If the animations are fairly complex, a few difficulties can arise:

• Substantial loading times, possibly half a minute or even more
• Slower devices can struggle
• Heavy on battery life

Challenges for development

Is this familiar?

What about this?

If not… you could be in for a real challenge. WebGL is unsurprisingly heavy on 3D maths, the type that you wouldn't have covered unless you did advanced maths in school or university. And these concepts can be a lot harder to learn than even a fully-fledged framework like AngularJS.

And even if you are adept at the required maths, it’s still a very time-consuming endeavour. If you’re choosing between injecting an image scroller of a blender vs rendering a 3D blender, it’s not hard to tell which is going take far longer to develop.

Likewise, there are challenges for the designers - Photoshop isn't sufficient to mock up a 3D website.

Suggested workflow

If your designers have prowess in producing 3D designs, then at it's simplest, you can follow a Waterfall-style management for producing a single component:

3D designer —> developer

With this, the designer produces files of the 3D models (e.g. CAD files) that the developers can then absorb in WebGL.

If your designers don't have that rarer skill, then it's necessary to take a very iterative approach for producing even the most basic single components:

designer <—> developer

With this, the designers envision concepts, then developers interpret those concepts to produce an output, then the designers review the output and propose changes. This scenario clearly has inefficiencies as the designer is unsure of what the final output should be. As such, it's worth doing comprehensive research for examples to describe specifically what is desired and what isn't.

So... when should I use WebGL?

If…

• the topic & context of the web page is 3D
• you’re trying to display a 3D diagram, object or concept
• you’re creating an immersive experience to “wow” users, and they’d be happy to take time

then WebGL is potentially a great tool to incorporate.

And when shouldn't I use it?

If…

• it’s a gimmicky/tacky application of the tool (think flaming text & marquee banners)
• you need 99%+ user support
• the project has insufficient budget for the complexity of WebGL and also for providing a fallback
• your developers don’t have the required maths knowledge

then avoid the pain! Native apps have had great 3D frameworks for a long time, but most don’t use them - there are many good reasons for that.

Summing it up

WebGL is a great tool to add to your kit, providing immense value if used creatively and wisely. The technology is still young; the greatest need is better frameworks for making the workflow (real life —> 3D model —> WebGL page) faster and simpler, but that will come as more products are developed utilising WebGL, and contributions are made to the open source communities surrounding WebGL.

DataBinding issues with a NULL or archived value

When binding web form controls to a data source you can often come unstuck when NULL values get returned from the database and you get dreaded run time NullReferenceExceptions. Although the examples below use Telerik controls, the equivalent ASP.NET controls can also be addressed in a similar manner

Binding a NULLable boolean field to a checkbox

When binding a boolean value to a grid, the add form will fail if it is NULLable due to there being no '3rd' option on a checkbox to handle the NULL value. To get around this we must set a default value manually when calling the InitInsert command as follows

Protected Sub rgd_ItemCommand(sender As Object, e As Telerik.Web.UI.GridCommandEventArgs) Handles rgd.ItemCommand
    If (e.CommandName = RadGrid.InitInsertCommandName) Then
        e.Canceled = True
        Dim newValues As System.Collections.Specialized.ListDictionary = New System.Collections.Specialized.ListDictionary()
        newValues("BoolField") = False
        e.Item.OwnerTableView.InsertItem(newValues)
    End If
End Sub
 

---

 
Binding a NULLable lookup table to Dropdown 

If you have a lookup table bound to a DropDownList/RadComboBox in a FormView/FormTemplate, by default it cannot handle NULL values if you create a line item for that NULL value. However by specifying a particular unused numeric value (such as 0 in this context will rarely by used for a table ID)

<telerik:RadComboBox ID="rcbForeignKeyID" runat="server" DataSourceID="sqlForeignKey" DataTextField="Name"
 DataValueField="ID" AppendDataBoundItems="true" SelectedValue='<%#Bind("ForeignKeyID")%>'>
    <Items>
        <telerik:RadComboBoxItem Text="No value..." Value="0" />
    </Items>
</telerik:RadComboBox>

On the Form's LinqDataSource we can then handle the Inserting & Updating events to set that 0 value to nothing which equates to DB NULL

Protected Sub lds_Inserting(sender As Object, e As System.Web.UI.WebControls.LinqDataSourceInsertEventArgs) Handles ldsForm.Inserting
    Dim rec As TableType = e.NewObject
    If rec IsNot Nothing Then
        If rec.ForeignKeyID = 0 Then rec.ForeignKeyID = Nothing
    End If
End Sub

Protected Sub lds_Updating(sender As Object, e As System.Web.UI.WebControls.LinqDataSourceUpdateEventArgs) Handles ldsForm.Updating
    Dim rec As TableType = e.NewObject
    If rec IsNot Nothing Then
        If rec.ForeignKeyID = 0 Then rec.ForeignKeyID = Nothing
    End If
End Sub

---

Binding a value no longer in the DataSource to a GridDropDownColumn

When using a dropdown in the edit form of a RadGrid you often encounter the scenario where the grid row value no longer exists in the dropdown data source due to the record being archived or some such. In order to work around this, we can manually add the existing record to the top of the dropdown so the SelectedValue binding won't break.

The aspx code should look something like this for the column specification
        <telerik:GridTemplateColumn UniqueName="ID" HeaderText="Header" AllowFiltering="false" DataType="System.Int32">
          <ItemTemplate>
            <asp:Label ID="lblHeader" runat="server" Text='<%#Eval("Name")%>' />
          </ItemTemplate>
          <EditItemTemplate>
            <telerik:RadComboBox ID="rcb" runat="server" Skin="Sunset" SelectedValue='<%#Bind("ID")%>'
                            OnDataBinding="rcb_DataBinding" AppendDataBoundItems="true"
              DataSourceID="sqlList" DataTextField="Name" DataValueField="ID" />
          </EditItemTemplate>
        </telerik:GridTemplateColumn>


The first bit of code required is an event handler for the RadComboBox / DropDownList control to add in the extra list item with the current value as follows
    'Add in option for existing record in dropdown
    Protected Sub rcb_DataBinding(sender As Object, e As EventArgs)
        Dim rcb As RadComboBox = DirectCast(sender, RadComboBox)
        Dim rci As New RadComboBoxItem("-- Existing Record --", rcb.SelectedValue)
        rcb.Items.Insert(0, rci)
    End Sub

The next bit of code is required to populate the text label of this new record with the actual value as follows
    'Set the label of the existing
    Protected Sub grd_ItemDataBound(ByVal sender As Object, ByVal e As Telerik.Web.UI.GridItemEventArgs) Handles grd.ItemDataBound
        If (TypeOf e.Item Is GridEditFormItem And e.Item.IsInEditMode) Then
            Dim editItem As GridEditFormItem = CType(e.Item, GridEditFormItem)
            Dim strName As String = editItem.ParentItem.DataItem("Name")
            Dim rcb As RadComboBox = DirectCast(e.Item.FindControl("rcb"), RadComboBox)
            If rcb IsNot Nothing Then rcb.Items(0).Text = strName
        End If
    End Sub

Customising the LinqDataSource

Binding a data grid to a data source quickly is a sinch with a LinqDataSource and it can save a lot of time. However sometimes it doesn't quite cut it and you wished you could use all of its benefits but just change one little aspect. Well actually with a bit of tweaking you can do exactly that. Here are two cases that I've frequently used while building some quick prototypes.

Altering the fields that are sent to a LinqDataSource

The data that is sent or received for a LinqDataSource action may need to be altered for a number of reasons, such as:

1. Altering the data sent in an update if the information is incorrect (e.g. a blank ("") field updating to a numerical field)
2. Filling in extra information for an insert (e.g. a predetermined foreign key)

The code is implemented in the handler for the LinqDataSource action, this will either be Inserting, Updating, or Deleting as the event will need to happen prior to the action.  In the code, first create a variable of the table type, then select the specific field and change it as needed.

Altering an Insert Example:

In this case, a foreign key needs to be automatically filled in.  The foreign key is taken from the query string parameter of the URL.

Protected Sub LinqDataSource_Inserting(sender As Object, e As System.Web.UI.WebControls.LinqDataSourceInsertEventArgs) Handles LinqDataSource.Inserting

    Dim rec As WEB_Table = e.NewObject

    Dim field As String = Request.QueryString("Field")

    If rec IsNot Nothing Then

        rec.Field = field

    End If

End Sub


---

Using a Stored Procedure or more complicated query with a LinqDataSource

Sometimes you want to use all the handiness of the automated Insert, Update and Delete functions but use a select command that is too complicated to be handled by the basic features provided by the LinqDataSource control. In order to do this we just override the Select query in the Selecting event. Here is an example of how to inject a stored procedure as the select data source.

1. Create stored procedure for your SELECT that includes any other business logic you want in it
2. Add the stored procedure to the data context and if you want to turn off optimistic concurrency checks, set Update Check to Never for all properties in that table (can highlight them all to change all at once)
3. In code behind change Selecting event of LinqDataSource to set the e.Result = DC.StoredProcedure(Params).ToList()

Protected Sub lds_Selecting(sender As Object, e As System.Web.UI.WebControls.LinqDataSourceSelectEventArgs) Handles lds.Selecting
    Dim res = db.usp_StoredProcList(Param1, e.WhereParameters("ParentID")).ToList()
    e.Result = res
End Sub

4. Remove any Where clause from LinqDataSource (and any WhereParameters if you don't need them)
5. Use WhereParameter to get ParentID field into Selecting event for Detail Level tables - accessed via e.WhereParameters("ParentID")

Finding orphaned records in SQL

In general relationships should always be created between a table's Primary Key and the associated Foreign Keys in other tables. However sometimes those relationships might not already exist in the database and it must be added later on. This can sometimes fail due to the constraint being unenforceable on the existing data (e.g. because certain database records have already been deleted and the IDs persist in the Foreign Key's table).

To solve this problem you can write one of the following queries to discover the records at fault:

SELECT * FROM FK_table f LEFT OUTER JOIN PK_table p ON p.ID = f.ID
WHERE p.ID IS NULL

SELECT * FROM FK_table f
WHERE NOT EXISTS (SELECT p.ID FROM PK_table p WHERE f.ID = p.ID)

SELECT * FROM FK_table f
WHERE f.ID NOT IN (SELECT p.ID FROM PK_table p)

This final T-SQL method is actually very useful as the equivalent DELETE syntax also works. Therefore to then delete these faulty records you can run this query:

DELETE FROM FK_table
WHERE ID NOT IN (SELECT ID FROM PK_table)

It should also be noted that sometimes Constraints can get dropped automatically when making changes to a table. So any table changes should be followed by a check to make sure all Relationships are still in place.

Testing for Javascript undefined correctly

I believe there are a number of incorrect answers to this topic. Contrary to common belief, "undefined" is NOT a keyword in JavaScript and can in fact have a value assigned to it.

// DO NOT USE the following bad examples, they are degenerate!

var undefined = false;  // Shockingly, this is completely legal!

 

var myVal;

if (myVar === undefined) {

    alert("You have been misled. Run away!");

}

if (myVar == undefined) {

    alert("You have been misled. Run away!");

}

 

Additionally, myVar === undefined will raise an error in the situation where myVar is undeclared.

The most robust way to perform this test is:

if (typeof myVar === "undefined")

 

This will always return the correct result, and even handles the situation where myVar is not declared.

Delayed Facebook Plugin Loading

When doing some complex javascript, using the standard Facebook plugins can really slow down and interfere with your script. To control when it loads and runs you can load it in manually like so (fadeTo function is an example only of organising timing):

$('.myelement').fadeTo(800, 1, function () {
    PluginSetup();
});

function PluginSetup() {
    $.ajaxSetup({ cache: true });
    $('body').append('<div id="fb-root"></div>');
    $.getScript('//connect.facebook.net/en_US/sdk.js', function () {
        FB.init({
            version: 'v2.4'
        });
        FB.XFBML.parse();
    });
}

FB.XFBML.parse() will then run through your HTML looking for plugins (you can specify a selector too) that it needs to activate.

Modern Browser Targeting

We all know that in general we should not put browser specific CSS in, just use CSS that works everywhere. Sometimes though it is unavoidable for little intricacies.

To target IE9 or less use conditional commenting to attach an external CSS file with appropriate overrides:

<!--[if lte IE 9]>
  <link href="/bundles/ie9.css" type="text/css" rel="stylesheet" />
<![endif]-->

To target IE10 and IE11:

@media all and (-ms-high-contrast: none), (-ms-high-contrast: active) { /* IE10+ CSS here */ }

To target Edge Browser:

@supports (-ms-accelerator:true) { /* MS Edge CSS here */ }

To target Firefox:

@-moz-document url-prefix() { /* Firefox CSS here */ }

Handling a LINQ ChangeConflict Exception

Multi-user applications using LINQ-to-SQL will commonly come across a ChangeConflictException at run time when a user tried to update a record which if not handled properly can cause a poor user experience as they can lose their changes. This is because it implements the optimistic concurrency approach by default.

There are a couple of ways of handling the ChangeConflictException which occurs when two processes are trying to update the same record in the database at about the same time (i.e. the record has changed in the database since you initially requested it). One way is to just add UpdateCheck = Never on each field in the DataContext which means last change wins - this is a bit crude though and you may actually want to do something more elegant.

Here is one way to handle it in code:

try {
    _db.SubmitChanges(ConflictMode.ContinueOnConflict);
} catch (ChangeConflictException e) {
    _db.ChangeConflicts.ResolveAll(RefreshMode.KeepCurrentValues);
    _db.SubmitChanges(ConflictMode.ContinueOnConflict);
}

Options for ConflictMode are ContinueOnConflict or FailOnFirstConflict
Options for RefreshMode are KeepChanges, KeepCurrentValues, OverwriteCurrentValues
You can then handle it how you want appropriate to the situation

More info: http://weblogs.asp.net/dixin/understanding-linq-to-sql-9-concurrent-conflict