Rhyous

May 28, 2010

How to query a SQL database in C#?

Filed under: C# (C-Sharp) — J. Abram barneck @ 12:42 pm
Tags: , ,

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 String to hold the query.
  3. Create a SqlCommand object and pass the constructor the connection string and the query string.
  4. Use the above SqlCommand object to create a SqlDataReader object.
  5. Create a DataTable object to hold all the data returned by the query.
  6. Use the DataTable.Load(SqlDataReader) function to put the results of the query into a DataTable.
  7. 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.

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.
            string sdwConnectionString = @"Data Source = ServerName; user id=UserName; password=P@sswd!; Initial Catalog = DatabaseName;";

            // 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);

            // 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.
            foreach (DataRow row in dataTable.Rows)
            {
                // Do something with each row
            }
       }
    }
}

So now the results are stored in a DataTable.

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


Copyright ® Rhyous.com – Linking to this page is allowed without permission and as many as ten lines of this page can be used along with this link. Any other use of this page is allowed only by permission of Rhyous.com.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: