Creating a Database for ASP .NET
Posted to: http://vbstudygroup.com for the students in Principles of Computer Programming at Seminole Community College, Sanford, Florida. The content of this document is an outline of Module 2.1 from “Data Access in the ASP.NET 2.0 Framework” by Stephen Walther and published by Sams Publishing. There are over four hours of videos on this DVD that would be very good to help new programmers get started with using SQL in Visual Basic 2005 or 2008.
Create a new database
Go to the pull down menu of Website Select Add New Item Select SQL Server Database Give the database a meaningful name Select Add Answer Yes to the dialogbox Once the database is created, notice that a new window is added to the project. If you are using the Express versions this window is titled “Database Explorer”. If you are using the Professional version the window title will be “Server Explorer. Look at the new window and you will see the new database you just created and all of its objects.
Add/Create a database table
Right-click the Tables object in the Server Explorer window Select Add New Table The Add New Table dialog box will appear
Creating the columns (fields) of the database
In the Column Name box type ID In the Data Type box select or type in “int” (for integer) Uncheck the Allow Nulls box which means the ID column can never be empty. Identity field Make the ID column an Identity Field which automatically increments the number in this field so it is always unique.
Go down to the Column Properties window. Scroll down to the Identity Specification row. Select the plus sign to expand the Identity Specification row. Double-click on (Is Identity) so it changes the No to Yes. (If the field is greyed out, make sure you set the Data Type to ‘int’.) The Primary Key field In many instances a column will contain a unique number to use as a reference number for each record. This is called a Primary Key. In this example, we will make the ID column the Primary Field. To make the ID column the Primary Field, select the right arrow next to ID or the ID Column Name box or the Data Type for the ID box. Select the gold key icon. (Another method is to right click any of the boxes on the ID row and from the pop-up menu select, “Set Primary Key”.) A gold key should now appear in the box to the left of the box that says ID. Add the rest of your fields Select the next Column Name box (right under ID) and add the rest of the fields you have designed for your database. NOTE: It is always better to have more characters allocated than needed for string fields such as nvarchar(xx). You may not use all of them but it is difficult to add more after the tables are created and the database contains data. For example: don’t use nvarchar(10) for the LastName field; it’s not enough. Use something more like nvarchar(50). NOTE: Do not use spaces in column/field names. Data Types The main data types you are most likely to use are: int – integer nvarchar – a variable length character field. The ‘n’ means it accepts Unicode or international character. You can change the maximum length. datetime – to enter dates and times. If you want the current date and time to be entered automatically, say for example when a ticket is sold, go sown to the Column properties and in the Devault Value or Binding row type, GetDate(). That will read the computer’s clock when the field is populated.
Save the table
Select the disk icon, type Ctrl+S, or go to the pull down menu of File and select Save Table1. Enter the name for your table.
Pick OK
Add sample data to a field
Go to the Server Explorer window Select the plus sign next to Tables to expand and make visible all of the tables in the current database. Right-click on the new table name Select “Show Table Data This page allows you to edit the contents of the fields except for the ID and DateSold field. These fields will be entered automatically when a new query is run by selecting the exclamation point icon.
Modify the page that will display the data in the database
Drag the table from the Server Explorer to the webpage that will display. Note that this action adds a GridView and a DataSource control to the page. All fields are displayed which is often not what you want. Erase the GridView and the DataSource control. This time, go to the Toolbox and expand (pick the plus sign next to) the Data Toolbox. Drag a GridView to the page that will display the data. Select the small arrow at the top right corner of the GridView. Select the down arrow next to Choose Data Source. Select New Data Source. On the Data Source Configuration Wizard window, select Database. You could change the name but for this example we will accept the default. Select OK. On the Configure Data Source – SqlDataSource1 dialog box, select New Connection. If asked, select Microsoft SQL Server Database File (because we used SQL Express to create this database). Select Continue. Browse to the path and name of the new database. Select Test Connection. Select OK. Pick the plus sign to see the connection string.
Select Next. The next page will ask if you want to save the connection in the application configuration file. Answer yes and accept the default name. Select Next. The next page allows you to select which fields you want retrieved from your database; * means all columns. You usually want to be selective not choosing all. Select Next. This screen displays the new query string built by the wizard. Note it’s syntax. Select Test Query and it will display the data requested. Select Finish.