Reader Level:
ARTICLE

Connection Strings for SQL Server Compact Edition

Posted by Nipun Tomar Articles | SQL December 22, 2010
SQL Server Compact Edition (SQL CE) is a compact relational database produced by Microsoft for applications that run on mobile devices and desktops.
  • 0
  • 0
  • 41854

SQL Server Compact Edition (SQL CE) is a compact relational database produced by Microsoft for applications that run on mobile devices and desktops. SQL CE targets occasionally-connected applications and applications with an embedded database It includes ADO.NET providers for data access using ADO.NET APIs, and built-in synchronization capabilities, as well as support for LINQ and Entity Framework. SQL CE supports transactions, referential integrity constraints, locking as well as multiple connections to the database store.

Providers for SQL Server Compact Edition9

  • .NET Compact Framework Data Provider for SQL Server Mobile (SqlCeConnection)
  • .NET Framework Data Provider for OLE DB (OleDbConnection)
  • Microsoft.SQLSERVER.CE.OLEDB.3.5
  • Microsoft.SQLSERVER.MOBILE.OLEDB.3.0

.NET Compact Framework Data Provider for SQL Server Mobile

.NET Compact Framework Data Provider for SQL Server Mobile is used to develop device applications by using the System.Data.SqlClient (namespace of the .NET Compact Framework Data Provider for SQL Server). System.Data.SqlClient in the .NET Compact Framework is a collection of classes that can be used to access SQL Server databases with managed code from Windows CE .NET-based devices.

Sample Code

using System.Data.SqlServerCe;
SqlCeConnection myConnection = new SqlCeConnection();
myConnection.ConnectionString = myConnectionString;
myConnection.Open();
//execute queries
myConnection.Close();

Standard Security

Syntax:

Data Source=urData.sdf;Persist Security Info=False;

Encryption Enabled

Syntax:

Data Source=urData.sdf;Encrypt Database=True;Password=urPassword;File Mode=shared read;

Persist Security Info=False;

Exclusive but Shared for Reading

Syntax:

Data Source=urData.sdf;File Mode=Shared Read;Persist Security Info=False;

Exclusive Access

Syntax:

Data Source=urData.sdf;File Mode=Exclusive;Persist Security Info=False;

Read Only Access

Syntax:

Data Source=urData.sdf;File Mode=Read Only;Persist Security Info=False;

Specification of Maximum Temp file Size

Syntax:

Data Source=urData.sdf;Temp File Max Size=345;Persist Security Info=False;

Specification of location of SDF File

Syntax:

Data Source=" +(System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAsse

mbly().GetName().CodeBase) + "\\urData.sdf;Persist Security Info=False;

Specification of Maximum Database Size

Syntax:

Data Source=uyData.sdf;Max Database Size=345;Persist Security Info=False;

Specification of Maximum Buffer Size

Syntax:

Data Source=urData.sdf;Max Buffer Size=1024;Persist Security Info=False;

.NET Framework Data Provider for OLE DB

.NET Framework data provider is used for connecting to a database, executing commands, and retrieving results. Those results are either processed directly, placed in a Dataset in order to be exposed to the user as needed, combined with data from multiple sources, or remoted between tiers. .NET Framework data providers are lightweight, creating a minimal layer between the data source and code, increasing performance without sacrificing functionality.

Sample Code

using System.Data.OleDb;
OleDbConnection myConnection = new OleDbConnection();
myConnection.ConnectionString = myConnectionString;
myConnection.Open();
//execute queries
myConnection.Close();

Bridging to Microsoft.SQLSERVER.CE.OLEDB.3.5

Syntax:

Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=urPath\urData.sdf;

Microsoft.SQLSERVER.CE.OLEDB.3.5

Standard Security

Syntax:

Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=urPath\urData.sdf;

Microsoft.SQLSERVER.MOBILE.OLEDB.3.0

Standard Security

Syntax:

Provider=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Data Source=urPath\urData.sdf;

Encryption Enabled

Syntax:

Provider=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Data Source=urPath\urData.sdf;SSCE:Encrypt Database=True;

Supplying the database password

Syntax:

Provider=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Data Source=urPath\urData.sdf;SSCE:Database Password='urPassword';

Specification of Maximum Temp file Size

Syntax:

Provider=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Data Source=urPath\urData.sdf;SSCE:Temp File Max Size=345;

Specification of location of Temp File

Syntax:

Provider=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Data Source=urPath\urData.sdf;SSCE:Temp File Directory="\urTempDir\";

Specification of location of SDF File

Syntax:

Provider=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Data Source="+(System.IO.Path.

GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase) + "\\urData.sdf;

Specification of Maximum Database Size

Syntax:

Provider=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Data Source=urPath\urData.sdf;SSCE:Max Database Size=345;

Specification of Maximum Buffer Size

Syntax:

Provider=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Data Source=urPath\urData.sdf;SSCE:Max Buffer Size=1024;

COMMENT USING

Trending up