I am getting
Parameter 'Volm' can be removed; its initial value is never used,
despite, as far as I can tell, the value being used.
I am getting
Unnecessary assignment of a value to 'Volm'
for that same value being assigned.
And, on publish, I am getting
Function signature of "FillRow" method does not match SQL declaration for table valued CLR function'GetQuoteHistory' due to column 7,
despite having 7 columns for each, and matching data types.
If I comment out the last column in the function, table definition, and assignment, the project publishes.
I am banging my head, and not because there is music playing. Appreciate other sets of eyes. The above messages are noted on the relevant lines.
public class TableFunctions
{
[SqlFunction(FillRowMethodName = "FillQuoteHistoryRow",
TableDefinition = "Symbol NVarChar(10), TradeTime datetime,
Opn money, High money,
Low money, Clos money,
Volm int")]
public static IEnumerable GetQuoteHistory(string Symbol)
{
return new QuoteHistory(Symbol);
}
private static void FillQuoteHistoryRow(Object Item, ref string Symbol, ref DateTime TradeTime,
ref decimal Opn, ref decimal High,
ref decimal Low, ref decimal Clos,
int Volm) // mouseover: Parameter 'Volm' can be removed; its initial value is never used.
// on publish, builds, but returns:
// (278,1): SQL72014: .Net SqlClient Data Provider: Msg 6258, Level 16, State 1, Procedure GetQuoteHistory,
// Line 1 Function signature of "FillRow" method(as designated by SqlFunctionAttribute.FillRowMethodName)
// does not match SQL declaration for table valued CLR function'GetQuoteHistory' due to column 7.
{
QuoteHistoryItem QHItem = (QuoteHistoryItem)Item;
Symbol = QHItem.Symbol;
TradeTime = QHItem.Time;
Opn = QHItem.Opn;
High = QHItem.High;
Low = QHItem.Low;
Clos = QHItem.Clos;
Volm = QHItem.Volm; // mouseover: Unnecessary assignment of a value to 'Volm'
} // method
} // class TableFunctions
public class QuoteHistory : List<QuoteHistoryItem>
{
public QuoteHistory(string Symbol)
{
LoadQuoteHistory(Symbol);
} // method QuoteHistory
public void LoadQuoteHistory(string Symbol)
{
string path = "C:\\Github\\Uranus\\SQLCLR\\APIOrig\\";
string func = "TIME_SERIES_INTRADAY_EXTENDED";
string interval = "1min";
string slice = "year1month1";
string apikey = "XBM9114REDBJXZIV";
string QueryURL = "https://www.alphavantage.co/query" +
"?function=" + func +
"&symbol=" + (string)Symbol +
"&interval=" + interval +
"&slice=" + slice +
"&apikey=" + apikey;
QueryURL = "file:///" + path + "Example.csv"; // FOR TESTING
Uri queryUri = new Uri(QueryURL);
using (WebClient client = new WebClient())
{
using (MemoryStream stream = new MemoryStream(client.DownloadDataTaskAsync(queryUri).Result))
{
stream.Position = 0;
using (StreamReader APIData = new StreamReader(stream))
{
while (!APIData.EndOfStream)
{
var quoteLine = APIData.ReadLine();
var quoteFields = quoteLine.Split(',');
var QHItem = new QuoteHistoryItem(Symbol, quoteFields);
if (QHItem.Time > new DateTime(1900, 1, 1))
{
Add(QHItem);
} // if
} // while
} // streamreader
} // memory stream
} // webclient
} // LoadQuoteHistory
public class QuoteHistoryItem
{
public string Symbol { get; set; }
public DateTime Time { get; set; }
public decimal Opn { get; set; }
public decimal High { get; set; }
public decimal Low { get; set; }
public decimal Clos { get; set; }
public int Volm { get; set; }
public QuoteHistoryItem(string symbol, string[] quoteFields)
{
if (quoteFields != null && quoteFields.Length > 5)
{
try
{
Symbol = symbol;
DateTime.TryParse(quoteFields[0], out DateTime time);
Time = time;
decimal.TryParse(quoteFields[1], out decimal opn);
Opn = opn;
decimal.TryParse(quoteFields[2], out decimal high);
High = high;
decimal.TryParse(quoteFields[3], out decimal low);
Low = low;
decimal.TryParse(quoteFields[4], out decimal clos);
Clos = clos;
int.TryParse(quoteFields[5], out int volm);
Volm = volm;
}
catch (FormatException fx)
{
Console.Error.WriteLine(fx.Message);
}
}
} // method QuoteHistoryItem
ref??TextFieldParserto parse the CSV? I must say I think this whole thing is an exercise in frustration. Shoehorning web access into SQL Server is always going to be difficult. You are much better off using something like Powershell for this eg you could do it in three linesGet-WebRequest .... | Import-Csv ... | WriteDbaDbTable