Gohalien

Hi , I am trying to do a windows form for record navigation from a mysql database, I dont know what is the best way to do this, if read directly from db, store stuff in a mysql adapter, etc.. the from have only 2 fields, ID; Description. Example:

Id: ________
Description: _________________

(first) (previous) (next) (last) <- buttons

If anyone can give me an example will be appreciated, thanks for your time


Re: Windows Forms General Record Navigation

TaylorMichaelL

You can either use a DataSet or a DataReader. DataSet gives you the ability to move back and forth within the data. This is ideal for grids. It also allows you to make changes and flush them back to the DB. Unfortunately DataSet is slow (compared to DataReader) and takes up more memory. Don't use it for large data sets. Once the data is loaded into the DB though the DB connection is dropped so you don't waste resources. You will use a DataAdapter to fill the DataSet. Refer to MSDN on how to do this.

DataReader is fast and slim for streaming through data. However it requires a consistent connection to the DB. It also is read-only and forward-only. Once you've read a row of data and moved on you can't go back. Until all the data is read the connection must remain open. For large data sets this is ideal but you don't want to keep a data reader open too long because it impacts scalability. Also be aware that (by default) you can have only one reader per connection so you don't want to open a bunch of readers at one time either. You get a reader back when you call ExecuteReader on a command. You can then enumerate the rows by calling Read. If the command returns multiple result sets (tables) then use MoveNext to move to the next result set.

It is a religious war as far as when to use which. Some people use only DataSet while others use only DataReader. My personal recommendation is to use DataSet for small data sizes where you want to be able to navigate through and/or update the data. If the data is large, you need to expose the data to a UI layer (in a n-tier app) or you need to do custom paging of data then switch to DataReader to stream the data in and (optionally) store it in custom business objects.

Michael Taylor - 5/17/07

http://p3net.mvps.org





Re: Windows Forms General Record Navigation

Gohalien

Thanks for your help, I was thinking about filling a DataTable and then navigate it, filling it again when a record is inserted or deleted, something like:

public static DataTable Load_Records(string table, string where)
{
string query = string.Format("SELECT * FROM {0} WHERE {1}", table, where);
MySqlDataAdapter dataAdapter = new MySqlDataAdapter(query, cnn);
MySqlCommandBuilder commandBuilder = new MySqlCommandBuilder(dataAdapter);
DataTable TableWithData= new DataTable();
TableWithData.Locale = System.Globalization.CultureInfo.InvariantCulture;
dataAdapter.Fill(TableWithData);
}

public void First_Record()
{
if (Data_Registros.Rows.Count > 0)
{
DataRow Line;
Line= TableWithData.Rows[0]; //First record
id = Line.ItemArray[0].ToString();
detail = Line.ItemArray[1].ToString();
current_row = // I know it is 0, but there is a way to "move" the pointer to the first row of the table to show up in what current row I am step in.
}
}
public void Next_Row()
{
DataRow Line;
Line= TableWithData.Rows[current_row+1];
id = Line.ItemArray[0].ToString();
detail = Line.ItemArray[1].ToString();
}