ADO.NET Developer Guide - Getting Started

CData ADO.NET Providers are powerful tools that allow developers to connect .NET applications with more than 270 cloud and on-prem 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.

With the right tools, getting started with ADO.NET in a WinForms application can be simple and efficient. This guide provides a step-by-step approach to building a basic Windows Forms (WinForms) application using CData ADO.NET Provider for CSV. The app will mimic a data explorer, allowing users to connect to a CSV file, retrieve table metadata, and query data effortlessly.

Also, please note that, in this guide, we have used csharp programming language to build the application.

The application consists of two primary forms:

  • Connect Form – This is the starting screen, where users enter the file path to a CSV file and establish a connection.
  • Data Explorer – Once connected, users can select a table from a dropdown (retrieved via a metadata call), and the data will be displayed in a DataGridView.

Following this guide will teach you how to set up ADO.NET connectivity, handle metadata retrieval, and display queried data in a WinForms UI. So, let’s dive in!

Please note that while this guide focuses on connecting CSV files, the same principles apply to all 270+ data sources supported by the CData ADO.NET Providers.

Prerequisites

  • Set Up Your Development Environment

    1. Install Visual Studio: If you don't have Visual Studio, download and install it from the Visual Studio website.
    2. Install .NET Desktop Development Workload: Ensure you have the .NET desktop development workload installed. You can add this via Visual Studio Installer.

  • Add CData ADO.NET Provider for CSV

    1. Request a free Community License for the CData ADO.NET Provider for CSV.
    2. Download the Provider: Download the CData ADO.NET Provider for CSV from our website or use the NuGet package manager in Visual Studio.
    3. Install the Provider: Follow the instructions and add your Community License for successful installation.
    4. Download sample CSV files: Download our sample CSV files for testing.

Create a new WinForms project

  1. Open Visual Studio. Click Create a new project.
  2. Select Windows Forms App (.NET Framework) and click Next.
  3. Name your project, add a location, select “Create new solution”, and click Create.

The project is now ready to be used.

Configure the connection to the CSV

  1. Add a reference: In your Visual Studio project, right-click on References in the Solution Explorer panel on the right and select Add Reference.
  2. Browse for the .dll files: In the Reference Manager window, click Browse and navigate to the installation directory of the CData ADO.NET Provider for CSV and add the necessary .dll files. In our case, we add "System.Data.CData.CSV.dll" from the installation directory "C:\Program Files\CData\CData ADO.NET Provider for CSV 20xx\lib".
  3. Once added, you can find the “System.Data.CData.CSV.dll" file under References.

Create and design the Connect form

  1. Rename Form1: Right-click on Form1.cs in the Solution Explorer, select Rename, and change it to ConnectForm.cs.
  2. Open the Form Designer: Double-click ConnectForm.cs to open the form designer (if not already open on the left).
  3. Add controls: Drag and drop a TextBox and a Button onto the form from the Toolbox (Open it using the combo Ctrl+Alt+X (or) View > Toolbox).
    • Select and set the TextBox Name property to “txtFilePath” in the Properties tab.
    • Similarly, select and set the Button Name property to "btnConnect" and the Text property to "Connect".

Once created, the Connect form looks like this:

Note: For better clarity, in the above screenshot we have added a Label below the TextBox from the Toolbox and updated its Text property.

Add code to ConnectForm.cs

  1. Open ConnectForm.cs: Right-click on ConnectForm.cs in the Solution Explorer and select View Code.
  2. Modify the code:

    The first code snippet is a "ConnectForm" class constructor which uses "this.btnConnect.Click += new System.EventHandler(this.btnConnect_Click);" to dynamically associate the click event of the btnConnect button with the btnConnect_Click event handler. This means that whenever the button is clicked, the btnConnect_Click method will execute.

    Additionally, update the ConnectForm text to "Connect" to match the form's purpose using the given code:

    public ConnectForm() { InitializeComponent(); // Subscribe to the Click event of btnConnect here this.btnConnect.Click += new System.EventHandler(this.btnConnect_Click); this.Text = "Connect"; } Now, define the event handler "btnConnect_Click" using this code: private void btnConnect_Click(object sender, EventArgs e) { string filePath = txtFilePath.Text; string connectionString = $"URI={filePath}"; DataExplorerForm dataExplorerForm = new DataExplorerForm(connectionString); dataExplorerForm.Show(); this.Hide(); }

Please note that this code initializes and opens a new "DataExplorerForm", passing a connection string to it. Once you click Connect, the form uses this connection string to load and display data from the selected CSV file in Data Explorer, which is defined in the next section.

Also, to make the application visually appealing, you can also adjust the font size, layout, and overall appearance by modifying the "System.Drawing.Point" and "System.Drawing.Size" co-ordinates in ConnectForm.Designer.cs.

Create and design the Data Explorer form

  1. Add a new form: Right-click on the project (i.e. WindowsFormsApp) in the Solution Explorer and select Add > Form (Windows Forms). The new form gets added as Form1.cs. Rename it to "DataExplorerForm.cs".
  2. Open the Form Designer: Double-click DataExplorerForm.cs to open the form designer (if not already open).
  3. Add controls: Drag and drop a ComboBox, a Button, and a DataGridView onto the form.
    • Select and set the ComboBox Name property to "cmbTables".
    • Select and set the Button Name property to "btnLoadData" and the Text property to "Load Data".
    • Select and set the DataGridView Name property to "dataGridView" and the Dock property to "Bottom" from the dropdown.

Add code to DataExplorerForm.cs

  1. Open DataExplorerForm.cs: Right-click on DataExplorerForm.cs in the Solution Explorer and select View Code.
  2. Modify the code: Make sure to add and reference the "System.Data.CData.CSV" namespace to connect to CSV files. Also, ensure that the specified event handlers are added: using System.Data.CData.CSV; //Reference the namespace to connect CSV files ...

    The constructor “DataExplorerForm” stores the provided connectionString (CSV file path) in a private variable "_connectionString". The rest of the code snippet is explained in comments below:

    private string _connectionString; public DataExplorerForm(string connectionString) { InitializeComponent(); _connectionString = connectionString; // Stores the provided connectionString (CSV file path) in a private variable "_connectionString". // Add the events here this.cmbTables.DropDownStyle = ComboBoxStyle.DropDownList; // Sets the table selection dropdown (cmbTables) to DropDownList mode, meaning the user can only select from predefined table names and cannot type custom values. this.btnLoadData.Click += new System.EventHandler(this.btnLoadData_Click); // Links the btnLoadData button to its click event handler (btnLoadData_Click). When the user clicks Load Data, the event handler will execute and load data from the selected table. this.Load += new System.EventHandler(this.DataExplorerForm_Load); // Associates the form’s Load event with DataExplorerForm_Load, which will initialize and populate the form when it is opened. this.FormClosed += new FormClosedEventHandler(this.DataExplorerForm_FormClosed); // Associates the FormClosed event with DataExplorerForm_FormClosed. This ensures resources are properly released and any cleanup tasks (such as closing database connections) are performed when the form is closed. this.Text = "Data Explorer"; // Sets the form's title to "Data Explorer". }

    Create a custom method which needs to be called manually from the "DataExplorerForm_Load" form load event. LoadTableNames() populates the dropdown with table names (CSV files present in the directory):

    private void LoadTableNames() { try { using (CSVConnection connection = new CSVConnection(_connectionString)) { connection.Open(); DataTable schemaTable = connection.GetSchema("Tables"); cmbTables.DataSource = schemaTable; cmbTables.DisplayMember = "TABLE_NAME"; } } catch (Exception ex) { MessageBox.Show($"Error loading table names: {ex.Message}"); } }

    Add the button click event "btnLoadData_Click" which gets triggered when the user clicks the Load Data button and loads data from the selected table.

    private void btnLoadData_Click(object sender, EventArgs e) { try { string tableName = cmbTables.Text; using (CSVConnection connection = new CSVConnection(_connectionString)) { CSVCommand command = new CSVCommand($"SELECT * FROM [{tableName}]", connection); CSVDataAdapter adapter = new CSVDataAdapter(command); DataTable table = new DataTable(); adapter.Fill(table); dataGridView.DataSource = table; } } catch (Exception ex) { MessageBox.Show($"Error loading data: {ex.Message}"); } }

    Add a form load event "DataExplorerForm_Load", which calls the custom method LoadTableNames() defined earlier, to initialize the form and load table names.

    private void DataExplorerForm_Load(object sender, EventArgs e) { LoadTableNames(); }

    Add a form close event "DataExplorerForm_FormClosed" to exit the application.

    private void DataExplorerForm_FormClosed(object sender, FormClosedEventArgs e) { Application.Exit(); }

You can adjust the font size, layout, and overall appearance by modifying the "System.Drawing.Point" and "System.Drawing.Size" co-ordinates in DataExplorerForm.Designer.cs like what is explained in the previous section.

Run the application

  1. Build the solution: Navigate to Build > Build Solution.
  2. Run the application: Go to Debug > Start Debugging.
  3. Connect to the CSV: Enter the folder path (e.g. "D:\TestFolder") containing the CSV files in the text box and click Connect. The Data Explorer window should appear.
  4. Load the data: In the Data Explorer window, select any CSV file (e.g. "Opportunity.csv" as shown) from the combo box dropdown and click on Load Data.
  5. The CSV table content populates in the data grid box.

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!