This post will cover how to use the DataList Control in ASP.NET for displaying data from a SQL database and from an XML file. It will also demonstrate how simple it is to switch from one to the other.
Each post in the Starter Series will take you from the very beginning, and will always start with creating the project we will be working with. So, here we go…
1. Create a new ASP.NET Web Application
You can certainly do this with a “Web Site” template, but a “Web Application” frees us up to do more in the future, so I always start with this template.
2. Create a new XML file.
One of the things we will need for this example is an XML file. Inside this file, we’re going to have some data. Let’s start by creating the file, and adding to our project.
We also need to put some data in that XML file. Rather than have you create one on your own, I’ve created one for you. Download the sample XML file Evangelists.xml here.
3. Switch over to Design View
We are going to do the rest of this operation without even writing a line of code. Open the “Default.aspx” file and click the “Design” button to get to the visual designer. Click the image below to enlarge this screenshot.
4. Add a DataList from your Toolbox.
All you need to do here is open your toolbox, go to the “Data” tab, and drag that DataList icon to your document. You will then see some interesting boxes on your screen.
5. Setting up a Data Source
From the menu that kicks out to the right, the second option is to “Choose Data Source.” From the dropdown, choose “New Data Source…” You will see a dialog like the one below.
Choose the “XML File” option, and for this example, I’m going to leave the default Data Source name: XmlDataSource1. You can rename it to anything you’ll remember, though, and then click OK.
It will then ask for you to indicate which XML file you want to use as a Data file.
Click the Browse button and choose the XML file we created in Step #2. Mine is named Evangelists.xml. You can also provide an XSLT file or an XPATH expression, if those suit you, but for our purposes, we don’t need those, so you can leave them blank. Click OK. You should now see your DataList fully populated with the data from the XML file. You should also see a new box below it. That’s our XmlDataSource. We can completely format this data to look the way we want, and we’ll address that a few steps from now. Next, we’re going to use a small database to populate the same DataList control.
6. Create a SQL Server database.
Just so we’ve done it from scratch, let’s create a new database. You don’t need SQL running on your machine or anything. We’re going to be using SQL Express, which is built into Visual Studio 2008. Just add a new item from the Data category.
It will ask you if you want to put this database in the “App_Data” folder. You may as well…that’s where nearly everyone else puts it too. I’ve named mine OldBooks.mdf because I’m going to have a table that contains old books I have on my bookshelf.
If you’re familiar with creating a database, and don’t want to take the time, you can download my example database file here. Just add it as an existing item, and off you go.
7. Add a table to the database.
I am going to create a table named book, and it will have columns named book_id, title, author, and publish_date. You can do all of this from the Server Explorer tab. Just right-click on the Tables folder.
Once your table is created, you can see it in your Server Explorer. Right-click on it and choose “Show Table Data.” This will open a window that allows you to enter data into your table. You can put whatever you want in these boxes for this demo. Or you can download my pre-populated sample here, and use it instead.
1 Gulliver’s Travels Jonathan Swift 1/1/1927 12:00:00 AM
2 Nicholas Nickelby Charles Dickens 1/1/1908 12:00:00 AM
3 Alice In Wonderland Lewis Carroll 1/1/1947 12:00:00 AM
4 The Deerslayer James Fenimore Cooper 1/1/1936 12:00:00 AM
5 Andersen’s Fairy Tales Hans Christian Andersen 1/1/1914 12:00:00 AM
6 Black Beauty Anna Sewell 12/1/1915 12:00:00 AM
8. Changing our data source.
Go back to your Default.aspx page, and open the menu on your DataList again. The one that we chose our XmlDataSource from in step #5. This time, choose “New Data Source…” again, and choose “Database”, instead of the XML File choice we made before.
On the next screen, there will be one WIDE dropdown list. Open it up, and choose the reference to the database you created. In my example, it is OldBooks.mdf. Then click Next.
It will also ask you if you want to save this connection in your “application configuration” file. This stores the connection string to your database in your web.config file instead of directly in the page. It’s more secure, and reusable. I recommend it. Click Next.
The last part of this wizard allows us to configure the SQL statement we want to use to retrive our data from the database. In our case, we want ALL of the data from the table, so just check the box with the * next to it. Then click Next.
Finally, the wizard allows you to test your query. Click the “Test Query” button, and it will retrieve your data according to the SQL you specified in the previous screen. If you’re happy with it, click Finish.
The biggest difference between using the SQLDataSource and the XML one is that Visual Studio will not populate the Design surface with actual data. It puts everything in place, creates the labels, but uses dummy data like “abc” for text values. When you run the application, however, your data will be displayed. Press F5 to see it in action.
9. Making your data prettier
Now that we can retrieve our data, we want to format it a little, and make it look nice. Without writing code, we’ve got some preset templates we can choose from, or we can just go back to Source mode and edit the HTML. In the menu for your DataList, choose “Auto Format.” There, they provide you with about 20 different templates that you can choose quickly rather than formatting by hand.
All we are really doing is making choices that are translated into HTML in our page, however. There are plenty of different templates, too. <ItemTemplate> is the default HTML that each element will use, but you can also specify an <AlternatingItemTemplate> which it will use for every EVEN item in your list. #2, #4, etc. It allows you to simply alternate colors, etc for each row. It makes readability much easier.
There’s an excellent overview of the different templates available to you at MSDN. I’ll leave it to the folks that wrote this control to let you in on all of the customization that is available to you.
After going through this article, you should now be able to create a DataList control on your web page, and consume data from either an XML file or a SQL database. To use other data sources (like an Access database), it’s just as simple. Just make sure to use the correct provider when choosing your data source.
If you need me to expand on this subject, just let me know. I am always looking for ideas to cover on this blog, and your recommendations go a long way. Thanks for reading.