Creating a Database in Web Matrix


You ll find data access in ASP.NET easiest to understand by creating a database and working with it in a Web Forms page. Therefore, I ll walk you through the process of creating a database and table and entering some data. In the next section, you ll create a page to display the data.

Before you begin, make sure you have MSDE or SQL Server installed and running. For details about how to install MSDE, see Chapter 3. MSDE runs as a service (background task). When it s running, you ll see an icon that is a computer with a green arrow in the system tray. The tooltip for this icon is MSSQLServer. If you don t see the icon, MSDE isn t running and you probably need to restart your computer.

Creating the Database

You ll create a database you can use for the work you ll do in this book. To start, you need to create a connection in Web Matrix to MSDE.

Establish a connection to MSDE

  1. Open Web Matrix. To establish a connection, you don t need to have a page open.

  2. In the Workspace window on the right, click the Data tab to display the Data window. Right now, the Data window is empty.

  3. In the toolbar of the Data window, click the New Connection button. Web Matrix displays the Connect To Database dialog box, as shown in the following illustration:

    click to expand

  • Determine the name for your local instance of MSDE. In the Windows system tray, double-click the MSDE icon. In the SQL Server Service Manager dialog box, look at the Server box. The name in that box is the name of your MSDE instance. Make a note of it, because you ll need that name whenever you make another connection to a database in MSDE. Close the dialog box when you re finished.

  • In the Web Matrix s Connect To Database dialog box, enter the MSDE instance name in the Server box. If your instance name is localhost, you can leave the default value; otherwise, delete the default value and enter your instance name.

  • Make sure the Windows Authentication option button is checked. Because MSDE is installed on your computer, you have permission to connect to it using your current user account.

  • At the bottom, click the Create A New Database link. When the Enter Information dialog box appears, type in WebMatrix (one word) as the name of your new database, and then click OK.

    Tip 

    If you get the error message Unable to connect to the database. Cannot generate SSPI context, you re probably not using the right MSDE instance name. Check the name again as described in step 4, and type it in the Server box.

    A connection to your new database appears in the Data window, which might look like the following illustration:

  • The Tables and Stored Procedures nodes have a plus sign (+) next to them, but they re just kidding nothing is there yet.

    The next step is to create a table to store data. You ll start with a table named Friends that will hold names and e-mail addresses. Each friend must have a unique identifier. Although you could use your friends names as their identifiers, that idea isn t foolproof because some of your friends might have the same name. So you ll just assign them sequential numbers.

    Create the Friends table

    1. In the Data window, select the Tables node of the connection you just created.

    2. Click the New Item button in the Data window toolbar, which displays the Create New Table dialog box as shown here:

      click to expand

  • In the Table Name box, type Friends.

  • Underneath the Columns box, click New to create the first column. By default, the new column will be named Column1, but you ll change that.

  • On the right-hand side, change the name to FriendID and for the data type, select Int.

    The data type Int is the MSDE/SQL Server equivalent to the ASP.NET data type Integer that you ve used before. MSDE and SQL Server use slightly different names for their data types than ASP.NET, and in this step you re setting an MSDE data type.

  • Because this column is the identifier the primary key do the following:

    • Check Required.

    • Check Primary Key.

    • Check Auto-Increment. This assigns the next sequential number to each friend.

  • Click New to create a second column, and name it FirstName. Set its data type to VarChar. Then set Default to ('') two single quotes inside parentheses and set the field size to 30. The exact field size isn t important, as long as you allow a generous amount for the largest possible first name you ll ever get.

    Tip 

    To learn more about the recommendations I m making for the column settings, see the sidebar More About Defining Columns.

  • Create a third column named LastName (VarChar, 45 characters, same default value as the second column) and a fourth one named E-mail (VarChar, 30 characters, same default value as the second column). At this point, the Create New Table window will look like the following illustration:

    click to expand

  • Tip 

    If you click OK too early by mistake, don t worry. Double-click the Friends table in the Data tab. In the Edit Table window, click Edit/ View Table Design at the bottom to display the Edit Table Design dialog box and continue designing your table.

    The table definition is finished now, but it contains no data. Later in the book you ll work with data-entry pages. For now, however, let s just do some quick-and-dirty data entry directly into the database.

    start sidebar
    More About Defining Columns

    When you create columns for tables in MSDE, you can specify a variety of attributes. One of the most important columns is the primary key column, which contains the data that will uniquely identify each record in the table. Naturally, each primary key value has to be unique for that table. If the data you re working with doesn t have an obvious candidate for a unique identifier, such as a Social Security number or a student number, it s almost always a good idea to use an auto-incrementing field of type Int. The starting value for an auto-incrementing primary key usually doesn t matter, although you can set it to start at any value you like.

    By the way, some unique values, such as phone numbers, aren t necessarily good candidates for the primary key. You generally don t want to use a primary key value that might change over the lifetime of the data. If you have to change a primary key value, you can end up losing the association between the primary key and the data it represents.

    In a SQL Server database you can define text (character) columns using different data types. Your basic choices are Char or VarChar, or their Unicode variants, NChar and NVarChar. You use the Unicode NChar or NVarChar types if you intend to work with characters in foreign scripts such as Chinese or Arabic, but because they store Unicode characters, these data types require twice the space of their non-Unicode equivalents. If your text will use the Latin alphabet, use Char or VarChar. (SQL Server also has a data type called Text but it s used to store entire documents, so you don t need that type here.)

    Char and NChar always store a fixed number of characters, even if the column contains no value. You define VarChar and NVarChar with a maximum size (up to 8,000 bytes for VarChar and 4,000 bytes for NVarChar), but the data takes up only the space it needs, not the full width of the column.

    The rule of thumb I ll suggest to you is this: if you have truly fixed-length data, such as a state abbreviation or a five-digit ZIP code, make your text fields Char with a fixed length. But if your text data is going to vary from record to record, use VarChar. Developers who need to eke out every last bit of performance from their databases worry about the comparative performance of the two data types. For the applications you ll create, the relative efficiency of one type over the other is inconsequential.

    Another issue when defining columns is that of null values. If a column has never had a value entered into it, the column s value is null. Null values in a column can sometimes cause problems; for example, you can t convert a null to a string, such as to display it in a control.

    You can avoid nulls in a couple of ways. One way is to define a default value for a column. When a new record is written to the database, if no value is available for a column, MSDE inserts the default value.

    For columns of type Char and VarChar, the default is easy use '', sometimes called an empty string. (Note a subtle difference. An empty string is a string with zero characters in it; a null is no value at all and is not a string.) Setting default values for other data types is trickier. For example, it s logically impossible to define a default value for a Boolean (bit) column because either value you put in, true or false, might be a valid value.

    A more stringent approach to avoiding nulls is to make every field required. That way, you can guarantee that you end up with no null values in your columns. However, enforcing that data is entered for every column might not prove practical in every case, such as when you don t have (and don t need) a value in a given column.

    The general lesson here is that when defining database columns, you want to think both flexibly (hence VarChar or even NVarChar) as well as defensively (hence default values and required fields to avoid nulls in your database). As always, some care up front in database design pays dividends down the road.

    end sidebar

    Enter data into your database table

    1. In the Data window, double-click the Friends table. The Edit Table dialog box appears, which is a data-entry grid for your new table.

    2. Click the first row to start editing. Because the FriendID column is an auto-increment column, it will contain -1, which indicates that the ID will be assigned when you save the row. You can t change the value, and it will be negative until you add a new row to the table.

    3. Enter a first name, last name, and e-mail address, and then press ENTER to save the new row.

    4. Enter some more rows, perhaps five or six in total. The screen might look something like the following illustration:

      click to expand

    Now you re set you ve got a database with a table in it, and data in the table.




    Microsoft ASP. NET Web Matrix Starter Kit
    Microsoft ASP.NET Web Matrix Starter Kit (Bpg-Other)
    ISBN: 0735618569
    EAN: 2147483647
    Year: 2003
    Pages: 169
    Authors: Mike Pope
    BUY ON AMAZON

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