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
Printable View
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
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
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
edit: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();
@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
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.
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.
this means exacly what it says, you are missing
or you have to writeCode:using MySql.Data.MySqlClient;
it's a long time since I used some so your current code could help me to help you :pCode:MySql.Data.MySqlClient.MySqlConnection conn = new MySqlConnection(...
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