How to query a SQL database in C#?

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#

17 Comments

  1. […] tried the following link to assist http://www.rhyous.com/2010/05/28/how-to-query-a-database-in-csharp/ and tried manually modifying to return to the method which would handle the addition of rows. This […]

  2. Wizgang says:

    so how do I return all of the rows in a table? Sometimes I only have 1 or 2 rows, other time or for other tables, I have hundreds.

    Thank you,
    David

  3. Aaron says:

    THANK YOU! I have been looking way too long for this information. I am very surprised how difficult is has been to find a clean walk-through overview of how this works, without the author assuming the reader understands more that she/he does.

    You've done a good service by posting this.

  4. erhan says:

    I want to run below script before running select statement in C# using oracle but I am getting
    ORA-00900: invalid SQL statement exception ? what i am doing wrong.

    conn1.Open();
    using (OracleCommand myCommand = new OracleCommand
    (
    "exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES', false)", conn1))
    {
    myCommand.ExecuteNonQuery();

    }

  5. Daniel C. says:

    Thank you. It is what I wanted.

    regartds.

  6. Jonesm8 says:

    MUCH thanks. This was very helpful.

  7. kavi says:

    If I want to put the results in the website how do I do it?

  8. yimb0 says:

    Hello fellow,
    Im thinking about "open connection", did you forget close a connection or is not needed???

    Thanks you
    y.-

  9. David Fritch says:

    Thanks, Rhyous. Very helpful. I am a newbie. :)

  10. [...] using a DataTable in C# June 6, 2012, 12:55 pm by Rhyous You may have already read How to query a SQL database in C#? and How to insert a row into a Microsoft SQL database using C#? but you are looking for more [...]

  11. David says:

    Hi, great code, but for a beginner it's like half a story!

    // Do something with each row

    I just want to show one row of data for a test, but my output is blank, Im trying the following with the help of Google:-

    // Do something with each row
    System.Console.WriteLine(row);
    Console.WriteLine(row["name"].ToString());
    Console.Write("Item: ");

    no luck, I know this may seem remedial to the experts, but we all start somwhere, for the sake of stress levels, please show an example where somthing gets displayed on the screen, then I know Im using it properly.

Leave a Reply

Powered by sweet Captcha