skip to Main Content

How to View Raw SMTP Email Headers in Outlook

I had to do some SMTP relay troubleshooting and it wasn’t obvious how to view the raw SMTP message in Outlook. So here’s how:

In the message window, choose File/Properties and look at the “Internet headers” at the bottom of the Properties dialog.

Microsoft Outlook Properties dialog - Internet headers

I am using Office Professional Plus 2016. If you have a different version of Outlook such as Outlook 2010 or Outlook 2013, the steps may be different for your version.

Tips & Tricks for Changing the Design/Content of a WordPress Site

I spent a good part of the weekend on a re-design of a client’s dental practice site. Like probably 95% of dentist/doctor practices’ sites out there, this site runs on WordPress. Here’s the updated design:

Dental Innovations of Virginia - Advanced General Dentistry and Mobile Dentistry Clinic

Here’s the mobile version:

I didn’t have time to find/get better photos for the mobile dentistry service at the bottom of the page, so those photos will have to do for now.

So, here are some tips for how to quickly make changes to an existing WordPress site:

Learn the Theme

Familiarize yourself with the WordPress theme in use. Every theme works a little differently. The theme for this site exposes a lot of the options via its Theme Options menu, but not all theme customizations can be done there. Some of the options must be done in WordPress’s built-in Appearance/Customize menu.

Most commercial themes also come with fairly extensive documentation. Give the docs a read-over at least once.

Learn the Plug-ins

Many sites / themes make heavy use of plug-ins. For example, this dentist office’s site uses Slider Evolution to show a slider on the home page. Slider Evolution itself is fairly deep application with lots of options.

Use the String Locator Plug-in

The String Location plug-in is great for when you see some element on a page that you need to change, but have no idea how it gets there. This plug-in lets you easily search through your themes, plugins, or even WordPress core to find files containing the text.

Track Your WordPress Site in Git

Adding the site’s files to a git repo allow you to track changes you make to the theme’s files (style.css, header.php, etc.) and easily revert or identify exactly what you changed.

You should configure your .gitignore to ignore at least the following folders/files:

  • uploads
  • /wp-content/plugins/
  • /wp-content/mu-plugins/
  • cache/
  • backups/

Make Frequent Site & MySql Backups

Set up automated backups of the site’s files and database, so that in the case a disaster happens, you have something to go back to.

Here’s the script I use to back up my files and database:

# database

/usr/local/bin/mysqldump -h<host> -u<user> -p<password> <dbname> > ~/backup/divadentistry_main.sql

# Files
/bin/tar zcvf ~/backup/divadentistry.gz ~/public_html/

Override CSS with Appearance/Customize/Additional CSS

With WordPress themes, the “Additional CSS” feature can be used to customize CSS to your liking. I find that the best way to use this is in conjunction with Chrome Dev Tools. I would try to get a relevant class for the element I need to tweak, and customize its CSS. A lot of times you need to add “!important” for your override to take effect. A really cool thing about the Additional CSS feature is that any CSS change takes effect right away in the preview pane.

Sending WAN/Internet Bandwidth Usage Data to Splunk from Tomato Routers using Splunk HEC

A while back I wrote about sending data from SmartThings and other home devices data to Splunk so I can monitor what goes on in my home via Splunk Dashboards. In addition to SmartThings devices, I also pulled data from other data sources such as network routers, Windows event logs, weather data retrieval scripts, etc.

To monitor our Internet bandwidth usage I wrote a Node.js program to scrape the data from the admin web UI for my Verizon Actiontec MI424WR router. Here‘s the code for that.

Last week I upgraded my internet to Verizon Fios Gigabit and with that upgrade, the Actiontec router was replaced with another router: a Netgear R7000 running Advanced Tomato (open source Linux-based firmware for Broadcom based Wi-fi routers). Advanced Tomato has a pretty click interface to monitor bandwidth, but I still want the data in my Splunk instance.

Luckily, Advanced Tomato runs a variant of Linux, so all I needed was a shell script to calculate bandwidth usage data and send to Splunk via the Splunk Http Event Collector.

I found a script by WaLLy3K that already had the bandwidth calculation logic and all I had to add was a little more code to send the data to Splunk.

Step-by-step Instructions

Enable JFFS Partition on Your Router

Enable JFFS Partition on your router so that you have permanent storage for your script. Otherwise if you saved your script in /tmp, it’ll be gone after the next reboot. Log into your router’s admin UI, choose Administration/JFFS, select Enabled and Save.

Create Your Script

SSH into your router and create a shell script at /jffs/ with the content from here. Update the splunkUrl variable with your Splunk HEC URL. If you are not able to SSH, make sure you have SSH Daemon enabled under Administration/Admin Access.

For more info on installing Splunk HTTP Event Collection, see my previous post.

# this is just an excerpt of the code. For full code see 

wan_iface=`nvram get wan_iface`
calc(){ awk "BEGIN { print $*}"; }    # Calculate floating point arithmetic using AWK instead of BC

checkWAN () {
    [ -z $1 ] && sec="1" || sec="$1"

    netdev=`grep "$wan_iface" /proc/net/dev`
    pRX=$(echo $netdev | cut -d' ' -f2)
    pTX=$(echo $netdev | cut -d' ' -f10)
    sleep $sec
    netdev=`grep "$wan_iface" /proc/net/dev`
    cRX=$(echo $netdev | cut -d' ' -f2)
    cTX=$(echo $netdev | cut -d' ' -f10)

    [ $cRX \< $pRX ] && getRX=`calc "$cRX + (0xFFFFFFFF - $pRX)"` || getRX=`calc "($cRX - $pRX)"`
    [ $cTX \< $pTX ] && getTX=`calc "$cTX + (0xFFFFFFFF - $pTX)"` || getTX=`calc "($cTX - $pTX)"`
    dlBytes=$(($getRX/$sec)); ulBytes=$(($getTX/$sec))
    [ $dlBytes -le "12000" -a $ulBytes -le "4000" ] && wanStatus="idle" || wanStatus="busy"

    getDLKbit=$(printf "%.0f\n" `calc $dlBytes*0.008`);        getULKbit=$(printf "%.0f\n" `calc $ulBytes*0.008`)
    getDLMbit=$(printf "%.2f\n" `calc $dlBytes*0.000008`);    getULMbit=$(printf "%.2f\n" `calc $ulBytes*0.000008`)

Create another shell script /jffs/ with the following content:


To test your script run it manually and confirm the data is showing in Splunk:

Splunk raw data

Schedule Your Script

To schedule your script, you can use the Scheduler (Administration/Schedule) in the router’s web admin UI. I have an automatic reboot scheduled at 4 AM, so I scheduled a custom script at 4:15 AM to run the script:

To start the script right away, spawn a process for it:

/jffs/ &

Additional Info

Here’s a little bit of info on how the script works. The raw bandwidth data is read from /proc/net/dev.

Per, /proc/net/dev "Lists the various network devices configured on the system, complete with transmit and receive statistics. This file displays the number of bytes each interface has sent and received, the number of packets inbound and outbound, the number of errors seen, the number of packets dropped, and more.”

Total bytes received and sent

For our purpose, we are interested in the first column which contains the cumulative number of bytes received by the interface, and the 10th column, which contains the number of bytes sent.

The script retrieves the current data, then sleeps for a number of seconds, and reads the updated data. The download/upload Mbit/s data is calculated by taking the difference and divide by the time elapsed. There’s also some logic to handle when the counters wrap around the max value back to zero.

Here’s how the data shows up in my Splunk Home dashboard:

Splunk Dashboard showing  WAN/Internet download/upload speed in Mbps

Deploying Your Web Site to Azure Static Web Apps

💭 Imagine: merging a Pull Request is all it takes to automatically deploy your static or single-page app to a secure, dynamically scaled, and globally distributed network with integrated API support – that’s the promise of Azure Static Web Apps.

Announced during Microsoft Build 2020, Azure Static Web App is a service that automatically builds and deploys static web apps to Azure from a GitHub repository. The features that I find the most interesting are:

  • First-party Github integration
  • Globally distributed
  • Free, auto-renewed SSL certificates
  • Integrated API support by Azure Functions
  • For now – Free hosting for your static site (Angular, React, etc)

I gave it a try and I have to say: it’s pretty cool! 👍 I’ll have the step-by-step on how to configure and deploy an existing site to Azure Static Web Apps below. The steps seem lengthy but there are really just a few basic steps:

  • Create a new “Static Web App” resource and configure basic parameters.
  • Point to the GitHub repository of your app.
  • After a few screens, your app is automatically deployed and available on its own secure URL on Azure Static Web Apps.

If you want to create a brand new site from scratch, see the official documentation

Deploying an Existing Static Site to Azure Static Web Apps

As part of the #100DaysOfCode challenge, I’ve been working on a React site that hosts various programming utilities like encoders/decoders, UUID generator, test data generators, etc. It’s a perfect candidate to test out Azure Static Web Apps.

Create Your Static Web App

First, log into Azure Portal and click “Create a resource“, then search for “Azure static web“. You should see Static Web App (Preview) in the search results. Click on it.

Azure Static Web App - Search

Click Create.

Fill out the Basics tab. Most of the fields are self-explanatory. Click “Sign in with GitHub“.

The page expands, showing a few more fields for GitHub. Fill out with the info for your app’s GitHub repository, and choose “Next: Build>“.

On the Build tab, fill in the appropriate values for “App location“, “Api location“, and “App artifact location“. Then click “Review + create“.

App location” is the root folder for your app. It’s typically / or /app. “Api location” should be left blank if you are unsure. “App artifact location” is the folder to your build.

Review your settings and click Create. Wait a few seconds for the deployment to complete. During the initial deployment, Static Web Apps automatically creates a GitHub Action for you (in the file named azure-static-web-apps-<id>.yml and adds it to your chosen branch. When it’s all done you should see this page:

Click “Go to resource” to go to the resource page for your new Static Web App. On the Overview page, you will see a link to your web app.

Click on “GitHub Action runs” to go over to your GitHub repo and view the status of your deploy Action. You should see a new Action named “ci: add Azure Static Web Apps workflow file”. It should take about two minutes to run.

Switch to the Code tab and see that a new Action file was added to your repo. This is what tells GitHub to automatically build and deploy your app to Azure Static Web Apps.

Bring Up Your Static Web App

It’s time to bring up our web site on Azure Static Web Apps!

Go back to Azure Portal and click on the URL to your app to bring it up. The below screenshot shows my app now running on Azure Static Web Apps with its own unique secure URL.

Custom Domain Name

Adding a custom domain name is pretty straightforward. On Azure Portal, go to the home page of your Static Web App, and click on “Custom domains“, then Add.

On the “Add custom domain” page, note the Azure host name for your site. You will need to create a DNS CNAME record to point your custom domain to that Azure host name. The specifics of this part depends on your domain registrar or web hosting provider. For me, the domain name is hosted on Pair Networks so I went to their control panel and created there CNAME record there.

After you have created your DNS CNAME record, go back to Azure Portal and paste your custom domain into the “Custom domain” box, then click Validate.

Depending on your DNS settings it can take up to 48 hours for a new DNS record to propagate. In practice it should not take more than an hour. In my case it took about 15 minutes. If you get an error in the next step, just wait some time and try again.

After you get the “Validation succeeded” message, click Add to add your custom domain to your Static Web App. This step took about one minute for me

And now I am able to get to the web site via the custom domain name

Adding Server Routes

To handle server routes, you need a routes.json file to your build folder. In my React app, I added it to the /public folder.

Server routing is required to handling “hard” navigation to routes that are handled by your single page app. In my case, I have a React route for /uuid which works fine when you navigate there within the app. But you will get a 404 if you go there directly, or do a browser refresh while you are on that page. Server routes take care of that. See the official docs for more info.

  "routes": [
      "route": "/uuid",
      "serve": "/index.html",
      "status": 200
      "route": "/encode",
      "serve": "/index.html",
      "status": 200
      "route": "/login",
      "serve": "/index.html",
      "status": 200

Troubleshooting Tips

I did run into one problem with a test site. The initial deployment for it did not happen automatically. I verified that the *.yml file was created but the Action never got executed. I was able to get around that by pushing a “dummy” change to the repository.

Game Changer?

Azure Static Web Apps looks to be a game changer. You are getting important features like SSL, dynamic scaling, global distribution, and GitHub deployment all in one easy-to-use package. Once configured, deploying your changes is as simple as pushing code to your GitHub repository. In fact I have deployed several new versions of the app over the past few days and it worked perfectly fine each time.

If cost is reasonable, I would definitely use it on a permanent basis for my single-page/static sites. Currently Azure Static Web Apps is in preview and is free, but things may change after it goes out of preview.

What do you think about Azure Static Web Apps? I would love to hear your thoughts. Let me know here in the Comments section or on Twitter!

Azure Static Web Apps

Monitor & Visualize Your SmartThings Smart Home with Splunk

The smart home has gone through quite a convergence in the last few years. Modern protocols like Z-Wave & ZigBee, along with mart hubs, and smart assistants like Amazon Alexa, Google Home & Apple Siri are finally bringing everything together to make the smart home a practical and reliable reality.

What had been still missing from the picture for me, is the ability to log, analyze, and visualize all the data that my smart home generated. I use Splunk (data capture and visualization tool) at work so I decided to give it a try at home and it’s worked out great.

Here’s a Splunk dashboard I created for my home, showing current and historical data from multiple data sources: energy meter, contact sensors, switches, weather data feed, Windows event logs, and some custom PowerShell scripts.

My SmartThings-based smart home setup:

  • Samsung SmartThings Hub 2nd Gen
  • Amazon Echo Devices
  • Various ZigBee/Z-Wave devices
    • Samsung SmartThings GP-U999SJVLAAA Door & Window Multipurpose Sensors
    • Samsung SmartThings GP-U999SJVLBAA Motion Sensors
    • Samsung F-OUT-US-2 SmartThings Outlets
    • Other ZigBee/Z-Wave switches, dimmers, and plugs
    • Samsung ST-CEN-MOIS-1/FTR-US-2 Water Leak Sensors
    • Aeotec HEM G2 whole house energy monitor
    • First Alert ZCOMBO 2-in-1 Smoke Detector & Carbon Monoxide Alarm, Z-Wave
  • PowerShell scripts to pull data from & run/log periodic Internet speed tests.
  • Splunk Free

Installing Splunk Free Edition

Download and install Splunk. You will start with the Enterprise version which comes with a 60-Day Trial. After that you can switch to the Free edition. Splunk Free allows indexing up to 500 MB of data per day which has been sufficient for my home logging needs. For my setup I installed Splunk on a 14-year old Windows box with a Intel Core2 Quad CPU Q6600 @2.40GHz – Splunk indexing/query performance has been pretty acceptable.

If your install was successful, you should be able to log into Splunk web by navigating to http://localhost:8000 (or replace localhost with your Splunk server hostname).

If you want to monitor other computers, install Splunk Universal Forwarder on each of those computers. I’ll go through how to configure the Universal Forwarders in a future post.

Read more

.NET Transactional File Manager Migrated to GitHub

I have migrated the .NET Transactional File Manager project to GitHub from CodePlex. The library is also available as a Nuget Package. I originally wrote about it in this blog post.

The library allows you do enlist file I/O operations in transactions using System.Transactions. For example you can wrap a SQL Server insert and creation of a new file on disk in the same transaction, and commit or rollback the two operations together as a unit.

Example code:

// Wrap a file copy and a database insert in the same transaction
TxFileManager fileMgr = new TxFileManager();
using (TransactionScope scope1 = new TransactionScope())
    // Copy a file
    fileMgr.CopyFile(srcFileName, destFileName);

    // Insert a database record


This library is available as a NuGet package.

dotnet add package TxFileManager

Version 1.4 is now available with the following changes/fixes:

  • Convert to xUnit tests
  • Add support for custom temp paths to address issues with file/dir operations accross filesystems
  • Fix for resource leak in TxFileManager._txEnlistment
  • Target .NET Standard 2.0
  • Additional testing for .NET Core on Ubuntu
  • Additional stress testing both on Windows and Ubuntu
  • Created Github workflow to automatically build/test on ubuntu
  • Added FxCop static analysis

Finding developers who recently made changes to a component with git

Who knows about this

A few git commands to list the developers with recent commits in a specific folder. I find that this is a good/quick way to find out who you need to talk to if you have questions about a specific code component or feature.

chinhdo@ubuntu2:~/v/tmp/converted2$ git shortlog -sn
    10  Chinh Do
     6  cdo
     5  Vas Gábor

Use git log and –pretty to show more columns:

$ git log --after='2020-01-01' --no-merges --abbrev-commit --pretty="format: %h (%an - %cr) - %s" -- .
 13495c64f7 (Joey Perrott - 3 days ago) - docs(dev-infra): update triage and contributing docs for dev-infra (#35995)
 3f88de9407 (George Kalpakas - 9 days ago) - build: move build scripts to dedicated directory (#35780)
 2e728f7fff (George Kalpakas - 9 days ago) - docs: remove `ivy` and mention `ve` label in docs (#35809)
 5615928df9 (Paul Gschwendtner - 10 days ago) - build: no longer run tslint from within gulp task (#35800)

List recent authors sorted by number of commits:

$ git log --after='2019-06-01' --no-merges -- . | grep Author: | sort | uniq -c | sort -bgr
       9 Author: George Kalpakas
       7 Author: Joey Perrott
       3 Author: Paul Gschwendtner
       2 Author: Michael Prentice
       2 Author: Judy Bogart

See also


Posting a feed to Google Search Appliance (GSA) from PowerShell

PowerShell code to submit a feed to Google Search Appliance:

$dataSource = "MyFeed" # replace this with your datasource name
$gsaUrl = ""; # replace this with your GSA URL

$xml = @"
$dataSource incremental
Write-Output "Submitting feed to $gsaUrl."

$body = "feedtype=metadata-and-url&datasource=$dataSource&data=$xml"
$contentType = "multipart/form-data"
$length = $body.Length

Write-Output "Posting feed to GSA:"
Write-Output $xml

$r=Invoke-WebRequest -uri $gsaUrl -method POST -body $body -ContentType $contentType -UserAgent "Ps-GsaFeedSubmitter"

See also Feeds Protocol Developer’s Guide

Finding the Last Friday (or any weekday) of the Month in PowerShell

If there are less than 7 days left in the month, then it’s the last weekday of this type in this month.

function Is-LastWeekDayOfMonth([DateTime] $d) {
    return [DateTime]::DaysInMonth($d.Year, $d.Month) - $d.Day -lt 7

Similarly, finding the last specific weekday of the month involves finding the last weekday, and the difference between that weekday and the target weekday:

function Get-LastFridayOfMonth([DateTime] $d) {
    $lastDay = new-object DateTime($d.Year, $d.Month, [DateTime]::DaysInMonth($d.Year, $d.Month))
    $diff = ([int] [DayOfWeek]::Friday) - ([int]
    if ($diff -ge 0) {
        return $lastDay.AddDays(- (7-$diff))
        return $lastDay.AddDays($diff)

Bulk Operations Using Oracle Data Provider for .NET (ODP.NET)

Putting up an old article I published on .NET Slackers.

In a typical multi-tier application, one of the biggest performance bottlenecks is the overhead of making round-trips to the database. Minimizing these round-trips is often the first area you should look at during performance tuning. Fortunately, the Oracle Data Provider for .NET (ODP.NET) makes it fairly easy to do this by providing several built-in methods to write and read data in bulk.

To run the code samples in this article, you will need to have:

  • Oracle Data Provider for .NET ODP.NET 2.0 (download from here).
  • A table named “BULK_TEST”.

Here’s the script that creates the table BULK_TEST:

  EMPLOYEE_ID  NUMBER(10)                       NOT NULL,
  FIRST_NAME   VARCHAR2(64 BYTE)                NOT NULL,
  LAST_NAME    VARCHAR2(64 BYTE)                NOT NULL,
  DOB          DATE                             NOT NULL

Bulk Inserts Using Array Binding

The Array Binding feature in ODP.NET allows you to insert multiple records in one database call. To use Array Binding, you simply set OracleCommand.ArrayBindCount to the number of records to be inserted, and pass arrays of values as parameters instead of single values:

string sql = 
	"insert into bulk_test (employee_id, first_name, last_name, dob) "
	+ "values (:employee_id, :first_name, :last_name, :dob)";

OracleConnection cnn = new OracleConnection(connectString);
OracleCommand cmd = cnn.CreateCommand();
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
cmd.BindByName = true;

// To use ArrayBinding, we need to set ArrayBindCount
cmd.ArrayBindCount = numRecords;

// Instead of single values, we pass arrays of values as parameters
cmd.Parameters.Add(":employee_id", OracleDbType.Int32,
				   employeeIds, ParameterDirection.Input);
cmd.Parameters.Add(":first_name", OracleDbType.Varchar2,
				   firstNames, ParameterDirection.Input);
cmd.Parameters.Add(":last_name", OracleDbType.Varchar2,
				   lastNames, ParameterDirection.Input);
cmd.Parameters.Add(":dob", OracleDbType.Date,
				   dobs, ParameterDirection.Input);

As you can see, the code does not look that much different from doing a regular single-record insert. However, the performance improvement is quite drastic, depending on the number of records involved. The more records you have to insert, the bigger the performance gain. On my development PC, inserting 1,000 records using Array Binding is 90 times faster than inserting the records one at a time. Yes, you read that right: 90 times faster! Your results will vary, depending on the record size and network speed/bandwidth to the database server.

A bit of investigative work reveals that the SQL is considered to be “executed” multiple times on the server side. The evidence comes from V$SQL (look at the EXECUTIONS column). However, from the .NET point of view, everything was done in one call.

Bulk Inserts Using PL/SQL Associative Arrays

PL/SQL Associative Arrays (formerly PL/SQL Index-By Tables) allow .NET code to pass arrays as parameters to PL/SQL code (stored procedure or anonymous PL/SQL blocks). Once the arrays are in PL/SQL, you are free to use them in whichever way you wish, including turning around and inserting them into a table using the “forall” bulk bind syntax.

Why would you want to insert bulk records using PL/SQL Associative Arrays instead of the simple syntax of Array Binding? Here are a few possible reasons:

  • You need to perform additional work in PL/SQL, in addition to the bulk insert.
  • The application login does not have permission to perform the work, but you can grant the necessary privilege to a stored procedure.

The major drawback with using Associative Arrays is that you have to write PL/SQL code. I have nothing against PL/SQL, but it’s not part of the skill set of the typical .NET developer. To most .NET developers, PL/SQL will be harder to write and maintain, so you will have to weigh this drawback against the potential gain in performance.

In the following example, we use PL/SQL Associative Arrays to insert 1,000 records, and returning a Ref Cursor at the end. As you can see, there’s quite a bit of more code to write:

OracleConnection cnn = new OracleConnection(connectString);
OracleCommand cmd = cnn.CreateCommand();
string sql = "declare "
 + "type t_emp_id is table of bulk_test.employee_id%type index by pls_integer; "
 + "type t_first_name is table of bulk_test.first_name%type index by pls_integer; "
 + "type t_last_name is table of bulk_test.last_name%type index by pls_integer; "
 + "type t_dob is table of bulk_test.dob%type index by pls_integer; "
 + "p_emp_id t_emp_id; "
 + "p_first_name t_first_name; "
 + "p_last_name t_last_name; "
 + "p_dob t_dob; "
 + "begin "
 + "  p_emp_id := :emp_id; "
 + "  p_first_name := :first_name; "
 + "  p_last_name := :last_name; "
 + "  p_dob := :dob; "
 + "  forall i in p_emp_id.first..p_emp_id.last "
 + "    insert into bulk_test (employee_id, first_name, last_name, dob) "
 + "    values (p_emp_id(i), p_first_name(i) , p_last_name(i), p_dob(i)); "
 + "  open :c1 for "
 + "  select employee_id, first_name, last_name, dob from bulk_test;"
 + "end;";
cmd.CommandText = sql;

OracleParameter pEmpId = new OracleParameter(":emp_id",
						numRecords, ParameterDirection.Input);
pEmpId.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
pEmpId.Value = employeeIds;

OracleParameter pFirstName = new OracleParameter(":first_name",
						OracleDbType.Varchar2, numRecords,
pFirstName.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
pFirstName.Value = firstNames;

OracleParameter pLastName = new OracleParameter(":last_name",
						OracleDbType.Varchar2, numRecords,
pLastName.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
pLastName.Value = lastNames;

OracleParameter pDob = new OracleParameter(":dob",
						OracleDbType.Date, numRecords,
pDob.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
pDob.Value = dobs;

OracleParameter pRefCursor = new OracleParameter();
pRefCursor.OracleDbType = OracleDbType.RefCursor;
pRefCursor.Direction = ParameterDirection.ReturnValue;


int rows = cmd.ExecuteNonQuery();

OracleDataReader dr = ((OracleRefCursor) pRefCursor.Value).GetDataReader();            
while (dr.Read())
	// Process cursor

Ref Cursors

ODP.NET Ref Cursors are objects that point to Oracle server-side cursors (or result sets). The important thing to .NET developers is that a Ref Cursor can be converted to the familiar OracleDataReader. With Ref Cursors, the logic to open result sets can be written entirely in PL/SQL and the results returned to .NET via Ref Cursors.

Why would you want to use Ref Cursors, instead of just doing an ExecuteReader with a SELECT statement? Here are some possible reasons:

  • You need to perform additional work in PL/SQL before returning the result set(s).
  • The user does not have direct access to the table(s) in question.

Here is a real world example. Say you need to update a record in the Orders table and insert a new record into the OrdersAudit table at the same time. Instead of executing two database calls, you can wrap everything into an anonymous PL/SQL block and make one database call.

See the previous section for code example using a Ref Cursor.

Controlling FetchSize

Controlling the FetchSize property is another method to minimize server round-trips. When you read data from the server using the OracleDataReader object, ODP.NET retrieves the data for you in chunks behind the scene. By default, a 64K chunk of data is retrieved each time. However, you can change the chunk size by setting the FetchSize property. By increasing FetchSize, you will lower the number of data retrieval round-trips and increase the overall retrieval operation.

It’s typical to set the FetchSize property is to the RowSize (of the OracleCommand object) multiplied by the number of records expected:

OracleDataReader dr = cmd.ExecuteReader();
dr.FetchSize = cmd.RowSize * numRecords;
while (dr.Read())
	// Perform reads...

When working with a Ref Cursor, the OracleCommand.RowSize property is always zero. You either have to calculate the row size at design time or use reflection at run-time to determine the RowSize by looking at the instance variable m_rowSize on the OracleDataReader object:

FieldInfo fi = dr.GetType().GetField("m_rowSize"
	, BindingFlags.Instance | BindingFlags.NonPublic);
int rowSize = (int) fi.GetValue(dr);

On my development PC, when reading 10,000 records from BULK_TEST, setting FetchSize = RowSize * improves the total elapsed time by a factor of two over leaving FetchSize at the default value (985 vs. 407 milliseconds).


For clarity purposes, the example code in this article does not use the “using” pattern for IDisposable objects. It’s recommended that you always use “using” with IDisposable objects such as OracleConnection or OracleCommand.


In this article, we looked at how various bulk operations in ODP.NET 2.0 can help you improve the performance of your ODP.NET application. Being familiar with these techniques can help you plan, design and implement applications that meet performance goals.


Back To Top