question about mysql and c# - Printable Version

question about mysql and c# - Printable Version

+- LCKB (https://lckb.dev/forum)
+-- Forum: ** OLD LCKB DATABASE ** (https://lckb.dev/forum/forumdisplay.php?fid=109)
+--- Forum: Programmers Gateway (https://lckb.dev/forum/forumdisplay.php?fid=196)
+---- Forum: Coders Talk (https://lckb.dev/forum/forumdisplay.php?fid=192)
+---- Thread: question about mysql and c# (/showthread.php?tid=2156)

- soryjero - 07-08-2013

hello,

i am coding a tool and i would like how to get records from a table.

i have been "googling" and i have found this structure:

SqlConnection sqlconn = 2 SqlConnection(connectionStr);
SqlCommand sqlcomm = 2 SqlCommand("SELECT * FROM OrderDetail", sqlconn);
SqlDataReader reader = sqlcomm.ExecuteReader();

const int UNITPRICE = 0;
const int QUANTITY = 1;

while (reader.Read())
{
float unitPrice = reader.getFloat(UNITPRICE);
int quantity = reader.getInt(QUANTITY);

// Do whatever with the values.
}

reader.Dispose();
sqlcomm.Dispose();
sqlconn.Dispose();

but i dont know if it works and of course, i wouldnt like to work on it and finally, it doesnt work. so i would like you could help me.

thanks in advance.

P.S. if there is another method easier than i have found, please tell me or if this method is good and i have to modify it tell me too please

- someone - 07-08-2013

Suggest using   MySqlClient, instead of SqlClient (since SqlClient is for MSSQL)

You first need to get the Mysql Connector from this site:

2

Install it: Then start your VC# - >RightClick on your project ->Add reference->.NET->Mysql.Data->OK

The connection string is this:

server=YourMysqlServer;database=YourDB;uid=YourMysqlUser;password=YourMysqlUserPassword
Your code:

using MySql.Data.MySqlClient;
...
string dbServer = "127.0.0.1";
string dBase = "testdb";
string dbuser = "root";
string dbPass = "test";
...
try{
    //Connecting to mysql
    string strConnection = string.Format("server={0};database={1};uid={2};password={3}", dbServer, dBase, dbuser, dbPass);
MySqlConnection dbConnerction = new MySqlConnection(strConnection);
   dbConnerction.Open();

   //executing a query
    string query = "SELECT * FROM test_table";
    MySqlCommand dbCommand = new MySqlCommand(query, dbConnerction);
    MySqlDataReader dbReader = dbCommand.ExecuteReader();

    //Reading the data
    while (dbReader.Read()){
          string stuff = dbReader["test_column1"].ToString();
float f= dbReader.GetFloat("test_column2");
    }

    //closing the reader
    dbReader.Close();
    dbCommand.Dispose();

   //Close the connection
    dbConnerction.Close();
}catch (Exception ex){
                MessageBox.Show(ex.Message, "SQL ERROR");
}
You can use OleDb or ODBC if you want to connect to the MySql as a alternative.

- Wizatek - 07-08-2013

While coding the emulator i noticed that in case of a exception the mysql connection doesnt close in a try catch block.

And eventually will flood the connection pool.

I suggest to work like this

using(MySqlConnection dbConnerction = new MySqlConnection(strConnection))
{
dbConnerction.Open();

//executing a query
string query = "SELECT * FROM test_table";
MySqlCommand dbCommand = new MySqlCommand(query, dbConnerction);
MySqlDataReader dbReader = dbCommand.ExecuteReader();

//Reading the data
while (dbReader.Read()){
string stuff = dbReader["test_column1"].ToString();
float f= dbReader.GetFloat("test_column2");
}

}
This way everything inside the using block will always get disposed when leaving the using block.

- soryjero - 07-08-2013

thanks guys. tomorrow i will try it and if i get it i will notice here.

thanks Smile

- soryjero - 07-09-2013

works perfectly.

thanks again guys Smile

#solved