ADO.NET Provider

dotConnect for Skyvia Connect, an ADO.NET provider for SQL endpoints, offers the standard ADO.NET interface to access data of your SQL endpoints. ADO.NET is Microsoft’s data access technology for .NET Framework, very widely used in .NET projects that easily integrates with other .NET data related technologies and solutions. You can use dotConnect for Skyvia Connect to create software that works with data, published via your SQL endpoints.

dotConnect for Skyvia Connect includes standard ADO.NET classes:

• SkyviaConnectConnection
• SkyviaConnectConnectionStringBuilder
• SkyviaConnectCommand
• SkyviaConnectCommandBuilder
• SkyviaConnectParameter
• SkyviaConnectParameterCollection
• SkyviaConnectDataReader
• SkyviaConnectDataAdapter
• SkyviaConnectProviderFactory

The provider requires .NET Framework 4.5 or higher installed and is compatible with all Windows platforms (Windows Vista or higher) that support .NET Framework 4.5. It can be used with any .NET Framework development environment that supports .NET Framework 4.5 or higher.

To use dotConnect for Skyvia Connect in your projects, you can either download dotConnect for Skyvia Connect installer from your SQL endpoint details or get it as a NuGet package from NuGet. For example, in Visual Studio, you can execute the Install-Package Devart.Data.SkyviaConnect command in NuGet Package Manager Console to add it to your project.

Adding References to Projects

If you are going to install our provider via the installer, you will need to add the following assemblies to the references of your projects, using dotConnect for Skyvia Connect:

• Devart.Data.dll
• Devart.Data.SqlShim.dll
• Devart.Data.SkyviaConnect.dll

You can find these assemblies in the folder where the provider is installed or in the GAC. By default, it’s _%ProgramFiles(x86)%\Devart\dotConnect\SkyviaConnect_

Connecting to SQL Endpoint

Connecting to an SQL endpoint is easy, you only need to provide the following connection string parameters:

• Endpoint URL - The URL of an SQL endpoint created on Skyvia. You may copy it from the Overview tab of endpoint details.
• User - A user ID to connect to a secure SQL endpoint. Required if you added user accounts for your endpoint.
• Password - A password to connect to a secure SQL endpoint. Required if you added user accounts for your endpoint.

To connect to an SQL endpoint, you need to create an instance of Devart.Data.SkyviaConnect.SkyviaConnectConnection class and pass the required connection string to it. You can either assign the whole connection string to the ConnectionString property of SkyviaConnectConnection or pass it to SkyviaConnectConnection constructor. You can also construct connection string from separate connection parameter values, using the Devart.Data.SkyviaConnect.SkyviaConnectConnectionStringBuilder class.

C#

1
2
3
4
5
6
7
SkyviaConnectConnectionStringBuilder connectionStringBuilder = new SkyviaConnectConnectionStringBuilder();

connectionStringBuilder.EndpointUrl = "https://connect.skyvia.com/4v6en3d0";
connectionStringBuilder.User = "TestUser";
connectionStringBuilder.Password = "TestPassword";

SkyviaConnectConnection connection = new SkyviaConnectConnection(connectionStringBuilder.ConnectionString);

Visual Basic

1
2
3
4
5
6
7
Dim connectionStringBuilder As SkyviaConnectConnectionStringBuilder = New SkyviaConnectConnectionStringBuilder()

connectionStringBuilder.EndpointUrl = "https://connect.skyvia.com/4v6en3d0"
connectionStringBuilder.User = "TestUser"
connectionStringBuilder.Password = "TestPassword"

Dim connection As SkyviaConnectConnection = New SkyviaConnectConnection(connectionStringBuilder.ConnectionString)

You may also set additional, not required parameters in the connection string to tweak the provider behavior. You can find the list of supported parameters and their descriptions below.

Retrieving Data

ADO.NET data providers serve as a bridge between an application and a data source, and allow you to execute commands as well as to retrieve data by using a DataReader or a DataAdapter. Updating data involves using Command and DataAdapter objects.

To retrieve or update data in database endpoints, use the SQL syntax of the source database. For cloud applications, use SQLite SQL syntax.

In the sample we will use SkyviaConnectCommand and SkyviaConnectDataReader to retrieve data. SkyviaConnectDataReader allows retrieving data in pages. While you read data from it, it automatically queries the next pages from an endpoint. SkyviaConnectDataReader offers higher performance than SkyviaConnectDataAdapter, especially when you query a lot of data.

This and other samples use an SQL endpoint that publishes data from Microsoft’s Northwind sample SQL Server database.

C#

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
using Devart.Data.SkyviaConnect;
...
class Program
{
    static void Main(string[] args) {

        const string connectionString = "Endpoint Url=https://connect.skyvia.com/4v6en3d0;User=testuser;Password=testpassword";
        const string sql = "SELECT ContactName, Phone FROM dbo.Customers";

        using (SkyviaConnectConnection connection = new SkyviaConnectConnection(connectionString)) {

            connection.Open();
            using (SkyviaConnectCommand command = connection.CreateCommand()) {

                command.CommandText = sql;
                using (SkyviaConnectDataReader reader = command.ExecuteReader()) {

                    while (reader.Read()) {

                        Console.WriteLine("{0}\t{1}", reader.GetValue(0), reader.GetValue(1));
                    }
                }
            }
        }
        Console.ReadKey();
    }

}

Visual Basic

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
Imports Devart.Data.SkyviaConnect
...
Class Program
    Private Shared Sub Main(ByVal args As String())
        Const connectionString As String = "Endpoint Url=https://connect.skyvia.com/4v6en3d0;User=testuser;Password=testpassword"
        Const sql As String = "SELECT ContactName, Phone FROM dbo.Customers"

        Using connection As SkyviaConnectConnection = New SkyviaConnectConnection(connectionString)
            connection.Open()

            Using command As SkyviaConnectCommand = connection.CreateCommand()
                command.CommandText = sql

                Using reader As SkyviaConnectDataReader = command.ExecuteReader()

                    While reader.Read()
                        Console.WriteLine("{0}" & vbTab & "{1}", reader.GetValue(0), reader.GetValue(1))
                    End While
                End Using
            End Using
        End Using

        Console.ReadKey()
    End Sub
End Class

Updating Data

You can update the endpoint data either by modifying data returned by the SkyviaConnectDataAdapter class and then calling its Update method or by performing corresponding DML statements (INSERT, DELETE, UPDATE) via SkyviaConnectCommand.

Here is an example showing how to update endpoint data using SkyviaConnectDataAdapter.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
using System.Data;
using Devart.Data.SkyviaConnect;
...
class Program
{

    static void Main(string[] args) {

        const string connectionString = "Endpoint Url=https://connect.skyvia.com/4v6en3d0;User=testuser;Password=testpassword";
        const string sql = "SELECT CategoryId, CategoryName, Description FROM dbo.Categories";

        using (SkyviaConnectConnection connection = new SkyviaConnectConnection(connectionString))
            {

                connection.Open();
                DataTable table = new DataTable("dbo.Categories");

                using (SkyviaConnectCommand command = connection.CreateCommand())
                {

                    command.CommandText = sql;
                    using (SkyviaConnectDataAdapter adapter = new SkyviaConnectDataAdapter(command))
                    {

                        adapter.Fill(table);

                        adapter.UpdateCommand = new SkyviaConnectCommand("UPDATE dbo.Categories SET CategoryName = @name, Description = @description WHERE CategoryId = @id", connection);

                        adapter.UpdateCommand.Parameters.Add("id", DbType.Int32).SourceColumn = "CategoryId";
                        adapter.UpdateCommand.Parameters["id"].SourceVersion = DataRowVersion.Original;
                        adapter.UpdateCommand.Parameters.Add("name", DbType.String).SourceColumn = "CategoryName";
                        adapter.UpdateCommand.Parameters.Add("description", DbType.String).SourceColumn = "Description";

                        DataRow firstrow = table.Rows[0];
                        firstrow["CategoryName"] = "sample name 1";
                        firstrow["Description"] = "sample description";
                        Console.WriteLine(adapter.Update(table));
                    }
                }
                Console.WriteLine("Rows after update.");

                foreach (DataRow row in table.Rows)
                {
                    Console.WriteLine("{0}\t{1}\t{2}", row[0], row[1], row[2]);
                }
            }
            Console.ReadKey();
    }

}

Visual Basic

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
Imports System.Data
Imports Devart.Data.SkyviaConnect
...
Class Program
    Private Shared Sub Main(ByVal args As String())
        Const connectionString As String = "Endpoint Url=https://connect.skyvia.com/4v6en3d0;User=testuser;Password=testpassword"
        Const sql As String = "SELECT CategoryId, CategoryName, Description FROM dbo.Categories"

        Using connection As SkyviaConnectConnection = New SkyviaConnectConnection(connectionString)
            connection.Open()
            Dim table As DataTable = New DataTable("dbo.Categories")

            Using command As SkyviaConnectCommand = connection.CreateCommand()
                command.CommandText = sql

                Using adapter As SkyviaConnectDataAdapter = New SkyviaConnectDataAdapter(command)
                    adapter.Fill(table)
                    adapter.UpdateCommand = New SkyviaConnectCommand("UPDATE dbo.Categories SET CategoryName = @name, Description = @description WHERE CategoryId = @id", connection)
                    adapter.UpdateCommand.Parameters.Add("id", DbType.Int32).SourceColumn = "CategoryId"
                    adapter.UpdateCommand.Parameters("id").SourceVersion = DataRowVersion.Original
                    adapter.UpdateCommand.Parameters.Add("name", DbType.String).SourceColumn = "CategoryName"
                    adapter.UpdateCommand.Parameters.Add("description", DbType.String).SourceColumn = "Description"
                    Dim firstrow As DataRow = table.Rows(0)
                    firstrow("CategoryName") = "sample name 1"
                    firstrow("Description") = "sample description"
                    Console.WriteLine(adapter.Update(table))
                End Using
            End Using

            Console.WriteLine("Rows after update.")

            For Each row As DataRow In table.Rows
                Console.WriteLine("{0}" & vbTab & "{1}" & vbTab & "{2}", row(0), row(1), row(2))
            Next
        End Using

        Console.ReadKey()
    End Sub
End Class

The following example updates the endpoint data using SkyviaConnectCommand.

C#

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
using Devart.Data.SkyviaConnect;
...
class Program
{

    static void Main(string[] args) {

        const string connectionString =  "Endpoint Url=https://connect.skyvia.com/4v6en3d0;User=testuser;Password=testpassword";
        const string sql = "UPDATE dbo.Categories SET CategoryName = 'sample name 2' WHERE CategoryName = 'sample name 1'";

        using (SkyviaConnectConnection connection = new SkyviaConnectConnection(connectionString)) {

            connection.Open();
            using (SkyviaConnectCommand command = connection.CreateCommand()) {

                command.CommandText = sql;
                Console.WriteLine(command.ExecuteNonQuery());
            }
        }
        Console.ReadKey();
    }
}

Visual Basic

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Imports Devart.Data.SkyviaConnect
...
Class Program
    Private Shared Sub Main(ByVal args As String())
        Const connectionString As String = "Endpoint Url=https://connect.skyvia.com/4v6en3d0;User=testuser;Password=testpassword"
        Const sql As String = "UPDATE dbo.Categories SET CategoryName = 'sample name 2' WHERE CategoryName = 'sample name 1'"

        Using connection As SkyviaConnectConnection = New SkyviaConnectConnection(connectionString)
            connection.Open()

            Using command As SkyviaConnectCommand = connection.CreateCommand()
                command.CommandText = sql
                Console.WriteLine(command.ExecuteNonQuery())
            End Using
        End Using

        Console.ReadKey()
    End Sub
End Class

Metadata

dotConnect for Skyvia Connect supports getting the endpoint metadata using the GetSchema method of the SkyviaConnectConnection class. This method retrieves detailed information about the endpoint objects as a DataTable object. It allows obtaining endpoint schema information without writing queries and parsing the output.

GetSchema Overloads

The GetSchema method is available in three overloads, each of them serves its own purpose:

If you call the GetSchema method without parameters, or with a single parameter “MetaDataCollections” (which is actually the same), the table object returned by the method will contain three columns. The first field of every row is a keyword allowed to be passed to the method (as collectionName argument). The second field is the number of restriction values for this keyword (passed through restrictionValues argument). The third field is not used in dotConnect for Skyvia Connect.

GetSchema with 1 argument returns general information about the collection queried. For example, GetSchema("Tables") returns the list of the tables (objects) available via your SQL endpoint and the information about them.

Instead of specifying the metadata collection name as a string constant, you may use members of System.Data.DbMetaDataCollectionNames and Devart.Data.SkyviaConnect.SkyviaConnectMetadataCollectionNames as the first GetSchema argument values. The members of these classes are the string fields, each field stores the corresponding metadata collection name. It is recommended to use these fields rather than manually input the collection names manually as the string constants because in case of using these fields, you will find misspellings at compile-time, and intellisense will show you all the available metadata collection names.

Finally, the third GetSchema overload allows you to specify the collection name and an array of restrictions. These collection-specific restrictions allow you to request information only for those objects of the specified collection that match restrictions or even about one specific object with matching name.

Collections

There are a few more acceptable collections, but they are intended for internal use.

Examples

The following code fragment is an elegant way to detect existence of a table.

C#

1
2
3
4
5
string tableName = "Products";
if (myConnection.GetSchema("Tables", new string[] {tableName }).Rows.Count > 0)
{
Console.WriteLine("Table " + tableName + " exists in the endpoint.");
}

Visual Basic

1
2
3
4
5
Dim tableName As String = "Products"
Dim restrictions() As String = { tableName}
If (myConnection.GetSchema("Tables", restrictions).Rows.Count > 0) Then
  Console.WriteLine("Table " + tableName + " exists in the endpoint.")
End If

The next sample shows how to retrieve columns information from a table and render it to console.

C#

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
static void GetTableInfo(SkyviaConnectConnection myConnection, string tableName, string schemaName)
{
  myConnection.Open();
  DataTable myDataTable = myConnection.GetSchema(
  "Columns", new string[] { schemaName, tableName });
  for (int i = 0; i < myDataTable.Columns.Count; i++)
  {
    Console.Write(myDataTable.Columns[i].Caption + "\t");
  }
  Console.WriteLine();
  foreach (DataRow myRow in myDataTable.Rows)
  {
    foreach (DataColumn myCol in myDataTable.Columns)
    {
      Console.Write(myRow[myCol] + "\t");
    }
    Console.WriteLine();
  }
  myConnection.Close();
}

Visual Basic

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Private Shared Sub GetTableInfo(ByVal myConnection As SkyviaConnectConnection, ByVal tableName As String, ByVal schemaName As String)
    myConnection.Open()
    Dim myDataTable As DataTable = myConnection.GetSchema("Columns", New String() {schemaName, tableName})

    For i As Integer = 0 To myDataTable.Columns.Count - 1
        Console.Write(myDataTable.Columns(i).Caption & vbTab)
    Next

    Console.WriteLine()

    For Each myRow As DataRow In myDataTable.Rows

        For Each myCol As DataColumn In myDataTable.Columns
            Console.Write(myRow(myCol) & vbTab)
        Next

        Console.WriteLine()
    Next

    myConnection.Close()
End Sub

Deployment

To deploy applications written with dotConnect for Skyvia Connect, you should register run-time assemblies Devart.Data.SkyviaConnect.dll, Devart.Data.SqlShim.dll, and Devart.Data.dll at Global Assembly Cache (GAC) for appropriate framework or place them in the folder of your application (Bin folder for web projects). These two assemblies should be available in all applications written with dotConnect for Skyvia Connect.

When your code uses dotConnect for Skyvia Connect via a factory-based class, you should register configuration information in the DbProviderFactories section of the *.config file to inform your environment about the existence of the provider factory. The provider factory is described either in machine.config (globally), in app.config or in web.config (just for your application), but not in both files. This is done as follows:

<system.data>
  <DbProviderFactories>
    <remove invariant="Devart.Data.SkyviaConnect" />
    <add name="dotConnect for Skyvia Connect" invariant="Devart.Data.SkyviaConnect" description="Devart dotConnect for Skyvia Connect" type="Devart.Data.SkyviaConnect.SkyviaConnectProviderFactory, Devart.Data.SkyviaConnect, Version=1.0.0.0, Culture=neutral, PublicKeyToken=09af7300eec23701" />
  </DbProviderFactories>
</system.data>

Replace 1.0.0.0 here with your actual version.

Additional Connection String Parameters