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.
- Create a
String
to hold the database connection string.
(Note: If you don’t know the proper format for a connection string useSqlConnectionBuilder
.) - Create a SQL connection object.
- Open the SQL connection.
- Create a
String
to hold the query. - Create a
SqlCommand
object and pass the constructor the connection string and the query string. - Use the above
SqlCommand
object to create aSqlDataReader
object. - Create a
DataTable
object to hold all the data returned by the query. - Use the
DataTable.Load(SqlDataReader)
function to put the results of the query into aDataTable
. - 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. - 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.
Hi,
Where does “sdwDBConnection” come from and what is it’s purpose? I get Error CS0103 The name ‘sdwDBConnection’ does not exist in the current context
Thanks,
Will
My apologies. I moved all my WPF posts to this blog and I didn’t know some non-WPF posts moved here. I had this all cleaned up and error free over here: http://www.rhyous.com/2010/05/28/how-to-query-a-database-in-csharp/
dog walking Sydney
How to query a SQL database in C#? | WPF