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 );
// 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;
Sqlceresultset resultset = cmd. executeresultset (options );
// Create a resultsetview object and configure it to display only columns with the serial numbers
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 errorcollection = 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)
{
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 ());
}