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

This article, provides a basic example, on how to establish a connection to SQL Server from a C# program with the use of the “using” statement in C# which help you better managing your database connections.

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

using System.Data.SqlClient;

Setting the Connection String

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;";

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

Featured Online Courses:

• SQL Server 2022: What’s New – New and Enhanced Features 
• Data Management for Beginners – Main Principles
• Introduction to Azure Database for MySQL
• Working with Python on Windows and SQL Server Databases
• Boost SQL Server Database Performance with In-Memory OLTP
• Introduction to Azure SQL Database for Beginners
• Essential SQL Server Administration Tips
• SQL Server Fundamentals – SQL Database for Beginners
• Essential SQL Server Development Tips for SQL Developers
• Introduction to Computer Programming for Beginners
• .NET Programming for Beginners – Windows Forms with C#
• SQL Server 2019: What’s New – New and Enhanced Features
• Entity Framework: Getting Started – Complete Beginners Guide
• A Guide on How to Start and Monetize a Successful Blog
• Data Management for Beginners – Main Principles