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.

 

Using the System.Data.SqlClient

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

 


Get Started with .NET Programming Fast and Easy – Enroll to the Online Course!

Check our online course titled “.NET Programming for Beginners: Windows Forms (C#)
(special limited-time discount included in link).

Learn how to implement Windows Forms projects in .NET using Visual Studio and C#, how to implement multithreading, how to create deployment packages and installers for your .NET Windows Forms apps using ClickOnce in Visual Studio, and more! 

Many live demonstrations and downloadable resources included!

.NET Programming for Beginners - Windows Forms with C# - Online Course
(Lifetime Access, Certificate of Completion, downloadable resources and more!)

Enroll Now with Discount!


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

 

The “Using” Keyword in C#

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

 

Did you find this article useful and interesting?

Interested in SQL Server Development? Find hundreds of useful SQL Server administration and troubleshooting articles in 9 Times Microsoft Data Platform MVP, Artemakis’ Artemiou eBook: “Developing with SQL Server (Second Edition)“.

Check our other related .NET articles.

Check out our latest software releases!

Subscribe to our newsletter and stay up to date!

 

Rate this article: 1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 5.00 out of 5)

Loading...

Reference: SQLNetHub.com (https://www.sqlnethub.com)

© SQLNetHub