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