[RESOLVED] Migrate records from MySQL to Excel with c#

[telmessos]

Hi all,

I would like to learn how I can read a MySQL table and move the records to an Excel file. While replying, please consider that I am at the very beginning of c# and write everything "Idiot proof"

Thanks
telmessos

[nelo]

I would like to learn how I can read a MySQL table and move the records to an Excel file. While replying, please consider that I am at the very beginning of c# and write everything "Idiot proof"

Well...you're probably not an idiot. At least you had the sense to come to this site . Break the problem down into two:

1. Read the data from MySQL. I don't have any experience of this but just doing a quick Google search now returned some good results. It should be straightforward if you're familiar with ADO.NET. If you are not familiar with ADO.NET then do a Google search for ADO.NET.

2. Write the data to an excel file. I've done this and it also is straightforward using ADO.NET. Here's a useful link to get you started.
Using ADO.NET to read and write to Excel

A good approach would be to work on these as two separate components. The first component would just be concerned with reading the data from MySQL. It shouldn't care about what is going to happen to the data. The second component would be concerned about writing some data to Excel. It shouldn't be concerned about where the data is going to come from eventually. The components here can be separate methods in the same class or separate classes in the same library or application.

I hope this helps. You were not detailed in your original post so I really don't know what you already know and what you don't know about C#, the .NET framework and general object oriented programming.

[telmessos]

thanks for the reply. I needed a sample telling how the loop and write works. I would appreciate if you could show me a little example. thanks

[Arjay]

thanks for the reply. I needed a sample telling how the loop and write works. I would appreciate if you could show me a little example. thanks

As nelo mentioned you need to break the problem into two parts. The reading from mySql and then the writing into Excel.

To read from mySql, use MySqlConnector (see the sample code here: http://dev.mysql.com/doc/refman/5.0/...als-intro.html). Open a db connection, read the mysql records, and then store them in a generic list.

Next walk through the list and use nelo's example link to write to Excel.

If you have trouble using the MySqlConnector, post back.

[memeloo]

to read your MySql database you can use the MySQL Connector-Net

here's a short example but you should read the MySql.Data.chm file for more info

MySqlConnection conn = new MySqlConnection("server=localhost;uid=csharp;pwd=csharp;database=csharptest;charset=utf8");
conn.Open();
cmd = new MySqlCommand();
cmd.Connection = conn;
cmd.CommandText = "SELECT * FROM someTable";
MySqlDataReader reader = cmd.ExecuteReader();

while (reader.Read())
{
	Debug.WriteLine(reader.GetValue(0));
}

reader.Close();

conn.Close();

edit:
@Arjay: another parallel replaying :] I didn't see you post.

[telmessos]

It's been very helpful. I will work on it.

Thanks
telmessos

[telmessos]

I copied and pasted the code you gave me and got 9 error messages from VS.

Error 1 The type or namespace name 'MySqlConnection' could not be found (are you missing a using directive or an assembly reference?) C:\Users\ceyhun\Desktop\ilkprogram\sgk.cs 22 13 WindowsFormsApplication1
Error 2 The type or namespace name 'MySqlConnection' could not be found (are you missing a using directive or an assembly reference?) C:\Users\ceyhun\Desktop\ilkprogram\sgk.cs 22 40 WindowsFormsApplication1
Error 3 The name 'cmd' does not exist in the current context C:\Users\ceyhun\Desktop\ilkprogram\sgk.cs 24 13 WindowsFormsApplication1
Error 4 The type or namespace name 'MySqlCommand' could not be found (are you missing a using directive or an assembly reference?) C:\Users\ceyhun\Desktop\ilkprogram\sgk.cs 24 23 WindowsFormsApplication1
Error 5 The name 'cmd' does not exist in the current context C:\Users\ceyhun\Desktop\ilkprogram\sgk.cs 25 13 WindowsFormsApplication1
Error 6 The name 'cmd' does not exist in the current context C:\Users\ceyhun\Desktop\ilkprogram\sgk.cs 26 13 WindowsFormsApplication1
Error 7 The type or namespace name 'MySqlDataReader' could not be found (are you missing a using directive or an assembly reference?) C:\Users\ceyhun\Desktop\ilkprogram\sgk.cs 27 13 WindowsFormsApplication1
Error 8 The name 'cmd' does not exist in the current context C:\Users\ceyhun\Desktop\ilkprogram\sgk.cs 27 38 WindowsFormsApplication1
Error 9 The name 'Debug' does not exist in the current context C:\Users\ceyhun\Desktop\ilkprogram\sgk.cs 31 17 WindowsFormsApplication1

[memeloo]

I copied and pasted the code you gave me and got 9 error messages from VS.

it's nothing unusual if you haven't installed MySQL Connection-Net (I've posted the link i my previous post). you need to reference the right dll. just read the chm file or the tutorial that @Arjay posted in replay #4 and you'll know what's to do. I suppose you haven't download it, have you?

[telmessos]

I had many problems with mysql-connector-net. So I made an ODBC connection. But still I don't know the codes which will read from the database and write to Excel.

[memeloo]

I had many problems with mysql-connector-net.

what kind of problems did you have? it's set up within 2 minutes (download the zip file, extract, add the reference to the right dll file, paste the code, make adjustments, use)

So I made an ODBC connection. But still I don't know the codes which will read from the database and write to Excel.

what have you already done?

[telmessos]

I downloaded the zip, run it, add to references but still have the following errors on Visual c# Express.

Error 1 The type or namespace name 'MySqlConnection' could not be found (are you missing a using directive or an assembly reference?) C:\Users\ceyhun\Desktop\ilkprogram\sgk.cs 22 13 WindowsFormsApplication1
Error 2 The type or namespace name 'MySqlConnection' could not be found (are you missing a using directive or an assembly reference?) C:\Users\ceyhun\Desktop\ilkprogram\sgk.cs 22 40 WindowsFormsApplication1
Error 3 The name 'cmd' does not exist in the current context C:\Users\ceyhun\Desktop\ilkprogram\sgk.cs 24 13 WindowsFormsApplication1
Error 4 The type or namespace name 'MySqlCommand' could not be found (are you missing a using directive or an assembly reference?) C:\Users\ceyhun\Desktop\ilkprogram\sgk.cs 24 23 WindowsFormsApplication1
Error 5 The name 'cmd' does not exist in the current context C:\Users\ceyhun\Desktop\ilkprogram\sgk.cs 25 13 WindowsFormsApplication1
Error 6 The name 'cmd' does not exist in the current context C:\Users\ceyhun\Desktop\ilkprogram\sgk.cs 26 13 WindowsFormsApplication1
Error 7 The type or namespace name 'MySqlDataReader' could not be found (are you missing a using directive or an assembly reference?) C:\Users\ceyhun\Desktop\ilkprogram\sgk.cs 27 13 WindowsFormsApplication1
Error 8 The name 'cmd' does not exist in the current context C:\Users\ceyhun\Desktop\ilkprogram\sgk.cs 27 38 WindowsFormsApplication1
Error 9 The name 'Debug' does not exist in the current context C:\Users\ceyhun\Desktop\ilkprogram\sgk.cs 31 17 WindowsFormsApplication 1



Secondly, I made an ODBC connection with a code found on internet but I couldn't find a sample code on the internet to read from database using this connection and write it to an excel file.

Thanks.

[memeloo]

Error 1 The type or namespace name 'MySqlConnection' could not be found (are you missing a using directive or an assembly reference?) C:\Users\ceyhun\Desktop\ilkprogram\sgk.cs 22 13 WindowsFormsApplication1
Error 2 The type or namespace name 'MySqlConnection' could not be found (are you missing a using directive or an assembly reference?) C:\Users\ceyhun\Desktop\ilkprogram\sgk.cs 22 40 WindowsFormsApplication1

this means exacly what it says, you are missing

using MySql.Data.MySqlClient;

or you have to write

MySql.Data.MySqlClient.MySqlConnection conn = new MySqlConnection(...

Secondly, I made an ODBC connection with a code found on internet but I couldn't find a sample code on the internet to read from database using this connection and write it to an excel file.

it's a long time since I used some so your current code could help me to help you

[telmessos]

I wrote all of them still causes problems I don't know why.


Anyway, here is all I have for now:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.Odbc;

namespace WindowsFormsApplication1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

private void Form1_Load(object sender, EventArgs e)
{
OdbcConnection conn = new OdbcConnection("DSN=ceyhun");

conn.Open();
string SQL;
SQL = sqlkomutu.Text;
}
}
}

[memeloo]

now I remember. you're doing this:
http://www.geekpedia.com/tutorial139...-and-ODBC.html
wow, this is 10 times more coplicated then the simple mysql connector-net I wonder why you chose to go this way. at this point I can't help you. I don't want to install this thing.

btw. it's about time for you to start using the code tags

[telmessos]

I have no other choice. Because I could not make the other way work and everybody on the internet sends half information with missing details. I just opened c# 2 days ago and that's the reason why I wrote please send your replies with detailed codes (idiot proof )

Regards