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

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

Quote:

---

Originally Posted by telmessos

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.

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

Quote:

---

Originally Posted by 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

---

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.

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

Code:

---

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.

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

Thanks
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

Quote:

---

Originally Posted by telmessos

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?

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.

Quote:

---

Originally Posted by telmessos

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)

Quote:

---

Originally Posted by telmessos

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?

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.

Quote:

---

Originally Posted by telmessos

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

Code:

---

using MySql.Data.MySqlClient;

---

or you have to write

Code:

---

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

---

Quote:

---

Originally Posted by telmessos

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 :p

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;
}
}
}

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 :eek: 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 ;)

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

if you decide to use the mysql connector-net there's a chance I can guide otherwise you have to wait for someone else to help you :p

I changed it to mysql-connector-net I don't have the previous error but a new one when I copy and paste the first code you gave, I get the following errors:

the name 'cmd' does not exist in the current context

Quote:

---

Originally Posted by telmessos

the name 'cmd' does not exist in the current context

---

oops, sorry, I forgot to copy one more line last time, here's my test class again:

Code:

---

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

                while (reader.Read())
                {
                        Debug.WriteLine(reader[0] + " -- " + reader[1]); // 0 and 1 because I've got two collumns in my test table
                }

                reader.Close();
                conn.Close();
                     
                      reader.Dispose();
                      conn.Dispose();
        }
}

---

this is only a sample so there is no try/catch and your app will crash if an error occurs

it seems to have no errors given. If I put the connection code on the form load section. Is it global? And how do I close connection at the end of the program? (asking too much :))

Quote:

---

Originally Posted by telmessos

it seems to have no errors given. If I put the connection code on the form load section. Is it global?

---

yes, if you make it a filed it will be class-wide accessible

Quote:

---

Originally Posted by telmessos

And how do I close connection at the end of the program? (asking too much :))

---

do this:

Code:

---

reader.Close();
conn.Close();

reader.Dispose();
conn.Dispose();

---

thanks ;)