Generate JSON from MySQL using C#

I would like to share this article because I try to search some related posts or articles and found out it was rare when compare with other topics. Hence, I do it by myself and hope you can get what you want from here.

Before start write your coding, please make sure you add reference Newtonsoft.Json. If you haven't get it, please download from here.

Define your mySQL connection first and write your query.

1. MySqlConnection conn = new MySqlConnection(
2. "server=localhost;" +
3. "uid=xx;" +
4. "pwd=xx;" +
5. "database=xx;");
6. MySqlCommand comm = new MySqlCommand();
8. conn.Open();
9. comm = conn.CreateCommand();
10. comm.CommandText = "SELECT name FROM student";
11. MySqlDataReader reader = comm.ExecuteReader();
12. List<Student> eList = new List<Student>();

Don't forget to build constructor to get and set.

1. public class Student
2. {
3. public string Name;
4. }

Read database data line by line.

1. while (reader.HasRows)
2. {
3. if (reader.Read())
4. {
5. Student e = new Student();
6. e.Name = Convert.ToString(reader["Name"]);
7. eList.Add(e);
8. }
9. else
10. {
11. break;
12. }
13. }

Convert to JSON format.

1. System.Web.Script.Serialization.JavaScriptSerializer oSerializer = new System.Web.Script.Serialization.JavaScriptSerializer();
2. string ans = oSerializer.Serialize(eList);
3. string script = "{\"Employee\": " + ans + "}";
4. ClientScriptManager cs = Page.ClientScript;
5. cs.RegisterStartupScript(Page.GetType(), "JSON", script, true);
6. Response.ContentType = "application/json";
7. Response.Write(script);

Check your aspx file in browser.

Sharing is caring. =)