[Paste] SQL Server Mobile Database code

Sort out several sections of common C # code used to operate the SQL Server Mobile database for your reference.

1. Create a database
// Create a database
File. Delete (\ "Test. sdf \");
SqlCeEngine engine = new SqlCeEngine (
\ "Data Source = 'test. sdf '; LCID = 1033; Password =\\" s $; 2 '! DS64 \ "; Encrypt = TRUE ;\");
Engine. CreateDatabase ();

2. Verify and repair the database
// Verify and repair the database
SqlCeEngine engine = new SqlCeEngine (\ "Data Source = AdventureWorks. sdf \");
If (false = engine. Verify ())
{
MessageBox. Show (\ "Database is already upted .\");
Engine. Repair (null, retries roption. RecoverCorruptedRows );
}

3. compress the database
// Compress the database
// Create a database file from an existing file to recycle the wasted space in the SQL Server Mobile database.
// This method can also be used to change the database sorting order, encryption, or password settings.
// This connection string specifies a connection to the target database created by this method.
// If the specified database already exists or another file with the same name already exists, an exception is thrown.
// If an empty string is passed for the connection string, the new database file will replace the old database file,
// The name remains unchanged.
SqlCeEngine engine = new SqlCeEngine (\ "Data Source = AdventureWorks. sdf \");
// Engine. Compact (null );
Engine. Compact (\ "Data Source =; Password = a @ 3! 7f $ dQ ;\");

4. shrink the database
// Shrink the database
// Move the blank page to the end of the file and then truncate the file,
// Reclaim the space wasted in the SQL Server Mobile database.
// Unlike the Compact method, the Shrink method does not create temporary database files,
// Instead, all blank pages and unallocated pages are moved to the end of the file and truncated to reduce the total size of the database.
SqlCeEngine engine = new SqlCeEngine (\ "Data Source = AdventureWorks. sdf \");
Engine. Shrink ();

5. Merge and copy
// Merge and copy
// Instantiate and configure the SqlCeReplication object
SqlCeReplication repl = new SqlCeReplication ();
Repl. InternetUrl = \ "http://www.adventure-works.com/sqlmobile/sqlcesa30.dll \";
Repl. InternetLogin = \ "MyInternetLogin \";
Repl. InternetPassword = \ "<password> \";
Repl. Publisher = \ "MyPublisher \";
Repl. PublisherDatabase = \ "MyPublisherDatabase \";
Repl. PublisherLogin = \ "MyPublisherLogin \";
Repl. PublisherPassword = \ "<password> \";
Repl. Publication = \ "MyPublication \";
Repl. Subscriber = \ "MySubscriber \";
Repl. SubscriberConnectionString = \ "Data Source = MyDatabase. sdf \";

// Create a subscription for the local SQL Server Mobile Database
Repl. addsubject (AddOption. CreateDatabase); [Page]

// Synchronize with the SQL Server database
Repl. Synchronize ();

// Clear the repl object
Repl. Dispose ();

6. Remote Data Access (RDA)
// Remote Data Access
// Instantiate and configure the SqlCeRemoteDataAccess object
SqlCeRemoteDataAccess rda = new SqlCeRemoteDataAccess ();
Rda. InternetUrl = \ "http://www.adventure-works.com/sqlmobile/sqlcesa30.dll \";
Rda. InternetLogin = \ "MyInternetLogin \";
Rda. InternetPassword = \ "<password> \";
Rda. LocalConnectionString = \ "Data Source = MyDatabase. sdf \";

// Download data from SQL Server
Rda. Pull (
\ "Employees \",
\ "SELECT * FROM DimEmployee \",
\ "Provider = sqloledb; server = mysqlServer; database = AdventureWorks; uid = sa; pwd = ;\",
RdaTrackOption. TrackingOnWithIndexes,
\ "ErrorTable \");

//
// Modify local data
//

// Upload the modified data to SQL Server
Rda. Push (
\ "DimEmployee \",
\ "Provider = sqloledb; server = MySqlServer; database = AdventureWorks; uid = sa; pwd = ;\");

// Submit the SQL statement and execute it on SQL Server.
Rda. SubmitSql (
\ "Create table MyRemoteTable (colA int )\",
\ "Provider = sqloledb; server = MySqlServer; database = AdventureWorks; uid = sa; pwd = ;\");

7. Use SqlCeResultSet
// Use SqlCeResultSet
// Create an SQL Server Mobile Database Connection
SqlCeConnection conn = new SqlCeConnection (\ "Data Source = Northwind. sdf \");

// Create and configure the SqlCeCommand object
SqlCeCommand cmd = conn. CreateCommand ();
Cmd. CommandText = \ "SELECT * FROM Orders \";

// Create a SqlCeResultSet object and configure it to be scroll, updatable, and detect data source changes
ResultSetOptions options = ResultSetOptions. Scrollable

ResultSetOptions. Sensitive |
ResultSetOptions. Updatable; [Page]
SqlCeResultSet resultSet = cmd. ExecuteResultSet (options );

// Create a ResultSetView object and configure it to display only columns with the serial numbers
ResultSetView resultSetView = resultSet. ResultSetView;
Int [] ordinals = new int [] {1, 3, 5, 8 };
ResultSetView. Ordinals = ordinals;

// Bind the ResultSetView to the DataGrid Control
This. dataGrid. DataSource = resultSetView;

8. Handling SqlCeException
// Process SqlCeException
Public static void ShowErrors (SqlCeException e)
{
SqlCeErrorCollection errs = e. Errors;

StringBuilder bld = new StringBuilder ();
Exception inner = e. InnerException;

Foreach (SqlCeError err in errs)
{
// Identify the HRESULT value of the error type. These errors are not inherent in SQL Server CE.
Bld. Append (\ "\ r \ nError Code: \"). Append (err. HResult. ToString (\ "X \"));
// Text describing the error
Bld. Append (\ "\ r \ nMessage: \"). Append (err. Message );
// Obtain the local error code of SqlCeError
Bld. Append (\ "\ r \ nMinor Err.: \"). Append (err. NativeError );
// Generate the wrong provider name
Bld. Append (\ "\ r \ nSource: \"). Append (err. Source );

// Traverse the first three error parameters. SQL Server CE uses error parameters to provide other details about errors.
Foreach (int numPara in err. NumericErrorParameters)
{
// Parameters may exist in errors, but not all errors will return parameters.
// If no parameter is returned when an error occurs, the value of this array is 0.
If (numPara! = 0)
{[Page]
Bld. Append (\ "\ r \ nNum. Par.: \"). Append (numPara );
}
}

// Traverse the last three error parameters. SQL Server CE uses error parameters to provide other details about errors.
Foreach (string errPara in err. ErrorParameters)
{
// Parameters may exist in errors, but not all errors will return parameters.
// If no parameter is returned when an error occurs, the value of this array is a null string.
If (errPara! = String. Empty)
{
Bld. Append (\ "\ r \ nErr. Par.: \"). Append (errPara );
}
}
}

MessageBox. Show (bld. ToString ());
}