Tuesday, December 16, 2014

How to debug SQLCLR in VS 2012



Here is how to debug your SQLCLR code in Visual Studio.
I assume that you already have your SQLCLR DLL and its related stored procedure set up in your SQL Server.


  1. Launch VS 2012.
  2. Open "SQL Server Object Explorer" (can be opened from View -> SQL Server Object Explorer menu)
  3. In SQL Server Object Explorer, add connection to target SQL Server (ex: local SQL server) where SQLCLR is located. Local SQL instance will be best for debugging.
  4. Rightclick on the connection and choose [Allow SQL/CLR debugging].



  5. Find your stored procedure (or function) you want to debug. This SP should be entry point of the SQLCLR.
  6. Rightclick on the SP and choose "Debug Procedure"
  7. Enter input value in the "Debug Stored Procedure" dialog and click OK. This will open new SQLQuery window and automatically generate TSQL script to start debugging.
  8. In SQLQuery document window, choose [Execute With Debugger]. This will start debugging at the first line.



  9. Click [Step Into] button at the SP call that invokes SQLCLR call.
  10. Now you are in SQLCLR C# code (or any other .NET code) and you can step into, step out in the C# code.


Sunday, August 4, 2013

Excel programming - OutOfMemoryException when setting value to a cell

I ran into OutOfMemory exception when setting a value to a cell in Excel.
Here is a simplified example in C#.

using Excel = Microsoft.Office.Interop.Excel;  //Excel PIA 12.0
//...

Excel.Application excelApp = new Excel.Application();
Excel.Workbook wb = excelApp.Workbooks.Add(Type.Missing);

Excel.Worksheet ws = wb.Worksheets.get_Item(1) as Excel.Worksheet;

string val = "-" + "".PadRight(8192, ' ');
ws.Cells[1, 1] = val;

//....

Running this code throws OutOfMemoryException with the following message:

   Not enough storage is available to complete this operation. 
   (Exception from HRESULT: 0x8007000E (E_OUTOFMEMORY))

When tried to set the same value to a cell manually in Excel, I got more detail message.

   Formula is too long. Formulas may not exceed 8192 characters. 

So it makes more sense. When text begins with -, Excel consider it as Excel Formula.
In Excel 2007 - 2010, the max size of formula is 8192 (In Excel 2003, it is 1024).

So workaround might be to replace the first character with something else or prepend something in front of the text.

Friday, May 24, 2013

installutil.exe - System.BadImageFormatException Could not load file or assembly or one of its dependencies

A .NET framework utility, installutil.exe, is used to install or uninstall .NET assembly to Windows Service. To uninstall existing service and reinstall new binaries, one can create a batch as below. That is, stop existing service and uninstall existing service. And then install new service and start it again.
net stop "My Service"
%SystemRoot%\Microsoft.Net\Framework\v4.0.30319\installutil.exe /u MyService.exe
%SystemRoot%\Microsoft.Net\Framework\v4.0.30319\installutil.exe MyService.exe
net start "My Service"

The script worked fine but one day the batch was failed with the following error.

Exception occurred while initializing the installation:
System.BadImageFormatException: Could not load file or assembly 'file:///D:\Services\MyService.exe' or one of its dependencies. An attempt was made to load a program with an incorrect format..
As it turned out, the MyService.exe assembly happened to be 64bit binary and the installutil.exe was for 32 bit. So changing the installutil.exe path to 64 bit folder made it working.
%SystemRoot%\Microsoft.Net\Framework64\v4.0.30319\installutil.exe MyService.exe

Saturday, April 6, 2013

Add new line in .resx resource string

A simple topic. When adding newline such as \n or \r\n in .RESX resource string, Visual Studio actually escapes the backslash character, so the actual data that is saved is double backslash (\\) instead od single backslash. For example, for input "\n" in .RESX resource editor, the actual data will be \\n  in .resx.

So if one tries to display that resource string in, say, message box, the 2 characters "\n" will be displayed instead of new line which one expects.

For example, let's say ErrorMsg in Resource1.resx has some error message like "Error : invalid data.\n{0}."

msg = string.Format(Resource1.ErrorMsg, errInfo);
MessageBox.Show(this, msg, "Error");

The code above will display something like "Error : invalid data.\nwrong chars found" in one line.
To add new line between two sentence, double backslash should be replaced by single backslash or Environment.NewLine.

msg = string.Format(Resource1.ErrorMsg, errInfo);
msg = msg.Replace("\\n", Environment.NewLine);
MessageBox.Show(this, msg, "Error");

Saturday, March 30, 2013

A connection was successfully established with the server, but then an error occurred during the pre-login handshake

One of my .NET application threw SqlConnection exception with the following error.

"A connection was successfully established with the server, but then an error
occurred during the pre-login handshake."

This application used to work fine but all of sudden it stopped working. The code is pretty typical ADO.NET connection routine. It failed at conn.Open(). Interestingly, it only occurred when connecting to remote server. Connecting to local SQL server worked fine.

<pre>
  string connectionString = "Data Source=abc.com;Initial Catalog=testdb;User ID=user1;Password=1234;";
  using (SqlConnection conn = new SqlConnection(connectionString))
  {
      conn.Open();
      //.....
  }
</pre>

One explanation can be found in http://support.microsoft.com/kb/2568167.
I ran the following command to reset and then everything worked fine.

     C> netsh winsock reset

Of course, there will be so many different reasons/solutions for this same error. I just wanted to jot down one case of those.

Tuesday, February 26, 2013

Send email from cmd script using Powershell

In C#, sending mail is a simple task. We create an instance of System.Net.Mail.SmtpClient class and then call Send method with MailMessage object. Sometimes smalle task is done by using windows shell script. If we have to use shell script in .cmd file, how do we send email? Well, shell command does not have a command for sendmail, so we probably can use third party utility. If we do not want to download and install the 3rd party tool on your system on whatever reason, we can utilize Powershell which can create and use .NET classes.
Let's say we have a .cmd file that runs an application and we want to send an alert email when the app is failed. The following code snippet shows that it runs MyApp.exe and check its exit code and  some literal text (Result=Pass) to verify that the app is successful. If it fails, it calls Powershell script (sendmail.ps1) which is located in the same folder.

@ECHO OFF
REM Run App
MyApp.exe data.ini > MyApp.out 2>&1

echo EXIT_CODE : %errorlevel%

REM Check exit code
if %errorlevel% NEQ 0 (
  echo ErrorLevel is "%errorlevel%"
  GOTO Error
)

REM Check valid output
find /i "Result=Pass" MyApp.out
if %errorlevel% NEQ 0 (
  echo Result : Fail
  GOTO Error
)

echo Result : Pass
GOTO End

:Error
Powershell .\sendmail.ps1
EXIT /B -1

:End
EXIT /B 0
@ECHO ON

The next code is powershell script that sends email. It creates various .NET objects related to mail and set their properties as we do in other languages like C#. If SMTP server is valid server name and client credential is valid, the mail will be delivered via SMTP.

#
# sendmail.ps1
#
$from = New-Object system.net.mail.MailAddress "sender@live.com"
$to = New-Object system.net.mail.MailAddress "receiver@live.com"
$message = new-object system.net.mail.MailMessage $from, $to
$message.Subject = "[FAILED] Scheduled Task Failed"
$message.Body = "Scheduled Task failed on Machine1. Please investigate the failure."

$smtpserver = "smtphost.domain.com"
$client = new-object system.net.mail.smtpclient $smtpserver
$client.UseDefaultCredentials = $TRUE
$client.Send($message)

Monday, December 24, 2012

C# : Simulate concurrent calls to SQL Server

I happened to need to write a small C# routine that simulate a lot of concurrent calls to a stored procedure in SQL Server. The purpose of this application was to simulate deadlock scenario that occurred in production server. A lot of deadlock occurred at highly concurrent situation only, so simulating the condition in test environment is very helpful to figure out deadlock root cause and also to validate deadlock fix.
I simplified the case and write a sample code that pretty much done the same thing.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Threading.Tasks;

namespace NS
{
    class Program
    {
        static void Main(string[] args)
        {
            Parallel.For(0, 100, (i) => Run(i));
        }

        static void Run(int i)
        {
            string strConn = "Data Source=(local);Initial Catalog=MyDB;Integrated Security=true";

            using (SqlConnection conn = new SqlConnection(strConn))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("sp_GetNext", conn);
                cmd.CommandType = CommandType.StoredProcedure;

                // Input param
                SqlParameter pInput = new SqlParameter("@in", SqlDbType.Int);
                pInput.Direction = ParameterDirection.Input;
                pInput.Value = 1;
                cmd.Parameters.Add(pInput);
                
                // Output param
                SqlParameter pOutput = new SqlParameter("@out", SqlDbType.Int);
                pOutput.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(pOutput);

                cmd.ExecuteNonQuery();

                Console.WriteLine(pOutput.Value);
            }
        }
    }
}

The calling stored procedure is nothing special, it is ordinary ADO.NET routine to make a SP call. To simulate concurrent calls, I called Run() method by using Parallel.For(). This Parallel.For() method runs Run() method 100 times, almost concurrently. Depending on CPU cores, the actual number of worker threads can vary but it will simulate as many concurrent calls as possible from one machine. If test machine has single CPU or is a slow machine, probably the simulation might fail. But it gaves me decent level of simulation with my eight core machine.

Wednesday, September 19, 2012

WPF DataGrid - Column Sorting

Small note for column sorting in WPF DataGrid control. How can we show data in DataGrid initially in descending order? If a column is in descending order, two things should be done. Firstly, the data should be sorted accordingly, that is, descending order. Secondly, the header of the column is supposed to show down arrow mark which represents "descending order."

To show down arrow mark in column header, add SortDirection attribute to the column. In example below, "ID" column has SortDirection=Descending attribute.

<DataGrid Name="dataGrid1" AutoGenerateColumns="False">
    <DataGrid.Columns>
        <DataGridTextColumn Header="ID" Binding="{Binding ID}"
             SortDirection="Descending" />
        <DataGridTextColumn Header="Name" Binding="{Binding Name}"/>
    </DataGrid.Columns>
</DataGrid>

To actually show sorted data, your data source should be sorted in descending order. If your data source is SQL data source, best way is doing it in SQL ORDER BY statement. If your data source is in memory collection, LINQ might be good way of doing sorting. Another approach is to use SortDescriptions property in DataGrid.Items as you can see below. The code below added ID as sorting column in descending order.
public partial class MainWindow : Window
{
    public MainWindow()
    {
        InitializeComponent();

        List<MyData> myItems = new List<MyData>();
        myItems.Add(new MyData(1, "Lee"));
        myItems.Add(new MyData(2, "kim"));

        dataGrid1.Items.SortDescriptions.Add(
new SortDescription("ID", ListSortDirection.Descending));
        dataGrid1.ItemsSource = myItems;
    }
}

public class MyData
{
    public int ID { get; set; }
    public string Name { get; set; }
    public MyData() { }
    public MyData(int id, string name)
    {
        this.ID = id;
        this.Name = name;
    }
}

Friday, August 17, 2012

WPF PropertyGrid - display partial enum values in ComboBox

In the previous post, I explained a way of getting a subset of enum values. This post shows an example of displaying a subset of enum onto WPF PropertyGrid. In WPF 4.0, there is no standard built-in PropertyGrid control but one can find a good PropertyGrid from Extended WPF ToolKit at codeplex website. As of now, its latest version is 1.6 but due to some compatibility issue, I happen to use v1.5. Since this is open source, you can also download source code from the website.

In WPF PropertyGrid 1.5, there is no way of doing enum filtering as we expect (of course). In order to facilitate enum filtering, I added an editor EnumFilterComboBoxEditor derived from ComboBoxEditor.

public class EnumFilterComboBoxEditor<T> : ComboBoxEditor where T : struct
{
    public EnumFilterComboBoxEditor()
    {
    }

    protected override void ResolveValueBinding(PropertyItem propertyItem)
    {
        EnumFilter<T> enumFilter = (EnumFilter<T>)propertyItem.Value;
        var _binding = new Binding("Value");
        _binding.Source = enumFilter;
        _binding.ValidatesOnExceptions = true;
        _binding.ValidatesOnDataErrors = true;
        _binding.Mode = propertyItem.IsWriteable ? BindingMode.TwoWay : BindingMode.OneWay;
        _binding.Converter = CreateValueConverter();
        BindingOperations.SetBinding(Editor, ValueProperty, _binding);
    }

    protected override IList<object> CreateItemsSource(PropertyItem propertyItem)
    {
        return GetValues(propertyItem.Value);
    }

    private static object[] GetValues(object enumObject)
    {
        List<object> values = new List<object>();

        EnumFilter<T> enumFilter = (EnumFilter<T>)enumObject;            
        var enums = enumFilter.GetEnums();
        foreach (var e in enums)
        {
            values.Add(e);
        }

        return values.ToArray();
    }
}

ResolveValueBinding() sets binding between source property EnumFilter<T>.Value and target Editor.ValueProperty. CreateItemsSource() sets all possible values of ComboBox items where we call GetEnums() method to get filtered enum values. And now to invoke this new type of editor, the following code added to PropertyGrid class (line 478 in propertygrid.cs).

else if (propertyItem.PropertyType.BaseType == typeof(Common.EnumFilter))
{
    Type tenum = propertyItem.Value.GetType().GetGenericArguments()[0];
    Type filterEditor = typeof(EnumFilterComboBoxEditor<>).MakeGenericType(tenum);
    editor = (ITypeEditor)Activator.CreateInstance(filterEditor, true);                        
}
else if (propertyItem.PropertyType.IsEnum) // all Enums
    editor = new EnumComboBoxEditor();

Basically what it does is check the PropertyType and sets different editor based on the type. For enum filtering case, propertyItem.Value is EnumFilter<T> object. And calling GetGenericArguments()[0] returns the first generic argument type (that is, T). For example, for EnumFilter<MartType>, GetGenericArguments()[0] will return MartType. Next line MakeGenericType() call returns concrete type from generics by using passed argument (tenum). So filterEditor will be EnumFilterComboBoxEditor<MartType> type. Please note C# generics cannot be a type unless generic argument T is specified. Once we got the concrete type, we can create an instance from it by using Activator.CreateInstance() method as seen in the next line. I think the sample code snippet is not optimized due to lack of time. Just wanted to summarize my thought...



Filter enum type - Show a subset of enum values

I happen to run into the case where I have to display a subset of enum type values instead of displaying the all elements - which is common case - of the enum type. .NET enum is a predefined type, should be defined at compile time and cannot be dynamically built at run time.

To illustrate the problem domain, let's say we have a enum called MartType as follows.

public enum MartType{
   Safeway,
   QFC,
   Alberson,
   HMart
}

If we display a enum type property in PropertyGrid (actually I used WPF property grid which is included in WPF extended toolkit), it will show a combox editor containing all enum values.




Now, what if we want to display a subset of enum values based on Area? For example, in case of another city (Bellevue), we only want to show 3 grocery stores.



In order to do this, one should restrict ItemsSource data for the ComboBox. And in order to accomplish this in WPF PropertyGrid control, one have to add a special editor to handle this special case (will write about this later).

For the example above, let's say we expose 2 properties like this.

public string Area { get; set; }
public EnumFilter<MartType> AreaMartType { get; set; }

AreaMartType public property is not simply MartType enum, but a EnumFilter of MartType. This AreaMartType is set by the following helper private method, which returns filtered enum based on areaNo.

private EnumFilter<MartType> FilterMarts(int areaNo, MartType selectedMart)
{
    List subset = new List();
    switch (areaNo)
    {
        case 1:
            subset.Add(MartType.QFC);
            subset.Add(MartType.Alberson);
            subset.Add(MartType.HMart);
            break;
        case 2:
            subset.Add(MartType.QFC);
            subset.Add(MartType.Alberson);
            subset.Add(MartType.Safeway);
            break;
        case 3:                    
            subset.Add(MartType.Alberson);
            subset.Add(MartType.Safeway);
            subset.Add(MartType.HMart);
            break;
        default:
            subset.Add(MartType.QFC);
            subset.Add(MartType.Alberson);
            subset.Add(MartType.Safeway);
            subset.Add(MartType.HMart);
            break;
    }
    EnumFilter<MartType> enumFilter = new EnumFilter<MartType>(subset);
    enumFilter.Value = selectedMart;
    return enumFilter;
}

So EnumFilter of T class takes enum value list which is a subset of enum values. And GetEnums() method actually do the filtering work based on the given subset. By IEnumerable and yield only filtered one, it can dynamically retrieve a subset of enum items.

public class EnumFilter
{        
}
public class EnumFilter<T> : EnumFilter, INotifyPropertyChanged
    where T : struct
{
    private List _elements;
    private T _value;

    public EnumFilter()
    {
        _elements = new List<T>();
    }

    public EnumFilter(List<T> elements)
    {
        _elements = elements;
    }

    public IEnumerable<T> GetEnums()
    {
        foreach (var field in typeof(T).GetFields(
            BindingFlags.GetField | BindingFlags.Public | BindingFlags.Static))
        {
            T enumVal = (T)field.GetValue(null);
            if (_elements.Contains(enumVal))
            {
                yield return enumVal;
            }
        }
    }

    public T Value
    {
        get { return _value; }
        set
        {
            _value = value;
            if (PropertyChanged != null)
            {
                PropertyChanged(this, new PropertyChangedEventArgs("Value"));
            }
        }
    }

    public event PropertyChangedEventHandler PropertyChanged;
}

How we display the subset of enums in WPF PropertyGrid is little different topic but related. Will write more. (Please see : http://dotnetbeyond.blogspot.com/2012/08/wpf-propertygrid-display-partial-enum.html )

Friday, April 20, 2012

WPF ribbonwindow - doubleclicking application icon causes Shutdown

A WPF application whose main window is created from WPF RibbonWindow shows different behavior between closing right-top close (X) button and doubleclicking application icon on the left-top side.

When clicking right-topmost Close button, the window is closed and developer can cancel window close in window's Closing event handler by settting e.Cancel to true. Here is a callstack example when the Close button is clicked. Alt-F4 has the same effect as clicking Close button.

  Console.MainWindow.WindowConsole_Closing(object sender, System.ComponentModel.CancelEventArgs e) Line 679 C#
  PresentationFramework.dll!System.Windows.Window.OnClosing(System.ComponentModel.CancelEventArgs e) + 0x8c bytes 
  PresentationFramework.dll!System.Windows.Window.WmClose() + 0x96 bytes 
  .............
  WindowsBase.dll!System.Windows.Threading.Dispatcher.PushFrameImpl(System.Windows.Threading.DispatcherFrame frame) + 0xc1 bytes 
  WindowsBase.dll!System.Windows.Threading.Dispatcher.PushFrame(System.Windows.Threading.DispatcherFrame frame) + 0x49 bytes 
  WindowsBase.dll!System.Windows.Threading.Dispatcher.Run() + 0x4c bytes 
  PresentationFramework.dll!System.Windows.Application.RunDispatcher(object ignore) + 0x17 bytes 
  PresentationFramework.dll!System.Windows.Application.RunInternal(System.Windows.Window window) + 0x6f bytes 
  PresentationFramework.dll!System.Windows.Application.Run(System.Windows.Window window) + 0x26 bytes 
  PresentationFramework.dll!System.Windows.Application.Run() + 0x1b bytes 
  Mitutoyo.QCCAT.Console.exe!Mitutoyo.QCCAT.Console.App.Main() + 0x5e bytes Unknown
  

If a user doubleclicks leftmost application icon, the application initiates app shutdown and send window close signals to all windows. But, if window close event handler cancels, it will not be honored and simply ignored and keep going for shutdown. This means develoepr cannot cancel window close, no matter what. Here is the call stack when app icon is doubleclicked. You can see Application.DoShutdown() in the middle.

> Console.MainWindow.WindowConsole_Closing(object sender, System.ComponentModel.CancelEventArgs e) Line 679 C#
  PresentationFramework.dll!System.Windows.Window.OnClosing(System.ComponentModel.CancelEventArgs e) + 0x8c bytes 
  PresentationFramework.dll!System.Windows.Window.WmClose() + 0x96 bytes 
  PresentationFramework.dll!System.Windows.Window.InternalClose(bool shutdown, bool ignoreCancel) + 0xa1 bytes 
  PresentationFramework.dll!System.Windows.Application.DoShutdown() + 0x1b6 bytes 
  PresentationFramework.dll!System.Windows.Application.ShutdownImpl() + 0x1c bytes 
  PresentationFramework.dll!System.Windows.Application.ShutdownCallback(object arg) + 0x5 bytes 
  ...........
  [Managed to Native Transition] 
  WindowsBase.dll!System.Windows.Threading.Dispatcher.PushFrameImpl(System.Windows.Threading.DispatcherFrame frame) + 0xc1 bytes 
  WindowsBase.dll!System.Windows.Threading.Dispatcher.PushFrame(System.Windows.Threading.DispatcherFrame frame) + 0x49 bytes 
  WindowsBase.dll!System.Windows.Threading.Dispatcher.Run() + 0x4c bytes 
  PresentationFramework.dll!System.Windows.Application.RunDispatcher(object ignore) + 0x17 bytes 
  PresentationFramework.dll!System.Windows.Application.RunInternal(System.Windows.Window window) + 0x6f bytes 
  PresentationFramework.dll!System.Windows.Application.Run(System.Windows.Window window) + 0x26 bytes 
  PresentationFramework.dll!System.Windows.Application.Run() + 0x1b bytes 
  Mitutoyo.QCCAT.Console.exe!Mitutoyo.QCCAT.Console.App.Main() + 0x5e bytes Unknown
  

One can work around this problem by overwriting Applications.Close behavior. Here is a code snippet.

 public partial class MainWindow : RibbonWindow
 {
    public MainWindow()
    {
       InitializeComponent();
       this.CommandBindings.Add(new CommandBinding(ApplicationCommands.Close, ApplicationCloseExecuted)); 
    }
          
    private void ApplicationCloseExecuted(object sender,
             ExecutedRoutedEventArgs e)
    {
       this.Close();
    }

    private void Window_Closing(object sender, CancelEventArgs e)
    {
       if (MsgBox("Sure?") == true) {
          Terminate();
       }
       else {
          e.Cancel = true; // cancel window close
       }
    }
 }

Thursday, April 12, 2012

LINQ to Entities : this method cannot be translated into a store expression

In LINQ to Entities or LINQ to SQL, if C# user-defined function is used in query expression, this function cannot be translated to SQL expression so the following error can occur.

{"LINQ to Entities does not recognize the method 'System.Collections.Generic.IList`1[System.String] getEmp(Int32)' method, and this method cannot be translated into a store expression."}

Here is an example of calling C# function directly from LINQ query. This will throw an exception since getEmp cannot be converted to a pure SQL statement.

private void WrongRun()
{
    MyDBEntities db = new MyDBEntities();
    var p = from d in db.Depts
            where d.ID == 1
            from emp in getEmp(d.ID)
            select new { d.Name, emp };

    foreach (var x in p)
    {
        Debug.WriteLine(x.Name + ": " + x.emp);
    }
}

public IList getEmp(int deptid)
{
    return new EmpData().GetEmps(deptid);            
}

Assuming there is primary key (Dept.ID) / foreign key (Emp.DeptID) relationship between Dept and Emp table, the problem can go away by rewriting LINQ query as follows.

private void Run()
{
    MyDBEntities db = new MyDBEntities();
    var p = from d in db.Depts
            where d.ID == 1
            from emp in d.Emps
            select new { d.Name, emp.EmpID };

    foreach (var x in p)
    {
        Debug.WriteLine(x.Name + ": " + x.EmpID);
    }
}

The relationship between Dept and Emp is 1 to many, so SelectMany() expression will be used if it is rewritten in LINQ methods.

Thursday, April 5, 2012

InstallShield : Check running process or get process list (InstallScript)

InstallShield does not provide any native function for checking processes, which led users to use Win32 API or WMI provider if they want to check a process or get process list. I found a 'List and Shut Down Running Applications' code in http://www.installsite.org but the code was not working in Windows 7 64bit machine. I didn't want to spend too much time on it, so moved on and tried WMI approach which worked for me.

Check to see if specific process is running.
IsAppRunning() function checks to see if a specified process is currently running on the machine. Basically the code finds a process in Win32_Process WMI class.

function BOOL IsAppRunning(appName)      
 OBJECT wmi, procs;      
begin         
  try
    set wmi = CoGetObject( "winmgmts://./root/cimv2", "" ); 
    if ( !IsObject(wmi) ) then   
        return FALSE;
    endif;
    set procs = wmi.ExecQuery ("Select * from Win32_Process Where Name = '" + appName + "'" ); 
    if (!IsObject(procs)) then    
 return FALSE;
    endif;          
                
    if (procs.Count > 0) then
       return TRUE;
    endif;
  catch  
  endcatch;
  return FALSE;
end;

Get running processes
Another similar code is about getting all processes currently running on the machine. This task also can be done by referring to Win32_Process class but it requires to enumerate all data from the result. This looping task is tricky and cannot be done by simply using for loop. Again, http://www.installsite.org site provides 'Get Object and ForEach in InstallScript' code and it worked great for me.

(1)Download GetObject.zip from installsite.org

(2)Unzip it and copy IsGetObj.dll to InstallShield (2010/mine) - Behavior and Login - Support Files / Billboards - Language Independent. This will copy the DLL file to SUPPORTDIR directory which is only available during installation. SUPPORTDIR is temp folder typically created under current user folder.

(3) Add prototype at the top of the script
   prototype ISGetObj.ForEachStart(byref OBJECT, byref VARIANT);
   prototype ISGetObj.ForEachGetNextItem(byref VARIANT, byref OBJECT);
(4) Use ForEachStart and ForEachGetNextItem function to iterate item from WMI query resultset.

function STRING GetProcesses()      
  OBJECT wmi, procs, procItem;      
  VARIANT __varEnumHolder;  
  STRING procList;
begin         
  try
   set wmi = CoGetObject( "winmgmts://./root/cimv2", "" ); 
   if ( !IsObject(wmi) ) then   
      return "";
   endif;
   set procs = wmi.ExecQuery("Select * from Win32_Process"); 
   if (!IsObject(procs)) then    
      return "";
   endif;          
                
   UseDLL(SUPPORTDIR ^ "IsGetObj.dll");   
   ForEachStart(procs, __varEnumHolder);    
   while(0 == ForEachGetNextItem(__varEnumHolder, procItem))  
     procList = procList + "\n" + procItem.Name;
   endwhile;       
   
   set __varEnumHolder = NOTHING;
   UnUseDLL("IsGetObj.dll");
  catch  
  endcatch;
  return procList;
end;   

The example above returns all running process names separated by CR.

Wednesday, April 4, 2012

Display empty string in DateTimePicker

By default, WinForms DateTimePicker control displays current date. Depending on Format property of the DateTimePicker control, it can display long date and time(Format=Long), short date(Format=Short) or time only(Format=Time). And if the Format is set to Custom, various user defined format is possible.

When it comes to DateTimePicker, some people prefers to display empty string instead of current date/time when the form is first shown up. And the thing is DateTimePicker control does not allow you to enter empty string easily, since Value property of the control is DateTime type which does not allow null value. If it were DateTime? nullable type, displaying empty string would have been an easy task. Some developers make Nullable DateTimePicker control by inheriting it, but a workaround can be used by utilizing custom format.
public partial class Form1 : Form
{
    public Form1()
    {
        InitializeComponent();
    }
    private void Form1_Load(object sender, EventArgs e)
    {
        // Setting CustomFormat to a Space.            
        dateTimePicker1.Format = DateTimePickerFormat.Custom;
        dateTimePicker1.CustomFormat = " ";            
    }
    private void dateTimePicker1_ValueChanged(object sender, EventArgs e)
    {
        // Change Format to Long,Short or Time
        dateTimePicker1.Format = DateTimePickerFormat.Short;
        // add more
    }
}

To add empty string to DateTimePicker textbox, set Format to Custom and then assign empty string to CustomFormat property in form Load event handler or form constructor. Once user starts changing the DateTimePicker value, we reset the Format back to normal format such as Long, Short, Time.

Sunday, December 11, 2011

Error "The project type is not supported by this installation" for web project

I happened to reinstall Visual Studio 2010, then when I opened an existing solution that contains a Class dll project and a web project, I got an error "The project type is not supported by this installation" for the web project only. So when I looked in the .csproj file in edit mode in Visual Studio, found a red line under <SilverlightApplicationList> </SilverlightApplicationList>. I removed it and tried again. But then <SccAuxPath>SAK</SccAuxPath> got a red line. Since this property is about TFS, spent some time by googling TFS project setttings (since I also reinstalled TFS), but in vain.


Finally it tried all other projects and noticed only web project had this kind of problem. It turned out that I did not install Visual Web Developer component when installing Visual Studio 2010. There are many reasons of this error "The project type is not... installation" but error messages in VS project edit mode was kind of confusing.
After adding Visual Web Developer in Add/Remove Program, it worked fine.

Tuesday, October 25, 2011

How to set cursor position in RichTextBox in WinForms

How can we set cursor position in WinForms RichTextBox control? One way of doing it is to use RichTextBox.Select() method. This method takes two parameters. First parameter is a starting character index and second one is the length of the selection.
The following code snippet shows how to set cursor on the current position when mouse rightclick button is clicked.
private void richTextBox1_MouseDown(object sender, MouseEventArgs e)
{
   if (e.Button == System.Windows.Forms.MouseButtons.Right)
   {
      int index = richTextBox1.GetCharIndexFromPosition(e.Location);
      richTextBox1.Select(index, 0);
   }
}

Tuesday, October 11, 2011

Multiple UI threads in WinForms

Most WinForms application has one main UI thread and some background worker threads. And it is very typical and clean way of doing multi-threading in WinForms. But, in some special situaions, you might want to create multiple UI threads so that each thread has each form independently. The following sample shows how to create multiple UI threads in WinForms.

First in Main(), it launches a form called 'MainForm' with Application.Run() method. Application.Run() creates message pump for a UI form, in this case, in main UI thread.

static class Program
{
   /// 
   /// The main entry point for the application.
   /// 
   [STAThread]
   static void Main()
   {
      Application.EnableVisualStyles();
      Application.SetCompatibleTextRenderingDefault(false);
      Application.Run(new MainForm());
   }
}

In Form_Load() of MainForm, we create 3 new Forms from 3 different threads.

public partial class MainForm : Form
{
   public MainForm()
   {
      InitializeComponent();
   }

   private void MainForm_Load(object sender, EventArgs e)
   {
      // Start new UI thread for Form1
      Thread thread1 = new Thread(ShowForm1);
      thread1.Start();

      // Start new UI thread for Form2
      Thread thread2 = new Thread(() => { Form2 f2 = new Form2(); Application.Run(f2); });
      thread2.Start();

      // Start new UI thread for Form3
      Thread thread3 = new Thread(ShowForm3);
      thread3.Start();
   }

   void ShowForm1()
   {
      // message pump with Application.Run()
      Application.Run(new Form1());
   }

   void ShowForm3()
   {
      Form3 f3 = new Form3();
      // Start message pump by using ShowDialog()
      f3.ShowDialog(); 
   }
}

First thread, thread1, runs ShowForm1() function which is simply calls Application.Run() against new Form1 instance. This thread creates new message pump for Form1 and works separately from MainForm.

Second thread, thread2, shows an example of using anonymous delegate to launch Form2. Basically it's the same as ShowForm1().

Third thread, thread3, calls ShowForm3() function where Form3 is launched by using ShowDialog(). If one uses Show() method instead of ShowDialog() here, thread will show the Form3 but then the form will be closed immediately since the thread will exit. ShowDialog begins new message pump for Form3 and will stay until user clicks Close button.


Each UI thread will work separately. Once all forms are closed, the application will be closed.

Monday, September 26, 2011

Read/Write Data in Excel using OleDb (C#)

There are many ways of read/write data in Excel file. This post shows a simple example of using System.Data.OleDb in C#. One of advantages of using this method is no need to have Office in client machine.

The code snippet below shows how to connect to Excel using OleDB, how to select, update and insert data. There is DELETE operation here since it is not supported by OLEDB provider.
private void Run()
{
   string szConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\temp\test.xls;Extended Properties='Excel 8.0;HDR=No'";
   OleDbConnection conn = new OleDbConnection(szConn);
   conn.Open();

   // Select
   OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", conn);
   OleDbDataAdapter adpt = new OleDbDataAdapter(cmd);
   DataSet ds = new DataSet();
   adpt.Fill(ds);

   foreach (DataRow dr in ds.Tables[0].Rows)
   {
      string data = string.Format("F1:{0}, F2:{1}, F3:{2}", dr[0], dr[1], dr[2]);
      MessageBox.Show(data);
   }
         
   // Update
   cmd = new OleDbCommand("UPDATE [Sheet1$] SET F2='Hello' WHERE F1='a'", conn); 
   cmd.ExecuteNonQuery();

   cmd = new OleDbCommand("UPDATE [Sheet1$A2:C2] SET F2='World'", conn);
   cmd.ExecuteNonQuery();

   // Insert
   cmd = new OleDbCommand("INSERT INTO [Sheet1$](F1,F2,F3) VALUES ('A3','B3','C3')", conn);
   cmd.ExecuteNonQuery();

   conn.Close();
}

Before:






After:







For connection string, HDR=Yes is default value and it specifies the first row of Excel is used for Column name. Having header in first row is typical case but if you do not want to use HDR, you have to specify HDR=No explicitly.

The Table(?) name in Excel is sheet name and its syntax is "[sheetName$]"
For example, [Sheet1$] is first sheet 'Sheet1' in the Excel file. In Excel, you can specify specific region by adding "range" after $ sign. That is, [Sheet1$A1:C1] represents first 3 columns (A to C) in first row (1).

Select/Update/Delete syntax is pretty much the same as other data store. One thing to note is that if HDR is NO, the column names are automatically generated and so F1 means first field, F2 means 2nd field, and so on. If HDR is used, you can use the same column name as you define in first header row.

Thursday, September 22, 2011

Format Specifier in C#

By and large there are two type of format specifiers in C# / .NET world : Standard Format Specifier and Custom Format Specifier. This post summarizes frequently used format specifiers.


The general syntax for output formatter does look like this:

{n, w:tp}
      n: argument number
      w: width
      t: data type
      p: precision

      ex)  string.Format("{0,9:N2}", v);

The below format specifiers are frequently used by developers.

Standard Format Specifiers for Numeric Type

(Case insensitive)
C : Currency
      string.Format("{0:C}", 1234567) => $1,234,567
D : Decimal
      string.Format("{0:D7}", 123456) => 0123456
E : Scientific
      string.Format("{0:E}", 12345.6) => 1.23456E+004
F : Fixed point
      string.Format("{0:F3}",12345.6) => 12345.600
N : Number
      string.Format("{0:N2}",1234.567)=> 1,234.57
X : Hexadecimal
      string.Format("{0:X}", 255) => FF

Standard Format Specifiers for DateTime Type

d : Short date
     string.Format("{0:d}", today) => 11/21/2011
D : Long date
     string.Format("{0:D}", today) => Firday, July 5, 2010
t : Short time
     string.Format("{0:t}", today) => 4:05 PM
T : Long time
     string.Format("{0:T}", today) => 4:05:55 PM
g : General datetime (short time)
     string.Format("{0:g}", today) => Firday, July 5, 20104:05:55 PM
G : General datetime
     string.Format("{0:g}", today) => Firday, July 5, 20104:05:55 PM

Custom Format Specifiers for Numeric Type

# : Digit placeholder (no leading zeros)
0 : Zero placeholder (w/ leading zeros)
. : Decimal point
, : Thousands operator
; : Section separator

ex)
   val = 12345;
   string.Format("{0:#,##0;(#,##0)}", val)
   => 12,345

   val = -12345;
   string.Format("{0:#,##0;(#,##0)}", val)
   => (12,345)

   val = 0;
   string.Format("{0:#,##0;(#,##0);Zero}", val)
   => Zero

Custom Format Specifiers for DateTime Type

M   : Month. Single digit for <10
MM  : Two digit month
MMM : Abbreviated name of the month
d   : Day. Single digit
dd  : Two digit day
ddd : Abbreviated name of the day
yy  : Two digit year 
yyyy: Four digit year
h   : Hour (12hr)
hh  : Two digit hour (12hr)
H / HH : Hour (24 hr)
m   : Minute
mm  : Two digit minute
s   : Second
ss  : Two digit second
tt  : AM / PM
ex) string.Format("{0:yyyy/MM/dd}", DateTime.Today) => 2011/11/22

Wednesday, September 14, 2011

How to check MouseDown event for all controls in WinForm

When a Form contains several child controls and nested UserControls, mouse click event is not propagated to parent form in WinForm. If one uses WPF, there is no need to add your code since WPF basically supports RoutedEvents. However, WinForm does not support Routed Events, so you have to manually implements the appropriate mechanism.

One way of doing is using Application.AddMessageFilter() method which basically add custom message filter to the application. Your custom MessageFilter should implement IMessageFilter interface which only has one method - IMessageFilter.PreFilterMessage(). In this method, you determine whether you want to filter out the message or you can add your custom code for specific message.

Here is an example of using MessageFilter. To check whether any control in the Form is clicked, I added a static property but this is only for simplified demonstration purpose, more options are available.

using System;
using System.Windows.Forms;
namespace WindowsFormsApplication2
{
   static class Program
   {
      [STAThread]
      static void Main()
      {
         Application.EnableVisualStyles();
         Application.SetCompatibleTextRenderingDefault(false);

         // Add Message Filter to precheck MouseDown
         IMessageFilter myFilter = new MyMessageFilter();
         Application.AddMessageFilter(myFilter);

         Application.Run(new Form1());
      }
   }

   public class MyMessageFilter : IMessageFilter
   {      
      public bool PreFilterMessage(ref Message m)
      {
         if (m.Msg == 0x201) //WM_LBUTTONDOWN
         {
            Form1.FormClicked = true; 
         }
         return false; // do not filter
      }
   }
}

namespace WindowsFormsApplication2
{
   public partial class Form1 : Form
   {            
      public static bool FormClicked {get; internal set;}      
      public Form1()
      {
         InitializeComponent();
      }
   }
}