Post
Ask Question

Configuring an SQL Server Connection String

Configuring an SQL Server Connection String

The SQL Server provider enables you to access SQL Server 7.0 and later. If you need to connect to SQL Server 6.5 and earlier, use the OLEDB provider. The classes are located in the System.Data.SqlClient namespace.

 

Following code creates and opens a connection to an SQL Server database using the SQL Server .NET data provider using integrated security and SQL Server authentication


using System;
using System.Data.SqlClient;

namespace SqlServerConnectionString
{
    class Program
    {
        static void Main(string[] args)
        {
            // Connect using  SQL Server data provider and integrated security
            string sqlConnectString = "Data Source=(local);Integrated security=SSPI;Initial Catalog=AdventureWorks;";
            
            using (SqlConnection connection = new SqlConnection(sqlConnectString))
            {
                string sqlSelect = "SELECT TOP 4 ProductID, Name, ProductNumber FROM Production.Product";    
                SqlCommand command = new SqlCommand(sqlSelect, connection);

                // Execute the DataReader
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();

                // Output the data from the DataReader to the console
                Console.WriteLine("ProductID\tProductName\t\tProductNumber\n");
                while (reader.Read())
                    Console.WriteLine("{0}\t\t{1}\t\t{2}\n\n", reader[0], reader[1], reader[2]);
            }

            // Connect using  SQL Server data provider and SQL Server authentication
            string sqlConnectString1 = "Data Source=(local);Initial Catalog=AdventureWorks;User Id=sa;Password=password;";

            using (SqlConnection connection = new SqlConnection(sqlConnectString1))
            {
                string sqlSelect = "SELECT TOP 4 Title, FirstName, LastName FROM Person.Contact";

                SqlCommand command = new SqlCommand(sqlSelect, connection);

                // Execute the DataReader
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();

                // Output the data from the DataReader to the console

                while (reader.Read())
                    Console.WriteLine("{0} {1} {2}", reader[0], reader[1], reader[2]);

            }            

            Console.WriteLine("\nPress any key to continue.");
            Console.ReadLine();

        }
    }
}


This connection string uses the TCP sockets library (DBMSSOCN) and connects to the AdventureWorks database on the computer located at IP address 10.1.2.3, using port 1422. Authentication is based on using sa as the user name and 1245 as the password.

Network Library=DBMSSOCN;

Data Source=10.1.2.3,1422;

Initial Catalog=AdventureWorks;

User ID=sa;

Password=1245


To attach a local database file, you can use the following connection string.

Data Source=.\SQLEXPRESS;

AttachDbFilename=C:\MyApplication\AdventureWorks.MDF;

Integrated Security=True;

User Instance=True;

 

AttachDBFile can also understand the keyword |DataDirectory| to use the application's data directory. Here is the revised connection string.

Data Source=.\SQLEXPRESS;

AttachDbFilename=|DataDirectory|\AdventureWorks.MDF;

Integrated Security=True;

User Instance=True



Next Recommended Reading Configuring an OLEDB Connection String