Article ID: 813731 - Last Review: December 7, 2006 - Revision: 5.5

How to retrieve data from a SQL Server CE 2.0 database or from a SQL Server 2005 Compact Edition database and save the data in an XML document

This article describes a Microsoft Visual C# .NET application that you can use to retrieve data from a Microsoft SQL Server 2000 Windows CE 2.0 Edition database or from a SQL Server 2005 Compact Edition database and to save the data in an XML document. This article also describes how to populate a DataGrid control by using the data in the XML document.

Back to the top

MORE INFORMATION

The sample application that is provided in this article retrieves data from a SQL Server CE or SQL Server 2005 Compact Edition database that is running on a Microsoft Pocket PC. The retrieved data is written to an XML document by using the DataSet object. The data in the XML document is read by using a DataSet object, and it is then populated in a DataGrid control. The ability to load and retrieve data from XML documents on mobile devices, such as Pocket PCs, is very useful because XML documents are interoperable across operating systems.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

To create the sample application, follow these steps:

Start Microsoft Visual Studio .NET 2003.
On the File menu, point to New, and then click Project.
In the New Project dialog box, click Project Types, and then click Visual C# Projects.
In Templates, click Smart Device Application, and then click OK.
In the Smart Device Application Wizard dialog box, click OK.
On the Project menu, click Add Reference.
In the Add Reference dialog box, click the .NET tab.
Click System.Data.SqlServerCe in the Component Name list, and then click Select.
Click System.Data.Common in the Component Name list, click Select, and then click OK.
In design view of the Form1 form, add a DataGrid control named dataGrid1.
On the Form1 form, add three Button controls. Name the first Button control btnCreate, name the second Button control btnWriteXML, and then name the third Button control btnLoadGrid.
In the Form1.cs code window, add the following statement to the top of the code:
using System.Data.SqlServerCe;

Add the following code to the btnCreate_Click event handler:

// Specify the data source
SqlCeEngine objEg = new SqlCeEngine("Data Source=\\my documents\\MYDB.SDF");
SqlCeConnection objCon = new SqlCeConnection("Data Source=\\my documents\\MYDB.SDF");

try
{
	// Create the SQL Server CE database
	objEg.CreateDatabase();
	objEg.Dispose();		
	objCon.Open();

	//  Create table mytable
	SqlCeCommand objCom = new SqlCeCommand("Create table mytable(id int primary key, name nvarchar(10))", objCon);
	objCom.ExecuteNonQuery();

	// Insert sample rows
	objCom.CommandText = "insert into mytable values(1, 'Bob')";
	objCom.ExecuteNonQuery();

	objCom.CommandText = "insert into mytable values(2, 'Sue')";
	objCom.ExecuteNonQuery();

	objCom.CommandText = "insert into mytable values(3, 'Mary')";
	objCom.ExecuteNonQuery();

	objCom.CommandText = "insert into mytable values(4, 'Larry')";
	objCom.ExecuteNonQuery();

	objCom.CommandText = "insert into mytable values(5, 'Ted')";
	objCom.ExecuteNonQuery();

	objCom.CommandText = "insert into mytable values(6, 'Mark')";
	objCom.ExecuteNonQuery();

	objCom.CommandText = "insert into mytable values(7, 'Bob')";
	objCom.ExecuteNonQuery();

	objCom.CommandText = "insert into mytable values(8, 'Tom')";
	objCom.ExecuteNonQuery();
}

catch (SqlCeException ex)
{
	MessageBox.Show(ex.Message);
}

finally
{
	objCon.Close();
	objCon.Dispose();
	MessageBox.Show("Table mytable created and populated with sample rows.");
}

Add the following code to the btnWriteXML_Click event handler:

// Specify the data source
SqlCeConnection oCon = new SqlCeConnection("Data Source=\\my documents\\MYDB.SDF");
SqlCeDataAdapter oDA = new SqlCeDataAdapter("select * from mytable", oCon);

try
{	
	DataSet oDS = new DataSet("mytable");

	//  Populate the DataSet 
	oDA.Fill(oDS);

	//  Write the DataSet contents to the XML document
	oDS.WriteXml("\\my documents\\mytable.xml");
}

catch (SqlCeException ex )
{
	MessageBox.Show(ex.Message);
}

finally 
{
	oDA.Dispose();
	MessageBox.Show("XML document mytable.xml created.");
}

Add the following code to the btnLoadGrid_Click event handler:

try 
{
	DataSet oDS = new DataSet("customers");
	oDS.ReadXml("\\my documents\\mytable.xml");
	dataGrid1.DataSource = oDS.Tables[0].DefaultView;
}

catch (SqlCeException ex)
{ 
	MessageBox.Show(ex.Message); 
}

finally
{
	MessageBox.Show("DataGrid populated.");}
}