About
In this code snippet, I’ll show you how to connect to and query an SQL database with C# in .NET. I’ll also show you how to perform the queries properly by using “parameterized queries” to avoid SQL injection vulnerabilities.
To learn more about SQL queries themselves check out this SQL Cheatsheet post I made.
Note: If you are working on a larger project with lots of objects/models that read/save data from/to the databse consider using an ORM(object relational mapping) framework like Entity Framework.
Let’s have a look at the code below to see how to make SQL queries in C#.
Prerequisites:
Before getting started you need to install the SQL client NuGet package into your project: System.Data.SqlClient
using System.Data.SqlClient;
Code Example
using System.Data.SqlClient;
namespace SqlConnectionExample
{
internal class Program
{
static void Main(string[] args)
{
//Add your connections string here.
//Note: It's best not to hardcode your connection string in your code. Use environment variables for production.
string connectionString = "Server=yourServerAddress;Database=yourDataBaseName;User Id=yourUsername;Password=yourPassword;";
//Create an SQL client.
using (SqlConnection connection = new SqlConnection(connectionString))
{
try
{
//Establish a connection.
connection.Open();
//Create a list of SQL commands.
List<SqlCommand> commands = new List<SqlCommand>();
#region Regular SQL Query /////////////////////////////////////////////////////////////////
//Define your SQL query.
string query = "SELECT * FROM myTable";
//Create a command and add it to the list of commands to be executed.
commands.Add(new SqlCommand(query, connection));
#endregion ////////////////////////////////////////////////////////////////////////////////
#region Parameterized SQL Query ///////////////////////////////////////////////////////////
//Define your SQL query with a parameter by prefixing your parameter with an @
string userInput = "unsanitizedInput";
string paramsQuery = $"SELECT * FROM myTable WHERE myRowKey LIKE @userInput";
//Create a new SQL command with the parameterized query.
SqlCommand paramCommand = new SqlCommand(paramsQuery, connection);
//And add the parameter to the command like so ...
paramCommand.Parameters.Add(new SqlParameter("@userInput", userInput));
//... or like so:
paramCommand.Parameters.AddWithValue("@userInput", userInput);
//Add the command to the list of commands to be executed.
commands.Add(paramCommand);
#endregion ////////////////////////////////////////////////////////////////////////////////
foreach (SqlCommand command in commands)
{
//If your commands take a very long time you might want to up the timeout limit like so:
//command.CommandTimeout = 120;
//Execute the query and read the data.
using (SqlDataReader reader = command.ExecuteReader()) //use command.ExecuteNonQuery() for non-query commands such as INSERT, UPDATE, DELETE
{
while (reader.Read())
{
//Do stuff with the data ...
Console.WriteLine($"{reader[0]} {reader[1]} {reader[2]} ...");
//Or use a loop ...
for (int i = 0; i < reader.FieldCount; i++)
{
Console.WriteLine(reader[i]);
}
}
}
}
}
catch (Exception ex)
{
Console.WriteLine($"Error: {ex.Message}");
}
}
}
}
} 
