- Building a DataSet with Code
- Loading a DataSet from a Database
- Saving Changes
- Saving Changes Redux
- Constraints
- Conclusion
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Loading a DataSet from a Database
Loading a DataSet from a database requires much less code than building one inline, though the code is a bit more complicated. Program FromDB shows how you can load a DataSet from a database. Click here to download program FromDB.
Program FromDB uses the code shown in Listing 2 to load its DataSet. The program begins with an Imports statement that lets the program use the short names of the SQL Server data objects it will use to interact with the database. For example, instead of declaring a variable of type System.Data.SqlClient.SqlDataAdapter, the Imports statement lets the program declare the variable as type SqlDataAdapter.
At the form-global level, the program creates two SqlDataAdapter objects. Data adapters copy data between a database and a DataSet or DataTable as shown in Figure 5. This program uses an SqlDataAdapter constructor that takes two parameters. The first gives the SQL SELECT statement that the data adapter should use to pull data from the database. The second gives a database connect string. In this example, the connect string specifies the server NetSDK on the computer BENDER. The program connects to the database as user sa, and the program is initially connected to the TestScores database.
)
Figure 5 Data adapters copy data to and from a database.
You could modify the code to work with other types of databases, such as Access, Oracle, or Informix. To do that, you would need to replace the program's SQL Server data objects with the corresponding OLE DB objects. For example, you would replace the SqlDataAdapter objects with OleDbDataAdapter objects. You would also need to change the connect string to specify the new database.
This example actually uses an MSDE database. MSDE (Microsoft Data Engine) is a subset of SQL Server. It uses the same basic architecture as SQL Server, but is available for free on Microsoft's Web site, and it is included in the Visual Studio .NET CD-ROMs. (For more on installing and using MSDE, see my article "Getting Started with MSDE and VB .NET.")
After it creates the two data adapters, program FromDB creates a DataSet, giving it the name Student Scores.
When the program runs, its Load event handler executes. It adds an entry to the m_daStudents object's TableMappings collection. This mapping tells the data adapter to take data selected under the default name Table and put it in a DataSet table named Students. If the program didn't create this mapping, the new table would be named Table by default. Not the most informative name possible.
The program then invokes the data adapter's Fill method to pull data from the Students table in the database and place it in the DataSet.
Next the program calls the m_daTestScores object's Fill method to pull data from the database's TestScores table. The second parameter to Fill tells the data adapter the name of the table that should receive the data in the DataSet. The result is roughly equivalent to using a TableMapping.
The Load event handler finishes by binding the form's DataGrid control to the DataSet. The result is identical to program InlineDB as far as the user can tell.
Listing 2—Program FromDB uses this code to create a DataSet from scratch
Imports System.Data.SqlClient
Public Class Form1
Inherits System.Windows.Forms.Form
' Data adapter for the Students table.
Dim m_daStudents As New SqlDataAdapter( _
"SELECT * FROM Students", _
"User Id=sa;" & _
"Data Source=BENDER\NetSDK;" _
"Initial Catalog=TestScores")
' Data adapter for the TestScores table.
Dim m_daTestScores As New SqlDataAdapter( _
"SELECT * FROM TestScores", _
"User Id=sa;" & _
"Data Source=BENDER\NetSDK;" _
"Initial Catalog=TestScores")
' The DataSet.
Private m_DataSet As New DataSet("Student Scores")
Private Sub Form1_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
' Load the Students table.
m_daStudents.TableMappings.Add("Table", "Students")
m_daStudents.Fill(m_DataSet)
' Load the TestScores table naming
' the new table TestScores.
m_daTestScores.Fill(m_DataSet, "TestScores")
' Bind the DataGrid to the DataSet.
DataGrid1.DataSource = m_DataSet
End Sub
End Class