How to query a SQL database in C# using TableAdapters

Visual Studio has some great features to help you access the database and create objects for your database. You could manually create a connection string and manually create objects that represent the data in your database described here: How to query a SQL database in C#?. This article can show you how Visual Studio can do this for you.

So how is it done? By adding a Data Source.

Imagine you have a simple database for authentication with these tables:

User
- Id INT AUTOINCREMENT
- UserName VARCHAR(100)
- Password VARCHAR(MAX)
- Salt VARCHAR(MAX)

Person
- Id INT AUTOINCREMENT
- FirstName VARCHAR(255)
- LastName VARCHAR(255)
- Birthdate DATETIME
- UserId int FK to User.Id

Now imagine that you want to query these tables and use the data in your application.

Step 1 – Create a Visual Studio Project

  1. In visual studio create a new C# Console Application project.
  2. Once you have the project created, click on Project | Add New Data Source.
  3. Select Database and click Next.
  4. Select DataSet and click Next.
  5. Click New Connection and follow the wizard to connect to your database.
  6. Make sure that Yes, save the connection as is checked and give your saved connection a name and click Next.
  7. Click the checkbox next to Tables and click Finish.

This adds the following files to your project (the names might be slightly different on yours):

  • AuthDataSet.xsd
  • AuthDataSet.Designer.cs
  • AuthDataSet.xsc
  • AuthDataSet.xss

This code will add table adapters to your project. This basically does a lot of work for you and can save you a lot of potential development time.

Step 2 – Query a SQL Database using the Table Adapter

Now you can get the data from either of your tables with one line of code:

using System;
using System.Data;
using TableAdapterExample.AuthDataSetTableAdapters;

namespace TableAdapterExample
{
    class Program
    {
        static void Main(string[] args)
        {
            // Query the database (select * from Person) into a DataTable
            AuthDataSet.PersonDataTable table = new PersonTableAdapter().GetData();

            // Print out the table as proof.
            PrintDataTable(table);
        }

        /// How to print a DataTable
        private static void PrintDataTable(AuthDataSet.PersonDataTable table)
        {
            foreach (DataRow row in table.Rows)
            {
                foreach (DataColumn col in table.Columns)
                {
                    Console.Write(row[col].ToString().Trim() + " ");
                }
                Console.WriteLine();
            }
        }
    }
}

Hope that helps you.

2 Comments

  1. daniel fensterheim says:

    I am a bit late for this but:
    using TableAdapterExample.AuthDataSetTableAdapters; returns an error:"the type or namespace name 'AuthDataSetTableAdapters' does not exist in namespace 'TableAdapterExample'"
    I am quite new to C#

    What should I do to resolve this?

    • Kris W says:

      ignore the "USING" and instead

      AuthDataSet.PersonDataTable table = new PersonTableAdapter().GetData();

      write

      AuthDataSet.PersonDataTable table = new AuthDataSetTableAdapters.PersonTableAdapter().GetData();

Leave a Reply

How to post code in comments?