Write to UsOnline Photo-Album with ASP.NET (page 2)
SqlDataAdapter is the object that allows the Dataset and the database to communicate. The words SELECT * FROM pictures form a SQL statement that tell the database to select every data inside the table called pictures. Notice that the name Photo_Connection follows the SQL statement, telling the Adapter which connection it'll work with.
Our DataSet is called Photoalbun_Dataset. How do we fill it with data? Examining the script should make it obvious to you. We put our SqlDataAdapter to use and using Photoalbum_Adapter.Fill, we send all the data to Photoalbum_Dataset. The Fill Method of the SqlDataAdapter adds or refreshes rows in the DataSet, so they can match those in the data source. In this case, the rows are put in a table called Albums_Table.
Once the data has been transmitted to the DataSet, the connection to the database is closed. The Close Method releases the connection to the connection pool. Pooling allows an application to use a connection from a pool of connections, so an application won't have to perform a connection process each time access to a database is needed. Keeping the connection open without using it will increase the size of the pool of connections and will consume memory and other resources. Before closing a connection, make sure you are really done using it. Some people disable the Connection Pool in their scripts. If you are one of them, using the Close Method will simply close the connection to the database. If you need to connect again, your script will have to go through the entire connection process.
Finally we attach the data to the DataList called DataList1. The name DataList1 was chosen at random. Unlike regular objects, the DataList object is declared inside the <body> tag, as you will see later on. The DataSource property of the DataList receives data from Photoalbum_Dataset through the latter's Tables property. As you can see the name “Albums_Table” shows up once again. Although it may seem useless in this script, naming tables is what allows DataSet to handle multiple tables.
We use DataBind() to link the data to the DataList called DataList1. It is time to retrieve the data from the server and let the client see it through his browser.
The Datalist Control
A DataList displays the data it contains using templates created inside it. Notice that the id equals DataList1. You are free to change the name, but you must change it in the previous part of the script as well, otherwise you'll get an error. Notice the template inside the DataList starts with a header. Had we used a DataGrid instead of a DataList to output our data, there would've been no need to create a table, for DataGrid does this automatically. However DataList renders the data as a list of records. Something we can easily change.
Our DataList Control will have four parts. The HeaderTemplate (shown below) will contain the start of the table as it will appear on the page. The ItemTemplate will contain the format in which the data will be displayed. In this case, we have chosen to also use the AlternatingItemTemplate, which will activate when the DataList Control needs a format to print out a database row, and it has already used the ItemTemplate's format for a previous row. Finally there's the FooterTemplate, which brings everything to a close. With these four ingredients, you can have a photoalbum that won't look dull at all, and we are scratching the surface when it comes to versatile controls like Repeater, Datalist, and DataGrid.
<HeaderTemplate>
<table style="border:1px; color: #000;" class="stdtext" cellspacing="2" cellpadding="5">
<thread bgcolor="#666666" style="color:white">
<td><b>Picture:</b></td>
<td><b>Name:</b></td>
<td><b>Description:</b></td>
</thread>
</HeaderTemplate>
Remember the name you gave to each variable in your database table? You better. Those same names will now be called by the DataItem property to output their values. A DataItem is inside the the <%# %> brackets, as you can see in the script below. Databinding operates in a row-by-row basis and DataItems output information from the current row that has been bound. Notice the syntax inside the DataItem. The DataRowView class allows you to see the data on the bound row, and Container.DataItem does exactly what its name implies. If you were using regular HTML, the syntax for displaying a picture would be fairly simple:
<img src=”picture_file.jpg” alt=”description” width=”500 />
However including every picture mentioned in the database using that syntax could take us a very long time. Notice that here src='images/<%# ((DataRowView)Container.DataItem)["filename"] %>, where images is the name of the directory, and everything after the / is part of the DataItem called filename. If the variable filename in the first row of the database is called landscapes1.jpg, the server will replace the DataItem with the name landscapes.jpg, and a picture called that will appear on any browser accessing the script provided the file is in the images directory.
When it comes to the picture's width, the syntax is more complicated for our DataItem. This is because the syntax can be NULL, and we want the DataList to be able to handle NULL values properly.
width='<%# ((DataRowView)Container.DataItem)["width"].ToString() == "" ? 300 : ((DataRowView)Container.DataItem)["width"] %>'
The line above may seem hard to understand, but it is quite simple. If the content of (DataRowView)Container.DataItem)["width"] turned into a string equal null, then the width value will be 300. If the content is not null, the width's value is taken from the database.
We bring this tutorial to a close by showing you the rest of the script. Notice how the data is formatted using the DataList. Once a .NET server turns this script into something your browser can understand, you'll see that the output is a table containing pictures accompanied by the names and descriptions you gave them.
<ItemTemplate>
<tr>
<td bgcolor="#77EEFF">
<img src='images/<%# ((DataRowView)Container.DataItem)["filename"] %>'
description='<%# ((DataRowView)Container.DataItem)["description"] %>'
width='<%# ((DataRowView)Container.DataItem)["width"].ToString() == "" ? 300 : ((DataRowView)Container.DataItem)["width"] %>'
/>
</td><!--The above code won't work in VB -->
<td bgcolor="#77EEFF"><strong> <%# ((DataRowView)Container.DataItem)["name"] %> </strong> </td>
<td bgcolor="#77EEFF"><strong> <%# ((DataRowView)Container.DataItem)["description"] %> </strong></td>
</tr>
</ItemTemplate>
<AlternatingItemTemplate>
<tr>
<td bgcolor="#EEEEEE"><img src='images/<%# ((DataRowView)Container.DataItem)["filename"] %>'
description='<%# ((DataRowView)Container.DataItem)["description"] %>'
width='<%# ((DataRowView)Container.DataItem)["width"].ToString() == "" ? 300 : ((DataRowView)Container.DataItem)["width"] %>' />
</td><!--The above code won't work in VB -->
<td bgcolor="#EEEEEE"><strong> <%# ((DataRowView)Container.DataItem)["name"] %> </strong></td>
<td bgcolor="#EEEEEE"><strong> <%# ((DataRowView)Container.DataItem)["description"] %> </strong></td>
</tr>
</AlternatingItemTemplate>
<FooterTemplate>
</table>
</FooterTemplate>
</asp:DataList>
</center>
</body>
</html>