Solved

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

Posted on 2011-03-09
16
1,010 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 3
16 Comments
 
LVL 34

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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
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 34

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
 
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 34

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Foreword This article was written many years ago, in the days when PHP supported the MySQL extension (http://php.net/manual/en/function.mysql-connect.php).  Today (http://php.net/manual/en/migration70.removed-exts-sapis.php) you would not use MySQL…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
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 learn additional member functions of the vector class. Specifically, the capacity and swap member functions will be introduced.

696 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