Keeping SqlCeConnection Open and Thread Safety

I've done quite a bit of searching online and it's fairly clear that there is a bit of confusion about how one should use the SqlCeConnection object, particularly with regards to two interrelated issues: (1) whether to keep the SqlCeConnection open and (2) whether the SqlCeConnection is thread safe. For example, some people advocate for keeping one static SqlCeConnection open and shared for the lifetime of an application. However, if the application is multithreaded, can we assume that we can safely share the connection object? I've blogged about this once before here.

I think some of this confusion stems from the differences between the desktop version of SQL Server (SqlConnection) and SQL Server Mobile (SqlCeConnection). For example, in a MSDN forum post, Dave Hayden (a .NET/C# MS MVP) suggests that "For 99% of all applications, the best practice is to open and dispose of database connections right when you need them and not to leave them open for the duration of the application. Open the database connection as late as possible and close/dispose of it as soon as possible." If, like Dave recommends, we always open a local connection within our method calls (and do not access the SQL connection as a state object), we don't really have to worry about the thread safety of the SQL connection object.

Although this may be good advice for accessing SQL Server from the desktop, it contradicts the advice given by Microsoft developer Marcus Perryman in his SQL Mobile post on his blog, "The SqlCeConnection class implements the IDisposable interface because it allocates a number of unmanaged resources, and therefore the code must call Dispose() on the object before it goes out of scope to ensure these resources are cleaned up in a timely manner. Creating and destroying SQL Mobile database connections is an expensive task and so the SqlCeConnection is designed to be a long lived, shared instance across the lifetime of the application.".

Thus, it appears that Dave and Marcus contradict each other. However, Dave's advice was (I believe) given under the assumption that the person was using SQL Server and not SQL Server Mobile. So, it looks like the correct usage of the SqlCeConnection object is to reduce the amount of creating and destroying SQL Mobile database connections (the reasoning behind this is that SQL Server Mobile supposedly does not have connection pooling, see Marcus Perry link above).

So, then what about thread safety? The MSDN documentation is clear. Most objects within the SQL Mobile namespace are not threadsafe. For example, the SqlCeConnection documentation states, "Any public static members of this type are thread safe. Any instance members are not guaranteed to be thread safe." Thus, instance methods like BeginTransaction(), CreateCommand(), etc. are not thread safe. I'm not sure what the detriment/exception would be if this was not followed, but MSDN is clear, SqlCeConnection was not meant to be shared across threads.

OK, so what if you have a multithreaded application? It seems like there are two prevailing methods: (1) Marcus Perry suggests that "for a complex app, ideally the SqlCeConnection instance would be placed in a singleton wrapper class that manages access to the database." or (2) Ginny Caughey (MS MVP) offers the other approach, using a separate SqlCeConnection object for each thread. For the first approach, I would imagine the simplest wrapper possible may look something like the following (I will implement it as a static class rather than a singleton):


static class SqlWrapper
{

private static SqlCeConnection _sqlCeConnection;
private static object _objLock = new object();

static void Open(string connectionString)
{
lock (_objLock)
{
if (_sqlCeConnection != null)
throw new InvalidOperationException("Connection already open.");
_sqlCeConnection = new SqlCeConnection(connectionString);
}
}

static void Close()
{
lock (_objLock)
{
if (_sqlCeConnection != null)
{
_sqlCeConnection.Dispose();
_sqlCeConnection = null;
}
}
}

static int ExecuteNonQuery(SqlCeCommand sqlCommand)
{
lock (_objLock)
{
sqlCommand.Connection = _sqlCeConnection;
return sqlCommand.ExecuteNonQuery();
}
}

static SqlCeDataReader ExecuteReader(SqlCeCommand sqlCommand)
{
lock (_objLock)
{
sqlCommand.Connection = _sqlCeConnection;
return sqlCommand.ExecuteReader();
}
}
}

Does this seem right to people? In this class, we do not expose the underlying SqlCeConnection object, thus commands must be passed in via the wrapper interface to be executed (as they require a reference to a SqlCeConnection).

For the second approach, one connection per thread, the difficulty isn't necessarily in opening 1 connection per thread but knowing when to close those connections. In other words, a SqlCeConnectionManager class might manage access to SqlCeConnections and might serve as a SqlCeConnection factory (with, perhaps, an underlying connection pool). The factory may look at which thread is active (Thread.CurrentThread), check to see if a connection has been allocated for that thread (create one if not) and return the SqlCeConnection. However, the onus would be on the threads themselves to close their connections before exiting (this seems messy to me). I suppose one could spawn a monitoring thread that looked as the status of Threads and, once dead, would either close the respective SqlCeConnection or return the connection the connection pool. However, .NET CF 2.0 does not support the instance property IsAlive so it's not clear how one could do this.

I would imagine that a multithreaded application that launches many short-lived threads that need database access should probably go with the SQL wrapper solution as the overhead of opening/closing connections would introduce a performance hit (even with connection pooling).

An interesting side note: I created a test app on the mobile phone which launched 50 threads on a shared SqlCeConnection and proceeded to read/write random bits of data. No exception was thrown. Of course, the lack of error does not mean that it will always work.

Get Control From Handle

I have not yet determined a clean way to invoke code on the UI thread without access to a Form/Control. The Process class has a property called "MainWindowHandle," which returns the window handle of the main window of the associated process. So, for example, Process.GetCurrentProcess().MainWindowHandle would return a Handle (HWND) to the main window. But how can we translate a window handle to a Form?

In .NET 2.0, this is relatively easy, we can simply call Control.FromHandle(IntPtr handle). Thus, receiving access to the main window is simply:
(Form)Control.FromHandle(Process.GetCurrentProcess().MainWindowHandle)

Unfortunately, however, .NET CF 2.0 does not expose the Control.FromHandle method. I have not found anyone on the web who has solved this problem. A few relevant links:

1. How do I get System.Control object from a Win32 Handle?
2. Google Groups.

Given the limitations of CF, one hacky way to do this would be to expose a static property on your MainForm class which would return a reference to itself. This would actually work in my case as the MainForm should not be instantiated more than once.

Update 01/30/2007: I just made a post to Google Groups about this (link).

Alpha Blending on SmartPhone

I created a quick test app to experiment with the performance of the P/Invoke AlphaBlend calls as discussed in this blog post by Chris Lorton. The AlphaBlend native call does not support per-pixel alpha blending so the alpha value is specified for the entire image. The "incr" label in these two videos is the step value for the alpha channel used in the animation. The frame rates were better than expected, right around 17-20fps.

The tool I am using to record the video is called CoolCapture (I'm using the trial version here). It's very cool. It's essentially a fully featured screen capture tool for Windows Mobile 5 devices. You can generate both videos or pictures. I was fairly impressed with the performance as well; capturing these two videos below only resulted in about a ~6 fps reduction in my animation (thus the blending doesn't look as smooth as it actually is). The trial version limits the recording interval to ~10 seconds (though the webpage says 5 seconds).

Note that the capture resolution was 240x320 (the device resolution); however, this was automatically changed to 640x480 when I uploaded the video to YouTube. This is why the videos have that stretched out look.

2125 ActiveSync Internet Pass Through

I was trying to use ActiveSync as a pass through to my laptop's internet connection so that I could debug a HttpWebRequest problem while tethered to VS2005. However, I couldn't even get PIE (Pocket IE) to use the ActiveSync connection much less my app--well, now I'm at least half way there. I can browse the web using PocketIE and ActiveSync as my internet connection.

1) Open ActiveSync on your computer, select File->Connection Settings. In the ComboBox below "This computer is connected to:" make sure "The Internet" is selected.

2) In PIE, select Tools->Options->Connections. Then, select "The Internet" as your network.

Now, I'm going to see if my own app can do this.

Link to Files in VS2005

David Kline posted about sharing code between multiple projects (where a common assembly is not appropriate):

I would prefer to not create a duplicate of this file as maintaining changes between the two copies can be time consuming and error prone. Fortunately, Visual Studio 2005 has a great feature to allow for file sharing between projects -- you can add an existing file as a link. Here's how:

1. In the project where you wish to add the file, right click the project name in the Solution Explorer
2. Select Add and then Existing Item...
3. In the Add Existing Item dialog, navigate to the desired file
4. Click the downward pointing arrow next to the Add button and select Add As Link.
Once added to a project, linked files are easy to identify by the small arrow in the lower-left corner of the file icon in the Solution Explorer.

Measuring CPU Load

I am looking for an API call or library I can download that provides access to CPU load information. I am willing to pay for third-party commercial applications. I have scoured the internet and it doesn't appear like anything exists out there. I experimented with the .NET CF Remote Performance Monitor today but that does not include any CPU statistics.

Note, it appears that the pps tool from http://www.xs4all.nl/~itsme/projects/xda/tools.html might do the trick. Further investigation is needed.

Cannot Create Native C++ Projects

If you try to create a native C++ Smart Device project, you would have repeatedly try to finish the ‘New Project’ dialog but wouldn’t have been able to get it to work.

This problem is due to the New Project dialog not playing nice with the changes in Internet Explorer 7 (the New Project dialog for native projects is actually a glorified web browser control).

The workaround is simple - Open the registry entry:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Ext\PreApproved
Add a new entry and name it
{D245F352-3F45-4516-B1E6-04608DA126CC}

Again this is something we have fixed for Visual Studio 2005 SP1.

(link)

Update 11/13/2006 2:50PM: Note: this did not work for me. I am currently installing the Visual Studio 2005 SP1 Beta release; hopefully this fixes the problem as well.

Update 11/13/2006 6:50PM: VS2005 SP1 Beta works!

SQL Server Everywhere Renamed

SQL Server Everywhere has been renamed to SQL Server Compact Edition. I guess there was a lot of confusion about what SQL Server Everywhere was--particularly, disambiguating it with SQL Server Express.

(link)

Platforms for .NET CF 2.0

As a corrollary to my last post, what does .NET CF 2.0 run on? According to Jim Wilson's post:

1. PocketPC 2003 & PocketPC 2003 SE
2. PocketPC 2005 (or 5.0)
3. SmartPhone 2005 (or 5.0)
4. WindowsCE 5.0
   

WindowsCE vs. Windows Mobile

What is the difference between Windows CE and Windows Mobile-based Smartphone and Pocket PC software?

Windows CE is a customizable, embedded operating system for a variety of small-footprint devices. OEMs can use Windows CE to design platforms and customize applications to maximize the user experience for devices such as handhelds, thin clients, logic controllers, and advanced consumer electronics.

Windows Mobile is a complete software platform built on Windows CE. Unlike Windows CE, the Windows Mobile Smartphone and Pocket PC operating systems are specifically designed for devices that require a specialized hardware configuration. The software includes standardized interfaces and applications that ensure compatibility across hardware designs. For more information, visit the Windows Mobile Web site.

(link)

Mike Hall adds his two cents here with regards to the above as well as Windows XP Embedded.

Installing a Windows Mobile Application from Desktop

Jim Wilson talks about how to create a Windows Mobile device application installer that works from from the Windows Desktop. (link)

Appointment ListChanged Event

It appears as though the ListChanged event in PocketOutlook for the AppointmentCollection does not work correctly--at least I couldn't get it to work. Not too much on the web about this but I found a related post on the microsoft.public.dotnet.framework.compactframework newsgroup:

Has anyone been able to receive an event when you've registered to receive events from ListChanged?

Here is the code I'm using (I'm using VS 2005 RC1 with CF 1.0):

OutlookSession outlook = new OutlookSession();
outlook.Appointments.Items.ListChanged += new
ListChangedEventHandler(Items_ListChanged);

This code runs just fine, but changing the calendar doesn't fire this event in my application. I've searched the net, but haven't found anyone who appears to have used it.

Peter Foot, from InTheHand fame, then responded:

I haven't checked, but it's possible that the events in Microsoft.WindowsMobile.PocketOutlook only fire when the changes are made through these APIs not when changed from another application...

The original poster, however, corrected Peter with information from the MSDN docs:

Here's what the docs say:

"Occurs when either the collection changes, or when an individual PIM item in the collection changes."

Additionally, the MSDN article "What's New for Developers in Windows Mobile 5.0" says that you can track changes made to Pocket Outlook folders using managed code and gives an example, so it sounds like it should show changes made by other applications. As far as I can tell, that's the whole idea, in fact.

But I can't get it to trigger any events.

SystemState CalendarAppointment Events Seem Very Broken

I posted the following to microsoft.public.dotnet.framework.compactframework about the CalendarAppointment related system states (direct link):

For the most part, I've had good experiences with the new Microsoft.WindowsMobile.Status.SystemState infrastructure in .NET CF 2.0. However, recently I've run up against numerous issues with the CalendarAppointment* and CalendarNextAppointment* events. Incidentally, I've tested this on the I-Mate KJAM Pocket PC Phone as well as the Cingular 2125, T-Mobile SDA, and the I-Mate SP5m SmartPhones. It shouldn't be too hard to repo what I'm describing on your own device.

I will describe the problems in descending importance:

1. The SystemState calendar related events do not properly notify subscribers when the calendar appointment state changes. My impression of these system states is that an event will be fired throughout the day as a user's calendar appointments occur. I would say that in more than 90% of the cases, no event is triggered. I haven't been able to diagnose exactly why this is. The problem I'm describing here seems relevant for all calendar related events. For example,

private SystemState _stateCalendarAppointmentBusyStatus;

private void Init()
{
_stateCalendarAppointmentBusyStatus = new
SystemState(SystemProperty.CalendarAppointmentBusyStatus, true);
_stateCalendarAppointmentBusyStatus.Changed += new
ChangeEventHandler(OnStateCalendarAppointmentBusyStatusChanged);

}

private void OnStateCalendarAppointmentBusyStatusChanged(object sender,
ChangeEventArgs args)
{
AppendText(SystemProperty.CalendarAppointmentBusyStatus,
(BusyStatus)args.NewValue);

}

private void AppendText(SystemProperty systemProperty, object newValue)
{
_textBox.Text += systemProperty + ": " + newValue + "\r\n";

}

Just to add a little more feedback on this (at the risk of muddling my point). I do actually tend to see events fire if I go in and modify the _current_ calendar entry. For example, if it's 4:15PM and I change my current calendar appointment (which occurs from 4-5:00PM) from "Busy" to "Out of Office", a calendar related event (e.g., CalendarAppointment or CalendarAppointmentBusyStatus) will fire. However, if it's 3:59PM and my current calendar entry says that I am "Free" then at 4:00PM, when you would expect my 4 o'clock appointment event to trigger an event, nothing happens :(

2. At this point, I thought perhaps I misunderstood what the current calendar appointment events did particularly because I seemed to get events when I would go into the calendar and modify the current appointment. However, this isn't the issue. I setup a polling sensor that would, instead of relying on the events, constantly poll the calendar related system states. You can do this because calling SystemState.CalendarAppointment, for example, returns the current value of that system state. However, what I found through polling was that this system state was not actually changing. To test this out, setup a "refresh" button in your GUI or run a windows Timer object to spit out the current state of the CalendarAppointment* system states. Then, add 5 or 6 test appointments to your calendar that will occur over the course of the next 10 minutes. You'll note that the states don't actually change with the calendar appointments. I've found that the CalendarAppointment* and CalendarNextAppointment* seem to be particularly troublesome while CalendarHomeScreenAppointment* stuff works a little more frequently (though these events are much less useful to us).

3. Going back to using the system state events. Interestingly, the following code will sometimes throw a NullReferenceException because the args object passed in is null. Perhaps this makes sense--if the current appointment is deleted the BusyStatus switches from a known state to a null state--however, this is underdocumented. An unsavvy programmer could easily fail to null check the ChangeEventArgs argument.

private void OnStateCalendarAppointmentBusyStatusChanged(object sender,
ChangeEventArgs args)
{
AppendText(SystemProperty.CalendarAppointmentBusyStatus,
(BusyStatus)args.NewValue);

}

Hopefully someone at MS has the patience to read through this whole post and respond. If you made it this far, thanks!

UPDATE 09/11/2007: I received word from Microsoft today that this is indeed a bug in WM 5.0. Direct quote from the Microsoft employee, "I’ve confirmed the bug on WM 5.0, but it seems to be fixed in WM 6.0. That is, that the Notification Broker (and associated registry settings) don’t update properly when the calendar changes."

Issues with SystemState.Date

The SystemState functionality on Windows Mobile 5.0 phones provides a straightforward facility for tracking certain device states and events (e.g., power, signal strength, sms and phone calls). See this link for a listing of all (144?) individual system states that one can easily track with the .NET event model. However, we've identified a few problems with this framework in my lab.

The first relates to SystemProperty.Date. Subscribing to the SystemProperty.Date results in a ChangedEventArgs object with the wrong value type, or, at least a value type that is inconsistent with documentation. Even the SystemState.Date type (which is of type System.DateTime) would leave you to believe that the ChangedEventArgs.NewValue object would be of type DateTime. In this case, however, it's actually a byte array of length 8. We believe this represents milliseconds in UTC time (8 bytes in C# is a long type). See this Date and Time article at MSDN for more on working with date and time. This typing issue results in a InvalidCastException in the following code.


private SystemState _stateDate;

cstr(){
_stateDate = new SystemState(SystemProperty.Date);
stateDate.Changed += new ChangeEventHandler(OnStateDateChanged);
}

private void OnStateDateChanged(object sender, ChangeEventArgs args){
//The code below will throw an exception as the args.NewValue type is actually
//a byte array of size 8! This is undocumented and unexplained.
DateTime date = ((DateTime)args.NewValue);
}

I have not comprehensively explored this issue. Other than the fact that the arg.NewValue is a byte array of length 8, I cannot elaborate. It is only conjecture that args.NewValue here is a timestamp in milliseconds at UTC. Regardless, it seems to contradict documentation and go against the paradigm established by other SystemState functions.

One final note about this state. The SystemState.Date docs say:

Gets the current date. This Date/Time value changes once per day. Monitor this property to perform actions on a daily basis. Do not use Date to get an accurate clock time; instead, use Time.

The documentation does not explicitly point out the strategy or time of day that SystemState.Date changes, only that it changes daily. I had hoped that it would change at midnight each and every day; however, what we found was that SystemState.Date appeared to change at midnight GMT (or 5PM PST). So, even if you were to schedule a daily routine with this property, it wouldn't necessarily be at the beginning of the day. Given this limitation, we decided to roll our own SystemState.Date event class that fires at midnight each day.

If I get a chance this weekend, I'd like to follow up this post with the problems we've experienced at my lab with the SystemState.CalendarAppointment* properties. They don't appear to change at all no matter what the calendar/appointment structure is like on the phone.

Update February 9th, 3:15AM: My suspicions about the 8-byte array were somewhat correct. It is a representation of DateTime. This is how you properly convert the byte array to a DateTime object:

byte[] rawValue = SystemState.GetValue (SystemProperty.Time) as byte[];
long value = BitConverter.ToInt64 (rawValue, 0);
DateTime time = DateTime.FromFileTime (value);

Size Limit of SQL Server 2005 Mobile Database

Size limit of SQL Server 2005 Mobile database: 4 Gigabytes.
Size limit of SQL Server Everywhere Edition database: 4 Gigabytes.

(link)

SqlCeParameter Bug?

I made the following post to microsoft.public.sqlserver.ce on August 17th.

The following SqlCeCommand throws an exception. Is this a bug or am I doing something wrong?

SqlCeCommand sqlCmd = sqlConn.CreateCommand();
sqlCmd.CommandText = String.Format("INSERT INTO {0} (UserId, VersionId, {1}, PropertyName, PropertyValue, PropertyValueType) VALUES (@userid, @versionid, @headercolumnval, @propertyname, @propertyvalue, @propertyvaluetype)", strTable, headerName);
String strValue = p.Value.ToString();
sqlCmd.Parameters.Add("@userid", SqlDbType.NVarChar, 255, "UserId").Value = Globals.UserId;
sqlCmd.Parameters.Add("@versionid", SqlDbType.BigInt, 8, "VersionId").Value = Globals.VersionId;
sqlCmd.Parameters.Add("@headercolumnval", SqlDbType.NVarChar, 255, headerName).Value = headerColumnValue;
sqlCmd.Parameters.Add("@propertyname", SqlDbType.NVarChar, 255, "PropertyName").Value = p.Key;
sqlCmd.Parameters.Add("@propertyvalue", SqlDbType.NVarChar, 4000, "PropertyValue").Value = strValue;
sqlCmd.Parameters.Add("@propertyvaluetype", SqlDbType.NVarChar, 255, "PropertyValueType").Value = p.Type.FullName;
sqlCmd.Transaction = sqlTransact;
sqlCmd.Prepare();
sqlCmd.ExecuteNonQuery();
sqlCmd.Dispose();

Throws the following exception at the sqlCmd.ExecuteNonQuery() line:

System.InvalidOperationException was unhandled
Message="@propertyvalue : String truncation: max=255, len=463, value="(removed for posting clarity)"."
StackTrace:
at System.Data.SqlServerCe.SqlCeCommand.FillParameterDataBindings()
at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommandText()
at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand()
at System.Data.SqlServerCe.SqlCeCommand.ExecuteNonQuery()
at MyExperience.IO.DatabasePopulator.InsertProperty()
at MyExperience.IO.DatabasePopulator.ParseTriggers()
at MyExperience.IO.DatabasePopulator.Populate()
at MyExperience.MyExperienceFramework.Initialize()
at MyExperience.Application.MainForm.OnLoad()
at System.Windows.Forms.Form._SetVisibleNotify()
at System.Windows.Forms.Control.set_Visible()
at OpenNETCF.Windows.Forms.Application2.RunMessageLoop()
at OpenNETCF.Windows.Forms.Application2.Run()
at MyExperience.Application.Program.Main()

However, if I change the line:

sqlCmd.Parameters.Add("@propertyvalue", SqlDbType.NVarChar, 4000, "PropertyValue").Value = strValue;

to

sqlCmd.Parameters.Add("@propertyvalue", SqlDbType.NVarChar).Value = strValue;

no exception is thrown and, better yet, the full amount of data actually makes it into the database. That is, the value is not truncated to the 255 length that Sql somehow thinks exists as a constraint.

Note that my schema for this table looks like:

TableName:
TriggerProperties

Columns:
UserId (PK, nvarchar(255), not null)
VersionId (PK, nvarchar(255), not null)
TriggerName (PK, nvarchar(255), not null)
PropertyName (PK, nvarchar(255), not null)
PropertyValue (nvarchar(4000), not null)
PropertyValueType (nvarchar(255), not null)

I followed this post up with this one on August 18th:

Ah, it looks like SqlCeParameter forces the Size property to 255. That's strange given the fact that the docs say, as previously mentioned, that the nvarchar data type can range from 1 to 4000 characters.

However, bear in mind, that by simply avoiding setting the Size property (either via the constructor or the property itself) allows one to INSERT nvarchar data much larger than 255. In other words, it looks like this "bug" is at the parameter level and not database level.

This is the test code:

for (int i = 0; i <= 500; i+=100){ SqlCeParameter param = new SqlCeParameter("@Test", SqlDbType.NVarChar, i); Debug.WriteLine(i + ": " + param.Size);

//Just in case the property acts differently, set it and print again
param.Size = i;
Debug.WriteLine(i + ": " + param.Size);

}

prints:

0: 0
0: 0
100: 100
100: 100
200: 200
200: 200
300: 255
300: 255
400: 255
400: 255
500: 255
500: 255

Any input from MS (or MVPs) on this? I'm far from being a seasoned C# SQL developer so maybe I'm doing something wrong.

Unfortunately I haven't heard any substantive comments back on usenet from MS or otherwise. Anyone in the blogosphere have an idea?

Update 8/25/2006 @ 11:08PM: On August 21st, I e-mailed the SQL Server Everywhere team via their contact page about this issue. Here's what I said:

Not sure how appropriate it is to contact you about problems I'm experiencing with Sql Mobile 2005, but I thought I'd give this a shot. If this is the wrong venue of communication, just ignore this plead for help.

I'm having issues with the SqlCeParameter class, particularly with regards to how it handles setting the Size property. I can't seem to set the size to anything larger than 255 for the nvarchar datatype.

Please see my usenet post for more details (unfortunately no one with any expertise in this area has responded yet): direct link here

I received a response on August 24th:

Yes Jon you are right that there seems to be some problem with larger than 255 chars. I just tried and could repro it.

I shall come back to you on this after some more digging around to see if there is any work around.

To which I responded:

Thank you so much for the response. I was really hoping I'd hear back from someone at Microsoft to help me with this.

The work around that I've come up with is to simply leave out specifying the length parameter in the SqlCeParameter object. So, for my SQL data columns that are of type nvarchar and length > 255 I do this:

SqlCeParameter param = new SqlCeParameter("@custid", SqlDbType.NVarChar);

Rather than explicitly including a length and column:

SqlCeParameter param = new SqlCeParameter("@custid", SqlDbType.NVarChar, 2000, “CustomerID”);

You’ll note that in the former case your data can be of length > 255 and, critically, it will still make it into the database. In the latter case, however, you incur an exception.

Perhaps my workaround will shed some light on the underlying problem.

On August 25th I received another response:

I found the work around you used works perfectly and some of the internal apps also use it the same way as you mentioned. However, while digging I found that you can not use parameterized query when you have image/ntext columns (Large Object Datatype). Will update you more once I get some clarity.

To which I responded:

Hmm, I also use image data types in my SQL Mobile 2005 database and it appears to be working. In fact, a parameterized query, I believe, is the only way to get binary data into the database. What was the issue you ran into here?

Nullable Types

I have yet to work extensively with nullable types, a new addition to C# 2.0. A nullable type can represent the normal range of values for its underlying value type, plus an additional null value. This is somewhat similar to how SQL allows you to create an int datatype column that allows a null value. To declare a nullable type, you use the '?' operator. For example, int? num = null;

More on the C# 2.0 Nullable Types
Jon Skeet's Blog : Elegant comparisons with the null coalescing operator
Jon Skeet's Blog : Nullable types and the null coalescing operator
MSDN Reference : ?? Operator (C# Reference)
MSDN Reference : Nullable Types (C# Programming Guide)

Recently Published Mobile Device Downloads

Direct link to the Microsoft Download Center for Mobile Devices (sorted by release date).

Hard Reset T-Mobile SDA

Had tip: (nino and here)

Update 08/26/2007: I couldn't get this to work on my Cingular 2125 device. I finally figured out what I was doing wrong; I held down the power button too long. You do not need to hold the power button down until you see the boot screen... simply press it for 0.5-1 seconds. Also, Mike Poulson points out there are two ways to reset the HTC Torando/Faraday-based SmartPhone.

The first way requires you to be able to boot the phone and get to the start menu. The second is if you cannot do this (i.e., forgot PIN or will not boot all the way).

First way: Start -> Accessories -> Clear Storage

Second way:

1. Remove Battery

2. Replace battery
3. Hold down right and left soft keys
4. Press Power button for ~1.45 seconds then release Power button only
5. Confirm prompt to reset

Usage of SqlCeCommand.Parameters

I posted the following to microsoft.public.sqlserver.ce:

I'm curious about the Parameters property in SqlCeCommand. Is this the preferred/best usage of the SqlCeCommand object? I suppose one benefit of using Parameters is the ability to set binary/image data (as those are byte arrays) for insertions/updates. Are there other benefits? I tend to format all of my command statements like the following:

SqlCeCommand cmd = sqlConn.CreateCommand();
cmd.CommandText = String.Format("INSERT INTO {0} (ActionName,
ActionType) VALUES ('{1}', '{2}')", "Actions", actionName, actionType);
cmd.Transaction = sqlTransact;
cmd.ExecuteNonQuery();

instead of

SqlCeCommand cmd = sqlConn.CreateCommand();
cmd.CommandText = "INSERT INTO @tablename (ActionName, ActionType)
VALUES (@actionname, @actiontype)";
cmd.Parameters["@tablename"].Value = "Actions";
cmd.Parameters["@actionname"].Value = actionName;
cmd.Parameters["@actiontype"].Value = actionType;
cmd.Transaction = sqlTransact;
cmd.ExecuteNonQuery();

What's the "best practice?"

Ilya Tumanov, a member of the .NET Compact Framework Team, responded with:

Yes, parameterized query is a preferred way. Benefits are:

• No need to create bunch of string objects all the time (in your code).
• No need to convert parameters from binaries to strings (in your code).
• No need to parse strings to get binaries back (in SQL CE).
• No locale/format specific issues as binary is format less and locale independent.
• No need to redo each command from scratch, prepared execution plan can be used with just different parameters.
• Works for all data types including binary/image.

Performance benefits from using parameters range from significant to really huge.

(link)

Update 08/16/2006 12:57AM: I followed up the above post with:

Thanks so much for this thorough response--it clarified much for me. One follow-up question, what do you mean by "No need to redo each command from scratch, prepared execution plan can be used with just different parameters?" This is most certainly my naivete here with respect to SQL; a short elaboration would probably suffice.

Illya once again responded:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlc...

Preparing Commands
To be able to execute a query, the database engine must first parse, compile, and optimize the SQL statement. Often, this work can be done once if the command is to be executed multiple times, potentially saving time. If clients expect to run a query more than once, it is recommended that the command be prepared once, then call Execute multiple times. This should maximize performance by avoiding query recompilation. Commands can be prepared prior to execution by calling ICommandPrepare::Prepare. This is equivalent to compiling the command.

Real life equivalent would be a move to another apartment. Would you rather move your stuff item by item or pack it and move it all at once?