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:

[sourcecode language=”csharp”]
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();
}
}
}
[/sourcecode]

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#

Leave a Reply

Your email address will not be published. Required fields are marked *


*

  1. Pingback: Washington

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

    [csharp]
    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;
    [/csharp]

  3. Pingback: Unable to Update rows of DataGridView from DB Query

  4. Pingback: How to query a SQL database in C# | Something Different

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

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

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

    }

    • I assume you are using an ASP.NET application? If so, you create an ASP.NET application. Add a list control. Then have this object in your code to populate the list control.

  8. Hello fellow,
    Im thinking about “open connection”, did you forget close a connection or is not needed???

    Thanks you
    y.-

  9. Pingback: How to insert table rows using a DataTable in C# | Rhyous

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