Microsoft KB Archive/813731

= 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 =

Article ID: 813731

Article Last Modified on 12/7/2006

-

APPLIES TO


 * Microsoft SQL Server 2000 Windows CE Edition 2.0
 * Microsoft Visual Studio .NET 2003 Enterprise Architect
 * Microsoft Visual Studio .NET 2003 Enterprise Developer
 * Microsoft Visual Studio .NET 2003 Professional Edition
 * Microsoft SQL Server 2005 Compact Edition

-



SUMMARY
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.



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.</li> Click System.Data.SqlServerCe in the Component Name list, and then click Select.</li> Click System.Data.Common in the Component Name list, click Select, and then click OK.</li> In design view of the Form1 form, add a DataGrid control named dataGrid1 .</li> 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.</li>  In the Form1.cs code window, add the following statement to the top of the code: using System.Data.SqlServerCe; </li>  Add the following code to the btnCreate_Click event handler: // Specify the data source SqlCeEngine objEg = new SqlCeEngine(&quot;Data Source=\\my documents\\MYDB.SDF&quot;); SqlCeConnection objCon = new SqlCeConnection(&quot;Data Source=\\my documents\\MYDB.SDF&quot;);

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

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

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

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

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

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

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

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

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

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

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

finally {   objCon.Close; objCon.Dispose; MessageBox.Show(&quot;Table mytable created and populated with sample rows.&quot;); } </li>  Add the following code to the btnWriteXML_Click event handler: // Specify the data source SqlCeConnection oCon = new SqlCeConnection(&quot;Data Source=\\my documents\\MYDB.SDF&quot;); SqlCeDataAdapter oDA = new SqlCeDataAdapter(&quot;select * from mytable&quot;, oCon);

try {      DataSet oDS = new DataSet(&quot;mytable&quot;);

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

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

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

finally {   oDA.Dispose; MessageBox.Show(&quot;XML document mytable.xml created.&quot;); } </li>  Add the following code to the btnLoadGrid_Click event handler: try {   DataSet oDS = new DataSet(&quot;customers&quot;); oDS.ReadXml(&quot;\\my documents\\mytable.xml&quot;); dataGrid1.DataSource = oDS.Tables[0].DefaultView; }

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

finally {   MessageBox.Show(&quot;DataGrid populated.&quot;);} } </li> On the Build menu, click Deploy Solution.</li></ol>

<div class="references_section">