Reading and Writing a SQL Server Database

Most Web applications need access to a database to read data from and write data to it. In this section, you create a new database to store event information, and see how to use this database from ASP.NET.

First, you create a new SQL Server database in the next Try It Out. This can be done directly from within Visual Studio 2005.

Try It Out – Create a New Database

1. Open the previously created Web application EventRegistrationWeb, using Visual Studio 2005.

2. Open the Server Explorer. If you cannot already see it in Visual Studio, you can open the window using the menu options View Other Windows Server Explorer.

3. In the Server Explorer select Data Connections, right-click to open the context menu, and select Create New SQL Server Database....

4. The dialog shown in Figure 18-23 opens. Enter localhost for the server name, and BegCSharpEvents for the database name.

   
   Figure 18-23

5. After the database is created, select the new database in Server Explorer.

6. Select the entry Tables below the database, and select the Visual Studio menu Data Add New Table.

7. Now enter the column names and data types as shown in Figure 18-24.

   
   Figure 18-24

8. Configure the ID column as a primary key column with an identity increment of 1 and an identity seed of 1. All columns should be configured not to allow nulls.

9. Save the table with the name Events.

10. Add a few events to the table with some sample titles, dates, and locations.

Displaying Data

To display and edit data there's a separate Data section in the Toolbox, representing data controls. The data controls can be categorized into two groups: data view and data source controls. A data source control is associated with a data source such as an XML file, a SQL database, or a .NET class; data views are connected with a data source to represent data.

The following table lists all the data controls.

Control	Description
GridView	The GridView control displays data with rows and columns.
DataList	The DataList control just displays a single column to display all items.
DetailsView	The DetailsView control can be used together with a GridView if you have a master/detail relationship with your data.
FormView	The FormView control is used to display a single row of the data source.
Repeater	With the Repeater control you have to define what HTML elements should be generated around the data from the data source.

The data source controls and their functionality are listed in the next table.

Control	Description
SqlDataSource	The SqlDataSource control accesses the SQL Server or any other ADO.NET provider (for example Oracle, ODBC, and OLEDB). Internally, it uses a DataSet or a DataReader class.
AccessDataSource	The AccessDataSource control enables you to use an Access database.
ObjectDataSource	The control ObjectDataSource allows you to use .NET classes as the data source.
XmlDataSource	XML files can be accessed with the XmlDataSource control. Using this data source, hierarchical structures can be displayed.
SiteMapDataSource	The SiteMapDataSource uses XML files to define a site structure for creating links and references with a Website. This feature is discussed in Chapter 19.

In the next Try It Out, you will use a GridView control to display and edit the data from the previously created database.

Try It Out – Using a GridView Control to Display Data

1. In the Solution Explorer create a new regular folder Admin.

2. Create a new Web page EventsManagement.aspx in the folder Admin.

3. Add a GridView control to the Web page.

4. In the Choose Data Source combo box of the control's smart tag, select <New data source...>.

5. The dialog shown in Figure 18-25 now opens. Select Database and enter the name EventsDataSource for this new data source.

   
   Figure 18-25

6. Click the OK button to configure the data source. The Configure Data Source dialog will open. Click the New Connection button to create a new connection.

7. With the Add Connection dialog shown in Figure 18-26 enter localhost as Server name, and select the previously created database BegCSharpEvents. Click the Test Connection button to verify that the connection is correctly configured. When you're satisfied that it is, click the OK button.

   
   Figure 18-26

8. The next dialog opens (to store the connection string, as shown in Figure 18-27). Click the check box to save the connection and enter the connection string name EventsConnectionString. Click the Next button.

   
   Figure 18-27

9. In the next dialog, select the Events table to read the data from this table, as shown in Figure 18-28. Select the ID, Title, Date, and Location columns to define the SQL command SELECT [Id], [Title], [Date], [Location] From [Events]. Click the Next button.

   
   Figure 18-28

10. With the last window of the Configure Data Source dialog, you can test the query. Finally, press the Finish button.

11. In the designer, you can now see the GridView control with dummy data and the SqlDataSource with the name EventsDatasource, as shown in Figure 18-29.

    
    Figure 18-29

12. For a more beautiful layout of the GridView control, select AutoFormat from the smart tag and select the scheme Lilacs in Mist, as shown in Figure 18-30.

    
    Figure 18-30

13. Start the page with Visual Studio 2005, where you will see the events in a nice table like that shown in Figure 18-31.

    
    Figure 18-31

How It Works

After you add the GridView control, you can see its configuration in the source code. the DataSourceID attribute defines the association with the data source control that can be found after the grid control. Within the <Columns> element all bound columns for displaying data are shown. HeaderText defines the text of the header and DataField defines the field name within the data source.

The data source is defined with the <asp:SqlDataSource> element, where the SelectCommand defines how the data is read from the database, and the ConnectionString defines how to connect with the database. Because you selected to save the connection string in the configuration file, <%$ is used to make an association with a dynamically generated class from the configuration file.

 <asp:GridView AutoGenerateColumns="False" BackColor="White"  BorderColor="White" BorderStyle="Ridge" BorderWidth="2px" CellPadding="3" CellSpacing="1" DataKeyNames="Id"  DataSource GridLines="None"  runat="server"> <FooterStyle BackColor="#C6C3C6" ForeColor="Black" /> <RowStyle BackColor="#DEDFDE" ForeColor="Black" /> <Columns> <asp:BoundField DataField="Id" HeaderText="Id"  InsertVisible="False" ReadOnly="True" SortExpression="Id"></asp:BoundField> <asp:BoundField DataField="Title" HeaderText="Title"  SortExpression="Title"></asp:BoundField> <asp:BoundField DataField="Date" HeaderText="Date"  SortExpression="Date"></asp:BoundField> <asp:BoundField DataField="Location" HeaderText="Location"  SortExpression="Location"></asp:BoundField> </Columns> <PagerStyle BackColor="#C6C3C6" ForeColor="Black"  HorizontalAlign="Right" /> <SelectedRowStyle BackColor="#9471DE" Font-Bold="True"  ForeColor="White" /> <HeaderStyle BackColor="#4A3C8C" Font-Bold="True" ForeColor="#E7E7FF" /> <EditRowStyle Font-Bold="False" Font-Italic="False" /> </asp:GridView> <asp:SqlDataSource  ConnectionString="<%$ ConnectionStrings:EventsConnectionString %>"  runat="server" SelectCommand= "SELECT [Id], [Title], [Date], [Location] FROM [Events]"> </asp:SqlDataSource> 

In the web.config configuration file you can find the connection string to the database:

 <connectionStrings> <add name="EventsConnectionString"  connectionString="Data Source=localhost; Integrated Security=True;Initial Catalog=BegCSharpEvents; Pooling=False" providerName="System.Data.SqlClient" /> </connectionStrings> 

Now the GridView control should be configured differently. In the next Try It Out, the ID will no longer be displayed to the user, and the date time display will only display the date.

Try It Out – Configure the GridView Control

1. Select the smart tag of the GridView control and select the Edit Columns menu. The Fields dialog, shown in Figure 18-32, shows up. Select the ID field, and change the Visible property to False. You can also arrange the columns with this dialog, and you can change the colors and define the header text.

   
   Figure 18-32

2. For editing the GridView, an update command must be defined with the data source. Select the SqlDataSource control with the name EventsDataSource, and select Configure Data Source... from the smart tag. With the Configure Data Source dialog click the Next button until you can see the previously configured SELECT command. Click the Advanced... button, and select the check box Generate INSERT, UPDATE, and DELETE statements as shown in Figure 18-33. Click the OK button. Then click the Next and Finish buttons.

   
   Figure 18-33

3. Select the smart tag of the GridView again. Now, there's a new item in the smart tag menu where you can enable editing (see Figure 18-34). After you've selected the check box to enable editing, a new column is added to the GridView control. You can also edit the columns with the smart tag menu to arrange the new Edit button.

   
   Figure 18-34

4. Now, you can start the application and edit the existing event records.

How It Works

No line of code had to be written manually, everything was possible by using ASP.NET Web controls. Behind the scenes, these controls make use of many features.

For example, the SqlDataSource control fills a DataSet with the help of a SqlDataAdapter with data from the database. The data that is used to fill the DataSet is defined with the connection string and the SELECT command. Just by changing a property of the SqlDataSource, the SqlDataReader can be used instead of the DataSet. Also, by setting the property EnableCaching to true, the Cache object (discussed earlier in the chapter) is used automatically.

You can read more about DataSets, the SqlDataAdapter, and the SqlDataReader in Chapter 24.