Download source and binaries - 3.1 MB

Introduction

A few days ago, after moving a hosted MySQL database to a new hosting, I got a nasty surprise: the new hosting does not accept connections to the database from outside it.

This was a problem for me, since I was using an application (developed in C#) that connects via ODBC to this database.

The way I found to get the information was: create some PHP files that, hosted on my server, will connect locally to the database and return the information in JSON format. Then I would need to change my C# application for consuming those JSON.

I want to share with you the whole process.

Json.NET

I found this great framework to work with JSON. The first thing to do is to install it, and the easiest way is NuGet. To do this, open Visual Studio, launch Package Manager and write this:

PM> Install-Package Newtonsoft.Json

Now we're ready to start working!

MySQL Table structure

I have created a very simple table with a few rows.

This is the script I have used to create it.

CREATE TABLE 'Users' (
  'Id' INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  'FirstName' VARCHAR( 50 ) NOT NULL ,
  'LastName' VARCHAR( 50 ) NOT NULL ,
  'Email' VARCHAR( 100 ) NOT NULL ,
  PRIMARY KEY ( 'Id' ) ,
  FULLTEXT (
    'FirstName' ,
    'LastName' ,
    'Email'
  )
) TYPE = MYISAM ;

Then, I have inserted a few rows. That's the script:

INSERT INTO 'Users' ('FirstName' , 'LastName' , 'Email' )
VALUES 
  ('Pepe', 'Pardo', 'pepe.pardo@gmail.com'),
  ('Manolo', 'Locomia', 'manolo.locomia@gmail.com'),
  ('Diplo', 'Docus', 'diplo.docus@gmail.com');

PHP script

The next step is to create a php file that connects to the database, execute a query and return the result in JSON format.

Foo.php

<?php
	// Set your database configuration here
	$DatabaseServer   = "localhost";
	$DatabaseUser     = "testUser";
	$DatabasePassword = "testPassword";
	$DatabaseName     = "myDBName";
 
	// Set a connection to the database
	$mysqli = new mysqli($DatabaseServer, $DatabaseUser, $DatabasePassword, $DatabaseName);
	
	// Try to connect. Die if error
	if ($mysqli->connect_errno) 
	{
		printf("Connect failed: %s\n", $mysqli->connect_error);
		exit();
	}
 
	// Set a resultset. For testing we are goint to return the full table (3 rows)
	$result = $mysqli->query("SELECT Id, FirstName, LastName, Email FROM Users;");
 
	// Iterate the resultset to get all data
	while($row = mysqli_fetch_assoc($result)) 
	{
		$rows[] = $row;
	}
	
	// Close the resultset
	$result->close();
 
	// Close the database connection
	$mysqli->close();
	
	// Returns the JSON representation of fetched data
	print(json_encode($rows, JSON_NUMERIC_CHECK));
?>

Keep in mind to use mysqli_fetch_assoc instead of mysql_fetch_array because otherwise would obtain duplicated values in the JSON string.

Note that from PHP 5.3.3 on, you can use the flag JSON_NUMERIC_CHECK for auto-converting numbers. I have used it because I want that the field Id being returned as an int.

Now is the time to get the PHP file server. If you try to run it from a webbrowser, you will get something like this:

So, we have solved the server side. Now we will need to code the application in C # to show the query.

Desktop Application

Once installed Json.NET as described above, we are ready to start coding.

First, create a User class that will serve to store each object returned.

User.cs

using Newtonsoft.Json;
 
namespace JsonToDataListView
{
    class User
    {
        [JsonProperty("Id")]
        public int Id { get; set; }
 
        [JsonProperty("FirstName")]
        public string Name1 { get; set; }
 
        [JsonProperty("LastName")]
        public string Name2 { get; set; }
 
        [JsonProperty("Email")]
        public string EmaiAddress { get; set; }
    }
}

The only special thing in this class is the use of JsonPropertyAttribute to match each JSON key with the class attribute.

Form1.cs

using System;
using System.Net;
using System.Windows.Forms;
using System.Collections.Generic;
using Newtonsoft.Json;
 
namespace JsonToDataGridView
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
 
        private void button1_Click(object sender, EventArgs e)
        {
            WebClient wc = new WebClient();
            var json = wc.DownloadString(textBox1.Text);
 
            List<User> users = JsonConvert.DeserializeObject<List<User>>(json);
 
            dataGridView1.DataSource = users;
        }
    }
}

First, we will create a WebClient object, allowing us to connect to the PHP page that we have created before.

Then, we use the DownloadString function to download the page content into a variable. At this point, we will have in our application all the information. Now we just need to make it easy to read. And this is when Json.NET is going to help us a lot.

Create a list of User objects from the JSON deserialization. We will use the static class DeserializeObject and the function JsonConvert, passing as parameter the full result obtained from the Web.

Now we just have to print the data in the DataGridView using the DataSource property.

Displaying JSON from PHP into a DataGridView using Json.NET