skip to Main Content

.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
    dbMgr.ExecuteNonQuery(insertSql);

    scope1.Complete();
}

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 kalpakas.g@gmail.com
       7 Author: Joey Perrott josephperrott.github@gmail.com
       3 Author: Paul Gschwendtner paulgschwendtner@gmail.com
       2 Author: Michael Prentice splaktar@gmail.com
       2 Author: Judy Bogart jbogart@gmail.com
       ...

See also

  • https://devhints.io/git-log-format

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 = "http://testsearch.example.com:19900/xmlfeed"; # 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]
    $lastDay.DayOfWeek)
    if ($diff -ge 0) {
        return $lastDay.AddDays(- (7-$diff))
    }
    else
    {
        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:

CREATE 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);
cnn.Open();
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);
cmd.ExecuteNonQuery();
cnn.Close();

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);
cnn.Open();
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",
						OracleDbType.Int32,
						numRecords, ParameterDirection.Input);
pEmpId.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
pEmpId.Value = employeeIds;

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

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

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

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

cmd.Parameters.Add(pEmpId);
cmd.Parameters.Add(pFirstName);
cmd.Parameters.Add(pLastName);
cmd.Parameters.Add(pDob);
cmd.Parameters.Add(pRefCursor);

int rows = cmd.ExecuteNonQuery();

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

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).

Notes

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.

Summary

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.

References

Back To Top