Programming the Tektronix TDS 340 100MHz digital storage oscilloscope

In my previous article I provided some information on the Tektronix TDS 340 100 MHz digital storage oscilloscope and instructions on how to install the Option 14 card to get VGA output and support for hard copy. This article will provide some further information on interfacing the oscilloscope with a computer using the RS-232 port to retrieve raw signal data and share some of my interesting findings.

Using the serial interface

The oscilloscope serial port must first be configured inside the Utility>System I/O menu and a cross-over serial cable is needed to connect the oscilloscope to the PC. It is best to turn off both hardware and software handshaking as they are not going to help much and will just cause problems. The recommended serial settings are 19200bps, 8 data bits, no parity, 1 stop bit and carriage-return (CR) line ending.

To check if the connection is working, use a terminal software such as Tera Term Web 3.1, configure it to echo characters locally with CR line ending, and type ID? followed by the ENTER key to ask the oscilloscope to return its identifier string, which should look like:

TEK/TDS340,CF:91.1CT,FV:v1.00
   
Commands are case-insensitive and multiple commands or responses are separated by a line ending character (CR, LF or CR/LF as configured). Commands ending with a question mark (?) are queries and a response from the oscilloscope is to be expected. Commands not ending with a question mark will simply be executed by the oscilloscope with no value returned. To check if there are any errors during command execution, use the following:

*ESR?   // Show the value of the Event Status Register
ALLE?   // Return the error log, which will show any errors with the commands previously sent

The following command set, extracted from the programmer manual, measures the frequency of the input signal at channel 1:

MEASU:IMM:SOURCE CH1  // Set measurement source to Channel 1
MEASU:IMM:TYPE FREQ   // Measure the signal frequency
MEASU:IMM:VAL?        // Get the measurement value

If the probe of channel 1 is connected to the 1kHz calibration point in front of the oscilloscope, the above command set would return a value of approximately 1.0000E3, indicating a frequency of 1000Hz.

Retrieving raw waveform data

Command CURV? is used to ask the oscilloscope for the raw measurement data of the waveform being displayed. The following code will return all 1000 data points in the oscilloscope data acquisition memory:

DAT:SOU CH1     // Measurement source to Channel 1
DAT:ENC ASCI    // ASCII format
DAT:WID 2       // 2 bytes data width
DAT:STAR 1      // first data point
DAT:STOP 1000   // last data point (1000th)           
CURV?           // get waveform data

The response will be a set of comma separated integers:

22784,23040,-6656,[.....],-6656,23040

The range of each returned integer value is –128 to 127 when DAT:WID is 1. Zero is center screen. The range is –32768 to 32767 when DAT:WID is 2. The upper limit is one division above the top of the screen and the lower limit is one division below the bottom of the screen.

To properly interpret the data, it will be useful to know the oscilloscope settings via the WFMPR? command, which will return the following:

2;16;ASC;RP;MSB;"Ch1, DC coupling, 2.0E0 V/div, 5.0E-4 s/div, 1000 points, Sample mode";1000;Y;"s";1.0E-5;500;"Volts";3.125E-4;1.28E4;0.0E0

Among the returned values, the voltage per division, the time per division and sampling rate parameters (highlighted) will be needed to accurately analyze the returned data.

Taking a screenshot of the oscilloscope

A screenshot of the oscilloscope display can be captured progammatically using the following commands:

HARDC ABO           // abort any existing hard copy
HARDC:FORM BMP      // set hard copy format to bitmap
HARDC:PORT RS232    // hard copy port to RS232
HARDC STAR          // start hard copy

The image data will be sent via the serial link. Unlike other commands, there is no end of file marker for the HARDC STAR command. To identify when to stop receiving data programmatically, one way is to count the number of bytes received and compare with the expected value. As the file size could vary depending on the hard copy output settings, an easier way is to assume that the hard copy operation has ended if no data is received after a certain period, e.g. 2 seconds.

Custom PC interface software

With some free time I made a .NET application that allows the user to retrieve the frequency measurements, waveform data as well as taking a screenshot of the oscilloscope:

To use the application, first configure the serial port settings (port number, baud rate) and click on Open Port to initialize the serial interface. The Activity Log text box shows the commands sent and responses received. The Screenshot button will request for a screenshot from the oscilloscope and show it in the application. If option Show original color is checked, the application will convert the black-on-white image returned by the TDS340 to green-on-black, to make it look more like a real screenshot.The Event Log button will show all error messages currently in the oscilloscope event log.

The application is using the SerialPort component of the .NET framework.  It assumes that both hardware and software handshaking is disabled in the oscilloscope serial settings. Interestingly, even with handshaking disabled, the DTR (Data Terminal Ready) and RTS (Ready To Send) lines must be on, otherwise the oscilloscope will not respond to the data sent. This is done using the following C# code:

SerialPort1.Handshake = false;
SerialPort1.DtrEnable = true;
SerialPort1.RtsEnable = true;

Due to the asynchronous nature of the DataReceived event of the .NET SerialPort component and the limited time that I have, I did not attempt to make the application wait for all data to be received before enabling the action buttons. For this reason, you will need to wait for a while and check the activity log after pressing any button to make sure that the command has finished executing before peforming the next action, otherwise the application behavior may be unexpected.

Download the PC application here

The Visual Studio 2012 source code is included in the download package. Microsoft .NET Framework 2.0 (which is installed by default on Windows 7 or later) is required. The executable can be found in the bin folder.

Downloads for TDS 340A, TDS 360 & TDS 380 oscilloscopes:

User Manual
Technical Reference
Programmer Manual
Service Manual 

See also:
Exploring Tektronix TDS 340 100MHz digital storage oscilloscope 
Calibration and acquisition problems on Tektronix TDS 340 oscilloscope 

Read More »

Accessing 3CX Call Data Record (CDR) PostgreSQL database

In my previous post I described a method to read the 3CX CDR information by parsing the CDR log files created by 3CX.  Although this approach may look straight forward from a programming point of view, as the number of call grows, problems will arise due to the reliance on the undocumented CDR file format and the needs to read and parse hundreds of CDR text files just to get the call statistics. As a result, I decided to attempt to read the database directly to see if better performance can be achieved and this article will share some of my findings.

Database configuration

As 3CX uses a PostgreSQL database to store its CDR information, you will need a tool such as pgAdmin to open the database. The database logon credentials can be found among the last few lines of the 3CXPhoneSystem.ini file located in the C:\Program Files\3CX PhoneSystem\Bin folder:

[CallReports]
USERNAME=logsreader
DATABASE=phonesystem
PORT=5480
DRIVER=PostgreSQL Unicode
ReadOnly=1
SERVER=localhost
PASSWORD=*******************

Add a new server in pgAdmin using the above credentials and you should be able to connect:

Database tables
 
The logsreader account only has access to some tables in the database, mostly tables with call history information and some other tables with information on the 3CX setup configuration, which perhaps can also be retrieved using the Call Control API.

The tables that contain the CDR are calldetails, callhistory2 and callhistory3. In my tests, table callhistory2 is always empty despite a large number of calls on the PBX. The remaining two tables, calldetails and callhistory3, store call information and are described below.

Table calldetails

This table contains information on each leg of the call, including the caller, destination number, timestamp and call status. Most useable columns should be self-explanatory from their names. Other important columns are described below:

• idcalldetails: primary key of the table
• idcallhistory2: the ID that identifies the same call in table callhistory2 and table callhistory3
• is_tooutside: TRUE if the call targets an external number which is not an extension on the PBX (and has to be terminated via a configured gateway)
• is_compl: whether the call was completed successfully, from the PBX point of view. For most calls (even for unanswered calls), this value will be TRUE. It will only be FALSE if an unexpected problem on the PBX occurred during the call.
• status: the status of the call. Possible values are:        

Connecting = 0,
Answered = 1,
DestBusy = 2,
DestNoAnswer = 3,
DestNotAvailable = 4,
NotAnswered = 5,
Completed = 6,
DstUnknown = 7

Table callhistory3

This table stores overall information for each call. Important columns are described below:

• idcallhistory3: primary key of the table
• callid: the history ID of the call. This is the same ID that you will find in the 3CX CDR. It will look something like "00000BD4DFFBDF88_1".
• is_answ: whether the call was answered by at least one party. If a call reaches a digital receptionist (IVR), is_answ is probably TRUE immediately after the calls reaches the PBX. If a call reaches a queue, is_answ will probably not be TRUE until it is diverted to a queue member. If an answered call is later forwarded to another party which does not answer the call, the is_answ flag remains TRUE.
• is_fail: TRUE if the call could not be completed. For most calls, this flag should probably be FALSE unless an unexpected error occurred during the call.
• is_compl: TRUE if at least one segment of the call is completed successfully.
• is_fromoutside: TRUE if the call is from an external number which is not an extension on the PBX. For example, from a user who calls a DID number assigned to a configured gateway on the PBX. 
• callerid: the number of the originating number of the call. If is_fromoutside is TRUE, this will be the external caller number. 
• group_no: the number of the queue from which the call originates. If the call is not from a queue, this field is empty.
• callchain: the semicolon separated list of all participants involved in the call 
• rec_file: path to the WAV recording of the call, if recording is enabled.

Difference between callhistory3 and calldetails tables

While table callhistory3 stores overall information for each call, table calldetails stores information for each leg of the call. To illustrate the difference, if extension 10009 called the queue number on 80001 and was diverted to extension 10010 which is a member of the queue, you will see the following 2 records from table calldetails:

 And only 1 record from table callhistory3 showing overall information about the call:

The 2 records from table calldetails have idcallhistory2=19903 to indicate that they belong to the same call having a history ID of 00000BDA221D1253_4 in the callhistory3 table.

The status column is missing from table callhistory3, as different segments of the calls might have different statuses. However, from table callhistory3, you can still check whether the call was answered (is_answ and answertime column) and whether it was completed without errors (is_compl and is_fail columns).

Useful SQL queries

One of the objectives during my experiment is to write the SQL queries to extract the call reports in a format similar to the 3CX Web Report tool (available at http://localhost:5000/Reports). Suprisingly, with some understanding and experimenting with the database, this is a matter of tweaking some SQL queries to retrieve the information I need.

For example, to get the total calls made to the queue on the PBX reported by 3CX in Call Center Statistics Report > Detailed Queue Statistics - All Queues, we use the following query (assuming 80001 is our queue number):

SELECT * FROM calldetails WHERE  dest_dn = '80001' AND (status=5 OR status=6)

Take note that the number of calls made through the queue returned from the above query (and reported by 3CX) may not be correct depending on how your queue is configured. For example, if I configure my queue to forward to another digital receptionist announcing "High Call Volume" after 30 seconds of waiting, and divert the caller back to the queue after the announcement, each "High Call Volume" announcement will add 1 entry to the database, resulting in an exaggerated number of reported calls to the queue.  To retrieve the true list of unique calls made through the queue on the PBX, we improve the query as below:

SELECT * FROM callhistory3 WHERE idcallhistory3 IN 
(
SELECT DISTINCT idcallhistory2 FROM calldetails 
INNER JOIN callhistory3 ON calldetails.idcallhistory2=callhistory3.idcallhistory3
WHERE  calldetails.dest_dn = '80001' AND (calldetails.status=5 OR calldetails.status=6)
)

The following query returns the number of calls to the queue that were answered by an operator:

SELECT COUNT(DISTINCT idcallhistory2) FROM calldetails 
INNER JOIN callhistory3 ON calldetails.idcallhistory2=callhistory3.idcallhistory3
WHERE  calldetails.dest_dn = '80001' AND (calldetails.status=5 OR calldetails.status=6) AND (callhistory3.answertime IS NOT NULL) 

Total number of abandoned calls, e.g. called to the queue that were never answered by an operator:

SELECT COUNT(DISTINCT idcallhistory2) FROM calldetails 
INNER JOIN callhistory3 ON calldetails.idcallhistory2=callhistory3.idcallhistory3
WHERE  calldetails.dest_dn = '80001' AND (calldetails.status=5 OR calldetails.status=6) AND (callhistory3.answertime IS NULL)

Total taktime for answered calls made through the queue:

SELECT sum(endtime - answertime) FROM
(
SELECT DISTINCT idcallhistory2, callhistory3.answertime, callhistory3.starttime FROM calldetails 
INNER JOIN callhistory3 ON calldetails.idcallhistory2=callhistory3.idcallhistory3
WHERE  calldetails.dest_dn = '80001' AND (calldetails.status=5 OR calldetails.status=6) AND (callhistory3.answertime IS NOT NULL) 
)
AS result 

The returned value does not match the 'Total Queue Talk Time' parameter in the 3CX report as it counts the call duration from the moment the queued call is answered until the call is terminated, which may include time spent on other legs of the call if the queued call is diverted to other parties. On the other hand, the 3CX report only calculates the duration of the segment of the call which involves the queue number and does not include the time spending on other call segments.

To retrieve all calls that have been made on the PBX excluding calls made to the queue, use the following query (assuming 80001 is the queue number):

SELECT * FROM callhistory3 WHERE from_no <> '80001' 

The result will be almost identical to the list of calls found in the Call Statistics Report > Call Logs section of the 3CX web report tool.

You will notice that the field status is missing from the returned value, as it is not available in the callhistory3 table. To get the detailed status of the call (e.g. answered, busy, no answer, invalid number, etc.), use the following query:

SELECT 
DISTINCT ON(idcallhistory3)
callid, from_no, callerid, to_no, is_answ, callchain, status, calldetails.is_compl,
is_fail, is_fromoutside, group_no, recfile FROM callhistory3
INNER JOIN calldetails ON calldetails.idcallhistory2 = callhistory3.idcallhistory3

What the query does is to join the returned result with table calldetails to get back the field status. Because a call may have multiple legs, with each leg having different status, we need to use the special DISTINCT ON keyword of PostgreSQL to only retrieve the status of the first leg of the call in the final result. If the call has multiple legs with different status, this field may not be reflective of the overall status of the call, in which case the is_compl, is_answ and is_fail fields will need to be examined.

Accessing the database from .NET

.NET does not have built-in support for PostgreSQL databases and the use of an ODBC driver is needed to access the 3CX database from a .NET application. You will then need to add a DSN under Control Panel > Administrative Tools > ODBC Data Sources that points to the 3CX database:

Queries can now be executed using the OdbcDataConnection class:

OdbcConnection connection = new OdbcConnection("DSN=3CXDB");
connection.Open();
string query = String.Format("SELECT COUNT(idcalldetail) FROM calldetails");
OdbcCommand command = new OdbcCommand(query, connection);
OdbcDataReader reader = command.ExecuteReader();
reader.Read();
int count = reader.GetInt32(0);
reader.Close();
connection.Close();

If your program is to be run as a Windows Service, it is best to add the DSN under System DSN (which the Local System account has access to), and not User DSN, otherwise accessing the DSN may fail with error "Data source name not found and no default driver specified" unless the service is configured to run as system administrator. Also it is advised to install the version of the ODBC driver that matches the architecture of your application. For example, if the application is 64-bit, the 64-bit PostgreSQL ODBC driver should be installed. In my case, my application is also using the 3CX Call Control API which uses the 64-bit libraries that come with the 3CX installation on a 64-bit Windows machine, and both the application and the ODBC driver have to be 64-bit versions.

See also:

3CX Call Data Record (CDR) output file format
Using 3CX Call Control API in a .NET application 
Integration of 3CX Phone System with Tariscope  - a third party reporting tool for 3CX. 

Read More »

Creating calls and conferences using 3CX Call Control API

By default, 3CX provides the HTTP API to allow users to make calls on the PBX programmatically as well as changing some other extension settings. However, the HTTP API is pretty limited and a better way is to use the Call Control API to have more controls on how calls are created.

Although the Call Control API is available on both 3CX version 12 and 11, the library DLLs (3cxpscomcpp2.dll and sl.dll) used by the DLL are not interchangeable between the two versions. This means you will probably need to build two versions of your application referencing the different versions of the libraries if you wish to target both 3CX version 11 and 12. Also it is advised to keep a local copy of 3cxpscomcpp2.dll (and sl.dll) in your application and add a reference to the local copy, and not the version of the DLL found in the .NET Global Assembly Cache (GAC). In my test, referencing the GAC version may result in problems finding the 3CX assembly when the application is run on a different machine.

Making calls

To make a call connecting two extensions on the PBX, use the following method:

public void MakeCall(
    string dn_from,
    string number_to
)

The source number, dn_from can be any internal number on the PBX and number_to can be any internal or external number. 3CX will call dn_from waiting for the call to be answered, upon which number_to will be called. When number_to picks up the call, dn_from and number_to will be able to talk to each other. This process is also known as callback.

While number_to can be any internal or external number, dn_from can only be an internal number. This means you will not be able to use this method to connect two arbitrary PSTN numbers via 3CX.

Conferencing on 3CX version 11

To initiate the simplest conference you can use

MakeCall(701, 101)

where 701 is the first conference extension on the system and 101 is the extension number. If 701 does not currently have any conference in progress, this will make a call to 101 informing the extension that a new conference will be created with the extension being the first participant and other people calling 701 will be able to join this conference. If a conference is already in progress, extension 101 will be able to join the existing conference.

The number of maximum simultaneous conference can be configured inside Settings > Advanced:

The above settings will create 4 conference extensions 701-704 on the system, viewable from System Extension Status from the 3CX web portal. A default conference extension 700 will also be created but cannot be used to make conference calls programmatically. From the call control API, extension 700 will be treated as a ConferencePlaceExtension with IsGateway property set to TRUE. User can still call 700 to create a new conference.

To have more controls on the created conference, use the following method:

public void MakeCall(
    string dnNumber,
    Dictionary<string, string> parameters
)

where dnNumber is a conference extension which is not a gateway and parameters is dictionary containing the following parameters:

"tojoin" - number to dial. Specifies destination of call.
"PIN" - the pin of this conference. Can be empty.
"noname" - "0" or "1". Only for initial call. Conference place will not ask new members to pronounce name before entering this conference call but will ask to confirm participation. Will inform participants about joining and leaving but without name.
"instant" - "0" or "1". Only for initial call. Conference is instant. Conference place will not ask member to confirm participation

For example, to create a conference to extension 100 that begins immediately, does not ask members for name or PIN code, use the following set of parameters:

{"tojoin":"100", "PIN":"", "noname":"1", "instant":"1"}

Conferencing on 3CX version 12

On 3CX version 12, major changes have been implemented in the conference architecture and associated APIs. There are no longer separated conference extensions (e.g. 701-704) on the system. Rather, there is only a single conference extension (700) and each extension will have its own conference slot which can hold conference calls for that extension. The number of simultaneous conferences is also removed from 3CX settings:

To create a simple conference, you can use

MakeCall(700**101, 101)

to use the dedicated conference slot on extension 101 to initiate a new conference that has 101 as the first participant.

Unfortunately, according to 3CX support, the method

public void MakeCall(
    string dnNumber,
    Dictionary<string, string> parameters
)

is no longer supported as at 3CX version 12, despite still being mentioned inside the API documentation.

The ScheduleTheConference.cs sample provided with the API provides another method to create a new conference:

PhoneSystem ps = PhoneSystem.Root;
var stat = ps.CreateStatistics("S_SCHEDULEDCONF", args[1]);
stat.clearall();
stat["name"] = args[4];
stat["idstat"] = args[1];
stat["pin"] = args[3];
stat["startat"] = dt.ToUniversalTime().ToString(@"yyyy-MM-dd HH\:mm\:ss");
stat["target"] = args.Length > 5 ? args[5] : "";
stat["numbertocall"] = "";
stat["email"] = "";
stat["description"] = "";
stat["emailtext"] = "";
stat.update();

            

However, despite much effort, I could not get this method to work on my 3CX setup. No errors are returned and no related events are found inside the 3CX system activity log. Even if I could get it to work, it will not create a new conference effectively since the method intention is to schedule a new conference to run in the future, not to start it immediately and monitor its progress. This means there is currently no way to have full control over the created conference on 3CX version 12.

Surprisingly, the new version of 3CX Phone for Windows is able to support creating, scheduling and maintaining conference:

My guess is that it uses a proprietary communication protocol to communicate with the 3CX server directly. Knowing that the phone application is built as a .NET Silverlight application, I tried to use Reflector to decompile it and although the source code is not obfuscated, the decompiled code still looks far too complicated. At this point it is not worth the time and efforts for me to attempt to identify the conference mechanism this way. 

See also:
Using 3CX Call Control API in a .NET application

Read More »

Converting an Outlook Form Template (OFT) to a .NET Windows form in Visual Studio

In one of my work projects, I was assigned with the task of developing a standalone Windows form application to replace what was initially implemented as an Outlook Form Template (OFT) file using Visual Basic for Application (VBA) for data processing. The objective is to provide the user with an interface to fill in the required data and have them checked for validity before submitting.

Since the original form has over 500 fields, consisting of text boxes, dropdown lists, radio buttons and check boxes, it would be a nightmare to start designing the new form from scratch using Visual Studio form designer. My first attempt is to open the original form in Design mode in Outlook, copy the fields and paste to Visual Studio form designer. This did not work - probably because the clipboard format is different. I decided to find a method for me to copy the fields from the original Outlook form over to save time.

Importing the form in Visual Studio

I came across this MSDN article indicating the possibility of importing an Outlook form to be used in Visual Studio, and decided to attempt it.

First open the form in Design mode in Outlook:

Add a new Outlook form region and pasted all controls from the original form to the region:

After that, save the Outlook form region to an .OFS (Outlook Form Storage) file on the hard drive.

Finally, in a Visual Studio Outlook add-in project, add a new Outlook Form Region and choose to import from an existing Outlook Form Storage file:

Most of the wizard settings can be kept as default, except for the "Select the type of form region you want to create page" and the "Which custom message classes will display this form region" option, which should be Replace-all and IPM.Task.XXXX where XXXX is any valid name for your form region respectively.

After completing the wizard, a set of form region files, including the .designer.cs file, were added to the project. 

Designer support for imported form regions

However, to my disappointment, despite the presence of the designer class, Visual Studio did not open this form region in the designer and simply opened the code editor for the designer class file. If however in the wizard I chose to design a new form region, Visual Studio would allow me to design the form region user interface. The difference is shown in the icons of the form region in the Solution Explorer:

Although this seems to be a common problem and can usually be fixed by reopening Visual Studio, cleaning and rebuilding the solution, in this case, I could not get FormRegion1 to open in the designer despite trying various workarounds.

The difference seems to be in the base class of the 2 forms. FormRegion2 inherits from Microsoft.Office.Tools.Outlook.FormRegionBase whereas FormRegion1 inherits from Microsoft.Office.Tools.Outlook.ImportedFormRegionBase and cannot be opened in the Designer. In fact while the generated for FormRegion2 contains all the necessary information to render the form fields at runtime, the code for FormRegion1 only contains minimal type declaration for the form controls, with most other information being retrieved from the .OFS file at runtime. This explains why the designer does not open FormRegion1 - it simply cannot be manipulated easily this way.

partial class FormRegion1 : Microsoft.Office.Tools.Outlook.ImportedFormRegionBase
{
        private Microsoft.Office.Interop.Outlook._DRecipientControl to;
....
        protected override void InitializeControls()
        {
            this.to = (Microsoft.Office.Interop.Outlook._DRecipientControl)GetFormRegionControl("To");
....
        }
....
        [System.Diagnostics.DebuggerNonUserCodeAttribute()]
         byte[] Microsoft.Office.Tools.Outlook.IFormRegionFactory.GetFormRegionStorage(object outlookItem, Microsoft.Office.Interop.Outlook.OlFormRegionMode formRegionMode, Microsoft.Office.Interop.Outlook.OlFormRegionSize formRegionSize)
        {
            System.Resources.ResourceManager resources = new System.Resources.ResourceManager(typeof(FormRegion1));
            return (byte[])resources.GetObject("Survey");
        }
....
}

At this point it became clear to me that it would not be possible to open the imported FormRegion1 in the Designer and I had to come up with a different method to copy the form fields.

Exporting the form controls

I then had an idea of enumerating through the form controls at run-time and outputting them to a format readable by Visual Studio. The best candidate for the file format would be VB6 form (.frm) file. This format is human-readable, can be easily constructed from code and can be upgraded to a .NET Windows form using the VB6 upgrade wizard in Visual Studio 2008 and earlier.

The best place to do this would be in the FormRegionShowing event:

private void FormRegion1_FormRegionShowing(object sender, System.EventArgs e)
{
UserForm oForm = this.OutlookFormRegion.Form;
foreach (Control ctl in oForm.Controls)
{
            if (ctl is Outlook.OlkCommandButton)
            {
                Outlook.OlkCommandButton cmdButton = (Outlook.OlkCommandButton)ctl;

                string buttonTemplate = String.Format(@"
    Begin VB.CommandButton {0}
        Caption         =   ""{1}""
        Height          =   {2}
        Left            =   {3}
        TabIndex        =   {4}
        Top             =   {5}
        Width           =   {6}
    End",
                    ctl.Name.Replace(" ", ""), escapeString(cmdButton.Caption), (int)(ctl.Height * 20), (int)(ctl.Left * 20), count, (int)(ctl.Top * 20),
                    (int)(ctl.Width * 20));
.............
            }
}
}

The generated .frm code for the button would look like:

   Begin VB.CommandButton Command1
      Caption         =   "Command1"
      Height          =   855
      Left            =   480
      TabIndex        =   3
      Top             =   3480
      Width           =   2175
   End
 
Using this method I was eventually able to generate the VB6 equivalent of all the forms in the original Outlook template and ugprade them to .NET forms. With some modifications, the upgrade forms are ready for further development work. The VBA code-behind of the forms can be upgraded using Telerik only code converter tool.

Potential issues with the conversion

Although it works well enough for my needs, this method is far from perfect. Differences in the possible measurement units (pixel, points, twips) used by Outlook and VB6 for the form fields cause the generated VB6 forms to look slightly different from the original form. Also, some imported controls belong to the namespace Microsoft.Vbe.Interop.Forms while others belong to the Microsoft.Office.Interop.Outlook namespace. This results in the need to use dynamic data type in my code to avoid unnecessary type casting. Finally, items in a combo box are not included in the .frm file, but stored in a separate .frx file:

   Begin VB.ComboBox Combo1
      Height          =   315
      ItemData        =   "Form1.frx":0000
      Left            =   3960
      List            =   "Form1.frx":0010
      TabIndex        =   5
      Text            =   "Combo1"
      Top             =   480
      Width           =   1215
   End

Since the frx file is binary, I did not attempt to generate it and simply copy the combo box items manually. For simplicity, several other less common form control properties are also not migrated and are set manually after the conversion process.

The prototype code to generate the VB6 form can be downloaded here for those who are interested.

Read More »

Allowing only numeric input in a TextBox

You may have seen a Windows textbox that would disallow non-numeric input, showing an error message like in the following screenshot:

This can be easily accomplished from .NET by applying the ES_NUMBER style to the textbox:

Public Sub SetNumericInputMode(ByVal txtBox As TextBox)
    Dim style As Int32 = GetWindowLong(txtBox.Handle, GWL_STYLE)
    SetWindowLong(txtBox.Handle, GWL_STYLE, style Or ES_NUMBER)
End Sub

Of course, SetWindowLong has to be declared properly:

<DllImport("user32.dll")>

Private Shared Function SetWindowLong( _
     ByVal hWnd As IntPtr, _
     ByVal nIndex As Integer, _
     ByVal dwNewLong As IntPtr) As Integer
End Function

Now what if you're using the RadTextBox control from the Telerik RadControls for WinForms? The following code works for a normal textbox, compiles but will not work for a RadTextBox:

SetNumericInputMode(textBox1.Handle);

The reason is that the RadTextBox is just a container for the native Win32 textbox. Any style changes have to be applied directly to the actual textbox, not the container. The following will work:

Public Sub SetNumericInputMode(ByVal txtBox As RadTextBox)
  'special handling for RadTextbox as the actual Win32 textbox is hidden underneath
  Dim hwnd As IntPtr = CType(txtBox.TextBoxElement.Children(0), RadTextBoxItem).HostedControl.Handle
  Dim style As Int32 = GetWindowLong(hwnd, GWL_STYLE)
  SetWindowLong(hwnd, GWL_STYLE, style Or ES_NUMBER)
End Sub

Notice that ES_NUMBER only prevents user from entering non-numeric (0..9) input for the textbox. It does not stop user from pasting random text. For more advanced features, I suggest something like MaskedTextBox

Read More »

Beware when using DES/TripleDES for data encryption

I recently worked on a secure device application which requires a PIN code to be entered as startup.The application database also needs to be encrypted. My first approach was to use a DESCryptoServiceProvider, which is among the few supported encryption methods in the .NET Compact Framework,  to encrypt the database using the PIN code itself as the secret key.

My full code can be found here.

Soon after, I discovered the problem. The encrypted database can be decrypted if the secret key provided is slightly wrong. For example, if the secret key I used when encrypting is 12345678, the decryption will work with both 12345678 and 12345778 (wrong value). Thinking that this was almost certainly a bug, I went ahead to file a report at Microsoft Connect.

I did not have to wait until Microsoft responsed me (which was 3 days later) for an answer. Soon after I read the Wikipedia article on DES, I immediate realized my mistake. The key ostensibly consists of 64 bits; however, only 56 of these are actually used by the algorithm. Eight bits are used solely for checking parity, and are thereafter discarded. This explains why the wrong secret key can be used to decrypt the data as described above.

I changed to TripleDES only to observe the same behaviour. As AES is not supported on the compact framework, I end up encrypting the database using an MD5 hash of the PIN code. Although you may argue that MD5 has some chance of collision, the above hash + encryption combination will ensure, at least with ultimate possibility, that there is no way for the database to be decrypted without the correct password.

Read More »

SerializableDictionary and System.InvalidOperationException

I recently attempted to use Paul Welter's SerializableDictionary in one of my Windows Mobile projects, Everything seems to work well with no code motifications, until when I need to serialize a class containing 2 dictionaries:

public class AppConfig

{       

      public SerializableDictionary Dictionary1;
      public SerializableDictionary Dictionary2;    

}

Attempt to serialize the above class will fail with a System.InvalidOperationException: Two mapping for dictionary. Searching the web for the exact error message returns no useful result, except for a posting which described a similiar problem (Two mappings for SourceTree.DTO.InventoryItemCollection.) but did not provide a solution. The posting also explicitly mentioned that the error occurred only on .NET Compact Framework, and not on the full framework.

The root cause of the error is at the first few lines of the SerializableDictionary source code:

[XmlRoot("dictionary")]

public class SerializableDictionary
    : Dictionary, IXmlSerializable

With this declaration, when 2 SerializableDictionary stay in the same class (which will be serialized), there will perhaps be a conflict somewhere as both of them are set up to use the same XmlRoot element ("dictionary").

A workaround is not to define XmlRoot inside SerializableDictionary class, but to create another class inheriting from SerializableDictionary and define XmlRoot in this class:

//parent class: SerializableDictionary

public class SerializableDictionary
    : Dictionary, IXmlSerializable { .... }

//dictionary 1

[XmlRoot("SerializableDictionary1")]

public class SerializableDictionary1 : SerializableDictionary { }

//dictionary 2

[XmlRoot("SerializableDictionary2")]

public class SerializableDictionary2 : SerializableDictionary { }

//the class to be serialized

public class AppConfigNew

{       

      public SerializableDictionary1 Dictionary1;
      public SerializableDictionary2 Dictionary2;    

}

This requires a few more lines of code, but is the cleanest workaround I have found. Also, I have yet to figure out why the full framework does not have such a problem.

Read More »

Extension method and VB.NET With construct

Recently I came across a codeproject article about extension methods in VB.NET, where one reader complained that he could not get it to work when using VB.NET's With construct. The comment by the reader is very long and contains irrelevant code, but the issue is valid. This post will demonstrate the issue and summarize some of my findings.

Suppose you have the following code:

Snippet #1:

    <System.Runtime.CompilerServices.Extension()>
    Public Sub ModifyString(ByRef source As String)
        source += " modified"
    End Sub

    Sub Main()
        Dim str As String = "original"
        str.ModifyString()

        Debug.WriteLine(str)
    End Sub 

When the code is run, you will get the value "original modified" as expected. Now change the extension method call to be inside a With construct:

Snippet #2:
 

    Sub Main()
        Dim str As String = "original"
        With str
            .ModifyString()
        End With

        Debug.WriteLine(str)
    End Sub

You will now get "original", e.g. the string "temp" is never modified! This is clearly a bug in the framework itself so no workaround is available. Neverthess, let's look at the IL code for the above 2 code samples.

Snippet #1 in IL (No 'With' is used):

         .method public static void Main() cil managed
        {
            .custom instance void [mscorlib]System.STAThreadAttribute::.ctor()
            .entrypoint
            .maxstack 1
            .locals init (
                [0] string str)
            L_0000: nop
            L_0001: ldstr "original"
            L_0006: stloc.0
            L_0007: ldloca.s str
            L_0009: call void ConsoleApplication1.Module1::ModifyString(string&)
            L_000e: nop
            L_000f: nop
            L_0010: ret
        }

Snippet #2 in IL ('With' is used):

         .method public static void Main() cil managed
        {
            .custom instance void [mscorlib]System.STAThreadAttribute::.ctor()
            .entrypoint
            .maxstack 1
            .locals init (
                [0] string str,
                [1] string str2)
            L_0000: nop
            L_0001: ldstr "original"
            L_0006: stloc.0
            L_0007: ldloc.0
            L_0008: stloc.1
            L_0009: ldloca.s str2
            L_000b: call void ConsoleApplication1.Module1::ModifyString(string&)
            L_0010: nop
            L_0011: ldnull
            L_0012: stloc.1
            L_0013: nop
            L_0014: ret
        }

The different is obvious when a text comparison tool such as ExamDiff is used. When a With construct is used, the compiler generates extra code to create a copy of the string variable (str is copied to str2) and pass it to the extension method ModifyString! So whatever changes made to the string have no effect on the original variable, in spite of the ByRef keywork to pass the string by reference. This explains why we get the original value of the string variable.

Now change the code to:

Snippet #3:

    Sub Main()
        Dim str As String = "original"
        With str
            str.ModifyString()
        End With

        Debug.WriteLine(str)
    End Sub

We still use the With construct, but instead of using shorthand to call the extension method, we explicitly refer to the string variable. Guess what, now you'll get the correct result "original modified"! Let's look at the IL code to see what happened:

Snippet #3 in IL:

         .method public static void Main() cil managed
        {
            .custom instance void [mscorlib]System.STAThreadAttribute::.ctor()
            .entrypoint
            .maxstack 1
            .locals init (
                [0] string str,
                [1] string str2)
            L_0000: nop
            L_0001: ldstr "original"
            L_0006: stloc.0
            L_0007: ldloc.0
            L_0008: stloc.1
            L_0009: ldloca.s str
            L_000b: call void ConsoleApplication1.Module1::ModifyString(string&)
            L_0010: nop
            L_0011: ldnull
            L_0012: stloc.1
            L_0013: nop
            L_0014: ret
        }

A copy of the original string variable (str2) is created as usual, but it was never used. Instead, the original string variable (str) is passed to the extension method. This explains why everything works as intended.

The conclusion is to never use With...End With together with extension method as you may get unexpected results. As for the solution, well, I'll leave it up to whoever designs the .NET framework...

UPDATE (17 June 2010): The issue was submitted to Microsoft Connect here. They acknowledge the issue, yet decided to do nothing, not even adding a compilation warning.

Read More »

Identifying used and unused resources in a Visual Studio's project Resources.resx file

If you often use project resources in a Visual Studio project, be it VB or C#, eventually you will end up with a big resource file (e.g. Resources) containing many unused items. This is because, when you remove the code that uses the resoure, very often you will forget to remove the actual resource item.

There are solutions to this problems such as using commercial code refactoring tool or making some minor modifications to Resources.Designer.vb/Resources.Designer.cs and relying on the compiler to generate warnings about unused resources. In this post, I choose to take a different approach: use a batch script.

My complete batch script will accept 4 parameters from the command line, listed from left to right:

1. Path to the VB.NET source code files, e.g. WindowsApplication1\*.vb
2. Name of project resource file, e.g. WindowsApplication1\My Project\Resources.resx
3. Name of file where all resources' usage are written, e.g. all_resources.txt
4. Name of file where all unused resources are written, e.g. unused_resources.txt

It makes use of the FOR extended syntax (FOR /F) to parse the resource file and FINDSTR to find where all the resources are referred to. It will only work properly with VB source code files, where most developers often use My.Resources.XXXX to access the resources. As FINDSTR simply performs a string search, the batch script will not care about code that are commented out, as well as resources that are accessed not by using the Vb's My namespace. If you want to use this with C# source code, you'll need to edit the call to FINDSTR to match your method of accessing the resources, e.g. Project1.Resources.Resource1 instead of My.Resources.Resource1.

The rest of this post will analyse some interesting points I encountered when writing it - hope this will be useful for those having similiar problems.

Batch script - powerful despite being crude

Many people may think that batch script is a thing of the past, since there are so many alternatives today - Windows PowerShell, Perl, or a .VBS file. Yet sometimes I still use it due to its powerful but simple nature. Take a look at the code to search for the resource:

FOR /F "tokens=*" %%a IN (%TMPFILE%) DO (

.........
        FINDSTR /S /P /N /C:"My.Resources.%%c" %SRCPATH% >> %OUT_ALLTOKENS%                  
        REM FindStr returns an errorlevel of 0 if the string was found, 1 and above if it was not found.
        IF ERRORLEVEL 1 (

.........
        )

.............      
    )
)

The call to FOR /F and FINDSTR can easily translate into 10-20 lines of .NET code, unless you use some existing text processing libraries. Here, almost everything is done, just simply remember the syntax, available via FOR /? or FINDSTR /?.

Another example is how to generate a random file name:

:GETTEMPNAME
set TMPFILE=%TMP%\mytempfile-%RANDOM%-%TIME:~6,5%.tmp
if exist "%TMPFILE%" GOTO :GETTEMPNAME

The equivalent .NET code would be:

string filename = Path.GetTempFileName()

However, batch scripts are still very crude. For example, although Windows XP batch scripts allow the use of block statement via (........), don't expect everything to work as they do in a modern programming language. The following would cause problem:

IF (%1) == () (
    ECHO Missing first parameter: path to the VB.NET source code files (e.g. WindowsApplication1\*.vb)
    EXIT /B 1
) ELSE (
    SET SRCPATH=%1
)

Execution would terminate shortly upon reaching ECHO, without any indication why. I believe it would take many people some time to figure out what causes it and fix it. The following will work:

IF (%1) == () (
    ECHO Missing first parameter: path to the VB.NET source code files, e.g. WindowsApplication1\*.vb     

    EXIT /B 1
) ELSE (
    SET SRCPATH=%1
)

Also the above example demonstrates how to check for missing parameter. This would have made more sense in C# or VB.NET, just compare against an empty string (e.g. "").

There are many other useful code snippets in this batch script, for example, how to use delayed environment variable expansion, or how to use double quotes as delimiters in FOR /F. I will not explain it in details - the code is well commented, take some time to study it yourself.

Read More »

"csc.exe - Application Error" when shutting Windows with a .running NET application

Recently a friend reported to me that my application written in .NET prevents his computer from shutting down. If the application is running when Windows tries to shut down, the following error message appears and Windows cannot shut down:

csc.exe - Application Error

The application failted to initialize properly (0xc0000142). Click on OK to terminate the application.

Some investigation reveals that the issue has to do with the .NET class XmlSerializer. My application stores user data into an XML file, which gets saved when the application is closed. In particular, the following code seems to cause the problem:

Dim serializer As New XmlSerializer(ConfigObj.GetType())
Dim writer As New StreamWriter(datafile)
serializer.Serialize(writer, ConfigObj)
writer.Close()

The error occurs as soon as XmlSerializer.Serialize() is called. A file system monitor tool such as Process Monitor shows me that csc.exe is creating temporary files in C:\Windows\Temp, which is perhaps interrupted by the shutdown process, causing the above problem. I am not even sure why csc.exe (the microsoft C# compiler) gets called even though I am using VB.NET!

Not sure how to fix the problem, I have to avoid calling XmlSerializer when Windows is shutting down by using:

Private Sub Form1_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
...

If e.CloseReason <> CloseReason.WindowsShutDown

    Dim serializer As New XmlSerializer(ConfigObj.GetType())
   Dim writer As New StreamWriter(datafile)
   serializer.Serialize(writer, ConfigObj)
   writer.Close()

End If
...

End Sub

This way, user data may be lost, but at least my application does not prevent the system from shutting down. I have to find another way to save user data more frequently...

Reference:

Problem with CSC.EXE

Read More »

.NET forms on Windows 7 at 125% zoom level

If you have ever tried to personalize your Windows 7 desktop and chose to zoom to 125%, as per the following screenshot, you'll perhaps notice that some applications do not display so well:

Believe it or not, the 125% or 150% zoom mode does not simply decrease the dpi of the display and make it look bigger, unlike the advanced display settings in Windows XP:

In fact, when the 125% or 150% zoom mode is set, Windows 7 does (at least) the following two things:

1. Increase the default font size
2. Tell every window (including child window) to increase its size

It's up to the application to handle the change in font size and window size gracefully. If it does not, the application interface may look distorted, with text truncated or displayed outside its designated area and graphics being tiled up or stretched.

Interestingly the 125% or 150% zoom mode cannot be used during a Remote Desktop session.

Effects on .NET forms

Under 125% or 150% zoom, the size of a .NET form will increase proportionally. The following changes will have to be made for the form to display properly:

1. All form controls should have their Anchor property set appropriately. 
2. Most importantly, the AutoScaleMode property of every form should be set to Dpi and not Font. AutoScaleMode of user controls can be set to either Dpi or Inherit. 

 If set inappropriately, only the form will resize while the controls do not resize, causing display distortion.

Also, do not open the form designer from inside Visual Studio 2008 under 125% or 150% zoom mode. Otherwise, Visual Studio will "zoom" the form (perhaps in an attempt to obey the Windows settings faithfully) by increasing its Size property and forgets (or is unable) to restore the original size when the zoom mode is set to 100%. If this happens, you'll have to manually resize every form back to their original size.

I am not sure if it's a Visual Studio bug, or it's just supposed to be that way.

Read More »

System.InvalidOperationException is thrown when attempting simultaneous calls to ExecuteNonQuery

If you have multiple threads trying to execute an SqlCommand via ExecuteNonQuery using a single SqlConnection object, you may get the following exception:

System.InvalidOperationException: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.

The following code replicates the problem:

Dim conn As New SqlConnection("Data Source=localhost;User Id=sa; Password=; Initial Catalog=test")

Sub Main()
conn.Open()

For i As Integer = 0 To 1000
Dim th As New Threading.Thread(AddressOf TestSqlThread)
th.Start(i)
NextEnd Sub

Private Sub TestSqlThread(ByVal state As Object)
Console.WriteLine(state)
Dim cmdLocal As New SqlCommand("INSERT INTO testtable(col1, col2) VALUES('H', 'Hello2')", conn)
cmdLocal.ExecuteNonQuery()
cmdLocal.Dispose()
End Sub

What the above code does is to open a single connection and create multiple threads which call ExecuteNonQuery on the connection which was created. The multiple threads do not wait for each other - they execute concurrently. The previous call to ExecuteNonQuery may not have been finished before the next one comes in. So there may be concurrent attempts to call ExecuteNonQuery to update the database. .NET only supports up to a maximum number of concurrent calls to ExecuteNonQuery on a single SqlConnection object. When the limit is reached, the exception mentioned above is thrown.

A workaround is to use a lock to make sure you call ExecuteNonQuery one after another, and not concurrently:

Dim mylock as new object
Private Sub TestSqlThread(ByVal state As Object)
Console.WriteLine(state)
Dim cmdLocal As New SqlCommand("INSERT INTO testtable(col1, col2) VALUES('H', 'Hello2')", conn)
Synclock MyLock
cmdLocal.ExecuteNonQuery()
End Synclock
cmdLocal.Dispose()
End Sub

However, this will have a performance impact as the query will be executed one by one and slow down the application. A better alternative would be to create a separate SqlConnection object for each thread that needs to call ExecuteNonQuery.

Read More »

P/Invoking C++ callback functions crashes when .NET application is not in focus

A C++ DLL exports one function having its only parameter as a pointer to another function:

#define AFX_EXT_CLASS __declspec(dllexport)
typedef void (CALLBACK* CALLBACKFUNC)(INT param1);
AFX_EXT_CLASS INT TestCallback(CALLBACKFUNC lpCallback);

INT TestCallback(CALLBACKFUNC lpCallback)
{
MessageBox(GetActiveWindow(), L"Press OK to make the callback", L"Testing", MB_OK);
lpCallback(1);
}

From .NET code, P/invoke the function TestCallback()

Public Delegate Sub MyCallback(ByVal param1 As Integer)

<DllImport(DllName)> _
Public Function TestCallback(ByVal lpCallback as MyCallback) As Integer
End Function

Public Function MyCallbackFunc(ByVal param1 As Integer) As Integer
MessageBox.Show("Callback works. Param = " + param1.ToString)
End sub

TestCallback(AddressOf MyCallbackFunc)

It seems to work fine for a while. However, if the C++ function TestCallback calls lpCallback() when the .NET application does not have focus, or after it's been running for sometime, the .NET application will crash.

The reason is probably that the callback delegate has been garbage collected and the function pointer that was passed to unmanaged code has been invalidated. You have to ensure that the delegate is alive for as long as the function pointer is used, by holding a reference to it. This means you can't create the delegate inline in the same way it's done in the above code sample:

TestCallback(AddressOf MyCallback) 'VB.NET

TestCallback(new MyCallback(MyCallback) //C#

The correct declaration would be:

Dim MyCallbackEvent As MyCallback = AddressOf CallbackHandler
TestCallback(MyCallbackEvent)

It is advised to keep these delegate as global variables, otherwise GC.Collect() and GC.KeepAlive() may be needed

Read More »