Help with MS SQL Server Compact Edition

sdarbyj · Post by **sdarbyj** » Tue 22 Jun 2010 17:31

I am trying to open a large SQLCE 3.5 database I created with Visual Studio 2008, in a delphi project.

I am trying to use a TSQLConnection, and I am getting this error:
Project raised exception class Exception with message 'The database file cannot be open with the current version of SQL Server Compact Edition'.

I am running Delphi 2010 on windows 7.

Here are the params I am using:
BlobSize=-1
HostName=
DataBase=c:\myDB.sdf
DriverName=DevartSQLServerCompact
MaxDatabaseSize=4090
User_Name=
Password=
LongStrings=True
EnableBCD=True
FetchAll=True

I am using the dbexpxda40.dll Library Name and sqlceoledb35.dll vendorLib.

Any help woudl be appreciated

Dimon · Post by **Dimon** » Wed 23 Jun 2010 08:55

To set the 'max database size' attribute for a SQL Server CE database, you should set appropriate parameter of TSQLConnection, like this:

Code: Select all

TSQLConnection.Params.Values['Custom String'] := 'MaxDatabaseSize=4090';

sdarbyj · Post by **sdarbyj** » Wed 23 Jun 2010 20:43

I've changed course at this time.

When I couldn't get this working in Delphi I went back to C# to do some performance testing thinking the times would be compareable enough.
From my tests it appears sql server compact edition will not work for my current project it is just too slow compared to our current system.

Dimon · Post by **Dimon** » Thu 24 Jun 2010 09:05

Did you compare SQL Server compact edition on Delphi and C#, or on C# you are working with standard SQL Server edition?

sdarbyj · Post by **sdarbyj** » Fri 25 Jun 2010 17:43

I only tested using c#.

Perhaps you could help me. What is the fastest way to get data from an sqlce file? (or other database)

I am returning a few rows of data based on a indexed column.
I was using a sqlcedatareader in c# for my performance tests.

This method took at least two times longer to read the data, than reading from our current flat file format.

sdarbyj · Post by **sdarbyj** » Fri 25 Jun 2010 17:46

This is the c# code

Code: Select all

            sql = String.Format(@"SELECT * FROM V WHERE pk_V={0} ", pkV);
            cecmd.CommandText = sql;

            using (SqlCeDataReader reader = cecmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    res = String.Format("{0}", reader[0]);
                    break;
                }
            }
            return res;

Dimon · Post by **Dimon** » Tue 29 Jun 2010 10:36

If you need to use dbExpress driver you should use the TSQLConnection and TSQLDataSet components to get data from SQL Server CE database.
Also you can use the TMSCompactConnection component of SDAC. SQL Server Data Access Components (SDAC) is a library of components that provides access to Microsoft SQL Server databases. You can find more detailed information about SDAC by the following link: http://devart.com/sdac/