How to query a SQL database in C#? or How to execute a database query against a database in C#?

Having used other languages where this is much simpler, I was surprised at how “not simple” this was in C#. I expected it to be a little more complex than in some scripting language such as PHP, but it was way more complex.

It is nice to run the Query and store the results in a DataTable, so that is what my example shows.

There are a few simple steps to remember.

1. Create a String to hold the database connection string.
   (Note: If you don’t know the proper format for a connection string use SqlConnectionBuilder.)
2. Create a SQL connection object.
3. Open the SQL connection.
4. Create a String to hold the query.
5. Create a SqlCommand object and pass the constructor the connection string and the query string.
6. Use the above SqlCommand object to create a SqlDataReader object.
7. Create a DataTable object to hold all the data returned by the query.
8. Use the DataTable.Load(SqlDataReader) function to put the results of the query into a DataTable.
9. Do something with the data in your DataTable here. For example, it is common to use a foreach loop to do something with each row.
10. Close the SQL connection.

Here is how I do it:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace CountRows
{
    class Program
    {
        static void Main(string[] args)
        {
            // Create a String to hold the database connection string.
            // NOTE: Put in a real database connection string here or runtime won't work
            string sdwConnectionString = @"Data Source = ServerName; user id=UserName; password=P@sswd!; Initial Catalog = DatabaseName;";

            // Create a connection
            SqlConnection sdwDBConnection = new SqlConnection(sdwConnectionString);

            // Open the connection
            sdwDBConnection.Open();

            // Create a String to hold the query.
            string query = "SELECT * FROM MyTable";

            // Create a SqlCommand object and pass the constructor the connection string and the query string.
            SqlCommand queryCommand = new SqlCommand(query, sdwDBConnection);

            // Use the above SqlCommand object to create a SqlDataReader object.
            SqlDataReader queryCommandReader = queryCommand.ExecuteReader();

            // Create a DataTable object to hold all the data returned by the query.
            DataTable dataTable = new DataTable();

            // Use the DataTable.Load(SqlDataReader) function to put the results of the query into a DataTable.
            dataTable.Load(queryCommandReader);

            // Example 1 - Print your  Column Headers
            String columns = string.Empty;
            foreach (DataColumn column in dataTable.Columns)
            {
                columns += column.ColumnName + " | ";
            }
            Console.WriteLine(columns);

            // Example 2 - Print the first 10 row of data
            int topRows = 10;
            for (int i = 0; i < topRows; i++)
            {
                String rowText = string.Empty;
                foreach (DataColumn column in dataTable.Columns)
                {
                    rowText += dataTable.Rows[i][column.ColumnName] + " | ";
                }
                Console.WriteLine(rowText);
            }

            // Close the connection
            sdwDBConnection.Close();
        }
    }
}

So now the results are stored in a DataTable.

You can now access each row of data using the DataTable.Rows collection.

Return to ADO.NET and Database with C#