C# Connection Strings

Connection strings are a fundamental concept in database programming with C#. They provide all the necessary information your application needs to establish a connection to a database. Think of them as the address and credentials that your application uses to find and access a database.

Introduction to Connection Strings

A connection string is a text string that includes parameters such as:

• The server or database location
• The database name
• Authentication credentials
• Other connection-specific settings

In C# applications, you'll typically use connection strings with ADO.NET, Entity Framework, or other data access technologies to connect to various database systems like SQL Server, MySQL, SQLite, and more.

Basic Structure of a Connection String

While the exact format varies depending on the database provider, most connection strings follow this pattern:

Provider=value;Server=value;Database=value;User ID=value;Password=value;

Each part of the string is a key-value pair separated by semicolons.

Common Connection Strings by Database Type

SQL Server Connection String

csharp

// SQL Server connection string using SQL Server Authentication
string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;";

// SQL Server connection string using Windows Authentication
string connectionStringWinAuth = "Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;";

MySQL Connection String

csharp

string mySqlConnectionString = "Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;";

SQLite Connection String

csharp

string sqliteConnectionString = "Data Source=myDatabase.sqlite;Version=3;";

Oracle Connection String

csharp

string oracleConnectionString = "Data Source=MyOracleDB;User Id=myUsername;Password=myPassword;";

Using Connection Strings in C# Code

Let's see how to use a connection string with ADO.NET to connect to a SQL Server database:

csharp

using System;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        // Define the connection string
        string connectionString = "Server=localhost\\SQLEXPRESS;Database=SampleDB;Trusted_Connection=True;";
        
        // Create a SQL connection object
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            try
            {
                // Open the connection
                connection.Open();
                Console.WriteLine("Successfully connected to the database!");
                
                // Here you would perform database operations
                
                // Connection is automatically closed when the using block exits
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error connecting to the database: " + ex.Message);
            }
        }
    }
}

Storing Connection Strings Securely

Hard-coding connection strings in your application code is generally not recommended for security and maintenance reasons. Instead, use configuration files to store them:

In App.config or Web.config (for .NET Framework)

xml

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <add name="MyDbConnection" 
         connectionString="Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;" 
         providerName="System.Data.SqlClient" />
  </connectionStrings>
</configuration>

Then access it in your code:

csharp

using System.Configuration;

// Get the connection string from App.config
string connectionString = ConfigurationManager.ConnectionStrings["MyDbConnection"].ConnectionString;

In appsettings.json (for .NET Core and .NET 5+)

json

{
  "ConnectionStrings": {
    "MyDbConnection": "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;"
  }
}

Then access it in your code:

csharp

using Microsoft.Extensions.Configuration;

// Assuming you've set up the configuration in your startup code
IConfiguration configuration = // ... get the configuration

// Get the connection string
string connectionString = configuration.GetConnectionString("MyDbConnection");

Connection String Parameters

Different database providers have different connection string parameters. Here are some common ones for SQL Server:

Parameter	Description
Server / Data Source	The server name or IP address
Database / Initial Catalog	The database name
User ID / UID	Username for SQL Authentication
Password / PWD	Password for SQL Authentication
Trusted_Connection	When set to "True", uses Windows Authentication
Integrated Security	Similar to Trusted_Connection
Connection Timeout / Connect Timeout	Time (in seconds) to wait while trying to connect
Encrypt	When "True", SQL Server uses SSL encryption for all data
MultipleActiveResultSets	When "True", allows multiple active result sets (MARS)

Practical Example: Reading Data with a Connection String

Let's see a complete example of connecting to a database and reading data:

csharp

using System;
using System.Data;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString = "Server=localhost\\SQLEXPRESS;Database=Northwind;Trusted_Connection=True;";
        string query = "SELECT TOP 5 ProductName, UnitPrice FROM Products";
        
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            SqlCommand command = new SqlCommand(query, connection);
            
            try
            {
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                
                Console.WriteLine("Product Name | Unit Price");
                Console.WriteLine("-------------|------------");
                
                while (reader.Read())
                {
                    string productName = reader["ProductName"].ToString();
                    decimal unitPrice = (decimal)reader["UnitPrice"];
                    Console.WriteLine($"{productName} | ${unitPrice}");
                }
                
                reader.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: " + ex.Message);
            }
        }
    }
}

Output:

Product Name | Unit Price
-------------|------------
Chai | $18.00
Chang | $19.00
Aniseed Syrup | $10.00
Chef Anton's Cajun Seasoning | $22.00
Chef Anton's Gumbo Mix | $21.35

Connection String Builders

To construct connection strings programmatically in a type-safe way, you can use connection string builders:

csharp

using System;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        // Creating a connection string using SqlConnectionStringBuilder
        SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder
        {
            DataSource = "localhost\\SQLEXPRESS",
            InitialCatalog = "Northwind",
            IntegratedSecurity = true,
            // For SQL Authentication instead of Windows Authentication:
            // UserID = "username",
            // Password = "password",
            ConnectTimeout = 30,
            Encrypt = true,
            TrustServerCertificate = true
        };
        
        string connectionString = builder.ConnectionString;
        Console.WriteLine(connectionString);
        
        // Now use this connection string to connect to the database
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            try
            {
                connection.Open();
                Console.WriteLine("Connected successfully!");
                // Perform database operations here
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: " + ex.Message);
            }
        }
    }
}

Output:

Data Source=localhost\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True;Connect Timeout=30;Encrypt=True;Trust Server Certificate=True
Connected successfully!

Connection Pooling

Connection strings can include parameters that control connection pooling, a performance optimization feature that reuses database connections:

csharp

string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;Min Pool Size=5;Max Pool Size=100;";

Connection pooling is enabled by default in ADO.NET. The above example sets the minimum number of connections to keep in the pool to 5 and the maximum to 100.

Summary

Connection strings are essential for connecting C# applications to databases. Key points to remember:

1. Connection strings contain all the information needed to connect to a database
2. Different database systems have different connection string formats
3. Store connection strings in configuration files, not in code
4. Use connection string builders for type-safe construction
5. Consider connection pooling for performance optimization

By understanding how to create and manage connection strings effectively, you'll build more secure and maintainable database applications.

Additional Resources

• Microsoft ADO.NET Connection Strings Documentation
• ConnectionStrings.com - A reference for connection string formats for various database systems

Exercises

1. Create a console application that connects to a SQL Server database and lists all tables.
2. Modify an existing application to store its connection string in a configuration file rather than in code.
3. Create a small application that allows users to input connection string parameters and tests if the connection can be established successfully.
4. Compare the performance of your database operations with and without connection pooling enabled.