Pull API data with RestSharp and insert into SQLite

scott oliver

Hello, I want to take some information from the OTC website API and put it into a sqlite db. This is what I have to pull the information. It currently saves it into a csv file. What is the easiest way to write it to the DB instead?

public void otcRestClient()
{
var restSharpClient = new RestClient("https://www.otcmarkets.com");
var downloadRequest = new RestRequest("research/stock-screener/api/downloadCSV?pageSize=20",
Method.GET);
var fileInBytes = restSharpClient.DownloadData(downloadRequest);
File.WriteAllBytes(@"C:\Users\openuser\Documents\Stock Reports\OTC\OTCStockData.csv",
fileInBytes);
Console.WriteLine("\n\nPress any key ...");
Console.ReadKey();
}

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sachin Singh6y ago
if you are using Entity Framework then
Public Void SaveToDB()
{

YourDbContext db = new YourDbContext();
            var restSharpClient = new RestClient("https://www.otcmarkets.com");

            var downloadRequest = new RestRequest("research/stock-screener/api/downloadCSV?pageSize=20",
                Method.GET);

            IRestResponse response = restSharpClient.Execute(downloadRequest);
            //Console.WriteLine(response.Content);

            string[] strSeparators = new string[] { "\r\n" };
            string[] Lines = response.Content.Split(strSeparators, StringSplitOptions.None);

            string[] Fields;



            List dataList = new List();


            foreach(var line in Lines.Skip(1).Take(5)){ // skip 1st line and take all , i have taken 5,simply don't use Take() to get all rows
                 Fields = line.Split(new char[] { ',' });

                     CsvData dt = new CsvData();
                    dt.Symbol=Fields[0];
                    dt.Security_Name = Fields[1];
                    dt.Tier = Fields[2];
                    dt.Price =Fields[3];
                    dt.Change = Fields[4];
                    dt.Vol =Fields[5];
                    dt.Sec_Type = Fields[6];
                    dt.Country = Fields[7];
                    dt.State = Fields[8];
                    dataList.Add(dt);


                }



              db.CsvDatas.AddRange(dataList);
              db.SaveChanges();
         }

public class CsvData
{
    public string Symbol { get; set; }
    public string Security_Name { get; set; }
    public string Tier { get; set; }
    public string Price { get; set; }
    public string Change { get; set; }

    public string Vol { get; set; }
    public string Sec_Type { get; set; }
    public string Country { get; set; }
    public string State { get; set; }

}
Amit Mohanty6y ago
Hey check the below code, this may help you.
var restSharpClient = new RestClient("https://www.otcmarkets.com");

var downloadRequest = new RestRequest("research/stock-screener/api/downloadCSV?pageSize=20",
    Method.GET);

IRestResponse response = restSharpClient.Execute(downloadRequest);
//Console.WriteLine(response.Content);

string[] strSeparators = new string[] { "\r\n" };
string[] Lines = response.Content.Split(strSeparators, StringSplitOptions.None);

string[] Fields;
Fields = Lines[0].Split(new char[] { ',' });
int Cols = Fields.GetLength(0);
DataTable dt = new DataTable();
//1st row must be column names; force lower case to ensure matching later on.
for (int i = 0; i < Cols; i++)
    dt.Columns.Add(Fields[i].ToLower(), typeof(string));
DataRow Row;
for (int i = 1; i < Lines.GetLength(0); i++)
{
    Fields = Lines[i].Split(new char[] { ',' });
    if (Fields.Length > 1)
    {
        Row = dt.NewRow();
        for (int f = 0; f < Cols; f++)
            Row[f] = Fields[f];
        dt.Rows.Add(Row);
    }
}
if (dt.Rows.Count > 0)
{
    // TODO: Code for save data to database.
}