About‎ > ‎

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.

        }

    }

}