using System;
using System.Data.OleDb;
using System.IO;
using AmiBroker.Data;
using AmiBroker.Utils.Data.DataSourceOffline;
namespace AmiBroker.Samples.DataSourceOfflineSamples.SQL
{
[ABDataSource("Sample - SQL")]
class DataSourceSql : DataSourceOffline
{
private PluginStatus pluginStatus = new PluginStatus(StatusCode.OK, System.Drawing.Color.Green, "OK", "Everything is fine...");
public DataSourceSql(string settings)
: base(settings)
{
}
/// <summary>
/// Get and build historical data for a ticker
/// </summary>
/// <param name="tickerData"></param>
/// <remarks>
/// Place short term resource allocation (e.g. local DB connection setup, etc.) here.
/// Load and populate all quotation data to tickerData.Quotes using the Merge method.
/// See .NET for AmiBroker's Help on QuotationList class.
/// </remarks>
public override void Ticker_GetQuotes(TickerData tickerData)
{
OleDbConnection conn = null;
OleDbCommand cmd = null;
OleDbDataReader dr = null;
// get the file path to Excel workbook
string baseDirectory = AppDomain.CurrentDomain.BaseDirectory;
baseDirectory = Directory.GetParent(baseDirectory).FullName;
string dataFilePath = Path.Combine(baseDirectory, "Samples\\Ascii\\SampleDB.xlsx");
// determine the Excel sheet/table name for the AB database periodicity
string worksheetName = null;
if (tickerData.Quotes.Periodicity >= Periodicity.OneMinute)
worksheetName = "[Data1Min$]";
if (tickerData.Quotes.Periodicity >= Periodicity.FifteenMinutes)
worksheetName = "[Data15Min$]";
if (tickerData.Quotes.Periodicity >= Periodicity.OneHour)
worksheetName = "[Data60Min$]";
// reading the "Excel database" via OLEDB driver
try
{
// create and open oledb connection object using Excel driver
// NOTE: It throws exception if Excel file does not exist, or Excel driver is not installed!!!
// NOTE: This sample usually works only with 32 bit AmiBroker because of the missing 64 bit Excel driver...!!!
conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + dataFilePath + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\"");
conn.Open();
// build and execute a command string to retrieve data for the ticker
// --- customize sql statement using ticker name as well
// --- E.g.:
// --- cmd = new OleDbCommand("Select * From " + worksheetName + "Where TickerId = '" + tickerData.Ticker + "'", conn);
cmd = new OleDbCommand("Select * From " + worksheetName, conn);
dr = cmd.ExecuteReader();
// clear previously stored quotes in the plugin
tickerData.Quotes.Clear();
// read all records from the result set
while (dr.Read())
{
// Excel table columns: TickerId Date Open High Low Close Volume
Quotation quote = new Quotation();
//string ticker = dr.GetString(0);
DateTime date = dr.GetDateTime(1);
quote.DateTime = (AmiDate)date; // cast to AmiBroker's internal datetime type represented on 64 bit ulong
quote.Open = (float)dr.GetDouble(2);
quote.High = (float)dr.GetDouble(3);
quote.Low = (float)dr.GetDouble(4);
quote.Price = (float)dr.GetDouble(5); // Close
quote.Volume = (float)dr.GetDouble(6);
// Merge method builds bars according to database's timebase
tickerData.Quotes.Merge(quote);
}
}
catch (Exception ex)
{
pluginStatus.Status = StatusCode.SevereError;
pluginStatus.Color = System.Drawing.Color.Red;
pluginStatus.ShortMessage = "Error";
pluginStatus.LongMessage = ex.ToString();
DataSourceBase.DotNetLog("Sample - SQL", "Error", ex.ToString());
}
finally
{
// cleaning up used resources
if (dr != null)
dr.Close();
if (cmd != null)
cmd.Dispose();
if (conn != null)
conn.Close();
}
}
/// <summary>
/// Ticker's quotation data is passed to AmiBroker
/// </summary>
/// <param name="tickerData"></param>
/// <remarks>
/// This method is called when quotes are passed to AmiBroker and there is no need for holding resources for this ticker.
/// Release resources allocated in Ticker_GetQuotes(e.g. local DB connection setup, quote objects, etc.)
/// </remarks>
public override void Ticker_Ready(TickerData tickerData)
{
if (tickerData.Quotes != null)
tickerData.Quotes.Clear();
}
/// <summary>
/// Data API method to get plugins status
/// </summary>
/// <returns></returns>
public override PluginStatus GetStatus()
{
return pluginStatus;
}
/// <summary>
/// Data API method to get plugins status
/// </summary>
/// <returns></returns>
public override bool SetTimeBase(Periodicity timeBase)
{
return timeBase >= Periodicity.OneMinute; // Smallest timeframe supported by Excel workbook. Upper timeframes are built by Merge method.
}
}
}