A database connection string is a formatted contact location for a data source. It identifies the server and database, and carries the authentication settings needed to connect. section 26.1
When building a PowerShell database tool, specify:
- The server or instance, such as localhost\SQLEXPRESS. section 26.2
- The target database, such as master while creating a database or Scripting afterward. section 26.2
- The authentication method, such as Windows credentials or an explicit username and password. section 26.2
Two practical connection strings
A local SQL Server Express instance using Windows authentication can use: Server=localhost\SQLEXPRESS;Database=master;Trusted_Connection=True;. For explicit credentials, use: Server=localhost\SQLEXPRESS;Database=master;un=xxxxx;pw=yyyyyy;. Change master to Scripting when working with the newly created database. ConnectionStrings.com provides a reference for constructing formats. section 26.2
Listing 26.1 Set up a new database on SQL Server Express instance
$conn_string = "Server=localhost\SQLEXPRESS;Database=master;Trusted_Connection=True;" #1 $conn = New-Object System.Data.SqlClient.SqlConnection #2 $conn.ConnectionString = $conn_string #3 $conn.Open() $sql = @" CREATE DATABASE Scripting; "@ #4 $cmd = New-Object System.Data.SqlClient.SqlCommand #5 $cmd.CommandText = $sql #6 $cmd.Connection = $conn #7 $cmd.ExecuteNonQuery() #8 $conn.close() #9
The setup listing shows the connection string flowing into a SqlConnection object, which opens the database connection before executing SQL and closes it afterward. section 26.2
For reusable automation, store the string in a module-level variable and assign it to each SqlConnection object's ConnectionString property. Open the connection before running a query, then close it when finished; an incorrect string produces an error at Open(). section 26.3
