SQL data source

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.

}

}

}