An Introduction to DataSet in VB.NET

An Introduction to DataSet in VB.NET Much of Microsofts data technology, ADO.NET, is provided by the DataSet object. This object reads the database and creates an in-memory copy of that part of the database that your program needs. A DataSet object usually corresponds to a real database table or view, but DataSet is a disconnected view of the database. After ADO.NET creates a DataSet, there is no need for an active connection to the database, which helps in scalability because the program only has to connect with a database server for microseconds when reading or writing. In addition to being reliable and easy to use, DataSet supports both a hierarchical view of the data as XML and a relational view that you can manage after your program disconnects. You can create your own unique views of a database using DataSet. Relate DataTable objects to each other with DataRelation objects. You can even enforce data integrity using the UniqueConstraint and ForeignKeyConstraint objects. The simple example below uses only one table, but you can use multiple tables from different sources if you need them. Coding a VB.NET DataSet This code creates a DataSet with one table, one column, and two rows: Dim ds As New DataSet Dim dt As DataTable Dim dr As DataRow Dim cl As DataColumn Dim i As Integer dt New DataTable() cl New DataColumn( theColumn, Type.GetType(System.Int32)) dt.Columns.Add(cl) dr dt.NewRow() dr(theColumn) 1 dt.Rows.Add(dr) dr dt.NewRow() dr(theColumn) 2 dt.Rows.Add(dr) ds.Tables.Add(dt) For i 0 To ds.Tables(0).Rows.Count - 1 Console.WriteLine( ds.Tables(0).Rows(i).Item(0).ToString) Next i The most common way  to create a DataSet is to use the Fill method of the DataAdapter object. Heres a tested program example: Dim connectionString As String Data SourceMUKUNTUWEAP; Initial CatalogBooze; Integrated SecurityTrue Dim cn As New SqlConnection(connectionString) Dim commandWrapper As SqlCommand New SqlCommand(SELECT * FROM RECIPES, cn) Dim dataAdapter As SqlDataAdapter New SqlDataAdapter Dim myDataSet As DataSet New DataSet dataAdapter.SelectCommand commandWrapper dataAdapter.Fill(myDataSet, Recipes) The DataSet can then be treated as a database in your program code. The syntax doesnt require it, but you will normally provide the name of the DataTable to load the data into. Heres an example showing how to display a field. Dim r As DataRow For Each r In myDataSet.Tables(Recipes).Rows Console.WriteLine(r(RecipeName).ToString()) Next Although the DataSet is easy to use, if raw performance is the goal, you might be better off writing more code and using the DataReader instead. If you need to update the database after changing the DataSet, you can use the Update method of the DataAdapter object, but you have to make sure that the DataAdapter properties are set correctly with SqlCommand objects. SqlCommandBuilder is usually used to do this. Dim objCommandBuilder As New SqlCommandBuilder(dataAdapter) dataAdapter.Update(myDataSet, Recipes) DataAdapter figures out what has changed and then executes an INSERT, UPDATE, or DELETE command, but as with all database operations, updates to the database can run into problems when the database is being updated by other users, so you often need to include code to anticipate and solve problems when changing the database. Sometimes, only a DataSet does what you need. If you need a collection and youre serializing the data, a DataSet is the tool to use. You can quickly serialize a DataSet to XML by calling the WriteXML method. DataSet is the most likely object you will use for programs that reference a database. Its the core object used by ADO.NET, and it is designed to be used in a disconnected mode.