Scripts and Batches

DECLARE @Ident int;

SET @TotalCost = 10
SET @TotalCost = @UnitCost * 1.1

SELECT @Test = MAX(UnitPrice) FROM Sales.SalesOrderDetail;

@@IDENTITY  

Returns the last identity value inserted as a result of the last INSERT or SELECT INTO statement
@@ROWCOUNT
Returns the number of rows affected by the last statement.

A batch is a grouping of T-SQL statements into one logical unit. All of the statements within a batch are
combined into one execution plan, so all statements are parsed together and must pass a validation of
the syntax or none of the statements will execute. Note, however, that this does not prevent runtime errors
from happening. In the event of a runtime error, any statement that has been executed prior to the runtime
error will still be in effect. To summarize, if a statement fails at parse-time, then nothing runs. If a statement
fails at runtime, then all statements until the statement that generated the error have already run.

To separate a script into multiple batches, we make use of the GO statement.  Because each batch is processed independently, an error in one batch does not prevent another batch from running.

Thinking that GO is a T-SQL command is a common mistake. GO is a command that is only recognized by
the editing tools (Management Studio, sqlcmd). If you use a third-party tool, then it may or may not
support the GO command, but most that claim SQL Server support will.

There are several commands that absolutely must be part of their own batch. These include:
❑ CREATE DEFAULT
❑ CREATE PROCEDURE
❑ CREATE RULE
❑ CREATE TRIGGER
❑ CREATE VIEW

USE AdventureWorks2008;
DECLARE @MyVarchar varchar(50); --This DECLARE only lasts for this batch!
SELECT @MyVarchar = ‘Honey, I’’m home...’;
PRINT ‘Done with first Batch...’;
GO
PRINT @MyVarchar; --This generates an error since @MyVarchar
--isn’t declared in this batch
PRINT ‘Done with second Batch’;
GO
PRINT ‘Done with third batch’; -- Notice that this still gets executed
-- even after the error
GO

Perhaps the most likely scenario for using batches is when precedence is required — that is, you need
one task to be completely done before the next task starts.

sqlcmd is a utility that allows you to run scripts from a command prompt in a Windows command box.

Dynamic SQL: Generating Your Code On the Fly with the EXEC Command

T-SQL offers most of the classic choices for control of flow situations, including:
❑ IF ... ELSE
❑ GOTO
❑ WHILE
❑ WAITFOR
❑ TRY/CATCH

CASE <input expression>
WHEN <when expression> THEN <result expression>
[...n]
[ELSE <result expression>]
END

WAITFOR
DELAY <’time‘> | TIME <’time‘>

BEGIN TRY
{ <sql statement(s)> }
END TRY
BEGIN CATCH
{ <sql statement(s)> }
END CATCH [ ; ]

Store Procedure

The primary benefits of sprocs include:
❑ Making processes that require procedural action callable
❑ Security
❑ Performance

Much like views, we can create a sproc that returns a recordset without having to give the user authority to the underlying table.

After the sproc has been created, it sits in wait for the first time that it is executed. At that time, the sproc
is optimized and a query plan is compiled and cached on the system. Subsequent times that we run our
sproc will, unless we specify otherwise using the WITH RECOMPILE option, generally use that cached
query plan rather than creating a new one.

EXEC spMySproc ‘1/1/2004’
WITH RECOMPILE

DROP PROC|PROCEDURE <sproc name>[;]

USE AdventureWorks2008;
GO
CREATE PROC spEmployeeByName
@LastName nvarchar(50)
AS
SELECT p.LastName, p.FirstName, e.JobTitle, e.HireDate
FROM Person.Person p
JOIN HumanResources.Employee e
ON p. BusinessEntityID = e.BusinessEntityID
WHERE p.LastName LIKE @LastName + ‘%’;

EXEC spEmployeeByName ‘Dobney’;

CREATE PROC spEmployeeByName
@LastName nvarchar(50) = NULL
AS
IF @LastName IS NOT NULL
SELECT p.LastName, p.FirstName, e.JobTitle, e.HireDate
FROM Person.Person p
JOIN HumanResources.Employee e
ON p.BusinessEntityID = e.BusinessEntityID
WHERE p.LastName LIKE @LastName + ‘%’;
ELSE
SELECT p.LastName, p.FirstName, e.JobTitle, e.HireDate
FROM Person.Person p
JOIN HumanResources.Employee e
ON p.BusinessEntityID = e.BusinessEntityID;

CREATE PROCEDURE [dbo].[uspLogError]
@ErrorLogID [int] = 0 OUTPUT -- contains the ErrorLogID of the row inserted
AS -- by uspLogError in the ErrorLog table
BEGIN
SET NOCOUNT ON;
-- Output parameter value of 0 indicates that error
-- information was not logged
SET @ErrorLogID = 0;
BEGIN TRY ……

Actually, your program will receive a return value whether you supply one or not. By default, SQL
Server automatically returns a value of zero when your procedure is complete.

Note that the return value must be an integer.

USE AdventureWorks2008;
GO
ALTER PROC spTestReturns
AS
DECLARE @MyMessage varchar(50);
DECLARE @MyOtherMessage varchar(50);
SELECT @MyMessage = ‘Hi, it’’s that line before the RETURN’;
PRINT @MyMessage
RETURN 100;
SELECT @MyOtherMessage = ‘Sorry, but we won’’t get this far’;
PRINT @MyOtherMessage;
RETURN;

DECLARE @Return int;
EXEC @Return = spTestReturns;
SELECT @Return;

@@ERROR

USE AdventureWorks2008
GO
CREATE PROC spInsertValidatedBusinessEntityContact
@BusinessEntityID int,
@PersonID int,
@ContactTypeID int
AS
BEGIN
DECLARE @Error int;
INSERT INTO Person.BusinessEntityContact
(BusinessEntityID
,PersonID
,ContactTypeID)
VALUES
(@BusinessEntityID, @PersonID, @ContactTypeID);
SET @Error = @@ERROR;
IF @Error = 0
PRINT ‘New Record Inserted’;
ELSE
BEGIN
IF @Error = 547 -- Foreign Key violation. Tell them about it.
PRINT ‘At least one provided parameter was not found. Correct and retry’;
ELSE -- something unknown
PRINT ‘Unknown error occurred. Please contact your system admin’;
END
END

Manually Raising Errors

RAISERROR (<message ID | message string | variable>, <severity>, <state>
[, <argument>
[,<...n>]] )
[WITH option[,...n]]

Extended Stored Procedures (XPs)

SQL Server does allow for the idea of externally written
code that runs as a .DLL in process with SQL Server. XPs are created using C or C++.

User-Defined Functions (UDFs)

With a sproc, you can pass parameters in and also get values in parameters passed back out. You
can return a value, but that value is really intended to indicate success or failure rather than return
data. You can also return result sets, but you can’t really use those result sets in a query without
first inserting them into some kind of table (usually a temporary table) to work with them further

Even using a table valued output parameter, you still need to make at least one additional step before
using the results in a query.

With a UDF, however, you can pass parameters in, but not out. Instead, the concept of output parameters
has been replaced with a much more robust return value. As with system functions, you can return a scalar
value — what’s particularly nice, however, is that this value is not limited to just the integer data type
as it would be for a sproc. Instead, you can return most SQL Server data types (more on this in the next
section).

As they like to say in late-night television commercials: “But wait! There’s more!” The “more” is that you
are actually not just limited to returning scalar values—you can also return tables. This is wildly powerful,
and we’ll look into this fully later in the chapter.

CREATE FUNCTION dbo.DayOnly(@Date datetime)
RETURNS varchar(12)
AS
BEGIN
RETURN CONVERT(varchar(12), @Date, 101);
END

SELECT * FROM Orders WHERE dbo.DayOnly(OrderDate) = dbo.DayOnly(GETDATE());

CREATE FUNCTION dbo.fnContactList()
RETURNS TABLE
AS
RETURN (SELECT BusinessEntityID,
LastName + ‘, ‘ + FirstName AS Name
FROM Person.Person);
GO

// parameterized view

CREATE FUNCTION dbo.fnContactSearch(@LastName nvarchar(50))
RETURNS TABLE
AS
RETURN (SELECT p.BusinessEntityID,
LastName + ‘, ‘ + FirstName AS Name,
ea.EmailAddress
FROM Person.Person as p
LEFT OUTER JOIN Person.EmailAddress ea
ON ea.BusinessEntityID = p.BusinessEntityID
WHERE LastName Like @LastName + ‘%’);
GO

Any coverage of UDFs would be incomplete without discussing determinism. If SQL Server is going to
build an index over something, it has to be able to deterministically define (define with certainty) what
the item being indexed is.

Transactions and Locks

Transactions are all about atomicity. Atomicity is the concept that something should act as a unit.
From our database standpoint, it’s about the smallest grouping of one or more statements that
should be considered to be all or nothing.

❑ BEGIN a transaction: Set the starting point.
❑ COMMIT a transaction: Make the transaction a permanent, irreversible part of the database.
❑ ROLLBACK a transaction: Say essentially that we want to forget that it ever happened.
❑ SAVE a transaction: Establish a specific marker to allow us to do only a partial rollback.

In the normal operation of your database, most activities that you perform are logged to the transaction
log, rather than written directly to the database. A checkpoint is a periodic operation that forces all dirty
pages for the database currently in use to be written to disk. Dirty pages are log or data pages that have
been modified after they were read into the cache, but the modifications have not yet been written to
disk. Without a checkpoint the log would fill up and/or use all the available disk space.

The SQL Server lock manager is that shopkeeper. When you come into the SQL Server store, the lock manager
asks what your intent is — what it is you’re going to be doing. If you say “just looking,” and no one
else already there is doing anything other than just looking, then the lock manager will let you in. If you
want to buy (update or delete) something, then the lock manager will check to see if anyone’s already there.
If so, you must wait, and everyone who comes in behind you will also wait. When you are let in to buy,
no one else will be let in until you are done.

Locks can address four major problems:
❑ Dirty reads
Dirty reads occur when a transaction reads a record that is part of another transaction that isn’t complete
yet. If the first transaction completes normally, then it’s unlikely there’s a problem. But what if the transaction
were rolled back? You would have information from a transaction that never happened from the
database’s perspective!
❑ Non-repeatable reads
A non-repeatable read is caused when you read the record twice in a transaction and a separate transaction
alters the data in the interim.
❑ Phantoms
records that appear mysteriously,
as if unaffected by an UPDATE or DELETE statement that you’ve issued. someone performed an INSERT statement at the very same time your UPDATE was running. Since it was
an entirely new row, it didn’t have a lock on it and it proceeded just fine
❑ Lost updates
Lost updates happen when one update is successfully written to the database, but is accidentally overwritten
by another transaction. Lost updates can happen when two transactions read an entire record, then one writes updated information
back to the record and the other writes updated information back to the record.

Lockable Resources

There are six lockable resources for SQL Server, and they form a hierarchy. The higher level the lock, the
less granularity it has (that is, you’re choosing a higher and higher number of objects to be locked a cascading
type of action just because the object that contains them has been locked). These include, in
ascending order of granularity:
❑ Database: The entire database is locked. This happens usually during database schema changes.
❑ Table: The entire table is locked. This includes all the data-related objects associated with that
table, including the actual data rows (every one of them) and all the keys in all the indexes associated
with the table in question.
❑ Extent: The entire extent is locked. Remember than an extent is made up of eight pages, so an
extent lock means that the lock has control of the extent, the eight data or index pages in that
extent, and all the rows of data in those eight pages.
❑ Page: All the data or index keys on that page are locked.
❑ Key: There is a lock on a particular key or series of keys in an index. Other keys in the same
index page may be unaffected.
❑ Row or Row Identifier (RID): Although the lock is technically placed on the row identifier (an
internal SQL Server construct), it essentially locks the entire row.

Lock Modes

Beyond considering just what resource level you’re locking, you also should consider what lock mode
your query is going to acquire. Just as there are a variety of resources to lock, there are also a variety of
lock modes.

Shared Locks
A shared lock is used when you need only to read the data. one thing that shared locks do is prevent users from performing dirty reads.

Exclusive Locks
Exclusive locks are not compatible with any other lock. This prevents two people from updating, deleting, or whatever at the same time.

Update Locks
Update locks are something of a hybrid between shared locks and exclusive locks. Think about it — in order to do an update, you need to validate your WHERE
clause (assuming there is one) to figure out just what rows you’re going to be updating. That means that
you only need a shared lock, until you actually go to make the physical update. At the time of the physical
update, you’ll need an exclusive lock. an update lock prevents any other update locks from being established.
deadlock: two update query finish the read and waiting to escalate to an exclusive lock. they wait for each other.

Intent Locks
Without intent locks, the higher-level objects wouldn’t even know that you had the lock at the lower
level.

Schema Locks
Bulk Update Locks

Isolation Level

The first thing to understand about the relationship between transactions and locks is that they are inextricably
linked with each other. By default, any lock that is data modification related will, once created,
be held for the duration of the transaction. If you have a long transaction, this means that your locks
may be preventing other processes from accessing the objects you have a lock on for a rather long time.
It probably goes without saying that this can be rather problematic.
However, that’s only the default. In fact, there are actually five different isolation levels that you can set:
❑ READ COMMITTED (the default)

the locks associated with the SELECT statement
are freed as soon as the SELECT statement is complete — SQL Server doesn’t wait for the end of the
transaction.
Action queries (UPDATE, DELETE, and INSERT) are a little different. If your transaction performs a query
that modifies data, then those locks will be held for the duration of the transaction (in case you need to
roll back).
By keeping this level of default with READ COMMITTED, you can be sure that you have enough data
integrity to prevent dirty reads. However, non-repeatable reads and phantoms can still occur.

❑ READ UNCOMMITTED

READ UNCOMMITTED is the most dangerous of all isolation level choices, but also has the highest performance
in terms of speed.
Setting the isolation level to READ UNCOMMITTED tells SQL Server not to set any locks and not to honor
any locks. With this isolation level, it is possible to experience any of the various concurrency issues we
discussed earlier in the chapter (most notably a dirty read).

there are actually good reasons to have this isolation level, and they almost always have to do with reporting.

By using READ UNCOMMITTED, you can often get around this problem — at least for reports where the
numbers don’t have to be exact.

You can get the same effect as READ UNCOMMITTED by adding the NOLOCK optimizer hint in your query.

❑ REPEATABLE READ

The REPEATABLE READ escalates your isolation level somewhat and provides an extra level of concurrency
protection by preventing not only dirty reads (the default already does that), but also preventing
non-repeatable reads.

That prevention of non-repeatable reads is a big upside, but holding even shared locks until the end of
the transaction can block users’ access to objects, and therefore hurt productivity. Personally, I prefer to
use other data integrity options (such as a CHECK constraint together with error handling) rather than
this choice, but it remains an available option.
The equivalent optimizer hint for the REPEATABLE READ isolation level is REPEATABLEREAD (these are
the same, only no space).

❑ SERIALIZABLE

SERIALIZABLE is something of the fortress of isolation levels. It prevents all forms of concurrency issues
except for a lost update. Even phantoms are prevented.
When you set your isolation to SERIALIZABLE, you’re saying that any UPDATE, DELETE, or INSERT to
the table or tables used by your transaction must not meet the WHERE clause of any statement in that
transaction. Essentially, if the user was going to do something that your

❑ SNAPSHOT

指定事务中任何语句读取的数据都将是在事务开始时便存在的数据的事务上一致的版本。事务只能识别在其开始之前提交的数据修改。在当前事务中执行的语句将看不到在当前事务开始以后由其他事务所做的数据修改。其效果就好像事务中的语句获得了已提交数据的快照，因为该数据在事务开始时就存在。
http://blog.csdn.net/sandyzhs/archive/2008/10/24/3136465.aspx

http://www.csstemplatesforfree.com/default.aspx

http://adamnoffie.blogspot.com/2007/03/aspnet-forms-authentication-strange.html

the login.aspx page would be missing all of the style information from our CSS style sheet

Solution - made a second Web.config and placed it in my App_Themes directory where my style sheet lives.
<system.web>
<authorization>
<allow users="*">
</allow>
</authorization>
</system.web>

or use

<location path="App_Themes" allowOverride="false">
  <system.web>
    <authorization>
      <allow users="*"/>
    </authorization>
  </system.web>
</location>

http://www.4guysfromrolla.com/articles/052406-1.aspx
http://www.ezzylearning.com/tutorial.aspx?tid=5187857

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
    BackColor="White" BorderColor="#336699" BorderStyle="Solid" BorderWidth="1px"  
    CellPadding="0" CellSpacing="0" DataKeyNames="CategoryID" Font-Size="10"
    Font-Names="Arial" GridLines="Vertical" Width="40%">
           
            <Columns>            
                <asp:TemplateField>
                    <ItemTemplate>
                        <asp:CheckBox ID="chkStatus" runat="server"
                            AutoPostBack="true" OnCheckedChanged="chkStatus_OnCheckedChanged"
                            Checked='<%# Convert.ToBoolean(Eval("Approved")) %>'
                            Text='<%# Eval("Approved").ToString().Equals("True") ? " Approved " : " Not Approved " %>' />
                    </ItemTemplate>                   
                </asp:TemplateField>
               
                <asp:BoundField DataField="CategoryID" HeaderText="CategoryID" />                   
                <asp:BoundField DataField="CategoryName" HeaderText="CategoryName"  />
            </Columns>
           
    <HeaderStyle BackColor="#336699" ForeColor="White" Height="20" />
          
</asp:GridView>

<asp:GridView ID="FileList" runat="server"
    AutoGenerateColumns="False" DataKeyNames="FullName">
    <Columns>
        <asp:TemplateField>
            <ItemTemplate>
                <asp:CheckBox runat="server" ID="RowLevelCheckBox" />
            </ItemTemplate>
        </asp:TemplateField>
        <asp:BoundField DataField="Name" HeaderText="Name" />
        <asp:BoundField DataField="CreationTime" HeaderText="Created On">
            <ItemStyle HorizontalAlign="Right" />
        </asp:BoundField>
        <asp:BoundField DataField="Length" DataFormatString="{0:N0}"
                     HeaderText="File Size"
            HtmlEncode="False">
            <ItemStyle HorizontalAlign="Right" />
        </asp:BoundField>
    </Columns>
</asp:GridView>

Protected Sub DeleteButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles DeleteButton.Click
   Summary.Text = "The following file would have been deleted:<ul>"

   Dim currentRowsFilePath As String

   'Enumerate the GridViewRows
   For index As Integer = 0 To FileList.Rows.Count - 1
      'Programmatically access the CheckBox from the TemplateField
      Dim cb As CheckBox = CType(FileList.Rows(index).FindControl("RowLevelCheckBox"), CheckBox)

      'If it's checked, delete it...
      If cb.Checked Then
         currentRowsFilePath = FileList.DataKeys(index).Value
         Summary.Text &= String.Concat("<li>", currentRowsFilePath, "</li>")
      End If
   Next

   Summary.Text &= "</ul>"
End Sub 

http://www.andybudd.com/archives/2004/02/css_crib_sheet_3_centering_a_div/

body {
	text-align: center;
	min-width: 600px;
}

#wrapper {
	margin:0 auto;
	width:600px;
	text-align: left;
}

http://thedatafarm.com/LearnEntityFramework/resources/
http://blogs.msdn.com/dsimmons/pages/entity-framework-faq.aspx

It is possible to set a minumum length e.g.
string.format("{0,10:s}",12345"); will return a string 10 chars long with right alignment

string.format("{0,10:s}",12345"); will return a string 10 chars long with left alignment

Well-designed databases can pose a problem for developers.

In the data world, a database is designed for maintainability, security, efficiency, and scalability. Its data is organized in a way that satisfies the demands of a good database administrator, yet provides challenges for the developer who needs to access that data.

The EDM follows this concept, but in the Entity Framework, it moves the modeling into XML files that different programming models can use. The primary XML file contains the conceptual model, which is the actual EDM. A second XML file contains a representation of the database and a third, the mapping between the first two. At design time, all three files are bundled into a single EDMX file. The build process splits the EDMX out into the three metadata files that are used at runtime. The Entity Framework then provides a framework that allows developers to write .NET applications based on this model.

As long as the EDM provides the conceptual schema, a representation of the database, a mapping file, and access to an Entity Framework-aware ADO.NET provider for the target database, the Entity Framework doesn't care what database is being targeted. It provides a common means of interacting with the database, common query syntax, and a common method for sending changes back to the database.

Although the Entity Framework provides a very rich set of features for developers, its most important capabilities are the following:

• It automatically generates classes from the model and updates those classes dynamically anytime the model changes.

• It takes care of all of the database connectivity so that developers are not burdened by having to write lots of code for interacting with the database.

• It provides common query syntax for querying the model, not the database, and then translates these queries into queries that the database can understand.

• It provides a mechanism for tracking changes to the model's objects as they are being used in applications, and handles the updates to the database.

In addition, because the model's classes are dynamically generated, minor changes to the model need not have a major impact on your application. Furthermore, modifying the model is much simpler than modifying your objects and the data access code on which they rely.

Navigation properties are pointers to related entities.
An Entity Set is a container for a collection of entities of a single type.

Cleaning up the entity, property, and association names is a step that you should consider performing immediately after you create a new model with the ADO.NET Entity Data Model Wizard. In this way, as you begin to code against the model, the names of the objects will be logical. Additionally, if you change these names after you have begun to code, you will have to modify your code to reflect the changes.

The EDMX file is composed of two main sections: the runtime information and the Designer information. The runtime section comprises three additional sections: one each for storage models, conceptual models, and mappings. The Designer section specifies where the various model elements should be placed visually in the Designer.

Why use the storage layer to represent the data store when you have the actual data store to work with? There are a number of reasons to use this piece of the model. The most important reason is that this provides loose coupling to the database; not every object in the database needs to be in the mode

Although the entire model is contained in a single file at design time, when the project is compiled it will create three separate files—one for each of these sections. The conceptual layer is saved to a file with a .csdl extension, which stands for Conceptual Schema Definition Language. The storage layer is saved to a file with an .ssdl extension (which stands for Store Schema Definition Language) and the mapping layer is saved to a file with an .msl extension (which stands for Mapping Specification Language). These files are used at runtime, which is why they are contained in a section called edmx:Runtime in the model.

Although it makes sense to have a container for an entity because you could have many contact entities to work with, how would there be a collection of associations? When you are working with entity objects, the associations between the entities are also objects. If you have a single contact with multiple addresses in memory, there would be one FK_Address_Contact association object for each relationship. Figure 2-14 shows two association objects that are used to define relationships between a single contact and two addresses.

<NavigationProperty Name="Contact"

                      Relationship="ProgrammingEFDB1Model.FK_Address_Contact"

                       FromRole="Address" ToRole="Contact" />

The collection that is exposed in the Addresses navigation property is not a collection from the System.Collections namespace, but rather an EntityCollection. The EntityCollection is a completely unique class in the Entity Framework.

The ReferentialConstraint element serves a number of purposes. It specifies the direction of the relationship using the Principal and Dependent role elements. In the example, Address is dependent upon Contact. This also translates to defining the primary key/foreign key relationship, and we finally see the foreign key in the Address table identified: it is the ContactID. This is another piece of the puzzle of how the association and the navigation property work in the conceptual model. The ContactID property doesn't exist anywhere in the CSDL, but it is specified here in the SSDL. The MSL will show us how they are linked.

The last purpose of the ReferentialConstraint element is to stipulate that a row in the Address table cannot exist without a reference to a row in the People table.

If you check back at the CSDL's association in Example 2-2, you will see that this ReferentialConstraint doesn't exist. The CSDL enforces that constraint in a different way. The multiplicity for the Person entity type in that relationship is "1", not "0..1".

Designer's Mapping Details window

Open the model in the XML Editor again and expand the <edmx:Mappings> section; you'll see that there is one big difference in how the mapping is described under the covers. The mapping, as shown in Example 2-4, is being made from the EntitySet, not the actual entity. When you add inherited types into the mix, you may also be mapping Customers who are a type of Contact. When you map the EntitySet you cover all of the entity types in an inheritance hierarchy. Therefore, the mapping needs to be done to the EntitySet, not a specific entity.

The Entity Framework automatically creates a set of classes from the model. These classes are what you will work with when you query the model, and objects will be returned that are based on these classes.

Each time a change is made to the model and the model is then saved, the Entity Framework's code generator kicks in and the classes are re-created.

IQueryable is a LINQ query type. At design time, the compiler recognizes the LINQ query and does its best to tell you its return type. The compiler doesn't realize that because it is a LINQ to Entities query, it will be processed by the Entity Framework and will result in an ObjectQuery. ObjectQuery implements IQueryable, so the two are very closely related.

IQueryable contains metadata about the query, such as the query expression and the provider being used. ObjectQuery is an IQueryable with additional query details that are specific to Entity Framework queries.

The results are described as an "enumerable type," based on the class IEnumerable, which is similar to a Collection. An IEnumerable allows you to enumerate or iterate through each item in the collection as you did in the preceding code sample (i.e., in For Each/foreach). A Collection is an enhanced IEnumerable. Whereas an IEnumerable is read-only, the more familiar Collection class allows you to perform additional actions, such as adding or removing items from the group.

EntityClient: The Lowest-Level Method for Returning Streamed Data Through EDM Queries

//add new entities

//insert new parents and children

You can divide the core functionality of Object Services into seven areas:

• Query processing

• Object materialization

• Object management

• Object relationship management

• Object state management

• Database Manipulation Language (DML) command processing

• Additional features

At a high level, query processing in the Entity Framework involves translating the LINQ or Entity SQL queries into queries that the data store can process. At a lower level, it first parses your query into a command tree of LINQ or Entity SQL query operators and functions, combined with the necessary entities and properties of your model. The command tree is a format that the various providers that have been designed to work with the Entity Framework will be expecting. Next, the provider API (Oracle, SQL Server, MySQL, etc.) transforms this tree into a new expression tree composed of the provider's operators and functions and the database's tables and columns. This tree is finally passed to the database.

LINQ starts its journey in the LINQ APIs and is then passed to the Object Services API. When you create a LINQ to Entities query, you are using syntax that is built into Visual Basic and C# that has enhancements that the Entity Framework has added. LINQ converts this query into a LINQ expression tree, which deconstructs the query into its common operators and functions. The LINQ expression tree is then passed to Object Services, which converts the expression tree to a command tree.

Customizing Entity Data Models

In object-oriented programming, when one object is a type of another object, you can use inheritance to share properties so that the properties of a base type (e.g., Contact) are exposed directly in a derived type (e.g., Customer). The EDM supports inheritance as well. The inheritance mapping used to allow Customer to derive from Contact and absorb Contact's properties is called Table per Type inheritance. Let's investigate this one first, and modify the model to simplify working with customers.

Entity splitting, also referred to as vertical splitting, allows you to map a single entity to more than one table. You can use entity splitting when tables share a common key; for example, if a contact's personal and business information is stored in separate tables. You can use entity splitting as long as the primary keys in the two database tables match.
(Entity splitting can solve this problem very easily, by mapping both the Customer table and the ContactPersonalInfo table to the Customer entity.)

Conditional mapping places a permanent filter on an entity by defining that an entity will be mapped to data in the database under only certain conditions.

Another type of inheritance that the EDM supports is Table per Hierarchy (TPH). TPH inheritance depends on conditional mapping. Rather than including only records that match the condition, the condition is used to define records as different types.

Creating Complex Types to Encapsulate Sets of Properties

QueryView is a mapping that allows you to override the default mapping for an entity set and return read-only data. QueryView is something you need to enter manually in the XML, and it belongs in the mapping layer.

A QueryView is a query that is expressed using Entity SQL syntax. However, rather than creating the Entity SQL expression against the conceptual layer of the model, the target of the expression is the store (SSDL) layer. In other words, when you construct the Entity SQL for a QueryView, the query is written against the elements of the SSDL.

Although QueryView returns read-only entities, if you want to use QueryView for some of its other benefits, you can force the entity to be updatable. Entities that are mapped with QueryView are still change-tracked by the ObjectContext. However, the Entity Framework is not able to automatically generate Insert, Update, and Delete commands for these entities. Instead, you can always create function mappings, as you did for the Payment entity. Then the entity that came from a QueryView will be affected by the call to SaveChanges.

In addition to returning read-only entities, another benefit of QueryView is that you can overcome the limitations of conditional mapping. As you saw earlier, conditional mapping lets you filter using =, Is Null, and Is Not Null.
Using a QueryView you can filter with a much wider variety of operators, including > and <. However, because QueryView returns read-only data, if you need the entity that results to be updatable, you can still achieve this by mapping stored procedures to the entity that results.

http://www.codeproject.com/KB/webservices/Programming_WCF.aspx

The WCF programming model unifies Web Services, .NET Remoting, Distributed Transactions, and Message Queues into a single Service-oriented programming model for distributed computing. WCF uses SOAP messages for communication between two processes, thereby making WCF-based applications interoperable with any other process that communicates via SOAP messages.

A WCF Service is composed of three components parts viz,

1) Service Class - A WCF service class implements some service as a set of methods.

2) Host Environment - A Host environment can be a Console application or a Windows Service or a Windows Forms application or IIS as in case of the normal asmx web service in .NET.

3) Endpoints - All communications with the WCF service will happen via the endpoints. The endpoint is composed of 3 parts (collectively called as ABC's of endpoint) as defines below:

Address: The endpoints specify a Address that defines where the endpoint is hosted.

Contract: The endpoints specify a Contract that defines which methods of the Service class will be accessible via the endpoint; each endpoint may expose a different set of methods.

Binding: The endpoints also define a binding that specifies how a client will communicate with the service and the address where the endpoint is hosted.Various components of the WCF are depicted in the figure below.

Who How What

There are three types of contracts namely,

Service Contracts - Describes the operations a service can perform. Maps CLR types to WSDL.
Data Contracts - Describes a data structure. Maps CLR types to XSD.
Messaga Contracts - Defines the structure of the message on the wire. Maps CLR types to SOAP messages.

Bindings can be defined in config file as well as programattically.

Services have behaviors that control their concurrency, throttling, transactions,
security, and other system semantics.

Metadata in WCF refers to the information that describes precisely how to
communicate with a service. Clients can request metadata from a running
service to learn about their endpoints and the message formats that they

require. At design time, clients send a request message defined by the
WS-MetadataExchange standard and receive WSDL in return. The WSDL
can be used by the client to define a proxy class and configuration file that
will later be used at runtime to communicate with the service. Figure 1.4
shows this interaction.

Use svcutil.exe to generate the proxy code

http://en.wikipedia.org/wiki/Component-oriented_programming

The main idea is separation of concerns;

Software engineers regard components as part of the starting platform for service orientation. Components play this role, for example, in Web Services, and more recently, in Service-Oriented Architecture (SOA) - whereby a component is converted^{[by whom?]} into a service and subsequently inherits further characteristics beyond that of an ordinary component.

An individual component is a software package or a module that encapsulates a set of related functions (or data).

All system processes are placed into separate components so that all of the data and functions inside each component are semantically related (just as with the contents of classes). Because of this principle, it is often said that components are modular and cohesive.

With regard to system-wide co-ordination, components communicate with each other via interfaces. When a component offers services to the rest of the system, it adopts a provided interface which specifies the services that can be utilized by other components and how. This interface can be seen as a signature of the component - the client does not need to know about the inner workings of the component (implementation) in order to make use of it. This principle results in components referred to as encapsulated.

Another important attribute of components is that they are substitutable,

Software components often take the form of objects or collections of objects (from object-oriented programming), in some binary or textual form, adhering to some interface description language (IDL) so that the component may exist autonomously from other components in a computer.

Reusability is an important characteristic of a high-quality software component. A software component should be designed and implemented so that it can be reused in many different programs.

It takes significant effort and awareness to write a software component that is effectively reusable. The component needs to be:

• fully documented
• thoroughly tested
  • robust - with comprehensive input-validity checking
  • able to pass back appropriate error messages or return codes
• designed with an awareness that it will be put to unforeseen uses

Differences from object-oriented programming

Proponents of object-oriented programming (OOP) maintain that software should be written according to a mental model of the actual or imagined objects it represents. OOP and the related disciplines of object-oriented design and object-oriented analysis focus on modeling real-world^{[citation needed]} interactions and attempting to create "verbs" and "nouns" which can be used in intuitive^{[citation needed]} ways, ideally by end users as well as by programmers coding for those end users.

Component-based software engineering, by contrast, makes no such assumptions, and instead states that developers should construct software by gluing together prefabricated components - much like in the fields of electronics or mechanics. Some peers^[who?] will even talk of modularizing systems as software components as a new programming paradigm.

Component-based development (CBD) is an extension of object-oriented programming. CBD does away with the language and vendor-specific limitations of OOP, and makes software reuse more practical and accelerates the development process. Event-based programming is the next logical step in CBD, and makes components more reusable due to their decoupled nature. But event-based systems are easier to develop, which means they are cheaper and more reliable than traditional OOP or CBD systems.

Web 2.0 addresses the new web technologies that are used to bring more interactivity to web
applications.

Additionally, Web 2.0 also includes a behavioral shift on the web, where users are
encouraged to customize their own content on web applications rather than view static/
generic content supplied by an organization.

In addition to the technology and behavior changes, Web 2.0 can also mean the shift
from shrink-wrapped software to software as a service.

Another aspect of Web 2.0 are mash-up and plug-in pages. (Personal google page)

Injection attacks are based on a single problem that persists in many technologies: namely,
no strict separation exists between program instructions and user data (also referred to as
user input). This problem allows for attackers to sneak program instructions into places
where the developer expected only benign data. By sneaking in program instructions, the
attacker can instruct the program to perform actions of the attacker’s choosing.

Input Injection

SQL Injection

SELECT id FROM user_table WHERE username = '' OR 1=1 -- ' AND password
= PASSWORD('x')

Injection attacks are not necessary blind attacks. Many web applications are developed
with open-source tools. To make injection attacks more successful, download free or
evaluation copies of products and set up your own test system. Once you have found an
error in your test system, it is highly probable that the same issue will exist on all web
applications using that tool.

Cure: 1. constrain data types, escape user input, prepared statements (the best)

XPath Injection

//users[username/text()='admin' and password/text()='' or '1'='1' ]/id/text()

Command Injection (Escape)

Directory Transversal Attacks
XXE (XML eXternal Entity) Attacks (prohibit the external entity in XML parser)

LDAP Injection
whitelisting characters—that is, allow
alphanumeric characters (a–z, A–Z, and 0–9) and deny all other characters.
Buffer Overflows
The injection aspect of buffer overflows is that the attacker injects
machine instructions (called shell code) into some user input. The attacker somewhat needs to
know where the shell code will end up in the memory of the computer running the web
application. Then the attacker overwrites the return address to point to the memory location
of the shell code.

                                               Security

HTTP offers integrated mechanisms for authenticating users. Collectively referred to as HTTP authentication, these mechanisms provide a way for users to be authenticated without the necessity of any server-side programming logic. This can be especially helpful for restricting access to static resources (such as images or HTML files). Of course, server-side scripts can also implement HTTP authentication, although Web developers often authenticate users in the application logic itself.

There are two basic types of HTTP authentication:

• Basic authentication

• Digest authentication

An elegant solution to these types of problems is SSL, Secure Sockets Layer. In 1994, Netscape released the specification of Secure Sockets Layer. By 1995, version 3.0 of SSL was released, and it has since taken the Web by storm. SSL has dramatically changed the way people use the Web, and it provides a very good solution to many of the Web's shortcomings, most importantly:

• Data integrity— SSL can help ensure that data (HTTP messages) cannot be changed while in transit.

• Data confidentiality— SSL provides strong cryptographic techniques used to encrypt HTTP messages.

• Identification— SSL can offer reasonable assurance as to the identity of a Web server. It can also be used to validate the identity of a client, but this is less common.

A digital certificate is a document that declares that a particular public key is owned by a particular Web site (see Figure 18.3). The CA's role is very similar to a notary whose responsibility is to ensure the correct identity of people signing a legal document.

SSL is basically a protocol that employs both symmetric and asymmetric cryptography to protect messages that use TCP as the transport-level protocol. Because of the high performance expense of asymmetric cryptography, it is only used to exchange the randomly generated symmetric key that is then used for the symmetric encryption of the HTTP messages.

https on port 443

Whenever a Web browser connects to a Web site over a secure connection, it requires that the SSL certificate the Web server presents meets three main conditions:

• The domain name on the certificate must match the domain name the Web browser believes itself to be requesting a resource from.

• The certificate must be valid (not expired).

• The certificate must be signed by a trusted certificate authority (CA).

Transport Layer Security (TLS) is a formally standardized version of SSL. The biggest difference, in fact, is that TLS is defined and maintained by an international standards body, the Internet Engineering Task Force (IETF). SSL is developed and maintained by Netscape.

One of the advantages of the IETF's involvement in TLS is that they also control the HTTP protocol. This situation can possibly be credited for RFC 2817, which describes a method for using the Upgrade general header to upgrade to HTTP over TLS. The significance of this is that it allows for a change in protocol without having to utilize a separate port. Thus, a Web server that supports this technique can implement TLS over port 80. An example of a Web client's request is the following:

GET / HTTP/1.1 
Host: 127.0.0.1 
Upgrade: TLS/1.0 
Connection: Upgrade 

A Web server that accepts this upgrade will issue an HTTP response similar to the following:

HTTP/1.1 101 Switching Protocols 
Upgrade: TLS/1.0, HTTP/1.1 
Connection: Upgrade 

At this point, a typical SSL handshake will take place over the current connection. It is sometimes confusing to consider that the SSL handshake can take place over port 80 at this point while the Web server can still accept normal HTTP requests over the same port. Note that the upgrade only affects the current TCP connection. Just as a Web server does not (barring extremely odd memory collisions) send the wrong HTTP response to the wrong Web client, it can also keep protocol upgrades straight.