Re: Is it just me or are there BIG problems with SQLCE 3.0?

On Oct 8, 8:00 pm, "William Vaughn" <billvaNoS...@xxxxxxxxx> wrote:
I encourage you to get up on MSDN Connect and start pounding on the SQL CE
3.0 team to help them repro the performance and crash cases you have
experienced. I have passed your comments to the dev team directly but did
not get much more than "It seems to run faster for us...". Since they have
not shipped I think it would make sense for everyone to get these issues
resolved.


Thanks. I've sent a message to the team through their blog as was
requested on the forums.microsoft SQLCE forum. What is MSDN Connect,
and how do I use this to 'pound' the SQL team? :)

I'm including a simplified example below for reference. Please note
that the content of the example is not that important, this same test
has been done with parameterised SQL, SqlCeRecordset and C++/OLE DB.
It *always* fails at exactly the same point (ie. 25592 records), and
never fails on SQLCE 2.0 (obviously the SqlCeRecordset test could not
be done on SQLCE 2.0). As suggested by another poster, I've added a
bit of code into my test which closes and reopens the database
connection after 10000 inserts. This does solve the problem on SQLCE
3.0 (with all of the above access methods). However, I've read that
opening and closing the DB connection is not a good idea. Will this
introduce any additional problems, and if so, what are they?

TIA

Source code:

using System.Data;
using System.Data.SqlServerCe;


namespace TestDB
{
class Program
{
static void Main(string[] args)
{
SqlCeConnection sqlConnection = new SqlCeConnection();
SqlCeCommand sqlCommand = sqlConnection.CreateCommand();
SqlCeResultSet sqlResult;


string strDatabase = "\\TestDB.sdf";
string strTable = "TestTable";


try
{
// Database connection string
sqlConnection.ConnectionString =
"Data Source=" + strDatabase;


// Database file doesn't exist?
if (!System.IO.File.Exists( strDatabase ))
{

Console.WriteLine("Creating database: " +
strDatabase);


// Create SQL engine object
SqlCeEngine sqlEngine = new SqlCeEngine(
sqlConnection.ConnectionString);


// Use it to create database

sqlEngine.CreateDatabase();
}


Console.WriteLine("Opening database: " + strDatabase);


// Open database connection
sqlConnection.Open();


// Display SQL version
Console.WriteLine("SQL Version: "
+ sqlConnection.ServerVersion.ToString());


// Check if table exists
sqlCommand.CommandText = "SELECT TABLE_NAME FROM "
+ "INFORMATION_SCHEMA.TABLES WHERE "
+ "TABLE_NAME = '" + strTable + "'";
sqlResult = sqlCommand.ExecuteResultSet(
ResultSetOptions.Insensitive);


// Table doesn't exist?
if (!sqlResult.Read())
{
Console.WriteLine("Creating table: " + strTable);


// Create table
sqlCommand.CommandText = "CREATE TABLE " +
strTable
+ "(Sequence integer IDENTITY(1,1) NOT NULL
PRIMARY KEY, "
+ "Timestamp datetime DEFAULT GETDATE(),"
+ "Message nvarchar(80))";
sqlCommand.ExecuteNonQuery();
}


string strText;

for (int i = 1; i <= 500000; i++)
{
Console.Write("\rInserted " + i.ToString() + "
records ");


strText = "Row Number: " + i.ToString();

sqlCommand.CommandText = "INSERT INTO " + strTable
+ " (Message) VALUES ('" + strText + "')";
sqlCommand.ExecuteNonQuery();
}
}
// SQL error
catch(SqlCeException sqlex)
{
// Display all error messages
Console.WriteLine( "ERROR:" );
foreach (SqlCeError sqlError in sqlex.Errors)
{
Console.WriteLine( sqlError );
}
}
// Other errors
catch(Exception ex)
{
// Display error message
Console.WriteLine( "ERROR:" );
Console.WriteLine( ex.Message );
}
finally
{
// Close connection
if (sqlConnection.State != ConnectionState.Closed)
{
Console.WriteLine("Closing database");

sqlConnection.Close();
}
}

Console.ReadLine();
}
}
}

.

Relevant Pages

  • RE: Logging in in background
    ... wold take all kinds of modifictions as I'd need to be checking that each SQL ... my database and all have connection strings associated with them. ... I suspect that there is also an issue on the SQL Server side as I keep ...
    (microsoft.public.access.modulesdaovba)
  • Re: User not associated with trusted SQL Server connection
    ... > using the osql utility. ... > associated with a trusted SQL Server connection. ... > database or is it just for that instance. ...
    (microsoft.public.sqlserver.security)
  • Re: Concurrent database access in SQL 2005 Mobile
    ... What version of SQL CE are you using? ... Are you accessing the database from an app written in C++? ... then opens his work forms and it's in those ... It wouldn't seem to me that you'd need a new connection to ...
    (microsoft.public.sqlserver.ce)
  • Re: Perl ODBC complex query example needed
    ... newsgroup about SQL would be better for that. ... I am guessing that you probably want to access an ODBC database ... the connection string will be different). ... > sql connection is in scope for the query. ...
    (comp.lang.perl.misc)
  • Re: Is it just me or are there BIG problems with SQLCE 3.0?
    ... Connect is the feedback mechanism use to submit bugs to the dev teams. ... Hitchhiker's Guide to Visual Studio and SQL Server ... opening and closing the DB connection is not a good idea. ... // Display all error messages ...
    (microsoft.public.sqlserver.ce)