When developing a .NET application that needs to connect to a SQL Server database, it is important to understand the different options available for creating connection strings. In this tutorial, we will explore various ways to connect to Microsoft SQL Server using different connection string properties in .NET.

SqlClient Connection Strings

SqlClient is a popular choice for connecting to SQL Server databases in .NET. Here are some examples of SqlClient connection strings:

• Connecting using a SQL Server login:

Server=ServerName;Database=DatabaseName;User Id=Username;Password=Password;

• Connecting to localhost using Windows Authentication:

Server=.;Database=DatabaseName;Trusted_Connection=True;

• Connecting to a named instance using a port number on localhost using Windows Authentication:

Server=.\instancename,51688;Database=DatabaseName;Trusted_Connection=True;

• Connecting to SQL Server Express on localhost using Windows Authentication:

Server=.\SQLExpress;Database=DatabaseName;Trusted_Connection=True;

ODBC Connection Strings

ODBC is another option for connecting to SQL Server databases. Here are some examples of ODBC connection strings:

• Connecting using a SQL Server login:

Driver={ODBC Driver 17 for SQL Server};Server=ServerName;Database=DatabaseName;UID=Username;PWD=Password;

• Connecting using Windows Authentication:

Driver={ODBC Driver 17 for SQL Server};Server=ServerName;Database=DatabaseName;Trusted_Connection=yes;

• Connecting to a named instance using Windows Authentication:

Driver={ODBC Driver 17 for SQL Server};Server=ServerName\InstanceName;Database=DatabaseName;Trusted_Connection=yes;

• Connecting using Windows Authentication and specifying a failover server:

Driver={ODBC Driver 17 for SQL Server};Server=ServerName;Failover_Partner=FailoverServerName;Database=DatabaseName;Trusted_Connection=yes;

• Connecting using a DSN and using Windows Authentication:

Driver={ODBC Driver 17 for SQL Server};Dsn=DsnName;Trusted_Connection=yes;

OLEDB Connection Strings

OLEDB is another option for connecting to SQL Server databases. Here are some examples of OLEDB connection strings:

• Connecting using Windows Authentication:

Provider=MSOLEDBSQL;Server=ServerName;Database=DatabaseName;Integrated Security=SSPI;

• Connecting to an Availability Group using Windows Authentication:

Provider=MSOLEDBSQL;Server=tcp:AvailabilityGroupListenerDnsName,55001;MultiSubnetFailover=Yes;Database=DatabaseName;Integrated Security=SSPI;Connect Timeout=30;

• Connecting using a SQL Server login and encrypting the connection:

Provider=MSOLEDBSQL;Server=ServerName;Database=DatabaseName;UID=Username;PWD=Password;Encrypt=yes;

• Connecting using Windows Authentication for database mirroring:

Provider=MSOLEDBSQL;Data Source=ServerName;Failover Partner=MirrorServerName;Database=DatabaseName;Integrated Security=SSPI;

Code Samples

Here are code samples that demonstrate how to connect to a SQL Server database using different connection strings:

SqlClient Example

using System;
using System.Data.SqlClient;

namespace LanguageDetectionExample
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                SqlConnectionStringBuilder conn = new SqlConnectionStringBuilder();
                conn.ConnectionString = "Server=.;Database=AdventureWorks2019;Trusted_Connection=True;";

                using (SqlConnection connection = new SqlConnection(conn.ConnectionString))
                {
                    String sql = "SELECT CurrencyCode,Name from Sales.Currency";
                    using (SqlCommand sqlcommand = new SqlCommand(sql, connection))
                    {
                        connection.Open();
                        using (SqlDataReader reader = sqlcommand.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                Console.WriteLine("\t{0}\t{1}\n", reader.GetString(0), reader.GetString(1));
                            }
                        }
                    }
                }
            }
            catch (SqlException e)
            {
                Console.WriteLine(e.ToString());
            }
            Console.ReadLine();
        }
    }
}

ODBC Example

using System;
using System.Data.Odbc;

namespace odbc_sample
{
    class Program
    {
        static void Main(string[] args)
        {
            string connectionString = "Driver={ODBC Driver 17 for SQL Server};Server=.;Database=AdventureWorks2019;Trusted_Connection=yes;";
            string query = "SELECT CurrencyCode,Name from Sales.Currency";

            OdbcConnection connection = new OdbcConnection(connectionString);

            OdbcCommand cmd = new OdbcCommand(query);

            cmd.Connection = connection;

            connection.Open();

            OdbcDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                Console.WriteLine("\t{0}\t{1}\n", reader.GetString(0), reader.GetString(1));
            }

            reader.Close();
            connection.Close();
        }
    }
}

OLEDB Example

using System;
using System.Data.OleDb;

namespace oledbreader_example
{
    class Program
    {
        static void Main(string[] args)
        {
            string connetionString = null;
            OleDbConnection connection;
            OleDbCommand cmd;
            string query = null;
            OleDbDataReader reader;

            connetionString = "Provider=MSOLEDBSQL;Server=.;Database=adventureworks2019;Integrated Security=SSPI;";
            query = "SELECT CurrencyCode,Name from Sales.Currency";

            connection = new OleDbConnection(connetionString);
            try
            {
                connection.Open();
                cmd = new OleDbCommand(query, connection);
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Console.WriteLine("\t{0}\t{1}\n", reader.GetString(0), reader.GetString(1));
                }
                reader.Close();
                cmd.Dispose();
                connection.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }
        }
    }
}

By understanding the different connection string options available in .NET, you can effectively connect to SQL Server databases and retrieve data for your .NET applications.

Article Last Updated: 2022-04-28