DataSet to DataTableReader Conversion

• Download sample - 1.5 KB

Introduction

This is a test example of converting dataset to datareader.

Background

DataSet to DataReader conversion in Vertica database connection and got it while making a connection and fetching data from the database.

Using the Code

I have fetched data from database as a complete dataset which has only one table in it.

Made a DataTableReader from it and it is working as same as DataReader.

In DataBase class, I made getConnection() to get the valid connection for the database using the connection string as defined in App.config.

As I am using Vertica as my database, this code will only run for vertica. If database is other than vertica, then there must be change in connection, command, etc. objects as per the database provider as you can put in the config file.

DataBase.cs

  public static class DataBase
{
 private static VerticaConnection vConnection = null;
 private static VerticaCommand vCommand = null;
 private static VerticaDataAdapter vAdapter = null;
string private static void GetConnection()
 {
   GetAppSettings(); //get connection string from config
   vConnection = new VerticaConnection(<connectionstring>);
   vCommand = new VerticaCommand();
   vAdapter = new VerticaDataAdapter(vCommand);
 }
public static System.Data.DataSet getData(string query)
{
try{
  GetConnection();
  vCommand.CommandText = query;
  vCommand.Connection = vConnection;
  vAdapter.SelectCommand = vCommand;           
  System.Data.DataSet ds = new System.Data.DataSet();
  vAdapter.Fill(ds);
  return ds; 
}catch(Exception ex)
{ 
throw ex;
}
}  
}

Programs.cs

class Program
    {
        static void Main(string[] args)
        {
string query ="<Select Query>";
 System.Data.DataSet ds = DataBase.getData(query);
 System.Data.DataTableReader dr =  ds.CreateDataReader();
 Console.Write(dr.FieldCount);
 while (dr.Read())
 {
   if (dr.HasRows)
    {
      for (int i = 0; i < dr.FieldCount; i++)
        {
           Console.Write(dr[i].ToString()+"--");
        }
    }
   Console.WriteLine();
 }
 Console.Read(); 
} 
} 

App.config

<?xml version="1.0"?>
<configuration>
  <appSettings>
    <add key="VerticaConn" value="SERVER={0};
    DATABASE={1};USERNAME={2};PASSWORD={3};PORT=<portno>;TIMEOUT={4}"/>   
    <add key="SERVER" value="server"/>
    <add key="USERNAME" value="user"/>
    <add key="PASSWORD" value="password"/>
    <add key="DATABASE" value="database"/>
    <add key="TIMEOUT" value="timeout"/>
  </appSettings>
<startup><supportedRuntime version="v4.0" 
sku=".NETFramework,Version=v4.0"/></startup></configuration>

Points of Interest

This code snippet helps me to solve my issue.

History

• 29^th August, 2014: Initial version