Procedures in SQL Server

Stored Procedures:
A stored procedure is a piece of programming code that can accept input parameters and can return one or more output parameters to the calling procedure or batch.
They can also return status information to the calling procedure to indicate whether they succeeded or failed.
SQL Server stored procedures provide excellent security for your database. You can grant rights to the stored procedure without granting rights to the underlying objects.
The SET NOCOUNT statement, when set to ON, eliminates the xxrow(s) affected message in the SQL Express Manager window. It also eliminates the DONE_IN_PROC communicated from SQL Server to the client application. For this reason the SET NOCOUNT ON statement, when included, improves the performance of the stored procedure.

Using the @@ Functions

Developers often refer to the @@ functions as global variables. In fact, they don’t really behave like variables. You cannot assign values to them or work with them as you would work with normal variables. Instead they behave as functions that return various types of information about what is going on in SQL Server.

The @@TranCount function is applicable when you are using explicit transactions. Transactions are covered later in this chapter. The BEGIN TRAN statement sets the @@TranCount to one. Each ROLLBACK TRAN statement decrements @@TranCount by one. The COMMIT TRAN statement also decrements @@TranCount by one. When you use nested transactions @@TranCount helps you to keep track of how many transactions are still pending.

The @@Identity function retrieves the new value inserted into a table that has an identity column.

The @@Error function returns the number of any error that occurred in the statement immediately preceding it.

When you build a stored procedure, a query plan is created. This query plan contains the most efficient method of executing the stored procedure given available indexes and so on.

SQL Server 2005

System Versus User Objects

System databases include Master, Model, MSDB, Resource, TempDB, and Distribution. SQL Server creates these databases during the installation process.

In addition to system databases, there are also system tables, stored procedures, functions, and other system objects.

Whereas system objects are part of the SQL Server system, you create user objects. User objects include the databases, stored procedures, functions, and other database objects that you build.

Each column or set of columns in a table that contains unique values is considered a candidate key. One candidate key becomes the primary key. The remaining candidate keys become alternate keys. A primary key made up of one column is considered a simple key. A primary key comprising multiple columns is considered a composite key.

A domain is a pool of values from which columns are drawn. A simple example of a domain is the specific data range of employee hire dates. In the case of the Order table, the domain of the CustomerID column is the range of values for the CustomerID in the Customers table.

Normalization

Normalization is the process of applying a series of rules to ensure that your database achieves optimal structure. Normal forms are a progression of these rules. Each successive normal form achieves a better database design than the previous form did.

To achieve first normal form, all columns in a table must be atomic. This means, for example, that you cannot store the first name and last name in the same field. The reason for this rule is that data becomes very difficult to manipulate and retrieve if multiple values are stored in a single field.

Second Normal Form

To achieve second normal form, all nonkey columns must be fully dependent on the primary key. In other words, each table must store data about only one subject.

Third Normal Form

To attain third normal form, a table must meet all the requirements for first and second normal form, and all nonkey columns must be mutually independent. This means that you must eliminate any calculations, and you must break out data into lookup tables.

Although the developer’s goal is normalization, many times it makes sense to deviate from normal forms. We refer to this process as denormalization. The primary reason for applying denormalization is to enhance performance.

If you decide to denormalize, document your decision. Make sure that you make the necessary application adjustments to ensure that the system properly maintains denormalized fields. Finally, test to ensure that performance is actually improved by the denormalization process.

Integrity Rules

Although integrity rules are not part of normal forms, they are definitely part of the database design process. Integrity rules are broken into two categories. They include overall integrity rules and database-specific integrity rules.

Overall Rules

The two types of overall integrity rules are referential integrity rules and entity integrity rules. Referential integrity rules dictate that a database does not contain any orphan foreign key values. This means that

• Child rows cannot be added for parent rows that do not exist. In other words, an order cannot be added for a nonexistent customer.
• A primary key value cannot be modified if the value is used as a foreign key in a child table. This means that a CustomerID cannot be changed if the orders table contains rows with that CustomerID
• A parent row cannot be deleted if child rows are found with that foreign key value. For example, a customer cannot be deleted if the customer has orders in the orders table

SQL Server has two wonderful features. One is called Cascade Update, and the other is called Cascade Delete. These features make it easier for you to work with data, while ensuring that referential integrity is maintained. With the Cascade Update feature, SQL Server Express automatically updates the foreign key field on the child rows when the primary key of the parent is modified. This allows the system to modify a primary key while maintaining referential integrity. Likewise, the Cascade Delete feature deletes the associated child rows when the parent rows are deleted, once again maintaining referential integrity.

Entity integrity dictates that the primary key value cannot be null. This rule applies not only to single-column primary keys, but also to multicolumn primary keys. In fact, in a multicolumn primary key, no field in the primary key can be null. This makes sense because if any part of the primary key can be null, the primary key can no longer act as a unique identifier for the row. Fortunately, SQL Server does not allow a field in a primary key to be null.

Database-Specific Rules

The other set of rules applied to a database are not applicable to all databases, but, instead, are dictated by business rules that apply to a specific application. Database-specific rules are as important as overall integrity rules. They ensure that the user enters only valid data into a database. An example of a database-specific integrity rule is that the delivery date for an order must fall after the order date.

A view is a virtual table. Its contents are based on a query. Like a table, a view is composed of rows and columns. Except in the case of a special type of view called an indexed view, views exist only in memory.

A stored procedure is a piece of programming code that can accept input parameters and can return one or more output parameters to the calling procedure or batch (see Figure 1.9). Stored procedures generally perform operations on the database, including the process of calling other stored procedures. They can also return status information to the calling procedure to indicate whether they succeeded or failed.

IIS Authentication

http://msdn.microsoft.com/en-us/library/aa292118%28v=vs.71%29.aspx

An important part of many distributed applications is the ability to identify someone, known as a principal or client, and to control the client's access to resources. Authentication is the act of validating a client's identity. Generally, clients must present some form of evidence, known as credentials, proving who they are for authentication.
IIS provides a variety of authentication schemes:

• Anonymous (enabled by default)
• Basic
• Digest
• Integrated Windows authentication (enabled by default)
• Client Certificate Mapping

Regardless of which method you choose, after IIS authenticates the client it will pass a security token to ASP.NET. If you configure ASP.NET authentication to use Windows authentication and you enable impersonation, ASP.NET will impersonate the user represented by this security token.

Anonymous

Anonymous authentication gives users access to the public areas of your Web site without prompting them for a user name or password. Although listed as an authentication scheme, it is not technically performing any client authentication because the client is not required to supply any credentials. Instead, IIS provides stored credentials to Windows using a special user account, IUSR_machinename. By default, IIS controls the password for this account. Whether or not IIS controls the password affects the permissions the anonymous user has. When IIS controls the password, a subauthentication DLL (iissuba.dll) authenticates the user using a network logon. The function of this DLL is to validate the password supplied by IIS and to inform Windows that the password is valid, thereby authenticating the client. However, it does not actually provide a password to Windows. When IIS does not control the password, IIS calls the LogonUser() API in Windows and provides the account name, password and domain name to log on the user using a local logon. After the logon, IIS caches the security token and impersonates the account. A local logon makes it possible for the anonymous user to access network resources, whereas a network logon does not.

EWS Managed API

The Microsoft Exchange Web Services (EWS) Managed API 1.1 provides an intuitive managed API for developing client and server applications that leverage Exchange 2010 data and business logic, whether Exchange is running on premise or in the cloud. The EWS Managed API 1.1 makes Exchange Web Services SOAP calls under the covers, so many environments are already configured for EWS Managed API 1.1.

System Requirements
You must have the following items to complete this lab:

• Microsoft Visual Studio 2010
• Exchange Web Services Managed API 1.1 SDK
• Two accounts configured to have Microsoft Exchange mailboxes, referred to as the primary and secondary lab users in this lab.

http://msdn.microsoft.com/en-us/library/exchange/dd633710%28v=exchg.80%29

What do you need in order to use the EWS Managed API?

To use the EWS Managed API, you need to have the following:

• The EWS Managed API, which you can download from the Microsoft Download Center. The EWS Managed API works with all versions of Exchange starting with Exchange 2007 SP1.
  
• A mailbox on an Exchange server that is running Exchange 2007 SP1 or a later version, or Exchange Online Preview. You must have the user name and credentials of the account. By default, direct EWS access is enabled for all Exchange Online Preview plans except for the Kiosk plan.
  
• The .NET Framework version 3.5 or later. Versions of the EWS Managed API starting with the EWS Managed API 2.0 Beta 2 require the .NET Framework 4.
  
• Familiarity with web services and managed programming.

Architecture
Exchange Web Services is deployed with the Client Access server role. Microsoft Exchange Server 2010 clients connect to the computer that is running Exchange 2010 that has the Client Access server role installed in an Active Directory directory service site by using an HTTPS connection. If the target mailbox is in another Active Directory site, the source Client Access server creates an HTTPS connection to the target Client Access server. The target Client Access server obtains the information by communicating over MAPI to the Exchange server that has the Mailbox server role installed and then sends it back to the source Client Access server. If the target mailbox is in the same Active Directory site, the Client Access server uses MAPI to communicate with the Mailbox server to obtain the information. The Client Access server then provides the data back to the client.

Windows Power Shell Jump Start

http://technet.microsoft.com/en-us/library/bb978526.aspx

http://www.sqlmag.com/content1/topic/accessing-sql-server-data-from-powershell-part-2/catpath/net-framework2/page/4

Windows PowerShell® is a task-based command-line shell and scripting language designed especially for system administration. Built on the .NET Framework, Windows PowerShell helps IT professionals and power users control and automate the administration of the Windows operating system and applications that run on Windows.

Windows PowerShell is built on top of the .NET Framework common language runtime (CLR) and the .NET Framework, and accepts and returns .NET Framework objects. This fundamental change in the environment brings entirely new tools and methods to the management and configuration of Windows.

Windows PowerShell is very different.

• Windows PowerShell does not process text. Instead, it processes objects based on the .NET Framework platform.
  
  
• Windows PowerShell comes with a large set of built-in commands with a consistent interface.
  
  
• All shell commands use the same command parser, instead of different parsers for each tool. This makes it much easier to learn how to use each command.
  
  

Best of all, you do not have to give up the tools that you have become accustomed to using. You can still use the traditional Windows tools, such as Net, SC, and Reg.exe in Windows PowerShell.

A cmdlet (pronounced "command-let") is a single-feature command that manipulates objects in Windows PowerShell. You can recognize cmdlets by their name format -- a verb and noun separated by a dash (-), such as Get-Help, Get-Process, and Start-Service.

Each cmdlet has a help file that you can access by typing:

get-help <cmdlet-name> -detailed

Windows PowerShell provides a new architecture that is based on objects, rather than text. The cmdlet that receives an object can act directly on its properties and methods without any conversion or manipulation. Users can refer to properties and methods of the object by name, rather than calculating the position of the data in the output.

Windows PowerShell provides a complete interactive environment. When you type a command or expression at the Windows PowerShell command prompt, the command or expression is processed immediately and the output is returned to the prompt.

This is true for all command types, including cmdlets, aliases, functions, CIM commands, workflows, and executable files.

You can also send the output of a command to a file or printer, or you can use the pipeline operator (|) to send the output to another command.

What is a script?

A script is text file that contains one or more Windows PowerShell commands or expressions. When you run the script, the commands and expressions in the script file run, just as if you typed them at the command line.
Typically, you write a script to save command sequence that you use frequently or to share a command sequence with others.

http://www.sqlmag.com/article/windows-powershell/powershell-scripting

http://technet.microsoft.com/en-us/library/ff730939.aspx

This Keyword

This Keyword is used To inform the compiler that you wish to

set the current object’s

name data field to the incoming name parameter, simply use this to resolve

the ambiguity:

public void SetDriverName(string name)

{ this.name = name; }

Chaining Constructor Calls Using this

Another use of the

this keyword is to design a class using a technique termed constructor chaining.

This design pattern is helpful when you have a class that defines multiple constructors. Given the

fact that constructors often validate the incoming arguments to enforce various business rules, it

can be quite common to find redundant validation logic within a class’s constructor set.

A cleaner approach is to designate the constructor that takes the

greatest number of arguments

as the “master constructor” and have its implementation perform the required validation logic. The

remaining constructors can make use of the

this keyword to forward the incoming arguments to

the master constructor and provide any additional parameters as necessary. In this way, we only

need to worry about maintaining a single constructor for the entire class, while the remaining constructors

are basically empty.

Here is the final iteration of the

Motorcycle class (with one additional constructor for the sake

of illustration). When chaining constructors, note how the

this keyword is “dangling” off the constructor’s

declaration (via a colon operator) outside the scope of the constructor itself:

class Motorcycle

{

public int driverIntensity;

public string driverName;

// Constructor chaining.

public Motorcycle() {}

public Motorcycle(int intensity)

: this(intensity, "") {}

public Motorcycle(string name)

: this(0, name) {}

// This is the 'master' constructor that does all the real work.

public Motorcycle(int intensity, string name)

{

if (intensity > 10)

{

intensity = 10;

}

driverIntensity = intensity;

driverName = name;

}

...

}

Observing Constructor Flow

On a final note, do know that once a constructor passes arguments to the designated master constructor

(and that constructor has processed the data), the constructor invoked originally by the

caller will finish executing any remaining code statements.

Constructors

C# supports the use of

class constructors

, which allow the state of an object to be

established at the time of creation. A constructor is a special method of a class that is called indirectly

when creating an object using the

new keyword. However, unlike a “normal” method,

constructors never have a return value (not even

void) and are always named identically to theclass they are constructing.

Every C# class is provided with a freebee

default constructor that you may redefine if need be. By

definition, a default constructor never takes arguments. Beyond allocating the new object into

memory, the default constructor ensures that all field data is set to an appropriate default value

Defining Custom Constructors

Typically, classes define additional constructors beyond the default. In doing so, you provide the

object user with a simple and consistent way to initialize the state of an object directly at the time

of creation

However, as soon as you define a custom constructor, the default constructor is

silently removed

from the class and is no longer available! Think of it this way: if you do not define a custom constructor,

the C# compiler grants you a default in order to allow the object user to allocate an

instance of your type with field data set to the correct default values. However, when you define

a unique constructor, the compiler assumes you have taken matters into your own hands.