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

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

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

Share on Facebook0Share on Google+0Tweet about this on TwitterShare on LinkedIn0Share on Reddit0
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();
            }

The SQL Server and .NET Hub

Reference: The SQL Server and .NET Hub (http://www.sqlnethub.com)


Recommended eBooks on SQL Server:

Tuning SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Tuning SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Administering SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Administering SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Artemakis Artemiou
Artemakis Artemiou is a Senior SQL Server Architect, Author, Software Developer and a Microsoft Data Platform MVP. He has over 15 years of experience in the IT industry in various roles. Among other, via his initiative SQLEBooks.com, Artemakis authors and publishes eBooks on different topics on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Additionally he is the founder of the SQLArtBits initiative that aims to provide the technical community with simple, yet powerful and high-quality SQL Server tools. Currently, the highlights of these tools are DBA Security Advisor and In-Memory OLTP Simulator. Artemakis's official website can be found at aartemiou.com. Artemakis's blogs can be found at: SQLNetHub.com and TechHowTos.com.