i am using sql server ce 3.5  my oledb provider is Microsoft.SQLSERVER.CE.OLEDB.3.5.  how do i modify appmain.vb to use this?  i don't think the connection wizard handles this but i could be wrong.  can someone step this beginner through whats necessary to hook my app up to the compact edition? 

Thanks in advance!

Mike

OK...let's get a couple of things cleared up here...there is not an OleDbDataSourceItem...when I posted this, I was alluding to the fact that you would be using a data source that using using the OleDbCommands.  SF comes with a base class called DbDataSourceItem.  There is not a generic OleDbDataSourceItem as each OleDbProvider may have different requirements and commands (such as Access and VFP).  But you can create your own very quickly.  Just open the SF source, and go to the Base assembly.  In that assembly there is a folder called Data\DataSourceClasses.  You will find all of the SF pre-plumbed data source classes there.  Just copy the Access or VFP source code and then go into your assembly and create your own data source item called WhateverDataSourceItem that is specific to the compact environment that you are trying to use.

Using SqlDataSourceItem on Embedded Editions
Now if I am not mistaken, if you are trying to use SQL Server Everywhere (or a compact edition of SQL Server) then you can use the SqlDataSourceItem and then specify the OLEDB provider or other supported SQL provider as part of your connection string and it will work just fine.  Ths SQLCommands should support the SQL Everywhere editions as I am pretty confident that this is the approach a number of our developers are using for this embedded data source.  But in the case that you are using something other than an SQL edition, then you will need to create the data source mentioned above.

This can also be impacted by the version of the Mobile SDK being used, etc.

Also, there is more than one way to connect to a Mobile version of SQL Server...you can use the SqlCeXXX commands and namespaces which would require you to create your own data source (mentioned above) or to use OLEDB or ODBC, which both of the latter mentioned should be able to use the standard SQLConnection and SQLCommand as well as the SqlDataSourceItem classes.

Finally, Keith, the DDT does not support a compact or embedded edition of SQL Server...this is a totally different platform and would require major changes to the DDT in order to work.

Hi Trent, if you had to develop a desktop solution for say home users - what database would you use foxpro or access?  it looks like i may not understand enough to get the framework to work with sql compact edition.  I am a past foxpro person but i wonder about using that anymore as a backend.

Hey Mike.

Just curious... what are your reasons for not using SQL Server Express?

Two apps i have in mind...one is for the home market  and I was thinking i didn't want the overhead and possibly slower performance of the SQL Express if I could use compact edition.  Also, i am not sure whats involved in distributing sqlexpress to get it install properly as part of the application installation.

do you have some experience with sqlexpress and distributing?

Distributing SQL Express is extremely easy.  In fact, if you use a tool like InstallAware, it is literally as easy as clicking a CheckBox and tweaking a few parameters.  We use InstallAware for our medical software and though, like any install software, there are some frustrations, one thing that they got right were the pre-reqs and installing pre-reqs.

Even if you choose to do this yourself, you can download the SQL Server Express redistributable and via command lines install it very easily.  SQL Server is also easy to test for in regards to services if you take the manual approach.  We use SQL Server Express in a high percentage of our medical software user base and so we have to test for and install SQL Server Express if needed.  This is really one of the easier parts of the install.

If I had to choose between those two it would be Access.  FoxPro is nothing but a problem waiting for a place to happen...this comes from MANY years of experience. 

But in truth, I would not use either of these, but SQL Server Express.  I know that this post has been geared around embedded database conversation, but there is a reason that we have not yet created a data source item for this...there is generally a better solution.  I do see the benefits of a disconnected laptop for the day, etc.  But even then I would tend to lean towards SQL Server Express.  First of all, it is free.  Secondly, it is faster and a full version of SQL Server.  Additionally, you don't have to deal with data type issues.  For example, SQL Mobile doesn't have a VarChar data type but only a NVarChar (unless something has changed recently).  Not that using an NVarChar is bad, but it just might not line up exactly with the desktop structures (if you a.] have the need or b.] ever plan to have a larger version of an application).

In my opinion, and if I were hired in a capacity for consultation purposes in a situation like this, I would tell my client that they are better off developing on a full SQL Server version versus an embedded edition....I have a lot of embedded experience from years past and there are other issues that you will inevitabely run into (i.e. row locking, etc.).