Before you can create an account with a web-hosting company, you need to find a company that supports the following:

In my opinion, the easiest way to find a web-hosting company that supports ASP.NET 3.5 and offers database accounts, and allows FTP access, is to check sites such as TopHosts.com and HostIndex.com, which list thousands of web-hosting companies. You can search these sites’ databases for web-hosting companies that meet certain criteria, such as cost per month, geographical location, web server platforms used, database availability, and so forth.

Costs for web-hosting companies can range from a few dollars per month to hundreds or thousands of dollars per month, based on the features provided. Additionally, most web-hosting companies have a setup fee in the $20 to $100 range. On top of the web-hosting costs, you will probably want to register a domain name, which typically costs between $10 and $35 per year, depending on the domain name registrar used.

After you have picked out a web-hosting company and have double-checked that it supports ASP.NET development and offers database accounts, contact the company’s sales staff to create an account.

Create a new ASP.NET application and place it on your computer’s file system. Because some additional steps are needed when deploying an application that uses the membership or roles systems, let’s configure this sample application to use membership. Launch the ASP.NET Web Site Administration Tool. From the Security tab, set the website’s authentication to authenticate users from the Internet. This configures the application to use forms-based authentication and automatically creates the ASPNETDB.MDF database file in the App_Data folder.

After configuring the ASP.NET Web Site Administration Tool to authenticate users through the Internet, the Security screen should include a link titled Create user. Click this and add a user account named Admin (see Figure 24.1). Close the ASP.NET Web Site Administration Tool and return to Visual Web Developer.

Figure 24.1. Create a user account named Admin using the ASP.NET Web Site Administration Tool.

In addition to supporting user accounts, let’s have the ASPNETDB.MDF database also store application data. Imagine that this sample website was going to be used as an employee directory, enabling visitors to quickly find contact information for the employees at your company. Such an application would need a database table that included a row for each employee, with columns to capture each employee’s name, phone number, email address, and other pertinent information.

From the Database Explorer, drill into the ASPNETDB.MDF database and add a new table with the following columns:

At this point your screen should look similar to Figure 24.2. Save the table, naming it Employees.

Figure 24.2. The table is composed of four columns: EmployeeID, Name, Email, and PhoneNumber.

Add a handful of records to the Employees table through Visual Web Developer by right-clicking the Employees table in the Database Explorer and choosing the Show Table Data option.

Next, open the Default.aspx page. Let’s build a user interface here to allow visitors to search the Employees table by name. Add the text Search by name: followed by a TextBox and Button control. Set the TextBox control’s ID property to NameSearch and the Button’s ID and Text properties to SearchButton and Search, respectively.

Beneath the Button, add a SqlDataSource control and name it EmployeesDataSource. Configure the data source to return the Name, Email, and PhoneNumber columns from the Employees table.

Click the WHERE button and add a WHERE clause on the Name column. Specifically, set the Column drop-down to Name, the Operator to LIKE, and the Source to Control, choosing the NameSearch option from the Control ID drop-down list (see Figure 24.3). Click the Add button to add the WHERE clause to the SqlDataSource control’s SELECT statement and then click OK to return to the wizard.

Figure 24.3. Add a WHERE clause to the SELECT statement to query on the Name column.

Click the ORDER BY button and select the Name column from the drop-down list so that the results are sorted alphabetically by employee name. At this point the SqlDataSource’s SELECT statement reads:

SELECT [Name], [Email], [PhoneNumber] FROM [Employees] WHERE ([Name] LIKE '%' + @Name + '%') ORDER BY [Name]

Click Finish to complete the wizard.

This WHERE clause returns the list of employees whose Name column value contains the text entered by the user in the NameSearch TextBox control. But what is displayed if the user leaves the NameSearch TextBox empty? By default, the SqlDataSource uses a database Null value when one of its parameters lacks any value, resulting in a WHERE clause of: WHERE ([Name] LIKE '%' + Null + '%'). Concatenating a database Null value with other string characters (such as %) results in a database Null value. Therefore, the WHERE clause is boiled down to: WHERE ([Name] LIKE Null). No columns match this condition. As a result, if the user omits a value from the NameSearch TextBox, no records are returned from the SqlDataSource control.

To have the SqlDataSource return all the Employees records when no value is entered into the NameSearch TextBox, we need to configure the parameter so that it does not convert an empty value in the NameSearch TextBox to a database Null value. To accomplish this, select the SqlDataSource to load its properties in the Properties window. Go to the SelectQuery property and click the ellipses to bring up the Command and Parameter Editor. Select the Name parameter from the list of parameters on the left and then click the Show advanced properties link on the right. Set the ConvertEmptyStringToNull property to False, as shown in Figure 24.4, and then click OK to close this dialog box.

Figure 24.4. Configure the Name parameter so that it does not convert empty string values to Nulls.

With the SqlDataSource control added and configured, add a GridView control to the page, binding it to the EmployeesDataSource control. Configure the GridView to enable paging and sorting.

Take a moment to test the page through a browser. When the page is first visited, you should see all the employees listed along with their contact information. You can sort the grid by any of these fields or narrow down the list of employees by typing a name into the NameSearch text box and clicking the Search button. Figure 24.5 shows the results when filtering the list of employees by those that contain the letter “s” somewhere in their name.

Figure 24.5. Employees whose name contains the letter “s” are displayed.

At this point, our application is complete, tested, and ready to be deployed! After a web-hosting account has been procured, deployment involves three steps: uploading the website’s files; replicating the database; and updating the connection strings in web.config.

To move files from your local computer to your web-hosting company, go to the Website menu in Visual Web Developer and choose Copy Web Site. This brings up the Copy Web interface, which lists the contents in your website and the contents of a remote site in side-by-side panes. Figure 24.6 shows this screen when a remote website has not yet been specified. The left column lists the contents of the local website—Default.aspx, Default.aspx.vb, Web.config, and the App_Data folder.

Figure 24.6. The Copy Web screen.

To connect to your web-hosting company, click the Connect button at the top of the screen. This brings up the Open Web Site dialog box. Select the FTP Site option and then enter the connection information. If you are unaware of the server, port, directory, and other FTP configuration information, contact your web-hosting company’s support staff.

After supplying the FTP information and clicking Open, the Copy Web screen lists the contents of the remote website. You can copy files between your local computer and the remote website by selecting a file from one column and then clicking the appropriate arrow icon.

When deploying a website for the first time, copy over all files except for the Microsoft SQL Server 2005 Express Edition database files (the .mdf files in the App_Data database). If you are redeploying the site after making additions or changes, you need to upload only the new and modified files.

Because this is the first deployment for this sample web application, upload all the files in the root directory: Default.aspx, Default.aspx.vb, and Web.config. Figure 24.7 shows the Copy Web screen after these files have been copied over.

Figure 24.7. Upload Default.aspx, Default.aspx.vb, and Web.config to the remote site.

As we have seen throughout the latter half of this book, Visual Web Developer and Microsoft SQL Server 2005 Express Edition make it easy to create and manage databases. Traditionally, working with databases required installing and configuring a database server. But with SQL Server 2005 Express Edition, new databases are simply added to the web application’s App_Data folder.

Unfortunately, Microsoft SQL Server 2005 Express Edition introduces security and configuration issues for web-hosting companies. Consequently, few, if any, web-hosting companies support Express Edition. Instead, most require that you transfer your database to their database server.

Moving your database to your web-hosting company’s database server involves replicating the database’s tables and their data. There are a variety of ways to accomplish this:

The first two options are the simplest and don’t require any further examination. The third option—using the Database Publishing Wizard—deserves more discussion.

Using the Database Publishing Wizard

The Database Publishing Wizard is a tool created by Microsoft that is included with Visual Web Developer 2008. To launch it, go to the Database Explorer, right-click on the database to publish, and choose the Publish to Provider menu option. This launches the Database Publishing Wizard.

The Database Publishing Wizard starts by displaying a welcome screen that explains its purpose. The subsequent screen, shown in Figure 24.8, prompts for the database to replicate.

Figure 24.8. Specify the database to replicate.

By the Way

By default, the Database Publishing Wizard replicates all database objects to the destination server. If you want to replicate only a subset of database objects, uncheck the Script All Objects in the Selected Database check box shown at the bottom of Figure 24.8. If this option is unchecked, the subsequent screens have you select what database objects to copy over.

After specifying the source database, the wizard asks where to publish the database. There are two options:

• Script to File—. This choice creates a script file with the necessary SQL commands to replicate the source database’s structure and data.

• Publish to a Shared Hosting Provider—. The Database Publishing Wizard can publish the database changes directly to the remote database server if the web-hosting company supports Microsoft’s SQL Server Database Publishing Services protocol. Check with your web-hosting company to see if they offer this support.

The Script to File option works for any web-hosting company, regardless of whether they support Microsoft’s SQL Server Database Publishing Services protocol. Therefore, let’s look at using this option. Select the Script to File option and choose the location to place the file. Then click Next to review the settings.

After you have verified that the appropriate database objects are being copied over, click Finish to begin the scripting process (see Figure 24.9).

Figure 24.9. The wizard is generating the SQL scripts needed to copy the source database to the web-hosting company’s database server.

Executing the Output Script on the Web-Hosting Company’s Database Server

At this point the Database Publishing Wizard has created the scripts to replicate the source database’s content and data. Our final step is to execute this script file on the web-hosting company’s database servers. The easiest way to execute a script file on a database is to download Microsoft SQL Server Management Studio Express Edition, which is a free application for managing Microsoft SQL Server databases.

Did you Know?

If you have any of the non-Express Editions of Microsoft SQL Server 2005 installed on your computer, chances are you have SQL Server Management Studio installed on your computer. If this is the case, you can use the already installed version and do not need to download SQL Server Management Studio Express Edition.

To download Microsoft SQL Server Management Studio Express Edition, visit www.microsoft.com/downloads/details.aspx?FamilyID=c243a5ae-4bd1-4e3d-94b8-5a0f62bf7796&displaylang=en.

By the Way

The Microsoft SQL Server Management Studio Express Edition download page includes two download options: SQLServer2005_SSMSEE.msi and SQLServer2005_SSMSEE_x64.msi. You’ll likely want to download the first option, as the latter one is for 64-bit systems.

After you’ve downloaded and installed Management Studio, launch it. Management Studio starts by asking you what server to connect to. Go ahead and click Cancel for now—we’ll provide this information later.

Go to the File menu and select the Open child menu. Next, select to open a file and browse to the file created by the Database Publishing Wizard. After selecting the file, Management Studio again prompts for the database server name and credentials. Fill in the server name and credentials needed to connect to the web-hosting company’s database server (see Figure 24.10).

Figure 24.10. Connect to the web-hosting company’s database server.

After entering the database server name and your credentials, Management Studio displays the contents of the selected file in a query window. Go to the Query menu and choose Execute to run this query on the database server.

By the Way

It may take anywhere from several seconds to several minutes for the SQL script file to finish executing. The time required depends on how many database objects were created by the Database Publishing Wizard, how many records were in these tables, the processing power of the database server, and other related factors.

To verify that the database has been replicated on the web-hosting company’s database server, go to the File menu and choose the Connect Object Explorer file option. This redisplays the Connect to Server dialog box shown in Figure 24.11. Reenter your credentials and click Connect.

Figure 24.11. The local database’s tables have been replicated to the web-hosting company’s database server.

The Object Explorer lists those database objects on the server that you have permission to examine. Drill down into the Databases folder and into your database. Expand the Tables folder. You should see the same set of Tables on the web-hosting company’s database server that is on your local database.

By the Way

Keep in mind that the Database Publishing Wizard replicates both the database schema and the database data. The Admin user account and the records you added to the Employees table through Visual Web Developer have been replicated to the remote database server.

After the database has been replicated, the final step is to update the connection string settings in web.config so that they point to the web-hosting company’s production server. You need to make these changes on the web.config file on the web-hosting company’s computer. To accomplish this, modify the web.config file on your computer and then upload it to the web-hosting company through the Copy Site screen. After uploading the web.config file to the web-hosting company, you may undo the changes so that the local web.config file’s connect strings still point to the local SQL Server database in the App_Data folder.

The connection string value in web.config should look similar to the following:

Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True

Replace this string with the connection string information provided by your web-hosting company. The web-hosting company will provide you with a database server, a database name, and a username and password. If the database is a SQL Server 2005 server, use the following connection string value:

Data Source=databaseServer;Initial Catalog=databaseName; User Id=username;Password=password;

For alternative databases, such as SQL Server 2000 or SQL Server 2008, consult www.ConnectionStrings.com for sample connection strings. If you have trouble formulating the correct connection string, contact the web-hosting company’s support for assistance.

If your web application uses the membership or roles systems, you will need to update the web.config at the web-hosting company to include an additional connection string setting. By default, the membership and roles systems use a hard-coded connection string named LocalSqlServer to connect to the database where user account and role information is stored. This connection string points to the ASPNETDB.MDF database in the App_Data folder. The remote web application, however, does not have an App_Data folder; the user accounts and roles systems are instead stored on the database server.

We need to update the LocalSqlServer connection string to point to the database server. If you forget to do this, you will get an error message when visiting a page that attempts to work with user accounts or roles.

Currently, the <connectionStrings> section in web.config looks similar to the following:

<connectionStrings>
    <add name="ConnectionString" connectionString="connectionString"
        providerName="System.Data.SqlClient" />
</connectionStrings>

We need to add two additional XML elements within the <connectionStrings> element:

After making these additions, your web.config file’s <connectionStrings> section should look like the following:

<connectionStrings>
    <clear />

    <add name="ConnectionString" connectionString="connectionString"
        providerName="System.Data.SqlClient" />

    <add name="LocalSqlServer" connectionString="connectionString"
        providerName="System.Data.SqlClient" />
</connectionStrings>

The connectionString values in the two <add /> elements should be the same, pointing to your database on the web-hosting company’s database server.

There are no exercises for this hour.