LINQ to SFTP Data

This article illustrates using LINQ to access tables within the SFTP via the CData ADO.NET Data Provider for SFTP. To achieve this, we will use LINQ to Entity Framework, which facilitates the generation of connections and can be seamlessly employed with any CData ADO.NET Data Providers to access data through 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;

👁 Required connection properties for the data source. (QuickBooks is shown.)

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.