Depends on which side you want to detect the connection. For the desktop side, there are some ActiveSync events you can hook into. The free, open source Desktop Communication library can get you those.
For the device there are a few ways. For WinMo, it's easy - you can use the state and notification broker by tapping in via the Microsoft.WindowsMobile.Status class.
For generic CE devices, you can P/Invoke the CeRunAppAtEvent (which can set an event, not just run an app) with the NOTIFICATION_EVENT_RS232_DETECTED flag. These APIs are also wrapped in OpenNETCF's Smart Device Framework, specifically in the DeviceManagement.SerialDeviceDetected event