Exercise 1: Connecting to a SQL Server Database

In this exercise you will learn how connect to a SQL Server 2012 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:

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 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:

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 “AdventureWorks2012”. 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).

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 AdventureWorks2012 database and perform operations in it as the database owner.

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 2010 Express Edition or Microsoft Visual Studio 2010 from Start | All Programs.

2. Create a new ASP.NET Empty Web Application. To do this, point to File | New | Project. In the Installed Templates list, click Web in the Visual C# node. In the Templates pane, click ASP.NET Empty Web Application and make sure that .NET Framework 4 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.

   Note:

   Watch Out – Launching the Shell Version of Visual Studio 2010

   We often refer to Visual Studio 2010 in a generic way in this Hands-on Lab, but there are many flavors of Visual Studio 2010.

   If you do not see an option for creating a new Web project, you may have launched the wrong flavor of Visual Studio 2010. 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 2010 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 styles.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 styles.css file.

4. Add a new Web Form to the project. To do this, right-click the ConnectingToSqlServer project, point to Add, and click New Item. In the Add New Item dialog, in the Installed Templates list, click Web in the Visual C# node and in the Templates pane, select Web Form. Finally, set the form's name to Default.aspx and click the Add button.

   Figure 2

   Adding New Item dialog.

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

   HTML

   <head id="Head1" runat="server">

   FakePre-4b331618ff8940d1adcd0aa48aab626b-ef9951bbc88a48afacb99215c04a2159 <meta content="text/html; charset=utf-8" http-equiv="content-type" /> <link href="styles.css" rel="stylesheet" type="text/css" />FakePre-65981781e3d1409782791933fb2ff7f0-df96be3842ce43d9b709f2051c1e6317

6. 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>FakePre-efbeaba448654326bb820347359ec8c5-7b69f5b26ebb4004b013165d76e39dfa

   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.

7. Next, we need to configure the connection string. Open the Web.Config file by double-clicking it in the Solution Explorer.

8. Add the connection string for the SQL Server database. To do this, locate the <configuration> tag and add the following bolded lines.

   XML

   <configuration> <connectionStrings> <add name="AdventureWorks" connectionString="Data Source=SQLServerTrainingKitAlias;Integrated Security=True;Initial Catalog=AdventureWorks2012"/> </connectionStrings>

   FakePre-cef2b1b13bcf4a61b5c1e40ad0ee2052-982e48a98348402ba7d03f90a1723cafFakePre-6fc4073ab4e34750884c18b506f09a28-051506d03ea1408aae4feb4f3f247cedFakePre-b1bde552fd7c499980afe89834d5b4c1-90a363c5624f4ee7a7ff3fb578d74a79FakePre-35adfed33ac74330a710f42b8c2011c8-b5235d95579f4e108d6395b4ef642e9eFakePre-6a4b56d2458c4a008bc19fa476705dc3-9b731541fc264d23b78da8928a7c20a1
   

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

10. 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;

11. 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"].ConnectionString)) { connection.Open(); var businessEntityId = 1; var commandText = "SELECT [FirstName] + ' ' + [MiddleName] + ' ' + [LastName] as FullName " + "FROM [AdventureWorks2012].[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; } }

    FakePre-d70dd3518b0f4d04af8d3c109e11055e-014a7c563e7a422d939e666bda93f408FakePre-bdda9e23a61c442cbddd55ac2d746ddf-9870d9d0ca544f35b3fb0ad964ee1305FakePre-7f2b8151af0b48508099f3ff9232ff80-ebab6226f9584fb6bdb0cf85b4491654
    

    In the preceding code, 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. Next, 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.

    Note:

    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.

    Note:

    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.
    Note:

    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.
    Note:

    Best Practice: When 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.

    Note:

    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.
    Note:

    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.

    Note:

    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.

12. Press CTRL + F5 to run the solution.

13. Click the Connect button to retrieve the user login.

    Figure 3

    Retrieving the Company CEO from the SQL Server

14. Close the browser.

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. Finally, you will learn how to page the items in SQL Server.

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.

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:

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.

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.