How do I read data (or records) from database using data reader?

To read data from the database, you first make a connection object (SqlConnection, etc) and open it.

C# Version

	string connString = "server=FARAZ; database=programmersheaven;" +
			        "uid=sa; pwd=";
	SqlConnection conn = new SqlConnection(connString);
	conn.Open();

VB.Net Version

        Dim connString As String = "server=siraj; database=programmersheaven;" + _
                                   "uid=sa; pwd="
        Dim conn As New SqlConnection(connString)
	  conn.Open()

Then you create a command using this connection and the command text.

C# Version

	string cmdString = "select * from author";
	SqlCommand cmd = new SqlCommand(cmdString, conn);

VB.Net Version

        Dim cmdString As String = "select * from author"
        Dim cmd As New SqlCommand(cmdString, conn)

Then you execute the command with the command object’s ExecuteReader() method. The ExecuteReader method returns the object of type IDataReader

C# Version

	SqlDataReader reader = cmd.ExecuteReader();

VB.Net Version

        Dim reader As SqlDataReader = cmd.ExecuteReader()

Now you read the individual records using this data reader. To advance to the next record, you call its Read() method which returns Boolean to indicate if there exists a next row. If the DataReader’s Read() method returns true then the DataReader acts as a database row (record). Now you can access the fields of this particular row specifying the column names (or integral indexes) in its indexers.

C# Version

	while(reader.Read())
	{
		txtData.Text += reader["authorId"].ToString();
		txtData.Text += ", "; 
		txtData.Text += reader["name"].ToString();
		txtData.Text += "\r\n";
	}

VB.Net Version

        While reader.Read()
            txtData.Text += reader("authorId").ToString()
            txtData.Text += ", "
            txtData.Text += reader("name").ToString()
            txtData.Text += vbCrLf
        End While

Finally, you need to close the database connection opened before performing the database operation (SELECT, in our case)

C# Version

	conn.Close();

VB.Net Version

        conn.Close()

Let’s look at the complete code now for review

C# Version

	string connString = "server=siraj; database=programmersheaven;" +
			        "uid=sa; pwd=";
	SqlConnection conn = new SqlConnection(connString);
	string cmdString = "select * from author";
	SqlCommand cmd = new SqlCommand(cmdString, conn);
	conn.Open();
	SqlDataReader reader = cmd.ExecuteReader();
	while(reader.Read())
	{
		txtData.Text += reader["authorId"].ToString();
		txtData.Text += ", "; 
		txtData.Text += reader["name"].ToString();
		txtData.Text += "\r\n";
	}
	conn.Close();

VB.Net Version

        Dim connString As String = "server=siraj; database=programmersheaven;" + _
                                   "uid=sa; pwd="
        Dim conn As New SqlConnection(connString)
        Dim cmdString As String = "select * from author"
        Dim cmd As New SqlCommand(cmdString, conn)
        conn.Open()
        Dim reader As SqlDataReader = cmd.ExecuteReader()
        While reader.Read()
            txtData.Text += reader("authorId").ToString()
            txtData.Text += ", "
            txtData.Text += reader("name").ToString()
            txtData.Text += vbCrLf
        End While
        conn.Close()

Back