Reading data (ADO.Net)

To read data from the database use VerticaDataReader, an implementation of DbDataReader.

To read data from the database use VerticaDataReader, an implementation of DbDataReader. This implementation is useful for moving large volumes of data quickly off the server where it can be run through analytic applications.

Note

A VerticaCommand cannot execute anything else while it has an open VerticaDataReader associated with it. To execute something else, close the data reader or use a different VerticaCommand object.

To read data from the database using VerticaDataReader:

Create a connection to the database.

Create a command object using the connection.

    VerticaCommand command = _conn.CreateCommand();

Create a query. This query works with the example VMart database.

        command.CommandText =
        "SELECT fat_content, product_description " +
        "FROM (SELECT DISTINCT fat_content, product_description" +
        "      FROM product_dimension " +
        "      WHERE department_description " +        "      IN ('Dairy') " +
        "      ORDER BY fat_content) AS food " +
        "LIMIT 10;";

Execute the reader to return the results from the query. The following command calls the ExecuteReader method of the VerticaCommand object to obtain the VerticaDataReader object.
```
VerticaDataReader dr = command.ExecuteReader();
```

Read the data. The data reader returns results in a sequential stream. Therefore, you must read data from tables row-by-row. The following example uses a while loop to accomplish this:

 Console.WriteLine("\n\n Fat Content\t  Product Description");
     Console.WriteLine("------------\t  -------------------");
     int rows = 0;
     while (dr.Read())
     {
        Console.WriteLine("     " + dr[0] + "    \t  " + dr[1]);
        ++rows;
     }
     Console.WriteLine("------------\n  (" + rows + " rows)\n");

When you're finished, close the data reader to free up resources.
```
    dr.Close();
```

Example usage:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Vertica.Data.VerticaClient;
namespace ConsoleApplication
{
    class Program
    {
        static void Main(string[] args)
        {
            VerticaConnectionStringBuilder builder = new VerticaConnectionStringBuilder();
            builder.Host = "192.168.1.10";
            builder.Database = "VMart";
            builder.User = "dbadmin";
            VerticaConnection _conn = new VerticaConnection(builder.ToString());
            _conn.Open();
        VerticaCommand command = _conn.CreateCommand();
            command.CommandText =
                "SELECT fat_content, product_description " +
                "FROM (SELECT DISTINCT fat_content, product_description" +
                "      FROM product_dimension " +
                "      WHERE department_description " +
                "      IN ('Dairy') " +
                "      ORDER BY fat_content) AS food " +
                "LIMIT 10;";
          VerticaDataReader dr = command.ExecuteReader();

         Console.WriteLine("\n\n Fat Content\t  Product Description");
         Console.WriteLine("------------\t  -------------------");
         int rows = 0;
         while (dr.Read())
         {
                Console.WriteLine("     " + dr[0] + "    \t  " + dr[1]);
                ++rows;
         }
         Console.WriteLine("------------\n  (" + rows + " rows)\n");
              dr.Close();
            _conn.Close();
        }
    }
}