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



Ebook Download
View all
Learn
View all