asp.net 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.

image

The data will be saved in the [DIRECTORY_NAME] folder.
To create a table as a FILETABLE:
CREATE TABLE FT AS 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).
image

Encrypt your connection string in Web.Config
image

You get this:
image

Every asp.net page has a ValidateRequest tag, and true by default:
image
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.
image

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

Define the FK condition
image

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.
image

A datacontext will be created for you.
image

Write code to read data using the context
image
F5, result:
image
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”.
image

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

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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