SqlCeConnection

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

UGottaBeKidding - 12 Nov 2007 22:37 GMT

I'm having some serious issues reading data from a SQL Server 2005 Mobile
database. My environment is the following: VS 2005 w/ SP 1, Windows CE 5.0,
and SQL Server 2005 Mobile.

I have a form with 3 buttons. The OnClick event of each button retrieves
data from the database using either a DataReader, DataSet, and binding the
DataSet to a DataGrid.

I have two module level SqlCeConnection objects, _con and _alwaysOpenCon.
The _alwaysOpenCon stays connected until the form closes to avoid the
database compacting issue. The _con object is used in each of the button
click events.

I have a database with one table (Device) containing two columns (DeviceID,
Name) with 10 records.

Here are my issues:
1. The DataSet and Bind takes more than 5 minutes when the
dataAdapter.Fill(dataSet) is called.
2. The dataGrid1.DataSource = dataSet.Tables[0] takes more than 5 minutes.
3. After closing the application and rerunning the application, I receive an
error but the Exception object does not contain a message to display.
4. My code actually has MessageBox.Show scattered throughout to show the
execution. For some reason, I can't get the environment and device to
communicate while debugging. The emulator doesn't help because it displays in
landscape and the device screen is portrait.

Can anyone help with these issues? I figure that I must be missing something.

Thanks for you assistance.

The source code follows
----------------------------------------------------------------------------------------------
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlServerCe;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

namespace TestApp
{
public partial class Form1 : Form
{
SqlCeConnection _alwaysOpenCon = null;
SqlCeConnection _con = null;

public Form1()
{
try
{
InitializeComponent();
_alwaysOpenCon = new SqlCeConnection(@"Data Source=\Program
Files\TestApp\default.sdf; Password='';");
_alwaysOpenCon.Open();
}
catch (Exception ex)
{
MessageBox.Show("Exception: " + ex.Message);
}
}

private void OpenConnection() {
string dataSource = @"Data Source=\Program
Files\TestApp\default.sdf; Password=''";
try
{
if (_con == null)
{
_con = new SqlCeConnection(dataSource);
_con.Open();
}

}
catch (Exception ex)
{
MessageBox.Show("Exception: " + ex.Message);
}
}

private void CloseConnection()
{
if (_con.State != ConnectionState.Closed)
{
_con.Close();
}
}

private void dataReaderButton_Click(object sender, EventArgs e)
{
SqlCeCommand sqlCommand = null;
SqlCeDataReader reader = null;
try
{
OpenConnection();

sqlCommand = new SqlCeCommand("SELECT * FROM Device", _con);
sqlCommand.CommandType = CommandType.Text;
reader = sqlCommand.ExecuteReader();
while (reader.Read())
{
MessageBox.Show("ID: " + reader.GetValue(0) + " - " +
reader.GetValue(1));
}
reader.Close();
sqlCommand.Dispose();
}
catch (Exception ex)
{
MessageBox.Show("Exception: " + ex.Message);
}
finally
{
CloseConnection();
}
}

private void dataSetButton_Click(object sender, EventArgs e)
{
SqlCeCommand sqlCommand;
DataSet dataSet;
SqlCeDataAdapter dataAdapter;

try
{
OpenConnection();

sqlCommand = new SqlCeCommand("SELECT * FROM Device", _con);
sqlCommand.CommandType = CommandType.Text;
MessageBox.Show("Set CommandText");

dataSet = new DataSet();
dataAdapter = new SqlCeDataAdapter();
dataAdapter.SelectCommand = sqlCommand;
dataAdapter.Fill(dataSet);

foreach (DataRow row in dataSet.Tables[0].Rows)
{
MessageBox.Show("DeviceID: " +
row["DeviceID"].ToString() + ": " + row["Name"].ToString());
}
dataSet.Dispose();
dataAdapter.Dispose();
sqlCommand.Dispose();
}
catch (Exception ex)
{
MessageBox.Show("Exception: " + ex.Message);
}
finally
{
CloseConnection();
}
}

private void bindButton_Click(object sender, EventArgs e)
{
SqlCeCommand sqlCommand = null;
DataSet dataSet = null;
SqlCeDataAdapter dataAdapter = null;

try
{
OpenConnection();

sqlCommand = new SqlCeCommand("SELECT * FROM Device", _con);
sqlCommand.CommandType = CommandType.Text;

dataSet = new DataSet();
dataAdapter = new SqlCeDataAdapter();
dataAdapter.SelectCommand = sqlCommand;
dataAdapter.Fill(dataSet);

dataGrid1.DataSource = dataSet.Tables[0];

dataSet.Dispose();
dataAdapter.Dispose();
sqlCommand.Dispose();
}
catch (Exception ex)
{
MessageBox.Show("Exception: " + ex.Message);
}
finally
{
CloseConnection();
}
}

private void Form1_Closing(object sender, CancelEventArgs e)
{
this._alwaysOpenCon.Close();
}
}
}