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:

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#

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

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. Skyvia onnectDataReader 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#

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

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.

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

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#

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

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

Collection Name Returned information Number of restrictions Available restrictions
Columns Returns list of columns, their type and some extra information. 3 • Name of a schema that the GetSchema method should search in
• Name of a table that the GetSchema method should search in
• Name of a column
DataSourceInformation Returns information about the data source. 0  
DataTypes Returns information about data types supported by the data source. 0  
ForeignKeyColumns Returns the list of columns that participate in foreign keys. 5 • Name of a schema that the GetSchema method should search in
• Name of a table that the GetSchema method should search in
• Name of a foreign key
• Referenced table schema
• Referenced table name
ForeignKeys Returns the list of foreign keys. 5 • Name of a schema that the GetSchema method should search in
• Name of a table that the GetSchema method should search in
• Name of a foreign key
• Referenced table schema
• Referenced table name
IndexColumns Returns the list of columns that participate in indexes. 3 • Name of a schema that the GetSchema method should search in
• Name of a table that the GetSchema method should search in
• Name of an index
Indexes Returns the list of indexes. 3 • Name of a schema that the GetSchema method should search in
• Name of a table that the GetSchema method should search in
• Name of an index
MetadataCollections Returns this list. Same as using the GetSchema() method without parameters. 0  
PrimaryKeyColumns Returns the list of columns that participate in primary keys. 3 • Name of a schema that the GetSchema method should search in
• Name of a table that the GetSchema method should search in
• Name of a primary key
PrimaryKeys Returns the list of primary keys. 3 • Name of a schema that the GetSchema method should search in
• Name of a table that the GetSchema method should search in
• Name of a primary key
ReservedWords Lists all reserved words used in the server. 0  
Tables Returns the list of the SQL endpoint tables (objects). 2 • Name of a schema that the GetSchema method should search in
• Name of a table
UniqueKeyColumns Returns the list of columns that participate in unique keys. 3 • Name of a schema that the GetSchema method should search in
• Name of a table that the GetSchema method should search in
• Name of a unique key
UniqueKeys Returns the list of unique keys. 3 • Name of a schema that the GetSchema method should search in
• Name of a table that the GetSchema method should search in
• Name of a unique key

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#

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

Visual Basic

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#

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

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

Parameter Description
Connection Lifetime When a connection is returned to the pool, its creation time is compared with the current time, and the connection is destroyed if that time span (in seconds) exceeds the value specified by Connection Lifetime. The default value is 0 (connection always returns to pool).
Connect Timeout -or- Connection Timeout The length of time (in seconds) to wait for a connection to the server before terminating the attempt and generating an error. The default value is 30.
Default Command Timeout The time in seconds to wait while trying to execute a command before terminating the attempt and generating an error. A value of 0 indicates no limit. The default value is 60.
Initialization Command Specifies a data source-specific command that should be executed immediately after establishing the connection.
Max Pool Size The maximum number of connections allowed in the pool. Setting the Max Pool Size value of the ConnectionString can affect performance. The default value is 100.
Min Pool Size The minimum number of connections allowed in the pool. Setting the Max Pool Size value of the ConnectionString can affect performance. The default value is 0.
Persist Security Info Indicates if security-sensitive information, such as the password, is not returned as part of the connection if the connection is open or has ever been in an open state.
Pooling If true (by default), the SkyviaConnectConnection object is drawn from the appropriate pool or is created and added to the appropriate pool.
Proxy Host If you are connected to the Internet via a proxy server, specify its address in this parameter. To find your Proxy server address, in the Control Panel open Internet Options, switch to the Connections tab, and click LAN settings.
Proxy Port If you are connected to the Internet via a proxy server, specify its port in this parameter. You can find it in the same way as its address, as described above.
Proxy User If Proxy User authorization is used, specify Proxy user name (ID) in this parameter.
Proxy Password If Proxy User authorization is used, specify Proxy password (ID) in this parameter.
Readonly Determines whether the connection is read-only (allows only SELECT statements).
UTC Dates Specifies whether all the datetime values retrieved from the data source are returned as UTC values or converted to local time and whether the date values specified on the application side (e.g., in SQL statements) are considered UTC or local. The default value is false.