Help me to connect c# with mysql

Hi Guyz,

I am developing a windows based system in c# that connecting to Mysql database. here is the code part i am using

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

 

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):

notelease 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.

 

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}

 

Sorry, I meant App.config file.
Why do you need to send a request every second?

 

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

 

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)

 

sorry i put the same thing twice, i didnt see my reply on the site, that's y i put that again

 

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.

 

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

 

Did you use MySql Connector for .NET ? Are you sure it supports SSL ?

 

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

 

Any luck? It will be good to see what caused the issue?

 

I have never used mysql with SSL connection so I can not talk about that more. So, you can read this article

http://dev.mysql.com/doc/refman/5.0/en/connector-net-tutorials-ssl.html

 

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

 

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

 

Hi Charitharana,

How is it going with this? Any good news!

Cheers,
Muz

 

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

 

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.

 

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

 

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;
}