How to Establish a Simple Connection from a C# Program to SQL Server

This is a basic example on how to establish a connection to SQL Server from a C# program.

Use the System.Data.SqlClient namespace in the top section of your code as below:
using System.Data.SqlClient;

Set the connection string - Trusted connection (Windows Authentication). In the below example I'm using "master" as the default database. You can replace "master" with the default database you wish to set:

string connString = @"Server=INSTANCE_NAME;Database=master;Trusted_Connection = True;";

In case you want to set a connection string with standard security (username/password - not recommended) then you can set the connection string based on the below example:

string connString = @"Server=INSTANCE_NAME;Database=master;User ID=USERNAME;Password=PASSWORD";

With the "using" keyword, .NET automatically manages the task of closing the connection to the database. This an easy way to avoid "connection leak" issues. Here's the code example:

 using (SqlConnection conn = new SqlConnection(connString))
            {
                //set the command to execute against SQL Server (this is where you set your query)
                string query = @"SELECT [fileid],[filename] FROM sysfiles";
                SqlCommand cmd = new SqlCommand(query, conn);

                //open connection
                conn.Open();

                //the actual command execution
                SqlDataReader dr = cmd.ExecuteReader();

                //if reader has any rows retrieve them
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        //handle the retrieved record (i.e. display it)
                        MessageBox.Show(dr.GetInt16(0) + " - " + dr.GetString(1));
                    }
                }
                else
                {
                    MessageBox.Show("No data found.");
                }
                dr.Close();
            }