如何从REST调用中导入数据，将json feed导入MAC上的SQLite数据库？

I have data that's on my server in a SQL Server database. I can access that with a REST call into a C# ASP.Net Web.API that I have control over and it will return json data. Possibly I can get that to return other formats of data but I am not sure about that. I have full access to the server application and the json it creates.

我在SQL Server数据库中的服务器上有数据。我可以通过REST调用访问我控制的C＃ASP.Net Web.API，它将返回json数据。可能我可以得到它来返回其他格式的数据，但我不确定。我可以完全访问服务器应用程序和它创建的json。

On my development Mac I am using DB Browser for SQLite and Xamarin to develop a multi-platform app. I have a small SQLite database created.

在我的开发Mac上，我使用DB Browser for SQLite和Xamarin来开发一个多平台应用程序。我创建了一个小的SQLite数据库。

How can I import/insert the JSON data from some of my tables on the server to tables in the SQLite database that I create on my MAC? I need to do this manually but I would like to automate the process of doing the import into a bash script or something similar that I can run with a command.

如何从服务器上的某些表中导入/插入JSON数据到我在MAC上创建的SQLite数据库中的表？我需要手动执行此操作，但我想自动执行导入bash脚本的过程或类似于我可以使用命令运行的过程。

I have researched this but don't seem to be able to find any examples of how to do it so I opened up a bounty in the hope that someone could give an answer that would be a big help to me and to others.

我已经研究了这个，但似乎无法找到任何如何做的例子，所以我开了一笔赏金，希望有人可以给出一个对我和其他人有很大帮助的答案。

2 个解决方案

#1

Off the top of my head, you have two options that you can do:

在我的头顶，你有两个选择，你可以做：

Make a simple Xamarin.Mac app that does this. Similar to how windows folks might make a console app. Just have it have one button and call pretty much the same code in your xamarin app to download the data and dump it into a sqlite db.
制作一个简单的Xamarin.Mac应用程序来做到这一点。类似于Windows人员可能制作控制台应用程序的方式。只需拥有一个按钮，并在您的xamarin应用程序中调用几乎相同的代码来下载数据并将其转储到sqlite数据库中。
A better option, would be to write a "unit test" (or integration test for those hardcore peeps) that calls the existing code in your xamarin app and writes it to a sqlite db on the file share. Then you can do this as often as you want with a run of a unit test. This could be done in your test runner in Xamarin Studio (Visual Studio or anything that has a test runner). I would use either nUnit or xUnit since they great cross-platform support.
更好的选择是编写一个“单元测试”（或那些硬核窥视的集成测试），调用xamarin应用程序中的现有代码并将其写入文件共享上的sqlite数据库。然后，您可以根据需要随时进行单元测试。这可以在Xamarin Studio（Visual Studio或具有测试运行器的任何东西）的测试运行器中完成。我会使用nUnit或xUnit，因为它们具有很好的跨平台支持。

In my previous app, I had XUnit tests that checked to make sure the API calls were working and other tests to ensure that my SQLite.Net-PCL code was working. You could combine that into a single "test" to download the data into a db3.

在我以前的应用程序中，我进行了XUnit测试，检查以确保API调用正常工作以及其他测试以确保我的SQLite.Net-PCL代码正常工作。您可以将它组合成一个“测试”，将数据下载到db3中。

This assumes you abstracted your code out. If not, you could just copy and paste it. Either way, if you are using x-plat nuget packages the code will work on desktop or mobile app.

这假设您抽象出代码。如果没有，您可以复制并粘贴它。无论哪种方式，如果您使用x-plat nuget包，代码将适用于桌面或移动应用程序。

#2

i would use node js to write a script using javascript.

我会使用节点js使用javascript编写脚本。

install nodejs. https://nodejs.org/en/download/ or

安装nodejs。 https://nodejs.org/en/download/或

brew install node

create a directory to work on your project mkdir myimporter cd myimporter

创建一个目录来处理你的项目mkdir myimporter cd myimporter

install the required libraries in the folder

在文件夹中安装所需的库

npm install --save request sqlite3 moment 

npm install -g nodemon

open the folder or app.js with your favorite text editor

用您喜欢的文本编辑器打开文件夹或app.js.

save the following code as app.js

将以下代码保存为app.js

var request = require('request');
var sqlite3 = require("sqlite3").verbose();
var moment = require("moment");
var url =  'http://www.google.com';
var s, e;



var fs = require("fs");
var file = "test.db";
//var file = process.env.CLOUD_DIR + "/" + "test.db";
var exists = fs.existsSync(file);
var sqlite3 = require("sqlite3").verbose();
var db = new sqlite3.Database(file);

// use same exists from checking if db exists, with assumption that table would exists in a new db.
if(!exists){
    db.run("CREATE TABLE Stuff (thing TEXT)");
}


function saveResultTosqlite3(message){
    var stmt = db.prepare("INSERT INTO Stuff VALUES (?)");
    stmt.run(message);
    stmt.finalize();
}


s = moment();
request(url, function (error, response, body) {
    e = moment();
    var elapsed =e.diff(s,'milliseconds');
    var responseCode = response.statusCode;     

    if (!error && response.statusCode == 200) {    
        console.log("successful request");
    }
    var message = 'request to ' + url + ' returned status code of ' + responseCode.toString() + ' in ' +elapsed.toString() + ' milliseconds'  ;
    console.log(message);
    saveResultTosqlite3(message);    
});

run the follwing terminal to run the script each time it changes, for development / testing

运行下面的终端以在每次更改时运行脚本，以进行开发/测试

nodemon app.js

#1