Posts tagged ‘DataTable’

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#