C # operations Sqlce,sql Server Mobile database.

Organize a few sections of common C # code that operates on SQL Server Mobile databases for reference by a friend who has just contacted SQL Server Mobile development.

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. Verifying and repairing the database
Validating and Repairing databases
SqlCeEngine engine = new SqlCeEngine ("Data source=adventureworks.sdf");
if (false = = engine. Verify ())
{
MessageBox.Show ("Database is corrupted.");
Engine. Repair (null, repairoption.recovercorruptedrows);
}

3. Compressing the database
Compress database
Reclaim wasted space in the SQL Server Mobile database by creating a new database file from an existing file.
This method can also be used to change the sort order, encryption, or password settings for a database.
The connection string specifies a connection to the target database that will be created by this method.
If the specified database already exists or another file with the same name already exists, an exception is thrown.
If you pass an empty string for the connection string, the new database file overwrites the old database file.
But the name remains the same.
SqlCeEngine engine = new SqlCeEngine ("Data source=adventureworks.sdf");
Engine.compact (NULL);
Engine.compact ("Data source=; [email PROTECTED]!7F$DQ;];

4. Shrinking the Database

Shrinking a Database
By moving the empty page to the end of the file and then truncating the file,
To reclaim wasted space in the SQL Server Mobile database.
Unlike the Compact method, the Shrink method does not create a temporary database file.
Instead, all empty pages and unallocated pages are moved to the end of the file, and then truncated, reducing the total size of the database.
SqlCeEngine engine = new SqlCeEngine ("Data source=adventureworks.sdf");
Engine. Shrink ();

5. Merge replication
Merge replication
Instantiating and configuring SqlCeReplication objects
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 to a local SQL Server Mobile database
Repl. AddSubscription (addoption.createdatabase);

Synchronizing with a SQL Server database
Repl. Synchronize ();

Cleaning up Repl objects
Repl. Dispose ();

6. Remote data access (RDA)
Remote Data access
Instantiating and configuring SqlCeRemoteDataAccess objects
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");

//
modifying local data
//

Uploading modified data to SQL Server
Rda. Push (
"DimEmployee",
"provider=sqloledb;server=mysqlserver;database=adventureworks;uid=sa;pwd=;");

The commit SQL statement executes on SQL Server
Rda. SubmitSQL (
"CREATE TABLE myremotetable (colA int)",
"provider=sqloledb;server=mysqlserver;database=adventureworks;uid=sa;pwd=;");

7. Using SqlCeResultSet
Using SqlCeResultSet
Create a SQL Server Mobile database connection
SqlCeConnection conn = new SqlCeConnection ("Data source=northwind.sdf");

Creating and configuring SqlCeCommand objects
SqlCeCommand cmd = conn. CreateCommand ();
Cmd.commandtext = "SELECT * from Orders";

Create a SqlCeResultSet object and configure it to be scrollable, updatable, detect data source changes
ResultSetOptions options = resultsetoptions.scrollable |
resultsetoptions.sensitive |
resultsetoptions.updatable;
SqlCeResultSet resultSet = cmd. Executeresultset (options);

Create a Resultsetview object that is configured to display only columns with the ordinal 1,3,5,8
Resultsetview Resultsetview = Resultset.resultsetview;
int[] ordinals = new int[] {1,3,5,8};
Resultsetview.ordinals = ordinals;

Bind Resultsetview to the DataGrid control
This.dataGrid.DataSource = Resultsetview;

8. Handling SqlCeException
Handling SqlCeException
public static void ShowErrors (SqlCeException e)
{
SqlCeErrorCollection errs = e.errors;

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

foreach (SqlCeError err in errs)
{
An HRESULT value that identifies the type of error that is not SQL Server CE-fixed
Bld. Append ("\r\nerror Code:"). Append (Err. Hresult.tostring ("X"));
The text that describes the error
Bld. Append ("\r\nmessage:"). Append (Err. Message);
Get the native error number for SqlCeError
Bld. Append ("\r\nminor Err.:"). Append (Err. NativeError);
The name of the provider that generated the error
Bld. Append ("\r\nsource:"). Append (Err. Source);

Traverse the first three error parameters. SQL Server CE uses error parameters to provide additional details about the error.
foreach (int numpara in err.) Numericerrorparameters)
{
Although an error may exist for a parameter, not all errors that occur are returned as parameters.
If no arguments are returned when an error occurs, the value of the array is 0.
if (Numpara! = 0)
{
Bld. Append ("\r\nnum. Par.: "). Append (Numpara);
}
}

Traverse the last three error parameters. SQL Server CE uses error parameters to provide additional details about the error.
foreach (String Errpara in Err. Errorparameters)
{
Although an error may exist for a parameter, not all errors that occur are returned as parameters.
If no arguments are returned when an error occurs, the value of the array will be an empty string.
if (Errpara! = String.Empty)
{
Bld. Append ("\r\nerr. Par.: "). Append (Errpara);
}
}
}

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

Resources:

Http://msdn.microsoft.com/zh-cn/library/ms173053.aspx

Http://msdn.microsoft.com/zh-cn/library/13kw2t64 (v=vs.90). aspx---basic introduction

C # operations Sqlce,sql Server Mobile database.