1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Help me to connect c# with mysql

Discussion in 'C#' started by charitharanasingha, Jan 20, 2011.

  1. #1
    Hi Guyz,

    I am developing a windows based system in c# that connecting to Mysql database. here is the code part i am using
    SEMrush
    string strProvider = "Data Source=xxx.xx.xx.xxx;Database=dbname;User ID=username;Password=password";
    MySqlConnection mysqlCon = new MySqlConnection(strProvider);
    mysqlCon.Open();

    I can connect to mysql database that do not implemented SSL on the server.

    Ex: http://www.mysite.com/cpanel - It's connecting
    But for something like https://www.mysite.com/cpanel - it's cannot connected and it's saying "Unable to connect to any of the specified MySQL hosts."

    please help me to resolve this.

    Many thanks
     
    charitharanasingha, Jan 20, 2011 IP
    SEMrush
  2. MuzD

    MuzD Peon

    Messages:
    167
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    0
    #2
    Try using the webconfig first.

    1) Connection String
    <connectionStrings>
      <add name="MySQLConnStr" connectionString="DRIVER={MySQL ODBC 3.51 Driver};Database=YourDatabase;Server=localhost;UID=YourUsername;PWD=YourPassword;"/>
    </connectionStrings>
    Code (markup):
    note:please make necessary changes to Database, Server, UId and PWD info.




    2)Test printing some data

    try
    {
        using(OdbcConnection connection = new OdbcConnection(ConfigurationManager.ConnectionStrings["MySQLConnStr"].ConnectionString))
        {
            connection.Open();
            using(OdbcCommand command = new OdbcCommand("SELECT name FROM test_users", connection))
            using(OdbcDataReader dr = command.ExecuteReader())
            {
                while(dr.Read())
                    Response.Write(dr["name"].ToString() + "<br />");
                dr.Close();
            }
            connection.Close();
        }
    }
    catch(Exception ex)
    {
        Response.Write("An error occured: " + ex.Message);
    }
    
    Code (markup):

    3) This should atleast tell you where the issue is if it doesnt work.
     
    MuzD, Jan 26, 2011 IP
  3. charitharanasingha

    charitharanasingha Member

    Messages:
    89
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    41
    #3
    Hi MuzD,

    thanks for your reply, and im just wondering where's the connection string part in windows systems, as i know web.config file can be used for web based systems only, i am developing a windows based system that can activly connect to mysql {Actually it's sending request in every second to my sql server}
     
    charitharanasingha, Jan 26, 2011 IP
  4. MuzD

    MuzD Peon

    Messages:
    167
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    0
    #4
    Sorry, I meant App.config file.
    Why do you need to send a request every second?
     
    MuzD, Jan 26, 2011 IP
  5. charitharanasingha

    charitharanasingha Member

    Messages:
    89
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    41
    #5
    because i have to check whether someone added a question through site and if available i am giving a popup to the client who have installed my system(actually i am using a timer for that) do you have any faster wy for this other than using a timer, plz help me
     
    charitharanasingha, Jan 26, 2011 IP
  6. charitharanasingha

    charitharanasingha Member

    Messages:
    89
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    41
    #6
    ahh okay i will give a try thanks for that. i am requesting every second because i have to check whether someone added a question thru the site and if it's avialable i am giving a pop up display to the client that my system installed, is there any fast way other than doing that(i have used a timer, in the timer_tick() event i am checking)
     
    charitharanasingha, Jan 26, 2011 IP
  7. charitharanasingha

    charitharanasingha Member

    Messages:
    89
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    41
    #7
    sorry i put the same thing twice, i didnt see my reply on the site, that's y i put that again
     
    charitharanasingha, Jan 26, 2011 IP
  8. MuzD

    MuzD Peon

    Messages:
    167
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    0
    #8
    Let me think about it, I might trying creating a service for this that runs on the client.
    Let me think of something, I feel if you keep connecting to your DB every second you might one day find it too resource consuming.

    Are you also closing all connections and destroying them as soon as you are done.
     
    MuzD, Jan 26, 2011 IP
  9. charitharanasingha

    charitharanasingha Member

    Messages:
    89
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    41
    #9
    i am openning the connection once, then checking every second whether the row has been added to the db without closing the connection, and if someone pause or stop getting alerts i am closing the connection
     
    charitharanasingha, Jan 26, 2011 IP
  10. Mr.AD

    Mr.AD Member

    Messages:
    89
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    41
    #10
    Did you use MySql Connector for .NET ? Are you sure it supports SSL ?
     
    Mr.AD, Jan 26, 2011 IP
  11. charitharanasingha

    charitharanasingha Member

    Messages:
    89
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    41
    #11
    Yeah i am using MySql Connector for .NET and i am not much aware on whether it's supports SSL thanks for da advise, i will look for that too
     
    charitharanasingha, Jan 26, 2011 IP
  12. MuzD

    MuzD Peon

    Messages:
    167
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    0
    #12
    Any luck? It will be good to see what caused the issue?
     
    MuzD, Jan 26, 2011 IP
  13. Mr.AD

    Mr.AD Member

    Messages:
    89
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    41
    #13
    Mr.AD, Jan 26, 2011 IP
  14. roxcon

    roxcon Member

    Messages:
    73
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    41
    #14
    hey machan i'm gayan
    did you download and refer mysql .net connector?

    if so this will work for sure
    <add name="Cnname" connectionString="Server=MySqlServer;Database=youdb;Uid=beUser;Pwd=password;" providerName="MySql.Data.MySqlClient"/>
    Code (markup):
    this blog says how to
    enable SSL
     
    Last edited: Jan 26, 2011
    roxcon, Jan 26, 2011 IP
  15. charitharanasingha

    charitharanasingha Member

    Messages:
    89
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    41
    #15
    Hi all,

    Bad News, actually i have remove the SSL from the server to check, and still it's not connecting, my concern is i can connect to the localhost mysql datatbase, and also the outside IP s database too. but they also in srilanka, i am trying to connect to a server in Canada.

    hmm this like a headache, because even i remove the SSL from the server to get my system connect to it, customer is waiting to release the system.

    Thanks gayan, Mr AD, roxcon. and all replied i am going to solve this at least tonight without sleep, if i could i will post the solution here thanks all
     
    charitharanasingha, Jan 27, 2011 IP
  16. MuzD

    MuzD Peon

    Messages:
    167
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    0
    #16
    Hi Charitharana,

    How is it going with this? Any good news!

    Cheers,
    Muz
     
    MuzD, Jan 31, 2011 IP
  17. charitharanasingha

    charitharanasingha Member

    Messages:
    89
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    41
    #17
    No men, but now i am getting the error saying like "Connection lost at the initial packet is transferring" looks like i connected, but still something is there that prevent me to connect that :(
     
    charitharanasingha, Jan 31, 2011 IP
  18. MuzD

    MuzD Peon

    Messages:
    167
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    0
    #18
    Try checking your firewall connections, or simply create a connection to another server with the same code and see if it works. If it does then there is a network issue or remote connectivity issue with the server you are trying to connect. If it doesn't connect to the new server then its the code.
     
    MuzD, Feb 2, 2011 IP
  19. charitharanasingha

    charitharanasingha Member

    Messages:
    89
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    41
    #19
    Hi MuzD,

    Still the problem there, as i said earlier it's connecting other servers, but not to this, as i get some knowledge from one of my system administrator he said, if we can telnet (Telnet [IP] [3306]) to that perticular server and connect definitly it should be able to connect from my system. i did it and it's not connecting, so i am waiting till the hosting party give me a response

    Thanks for your concern on this MuzD
     
    charitharanasingha, Feb 2, 2011 IP
  20. breezewood15

    breezewood15 Peon

    Messages:
    2
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #20
    my connection string
    in Web.config
    <add name="ABCConnectionString" connectionString="port=3306;Character Set=utf8;server=192.168.1.2;User Id=test;password=test;Persist Security Info=True;database=test" providerName="MySql.Data.MySqlClient"/>
    in dbConnection.cs
    private MySqlDataAdapter myAdapter;
    private MySqlConnection conn;

    public dbConnection()
    {
    myAdapter = new MySqlDataAdapter();
    conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["ABCConnectionString"].ConnectionString);
    }

    private MySqlConnection openConnection()
    {
    if (conn.State == ConnectionState.Closed || conn.State == ConnectionState.Broken)
    {
    conn.Open();
    }
    return conn;
    }

    public DataTable executeSelectQuery(String _query, MySqlParameter[] sqlParameter)
    {
    MySqlCommand myCommand = new MySqlCommand();
    DataTable dataTable = new DataTable();
    dataTable = null;
    DataSet ds = new DataSet();
    myCommand.Connection = openConnection();
    myCommand.CommandText = _query;
    myCommand.Parameters.AddRange(sqlParameter);
    myCommand.ExecuteNonQuery();
    myAdapter.SelectCommand = myCommand;
    myAdapter.Fill(ds);
    dataTable = ds.Tables[0];
    return dataTable;
    }
     
    breezewood15, Feb 7, 2011 IP