System.Data.SqlClient (namespace)

System.Data.SqlClient vs Microsoft.Data.SqlClient

System.Data.SqlClient is included in both .NET Framework and .NET Core.

Up until 2019, it was regularly updated with new features.

With .NET Core becoming the future of .NET (see also here), System.Data.SqlClient is not being updated anymore and development has migrated to Microsoft.Data.SqlClient.

Therefore, System.Data.SqlClient is not recommended for developping new applications anymore.

Simple PowerShell example demonstrating System.Data.SqlClient

Establish a connection

First, we need a connection string. The System.Data.SqlClient namespace provices the SqlConnectionStringBuilder class to create such a connection string:

$builder = new-object System.Data.SqlClient.SqlConnectionStringBuilder

$builder."Data Source"     = …
$builder."User ID"         = …
$builder."Password"        = …
$builder."Initial Catalog" = …

The actual value of the connection string can be displayed with

$builder.ConnectionString

With the connection string now created, we can establish the connection to the database:

$conn = new-object System.Data.SQLClient.SqlConnection $builder
$conn.open()

Create a table

We also need a table to insert data and select it again:

$null = (new-object System.Data.SqlClient.SqlCommand 'create table T1 (foo numeric, bar varchar(20), baz datetime)',$conn).executeNonQuery()

Insert a few records

The newly created table is filled with three records.

First, we need to create an insert statement (SqlCommand) and a parameter for each column we want to fill:

$insertStmt = new-object System.Data.SqlClient.SqlCommand 'insert into T1 values (@foo, @bar, @baz)',$conn
$insertStmt.commandType = [System.Data.CommandType]::Text
$foo = $insertStmt.parameters.add('@foo', [System.Data.SqlDbType]::Int     )
$bar = $insertStmt.parameters.add('@bar', [System.Data.SqlDbType]::Varchar )
$baz = $insertStmt.parameters.add('@baz', [System.Data.SqlDbType]::DateTime)

The created insert statement and its parameters allow to insert the records into the table:

$null = $foo.value, $bar.value, $baz.value = 1, 'one'          , (get-date                      ) ; $insertStmt.executeNonQuery()
$null = $foo.value, $bar.value, $baz.value = 2, 'two'          , (get-date '2020-02-02T22:22:22') ; $insertStmt.executeNonQuery()
$null = $foo.value, $bar.value, $baz.value = 3, [DBNull]::value, [DBNull]::value                  ; $insertStmt.executeNonQuery()

Select the data from the table

To select the data from the table, we need another statement (SqlCommand) and a reader.

Special care is taken to recognize null values by using $reader.isDbNull():

$selectStmt = new-object System.Data.SqlClient.SqlCommand 'select foo, bar, baz from T1', $conn
$reader     = $selectStmt.executeReader()
while ($reader.read()) {
   write-host ('{0} {1} {2}' -f 
      $(if ( $reader.isDbNull(0)) {  ''  } else {$reader.getDecimal(0)  } ),
      $(if ( $reader.isDbNull(1)) {  ''  } else {$reader.getString(1)   } ),
      $(if ( $reader.isDbNull(2)) {  ''  } else {$reader.getDateTime(2) } )
   )
}
$reader.close()