Ready to get started?

Learn more about the CData ADO.NET Provider for SFTP or download a free trial:

Download Now

LINQ to SFTP Data

LINQ provides general-purpose query facilities in .NET Framework 3.0 and above and provides one easy way to programmatically access data through from CData ADO.NET Data Providers. This example uses LINQ to access information from the SFTP Data Provider.

This article demonstrates how to use LINQ to access SFTP tables through the CData ADO.NET Data Provider for SFTP. To do this you will LINQ to Entity Framework, which is used to generate the connection and can be used with any CData ADO.NET Data Providers to access data via LINQ.

See the help documentation for a guide to setting up an EF 6 project to use the provider.

  1. In a new project in Visual Studio, right-click on the project and choose to add a new item. Add an ADO.NET Entity Data Model.
  2. Choose EF Designer from Database and click Next.
  3. Add a new Data Connection, and change your data source type to "CData SFTP Data Source".
  4. Enter your data source connection information.

    SFTP can be used to transfer files to and from SFTP servers using the SFTP Protocol. To connect, specify the RemoteHost;. service uses the User and Password and public key authentication (SSHClientCert). Choose an SSHAuthMode and specify connection values based on your selection.

    Set the following connection properties to control the relational view of the file system:

    • RemotePath: Set this to the current working directory.
    • TableDepth: Set this to control the depth of subfolders to report as views.
    • FileRetrievalDepth: Set this to retrieve files recursively and list them in the Root table.
    Stored Procedures are available to download files, upload files, and send protocol commands. See gdatamodel for more on using SQL to interact with the server.

    Below is a typical connection string:

    RemoteHost=MyFTPServer;
  5. If saving your entity connection to App.Config, set an entity name. In this example we are setting SFTPEntities as our entity connection in App.Config.
  6. Enter a model name and select any tables or views you would like to include in the model.

Using the entity you created, you can now perform select , update, delete, and insert commands. For example:

SFTPEntities context = new SFTPEntities(); var mydirectoryQuery = from mydirectory in context.MyDirectory select mydirectory; foreach (var result in mydirectoryQuery) { Console.WriteLine("{0} {1} ", result.Id, result.Filesize); }

See "LINQ and Entity Framework" chapter in the help documentation for example queries of the supported LINQ.