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#

24 Comments

  1. Advances in digital technologies mean that sportsbooks can update odds instantaneously through
    a match or race.

    My web site :: 안전놀이터

  2. Slot5000 says:

    I really like it when folks get together and share opinions.
    Great website, continue the good work!

    Here is my page Slot5000

  3. Queen says:

    want to ask : can we use datatable as a "table" and we mention it in the query << select * into "target table" from datatable ??
    my problem is that i have 2 database on 2 different servers . i have to extract data from database1 and put it into database2 . pleeeeeeeeeeeeeeeeease help.

  4. […] like this approach is working for me. Thanks […]

  5. Washington says:

    Washington

    How to execute a sql query in c# | Rhyous

  6. mcalex says:

    Entity Framework is your friend

  7. Jerry says:

    Here is the cleanest way I have been able to write this. I am calling a stored procedure, but you could be calling a table if needed. I am utilizing this with jQuery Ajax returning JSON. Hope this helps some.

    Notes: My connection string is stored in my Web.config for reusability and best practice.

       object result = null;
       SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionStringNameInWebConfig"].ConnectionString);
       SqlCommand cmd = new SqlCommand("Stored_Proc_Name", conn);
       cmd.CommandType = CommandType.StoredProcedure;
       cmd.Parameters.Add(new SqlParameter("@ParameterNameIfNeeded", valueofparameter)); 
       conn.Open();
       SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
       result = rdr.Cast<IDataRecord>();
         .Select(dr => new { Col_3_index_Value = dr.GetString(3), Col_7_Index_Value = dr.GetString(7) }).ToList();
          // The above statement selects the desired columns by index the assigned name can be whatever you like.
       return result;
    
  8. Unable to Update rows of DataGridView from DB Query says:

    […] tried the following link to assist https://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 […]

  9. How to query a SQL database in C# | Something Different says:

    […] ? […]

  10. 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

  11. 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.

  12. 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();

    }

  13. Daniel C. says:

    Thank you. It is what I wanted.

    regartds.

  14. Jonesm8 says:

    MUCH thanks. This was very helpful.

  15. kavi says:

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

  16. yimb0 says:

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

    Thanks you
    y.-

  17. David Fritch says:

    Thanks, Rhyous. Very helpful. I am a newbie. 🙂

  18. [...] 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 [...]

  19. 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

How to post code in comments?