Connection Strings for SQL Server Compact Edition

Introduction

This article will teach about Connection Strings for SQL Server Compact Edition. 

SQL Server Compact Edition

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 supports transactions, referential integrity constraints, locking, and multiple connections to the database store. 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.

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

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

Encryption Enabled

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

Persist Security Info=False;

Exclusive but Shared for Reading

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

Exclusive Access

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

Read Only Access

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

Specification of Maximum Temp file Size

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

Specification of the location of the SDF File

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

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

Specification of Maximum Database Size

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

Specification of Maximum Buffer Size

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

.NET Framework Data Provider for OLE DB

.NET Framework data provider connects to a database, executes commands, and retrieves results. Those results are either processed directly, placed in a Dataset 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

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

Microsoft.SQLSERVER.CE.OLEDB.3.5

Standard Security

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

Microsoft.SQLSERVER.MOBILE.OLEDB.3.0

Standard Security

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

Encryption Enabled

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

Supplying the database password

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

Specification of Maximum Temp file Size

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

Specification of the location of the Temp File<

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

Specification of the location of the SDF File

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

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

Specification of Maximum Buffer Size

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

Conclusion

This article taught us about Connection Strings for SQL Server Compact Edition. 

Recommended Ebook

SQL Queries For Beginners

Download Now!
Similar Articles