CreateDatabase Method (SQL Server Compact)

  • Article

The CreateDatabase method creates a new Microsoft SQL Server Compact 4.0 database.

Syntax

object.CreateDatabase(LocalConnection)

Parameters

Parameter

Description

LocalConnection

A string value specifying the path and file name of the database to be created.

Prototype

HRESULT CreateDatabase(BSTR LocalConnection);

Example

The first example demonstrates how to create, encrypt, upgrade, compact, and repair a Microsoft SQL Server Compact 4.0 case-insensitive database. The second example demonstrates how to create, compact, and repair a Microsoft SQL Server Compact 4.0 database with a case-sensitive collation. For more information about case-sensitive SQL Server Compact databases, see Working with Collations (SQL Server Compact).

// The first example:
// ClientAgent.cpp : Defines the entry point for the console application.
//

#include "stdafx.h"

// Include OS Header files
//

#include <stdio.h>
#include <conio.h>
#include <windows.h>
#include<iostream>
// Include OS Header files
//
#include <sqlce_err.h>
#include <sqlce_oledb.h>
#include <sqlce_sync.h>

#define ExitOnFail(hr)              { if (FAILED(hr)) goto Exit; }
#define ExitOnNull(ptr)             { if (NULL == (ptr)) goto Exit; }
#define ExitOnFailOrNull(hr, ptr)   { ExitOnFail(hr); ExitOnNull(ptr); }
#define ExitOnAnyFailure(hr, pErr)  { ExitOnFail(hr); if (FAILED(pErr->GetMajor())) goto Exit; }
#define SAFERELEASE(ptr)            { if (NULL != ptr) ptr->Release(); ptr = NULL; }
#define DELNULL(ptr)                { if (NULL != ptr) delete ptr; ptr = NULL; }

#define WAIT_FOR_KEY_OUT(hr) {\
    printf("\n\nHRESULT = %lu (0x%08x)\n", hr, hr);\
    printf("Press ENTER to continue ...\n");\
    getchar();\
}

#define WAIT_FOR_KEY() {\
    printf("\nPress ENTER to continue ...\n");\
    getchar();\
}

//Files

BSTR connStrCreate = NULL;

BSTR connStrSrc = NULL;
BSTR connStrDst = NULL;

BSTR connStrCompactin = NULL;
BSTR connStrCompactout = NULL; 

BSTR connStrRepairedin = NULL;
BSTR connStrRepairedout = NULL;

HRESULT GetInterfacePointers(
    ISSCEEngine   **ppEngine, 
    ISSCEError    **ppErr = NULL
)
{
    HRESULT hr = NOERROR;

    if (NULL != ppEngine)
    {
        *ppEngine = (ISSCEEngine *) NULL; 

        hr = CoCreateInstance(CLSID_Engine, NULL, 
            CLSCTX_INPROC_SERVER, IID_ISSCEEngine, (LPVOID *) ppEngine);

        ExitOnFailOrNull(hr, *ppEngine);
    }

    if (NULL != ppErr)
    {
        *ppErr = (ISSCEError *) NULL;

        hr = CoCreateInstance(CLSID_SSCEError, NULL, 
            CLSCTX_INPROC_SERVER, IID_ISSCEError, (LPVOID *) ppErr);

        ExitOnFailOrNull(hr, *ppErr);
    }

Exit:
    return hr;
}


int _tmain(int argc, _TCHAR* argv[])
{
    ISSCEEngine  *pEngine = NULL;
    ISSCEError   *pError = NULL;

    HRESULT hr = NOERROR;
BSTR connStrCreate = SysAllocString((const OLECHAR*)L"Data Source=C:\\created35.sdf; SSCE:Database Password='123'; ");

BSTR connStrSrc = SysAllocString((const OLECHAR*)L"Data Source=C:\\encrypted31.sdf; SSCE:Database Password='123' ;");
BSTR connStrDst = SysAllocString((const OLECHAR*)L"Data Source=C:\\31upgradedto35.sdf; SSCE:Database Password='123' ; SSCE:Database Password='123' ; ssce:encryption mode=' platform default';");

BSTR connStrCompactin = SysAllocString((const OLECHAR*)L"Data Source=C:\\encryptedC35.sdf; SSCE:Database Password='encrypt' ; ");
BSTR connStrCompactout = SysAllocString((const OLECHAR*)L"Data Source='C:\\35compacted.sdf'; SSCE:Database Password='123' ; SSCE:Database Password='123' ; ssce:encryption mode= ’platform default’;"); 

BSTR connStrRepairedin = SysAllocString((const OLECHAR*)L"Data Source=C:\\encryptedR35.sdf; SSCE:Database Password='encrypt'");
BSTR connStrRepairedout = SysAllocString((const OLECHAR*)L"Data Source=C:\\35repaired.sdf;  SSCE:Database Password='123' ; ssce:encryption mode=' platform default'; ");

//DeleteFile(L"c:\\created35.sdf");
//DeleteFile(L"c:\\encrypted31.sdf");
//DeleteFile(L"c:\\31upgradedto35.sdf");
//DeleteFile(L"c:\\encryptedC35.sdf");
//DeleteFile(L"c:\\35compacted.sdf");
//DeleteFile(L"c:\\encryptedR35.sdf");
//DeleteFile(L"c:\\35repaired.sdf");

    CoInitialize(NULL);

    hr = GetInterfacePointers(&pEngine, &pError);
ExitOnFail(hr);

// test create
//
hr = NOERROR;
hr=pEngine->CreateDatabase(connStrCreate);
printf("\n for create error=%lu",hr);

// test upgrade
// NOTE: you need a older ssce database for this - this wont work otherwise.
//
hr = NOERROR;
    hr = pEngine->UpgradeDatabase(connStrSrc, connStrDst);
printf("\n for upgrade error=%lu",hr);
    
// test compact
//
hr = NOERROR;
hr=pEngine->CreateDatabase(connStrCompactin);
printf("\n for create error=%lu",hr);

hr = NOERROR;
hr=pEngine->CompactDatabase(connStrCompactin,connStrCompactout);
printf("\n for compact error=%lu",hr);

// test repair
//
hr = NOERROR;
hr=pEngine->CreateDatabase(connStrRepairedin);
printf("\n for create error=%lu",hr);

hr = NOERROR;
hr=pEngine->Repair(connStrRepairedin,connStrRepairedout,RECOVERCORRUPTED);
printf("\n for repair error=%lu",hr);
    hr = NOERROR;

Exit:
    SysFreeString(connStrCreate);
    SysFreeString(connStrSrc);
    SysFreeString(connStrDst);
    SysFreeString(connStrCompactin);
    SysFreeString(connStrCompactout);
    SysFreeString(connStrRepairedin);
    SysFreeString(connStrRepairedout);
    CoUninitialize();
    WAIT_FOR_KEY_OUT(hr);
    return 0;
}

The following example demonstrates how to create a case-sensitive Microsoft SQL Server Compact 4.0 database, how to use the CompactDatabase Method (SQL Server Compact) to change the case sensitivity setting of the database and, how to create a repaired database with a case-sensitive collation. For more information about case-sensitive SQL Server Compact databases, see Working with Collations (SQL Server Compact).

// The second example:
// ClientAgent2.cpp : Defines the entry point for the console application.
//

#include "stdafx.h"

// Include OS Header files
//

#include <stdio.h>
#include <conio.h>
#include <windows.h>
#include<iostream>
// Include OS Header files
//
#include <sqlce_err.h>
#include <sqlce_oledb.h>
#include <sqlce_sync.h>

#define ExitOnFail(hr)              { if (FAILED(hr)) goto Exit; }
#define ExitOnNull(ptr)             { if (NULL == (ptr)) goto Exit; }
#define ExitOnFailOrNull(hr, ptr)   { ExitOnFail(hr); ExitOnNull(ptr); }
#define ExitOnAnyFailure(hr, pErr)  { ExitOnFail(hr); if (FAILED(pErr->GetMajor())) goto Exit; }
#define SAFERELEASE(ptr)            { if (NULL != ptr) ptr->Release(); ptr = NULL; }
#define DELNULL(ptr)                { if (NULL != ptr) delete ptr; ptr = NULL; }

#define WAIT_FOR_KEY_OUT(hr) {\
    printf("\n\nHRESULT = %lu (0x%08x)\n", hr, hr);\
    printf("Press ENTER to continue ...\n");\
    getchar();\
}

#define WAIT_FOR_KEY() {\
    printf("\nPress ENTER to continue ...\n");\
    getchar();\
}

//Files

BSTR connStrCreate = NULL;

BSTR connStrSrc = NULL;

BSTR connStrCompactin = NULL;
BSTR connStrCompactout = NULL; 

BSTR connStrRepairedin = NULL;
BSTR connStrRepairedout = NULL;

HRESULT GetInterfacePointers(
    ISSCEEngine   **ppEngine, 
    ISSCEError    **ppErr = NULL
)
{
    HRESULT hr = NOERROR;

    if (NULL != ppEngine)
    {
        *ppEngine = (ISSCEEngine *) NULL; 

        hr = CoCreateInstance(CLSID_Engine, NULL, 
            CLSCTX_INPROC_SERVER, IID_ISSCEEngine, (LPVOID *) ppEngine);

        ExitOnFailOrNull(hr, *ppEngine);
    }

    if (NULL != ppErr)
    {
        *ppErr = (ISSCEError *) NULL;

        hr = CoCreateInstance(CLSID_SSCEError, NULL, 
            CLSCTX_INPROC_SERVER, IID_ISSCEError, (LPVOID *) ppErr);

        ExitOnFailOrNull(hr, *ppErr);
    }

Exit:
    return hr;
}


int _tmain(int argc, _TCHAR* argv[])
{
    ISSCEEngine  *pEngine = NULL;
    ISSCEError   *pError = NULL;

    HRESULT hr = NOERROR;

BSTR connStrCreate = SysAllocString((const OLECHAR*)L"Data source=C:\\created35.sdf; SSCE:case sensitive=true; SSCE:Database Password='123'");

BSTR connStrCompactin = SysAllocString((const OLECHAR*)L"Data Source=C:\\compactIn35.sdf; SSCE:Database Password='123'");
BSTR connStrCompactout = SysAllocString((const OLECHAR*)L"Data Source='C:\\compactOut35.sdf'; SSCE:Database Password='123'; SSCE:case sensitive=true;"); 

BSTR connStrRepairedin = SysAllocString((const OLECHAR*)L"Data Source=C:\\repairIn35.sdf; SSCE:Database Password='123';");
BSTR connStrRepairedout = SysAllocString((const OLECHAR*)L"Data Source=C:\\repairOut35.sdf; SSCE:Database Password='123' ; SSCE:case sensitive=true;");

//DeleteFile(L"c:\\created35.sdf");
//DeleteFile(L"c:\\compactIn35.sdf");
//DeleteFile(L"c:\\compactOut35.sdf");
//DeleteFile(L"c:\\repairIn35.sdf");
//DeleteFile(L"c:\\repairOut35.sdf");

    CoInitialize(NULL);

    hr = GetInterfacePointers(&pEngine, &pError);
ExitOnFail(hr);

// test create
//
hr = NOERROR;
hr=pEngine->CreateDatabase(connStrCreate);
printf("\n for create error=%lu",hr);

// test compact
//
hr = NOERROR;
hr=pEngine->CreateDatabase(connStrCompactin);
printf("\n for create error=%lu",hr);

hr = NOERROR;
hr=pEngine->CompactDatabase(connStrCompactin,connStrCompactout);
printf("\n for compact error=%lu",hr);

// test repair
//
hr = NOERROR;
hr=pEngine->CreateDatabase(connStrRepairedin);
printf("\n for create error=%lu",hr);

hr = NOERROR;
hr=pEngine->Repair(connStrRepairedin,connStrRepairedout,RECOVERCORRUPTED);
printf("\n for repair error=%lu",hr);
    hr = NOERROR;

Exit:
    SysFreeString(connStrCreate);
    SysFreeString(connStrSrc);
    SysFreeString(connStrCompactin);
    SysFreeString(connStrCompactout);
    SysFreeString(connStrRepairedin);
    SysFreeString(connStrRepairedout);
    CoUninitialize();
    WAIT_FOR_KEY_OUT(hr);
    return 0;
}

Remarks

Important

Support for case sensitive collations is introduced starting with the SQL Server Compact 3.5 Service Pack 1 (SP1) release. For more information, see Working with Collations (SQL Server Compact).

The following table lists the connection properties supported in SQL Server Compact 4.0. 

Property

Description

data source

File path and name of the SQL Server Compact 4.0 database. You should specify the absolute path of the database file, instead of a relative path.

Note   An application that is running on a desktop computer cannot open a database connection to a .sdf file on a device. Similarly, an application that is running on a device cannot open a database connection to a .sdf file on a desktop computer.

ssce: case sensitive

A Boolean value that determines whether the database is case sensitive. Must be set to true to enable case sensitivity or false for case insensitivity. If not specified, the default value is false.

NoteNote
This property is introduced starting with the SQL Server Compact 4.0 SP1 release. For more information, see Working with Collations (SQL Server Compact).

ssce: database password

The database password, which can be up to 40 characters in length. If not specified, the default value is no password. This property is required if you enable encryption on the database. If you specify a password, encryption is enabled on the database.

ssce:encryption mode

or

encryption mode

The values for this property are

engine default

or

platform default

ssce: max buffer size

The largest amount of memory, in KB, that SQL Server Compact 4.0 can use before it starts flushing changes to disk. If not specified, the default value is 640.

ssce: max database size

The maximum size of the database, in MB. If not specified, the default value is 128.

ssce:mode

The mode to use when you open the database file. For valid values, see the table that follows. If not specified, the default value is Read/Write.

ssce: default lock timeout

The default number of milliseconds that a transaction will wait for a lock. If not specified, the default value is 5000 milliseconds for desktops and 2000 milliseconds for devices.

ssce: default lock escalation

The number of locks a transaction will acquire before you try escalation from row to page, or from page to table. If not specified, the default value is 100.

ssce: flush interval

Specifies the interval time in seconds before all committed transactions are flushed to disk. If not specified, the default value is 10.

ssce: autoshrink threshold

The percent of free space in the database file that is permitted before autoshrink begins. A value of 100 disables autoshrink. If not specified, the default value is 60.

ssce: temp file directory

The location of the temporary database. If not specified, the default is to use the database specified in the data source property for temporary storage.

ssce: temp file max size

The maximum size of the temporary database file, in Megabytes. If not specified, the default value is 128.

locale identifier

The locale ID (LCID) to use with the database.

The following table shows valid values for the mode property of the connection string.

Value

Definition

Read Write

Lets multiple processes open and modify the database. This is the default setting if the mode property is not specified.

Read Only

Lets you open a read-only copy of the database.

Exclusive

Does not let other processes to open or modify the database.

Shared Read

Lets other processes read, but not modify, the database while you have it open.

The following rules apply to connection strings:

  1. All blank characters, except those within a value or within quotation marks, are ignored.

  2. Keyword value pairs must be separated by a semicolon (;). If a semicolon is part of a value, it also must be delimited by quotation marks.

  3. No escape sequences are supported.

  4. The value type is irrelevant.

  5. Names are not case-sensitive.

  6. If a property name occurs more than one time in the connection string, the value associated with the last occurrence is used.

  7. Values can be delimited by single or double quotation marks, for example, name='value' or name="value". Either single or double quotation marks can be used within a connection string by using the other delimiter. For example, the following are valid uses of quotation marks within a connection string:

    "data source='MyDb.sdf'; ssce: mode=Exclusive;"
'data source="MyDb.sdf"; ssce: mode=Exclusive;'
    • But these examples are not valid:
    "data source="MyDb.sdf"; ssce: mode=Exclusive;"
'data source='MyDb.sdf'; ssce: mode=Exclusive;'

See Also

Other Resources

Engine Object Programming (SQL Server Compact)