Connecting to SQL Server

Recipe 1.1. Connecting to SQL Server

1.1.1. Problem

You want to connect to a SQL Server database.

1.1.2. Solution

You can connect to an SQL Server database using the SQL Server .NET data provider, the OLE DB .NET data provider, or the ODBC .NET data provider.

The solution creates and opens a connection to an SQL Server database using the SQL Server .NET data provider, OLE DB .NET data provider, and ODBC.NET data provider in turn. In each case, information about the connection is displayed from the properties of the connection object.

The C# code in Program.cs in the project ConnectSqlServer is shown in Example 1-1.

Example 1-1. File: Program.cs for ConnectSqlServer solution

Code View:

using System;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data.Odbc;


namespace ConnectSqlServer
{
    class Program
    {
        static void Main(string[] args)
        {
            // Connect using .NET data provider for SQL Server and integrated security
            string sqlConnectString1 = "Data Source=(local);Initial Catalog=AdventureWorks;Integrated security=SSPI;";
            using (SqlConnection connection = new SqlConnection(sqlConnectString1))
            {
                connection.Open();
                Console.WriteLine("---.NET data provider for SQL Server with Windows Authentication mode---");
                Console.WriteLine("ConnectionString = {0}\n", sqlConnectString1);
                Console.WriteLine("State = {0}", connection.State);
                Console.WriteLine("DataSource = {0}", connection.DataSource);
                Console.WriteLine("ServerVersion = {0}", connection.ServerVersion);
            }
            // Connect using .NET data provider for SQL Server and SQL Server authentication
            string sqlConnectString2 = "Data Source=(local);Initial Catalog=AdventureWorks;User Id=sa;Password=password;";
            using (SqlConnection connection = new SqlConnection(sqlConnectString2))
            {
                connection.Open();
                Console.WriteLine("\n---.NET data provider for SQL Server with SQL Server Authentication mode---");
                Console.WriteLine("ConnectionString = {0}\n", sqlConnectString2);
                Console.WriteLine("State = {0}", connection.State);
                Console.WriteLine("DataSource = {0}", connection.DataSource);
                Console.WriteLine("ServerVersion = {0}", connection.ServerVersion);
            }
            // Connect using .NET data provider for OLE DB.
            string oledbConnectString = "Provider=SQLOLEDB;Data Source=(local);Initial Catalog=AdventureWorks;User Id=sa;Password=password;";
            using (OleDbConnection connection = new OleDbConnection(oledbConnectString))
            {
                connection.Open();
                Console.WriteLine("\n---.NET data provider for OLE DB---");
                Console.WriteLine("ConnectionString = {0}\n", oledbConnectString);
                Console.WriteLine("State = {0}", connection.State);
                Console.WriteLine("DataSource = {0}", connection.DataSource);
                Console.WriteLine("ServerVersion = {0}", connection.ServerVersion);
            }
            // Connect using .NET data provider for ODBC.
            string odbcConnectString = "Driver={SQL Native Client};Server=(local);Database=AdventureWorks;uid=sa;pwd=password;";
            using (OdbcConnection connection = new OdbcConnection(odbcConnectString))
            {
                connection.Open();
                Console.WriteLine("\n---.NET data provider for ODBC---");
                Console.WriteLine("ConnectionString = {0}\n", odbcConnectString);
                Console.WriteLine("State = {0}", connection.State);
                Console.WriteLine("DataSource = {0}", connection.DataSource);
                Console.WriteLine("ServerVersion = {0}", connection.ServerVersion);
            }
            Console.WriteLine("\nPress any key to continue.");
            Console.ReadKey();
        }
    }
}

advice you to use the first data provider.

Additionally, the web site http://www.connectionstrings.com/ shows you how to construct connection strings for a wide variety of database servers and other data sources, such as DB2,ORACLE,SYBSAE.