Solved

Need a code example for Ado.net to use with VC++ 2010 with MySQL database

Posted on 2011-03-09
16
991 Views
Last Modified: 2012-05-11
Hi,
I am new to databases and VC++.
II have installed MYSQL on my PC and I also have VC++ 2010 on the same machine.
I want to try to use an ADO.net to connect between the VC++ and MYSQL.
I understand that ADO.net has the advantage of using a DataSet over ODBC.
I need to create long tables from VC++ to the MYSQL and to update the values of the tables.
The MySQL and the VC++ are running on the same machine.

1) Is it better to use ADO.net than ODBC?
2) I was not able to find a working example with VC++ 2010 does anyone have a working example?
3) Also an example with ODBC can help.
4) I installed the MYSQL, the ADO connector and the MYSQL Workbench what else is needed for running the code?
 Thanks!
0
Comment
Question by:star90
  • 7
  • 6
  • 3
16 Comments
 
LVL 32

Expert Comment

by:sarabande
ID: 35087138
as far as i know ADO only can be used for ms access and sql server, but i can be wrong.

1) ADO is known as to be faster than ODBC as it works via COM and it doesn't need special ODBC driver. i have only good experiences with odbc and if speed is a problem i would use native interface to mysql rather than ADO.

2) as told, i don't think mysql can be run via ADO.

3) there are many odbc samples with vs versions up to VS2008. i don't think those are missing on VS2010 beside you have express version.

4) see 1 and 2

Sara
0
 

Author Comment

by:star90
ID: 35087663
Hi Sara,
MySQL has a connector for ADO.net:
 
http://dev.mysql.com/downloads/connector/net/

referring to ODBC I  see examples for C# and VB in the help for Visual 2010.
Can you please specify a location of  an example for ODBC usage with Visual C++?
Is it possible to combine a code for C# inside a C++ program?
0
 
LVL 11

Expert Comment

by:Kusala Wijayasena
ID: 35089617
Hi,

ODBC is a generic mechanism that used to make database connection. ADO.NET will provide more database specific connection using power of .NET infrastructure

Check this out for VC++/ADO.NET : http://www.functionx.com/mysqlnet/vcnet/Lesson01.htm

-Kusla  
0
 
LVL 11

Expert Comment

by:Kusala Wijayasena
ID: 35089632
MySQL driver for .NET (i.e MySQL ADO.NET Connector) : http://dev.mysql.com/downloads/connector/net/

-Kusala
0
 

Author Comment

by:star90
ID: 35089863
Hi Kusala,
Thanks for the link.
I have problems to transform it to a working example.
For example:
The line
public: MySqlConnection(String *connectionString);

Do I need to write a class in order to use it? What is the connection string?
0
 
LVL 32

Expert Comment

by:sarabande
ID: 35092631
there are mfc samples  CATALOG, CATALOG2, DBFETCH, ODBCINFO, STDREG coming with msdn. type 'samples' in the help index of visual studio help and you should get a link for download or cd.

The MySqlConnection is a class of the (class) library kusala has given a link to. you would use it to open a connection to a mysql database. you can use the instance of MySqlConnection then for further calls to that library.

The connection string tells what database should be opened. in the simplest form it is only the name of a datasource which you registered using the odbc applet of system settings (or call odbcad32.exe from 'Run'). but you also could specifiy details like dbms, schema, user, password, driver via a connection string. look at the help of the class library to learn more.
 
Sara
0
 
LVL 11

Accepted Solution

by:
Kusala Wijayasena earned 500 total points
ID: 35093273
Here is code sample for connect MySQL using Connector/Net (in C++/CLI)

using namespace System::Data;
using namespace MySql::Data::MySqlClient;

.......
.......

MySqlConnection ^conn = gcnew MySqlConnection("Server=localhost;Database=mydb;Uid=root;Pwd=password@123;");
conn->Open();
DataTable ^dt = gcnew DataTable();
MySqlDataAdapter ^adapter = gcnew MySqlDataAdapter("select * from tbltest", conn);
adapter->Fill(dt);

Open in new window


For connection strings, please refer : http://www.connectionstrings.com/mysql

-Kusala

0
 

Author Comment

by:star90
ID: 35094723
Hi Kusala,
Thanks for the example.
I was able to connect to the database.
However I have a problem to add columns to my tables and to update the database from the DataTables
I get errors with
dt->Rows->Add(dt->NewRow());
int m_int=adapter->Update(dt);
the error image is attached.

1) How should I use the Update command to update the DataTable to the Database?
2) How can I add Columns?
The command below does not work:
//dt->Columns->Add(dt->NewColumn());

3) Do I need to declare Colum 0 as a primary key?
I want to build a table like:
key value1 value2
0    343       55
1   3477      44
2  56565     22

Thanks

////////////////////////////////////////////////////////////////////////////////////////////
The code:


#include "stdAfx.h"
using namespace System;
using namespace System::Data;
using namespace MySql::Data::MySqlClient;
using namespace std;

int main()
{

MySqlConnection ^conn = gcnew MySqlConnection("Server=localhost;Database=test;Uid=root;Pwd=ttt;Port=3307;");
conn->Open();
DataTable ^dt = gcnew DataTable();
MySqlDataAdapter ^adapter = gcnew MySqlDataAdapter("select idtable2 from table2", conn);
adapter->Fill(dt);
Console::WriteLine(dt->Rows[3][0]->ToString());

dt->Rows->Add(dt->NewRow());
dt->Rows[5][0]=5555;

//dt->Columns->Add(dt->NewColumn());


//int m_int=adapter->Update(dt);


Console::WriteLine(dt->Rows[5][0]->ToString());
return 0;
}
error.bmp
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 11

Expert Comment

by:Kusala Wijayasena
ID: 35106136
MySqlConnection ^conn = gcnew MySqlConnection("Server=localhost;Database=mydb;Uid=root;Pwd=password@123;");
conn->Open();

MySqlCommand ^cmd = gcnew MySqlCommand();
cmd->Connection = conn;
cmd->CommandType = System::Data::CommandType::Text;
    
//Create table
cmd->CommandText = "CREATE TABLE mytbl(id INT PRIMARY KEY, value1 INT, value2 INT)";
cmd->ExecuteNonQuery();

//Insert records
cmd->CommandText = "INSERT INTO mytbl(id, value1, value2) VALUES(0, 343, 55)";
cmd->ExecuteNonQuery();

//Update record
cmd->CommandText = "UPDATE mytbl SET value2 = 66 WHERE id = 0";
cmd->ExecuteNonQuery();

//Alter table
cmd->CommandText = "ALTER TABLE mytbl ADD COLUMN value3 INT";
cmd->ExecuteNonQuery();

conn->Close();

Open in new window


-Kusala
0
 

Author Comment

by:star90
ID: 35109320
Thanks its working!.
Two more questions:

1) When I read data from  the database do I need always to read it inside a Datatable?
or can I read it directly from the Databse?

2)
Assuming I have an adapter:
MySqlDataAdapter ^adapter = gcnew MySqlDataAdapter("select * from tbltest", conn);

If I declare new value for the adapter
adapter = gcnew MySqlDataAdapter("select * from mytbl", conn);

Do I need to dispose of the old one that was declared with gcnew? and how do I do it?
0
 
LVL 11

Expert Comment

by:Kusala Wijayasena
ID: 35126939

1. No it is not necessary to fill the rows into a datatable and access it. You can use MySqlDataReader for access rows in sequential manner

MySqlCommand ^cmd = gcnew MySqlCommand();
cmd->Connection = conn;
cmd->CommandType = System::Data::CommandType::Text;
cmd->CommandText = "SELECT * FROM mytbl";
MySqlDataReader ^reader = cmd->ExecuteReader();

while(reader->Read())
{
     //Now you can access row data by column index (like: reader[0], reader[0] .. etc)
}

Open in new window


2. You can reuse the DataAdapter

DataTable ^dt1 = gcnew DataTable();
DataTable ^dt2 = gcnew DataTable();

MySqlDataAdapter ^adapter = gcnew MySqlDataAdapter("select * from mytbl", conn);
adapter->Fill(dt1);

adapter->SelectCommand->CommandText = "SELECT * FROM mytbl2";
adapter->Fill(dt2);

Open in new window


-Kusala

0
 

Author Comment

by:star90
ID: 35131915
Thanks a lot for the answers!
0
 

Author Comment

by:star90
ID: 35132569
I wrote the code and my program is transferring the Data to the MYSQL .

I have however another problem, its working too slow.
I open the connection to the MYSQL nd my program calls a function that uses these 2 lines each time to  write a line to the Database:
//Insert records
cmd->CommandText = "INSERT INTO mytbl(id, value1, value2) VALUES(0, 343, 55)";
cmd->ExecuteNonQuery();

However I see that its running very slow.
My program reads the Data from text files and write them to the MYSQL
To put 80000 lines of Data takes me like 45 minutes .

Is there another way to do it?
Maybe to write many lines in one time to the MYSQL instead of one line at a time?

The Data looks like
int float float
int float float
.
.
Thanks.
0
 
LVL 11

Expert Comment

by:Kusala Wijayasena
ID: 35134123
Appreciate if you could open a new question for further matters, since this thread is already closed

-Kusala
0
 
LVL 32

Expert Comment

by:sarabande
ID: 35137053
i found this:

INSERT statements that use VALUES syntax can insert multiple rows. To do this, include multiple lists of column values, each enclosed within parentheses and separated by commas. Example:

  INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

The values list for each row must be enclosed within parentheses.


But i doubt it will be much faster.

the fastest way to insert data should be the LoadData which is decribed at

http://www.chen.net/~hubt/blog/2006/03/mysql-insert-performance.html

Sara
0
 

Author Comment

by:star90
ID: 35137369
Hi sarabande,
Thanks a lot !!
I think it will solve the speed problem.
By the way I saw the link below which is also interesting but your solution is much better.
http://dev.mysql.com/doc/refman/5.0/en/insert-speed.html
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Container Orchestration platforms empower organizations to scale their apps at an exceptional rate. This is the reason numerous innovation-driven companies are moving apps to an appropriated datacenter wide platform that empowers them to scale at a …
The goal of the video will be to teach the user the difference and consequence of passing data by value vs passing data by reference in C++. An example of passing data by value as well as an example of passing data by reference will be be given. Bot…
The viewer will be introduced to the member functions push_back and pop_back of the vector class. The video will teach the difference between the two as well as how to use each one along with its functionality.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now