ADO.NET 3.5 Cookbook, 2nd Edition

1.3. Connecting to SQL Server

Problem

You want to connect to a SQL Server database.

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-4.

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

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);" + "Integrated security=SSPI;Initial Catalog=AdventureWorks;"; using (SqlConnection connection = new SqlConnection(sqlConnectString1)) { connection.Open( ); // Return some information about the server. 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); ...