Overview of SQL Server 2005 Express


Microsoft SQL Server 2005 Express is the version of SQL Server bundled with Visual Web Developer. You can also download this database engine from the Microsoft website (http://msdn.microsoft.com/sql/2005). SQL Server Express is used for almost all the database examples in this book.

In this section, you are provided with a brief overview of the features of this database. You also learn how to connect to SQL Server Express.

Features of SQL Server Express

One of the most important features of SQL Server 2005 Express is that it is a royalty-free database engine. You can download it and use it for free in your applications. You also can distribute the database in commercial applications that you produce for others without paying royalties to Microsoft (registration at the Microsoft site is required to do this).

Note

Microsoft SQL Server 2005 Express replaces the Microsoft Desktop Engine version of SQL Server. There won't be an MSDE version of SQL Server 2005.


SQL Server 2005 Express works with the Windows XP, Windows 2000, and the Windows 2003 operating systems. It requires the .NET Framework 2.0 to be installed on its host computer.

Microsoft SQL Server 2005 Express uses the same database engine as the full retail version of SQL Server 2005. However, because it is a free product, Microsoft has limited some of its features to encourage you to upgrade to the full version of SQL Server 2005.

First, unlike the full version of SQL Server 2005, a SQL Server Express database can be no larger than 4 gigabytes. Furthermore, SQL Server Express is limited to using 1 gigabyte of RAM. Also, SQL Server Express uses only a single processor even when used on a multiprocessor server.

SQL Server Express also does not support several of the advanced features of the full version of SQL Server 2005. For example, it doesn't support Full-Text Search, Reporting Services, Analysis Services, Notification Services, English Query, Data Transformation Services, or OLAP.

However, unlike Microsoft SQL Server 2000 MSDE, SQL Server Express does not have a Workload Governor. The performance of a SQL Server Express database is never throttled. This means that you can use SQL Server Express for small websites without worrying about performance limitations.

Finally, like the full version of SQL Server 2005, SQL Server Express supports the Common Language Runtime. In other words, you can use Visual Basic .NET or C# to create stored procedures, triggers, user-defined functions and user-defined types. You can learn how to create stored procedures and user-defined types written with VisualBasic.NET in Chapter 16, "Building Data Access Components."

SQL Server 2005 Express Management Tools

You can use three tools to create new database objects when using SQL Server 2005 Express. You can use the Database Explorer in Visual Web Developer, you can use the Microsoft SQL Server Management Studio Express, and you can use the SQLCMD utility.

The Database Explorer included in Visual Web Developer provides you with a user-friendly interface for working with database objects (see Figure 8.13). I assume that you are using the Database Explorer in the case of the database samples in this book.

Figure 8.13. The Database Explorer window in Visual Web Developer.


Alternatively, you can use the Microsoft SQL Server Management Studio Express (its groovy name is XM). You can download Management Studio from the Microsoft site at http://msdn.microsoft.com/sql/2005. This tool enables you to browse database objects and execute SQL queries (see Figure 8.14).

Figure 8.14. Using the Microsoft SQL Server Management Studio Express.


Finally, SQL Server 2005 Express includes a command-line tool named SQLCMD. You can use the SQLCMD tool to fire off SQL queries from the Command Prompt (see Figure 8.15). This alternative is the most painful, but it works.

Figure 8.15. Executing a SQL query with SQLCMD.


You use SQLCMD by opening a command prompt and connecting to your database with the following command:

SQLCMD -S .\SQLExpress


Next, you can enter SQL statements at the command prompt. The statements are not executed until you type GO. You can get help using SQLCMD by typing :HELP after starting the tool. When you are finished using the tool, type EXIT to quit.

Server Databases versus Local Databases

You can create two different types of databases with SQL Server Express: Server databases and Local databases.

By default, when you install SQL Server 2005 Express, a named instance of the server is created with the name SQLExpress. You can create a new Server database by connecting to the named instance and adding a new database.

Note

To connect to SQL Server 2005 Express from a page served from Internet Information Server, you must add either the ASPNET account (in the case of Windows XP or Windows 2000) or the Network Service account (in the case of Windows 2003) to SQL Server Express. These accounts are created for you automatically when you install the QuickStart Tutorials included with the .NET Framework SDK 2.0 Documentation.


If you own Visual Studio .NET 2005, then you can create a new Server database directly from the Server Explorer window. Simply right-click the Data Connections node in the Server Explorer window and select the menu option Create New SQL Server Database.

Unfortunately, you can't use Visual Web Developer to create a new Server database. This option is grayed out. If you need to create a new Server database, and you don't have Visual Studio .NET 2005, then you need to use Microsoft SQL Server Management Studio Express as discussed in the previous section (see Figure 8.16).

Figure 8.16. Creating a new Server database.


When you create a Server database, the database is attached and available to any application running on the server. You can connect to the database easily from any ASP.NET application.

For example, the following connection string enables you to connect to a Server database named MyData:

Data Source=.\SQLExpress;Initial Catalog=MyData;Integrated Security=True


Note

There are many different ways to write a connection string that does the same thing. For example, instead of the Data Source parameter, you can use the Server parameter, and instead of the Initial Catalog parameter, you can use the Database parameter. For a list of all the keywords supported when connecting to a Microsoft SQL Server database, see the SqlConnection.ConnectionString enTRy in the Microsoft .NET Framework SDK 2.0 documentation.


The other option is to create a Local database instead of a Server database. When you create a Local database, you create the database in your project. The database file is added to the App_Data folder in your website.

Here are the steps for creating a Local database in Visual Web Developer:

1.

Open the Add New Item dialog box by selecting the menu option Website, Add New Item (see Figure 8.17).

Figure 8.17. Creating a new Local database.


2.

Select Sql Database and provide the database with a name (for example, MyLocalData.mdf).

3.

Click Add.

When you click Add, Visual Web Developer warns you that it needs to create the App_Data folder (if the folder doesn't already exist). The MyLocalData.mdf file will be added to this folder. Click OK to create the new folder.

You can connect to a Local database named MyLocalData.mdf by using the following connection string:

Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|MyLocalData.mdf; Integrated Security=True;User Instance=True


When you connect to the MyLocalData.mdf file, the database is attached automatically to Microsoft SQL Server Express.

The connection string includes an AttachDbFilename parameter. This parameter represents the physical path to a database file (.mdf file). Notice that the keyword |DataDirectory| is used in the path. The |DataDirectory| keyword represents a website's App_Data folder.

Instead of using the |DataDirectory| keyword, you could supply the entire physical path to a database file. The advantage of using the |DataDirectory| keyword is that you can move your web application easily to a new location without needing to change the connection string.

Notice that the connection string also includes a User Instance parameter. Creating a User Instance connection enables you to connect to a Local database without using an Administrator account. Because the ASPNET account is not an Administrator account, you need to add this parameter to use Local databases from ASP.NET pages.

Including the User Instance parameter in a connection string causes a separate user instance of SQL Server to execute with the security context of the user. The first time a user creates a User Instance connection, copies of the system databases are copied to a user's application data folder located at the following path:

[View full width]

C:\Documents and Settings\[Username]\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS


A separate set of system databases is created for each user.

Note

By default, when a page is served from Internet Information Server, the page executes in the security context of either the ASPNET or Network Service account. When a page is served from the web server included in Visual Web Developer, the page executes in the security context of the current user.


One of the primary advantages of using a Local database rather than a Server database is that a Local database can be moved easily to a new location. If you email a Local database file (the .mdf file stored in the App_Data folder) to a friend, your friend can start using the database immediately. The only requirement is that your friend have SQL Server 2005 Express installed on a computer.

I use Local databases for all the code samples in this book. You can simply copy the database files (the .mdf files) from the CD onto your local hard drive to use the sample databases.




ASP. NET 2.0 Unleashed
ASP.NET 2.0 Unleashed
ISBN: 0672328232
EAN: 2147483647
Year: 2006
Pages: 276

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net