• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1043
  • Last Modified:

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

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
star90
Asked:
star90
  • 7
  • 6
  • 3
1 Solution
 
sarabandeCommented:
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
 
star90Author Commented:
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
 
Kusala WijayasenaSoftware EngineerCommented:
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
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Kusala WijayasenaSoftware EngineerCommented:
MySQL driver for .NET (i.e MySQL ADO.NET Connector) : http://dev.mysql.com/downloads/connector/net/

-Kusala
0
 
star90Author Commented:
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
 
sarabandeCommented:
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
 
Kusala WijayasenaSoftware EngineerCommented:
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
 
star90Author Commented:
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
 
Kusala WijayasenaSoftware EngineerCommented:
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
 
star90Author Commented:
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
 
Kusala WijayasenaSoftware EngineerCommented:

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
 
star90Author Commented:
Thanks a lot for the answers!
0
 
star90Author Commented:
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
 
Kusala WijayasenaSoftware EngineerCommented:
Appreciate if you could open a new question for further matters, since this thread is already closed

-Kusala
0
 
sarabandeCommented:
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
 
star90Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 7
  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now