ADO.NET II - DIRECT DATA ACCESS

Building SQL commands and using ADO.NET classes to access database without the need of data oriented controls is considered as direct data access.

There are a number of steps you should follow to achieve direct data access:

1. Import relevant namespace
2. create an instance object of SqlConnection class
3. set the ConnectionString property of the connection object
4. Open the connection
5. Create an instance object of SqlCommand
6. set the CommandText property of the command with your sql statement
7. set the Connection property of the command object with the SqlConnection object you’ve created
8. execute the command

If you are retrieving data (i.e: if you are using Select sql statement) you need to use SqlDataReader class to extract the data from the SqlCommand:

9. create a variable object of type SqlDataReader. Note: SqlDataReader does not use the new keyword that means it has no constructor
10. Execute command reader. Command reader (SqlCommand.ExecuteReader() ) is a method that executes the command against the database and at the same time returns a SqlDataReader with the result
11. Traverse the rows of SqlDataReader one after one by using Read() method
12. after you call the Read() method use the data in the reader by column index or name
13. close the reader and the connection if you are done (important)

Example:

Imports System.Data.SqlClient

Partial Class CustDetails

    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        'Dim id As String

        'id = Request.QueryString("id")

        Dim conn As New SqlConnection()

        conn.ConnectionString = "Data source=(local)\sqlexpress; AttachDbFilename=|DataDirectory|\Database.mdf;" _

        & "Integrated Security=true; User Instance=true;"

        conn.Open()

        Dim cmd As New SqlCommand()

        cmd.CommandText = "select * from Customers where custid='12'"

        'cmd.Parameters.AddWithValue("@cust", id)

        cmd.Connection = conn

        Dim drdr As SqlDataReader

        drdr = cmd.ExecuteReader()

        drdr.Read()

        If drdr.HasRows Then

            lblCustID.Text = drdr("custID").ToString()

            lblCustName.Text = drdr("fname").ToString()

            lblCustSurname.Text = drdr("lname").ToString()

        Else

            lblError.Text = "No customer found with the stated ID"

        End If

        drdr.Close()

        conn.Close()

    End Sub

End Class

For how to use Parameters go to my channel http://youtube.com/nuux14