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

MORE INFORMATION

The sample application that is provided in this article retrieves data from a SQL Server CE or SQL Server 2005 Mobile 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:

1. Start Microsoft Visual Studio .NET 2003.
2. On the File menu, point to New, and then click Project.
3. In the New Project dialog box, click Project Types, and then click Visual C# Projects.
4. In Templates, click Smart Device Application, and then click OK.
5. In the Smart Device Application Wizard dialog box, click OK.
6. On the Project menu, click Add Reference.
7. In the Add Reference dialog box, click the .NET tab.
8. Click System.Data.SqlServerCe in the Component Name list, and then click Select.
9. Click System.Data.Common in the Component Name list, click Select, and then click OK.
10. In design view of the Form1 form, add a DataGrid control named dataGrid1.
11. 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.
12. In the Form1.cs code window, add the following statement to the top of the code:

    using System.Data.SqlServerCe;

13. 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.");
    }

14. 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.");
    }

15. 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.");}
    }

16. On the Build menu, click Deploy Solution.