Database Reference
In-Depth Information
$employeeId = Add-Employee -firstName "Joe" -lastName "De Mase"
-email "joe@company.com" `
-hireDate ([DateTime]::Now.Date) -jobId "CFO"
if ($employeeId) { "Added employee; new id is $employeeId" }
Add-Oracle $conn `
"INSERT INTO COUNTRIES (COUNTRY_ID,COUNTRY_NAME,REGION_ID)
VALUES (:COUNTRY_ID, :COUNTRY_NAME, :REGION_ID)" `
@{ 'COUNTRY_ID'='BG'; 'COUNTRY_NAME'='Bulgaria'; 'REGION_
ID'=1; }
$conn.Close()
$conn.Dispose()
How it works...
The
Add-Oracle
function expects a connection and a SQL
INSERT
statement at a minimum.
Optionally an associative array of name/value pairs (a dictionary that's a
Hashtable
) can be
speciied in
$paramValues
to set any bind variables that may be in the SQL for the values
to be inserted. Bind variables help in preventing SQL injection attacks and avoid parsing. If
$paramValues
is speciied, it is enumerated to set up the
Parameters
collection of the
OracleCommand
used for the insert.
Note that only the parameter names and values are set and other properties
such as
DbType
will be automatically resolved.
For situations where you are inserting into a table that has a numeric primary key column
and you want the record id back after the insert, specify the name of the primary key column
with the
$idColumn
parameter. If set, the function will create an output parameter and
modify the command text to return the id column value into that parameter. After the SQL is
executed with
$cmd.ExecuteNonQuery()
, the parameter value is output for the function
return value; any
DBNull.Value
(that is, error situation) is converted to
$null
to ease any
conditional checking of the value.
Add-Oracle
function is more generic in nature to handle the common
insert cases; in some scenarios you may need more ine-grained control
over the Oracle parameter collection than this function provides.
The
Add-Employee
function simply makes inserting an employee record easier and reusable
via creating the
$params
dictionary and SQL with matching bind variables, passing that along
to
Add-Oracle
, and returning the employee id of the new record.
Search WWH ::
Custom Search