Hi!

I am developing a ASP.NET 1.1 application for one of our customers in
Stockholm. Today we are using Microsoft Access for our database. The
application runs on XP, Vista and Win 2003 Server. Our customer want to
support SQL Server and we have been looking at the Express Edition. But now I
have seen that Compact Edition is available for desktops. We will probably
upgrade to the latest .NET Framework too.
The first impression is that we shall use Express, but after reading a
document that shall guide you in choosing between the products there is a lot
of things of Compact that is satisfying me. For example the download size and
the ability to embed the engine in an application will allow our customer to
distribute updates easy via mail and the Compact engine will not be affected
by automatic updates in Windows. I have a few questions that may ing me
choosing data access product.

1. Is it possible to use and embed Compact Edition in an ASP.NET 1.1
application? If not, for ASP.NET 2.0?

2. Is there a Visual Studio integration for the Compact Edition? If there
is, for which Visual Studio version?

3. Is Access, Express and Compact supported by the Distributed Transaction
Coordinator, DTC?

4. What about performance comparing, Access (used today with a database
containing one table with a few thousand of rows, the other ones a few
hundred rows each), Express and Compact?

5. Can I use the Sql-classes in ADO.NET when accessing a Compact database?
Does the "data access component" in the Enterprise library support a Compact
database?

--
Thanks in advance

Anders Olsson

> The first impression is that we shall use Express, but after reading a
> document that shall guide you in choosing between the products there is a lot
> of things of Compact that is satisfying me. For example the download size and
> the ability to embed the engine in an application will allow our customer to
> distribute updates easy via mail and the Compact engine will not be affected
> by automatic updates in Windows. I have a few questions that may ing me
> choosing data access product.

If you deploy using the MSI file then Windows Updates will deliver
updates to SQL Server Compact. Only if you deploy manually the DLLs
Windows Update will not affect it.

>
> 1. Is it possible to use and embed Compact Edition in an ASP.NET 1.1
> application? If not, for ASP.NET 2.0?
>

Yes, any client API can connect to Compact Edition. If you use the OLE
DB provider then it does not even require the .NET framework. But if you
use the ADO.NET provider then .NET 2.0 is required (since it is using
the SQL Server Native Client).

> 2. Is there a Visual Studio integration for the Compact Edition? If there
> is, for which Visual Studio version?
>

Visual Studio 2005/2008:
http://www.microsoft.com/downloads/d...displaylang=en
http://support.microsoft.com/kb/920700

> 3. Is Access, Express and Compact supported by the Distributed Transaction
> Coordinator, DTC?
>

Distributed transactions are not supported with Compact Edition.
http://technet.microsoft.com/en-us/l.../ms172400.aspx

> 4. What about performance comparing, Access (used today with a database
> containing one table with a few thousand of rows, the other ones a few
> hundred rows each), Express and Compact?
>

Express Edition will have best performance on a large database.

> 5. Can I use the Sql-classes in ADO.NET when accessing a Compact database?
> Does the "data access component" in the Enterprise library support a Compact
> database?
>

The latest Enterprise Library supports Compact Edition.


There are may other factors to consider, like Compact Edition does not
support procedural T-SQL (stored procedures, triggers, views),
limitations of T-SQL (no ROW_NUMBER, TOP, IF), missing native XML support.


--
Plamen Ratchev
http://www.SQLStudio.com

Thank you for the answers. I have 3 new questions.

1. I have seen that it is possible to export an Access database to SQL
Server. A) Does this database file fit into Express and Compact? B) Is it
possible in such easy way to convert a database including data between
Express and Compact? (Both directions)

2. Does ADO.NET and Enterprise Library handle simultaneous database updates
(different ASP.NET users, different connections, different threads, but same
SQL user) without interfering with each other? (Lost of performance is
acceptable.)

3. Based on the information given in my 2 posts, do you recommend using
Compact for our ASP.NET application?

--
Thanks in advance

Anders Olsson


"Plamen Ratchev" wrote:

> > The first impression is that we shall use Express, but after reading a
> > document that shall guide you in choosing between the products there is a lot
> > of things of Compact that is satisfying me. For example the download size and
> > the ability to embed the engine in an application will allow our customer to
> > distribute updates easy via mail and the Compact engine will not be affected
> > by automatic updates in Windows. I have a few questions that may ing me
> > choosing data access product.
>
> If you deploy using the MSI file then Windows Updates will deliver
> updates to SQL Server Compact. Only if you deploy manually the DLLs
> Windows Update will not affect it.
>
> >
> > 1. Is it possible to use and embed Compact Edition in an ASP.NET 1.1
> > application? If not, for ASP.NET 2.0?
> >
>
> Yes, any client API can connect to Compact Edition. If you use the OLE
> DB provider then it does not even require the .NET framework. But if you
> use the ADO.NET provider then .NET 2.0 is required (since it is using
> the SQL Server Native Client).
>
> > 2. Is there a Visual Studio integration for the Compact Edition? If there
> > is, for which Visual Studio version?
> >
>
> Visual Studio 2005/2008:
> http://www.microsoft.com/downloads/d...displaylang=en
> http://support.microsoft.com/kb/920700
>
> > 3. Is Access, Express and Compact supported by the Distributed Transaction
> > Coordinator, DTC?
> >
>
> Distributed transactions are not supported with Compact Edition.
> http://technet.microsoft.com/en-us/l.../ms172400.aspx
>
> > 4. What about performance comparing, Access (used today with a database
> > containing one table with a few thousand of rows, the other ones a few
> > hundred rows each), Express and Compact?
> >
>
> Express Edition will have best performance on a large database.
>
> > 5. Can I use the Sql-classes in ADO.NET when accessing a Compact database?
> > Does the "data access component" in the Enterprise library support a Compact
> > database?
> >
>
> The latest Enterprise Library supports Compact Edition.
>
>
> There are may other factors to consider, like Compact Edition does not
> support procedural T-SQL (stored procedures, triggers, views),
> limitations of T-SQL (no ROW_NUMBER, TOP, IF), missing native XML support.
>
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com
>

Anders Olsson 2 wrote:
> Thank you for the answers. I have 3 new questions.
>
> 1. I have seen that it is possible to export an Access database to SQL
> Server. A) Does this database file fit into Express and Compact? B) Is it
> possible in such easy way to convert a database including data between
> Express and Compact? (Both directions)
>

In most cases an Access database should fit just fine. SQL Server
Express has 4 GB limit for database size. Converting data is not an easy
process and should be carefully planned. There are some tools that can
assist. Going both directions will not be easy.

> 2. Does ADO.NET and Enterprise Library handle simultaneous database updates
> (different ASP.NET users, different connections, different threads, but same
> SQL user) without interfering with each other? (Lost of performance is
> acceptable.)
>

Yes, the Enterprise Library handles very well concurrent users, but that
is more a feature of the database/.NET.

> 3. Based on the information given in my 2 posts, do you recommend using
> Compact for our ASP.NET application?
>

I would avoid using the Compact edition for a web site. You lose a lot
of the capabilities provided by Express. The Compact edition is
appropriate for applications on mobile devices and those that require
very low footprint.

A couple articles that may be of interest:
http://www.microsoft.com/sql/edition...omparison.mspx
http://www.microsoft.com/sql/prodinf...-features.mspx

--
Plamen Ratchev
http://www.SQLStudio.com