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