For this article we will use the Northwind database, SQL Server and Visual Studio Community 2015 to view data on a web page using ASP.net controls. Click here for a short article on downloading and installing Microsoft's example databases.
There are just a few steps involved in this project, assuming the database is installed. First, create an asp web form in Visual Studio and drag an ASP Grid View control onto it. Second, set create the Data Source, Connection String and basic query.
1. With your web site or project open in Visual Studio, right-click on the Solution Name or location (folder) in the solution and choose Add > Add New Item > Web Form. Don't forget to name your page, and choose your programming language and master page if you are using them.
2. Go to Design View and from the Toolbox Data section, drag an ASP GridView onto your from.
3. Click the Smart Tag to reveal the GridView Tasks and click Choose Data Source > New Data Source. Choose SQL Database. You can leave the name as SQLDataSource1 and click OK. This process will automatically add an ASP SqlDataSource control attached to your GridView control.
4. Now we need to configure the data source with a Connection String. This will tell the GridView where the data is located and what data to view. Click on New Connection. On the Add Connection window, change the Data Source to Microsoft SQL Server. Even clicking Refresh for the Server Name may not correctly populate that field. The Server Name is YourComputerName\SQLEXPRESS. If you are using a different version of SQL Server, you can open SQL Server Management Studio and find the Server Name on the login screen. Go down to Select or enter a database name and type the database name; Northwind. The field should auto-type after the first few letters. You can click Test Connection and you should get a Test Successful pop up. Click OK. Now, if you click the + next to Connection String, you should see your connection string. When asked to add the Connection String to your
WebConfig choose Yes.
The Connection String will change when you move your data or database. For instance, if you are working on a file system web site, when you go live with a data access page, your database will need to be on your hosting provider's server. The company I use,
WinHost has a control panel where I can create databases and the system generates the connection string. It's a little more involved than that, but that's fodder for a separate article.
5. From the Configure a Select Statement screen let's just use Specify columns from a table or view and use the top item, Alphabetical List of Products. This is a stored or saved View. A view is like a table of selected records. From this view, choose: ProductID, ProductName, CategoryID, and UnitPrice. In the SELECT statement field, you can see your statement. Click Next, Test the Query and you should see a table of data returned. Click Finish. When asked to update the DataGrid, choose Yes. This will give the grid the correct databound columns to accommodate your query.
6. Before leaving the GridView Task menu, go down and check Enable Paging and Enable Sorting. Paging allows 12 items to be viewed per page and puts page numbers at the bottom of the table. Sorting allows clicking on the column headers for sort ascending and sort descending. When ready, run you page in a browser and you should have the table below of products from the Northwind Traders database.
Test the paging and sorting ability of the gridview. Obviously there are some aesthetic and formatting issues with the table. In the next article we will style and format this table and the data view.
Thanks for viewing. Please check out my other articles on education and technology. I welcome any comments!
Comments »