Project Resources

Note: I have tried to trim some of the resources out that no longer apply to the current project. If you have resources that you find valuable that you think should be listed send me an email.

Local Resources

Web Resources

Getting Started With SQL Server

We will be using Microsoft SQL Server 2005 running on iisqlsrv.cs.washington.edu. Accounts on it have been set up for each of you, and you have been given your user names and passwords. For the first homework, you need to set up an interactive query session with the server. For this, you will need access to SQL Server Management Studio, which is installed on the machines in the undergrad labs. You will it for both administrative tasks and interactive querying. The first thing you need to do is register the server, and change your password.

For the first homework, you will use Management Studio to connect to the server, and run SQL queries. This will involve the following steps:

The variant of SQL supported by SQL Server is called Transact-SQL. In addition to executing all Transact-SQL statements, management Studio can also be used to view query execution plans, statistics, and more.

You are welcome to create and play with your own databases. You can either use SQL statements for this, or you can use Management Studio.

Management Studio Manual

Importing Data Into SQL Server

At some point in the homework or the project you are going to need to import data into SQL server from a text file. There is good news and bad news. The good news is Management Studio has a nice wizard for importing data, the bad news is that it doesn't work. =) To work around this, you can import the data first into a MS Access database then use the wizard in Management Studio to import that database into SQL Server. To do that, follow these stpes:

Setting Up Your Webspace

The server we have for publishing ASP.NET applications is the same server that we use to host the database, IPROJSRV. Unfortunately there are many ways to write buggy ASP.NET code that will crash the server in such a way that it needs reset. Thus you can only use IPROJSRV to publish your final product. For development and debugging, you must use a lab machine, or your own computer.

Configuring Workstation for Development

Setting up a lab workstation for ASP.NET development is a royal pain in the you know what. So if you have your own laptop with XP Professional, IIS and Visual Studio, I would suggest using that!

If you must use a lab computer, there are instruction here.

Publishing Your Project

Map your folder on IPROJSRV to your local machine. (Instructions for XP)

Copy everything from your project directory into your newly mapped drive.

Now you should be able to see your project at http://iprojsrv.cs.washington.edu/phase1/[YOUR UW email]/default.aspx.

Getting the Most From Visual Studio

We recommend using Visual Studio for your projects in this class. The lab machines already have Visual Studio 2003 installed. Visual Studio is packed with useful features for .NET development. I'm just going to list a couple here that I think get overlooked alot.

When working with web forms, avoid embedding the C# code in your web form. You can use what’s called 'code behind' to separate the C# into *.cs files. Using code behind lets use take advantage of Visual Studio's intellisinse and syntax correction features. (The starter files will have examples of codebehind.)

Connecting to SQL from .NET

To connect to a SQL database in .NET, you use the classes in the System.Data.SqlClient namespace. The following example shows how you would set up and connect to the movies database on IPROJSRV.

private SqlConnection sqlConn; // Declare a connection object
sqlConn = new SqlConnection("server=IPROJSRV.cs.washington.edu; uid=yourID; pwd=yourPassword; database=movies");
sqlConn.Open(); // open the connection

Once you are connected to the database, you also use the APIs to execute queries. The following example shows how to select all rows from the actors table.

private SqlCommand sqlCmd;    // Declare a new sql command
private DataReader myResults; // Declare a new data reader

sqlCmd = new SqlCommand("SELECT * FROM actors", sqlConn); // create a new command using an SQL statement
myResults = sqlCmd.ExecuteReader(); // executes command on server and returns a reader 

if (myResults.HasRows) {
    // do something with the data
}

Once you have finished using the sqlConnection on a page, make sure you close it so that it can return to the connection pool.

sqlConn.Close(); // close the connection

Your Group Webspace

Your group webspace works just like your personal webspace did in phase 1 only this time the folder is //iprojsrv/groups/[GROUP NAME]. [GROUP NAME] is the name of your group that you submitted, or an arbitrarily chosen group name if you failed to submit one on time. All of your group members should have access to the group folder.

You can also use your group folder on IPROJSRV for a CVS repository if you like. Check out the TortoiseCVS tool for CVS in the Windows shell.

Cross Database Queries

Some of the tasks in Phase 2 will require you to do a cross-database query. In order to do so you need to grant your teammates access to your database. The easy way to do this is by using a GRANT query. (This can also be done using Management Studio, but that's more difficult.)

USE [Your Database];
GRANT SELECT, INSERT, UPDATE, DELETE TO [teammate1], [teammate2]

Then when you're writing your queries, you can refer to your teammate's tables as [teammate1's_database].dbo.[table_name]. Example:

SELECT *
FROM nbales_inventory.dbo.products AS a, akgupta_inventory.dbo.products AS b
WHERE a.upc_code = b.upc_code

The same example in join syntax:

SELECT *
FROM nbales_inventory.dbo.products AS a
    INNER JOIN akgupta_inventory.dbo.products AS b ON a.upc_code = b.upc_code

Maintaining State Across Web Forms

But wait . . . there are different meanings to "across web forms."

On the same page when it has to post back to the server.

This one Microsoft has done its best to take care of for you! Yay! The concept they use is called "viewstate." Basically its just an invisible form field that holds a bunch of encrypted data about the page state. It gets sent back and forth between the client and the server as long as the same page is being requested over and over. If you want to add your own state to the viewstate then use the ViewState object. It's basically a name value collection that you can access via Page.ViewState[key].

Between two different pages in a users session.

There are two ways to accomplish this. One is to use the Session object, the other is to use cookies. The Session object is preferred because cookies are limited size and a lot of people naively reject cookies. The Session object, like the ViewState object, is a name value collection that you can access via Page.Session[key].

Between two sessions by the same user.

There are also two ways to persist state across separate user sessions, cookies and keeping it in the database. Both have advantages. Cookies are stored on the client side and can thus help you remember who this is. You have to use cookies if you want users to automatically log into something. On the other hand, cookies can't hold much data, so if you wanted to remember what products a user has looked at, for example, this wouldn't fit in the cookie and must be stored in the database. If you want to write to the cookie, use the Response object: Page.Response.Cookies[key] = value;. To read cookies use the request object: value = Page.Request.Cookies[key];.