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.