Creating a Command Object in Code

After creating a connection object to connect to the authors table in the pubs database in the DataSetCode example on the CD-ROM (see the previous two topics), we need a command object to load the authors table into our dataset. Here's how I create an OleDbCommand object, give it the SQL "SELECT * FROM authors" and set the command's type to CommandType.Text (which is the value you use for SQL, and is the default). Then, after opening the connection object we created in the previous topic, assign that connection object to the command object's Connection property:

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
        ⋮

Our command object is now ready to be used with a data adapter to get the authors table from the pubs database. See the next topic for the details.