Tuesday, March 20, 2007

New SQL Server Compact Edition Article

"Lighten Up Your Local Databases" from Visual Studio Magazine's March 2007 issue recommends that you "[p]ut local data storage on a resource diet and gain performance with the newly upgraded (and free) SQL Server 2005 Compact Edition." The article's sample application includes a VS 2005 application that demonstrates binding an SqlCeResultset object to a BindingNavigator and DataGridView:

Click for Full-Size Image

Neither SQL Server Management Studio [Express] or Visual Studio 2005's SP2 Server Explorer let you display and edit data in a grid. Orcas March 2007 CTP's Server Explorer SSCE table nodes have a context menu with a Show Table Data choice:

Click for Full-Size Image

Here are a couple of minor corrections and clarifications to Table 1:

  • The "Non-destructive change to the identity property of columns" topic states "You must drop and recreate the table to change the identity property." This is true to add or remove the identity property, but you can change the seed and increment values without altering the data in the table.
  • The @@IDENTITY system function has session scope and doesn't return a value until after you've inserted a row in a table. SCOPE_IDENTITY and IDENT_CURRENT aren't supported.

Update 3/27/2007: The March 27, 2007 edition of 1105 Media's .NETInsight newsletter features "Lighten Up Your Local Databases." Click here to subscribe.

4 comments:

Anonymous said...

I'm trying to use SQLce in a WebService (for logging).

I have the following code:
AppDomain.CurrentDomain.SetData("SQLServerCompactUnderWebHosting", True)
Dim myConnection As New SqlCeConnection("Data Source =""C:\consult\logs.sdf"";")


Running this causes a
"System.NotSupportedException: SQL Server Compact Edition is not intended for ASP.NET development"
on the SQLceConnection line.

What else do you have to do besides the SQLServerCompactUnderWebHosting to get this to work?

Thanks,

Roger Jennings (--rj) said...

Anonymous,

That question would be better addressed to the SSCE Forum: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=152&SiteID=1

--rj

Anonymous said...

The settings was change in RTM to SQLServerCompactEditionUnderWebHosting.

Read more at my blog:
http://www.vidmar.net/weblog/

Anonymous said...

The correct link to previous comment is
http://www.vidmar.net/weblog/archive/2007/04/19/SQL-Server-2005-Compact-Edition-and-ASP.NET.aspx