How to: Connect to SQL Azure

Sep 09, 2020
2 minutes to read

With XPO, connecting to SQL Azure is similar to connecting to SQL Server. Because SQL Azure Database represents a cloud-based database service built on Microsoft SQL Server technologies, the connection requires the same connection provider, MSSqlConnectionProvider, which was extended with support for SQL Azure. So, to connect to a SQL Azure database, you can use the same techniques described in the How to: Connect to a SQL Server topic. The only difference involves specifying a cloud-based SQL server instance as a data source.

Supplying a SQL Azure connection string

As with SQL Server, you can manually specify a SQL Azure connection string. To indicate that the MSSqlConnectionProvider needs to be used, add the “Initial Catalog” entry to the connection string, as shown below.

C#
VB.NET

csharp

XpoDefault.Session.ConnectionString = String.Format(
    "data source=tcp:{0}.database.windows.net;initial catalog={1};user id={2};password={3};", 
serverName, databaseName, userName, password);

XpoDefault.Session.ConnectionString = String.Format( _
"data source=tcp:{0}.database.windows.net;initial catalog={1};user id={2};password={3};" _
, serverName, databaseName, userName, password)

Using the GetConnectionString Function

As with SQL Server, you can use the MSSqlConnectionProvider.GetConnectionString overloaded function to create the default Data Access Layer (DAL).

C#
VB.NET

csharp

string sqlConn = 
    MSSqlConnectionProvider.GetConnectionString(@"tcp:MyServer.database.windows.net", "MyDatabase");
XpoDefault.DataLayer = XpoDefault.GetDataLayer(sqlConn, AutoCreateOption.DatabaseAndSchema);

Dim sqlConn = _
MSSqlConnectionProvider.GetConnectionString("tcp:MyServer.database.windows.net", "MyDatabase")
XpoDefault.DataLayer = XpoDefault.GetDataLayer(sqlConn, AutoCreateOption.DatabaseAndSchema)