EF casting nchar to nvarchar

Weird glitch to watch out for in EF codefirst approach, where a string property defined in model which SQL Server automatically presumes is an nvarchar. But which actually is a varchar in the database. Since these are coercible, Entity Framework decided to coerce the type under the covers....

Performance Eater: Casting nchar to nvarchar
Performance Eater: Casting nchar to nvarchar

Detecting asp.net session timeouts

A common requirement i get in alot of asp.net applications to automatically redirected to a login page or a home page when my ASP.Net session times out and the user tries to hit a page in the website.

Before i mention the code , it is important to note that every asp.net application you install on your IIS server gets the same session cookie id by default : a cookie called:ASP.NET_SessionId

To establish this , go to IIS , highlight your website , ensure that Features view is on and click on the Session State icon.



Go to the Cookie settings section and you'll se that your webapplication has a cookie name/id of ASP.NET_SessionId. If you install another application , it will get the same id.




So in order to detect when the session has ended you can override the onInit method in your master page and add the following code:

/// <summary>

/// Check for the session time out

/// </summary>

/// <param name="e"></param>

protected override void OnInit(EventArgs e)

{

base.OnInit(e);

if (Context.Session != null)

{

//check whether a new session was generated

if (Session.IsNewSession)

{

//check whether a cookies had already been associated with this request

HttpCookie sessionCookie = Request.Cookies["ASP.NET_SessionId"];

if (sessionCookie != null)

{

string sessionValue = sessionCookie.Value;

if (!string.IsNullOrEmpty(sessionValue))

{

// we have session timeout condition!

Response.Redirect(

"MyhomePage.aspx");

}

Session Isolation

This is all well and good , but seeing as every .NET application gets the same cookie id on the same iis box , it is clear that this would cause unpredictable results in the scenario that you would have two or more .NET applications installed on the same server and want to detect the session timeouts in each.

In order to do this we would need to ensure session isolation. One solution to this would to put each .NET application in a different app pool. This would create the isolation needed to allow thier sessions to work independantly to each other.

If this isn't an option , another way to do it is to specify a unique session cookie Id for each .NET application in their web.configs. This can be either done manually in the web.config or can be programatically achived by Creating customized Setup projects in Visual Studio 2008/2010.

To Manually add a unique cookie to your web.config , add the cookieName attribute to your sesssionstate element. It's good practice to give this your application name. If you have multiple versions of the sample application installed on your iis box you can configure this via the a customized setup project to create the cookie based on application name and version so you get a unique cookieName.

sessionState mode="InProc" cookieName="MyCompany.MyFinancialWebsite" timeout="15" />

/// <summary>

/// Handles the Start event of the Session control.

/// </summary>

/// <param name="sender">The source of the event.</param>

/// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>

protected void Session_Start(object sender, EventArgs e)

{

SessionStateSection sessionStateSection =

(System.Web.Configuration.

SessionStateSection)

ConfigurationManager.GetSection("system.web/sessionState");

string cookieName = sessionStateSection.CookieName;

//Detect session timeout and re-direct to home page

string request_cookies = Request.Headers["Cookie"];

if ((null != request_cookies) && (request_cookies.IndexOf(cookieName) >= 0))

{

////cookie existed, so this new one is due to timeout.

////Redirect the user to the default page

Response.Redirect(

"AccountSearch.aspx?Timeout=true");

}


}

EF5 Performance Considerations

Over the past few weeks the EF team has been putting together a whitepaper that talks about the performance considerations developers should take when using Entity Framework. Performance is one critical aspect of developing modern applications, and this document will help developers make informed design decisions and get the most out of their applications when using the Entity Framework 5 (and also EF 4).

http://blogs.msdn.com/b/adonet/archive/2012/04/05/ef5-performance-considerations.aspx

New EF5 Pluralsight Course

Pluralsight have just posted a new Entity Framework 5 course by EF Expert Julie Lerman .
This course provides an introduction to using Entity Framework 5 with Visual Studio 2012
Check it out !

Non Ajax solution for disabling a button after click for full .NET postbacks

It's that old chestnut where you want to disable a button after the user clicks it to prevent them potentially clicking it again and accidental calling the server side event twice. This is especially an issue for longer running tasks .

This may sound really easy to do at first , just disable it with javasctip right ? But when you try this, you'll find that with disabling a submit button on the client side will cancel the browser’s submit, and thus the postback. Not what you want to happen!

There are a few methods going around to do this , but i find this one the easiest , kick back to Encosia where i first found this approach.

This method is to use the OnClientClick and UseSubmitBehavior properties of the button control.

<asp:Button runat="server" ID="BtnSubmit"
  OnClientClick="this.disabled = true; this.value = 'Submit in progress...';"
  UseSubmitBehavior="false"
  OnClick="BtnSubmit_Click"
  Text="Click to Submit" />

OnClientClick allows you to add client side OnClick script. In this case, the JavaScript will disable the button element and change its text value to a progress message. When the postback completes, the newly rendered page will revert the button back its initial state without any additional work

Column Level Database Encryption using Symmetric Keys

Recently i needed to encrypt a column on (a password field) in a table in SQL Server 2008 database. After a bit of research and reading some great articles by Pinal Dave and Laurentiu Cristofor i decided Symmetric keys were the best approach for what i needed to achieve.

Encryption using Symmetric keys are one of the recommended methods of column level encryption in in SQL Server 2005/2008 for a number of reasons:

Advantages Of Symmetric Keys Encryption

• Performance.Symmetric key encryption is known to be much faster and stronger than their asymmetric counterpart. It uses less overhead on system resources. For some examples and timings between Symmetric and Asymmetric key encryption check out Brian Kelley
• Personally i find the symmeteric key approach very easy to work with.
• Easy to backup and restore your database without alot of re-work or data loss , ill talk about this later on in the article.

When a symmetric key is created, the symmetric key must be encrypted by using at least one of the following: certificate, password, symmetric key, asymmetric key, or PROVIDER. The key can have more than one encryption of each type. In other words, a single symmetric key can be encrypted by using multiple certificates, passwords, symmetric keys, and asymmetric keys at the same time.

Simple Encryption Example

Here is a simple example using the Master key to encrypt the Certificates and Keys in a database.

/* Create Database Master Key */

USE AliciaEncryptionTest
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD = 'MyTestPassword'
GO

Now, you'll need a certificate with which you will encrypt your symmetric key. Certificates are used to safeguard encryption keys, which are used to encrypt data in the database.

/* Create Encryption Certificate */

USE AliciaEncryptionTest
GO
CREATE CERTIFICATE EncryptTestCert
WITH SUBJECT = 'MyEncryptionDatabaseCert'
GO

Once you have your certificates, you can create your key. The symmetric key can be encrypted by using various options such as certificate, password, symmetric key, and asymmetric key.

We can use many types of algorithm while creating Symmetric keys like DES, TRIPLE_DES, TRIPLE_DES_3KEY, AES_128, AES_192, AES_256 etc.



You should try to use the most secure algorithm you can, which is AES_256 in SQL Server 2012. It’s the same back to SQL Server 2005. You should avoid the RC4 algorithms, since they are not terribly secure. Even the DES ones you might avoid, but do some research to understand if you have a need to use anything less than AES_256.

/* Create Symmetric Key */
USE AliciaEncryptionTest
GO
CREATE SYMMETRIC KEY TestTableKey
WITH ALGORITHM = AES_256
BY CERTIFICATE EncryptTestCert
GO



It's as simple as that. You're key is now created and ready to use. To use this key in a simple example , create a table and add a column of type varbinary which will be the column you wish to encrypt.

USE EncryptTest
GO
CREATE TABLE TestTable(FirstCol INT, EncryptSecondCol VARBINARY(256))
GO

Before you can use your symmetric key, you have to open it. The symmetric key remains open for the life of the session. It is good practice to close your key after use as well. Here's how you open and close keys.

/* Update binary column with encrypted data created by certificate and key */
USE AliciaEncryptionTest
GO
OPEN SYMMETRIC KEY TestTableKey

DECRYPTION BY CERTIFICATE EncryptTestCert

INSERT

INTO TestTable VALUES (1,ENCRYPTBYKEY(KEY_GUID('TestTableKey'),'NewPassword' ))

GO

/* Close symmetric key */

CLOSE SYMMETRIC KEY TestTableKey ;

Authorized user can use the decryptbykey function to retrieve the original data from the encrypted column. If Symmetric key is not open for decryption, it has to be decrypted using the same certificate that was used to encrypt it. Decryption uses the same method that was used for encrypting it. Because of the same reason, we are using the same certificate for opening the key and making it available for use.

/* Decrypt the data of the SecondCol */
USE AliciaEncryptionTest
GO
OPEN SYMMETRIC KEY TestTableKey

DECRYPTION BY CERTIFICATE EncryptTestCert
SELECT CONVERT(VARCHAR(50),DECRYPTBYKEY(EncryptSecondCol)) AS DecryptSecondCol
FROM TestTable
GO

CLOSE SYMMETRIC KEY TestTableKey ;

GO

/* Clean up database */
USE AliciaEncryptionTest
GO
CLOSE SYMMETRIC KEY TestTableKey
GO
DROP SYMMETRIC KEY TestTableKey
GO
DROP CERTIFICATE EncryptTestCert
GO
DROP MASTER KEY
GO

Backing up and restoring your database to a different server

Often you have to backup your database and restore it to a different server. Sometimes you have data already in an encrypted table and you need to be able to decrypt it from the new server. It is important to note that you cannot backup a symmetric key from one database and copy it to another.

When you create a backup, the symmeteric key is saved as part of the backup, so therefore they are available upon restore. However if the symmeteric  key was ecrypted using a certificate, that may not be available if the restore is to a different box.




Certificates are also stored in the database, so they should be available with the symmetric keys. You don't loose the certificates if you move the database to another server. The only thing you may need to do after restoring a database on a different server, is to restore the SMK encryption of the DbMK. For this, you need to execute the following statements in the database after you restored it:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'DbMK password';ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
Other than this, you don't need to do anything special to be able to work with the encrypted data like you worked on the original server.

Recreate your default web site IIS6/7

If your default web site ever gets deleted or becomes corrupt as has happened to me on a number of occasions , make sure you recreate your Default web site this way using systemdrive variable and not 'C:\inetpub\wwwroot' as the physical location for the default website.

%SystemDrive%\Inetpub\wwwroot

Go to IIS , Click Actions/Add Website and fill in like this :

Exporting a asp.NET GridView to csv format

If you need to export the contents of a GridView to CSV format, one way is to use this static class and static method ExportToCSV which take a GridView object as a param.

This loops through all headers and rows in grid and exports them all as comma delimited csv and pushes the response to the screen to allow the user to either save or open. On clicking open , it opens the response in Excel by default.

Important points to note in this code are :AddHeader & ContentType properties of the Response. This ensures you create a CSV.

/// <summary>

/// Exports a Grid to CSV format

/// </summary>

/// <param name="grid">The grid.</param>

public static void ExportToCSV(GridView grid, int jurisdictionId)

{

StringBuilder sb = new StringBuilder();

bool addExtraROIFields = false;

addExtraROIFields = jurisdictionId ==

Constant.Juristiction_ROI;

////Loop through all our grid columns to build up our string

for (int k = 0; k < grid.Columns.Count; k++)

{

////Check jurisdiction , for GB , we dont want IBAN or Swift coming out , which are column 3 , 4

if (k == 3 || k == 4)

{

///If we are to add extra fields , go ahead ...

if (addExtraROIFields)

{

//add column text , plus a comma separator

sb.Append(grid.Columns[k].HeaderText +

',');

}

}

else

{

sb.Append(grid.Columns[k].HeaderText +

',');

}

}

//append new line

 sb.Append(

"\r\n");

////Loop through all our rows and append each with comma

for (int i = 0; i < grid.Rows.Count; i++)

{

for (int k = 0; k < grid.Columns.Count; k++)

{

////Check jurisdiction , for GB , we dont want IBAN or Swift coming out , which are column 3 , 4

if (k == 3 || k == 4)

{

///If we are to add extra fields , go ahead ...

if (addExtraROIFields)

{

//add row text , plus a comma separator

sb.Append(grid.Rows[i].Cells[k].Text +

',');

}

}

else

{

sb.Append(grid.Rows[i].Cells[k].Text +

',');

}

}

//append new line

sb.Append(

"\r\n");

}

///Make sure you strip out spaces

string theResult = sb.ToString().Replace("&nbsp;", "");

///Push it out to the screen , to prompt user to open or save

HttpContext.Current.Response.Clear();

HttpContext.Current.Response.Buffer = true;

HttpContext.Current.Response.AddHeader("content-disposition",

"attachment;filename=GridViewExport.csv");

HttpContext.Current.Response.Charset = "";

HttpContext.Current.Response.ContentType = "application/text";

HttpContext.Current.Response.Output.Write(theResult);

HttpContext.Current.Response.Flush();

HttpContext.Current.Response.End();

 }

Adding concurency checking - with Entity Framework

By default Entity Framework does not check for concurrency.It supports an optimistic concurrency model. Default is a client wins approach. So if two users are editing a record at the same time , the last to save it will get their record in and any changes from the other user will be lost.

If you do not wish to allow "client wins" approach or a "store wins" and you want to prevent any updates if a given record has been updated and force the user to re-load the record if they wish to proceed. Then this approach is for you.

In this way you can raise a validation to the user and tell them that the record is out of date and they need to reload it , or alternatively get the latest updated version from the database. However i would not recommend this approach because the the user is not updating the record the see on their screen. Therefore i would recommend forcing the user to re-open the record or refresh the screen.

This approach of managing concurrency uses the timestamp method.

1. Add a new column , of datatype Timestamp to the table on which you want to perform concurrency checks on. NB: Ensure it doesn't allow nulls

           alter table XXX

           add ConcurrencyCheck timestamp null 

                  2. Delete your table from your model in your .NET application and re-add it.

Implementing a concurrency mode of Fixed or None ??

Now you have your column in your database you have two ways to implement it.

There are two concurrency modes in Entity Framework:

• None – This is the default and means the property is not involved in any concurrency checks 
• Fixed – Which means that the original value of this property is sent as part of the WHERE clause in all updates or deletes 

So timestamps have a Concurrency Mode of Fixed, which means the original value loaded from the database is included in the WHERE clause of any Updates or Deletes. This means EF does the checking for your automatically.

If you want to use Fixed and let the EF handle your concurrency issues :

Using Concurrency Mode : Fixed

1. Ensure you change the ConcurrencyMode in the properties of the new column you just added to "Fixed".

If you set it to Fixed then you just have to trap an Optimistic concurrency error when you save the context.

1: 2: 3: 4: 5: 6: 7: 8: 9: 10: 11:

try { context.SaveChanges(); } catch (OptimisticConcurrencyException ex) { ////handle your exception here...

Using Concurrency Mode : None

If you wish to handle your own concurrency checking , i.e. raise a validation informing the user and not even allowing a save to occur then you can set Concurrency mode None.

1. Ensure you change the ConcurrencyMode in the properties of the new column you just added to "None".
2. To use this in your code , i would create a variable to store your current timestamp on the screen you which to check a save on.

1: 2: 3: 4: 5: 6: 7: 8: 9: 10: 11: 12:

private byte[] CurrentRecordTimestamp { get { return (byte[])Session["currentRecordTimestamp"]; } set { Session["currentRecordTimestamp"] = value; } }

1. On screen load , or when you populate the screen with the data you wish you edit , i would pull out the current record under edit's ConcurrencyCheck value into this variable you created

1: 2: 3: 4:

private void PopulateScreen() { this.CurrentRecordTimestamp = currentAccount.ConcurrencyCheck;

Then if the user leaves the record open , and someone else in the meantime changes it , and then they also attempt to save , you can compare this timestamp value you saved earlier with the concurrency value it is now.

1: 2: 3:

If (Convert.ToBase64String(accountDetails.ConcurrencyCheck) !=  Convert.ToBase64String(this.CurrentRecordTimestamp)) { }

If exists , drop temp table

I have to write this code again and again, so keeping it here as a handy reference.

IF EXISTS( SELECT 1

FROM tempdb.dbo.sysobjects

where ID = OBJECT_ID(N'#tempOutput'))

BEGIN

DROP TABLE #tempOutput

END

------OR--------

IFOBJECT_ID(N'tempdb..#Temp') IS NOT NULL
BEGIN
DROP TABLE #Temp
END