Populating a Microsoft SQL Server CE Database

A SQL Server CE database can be populated by using SQL DML statements. SQL Server CE 2.0 supports a subset of the DML statement that is supported by SQL Server. The supported commands are listed in Table 7.7. See the Microsoft SQL Server CE 2.0 Books Online to learn more about the DML statements.

Table 7.7. DML Statements Supported by SQL Server CE

STATEMENT	FUNCTION
INSERT	Adds a new row to a table.
UPDATE	Modifies existing data in a table.
DELETE	Removes rows from a table.
SELECT	Retrieves rows from the database and allows the selection of one or many rows or columns from one or many tables. The SELECT statement supports inner and outer joins, and the Order By , Group By , and Having clauses.

SQL Server CE Query Analyzer can be used to execute DML statements against the database. The SqlCeCommand class can be used to programmatically execute these statements by using the SQL Server CE Data Provider.

To populate a SQL Sever CE database, you can run a series of INSERT commands against the database. Running an INSERT statement is very similar to running a CREATE TABLE DDL statement. The steps are basically the same.

1. Open a connection to the SQL Server CE database by using an instance of the SqlCeConnection class.

2. Create a SqlCeCommand object instance, and give it the INSERT command string to run against the server.

3. Set the command type, and execute the statement by using the ExecuteNonQuery method.

Listing 7.5 demonstrates how to insert a new package into the Package table.

Listing 7.5 Inserting a new package into the Package table

 C# public static void InsertNewPackage(string pckgCode, string destID) {   String connstr = @"Data Source=\My Documents\PTSystem.sdf";   using(SqlCeConnection conn = new SqlCeConnection(connStr)) {     conn.Open();     string dmlInsertPackage =       "INSERT INTO Package(Code, DestinationID) " +       "VALUES ('" + pckgCode + "', '" + destID + "')";     SqlCeCommand cmdInsertPackage =       new SqlCeCommand(conn, dmlInsertPackage);     try {       cmdInsertPackage = new SqlCeCommand(conn , dmlInsertPackage);       cmdInsertPackage.CommandType = CommandType.Text;       cmdInsertPackage.ExecuteNonQuery();     } catch(SqlCeException scee) {       for(int curNdx=0; curNdx<scee.Errors.Count; ++curNdx) {         MessageBox.Show("Error:"+scee.Errors[curNdx].ToString()+"\n");       }     } finally {       if(cmdInsertPackage != null)         cmdInsertPackage.Dispose();     }   } } VB sub InsertNewPackage(pckgCode as string , destID as string)   Dim connstr As String   connstr = "Data Source=\My Documents\PTSystem.sdf"   Dim conn As SqlCeConnection   conn = New SqlCeConnection(connstr)   conn.Open()   Dim dmlInsertPackage As String   dmlInsertPackage = _     "INSERT INTO Package(Code, DestinationID) " & _     "VALUES ('" + pckgCode + "', '" + destID + "')"   Dim cmdInsertPackage As SqlCeCommand   cmdInsertPackage = New SqlCeCommand(dmlInsertPackage, conn)   Try     cmdInsertPackage = New SqlCeCommand(dmlInsertPackage, conn)     cmdInsertPackage.CommandType = CommandType.Text     cmdInsertPackage.ExecuteNonQuery()   Catch scee As SqlCeException     Dim curNdx As Int32     For curNdx = 0 To scee.Errors.Count       MessageBox.Show("Error:" & scee.Errors(curNdx).ToString())     Next     Finally       If Not cmdInsertPackage Is Nothing Then         cmdInsertPackage.Dispose()       End If     End Try     conn.Close() End Sub