791

Chapter 30: Confi guring and Managing SQL Server with PowerShell

30

■ (^) DataReader object: Designed to return query results as quickly as possible.
■ Transaction object: Groups a number of changes to a database and treats them as a
single unit of work. The Connection object has a BeginTransaction method that can
be used to create Transaction objects.
■ (^) Parameter object: Enables the specifi cation of parameters for stored procedures or
parameterized queries.
■ (^) DataAdapter object: Acts as a bridge between the database and the disconnected
objects in the ADO.NET object model.
Disconnected Objects are as follows:
■ DataTable object: Enables the examination of data through collections of rows and
columns
■ DataColumn object: Corresponds to a column in a table
■ (^) Constraint object: Defi nes and enforces column constraints
■ DataRow object: Provides access to the DataTable’s Rows collection
■ (^) DataSet object: The container for a number of DataTable objects
■ DataRelation object: Defi nes the relations between DataTables in the DataSet
object
■ DataView object: Enables the examination of DataTable data in different ways
The fi rst thing needed for a session using ADO.NET is a connection to the database. The
SqlConnection object is initialized using the following commands:
$connstring = "Data Source=myServerAddress;Initial
Catalog=myDataBase;Integrated Security=SSPI;"

or its equivalent

$connstring = "Server=myServerAddress;
Database=myDataBase;Trusted_Connection=True;"
$cn = new-object system.data.SqlClient.SqlConnection($connstring)
Many options are available for confi guring connection strings, most of which are available
at http://www.connectionstrings.com/sql-server-2008.
After the connection object is initialized, the connection can be used to send
queries to SQL Server. Alternatively, you can download Out-DataTable from the
TechNet Script Center Repository (http://gallery.technet.microsoft.com/
scriptcenter/4208a159-a52e-4b99-83d4-8048468d29dd) and pipe the results of
Invoke-Sqlcmd to it. Doing so returns results in an ADO.NET datatable instead of an array.
Listing 30-4 shows an example using the AdventureWorks2012 sample database, returning
query results to a DataTable object. Simply call the $Results variable one more time to
present the results to the user.
c30.indd 791c30.indd 791 7/31/2012 9:46:23 AM7/31/2012 9:46:23 AM
http://www.it-ebooks.info