ePlaice / For the Best Software on the Net
Mainly Free and Open Source Software
Software Dev't Navigation
Delphi Conversion | DataSets | DataGridView | Record Navigation | Data Formatting / Validation | Data Queries Data Input Form Data Readers MDI Forms Charts Using MySQL | MySQL NET Connector |
Latest news
27 Feb 2007: I decided to write a few lines about my experiences with C# and SharpDevelop, but got waylaid looking at some of my earlier experiences.
07 Mar 2007: SharpDevelop 2.1 Final has been released.
Links:
DataSets
The DataSet is central to ADO NETs view of data and so even though it seems to be a well understood part of the framework I decided to include my own views at an early stage before becoming over familiar with their use.
ADO NET works on a disconnected data access model, so that whenever your application wants some data, a new database connection is opened which gets closed as soon as the request completes. This approach also holds true for database updates. Using this approach allows ADO NET to conserve system resources and maximise security by keeping data connections alive only for as long as necessary. By minimising the connection time and by operating on a subset of the database locally, performance is significantly better than ODBC, DAO and previous Microsoft database offerings.
What is a DataSet?
Delphi has been using the term DataSet for years and when I looked it up in Delphi Database architecture I came across this definition; 'A collection of data returned by a database is called a dataset'. Also a dataset can be more than just the data contained in a table; it can be the result of a query containing data from many tables. The important thing to realise is, that when you are working with a dataset you are not working directly with the database, but with a local copy. So there is talk about the dataset being disconnected from the database. So if someone updates the database after you have created your dataset, then your dataset will be out of date. If you update the dataset the underlying database does not change until you also explicitly update the database. So it must cross your mind why use datasets? I can think of a few reasons such as the ability to check out all your data updates before applying them to the database, the ability to perform complex queries across many tables without having an open connection to the database. So it's a bit like having your own personal database without the fear of compromising other users or compromising the main database. Of course there are downsides to this approach such as an increase in overall complexity, but this concept fits neatly with Client/Server architecture. I should also point out that the Delphi implementation of a DataSet is quite different from the ADO NET implementation.
ADO NET Data Provider
A Data Provider is a set of related components that allow access to the database, consisting of the following parts :-
The Connection
Before you can read or make changes to a database you need a database connection. For an example of a database connection to a MySQL database see my webpage Using MySQL with .NET. The connection class contains all the information needed to open a connection such as the userid, password and server.
The Command
This contains the instructions used to access the data. It is normally in the form of an SQL query (or could be SQL commands in a stored procedure) which can return part of a table, the whole table or just a single row.
The DataReader
The DataReader allows further operations to take place on the data returned by the SQL query. For example your query may have returned several rows, but you just want to read certain values from particular rows or you want to display a row at time on the screen. The DataReader allows you to read through your returned DataSet a row at a time. Note that if you are fetching a DataSet and all you want to do is just display all the rows returned in say, a DataGridView then you don't need to use a DataReader. In this case you just bind the entire DataSet to the grid.
The DataAdapter
An ADO DataSet can contain tables, columns, relationships, constraints, views and more which your program can manipulate. A DataAdapter is used to connect to the database and by using an SQL query it can fill itself with data. When the application has finished manipulating the data; for example by updating a field in a row, or adding or removing a row; the DataAdapter is then used again to connect back to the database and update the actual live database.
Creating a DataSet
So far, apart from having a cursory look at typed datasets I have not attempted to use these in any of my applications. From what I have read it appears they are worth the effort in the long run, but I have to admit to being a bit unwilling to take on a further new area on top of an already steep learning curve. What I have done is tried to keep all my dataset creation in a separate module, because this is the way I am used to doing things in Delphi with it's separate DataModule. In addition, I have tried to keep all of the SQL, datareader operations and Table Inserts, Updates and Deletes in this module. With this separation, I can leave my application modules to largely forget about the underlying database and concentrate on the processing of the datasets.
Using MySqlDataAdapter
The MySQLDataAdapter acts as a bridge between a DataSet and a MySQL database for retrieving and saving data. The following set of commands sets up a new dataset, runs some SQL from a database table, uses the MySqlDataAdapter to create a DataAdapter (da1) with connection (conn) and fills the new dataset with the records extracted from the query:-
SQLString = "SELECT * FROM author" +
" ORDER by surname";
da1["author"] = new MySqlDataAdapter(SQLString, conn);
da1["author"].Fill(dataSet1,"author");
Creating Primary Key
The dataset may need to use a primary key (which is not created by the Fill command), for example when updating the dataset.
dataSet1.Tables["book"].Columns["bookno"] };