Lab by sRNcOz

VIEWS: 9 PAGES: 57

									Hands-On Lab
How to build your first Web Application with
SQL Server and ASP.NET

Lab version:    1.1
Last updated:   11/10/2011
Contents

OVERVIEW ................................................................................................................................................... 3

EXERCISE 1: CONNECTING TO A SQL SERVER DATABASE ............................................................... 7
       Task 1 − Connecting and Disconnecting from the SQL Server ............................................................ 10

EXERCISE 2: READING DATA FROM A SQL SERVER DATABASE .................................................... 16
       Task 1 – Creating a Data Access Class ................................................................................................. 18
       Task 2 – Binding the Data to the UI .................................................................................................... 22
       Task 3 – Adding a Handler to Display the Product Images ................................................................. 26
   Exercise 2: Verification ........................................................................................................................................... 27

EXERCISE 3: INSERTING DATA .............................................................................................................. 28
       Task 1 – Adding the Insert Method to the Data Access Class ............................................................. 29
       Task 2 – Binding the Data to the UI .................................................................................................... 33
   Exercise 3: Verification ........................................................................................................................................... 41

EXERCISE 4: UPDATING DATA ............................................................................................................... 43
       Task 1 – Adding the Update Method to the Data Access Class .......................................................... 44
       Task 2 – Binding the Data to the UI .................................................................................................... 46
   Exercise 4: Verification ........................................................................................................................................... 49

EXERCISE 5: DELETING DATA................................................................................................................ 52
       Task 1 – Adding the Delete Method to the Data Access Class............................................................ 52
       Task 2 – Binding the Data to the UI .................................................................................................... 53
   Exercise 5: Verification ........................................................................................................................................... 55

SUMMARY .................................................................................................................................................. 56
Overview
If you want to learn the basics of how to build web applications with the Microsoft Web Platform, this
hands-on lab is the perfect starting point because it is full of practical information and valuable best
practices. If you complete all of the exercises, you will learn how to build a functional ASP.NET web
application with Microsoft Visual Studio that uses Microsoft SQL Server to manage product data and
images. If you are new to the Microsoft Web Platform, this overview section provides some helpful
introductory information to get you started.
More than just a powerful set of tools, servers and technologies, the Microsoft Web Platform offers a
complete ecosystem for building and hosting web sites, services and applications. Whether you are
looking to build your company’s web presence or launch the next MySpace, we have got the products
you need to be successful. The Microsoft Web platform is trusted by the Fortune 500, used by millions
of individuals and built to work for you. This hands-on lab will introduce you to a few important
components of the Microsoft Web Platform, including Microsoft SQL Server, the Microsoft .NET
Framework, ASP.NET and Visual Studio. Check out the Web Platform Installer which makes it easy to
find and install everything you need to start building applications, including free editions of SQL Server
and Visual Studio. If you want to learn more about the Microsoft Web Platform or the Web Platform
Installer, check out the official website at http://www.microsoft.com/web.
Microsoft SQL Server is a family of database products, tools and services used to manage and analyze
data of all shapes and sizes. Developers use SQL Server to create everything from small desktop
databases, to data-driven web applications, to large enterprise-grade databases. SQL Server supports an
ANSI standard Structured Query Language syntax for working with data known as Transact-SQL. The
four basic Transact-SQL commands for manipulating data are SELECT for retrieving data, INSERT to add
new data, UPDATE to change exiting data and DELETE to remove data. You can send these commands
directly from an application, or you can create SQL Server stored procedures that perform database
operations and execute them from your application. If you are new to SQL Server, check out the official
SQL Server website at http://www.microsoft.com/sql.
The Microsoft .NET Framework is both a flexible runtime environment and a collection of class libraries
and services for building applications. .NET supports a variety of popular programming languages
including C# and Visual Basic. In this hands-on lab, you will use the C# language to program a web
application. If you are new to the .NET Framework, check out the .NET Framework Developer Center on
MSDN at http://msdn.microsoft.com/netframework.
The .NET Framework includes a set of services known as ASP.NET to create everything from small,
personal websites to large enterprise-grade web applications. ASP.NET provides useful web controls for
binding data to the user interface of a web application, including the ListView and DetailsView controls
which you will use in this lab. If you are new to ASP.NET, check out the official ASP.NET website at
http://www.asp.net.
Most .NET applications need to access data stored in a database. The .NET Framework includes
System.Data, also known as ADO.NET, for data programmability.
ADO.NET provides a comprehensive set of class libraries, services and providers for working with data in
an application. Some important class libraries for working directly with SQL Server data include
SqlConnection, SqlCommand and SqlDataReader in System.Data.SqlClient, which is the ADO.NET data
provider for SQL Server. Applications use SqlConnection to establish a connection with SQL Server, then
SqlCommand to send Transact-SQL commands using the connection. If the command executes
successfully, results are returned to the application and can be read directly with SqlDataReader. If you
are new to ADO.NET, check out the Data Platform Developer Center on MSDN at
http://msdn.microsoft.com/data.
While the .NET Framework includes its own SDK and compiler, most .NET developers prefer to use the
Microsoft Visual Studio family of products to develop their .NET and SQL Server applications. Visual
Studio makes developers more productive thanks to breakthrough features like IntelliSense and
refactoring. If you are new to Microsoft Visual Studio, check out the Visual Studio developer center on
MSDN at http://msdn.microsoft.com/vstudio.


Objectives
In this Hands-on Lab, you will learn how to:
       Create your first web application using ASP.NET and SQL Server.

       Design a class in C# that makes it easy to work with SQL Server data using a business object.
       Bind data from SQL Server to ASP.NET web server controls using a business object.

       Display images on web pages that are stored in SQL Server.
       Build a SQL Server connection string and use it to connect to SQL Server using the ADO.NET
        SqlConnection class.
       Construct parameterized Transact-SQL statements in an application and execute them using the
        ADO.NET SqlCommand class.

       Read query results returned by SQL Server using the ADO.NET SqlDataReader class.
       Handle database null values from SQL Server in an ASP.NET application.
       Automatically generate unique primary keys using SQL Server IDENTITY values.

       Use transactions to ensure that data modifications are performed in a consistent fashion.
       Use optimistic concurrency to avoid lost updates.


System Requirements
You must have the following items to complete this lab:
       Microsoft Visual Web Developer 2008 (Express Edition) or any edition of Microsoft Visual Studio
        2008 with Visual Web Developer
       Microsoft SQL Server 2008 R2 Express or any edition of Microsoft SQL Server 2008 R2

       SQL Server AdventureWorks2008R2 sample database


Setup
All the requisites for this lab are verified using the Configuration Wizard. To make sure that everything is
correctly configured, follow these steps.

 Note: To perform the setup steps you need to run the scripts in a command window with
 administrator privileges.



    1. Launch the Configuration Wizard for this Lab by running the Setup.cmd script located under the
       Setup folder in the Source folder of this lab. Install any pre-requisites that are missing
       (rescanning if necessary) and complete the wizard.

         Note: For convenience, much of the code you will be managing along this lab is available as
         Visual Studio code snippets. The Setup.cmd file launches the Visual Studio installer file that
         installs the code snippets.
         The Configuration Wizard also creates the 'SQLServerTrainingKitAlias' sever alias used to
         establish a connection with the database server.




        Watch Out – Windows Server 2003 Users
 If you receive an error when installing the AdventureWorks2008R2 sample database, switching the
 SQL Server service to run using the LocalService account can solve your problem. To do this, click Start,
 point to Administrative Tools, and then click Services. Locate the SQL Server service and double-click
 it to open the Properties sheet. Go to the Log On tab, and click the Browse button. Type LOCAL
 SERVICE in the textbox, click Check Names, and then click OK. Finally, click OK in the Properties
 window to accept the changes. After this, right-click the service and then click Restart.




Exercises
The following exercises make up this Hands-on Lab:
    1. Connecting to a SQL Server database
    2. Reading data from a SQL Server database
    3. Inserting data
    4. Updating data
    5. Deleting data


 Note: Each exercise is accompanied by a starting or “begin” solution. These solutions are missing some
 code sections that are completed through each exercise and therefore will not work if running them
 directly.
 Inside each exercise, you will also find an end folder where you find the completed solution. You can
 use this solution as a guide if you need additional help working through the exercises.



Estimated time to complete this lab: 90 minutes.
Exercise 1: Connecting to a SQL Server
Database
In this exercise you will learn how connect to a SQL Server 2008 R2 database from an ASP.NET
application using ADO.NET, how to perform a simple query and how to display the results on a web
page.
This lab makes use of connection strings. If you are new to SQL Server, you should understand a few
things about connection strings before we get started. Here is the connection string you will be using for
this Hands-on Lab:
XML
  <connectionStrings>
    <add name="AdventureWorks" connectionString="Data
Source=SQLServerTrainingKitAlias;Integrated Security=True;Initial
Catalog=AdventureWorks2008R2"/>
  </connectionStrings>



The simple SQL Server connection string shown here has a three different attributes. Let’s examine each
of them in more detail starting with Data Source.
Normally the Data Source attribute is set to a string known as an instance, which in its simplest form is
known as a default instance. A default instance simply specifies the name of the computer where SQL
Server is installed, such as “MYSERVER”. You can also use “(local)” if the SQL Server resides on the same
server as your application, a fully qualified domain name “myserver.mycompany.com”, or you can
specify a TCP/IP address and port number.
Sometimes it is useful to have multiple SQL Server installations on the same server. To accomplish this,
SQL Server setup supports the ability to install a single default instance and additional named instances.
A named instance takes the form “MYSERVER\MYINSTANCE”. You can find out the names of all of the
SQL Server instances installed using the SQL Server Configuration Manager tool.
In this lab, the Data Source is set to a server alias (‘SqlServerTrainingKitAlias’). A server alias provides a
handy way to reference a SQL Server instance from multiple applications so it can be updated in one
place without having to modify application source code. You create and edit a server alias using the SQL
Server Configuration Manager tool. That wraps up our discussion of the Data Source attribute.
The Integrated Security attribute tells ADO.NET what type of authentication to use to connect to SQL
Server. When set to True, ADO.NET will attempt to use Windows Authentication to connect to SQL
Server. Windows Authentication makes it easy to use existing security accounts defined in Windows or
Active Directory instead of creating duplicate security accounts in the database. An added benefit is that
you do not have to include security credentials in your connection string.
In order to successfully connect to SQL Server using Windows Authentication, an administrator must
have previously granted connection privileges to the Windows security account using the Transact-SQL
CREATE LOGIN command or using a tool like SQL Server Management Studio. This is analogous to an
administrator granting privileges to access other resources like a file, directory or share. SQL Server
setup requires the user who is performing the installation to create a login for at least one Windows
security account. Setup makes this login a member of the server-level sysadmin role. For this hands-on
lab it is assumed that that you installed SQL Server and therefore your Windows security account has
connection and sysadmin privileges. Members of sysadmin can perform any activity on the SQL Server
instance, so be careful!
When the Integrated Security attribute is set to False, ADO.NET will attempt to use SQL Server
Authentication to connect. SQL Server Authentication is useful when you do not have access to
Windows or Active Directory security credentials, or if Windows Authentication is not supported in your
environment. SQL Server Authentication must be enabled on the instance you are trying to connect to. It
also requires that you specify two additional attributes in your connection string: “User ID” and
“Password”. If you have to use SQL Server authentication, encrypt your connection strings to protect
them.
In order to successfully connect to SQL Server using SQL Server Authentication, an administrator must
have previously granted connection privileges using the Transact-SQL CREATE LOGIN command or using
a tool like SQL Server Management Studio. Here’s an example of a connection string that uses SQL
Server authentication:
XML
  <connectionStrings>
    <add name="AdventureWorks" connectionString="Data
Source=SQLServerTrainingKitAlias;Integrated Security=False;Initial Catalog=
AdventureWorks2008R2;User Id=userName;Password=userPassword"/>
  </connectionStrings>



 Best Practice: Use Windows Authentication to connect to SQL Server if your environment supports
 the use of Windows or Active Directory security credentials, otherwise use SQL Server authentication.
 If you use SQL Server Authentication, encrypt your connection strings to protect them.



Let us move on and talk about the Initial Catalog attribute. SQL Server not only supports multiple
instances per server, but it also supports multiple databases per instance. This lab uses a database
named “AdventureWorks2008R2”. Specifying an Initial Catalog in the connection string automatically
switches the context of the current database when the application connects to ensure you are accessing
the correct data, so it is important that you set it correctly to avoid making changes in the wrong
database (like the master system database for example).
 Best Practice: When constructing a connection string, always specify the Initial Catalog attribute to
 ensure your application is in the correct database context. Do not rely on default database settings.



Because each database may have different security and access requirements, SQL Server supports a
robust security architecture based upon security principals. There are three layers of principals,
Windows-level principals, SQL Server-level principals and database-level principals, and each of them
can be secured separately. So far we have only dealt with access control for Windows-level and SQL
Server-level principals. These principals will get you connected to SQL Server, but you will need to
understand and properly configure database-level principals to actually work with data.
The most common database-level principle is known as a database user. Database users are created in
specific databases using the Transact-SQL CREATE USER command or a tool like SQL Server
Management Studio. Each database user is associated with a server-level principal. In order to access or
modify data, the database user performing the operation must have been granted privileges to do so
using the Transact-SQL GRANT command or a tool like SQL Server Management Studio. Those privileges
can be granted directly to the database user or to a database role that the database user is a member
of. Examples of privileges include SELECT, UPDATE, INSERT, DELETE and EXECUTE (for running stored
procedures).
Note that some server-level principles, such as those that belong to the server-level sysadmin role, are
automatically associated with a high-privileged database user known as dbo. This is a special user in all
SQL Server databases known as the “database owner”. The database owner can perform all actions in a
particular database because it is a member of the db_owner database-level role. Since this lab assumes
your Windows security account is a member of sysadmin, we are also assuming you can access the
AdventureWorks2008R2 database and perform operations in it as the database owner.

 Best Practice: When accessing data in SQL Server, production applications should not use server-level
 principals that are members of the sysadmin server role (like sa), or database-level principles that are
 members of the db_owner role (like dbo). Designing a secure application requires the developer to
 employ the principle of least privilege. To implement this, create database roles that mirror the
 different types of users in your application, and grant only the privileges that are required to those
 roles.



Developers have the option of configuring their applications to connect to the database using the
security credentials of the user, or alternatively using security credentials associated with the
application (which in ASP.NET is known as an application identity). Using the user’s security credentials
provides more flexibility in controlling access to data using built-in SQL Server functionality in an
application-independent fashion, while using an application identity requires you to implement most
data access control yourself in application logic, which is fine if only one application will access the data.
One final note on connection strings. The format of connection strings can be unforgiving, and may vary
from provider to provider. If you would like to construct your connection string using a higher-fidelity
approach, check out the SqlServerConnectionStringBuilder class of ADO.NET.
This wraps up our discussion of connection strings. Now it is time to start the hands-on lab!
Task 1 − Connecting and Disconnecting from the SQL Server
    1. Start Microsoft Visual Web Developer 2008 Express Edition or Microsoft Visual Studio 2008
       from Start | All Programs.
    2. Create a new ASP.NET Web Application. To do this, point to New on the File menu, and then
       click Project. In the Installed Templates list, click Web in the Visual C# node. In the Templates
       pane, click ASP.NET Web Application and make sure that .NET Framework 3.5 is selected as the
       target runtime. Finally, set the project's name to ConnectingToSqlServer, set the name of the
       solution to Begin, select the Create directory for solution option, set the location to Ex1-
       ConnectingToSqlServer in the Source folder of this lab, and then click OK.


                 Watch Out – Launching the Shell Version of Visual Studio 2008
         We often refer to Visual Studio 2008 in a generic way in this Hands-on Lab, but there are many
         flavors of Visual Studio 2008.
         If you do not see an option for creating a new Web project, you may have launched the wrong
         flavor of Visual Studio 2008. SQL Server uses the Visual Studio shell for some of its tools, and
         depending upon the configuration of your system, the Visual Studio shell may not support all
         project types.
         To resolve this problem simply point to Exit on the File menu to close the Visual Studio. Find
         the right version of Visual Studio in Start | All Programs. This time, launch Microsoft Visual
         Web Developer 2008 Express Edition, and you will be able to work with web projects.
   Figure 1
   Creating a new ASP.NET Web Application named “ConnectingToSqlServer”


3. Add some formatting styles to the web application using a cascading style sheet by adding the
   style.css file to the solution. To do this, right-click the ConnectingToSqlServer project, point to
   Add, and click Existing Item. Browse to Assets in the Source folder of this lab and select the
   style.css file.
4. Reference the style sheet in the web application to make use of it. To do this, add the following
   bolded lines containing the <meta> and <link> elements in the Default.aspx page. This page is
   shown after creating the solution.


   HTML
   <head>
       <title></title>
       <meta content="text/html; charset=utf-8" http-equiv="content-type" />
       <link href="styles.css" rel="stylesheet" type="text/css" />
   </head>



5. Create a simple web form designed to connect to SQL Server and execute a query. To do this,
   paste the following code (shown in bold) between the <body> tags replacing the original code.
   HTML
   <body>
       <form id="Form1" runat="server">
       <blockquote>
           <p>
                <b>Query statement executed:</b><br />
                <asp:Label runat="server" ID="QueryExecutedLabel" Text="Query not
   executed" />
           </p>
       </blockquote>
       <asp:Button ID="ConnectButton" runat="server" Text="Connect"
   OnClick="ConnectButtonClick" />
       <br />
       <p>
           <span class="userlabel"><b>AdventureWorks CEO</b>:&nbsp;<asp:Label
   ID="ResultLabel" runat="server">Not connected</asp:Label></span>
       </p>
       </form>
   </body>



   The preceding code adds a web form to the page which contains a block where the executed
   query is shown along with a button to connect to SQL Server that runs a simple query. The result
   of this query will be shown in a label.

     Note: The ConnectButton button has a method named ConnectButtonClick associated to its
     OnClick event. This method will be created later in this exercise.



6. Next, we need to configure the connection string. Open the Web.Config file by double-clicking it
   in the Solution Explorer.
7. Add the connection string for the SQL Server database. To do this, locate the
   <connectionStrings> tag and replace it with the following code.
   XML
     <connectionStrings>
       <add name="AdventureWorks" connectionString="Data
   Source=SQLServerTrainingKitAlias;Integrated Security=True;Initial
   Catalog=AdventureWorks2008R2"/>
     </connectionStrings>



8. Open the code-behind file of the Default.aspx page. To do this, select the Default.aspx page in
   the Solution Explorer and press F7.
9. Add the following using statements at the top of the file. We will need the
   System.Configuration namespace to work with connection strings, and the
   System.Data.SqlClient namespace to connect to SQL Server using ADO.NET.
   C#
   using System.Configuration;
   using System.Data.SqlClient;



10. Create a protected method named ConnectButtonClick to handle the click event of the connect
    button. To do this, paste the following code inside the pages Default class.
   (Code Snippet – First SQL ASP.NET Application Lab – Ex01 – Connect and run query)
   C#
   protected void ConnectButtonClick(object sender, EventArgs e)
   {
       using (SqlConnection connection = new
   SqlConnection(ConfigurationManager.ConnectionStrings["AdventureWorks"].Connect
   ionString))
       {
           connection.Open();

             var businessEntityId = 1;

           var commandText = "SELECT [FirstName] + ' ' + [MiddleName] + ' ' +
   [LastName] as FullName " +
                   "FROM [AdventureWorks2008R2].[Person].[Person] WHERE
   [BusinessEntityID] = @BusinessEntityId";
           var command = new SqlCommand(commandText, connection);
           command.Parameters.AddWithValue("@BusinessEntityId",
   businessEntityId);

             var result = command.ExecuteScalar() as string;

             this.ResultLabel.Text = result;
             this.QueryExecutedLabel.Text = commandText;
        }
   }



   In the preceding code, a variable is declared and initialized to the primary key for the row we
   want to retrieve. This value could also be accepted as input from the user. Next, the connection
   is opened in the scope of a using statement, which ensures that the connection will be closed
   automatically when the code in the body of the using statement completes.
 Best Practice: When opening connections in C#, do so in the scope of a using statement to
 ensure that the connection is properly disposed of. The failure to dispose of connections could
 result in running out of available connections and runtime errors.



Next, the commandText variable is initialized with the Transact-SQL command to be sent to SQL
Server. The command in this case is a SELECT statement that returns a single calculated column
based upon an expression which concatenates the three name columns into a new calculated
column named FulName. The SELECT statement uses a WHERE clause to limit the result set to a
single row using a search argument that references the primary key of the table.

 Best Practice: When writing a Transact-SQL SELECT statement, always constrain the results
 using a WHERE clause to avoid returning data to your application that will not be used. Tables
 can get very large, and processing result sets requires memory and CPU resources both in SQL
 Server and your application.



 Best Practice: When designing your database, make sure to create a primary key for each
 table in your database. A primary key is a single column or combination of columns that can be
 used to uniquely identify each row in your table. Primary keys are implemented using a
 PRIMARY KEY constraint defined when a table is created or when adding an index to your
 table. SQL Server’s query optimizer can produce more efficient execution plans on tables that
 have primary keys.



 Best Practice: Wen designing your database, consider creating an index on SQL Server columns
 that are frequently referenced in the WHERE clause of SELECT statements. This may yield a
 tremendous performance improvement depending upon the size of your table, the
 distribution of data in your column and the frequency of updates. The Database Engine Tuning
 Advisor tool can provide recommendations on which columns should be indexed and the
 types of indexes to create.



Notice the use of the named parameter @BusinessEntityId in the SELECT statement. This tells
the ADO.NET SQL Server data provider to substitute the named parameter with a value provided
by your application at execution time.
Next, a SqlCommand object is created by passing the connection and command text. A
parameter is added to the SqlCommand object for each named parameter used in the command
text. In this case, we add a single parameter for @BusinessEntityId and initialize it to the value
of the businessEntityId variable.
     Best Practice: Use of parameterized commands is recommended for security. Hackers can use
     an approach called SQL injection to insert malicious SQL statements into Transact-SQL
     commands. To guard against SQL injection, never directly execute text entered by a user as a
     Transact-SQL command. Instead, only use pre-defined Transact-SQL commands or stored
     procedures and substitute data values provided by the user using parameters.



     Best Practice: Use of parameterized commands is also recommended for performance. SQL
     Server uses a sophisticated cost-based query optimizer to determine the most efficient
     execution plan for commands at run time. Using parameterized queries helps SQL Server re-
     use these plans from execution to execution which can save query optimization CPU cycles.



   It is time now to execute the command. In this case the ExecuteScalar method is invoked, which
   returns a single column of a single row.

     Best Practice: When retrieving only a single value from SQL Server, use ExecuteScalar. This is
     more efficient than using the ExecuteReader.



   Finally, the results of the query are displayed using a label.
11. Press CTRL+F5 to run the solution.
12. Click the Connect button to retrieve the user login.
        Figure 2
        Retrieving the Company CEO from the SQL Server


    13. Close the browser.



Exercise 2: Reading data from a SQL
Server database
In this exercise you will learn how to read data directly from SQL Server and display the results in an
ASP.NET page. You will also learn how to execute a query that retrieves an image from the server and
how to create a handler to display those images.
Before we get started, this is a good time to introduce the concept of a database null value. If you are
new to SQL Server and relational databases in general, you may be unfamiliar with how to correctly
handle database null values. A database null value is represented using the NULL reserved word in
Transact-SQL. In database terms, NULL means “unknown value”, and is not equivalent to anything
including an empty string or a 0.
You have to be careful when writing expressions in Transact-SQL that involve columns or variables that
allow NULL values. NULL is not equal to anything else, even another NULL value. This is the default
behavior of SQL Server. The expression “MyColumn = NULL” will always evaluate to false, regardless of
whether there are NULL values in MyColumn or not.

 Best Practice: When designing your application, do not change the default SQL Server behavior for
 how expressions that involve NULLs are evaluated. While this is currently supported in SQL Server
 2008 R2 by disabling the ANSI_NULLS query option, the ability to do so will be deprecated in a future
 release.



The correct syntax to test for a NULL value in Transact-SQL is to use the “IS NULL” or “IS NOT NULL”
clause. For example: “MyColumn IS NULL” or “MyColumn IS NOT NULL”. Transact-SQL also provides a
handy ISNULL function, which automatically replaces a NULL with a different value. For example:
Transact-SQL
SELECT ISNULL(MyColumn, 'empty') FROM MyTable;



The execution of the previous Transact-SQL command returns the string “empty” for any rows where
MyColumn is NULL. This can be useful when you want to avoid handling database null values in an
application.

 Best Practice: When designing queries, always be aware of whether the columns accessed by your
 application allow null values and handle them correctly in your application source code when
 constructing Transact-SQL commands and processing results.



Let us move on and talk about how to implement this best practice in the .NET Framework. The .NET
Framework uses DbNull.Value in System.Data to represent a database null value. DBNull.Value can only
be used to assign a database null value to a database column or parameter. For example, you can set
the SqlParameter.Value property to DBNull.Value, but you cannot set the value of a string variable to
DBNull.Value.
The .NET Framework supports the concept of a nullable type which can be useful when dealing with
database nulls in an application. A type is considered nullable if it supports a null reference. Reference
type variables, such as a variable designed to work with an instance of a class, point to a location in
memory where the data is located. If an instance of the class has not been created yet, the variable is
said to have a null reference. In .NET terms, a null reference means “points to nothing”, and is
equivalent to another null reference, but not to anything else including 0, an empty string or
DBNull.Value.
One of the most commonly used nullable reference types is System.String for working with Unicode
strings. This means that strings are treated as an instance of a class in the .NET Framework, not as a
primitive value type like an Integer or Boolean. You can write expressions using the null keyword in C#
(or Nothing in Visual Basic) to test for a null reference or to initialize a class variable to a null reference.
For example, the C# statement “string myString = null;” declares a variable of type System.String that
points to nothing. At this point the C# expression “myString == null” evaluates to true.
Some applications use the concept of a null reference to imply a database null value. This is a perfectly
acceptable approach as long as you understand three inconsistencies between the .NET concept of a
null reference and the SQL Server concept of a database null value.
    1. Expressions that involve a null reference are evaluated in a completely different fashion than
       expressions that involve a database null value. For example, the C# expression “null == null”
       evaluates to true, but the Transact-SQL Expression “NULL = NULL” evaluates to false.
    2. Nullable types can be set to a null reference, but they cannot be set to a database null value.
       The C# statement “string myString = null;” is perfectly valid but the statement “string myString =
       DBNull.Value” will generate an exception.
    3. By default, primitive value types in .NET, such as System.Int32 and System.Boolean, are not
       nullable types. You have to explicitly specify that they are nullable in the variable declaration.
       For example, in C# the statement “Int32? myVar = null;” declares a nullable value-type variable
       of type System.Int32 and initializes it to a null reference.

As long as you are aware of these inconsistencies, you can avoid logic errors in your application when
using a null reference to imply a database null value. Now that you understand the basics of how to
handle database null values using nullable types, let us move on.
In this exercise you will learn how to properly handle the results of a Transact-SQL SELECT statement
when one of the source columns allows database null values. We will revisit the proper handling of
database null values for other types of operations in subsequent exercises.
Task 1 – Creating a Data Access Class
    1. Start Microsoft Visual Web Developer 2008 Express Edition or Visual Studio 2008 from Start |
       All Programs.
    2. In the File menu, choose Open and then Project/Solution. In the Open Project dialog, browse
       to Ex2-ReadingData\Begin in the Source folder of this lab, select Begin.sln and click Open.
    3. Create a Product class that contains the properties that you are going to show in the page. To
       do this, right-click the ListProducts project in Solution Explorer, point to Add, and then click
       Class. Type Product.cs in the Name text box and click Add.
    4. Add the public access modifier to the class if necessary.
    5. Add the following properties to the Product class.
        (Code Snippet – First SQL ASP.NET Application Lab – Ex02 – Product class properties)
        C#
        namespace ListProducts
        {
            public class Product
       {
            public int ProductId
            {
                get;
                set;
            }

            public string Name
            {
                get;
                set;
            }

            public decimal ListPrice
            {
                get;
                set;
            }

            public string Size
            {
                get;
                set;
            }

            public string OriginalName
            {
                get;
                set;
            }

            public decimal OriginalListPrice
            {
                get;
                set;
            }

            public string OriginalSize
            {
                get;
                set;
            }
       }
}



    Best Practice: When designing your application, create a class to work with your data as a
    business object in ASP.NET. This will make it easier to leverage powerful web server controls
     that support data binding. Classes will also make it easier to work with your data in code
     because of Visual Studio features like IntelliSense and AutoComplete.



   While there are many columns in the Products table, we are only going to work with four of
   them in this hands-on lab (ProductId, Name, ListPrice and Size). Notice that the class contains
   one property for the primary key (ProductId) and two properties for each of the data values.

     Best Practice: When designing a class to be used for data binding, consider adding two
     properties for each data field that can be updated. One property is used to store the current
     value in the application, and the other property is used to store the value that was originally
     read from the database. This will make it easier to implement optimistic concurrency, which
     we will examine in an exercise later in this hands-on lab.



   If you examine the structure of the Products table, you will find that of the four columns we
   intend to use, only the Size column allows database null values. Since Size is a string, and strings
   are a nullable type, we can use a null reference for the Product.Size property to imply a
   database null value in the source table.

     Best Practice: When designing a class to be used for data binding, use nullable types for
     properties whose bound column allows database null values. This will allow you to implement
     logic in your application that implies that a null reference is equivalent to a database null
     value. Remember that primitive value types such as System.Int32 and System.Boolean must be
     explicitly declared as nullable since value types do not support null references by default.



6. Create a Data Access class that will perform common operations against the database. To do
   this, right-click the ListProducts project in Solution Explorer, point to Add, and then click Class.
   Type ProductsDataAccess.cs in the Name text box and click Add.
7. Add the following using statements at the top of the file,.
   C#
   using System.Configuration;
   using System.Data.SqlClient;



8. Modify the class signature to make it static and public.
   C#
   public static class ProductsDataAccess
   {
   }
9. Add a public static method for retrieving all products. To do this, add the following code (shown
   in bold) to the ProductsDataAccess class. Add code to open the connection and execute a query
   to return all the products with a list price greater than $3,500.
   (Code Snippet – First SQL ASP.NET Application Lab – Ex02 – RetrieveAllProducts method)
   C#
   public static ICollection<Product> RetrieveAllProducts()
   {
       List<Product> products = new List<Product>();

       using (SqlConnection connection = new
   SqlConnection(ConfigurationManager.ConnectionStrings["AdventureWorks"].Connect
   ionString))
        {
           var listPrice = 3500;
           connection.Open();

           var commandText = "SELECT ProductID, Name, ListPrice, Size FROM
   Production.Product WHERE ListPrice > @ListPrice ORDER BY ProductID DESC";
           var command = new SqlCommand(commandText, connection);
           command.Parameters.AddWithValue("@ListPrice", listPrice);

              using (SqlDataReader reader = command.ExecuteReader())
              {
                  while (reader.Read())
                  {
                      products.Add(new Product
                      {
                          ProductId = reader.GetInt32(0),
                          Name = reader.GetString(1),
                          ListPrice = reader.GetDecimal(2),
                          Size = !reader.IsDBNull(3) ? reader.GetString(3) : null,
                          OriginalName = reader.GetString(1),
                          OriginalListPrice = reader.GetDecimal(2),
                          OriginalSize = !reader.IsDBNull(3) ? reader.GetString(3) :
   null
                       });
                  }
              }
          }

          return products;
   }



   Let us look at this code in a bit more detail. The SELECT command is executed using the
   ExecuteReader method and if it succeeds a SqlDataReader object is returned to the application
   which can be used to read the results a row at a time. While the query is being executed, SQL
       Server obtains a shared lock on the data. Shared locks permit other users to read the same data,
       but prevent users from changing the data while the lock is held. In this case, the lock is released
       as soon as query execution completes because we are not using a transaction. We will talk more
       about transactions and locks in a future exercise.
       Next, the result set is read one row at a time in a loop and a new instance of the Product class is
       created and added to a collection after each iteration. The properties of each new Product
       instance are set using the appropriate column in the SqlDataReader object.
       After the last row is read, the SqlDataReader and SqlConnection objects are closed and a
       collection of products is returned to the caller. At this point the application has a copy of the
       results stored in memory. This means that other users can now change the source data. It also
       means that the data stored in the collection may become out of date. We will discuss this more
       in an upcoming exercise.
    10. Press CTRL+SHIFT+B to build the solution.

Task 2 – Binding the Data to the UI
    1. Open the Default.aspx page in Design view. To do this, double-click the file in the Solution
       Explorer, and then click Design at the bottom left corner of the designer.
    2. From the Toolbox drag a ListView control into the designer surface. Click the Choose Data
       Source drop down list, and then click New Data Source.

         Note: If you do not find the Chose Data Source dropdown list beside the ListView control,
         right-click the control in the designer surface and click Show Smart Tag in the menu.




       Figure 3
       Choosing the Data Source for the ListView control


    3. In the Data Source Configuration Wizard, select the Object data source type, enter
       ProductsDataSource as the Data Source ID, and then click OK.
   Figure 4
   Choosing the data source type


4. Choose the ListProducts.ProductsDataAccess as the business object, and then click Next.
   Figure 5
   The Configure Data Source Wizard


5. In the SELECT tab, choose the RetrieveAllProducts method and click Finish.
   Figure 6
   Choosing the select method


6. Go to the Source view, by clicking Source. Replace the Label control located between the
   <blockquote> tags with the following one.
   HTML
   <asp:Label runat="server" ID="QueryExecutedLabel" Text="SELECT ProductID,
   Name, ListPrice, Size FROM Production.Product WHERE ListPrice > 3500
   ORDER BY ProductID DESC" />



7. Paste the following code in bold between the <asp:ListView> tags. This code adds an
   ItemTemplate for the rows returned from the Data Source to specify how they will be
   displayed.
   HTML
   <asp:ListView ID="ProductsListView" runat="server"
   DataSourceID="ProductsDataSource">
       <LayoutTemplate>
                 <asp:PlaceHolder ID="itemPlaceholder" runat="server" />
             </LayoutTemplate>
             <ItemTemplate>
                 <div>
                     <h3>
                          <b>Name:&nbsp;</b><%# Eval("Name") %></h3>
                     <p>
                          <b>Product ID:&nbsp;</b><%# Eval("ProductId")%><br />
                          <b>List price:&nbsp;</b><%# Eval("ListPrice") %><br />
                          <b>Size:&nbsp;</b><%# Eval("Size") %>
                     </p>

                </div>
            </ItemTemplate>
        </asp:ListView>



        The ListView web server control allows you to create templates to define the layout of its
        content. Some of the templates are: AlternatingItemTemplate, EditItemTemplate,
        InsertItemTemplate, ItemTemplate and SelectedItemTemplate. In this case we are defining the
        ItemTemplate layout, which controls the default way an instance of the underlying business
        object will be displayed in the list.
    8. Save and close the files.

Task 3 – Adding a Handler to Display the Product Images
Lots of web applications need to display images that are stored in a database. ASP.NET supports the img
tag for displaying images that are stored in the file system using a URL specified in the src attribute. In
this task you will add code that replaces the URL with a call to a custom method that retrieves the image
from the database and inserts the binary data associated with the image directly into the response
stream so it can be rendered on the page.
    1. Add the ProductImage.ashx handler file to the solution. To do this, right-click the ListProducts
       project, point to Add, and click Existing Item. Browse to Assets in the Source folder of this lab,
       select the ProductImage.ashx file and click Add.

         Note: The image handler intercepts the HTTP request and returns the image binary data of the
         corresponding product as the response.



    2. Open the ProductImage.ashx handler. To do this, double-click the file in the Solution Explorer.
    3. Locate and inspect the GetImageBytes method. This method contains the query that returns
       the first photo associated to the given product ID.
    4. Open the Default.aspx page, by double-clicking the file in the Solution Explorer.
    5. Add code to display the image corresponding to each product. To do this, add the following
       code, inside the ItemTemplate tags after the closing </p> tag.
        HTML
        <img src="<%# "ProductImage.ashx?ProductId=" + Eval("ProductId") %>" />



         Note: The ProductId is passed as a parameter to the handler. The handler executes a SQL
         query and returns the corresponding image as a stream.



    6. Save and close the files.


Exercise 2: Verification
In order to verify that you have correctly performed every steps of exercise two, proceed as follows.
    1. Press CTRL+F5 to run the solution. Each product should be accompanied by its image.




        Figure 7
        Retrieving the products with their images from the SQL Server


    2. Close the browser to end this verification.
Exercise 3: Inserting Data
In this exercise you will learn how to add a new product and its associated image to the database using
the Transact-SQL INSERT statement. Because of the structure of the AdventureWorks2008R2 database,
this involves inserting a new row into three separate tables. Before we get started, it is important that
you understand the concept of exclusive locks, transactions and unique identifiers.
Let us start with exclusive locks. SQL Server places exclusive locks to an exclusive lock on data affected
when executing Transact-SQL command that change the database (INSERT, UPDATE and DELETE).
Exclusive locks prevent other locks (shared or exclusive) from being placed on the same data until they
are released. If the execution of a command is blocked by a lock, SQL Server will automatically wait for it
to be released before proceeding. This may result in some latency known as a lock wait, especially in a
database that sees frequent changes. In most cases you do not have to do anything special in your
application to account for lock waits.
Next, let us discuss transactions. In some cases, it is useful to retain locks to ensure that a set of changes
is applied to the database in a consistent way. To support this, SQL Server and ADO.NET support the
concept of a transaction. Transactions provide the ability to enforce consistency at the expense of multi-
user concurrency by holding locks until a series of changes is complete. While transactions are an
integral component of any data-driven application, you should take care to limit their scope and
duration. In ADO.NET, transactions are implemented using the System.Transactions class library.
Finally, let us talk about unique identifiers. As we mentioned previously, tables should have a primary
key that can be used to uniquely identify every row in the table. This sounds simple enough, but
implementing this in a multi-user environment can be tricky. Fortunately SQL Server makes the process
of creating unique identifiers a snap. You can generate new unique identifiers using the SQL Server
IDENTITY property or using globally unique identifier.
An IDENTITY is nothing but an integer sequence managed by SQL Server. To use the IDENTITY approach,
simply define a column in your table using one of the integer data types and declare it as an IDENTITY.
When new data is inserted in the table, SQL Server will automatically generate an integer sequence
number and store it in the column. You can determine the value that was generated for a newly-
inserted row by querying @@IDENTITY immediately after the insert.

 Best Practice: Use the SQL Server IDENTITY property to generate unique identifiers when you want
 SQL Server to manage the creation of unique identifiers that are pneumonic.



A globally unique identifier, or GUID, is a reference number generated by an algorithm that is
guaranteed to be unique. GUIDs are typically represented as text, like this: F1F7310E-A04E-441B-B56E-
FA957352D9FE. To use this approach, simply define a column in your table using the uniqueidentifier
data type and declare it as a ROWGUIDCOL. If you want SQL Server to automatically generate a new
GUID during an insert if one is not provided, associate a DEFAULT constraint with the column that uses
the NEWSEQUENTIALID() function to generate a new GUID. Otherwise, generate the GUID in your
application and specify its value in the insert statement.

 Best Practice: Use GUIDs for unique identifiers when you want the application to manage the creation
 of unique identifiers and they do not have to be pneumonic.



Task 1 – Adding the Insert Method to the Data Access Class
    1. In the File menu, choose Open and then Project/Solution. In the Open Project dialog, browse
       to Ex3-InsertingData\Begin in the Source folder of this lab, select Begin.sln and click Open.
       Alternatively, you may continue working with the solution obtained after completing the
       previous exercise.
    2. Open the ProductsDataAccess.cs class by double-clicking the file in the Solution Explorer.
    3. Add a reference to the System.Transactions assembly. To do this, right-click the project name in
       Solution Explorer, and click Add Reference. In the .NET tab, select the System.Transactions
       assembly, and then click OK.
    4. Add the following using statements at the top of the file.
       C#
       using System.Data.SqlTypes;
       using System.Transactions;



    5. Create a new public static method to insert a new product. To do this, paste the following code
       (shown in bold) inside the ProductDataAccess class.
       (Code Snippet – First SQL ASP.NET Application Lab – Ex03 – InsertProduct method)
       C#
       public static void InsertProduct(string name, decimal listPrice, string
       size, byte[] photo)
       {
           using (TransactionScope scope = new TransactionScope())
           {
               using (SqlConnection connection = new
       SqlConnection(ConfigurationManager.ConnectionStrings["AdventureWorks"].
       ConnectionString))
               {
                    connection.Open();
                    SqlInt32 productId = InsertProductRow((SqlString)name,
       (SqlMoney)listPrice, (SqlString)size, connection);
                    InsertProductPhoto(productId, photo, connection);
                    scope.Complete();
               }
           }
   }



   Let us look at this code a little more closely. Notice that we did not use SQL Server native types
   for the parameters of the InsertProduct method. This is because this method will be called by an
   ASP.NET Web Control. ASP.NET web controls only support native .NET Framework data types, so
   we cast these parameter values to SQL Server native types later.
   Adding a new product involves performing three separate insert operations against three
   different related tables in the AdventureWorks2008R2 database. All three of these inserts must
   succeed for the database to be in a consistent state. If there are any errors, all of the changes
   must be rolled back. What we need is a transaction.
   To implement this behavior, the InsertProduct method wraps all of the individual inserts in a
   TransactionScope. Any connections that are opened inside the using statement will
   automatically be enlisted in the transaction. If multiple connections are opened to different SQL
   Server instances, the transaction is automatically promoted to a distributed transaction. Use of
   distributed transactions requires that the Distributed Transaction Coordinator service be
   started and configured by a system administrator in advance.
   After each of the individual insert operations have completed it is time to commit the
   transaction. This is done by calling the Complete method on the TransactionScope object.
   Notice that all of the data needed to perform our three inserts is collected from the user outside
   of the TransactionScope. This is by design. Using this approach the exclusive locks required to
   perform the three inserts can be placed and released quickly with no user intervention required,
   maximizing the concurrency of the application.

       Best Practice: To improve the multi-user concurrency of your application, limit the scope and
       duration of transactions to short operations that do not require user intervention to complete.
       Avoid using distributed transactions except in situations where they are absolutely necessary.
       If you must use them, strictly limit their scope and duration. Distributed transactions can
       introduce additional latency and can be complicated to configure, monitor and debug.



6. Create the InsertProductRow method that is invoked by the InsertProduct method. To do this,
   add the following method in the ProductDataAccess class.
   (Code Snippet – First SQL ASP.NET Application Lab – Ex03 – InsertProductRow method)
   C#
   private static SqlInt32 InsertProductRow(SqlString name, SqlMoney listPrice,
   SqlString size, SqlConnection connection)
   {
       // at the end of the command we execute a SELECT to get the newly
   generated product Id
       var commandText = "INSERT INTO Production.Product " +
       "(Name, ProductNumber, ListPrice, Size, SafetyStockLevel, " +
       "ReorderPoint, StandardCost, DaysToManufacture, SellStartDate) " +
       "VALUES (@Name, @ProductNumber, @ListPrice, @Size, @SafetyStockLevel, " +
       "@ReorderPoint, @StandardCost, @DaysToManufacture, @SellStartDate); " +
       "SELECT CAST(@@IDENTITY as integer); ";

       var command = new SqlCommand(commandText, connection);

       command.Parameters.AddWithValue("@Name", name);
       command.Parameters.AddWithValue("@ListPrice", listPrice);
       command.Parameters.AddWithValue("@Size", size);

    // set all other required fields that don't have a default value.
    command.Parameters.AddWithValue("@ProductNumber", "TK-" +
DateTime.Now.Millisecond);
    command.Parameters.AddWithValue("@SafetyStockLevel", 100);
    command.Parameters.AddWithValue("@ReorderPoint", 100);
    command.Parameters.AddWithValue("@StandardCost", 0);
    command.Parameters.AddWithValue("@DaysToManufacture", 0);
    command.Parameters.AddWithValue("@SellStartDate", DateTime.Today);

       // return to the program the result of the recently inserted product Id
       SqlInt32 productId = (Int32)command.ExecuteScalar();
       return productId;
}



Let us examine this code in a bit more detail. This code inserts the first of three rows required to
add a product. In this case we are inserting a new row into the Production.Product table. Notice
that the command text actually includes two commands, an INSERT and a SELECT. The INSERT
statement adds a new row to the table. Notice that the ProductId is not specified. This is
because it will be automatically generated by SQL Server using an IDENTITY. The SELECT
statement returns the newly generated ProductId using @@IDENTITY cast as an integer.

    Best Practice: Return a newly generated IDENTITY value to the application using the
    @@IDENTITY function instead of re-querying the source table to find out the value of an
    automatically generated unique identifier. This avoids unnecessary overhead and will make
    your application perform better.



Next, parameters and values are added to the SqlCommand object for each of the named
parameters in the INSERT command. One parameter is created for each column that requires a
value. User-provided values are available for name, listPrice and size, and dummy values are
used for the other required columns.
   Finally, the command is executed using the ExecuteScaler method, and the newly generated
   ProductId is returned to the caller. Note that you can only cast the return values of the
   ExecuteScalar method to native .NET Framework data types.
7. Create the InsertProductPhoto method that is invoked by the InsertProduct method. To do
   this, add the following method in the ProductDataAccess class.
   (Code Snippet – First SQL ASP.NET Application Lab – Ex03 – InsertProductPhoto method)
   C#
   private static void InsertProductPhoto(SqlInt32 productId, byte[] photo,
   SqlConnection connection)
   {
       // at the end of the command we execute a SELECT to get the newly
   generated product photo Id
       var insertPhotoCommandText = "INSERT INTO Production.ProductPhoto " +
       "(ThumbNailPhoto) VALUES (@ThumbNailPhoto); " +
       "SELECT CAST(@@IDENTITY AS integer); ";
       var insertPhotoCommand = new SqlCommand(insertPhotoCommandText,
   connection);

          insertPhotoCommand.Parameters.AddWithValue("@ThumbNailPhoto", photo);

          // store the new product photo Id
          SqlInt32 productPhotoId = (Int32)insertPhotoCommand.ExecuteScalar();
   }



   The preceding code creates the query to insert a new photo in the ProductPhoto table, and
   after that it returns the newly generated product photo ID. Notice that the photo binary data is
   passes as a Command Parameter.

       Note: Due to the AdventureWorks2008R2 database structure, to insert an image for a product
       you need to insert the image in the ProductPhoto table and then create the relation between
       the Product and ProductPhoto tables.



8. Add code to the InsertProductPhoto method to add the relation between the Product and the
   ProductPhoto tables. To do this, add the following bolded code in the InsertProductPhoto
   method, below the code inserted previously.
   (Code Snippet – First SQL ASP.NET Application Lab – Ex03 – Relation between Product and
   ProductPhoto)
   C#
   private static void InsertProductPhoto(SqlInt32 productId, byte[] photo,
   SqlConnection connection)
   {
               ...

            var addRelationCommandText = "INSERT INTO
        Production.ProductProductPhoto " +
            "(ProductID, ProductPhotoID, [Primary]) VALUES " +
            "(@ProductID, @ProductPhotoID, @Primary)";

            var addRelationCommand = new SqlCommand(addRelationCommandText,
        connection);

            addRelationCommand.Parameters.AddWithValue("@ProductID",
        productId);
            addRelationCommand.Parameters.AddWithValue("@ProductPhotoID",
        productPhotoId);
            addRelationCommand.Parameters.AddWithValue("@Primary", true);

               addRelationCommand.ExecuteNonQuery();
        }



            Note: The ExecuteNonQuery method of the SqlCommand class is used when the query does
            not return any value. In the preceding code it is used for executing an Insert query.



    9. Press CTRL+SHIFT+B to build the solution.

Task 2 – Binding the Data to the UI
In this task you will learn how to add the insert form and bind it to the method created in the previous
task.
    1. Open the Default.aspx page in Design view. To do this, double-click the file in the Solution
       Explorer, and then click Design at the bottom left corner of the designer.
    2. Click the ObjectDataSource control in the designer surface to select it.

    3. Click the Smart Tag (    ) to expand the ObjectDataSource tasks, and then click Configure Data
       Source.
    4. Click Next in the Choose a Business Object page. Click the Insert tab, choose the InsertProduct
       method and click Finish.
   Figure 8
   Choosing the insert method


5. Add a DetailsView control to the page to. To do this, from the Toolbox drag a DetailsView
   control at the top of the designer surface, below the Query Statement Executed block.
6. Select the existing ProductsDataSource data source as the Data Source for the control. To do
   this, click the Choose Data Source drop down list, and then select ProductsDataSource.
   Figure 9
   Choosing the Data Source for the DetailsView control


7. Click the DetailsView control Smart Tag ( ) to display the DetailsView Tasks context menu
   and then click Add New Field. In the Add Field dialog box, select TemplateField as the field
   type, type Image as the Header text, and then click OK.
   Figure 10
   Add Field dialog box


8. Click the DetailsView control Smart Tag ( ) to display the DetailsView Tasks context menu
   and then click Edit Templates. Select Field[7] – Photo in the Display combo box.




   Figure 11
   Editing the templates for the DetailsView control


9. From the Standard section of the Toolbox, drag a FileUpload control to the InsertItemTemplate
   area.
   Figure 12
   Adding a FileUpload control to the InsertItemTemplate area


10. Click over the FileUpload control previously added to select it and press F4 to go to the control
    properties. Type ProductImageFileUpload as the control (ID) in the Properties pane.
   Figure 13
   Changing the control ID of the FileUpload control


11. Click the DetailsView control Smart Tag (     ) and then click End Template Editing to finish
    editing the control templates.

12. Click again the DetailsView control Smart Tag ( ) and now click Edit Fields. In the Fields
    window, delete the ProductID, OriginalName, OriginalListPrice and OriginalSize fields from the
    Selected Fields list. To do this, select one by one each of the fields and click the delete button (
        ). Finally click OK to close the Fields window.
   Figure 14
   Deleting field from the DetailsView control


13. Select the Enable Inserting checkbox of the DetailsView Tasks context menu.




   Figure 15
   Selecting the Enable Inserting check box
14. Select the DetailsView control and press F4 to see its properties. In the Properties pane set the
    following properties:
        ◦   Set the control (ID) to InsertProductDetailsView.

        ◦   Set the AutoGenerateRows property to False.
        ◦   Set both CellPadding and CellSpacing properties to 1.

        ◦   Set the DefaultMode property to Insert.

        ◦   Expand the FieldHeaderStyle element and set the CssClass property to FieldHeader.

15. Click the events icon ( ) on the Properties pane to see the available event of the control.
    Double-click the ItemInserting event to generate the event handler for that event.
16. Paste the following code inside the event handler to get the photo from the FileUploader
    control.
   (Code Snippet – First SQL ASP.NET Application Lab – Ex03 – ItemInserting event code)
   C#
   protected void InsertProductDetailsView_ItemInserting(object sender,
   System.Web.UI.WebControls.DetailsViewInsertEventArgs e)
   {
       var fileUpload =
   this.InsertProductDetailsView.FindControl("ProductImageFileUpload") as
   FileUpload;
       e.Values["photo"] = fileUpload.FileBytes;
   }



     Note: This handler is used to intercept the insertion before it is made, and update the product
     with the corresponding bytes of the chosen image.



17. Add the following using statements at the top of the file.
   C#
   using System.Web.UI.WebControls;



18. Open the Default.aspx page in Source view and add the following code after the
    ObjectDataSource closing tag.
   HTML
   <fieldset style="clear: both">
           <legend>Add new product</legend>
           <br />
           <p>
                    * Size field has a max lenght of 5</p>
               <br style="clear: both" />
               <%--Move the DetailsView control here--%>
        </fieldset>



    19. Move the DetailsView control inside the <fieldset> tags replacing the comment “Move the
        DetailsView control here”. This is shown in the following code.
        HTML
        <fieldset style="clear: both">
            <legend>Add new product</legend>
            <br />
            <p>
                * Size field has a max lenght of 5</p>
            <br style="clear: both" />
            <asp:DetailsView ID="InsertProductDetailsView" runat="server"
        DataSourceID="ProductsDataSource"
                DefaultMode="Insert" AutoGenerateRows="False"
        OnItemInserting="InsertProductDetailsView_ItemInserting">
                ...
            </asp:DetailsView>
        </fieldset>



    20. Save the changes and close the files.


Exercise 3: Verification
In order to verify that you have correctly performed every steps of exercise three, proceed as follows.
    1. Press CTRL+F5 to run the web application.
   Figure 16
   Listing the products


2. In the Add new product section type the following data in the product fields:
       ◦   Name: Road-180 Mountain Bike, 48
       ◦   ListPrice: 3750

       ◦   Size: 48
3. Click the Browse button and select a photo from your computer. There are a couple of images
   that you can use, located under the Assets folder in the Source folder of this lab. Finally, click
   Insert.
4. The listing page will be updated, showing the new product you have just added.
        Figure 17
        Listing the products after the insertion




Exercise 4: Updating Data
In this exercise, you will learn how to create an update method, how to bind it to the object data source,
and modify the UI to change existing products. Before we begin, let us talk about some special
circumstances that can arise in applications that support the ability for multiple users to change the
same data.
Updates present an interesting challenge related to handling conflicts when users can change the same
data. The classic scenario is the following:

       User A reads record X from the database with the intent to change it.

       User B also reads record X with the intent to change it.
       User B updates record X, making User A’s copy out of date.

       User A updates record X, resulting in a conflict.
There are three basic ways to handle conflicts:
    1. Ignore conflicts and permit lost updates.
    2. Use pessimistic concurrency which employs locking and transactions to avoid conflicts in the
       first place.
    3. Use optimistic concurrency to detect conflicts by comparing values to avoid lost updates and
       forcing the loser to re-read the source data and try their update again.
Each of these approaches has advantages and disadvantages. Permitting lost updates will probably not
be an option for most serious applications. Pessimistic concurrency is fairly easy to implement but may
require holding locks that require user intervention to release, which is contrary to a best practice we
stated earlier in the Hands-on Lab. So that leaves optimistic concurrency.

 Best Practice: Use optimistic concurrency to detect conflicts and prevent lost updates in applications
 that permit users to change the same data.



Optimistic concurrency requires the application to see if data has changed since it was read from the
database before it is updated. There are many ways of implementing optimistic concurrency, including
comparing the current values in the database with the ones that were originally read or using some kind
of metadata like a row version or time stamp. If the data has changed the application usually reports an
error to the loser, then re-reads the data and asks them to resubmit the request with fresh data.
Task 1 – Adding the Update Method to the Data Access Class
    4. In the File menu, choose Open and then Project/Solution. In the Open Project dialog, browse
       to Ex4-UpdatingData\Begin in the Source folder of this lab, select Begin.sln and click Open.
       Alternatively, you may continue working with the solution obtained after completing the
       previous exercise.
    5. Open the ProductsDataAccess.cs class by double-clicking the file in the Solution Explorer.
    6. Create a new public static method to update an existing product. To do this, paste the following
       code (shown in bold) inside the ProductDataAccess class.
        (Code Snippet – First SQL ASP.NET Application Lab – Ex04 – UpdateProduct method)
        C#
        public static void UpdateProduct(int productId, string name, string
        originalName, decimal listPrice, decimal originalListPrice, string size,
        string originalSize)
        {
            var rows = 0;

            using (SqlConnection connection = new
        SqlConnection(ConfigurationManager.ConnectionStrings["AdventureWorks"].Connect
        ionString))
            {
                connection.Open();

                  var commandText = "UPDATE Production.Product " +
                   "SET Name=@Name, ListPrice=@ListPrice, Size=@Size " +
                   "WHERE ProductID=@ProductID " +
                   "AND Name=@OriginalName " +
                   "AND ListPrice=@OriginalListPrice " +
                   "AND " +
                   "( " +
                   " (Size IS NULL AND @OriginalSize IS NULL) " +
                   " OR " +
                   " (Size=@OriginalSize) " +
                   "); ";

               var command = new SqlCommand(commandText, connection);

           command.Parameters.AddWithValue("@ProductID", productId);
           command.Parameters.AddWithValue("@Name", name);
           command.Parameters.AddWithValue("@OriginalName", originalName);
           command.Parameters.AddWithValue("@ListPrice", listPrice);
           command.Parameters.AddWithValue("@OriginalListPrice",
   originalListPrice);

               if (size == null)
                    command.Parameters.AddWithValue("@Size", DBNull.Value);
               else
                    command.Parameters.AddWithValue("@Size", size);

               if (originalSize == null)
                    command.Parameters.AddWithValue("@OriginalSize", DBNull.Value);
               else
                    command.Parameters.AddWithValue("@OriginalSize", originalSize);

               rows = command.ExecuteNonQuery();
          }

          if (rows < 1)
              MessageBox.Show("Conflict detected.           Please try again.");
   }



       Note: The WHERE clause of the UPDATE statement uses the original values to detect conflicts
       in the database. If no rows are affected by the update, that means that the original values
       changed or the row was deleted and there was a conflict. Take notice of the way the Size
       column must be handled because it supports NULL.



7. Add the MessageBox.cs file to the solution. To do this, right-click the ListProducts project, point
   to Add, and click Existing Item. Browse to Assets in the Source folder of the lab, select the
   MessageBox.cs file and click Add.
         Note: The MessageBox is a helper class that allows displaying a message when the page is
         loaded in the browser (by writing a JavaScript alert in the Response).



    8. Press CTRL+SHIFT+B to build the solution.

Task 2 – Binding the Data to the UI
In this task you will learn how to add the update form and bind it to the method created in the previous
task.
    1. Open the Default.aspx page in Design view. To do this, double-click the file in the Solution
       Explorer, and then click Design at the bottom left corner of the designer.
    2. Click the ObjectDataSource control in the designer surface to select it.

    3. Click the Smart Tag (    ) to expand the ObjectDataSource tasks, and then click Configure Data
       Source.
    4. Click Next in the Choose a Business Object page. Click the Update tab and choose the
       UpdateProduct method and click Finish.




        Figure 18
   Choosing the update method


5. If the Refresh Fields and Keys for 'InsertProductsDetailsView' dialog displays, click No.




   Figure 19
   Refresh Fields and Keys dialog box


6. Open the Default.aspx page in Source view, locate the opening ListView tag, and add the
   DatakeyNames property as shown in the following code.
   HTML
   <asp:ListView ID="ProductsListView" runat="server"
   DataSourceID="ProductsDataSource" DataKeyNames="ProductId">



   The DataKeyNames property sets the names of the primary key fields in the data source bound
   to the ListView.
7. Add the EditItemTemplate template to the ListView. To do this, add the following bolded code
   below the <LayoutTemplate> closing tag.
   HTML
   <asp:ListView ID="ProductsDataList" runat="server"
   DataSourceID="ProductsDataSource"
       DataKeyNames="ProductId">
       <LayoutTemplate ... />
        <EditItemTemplate>
           <div>
             <h3>
               <b>Name:&nbsp;</b>
               <asp:TextBox ID="NameTextBox" runat="server" Text='<%#
   Bind("Name") %>' />
               <asp:TextBox ID="OriginalNameTextBox" runat="server"
   Visible="False" ReadOnly="True" Text='<%# Bind("OriginalName") %>' />
             </h3>
             <p>
               <b>Product ID:&nbsp;</b>
               <%# Eval("ProductId")%><br />
               <b>List price:&nbsp;</b>
               <asp:TextBox ID="ListPriceTextBox" runat="server" Text='<%#
   Bind("ListPrice") %>' />
               <asp:TextBox ID="OriginalListPriceTextBox" runat="server"
   Visible="False" ReadOnly="True" Text='<%# Bind("OriginalListPrice") %>'
   />
               <br />
               <b>Size:&nbsp;</b>
               <asp:TextBox ID="SizeTextBox" runat="server" Text='<%#
   Bind("Size") %>' />
               <asp:TextBox ID="OriginalSizeTextBox" runat="server"
   Visible="False" ReadOnly="True" Text='<%# Bind("OriginalSize") %>' />
             </p>
             <p style="clear: both">
               * Size field has a max length of 5</p>
             <img src="<%# "ProductImage.ashx?ProductId=" +
   Eval("ProductId") %>" />
             <br style="clear: both" />
             <asp:LinkButton ID="LinkButton1" runat="server"
   CommandName="Update">Save</asp:LinkButton>&nbsp;|&nbsp;<asp:LinkButton
                 ID="LinkButton2" runat="server"
   CommandName="Cancel">Cancel</asp:LinkButton>
           </div>
        </EditItemTemplate>
       <ItemTemplate ... />
   </asp:ListView>



   The preceding code sets the template for a product when it is in edit mode.

     Note: the originalName, originalPrice and originalSize parameters are bound as hidden fields
     so their values are accessible from the ASP.NET ViewState. This makes it possible to check the
     original values to detect conflicts in an update operation.



8. Add an Edit button to the ItemTemplate for entering edition mode. To do this, add the
   following bolded code inside the ItemTemplate tags, at the bottom of the <div> element.
   HTML
   <ItemTemplate>
      <div>
      ...
        <br style="clear: both" />
        <asp:LinkButton runat="server" CommandName="Edit">Edit</asp:LinkButton>
           </div>
        </ItemTemplate>



    9. Save the changes and close the files.


Exercise 4: Verification
In order to verify that you have correctly performed the steps for exercise four, proceed as follows.
    1. Press CTRL+F5 to run the solution.




        Figure 20
        Listing the products


    2. Click Edit, in the product inserted in the previous exercise, to update it.
   Figure 21
   Updating a product


3. Update the fields with the following data.
       ◦   Name: XL Mountain Bike
       ◦   List Price: 4000
4. Click Save to update the products. The product listing will be reloaded and the updated product
   will be shown.
   Figure 22
   Updating the products



     Note: Next we will simulate a conflict by updating the same row from a different program.



5. Click Edit on the same product edited in the previous step.
6. Start SQL Server Management Studio from Start | All Programs | Microsoft SQL Server 2008
   R2.
7. In the Connect to Server dialog, establish a new connection using the following values:

       ◦   Server Type: Database Engine
       ◦   Server Name: SqlServerTrainingKitAlias
       ◦   Authentication: Windows Authentication
8. Click on New Query.
9. Choose AdventureWorks2008R2 from the Available Databases dropdown list.
10. Type in the following query and type F5 to execute it:
   Transact-SQL
        UPDATE Production.Product SET ListPrice = 6000 WHERE Name = 'XL
        Mountain Bike';



        This query should generate the following output: (1 row(s) affected).
    11. Close SQL Server Management Studio.
    12. Update the fields with the following data.
            ◦   List Price: 5000
    13. Click Save. This will result in a conflict message being displayed.




        Figure 23
        Message shown when a conflict is detected


    14. Click OK.
    15. Finally, close the browser.



Exercise 5: Deleting Data
In this exercise, you will add code to implement deletion of products and bind that code to a link button.
Task 1 – Adding the Delete Method to the Data Access Class
    1. In the File menu, choose Open and then Project/Solution. In the Open Project dialog, browse
       to Ex5-DeletingData\Begin in the Source folder of this lab, select Begin.sln and click Open.
       Alternatively, you may continue working with the solution obtained after completing the
       previous exercise.
    2. Open the ProductsDataAccess.cs class by double-clicking the file in the Solution Explorer.
    3. Create a new public static method to delete existing products. Remember that, as the
       ProductDataAccess class is static, all its methods must also be static. To do this, paste the
       following code (shown in bold) inside the ProductDataAccess class.
        (Code Snippet – First SQL ASP.NET Application Lab – Ex05 – DeleteProduct method)
        C#
        public static void DeleteProduct(int productId)
        {
            using (var scope = new TransactionScope())
            {
                 using (SqlConnection connection = new
        SqlConnection(ConfigurationManager.ConnectionStrings["AdventureWorks"].
        ConnectionString))
                 {
                     connection.Open();

                    var deleteProductProductPhotoCommand = new
        SqlCommand("DELETE Production.ProductProductPhoto WHERE
        ProductID=@ProductID;", connection);
                    deleteProductProductPhotoCommand.Parameters.AddWithValue(
        "@ProductID", productId);
                    deleteProductProductPhotoCommand.ExecuteNonQuery();

                    var deleteProductPhotoCommand = new SqlCommand("DELETE
        Production.ProductPhoto WHERE ProductPhotoId NOT IN (SELECT DISTINCT
        ProductPhotoId FROM Production.ProductProductPhoto);", connection);
                    deleteProductPhotoCommand.ExecuteNonQuery();

                    var deleteProductCommand = new SqlCommand("DELETE
        Production.Product WHERE ProductID=@ProductID;", connection);
                    deleteProductCommand.Parameters.AddWithValue( "@ProductID",
        productId);
                    deleteProductCommand.ExecuteNonQuery();

                        scope.Complete();
                  }
             }
        }



        The preceding code creates a new transaction to perform the delete operations. First, it deletes
        the relation between the Products and Photo tables. Next it deletes any orphaned photos.
        Finally it deletes the product.
    4. Press CTRL+SHIFT+B to build the solution.

Task 2 – Binding the Data to the UI
In this task you will see how to bind the ObjectDataSource to the method created in the previous task,
and to create a button for deleting products.
1. Open the Default.aspx page in Design view. To do this, double-click the file in the Solution
   Explorer, and then click Design at the bottom left corner of the designer.
2. Click the ObjectDataSource control in the designer surface to select it.

3. Click the Smart Tag (   ) to expand the ObjectDataSource tasks, and then click Configure Data
   Source.
4. Click Next in the Choose a Business Object page. Click the Delete tab and choose the
   DeleteProduct method and click Finish.




   Figure 24
   Choosing the delete method


5. If the Refresh keys and fields for InsertProductsDetailsView dialog displays, click No.
6. Open the Default.aspx page in Source view by clicking Source at the bottom left corner of the
   designer.
7. Add a Delete button to the ItemTemplate for deleting a product. To do this, add the following
   bolded code inside the ItemTemplate tags, next to the Edit button.
   HTML
       <ItemTemplate>
           <div>
           ...
               <asp:LinkButton runat="server"
       CommandName="Edit">Edit</asp:LinkButton>&nbsp;|&nbsp;<asp:LinkButton
       runat="server" CommandName="Delete">Delete</asp:LinkButton>
           </div>
       </ItemTemplate>



    8. Save the changes and close the files.


Exercise 5: Verification
    1. Press CTRL+F5 to run the solution.




       Figure 25
       Listing the products


    2. Click Delete on the first product, the one you have added in previous exercises, to delete it.
       Notice that that bike is no longer listed.
        Figure 26
        Deleting the products


    3. Finally, close the browser.




Summary
By completing this hands-on lab you have learned how to build your first web application with SQL
Server 2008 R2 and ASP.NET. More precisely, you have seen how to connect to a SQL Server database
and implement the SELECT, INSERT, UPDATE and DELETE operations using ADO.NET. Additionally, you
have seen how to store and read images in the database.
Here is a quick checklist you can use to get started building your first web application using SQL Server
2008 R2 and ASP.NET:

       Install a SQL Server instance and select Windows Authentication. Select Mixed Authentication
        only if you need support for SQL Server authentication.

       During the installation process, provision at least one Windows user as a member of the SQL
        Server sysadmin server-level role using the Specify SQL Server Administrators option in setup.
        You will use this account after installation to grant access to SQL Server for your administrators,
        developers, users and applications.
   After SQL Server is installed, launch SQL Server Management Studio and log in with a server-
    level principal that has CREATE DATABASE privileges.
   Create your database.

   Change your database context to the newly created database. Do not forget to do this or you
    may make changes to the wrong database.

   Create the necessary database objects to support your application, including tables, views and
    stored procedures. Load initial data as required using Transact-SQL scripts, bulk insert, or the
    Import and Export Data tool.
   Create database roles to represent each type of user in your application.

   Grant the minimum privileges necessary to the database roles that your users and/or
    applications will need to access your database objects.

   Configure the connection string in your application to connect to your SQL Server instance. In
    the connection string, specify the instance you want the application to connect to, the
    authentication type to use (Windows Authentication or SQL Server Authentication) and
    credentials if necessary. Also be sure to specify the name of your database using the Initial
    Catalog attribute.
   Configure your ASP.NET application to connect to SQL Server using either the user’s security
    credentials or an application identity.

								
To top