What is a CData ADO.NET Provider?

CData ADO.NET Providers are powerful tools that allow developers to connect .NET applications with more than 270 data sources, treating them as if they were databases. This capability simplifies data integration and enables seamless interaction with data from SaaS, NoSQL, Big Data, and more.

CData ADO.NET Providers are meticulously designed to adhere to the ADO.NET standard, ensuring seamless integration with .NET applications. This adherence guarantees compatibility with ADO.NET features such as connection pooling, transaction management, and command execution, enabling developers to leverage familiar tools and techniques while working with diverse data sources.

In this article, we will explore how to use CData ADO.NET Providers to connect to a data source, retrieve table metadata, query data for desired results, and execute stored procedures for specific actions. For demonstration purposes, we will use the CData ADO.NET Providers for CSV, but the principles apply to any of the 270+ data sources we support.

Connecting to a data source

The CData ADO.NET Provider for CSV implements a standard DbConnection object in CSVConnection. Additionally, you can use the CSVConnectionStringBuilder to programmatically build, parse, and rebuild connection strings.

Creating Connection Objects

Each Provider comes with comprehensive documentation on connecting to the data source. For connecting to CSV documents, you can simply create a connection string with the path to your CSV documents (e.g. C:\Users\Public\MyCSVs\). Below is a typical invocation to create CSVConnection objects.

C#


using (CSVConnection connection =  
  new CSVConnection("URI=C:\Users\Public\MyCSVs\")) 
{ 
  connection.Open(); 
}

VB.NET


Using connection As New CSVConnection("URI=C:\Users\Public\MyCSVs\")
  connection.Open
End Using

Using CSVConnectionStringBuilder

The following code example shows how to use an ADO.NET connection string builder to parse a connection string.

C#


string conn_string = "URI= C:\Users\Public\MyCSVs\";
//Pass the connection string builder an existing connection string and you can get and set any of the elements as strongly typed properties.
CSVConnectionStringBuilder builder = new CSVConnectionStringBuilder(conn_string);
// You can instantiate a CSV Connection string builder object and then work with individual items
CSVConnectionStringBuilder builder = new CSVConnectionStringBuilder();
//You can work with individual items by referring to the connection properties within the builder object:
builder.URI = "C:\Users\Public\MyCSVs\";
//You can refer to the connection keys using strings, as well.
builder["URI"] = "C:\Users\Public\MyCSVs\";

VB.NET


Dim conn_string As String = "URI=C:\Users\Public\MyCSVs\"

' Pass the connection string builder an existing connection string and you can get and set any of the elements as strongly typed properties.

Dim builder As New CSVConnectionStringBuilder(conn_string)
' You can instantiate a CSV Connection string builder object and then work with individual items

Dim builder As New CSVConnectionStringBuilder()

' You can work with individual items by referring to the connection properties within the builder object:
builder.URI = "C:\Users\Public\MyCSVs\"

' You can refer to the connection keys using strings, as well.

builder("URI") = "C:\Users\Public\MyCSVs\"

Using metadata calls for schema discovery

Once connected, you can use metadata calls to explore the structure of your data. The provider supports schema discovery through ADO.NET classes or SQL queries on system tables. ADO.NET classes provide access to schema details, connection properties, and column information.

While tables, views, and columns are regular parts of a database (what ADO.NET providers generally connect to), they don't always have meaning for file stores, services, and SaaS applications. CData's ADO.NET providers convert the structure of this non-relational source into a tabular model:

Entities and objects get surfaced as views (read-only) or tables (read-write)
Fields and attributes surfaced as columns

For example, the metadata for a directory containing CSV-formatted files would include a table for each file and columns for the fields in a given file.

System tables are special tables maintained by a database system to store metadata information about the database itself. They contain details about database objects such as tables, columns, indexes, constraints, users, and permissions. By querying system tables, you can retrieve schema and connection property details, along with data source functionality insights and statistics on update operations.

In ADO.NET, system tables (or system catalog views) can be queried using the GetSchema method, which provides schema collections like Tables, Columns, Indexes, and Views.

Here's an example to find the tables and columns’ schemas:

Tables

The Tables schema collection lists all tables in the database, including views.

Retrieving the table listing

To retrieve the Tables schema collection, call the GetSchema method of the CSVConnection class. The code below then prints the names of the Tables (each of which represents a CSV-formatted file in the directory noted in the URI connection property).

C#


String connectionString = "URI=C:\Users\Public\MyCSVs\";
 
using (CSVConnection conn = new CSVConnection(connectionString)) {
  conn.Open();
  DataTable databaseSchema = conn.GetSchema("Tables");
  foreach (DataRow row in databaseSchema.Rows) {
    Console.WriteLine(row["TABLE_NAME"]);
  }
}

VB.NET


Dim connectionString As String = "URI=C:\Users\Public\MyCSVs\"
 
Using conn As New CSVConnection(connectionString)
  conn.Open()
  Dim databaseSchema As DataTable = conn.GetSchema("Tables")
  For Each row As DataRow In databaseSchema.Rows
    Console.WriteLine(row("TABLE_NAME"))
  Next
End Using

Columns

To access metadata for the columns in the database, retrieve the Columns schema collection. For views, you can obtain column metadata by retrieving the ViewColumns schema collection.

Alternatively, you can retrieve metadata from the Result Sets. The result set metadata includes the same columns as the Columns or ViewColumns schema collections. See below.

Retrieving the column metadata

Call the GetSchema method of the CSVConnection class to retrieve the Columns or ViewColumns schema collections. You can restrict results by table name, as shown in the example below.

C#


string connectionString = "URI=C:\Users\Public\MyCSVs\";
 
using (CSVConnection conn = new CSVConnection(connectionString)) {
  conn.Open();
  DataTable databaseSchema = conn.GetSchema("Columns", new string[] {"[MyFile.csv]"});
  foreach (DataRow column in databaseSchema.Rows) {
    Console.WriteLine(column["COLUMN_NAME"]);
    Console.WriteLine(column["IS_KEY"]);
    Console.WriteLine(column["DATA_TYPE"]);
  }
}

VB.NET


Dim connectionString As String = "URI=C:\Users\Public\MyCSVs\"
 
Using conn As New CSVConnection(connectionString)
  conn.Open()
  Dim databaseSchema As DataTable = conn.GetSchema("Columns", New String() {"[MyFile.csv]"})
  For Each column As DataRow In databaseSchema.Rows
    Console.WriteLine(column("COLUMN_NAME"))
    Console.WriteLine(column("IS_KEY"))
    Console.WriteLine(column("DATA_TYPE"))
  Next
End Using

Querying the data and retrieving results

The CData ADO.NET Provider for CSV implements the DataAdapter and DataReader ADO.NET classes for retrieving data from CSV files as CSVDataAdapter and CSVDataReader. The CSVDataAdapter retrieves a complete ResultSet that matches a query, while the CSVDataReader fetches data in subsets as needed.

Using the CSVDataReader

The CSVDataReader retrieves data faster than the CSVDataAdapter because it processes data in pages. As it reads data, it periodically requests the next page of results from the data source when needed, ensuring faster retrieval. The following example selects all columns from the "MyFile.csv" table:

C#


string connectionString = "URI=C:\Users\Public\MyCSVs\";
 
using (CSVConnection connection = new CSVConnection(connectionString)) {
  CSVCommand cmd = new CSVCommand("SELECT * FROM [MyFile.csv]", connection);
   
  CSVDataReader rdr = cmd.ExecuteReader();
 
  while (rdr.Read()) {
    Console.WriteLine(String.Format("	{0} --> 		{1}", rdr["Email"], rdr["Username"]));
  }
}

VB.NET


Dim connectionString As String = "URI=C:\Users\Public\MyCSVs\"
 
Using connection As New CSVConnection(connectionString)
  Dim cmd As New CSVCommand("SELECT * FROM [MyFile.csv]", connection)
 
  Dim rdr As CSVDataReader = cmd.ExecuteReader()
 
  While rdr.Read()
    Console.WriteLine([String].Format(vbTab & "{0} --> " & vbTab & vbTab & "{1}", rdr("Email"), rdr("Username")))
  End While
End Using

Using the CSVDataAdapter

Use the adapter's Fill method to retrieve data from the data source. Pass an empty DataTable instance as an argument to the method. Upon execution, the DataTable is populated with the queried data. Note that CSVDataAdapter is slower than CSVDataReader because the Fill method retrieves all data from the source before returning.

The following example selects the Email and Username columns of the table named "MyFile.csv".

C#


string connectionString = "URI=C:\Users\Public\MyCSVs\";
 
using (CSVConnection connection = new CSVConnection(connectionString)) {
  CSVDataAdapter dataAdapter = new CSVDataAdapter(
  "SELECT Email, Username FROM [MyFile.csv]", connection);
 
  DataTable table = new DataTable();
  dataAdapter.Fill(table);
 
  Console.WriteLine("Contents of MyFile.csv");
 
  foreach (DataRow row in table.Rows) {
    Console.WriteLine("{0}: {1}", row["Email"], row["Username"]);
  }
}

VB.NET


Dim connectionString As String = "URI=C:\Users\Public\MyCSVs\"
 
Using connection As New CSVConnection(connectionString)
  Dim dataAdapter As New CSVDataAdapter("SELECT Email, Username FROM [MyFile.csv]", connection)
 
  Dim table As New DataTable()
  dataAdapter.Fill(table)
 
  Console.WriteLine("Contents of MyFile.csv.")
 
  For Each row As DataRow In table.Rows
    Console.WriteLine("{0}: {1}", row("Email"), row("Username"))
  Next
End Using

Performing actions using stored procedures

Stored procedures are function-like interfaces that extend the functionality of the provider beyond simple SELECT/INSERT/UPDATE/DELETE operations with CSV.

Stored procedures accept a list of parameters, perform their intended function, and then return any relevant response data from CSV, along with an indication of whether the procedure succeeded or failed.

In this section, we'll execute the stored procedure "MoveFile", which helps move a file from local or cloud storage.

Procedure-Specific Information

The procedure accepts the following parameters:

SourcePath: path of the file you request to move.
DestinationPath: path of the folder where you request to move the file.

Both PATH parameters accept relative and absolute paths to the file you request to move:

Usage of absolute path: D:\folder1\file1.csv will move the file from the source path D:\folder1 to the destination path D:\folder2 .
Usage of relative path: file1.csv will move the file from the path mentioned in the {CONNECTION STRING URI}/file1.csv if connection string has the URI set to: D:\folder1, it will move the file from the source path \folder1\file1.csv.

The procedure is executed as below:


EXEC MOVEFILE @SourcePath = "D:\TestFolder\Account.csv" @DestinationPath = "D:\SampleFolder" //absolute path
EXEC MOVEFILE @SourcePath = 'Account.csv' @DestinationPath = 'SampleFolder' //relative path

Free Community License for data developers

CData ADO.NET Providers further enhance the capabilities of ADO.NET by offering consistent, SQL-based connectivity to more than 270 data sources beyond traditional databases, including SaaS, NoSQL, and big data systems. They provide advanced features such as efficient querying with the DataReader and DataAdapter, data modification, data handling, batch processing, transaction management, connection pooling, and the ability to call stored procedures.

With the CData ADO.NET Community License, you get free-forever libraries to access your data in personal .NET projects, all through familiar SQL. Request a license and start building data apps today!