Creating a Data Adapter in Code

In the previous topic, we created a command object that will get the authors table from the pubs database in the DataSetCode example on the CD-ROM. To actually get the authors table, I'll create an OleDbDataAdapter object, and assign our command object to that adapter's SelectCommand property, because the select command of a data adapter is used when you use the Fill method. I also add a default table mapping to the data adapter (see the discussion on table mappings in the In Depth section of this chapter for more information), and fill the dataset, ds, with data. Finally, I bind the filled dataset to the data grid you see in Figure 22.1:

Private Sub Button1_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles Button1.Click
    Dim ds As New DataSet()
    ds = New DataSet("authors")

    Dim ConnectionString As String = "Provider=SQLOLEDB.1;Integrated " & _
        "Security=SSPI;Persist Security Info=False;Initial " & _
        "Catalog=pubs;Packet Size=4096;Workstation ID=STEVE;" & _
        "Use Encryption for Data=False"

    Dim Connection1 As OleDbConnection = New _
        OleDbConnection(ConnectionString)

    Dim Command1 As OleDbCommand = _
        New OleDbCommand("SELECT * FROM authors")
    Command1.CommandType = CommandType.Text

    Connection1.Open()
    Command1.Connection = Connection1

    Dim OleDbDataAdapter1 As OleDbDataAdapter = New OleDbDataAdapter()

    OleDbDataAdapter1.SelectCommand = Command1
    OleDbDataAdapter1.TableMappings.Add("Table", "authors")
    OleDbDataAdapter1.Fill(ds)

    DataGrid1.SetDataBinding(ds, "authors")
End Sub

And that's the complete code—all you need to create a dataset from a connection to a data table in a database. Actually, you don't need to connect to a database to access a data table—you can create your own tables in code. See the next topic for the details.

*Related solution:*	*Found on page:*
Data Access Using Data Adapter Controls	872