ASP.NET / MySQL

Notes On DataView, GridView, and ListView.

 

When to use (or not use) DataGrid, GridView, and ListView with DataReaders, Data Sets, and custom paging.

Dataview. Apparently, DataGrids worked easily with custom paging. Not content to have something work easily, DataGrids were depreciated by Microsoft and replaced with Gridviews. Gridviews made paging, "easy," but only if you do not need to page through large amounts of data. 

Gridview. The staple data display tool of asp.net 3.0-plus. Easily allows server-side data paging. However, custom paging using AllowCustomPaging="True" does not apply to Gridview. Custom paging may be possible with Gridview, but be prepared to write some code to do so. If you do a databind to a dataset, and allow server-side paging, paging will only apply to the actual data in the dataset. This makes this combination impractical if you need to page through a large amount of data.

ListView. Listview makes a barebones data display that can be customized with CSS. If the ability to page through a very large table is needed, ListView combined with a DataPager is the easiest solution. With the DataPager, it grabs sets of a large table and requests each set as the user pages through them. Do paging in this method increases performance compared to the simple paging done in Gridview, which is really impractical with large sets of data.

Note: If  ListView is bound to a Datareader, paging will not be an option. If paging is required, bind to a DataSet or something else which implements ICollection.

DataReaders. DataReaders work OK, but when used with MySQL, you can have only one DataReader open at a time, period. This means you have to open and close database connections for each DataReader needed, or find another way to accomplish your goal. Note that MultipleActiveResultSets=True only applies to Microsoft SQL and it will not currently work with MySQL.


Here are a few examples of the same data being bound to a DataReader, a DataSet, and  ListView:

Imports MySql.Data.MySqlClient
Imports
System.Data
Partial
Class Default4
Inherits System.Web.UI.Page
Private oConnection As New MySqlConnection()

Protected Sub DropDownList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DropDownList1.SelectedIndexChanged
If (DropDownList1.SelectedValue <> "") Then
   
oConnection.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings.Item("ConnectionString").ConnectionString
   
 

Default.aspx.vb  Example with a Gridview filled by a  DataReader

Or

Default.aspx.vb  Example with a Gridview Bound To A DataSet

Or

Default.aspx.vb  Example with a Listview Bound to a DataSet*

End If
End
Sub
End
Class


*A Handy Trick to Bind a DataSet to a ListView in Visual Studio 2008 (Original Source)

The trick is to cheat by using the Visual Studio 2008 Wizard to setup the binding of an sqldatasource to a ListView. This allows you to configure the ListView, but then delete the sqldatasource and configure a data source programmatically in the code behind. 

1.1. drag a ListView and a SqlDataSource on your form. Give a name to your ListView
1.2. connect the SqlDataSource and set the appropriate query, test the query
1.3. connect the ListView to the SqlDataSource, edit it by using pre-tempalte (grid, color, paging etc...) click finish

2.1. go to the asp script and copy/paste the <asp:ListView> </asp:ListView> in a notepad
2.2. delete the ListView and the SqlDataSource from the form (cleaning)
2.3. drag a brand new ListView and give same name as 1.1.

3.1. copy/paste back your bloc-note content to the asp script
3.2. go to the form, select the now well formatted ListView, empty the property DataSourceID (still connected to the former SqlDataSource that doesn't exist enymore since you'll built all that with a DataSet dynamically)

4.1. Perform dynamic binding as follow:  

Default.aspx.vb

 

 

Tags:  asp.net 3.5, Visual Basic, VB, MySQL 5.1, MySQL .net connector