cAlgo and MySQL

Created at 27 Feb 2014, 21:34
Subscribe
How’s your experience with the cTrader Platform?
Your feedback is crucial to cTrader's development. Please take a few seconds to share your opinion and help us improve your trading experience. Thanks!
TH

thakas

Follow

Joined 22.02.2013

cAlgo and MySQL
27 Feb 2014, 21:34

Hi,

I want to connect cAlgo with MySQL. Searching the internet i found the following link, where is explaining how to connect a C# Application with MySQL Database:

http://www.youtube.com/watch?v=4gybWgePuOs

When I try to build the following code, i get this error:

Error CS0012: Referenced indicators or cbots are out of date. Please rebuild them.

Is it any Solution?

thanks in advance.

//#reference: MySql.Data.dll

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;

namespace MySQLClass
{

    //Don't forget to add the MySQL.Data dll to your projects references
    //It can be downloaded for free from MySQL's official website.
    //Link to the .NET Connector (.dll file) http://dev.mysql.com/downloads/connector/net/


    class MySQLClient
    {
        MySqlConnection conn = null;


        #region Constructors
        public MySQLClient(string hostname, string database, string username, string password)
        {
            conn = new MySqlConnection("host=" + hostname + ";database=" + database + ";username=" + username + ";password=" + password + ";");
        }

        public MySQLClient(string hostname, string database, string username, string password, int portNumber)
        {
            conn = new MySqlConnection("host=" + hostname + ";database=" + database + ";username=" + username + ";password=" + password + ";port=" + portNumber.ToString() + ";");
        }

        public MySQLClient(string hostname, string database, string username, string password, int portNumber, int connectionTimeout)
        {
            conn = new MySqlConnection("host=" + hostname + ";database=" + database + ";username=" + username + ";password=" + password + ";port=" + portNumber.ToString() + ";Connection Timeout=" + connectionTimeout.ToString() + ";");
        }
        #endregion

        #region Open/Close Connection
        private bool Open()
        {
            //This opens temporary connection
            try
            {
                conn.Open();
                return true;
            } catch
            {
                //Here you could add a message box or something like that so you know if there were an error.
                return false;
            }
        }

        private bool Close()
        {
            //This method closes the open connection
            try
            {
                conn.Close();
                return true;
            } catch
            {
                return false;
            }
        }
        #endregion

        public void Insert(string table, string column, string value)
        {
            //Insert values into the database.

            //Example: INSERT INTO names (name, age) VALUES('John Smith', '33')
            //Code: MySQLClient.Insert("names", "name, age", "'John Smith, '33'");
            string query = "INSERT INTO " + table + " (" + column + ") VALUES (" + value + ")";

            try
            {
                if (this.Open())
                {
                    //Opens a connection, if successful; run the query and then close the connection.

                    MySqlCommand cmd = new MySqlCommand(query, conn);

                    cmd.ExecuteNonQuery();
                    this.Close();
                }
            } catch
            {
            }
            return;
        }

        public void Update(string table, string SET, string WHERE)
        {
            //Update existing values in the database.

            //Example: UPDATE names SET name='Joe', age='22' WHERE name='John Smith'
            //Code: MySQLClient.Update("names", "name='Joe', age='22'", "name='John Smith'");
            string query = "UPDATE " + table + " SET " + SET + " WHERE " + WHERE + "";

            if (this.Open())
            {
                try
                {
                    //Opens a connection, if successful; run the query and then close the connection.

                    MySqlCommand cmd = new MySqlCommand(query, conn);
                    cmd.ExecuteNonQuery();
                    this.Close();
                } catch
                {
                    this.Close();
                }
            }
            return;
        }

        public void Delete(string table, string WHERE)
        {
            //Removes an entry from the database.

            //Example: DELETE FROM names WHERE name='John Smith'
            //Code: MySQLClient.Delete("names", "name='John Smith'");
            string query = "DELETE FROM " + table + " WHERE " + WHERE + "";

            if (this.Open())
            {
                try
                {
                    //Opens a connection, if successful; run the query and then close the connection.

                    MySqlCommand cmd = new MySqlCommand(query, conn);
                    cmd.ExecuteNonQuery();
                    this.Close();
                } catch
                {
                    this.Close();
                }
            }
            return;
        }

        public Dictionary<string, string> Select(string table, string WHERE)
        {
            //This methods selects from the database, it retrieves data from it.
            //You must make a dictionary to use this since it both saves the column
            //and the value. i.e. "age" and "33" so you can easily search for values.

            //Example: SELECT * FROM names WHERE name='John Smith'
            // This example would retrieve all data about the entry with the name "John Smith"

            //Code = Dictionary<string, string> myDictionary = MySQLClient.Select("names", "name='John Smith'");
            //This code creates a dictionary and fills it with info from the database.

            string query = "SELECT * FROM " + table + " WHERE " + WHERE + "";

            Dictionary<string, string> selectResult = new Dictionary<string, string>();

            if (this.Open())
            {
                MySqlCommand cmd = new MySqlCommand(query, conn);
                MySqlDataReader dataReader = cmd.ExecuteReader();

                try
                {
                    while (dataReader.Read())
                    {

                        for (int i = 0; i < dataReader.FieldCount; i++)
                        {
                            selectResult.Add(dataReader.GetName(i).ToString(), dataReader.GetValue(i).ToString());
                        }

                    }
                    dataReader.Close();
                } catch
                {
                }
                this.Close();

                return selectResult;
            }
            else
            {
                return selectResult;
            }
        }

        public int Count(string table)
        {
            //This counts the numbers of entries in a table and returns it as an integer

            //Example: SELECT Count(*) FROM names
            //Code: int myInt = MySQLClient.Count("names");

            string query = "SELECT Count(*) FROM " + table + "";
            int Count = -1;
            if (this.Open() == true)
            {
                try
                {
                    MySqlCommand cmd = new MySqlCommand(query, conn);
                    Count = int.Parse(cmd.ExecuteScalar() + "");
                    this.Close();
                } catch
                {
                    this.Close();
                }
                return Count;
            }
            else
            {
                return Count;
            }
        }
    }
}
@thakas
Replies

Spotware
28 Feb 2014, 08:54

You need to add a reference to System.Data.dll

@Spotware

breakermind
13 Mar 2014, 08:27

RE:

Hi,


and maybe someone knows how connect to mysql database using HTTPS
or how to send get request to web server (like apache2) using HTTPS 

Regards

@breakermind

modarkat
13 Mar 2014, 08:42

RE: RE:

breakermind said:

Hi,


and maybe someone knows how connect to mysql database using HTTPS
or how to send get request to web server (like apache2) using HTTPS 

Regards

http://stackoverflow.com/questions/708210/how-to-use-http-get-request-in-c-sharp-with-ssl-protocol-violation

@modarkat

breakermind
13 Mar 2014, 08:59

RE: RE: RE:

modarkat said:

breakermind said:

Hi,


and maybe someone knows how connect to mysql database using HTTPS
or how to send get request to web server (like apache2) using HTTPS 

Regards

http://stackoverflow.com/questions/708210/how-to-use-http-get-request-in-c-sharp-with-ssl-protocol-violation

Thank you

@breakermind

lamfete
15 May 2014, 09:20

Hi,

I tried to connect cAlgo and mysql.

when I add reference: MySql.Data.dll and build it, I got this error:

a namespace cannot directly contain members such as fields or methods

this is my source code :

reference: MySql.Data.dll

using System;
using cAlgo.API;
using cAlgo.API.Indicators;
using cAlgo.API.Collections;
using System.Linq;

namespace cAlgo.Indicators
{
    [Indicator(ScalePrecision = 5, IsOverlay = false, AccessRights = AccessRights.None)]

    public class TickChart : Indicator
    {
        [Output("Ask", Color = Colors.Blue)]
        public IndicatorDataSeries Ask { get; set; }

        [Output("Bid", Color = Colors.Red)]
        public IndicatorDataSeries Bid { get; set; }

        //private MarketDepth _marketDepth;

        private static void ShiftDataSeries(IndicatorDataSeries dataSeries)
        {
            for (var i = 0; i < dataSeries.Count - 1; i++)
            {
                dataSeries[i] = dataSeries[i + 1];
            }
        }

        private static void FillDataSeries(IndicatorDataSeries dataSeries, double value, int startIndex, int count)
        {
            for (var i = startIndex; i < startIndex + count; i++)
                dataSeries[i] = value;
        }

        public override void Calculate(int index)
        {
            if (!IsRealTime)
                return;

            if (!double.IsNaN(Ask[index]))
            {
                ShiftDataSeries(Ask);
                ShiftDataSeries(Bid);
            }

            FillDataSeries(Ask, Symbol.Ask, index, 50);
            FillDataSeries(Bid, Symbol.Bid, index, 50);

            var spread = Math.Round((Symbol.Ask - Symbol.Bid) / Symbol.PipSize, 1);
            ChartObjects.DrawText("Spread label", "Spread:\t" + spread + " pips", StaticPosition.BottomRight);
        }
    }
}

 

@lamfete

Spotware
15 May 2014, 09:52

Dear lamfete,

You specified reference in the wrong format. Instead of 

reference: MySql.Data.dll

you need to write

//#reference: MySql.Data.dll

However such approach is already obsolete. We can recommend you to read an article Legacy References

@Spotware

lamfete
17 May 2014, 06:47

RE:

Spotware said:

Dear lamfete,

You specified reference in the wrong format. Instead of 

reference: MySql.Data.dll

you need to write

//#reference: MySql.Data.dll

However such approach is already obsolete. We can recommend you to read an article Legacy References

thank you for your help :)

@lamfete

lamfete
19 May 2014, 15:51

Need help again.

I want to insert every tick of the price into MySQL.

There is no error when I build my code. But the tick price is not entered in the database.

using System;
using cAlgo.API;
using cAlgo.API.Indicators;
using cAlgo.API.Collections;
using System.Linq;
using MySql.Data.MySqlClient;

namespace cAlgo.Indicators
{
    [Indicator(ScalePrecision = 5, IsOverlay = false, AccessRights = AccessRights.None)]

    public class TickChart : Indicator
    {
        MySqlConnection conn = null;


        #region Constructors
        public TickChart(string hostname, string database, string username, string password)
        {
            hostname = "localhost";
            database = "test";
            username = "root";
            password = "";

            conn = new MySqlConnection("host=" + hostname + ";database=" + database + ";username=" + username + ";password=" + password + ";");
        }

        public TickChart(string hostname, string database, string username, string password, int portNumber)
        {
            hostname = "localhost";
            database = "test";
            username = "root";
            password = "";

            conn = new MySqlConnection("host=" + hostname + ";database=" + database + ";username=" + username + ";password=" + password + ";port=" + portNumber.ToString() + ";");
        }

        public TickChart(string hostname, string database, string username, string password, int portNumber, int connectionTimeout)
        {
            hostname = "localhost";
            database = "test";
            username = "root";
            password = "";

            conn = new MySqlConnection("host=" + hostname + ";database=" + database + ";username=" + username + ";password=" + password + ";port=" + portNumber.ToString() + ";Connection Timeout=" + connectionTimeout.ToString() + ";");
        }
        #endregion

        #region Open/Close Connection
        private bool Open()
        {
            //This opens temporary connection
            try
            {
                conn.Open();
                return true;
            } catch
            {
                //Here you could add a message box or something like that so you know if there were an error.
                return false;
            }
        }

        private bool Close()
        {
            //This method closes the open connection
            try
            {
                conn.Close();
                return true;
            } catch
            {
                return false;
            }
        }
        #endregion

        public void Insert(string table, string column, string value)
        {
            //Insert values into the database.
            table = "test1";
            column = "ask, bid";
            value = Ask + ", " + Bid;

            //Example: INSERT INTO names (name, age) VALUES('John Smith', '33')
            //Code: MySQLClient.Insert("names", "name, age", "'John Smith, '33'");
            string query = "INSERT INTO " + table + " (" + column + ") VALUES (" + value + ")";

            try
            {
                if (this.Open())
                {
                    //Opens a connection, if successful; run the query and then close the connection.

                    MySqlCommand cmd = new MySqlCommand(query, conn);

                    cmd.ExecuteNonQuery();
                    this.Close();
                }
            } catch
            {
            }
            return;
        }

        [Output("Ask", Color = Colors.Blue)]
        public IndicatorDataSeries Ask { get; set; }

        [Output("Bid", Color = Colors.Red)]
        public IndicatorDataSeries Bid { get; set; }

        //private MarketDepth _marketDepth;

        private static void ShiftDataSeries(IndicatorDataSeries dataSeries)
        {
            for (var i = 0; i < dataSeries.Count - 1; i++)
            {
                dataSeries[i] = dataSeries[i + 1];
            }
        }

        private static void FillDataSeries(IndicatorDataSeries dataSeries, double value, int startIndex, int count)
        {
            for (var i = startIndex; i < startIndex + count; i++)
                dataSeries[i] = value;
        }

        public override void Calculate(int index)
        {
            if (!IsRealTime)
                return;

            if (!double.IsNaN(Ask[index]))
            {
                ShiftDataSeries(Ask);
                ShiftDataSeries(Bid);
            }

            FillDataSeries(Ask, Symbol.Ask, index, 50);
            FillDataSeries(Bid, Symbol.Bid, index, 50);

            var spread = Math.Round((Symbol.Ask - Symbol.Bid) / Symbol.PipSize, 1);
            ChartObjects.DrawText("Spread label", "Spread:\t" + spread + " pips", StaticPosition.BottomRight);
        }
    }
}

 

@lamfete

lamfete
19 May 2014, 18:39

I made a several changes and debugging.

It seemly something wrong with the connection.

using System;
using cAlgo.API;
using cAlgo.API.Indicators;
using cAlgo.API.Collections;
using System.Linq;
using MySql.Data.MySqlClient;

namespace cAlgo.Indicators
{
    [Indicator(ScalePrecision = 5, IsOverlay = false, AccessRights = AccessRights.None)]

    public class TickChart : Indicator
    {
        MySqlConnection conServer1;

        #region Open/Close Connection
        private bool Open()
        {
            //This opens temporary connection
            try
            {
                //conn.Open();
                string mysql_port = "3306";
                string mysql_host = "localhost";
                string mysql_database = "test";
                string mysql_user = "root";
                string mysql_password = "123";
                //MySqlConnection conn = null;
                conServer1 = new MySqlConnection("Max Pool Size='1000'; Port='" + mysql_port + "';Network Address='" + mysql_host + "';" + "Initial Catalog='" + mysql_database + "';" + "Persist Security Info=no;" + "User Name='" + mysql_user + "';" + "Password='" + mysql_password + "'; convert zero datetime=True");
                conServer1.Open();
                return true;
            } catch
            {
                //Here you could add a message box or something like that so you know if there were an error.
                return false;
            }
        }

        private bool Close()
        {
            //This method closes the open connection
            try
            {
                //conn.Close();
                conServer1.Close();
                return true;
            } catch
            {
                return false;
            }
        }
        #endregion

        [Output("Ask", Color = Colors.Blue)]
        public IndicatorDataSeries Ask { get; set; }

        [Output("Bid", Color = Colors.Red)]
        public IndicatorDataSeries Bid { get; set; }

        //private MarketDepth _marketDepth;

        private static void ShiftDataSeries(IndicatorDataSeries dataSeries)
        {
            for (var i = 0; i < dataSeries.Count - 1; i++)
            {
                dataSeries[i] = dataSeries[i + 1];
            }
        }

        private static void FillDataSeries(IndicatorDataSeries dataSeries, double value, int startIndex, int count)
        {
            for (var i = startIndex; i < startIndex + count; i++)
                dataSeries[i] = value;
        }

        public override void Calculate(int index)
        {
            if (!IsRealTime)
                return;

            if (!double.IsNaN(Ask[index]))
            {
                ShiftDataSeries(Ask);
                ShiftDataSeries(Bid);
            }

            FillDataSeries(Ask, Symbol.Ask, index, 50);
            FillDataSeries(Bid, Symbol.Bid, index, 50);

            var spread = Math.Round((Symbol.Ask - Symbol.Bid) / Symbol.PipSize, 1);
            ChartObjects.DrawText("Spread label", "Spread:\t" + spread + " pips", StaticPosition.BottomRight);

            //menampilkan query
            string query = "INSERT INTO test2 (ask, bid) VALUES ('" + Symbol.Ask + "', '" + Symbol.Bid + "')";
            ChartObjects.DrawText("echo_query", "Query:\t" + query, StaticPosition.BottomLeft);

            try
            {
                if (this.Open())
                {
                    //Opens a connection, if successful; run the query and then close the connection.
                    MySqlCommand cmd = new MySqlCommand(query, conServer1);

                    string query1 = "INSERT INTO test2 (ask, bid) VALUES ('" + Symbol.Ask + "', '" + Symbol.Bid + "')";
                    ChartObjects.DrawText("echo_query", "Query:\t" + query1, StaticPosition.TopRight);
                    cmd.ExecuteNonQuery();
                    this.Close();
                }
            } catch
            {
            }
            return;
        }

        public void Insert(string table, string column, decimal value)
        {
            //Insert values into the database.
            //table = "test2";
            //column = "ask, bid";
            //value = Ask, Bid;

            //Example: INSERT INTO names (name, age) VALUES('John Smith', '33')
            //Code: MySQLClient.Insert("names", "name, age", "'John Smith, '33'");
            //string query = "INSERT INTO " + table + " (" + column + ") VALUES (" + value + ")";
            string query = "INSERT INTO test2 (ask, bid) VALUES ('" + Symbol.Ask + "', '" + Symbol.Bid + "')";

            try
            {
                if (this.Open())
                {
                    //Opens a connection, if successful; run the query and then close the connection.

                    MySqlCommand cmd = new MySqlCommand(query, conServer1);

                    cmd.ExecuteNonQuery();
                    this.Close();
                }
            } catch
            {
            }
            return;
        }
    }
}

 

@lamfete
Copyright © 2024 Spotware Systems Ltd. All rights reserved.
The services provided by Spotware Systems Ltd. are not available to citizens or residents of the USA. Neither is the information on our websites directed toward soliciting citizens or residents of the USA.