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
$DatabaseServer = "localhost";
$DatabaseUser = "testUser";
$DatabasePassword = "testPassword";
$DatabaseName = "myDBName";
$mysqli = new mysqli($DatabaseServer, $DatabaseUser, $DatabasePassword, $DatabaseName);
if ($mysqli->connect_errno)
{
printf("Connect failed: %s\n", $mysqli->connect_error);
exit();
}
$result = $mysqli->query("SELECT Id, FirstName, LastName, Email FROM Users;");
while($row = mysqli_fetch_assoc($result))
{
$rows[] = $row;
}
$result->close();
$mysqli->close();
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.