4.5 database (2)

SQL2012 new features – FileTable. Allow access to data in the form of files and folder. Also supports pagination –
SELECT * FROM abc order by name offset 5 rows fetch next 10 rows only

A database must be FILESTREAM enabled before you can create FileTables. There is a T-SQL you need to execute when you create the database.


The data will be saved in the [DIRECTORY_NAME] folder.
To create a table as a FILETABLE:

Chapter 4 – Accessing Data Using ADO.NET
SqlCrential is a new class in .NET 4.5. You can use it to store connection string credentials (SQL id/password).

Encrypt your connection string in Web.Config

You get this:

Every page has a ValidateRequest tag, and true by default:
This will prevent injection attack.

Use TryParse to test casting operation before you use the value (Save to DB etc.)
Chapter 5 – LINQ

Difference between iEnumerable and iQueryable –
iEnumerable – the query (and filters) is executed in the memory;
iQUeryable – the query (and filters) is executed on the source. Good for data paging. Page 83

Exercise using LINQ to SQL
Create a DB: with 2 table. And insert some test data.

In VS 2012, add a console app:
Right click the project and add New iTem –> Linq To SQL Class.
To add foreign key relationship

Define the FK condition

Once you define the FY, when you drag the tables from SQL explorer to the LINQ designer, it will create two classes and also the relationship.

A datacontext will be created for you.

Write code to read data using the context
F5, result:
Behind the scenes, Linq to SQl use sqlmetal.exe to generate the mapping.
By default, when an entity is loaded with data, child entities are not populated. Called Lazy Loading – for performance reasons.

To eager load, use the class “DataLoadOptions”.

Querying WCF Data services
WCF DS allows you to expose your data as RESTFUL services using OData protocol.

Exercise: Querying NetFlix DB – no longer works.
Page 98


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s