Common C # code to manipulate SQL Server Mobile 2005 databases

server|sql| Data | database

Organize a few paragraphs of common C # code to operate a SQL Server Mobile database for reference by friends who have just come in contact with SQL Server Mobile development.

1. Create a database
Creating 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. Validating and Repairing databases
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
Compressing a 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 unchanged.
SqlCeEngine engine = new SqlCeEngine ("Data source=adventureworks.sdf");
Engine.compact (NULL);
Engine.compact ("Data source=; PASSWORD=A@3!7F$DQ; ");

4. Shrinking the Database

Shrink Database
By moving an 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 and unassigned pages are moved to the end of the file and then truncated to reduce the total size of the database.
SqlCeEngine engine = new SqlCeEngine ("Data source=adventureworks.sdf");
Engine. Shrink ();

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

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

Clean up REPL objects
Repl. Dispose ();

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

Downloading 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
//

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

Committing SQL statements to execute on SQL Server
Rda. SubmitSQL (
"CREATE TABLE myremotetable (cola int)",
"provider=sqloledb;server=mysqlserver;database=adventureworks;uid=sa;pwd=;");

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

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

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

Create a Resultsetview object, configured to display only columns with ordinal number 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 errorcollection = e.errors;

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

    foreach (SqlCeError err in errs)
    {
        //Identifies the HRESULT values of the error type, which are not intrinsic to SQL Server CE,
        bld. Append ("\r\nerror Code:"). Append (Err. Hresult.tostring ("X"));
       //Text describing the error
        bld. Append ("\r\nmessage:"). Append (Err. message);
       //Get SqlCeError's native error number
         BLD. Append ("\r\nminor Err.:"). Append (Err. NativeError);
       //Generate the name of the wrong provider
        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 parameters may exist for errors, not all errors that occur are returned by 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);
           }
       }

Iterate through 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 the error may have parameters, not all errors that occur are returned by parameters.
If no arguments are returned when an error occurs, the value of the array is an empty string.
if (Errpara!= String.Empty)
{
Bld. Append ("\r\nerr.") Par.: "). Append (Errpara);
}
}
}

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

Reference:
SQL Server Mobile 2005 Books Online
MSDN Library