WPF datagrid binding with remote/online mysql in c#

Question

Hello i am trying to connect my datagrid in WPF with c#, my database is mysql in remote server and it is in shared hosting account the ip of shared box server is like xx.xxx.xxx.xxx and my account is like yyyyyyyw so the direct ip is http://xx.xxx.xxx.xxx/~yyyyyyyw/ how can i connect and get the data from my database?

---

I am working in VS10 with DevExpress Tools if anyone is familiar with this it would be more useful.

Answer 1

There are several possibilities to achieve your goal - given you have sufficient access to the specified server. You could - for example - set up the MySQL database that your user can access the DB directly via ADO.Net (with MySQL Connector/Net, https://dev.mysql.com/downloads/connector/net/).

If this is not possible, you could write some sort of PHP-Script, which runs server-side and gets the desired data from your DB, stores it in some kind of schema (XML, JSON, Base64 encoded binary data - whatever you'd like) and returns it. You'd just have to make sure that you protect the channel appropriately, for example with password protection (but no plain text, of course - the password should definitively be encrypted and best of with some sort of handshake, that one can't simply copy your request and get the data - or you could use HTTPS). Then you can just call http://xx.xxx.xxx.xxx/~yyyyyyyw/getdata.php?id=1 from your application and it returns something like

<root>
    <dataset id='1'>
        <timestamp>
            2013-12-05T11:52:00
        </timestamp>
        <text>
            This is a sample dataset!
        </text>
    </dataset>
</root>

This could for example be a PHP-script like the following (please note, that this is a very basic example completely disregarding security concerns)

<?php
    $id = $_GET['id'];

    $mysql = mysql_connect('localhost', 'root', '12345');
    mysql_select_db('yourdatabase', $mysql);
    $query = "SELECT id,timestamp,text FROM dataset WHERE id=$id";

    $result = mysql_query($query, $mysql);

    echo "<root>\r\n";

    while($row = mysql_fetch_row($result))
    {
        $rowid = $row[0];
        $rowtimestamp = $row[1];
        $rowtext = $row[2];

        echo "<dataset id='$rowid'>\r\n";
        echo "    <timestamp>\r\n";
        echo "        $rowtimestamp\r\n";
        echo "    </timestamp>\r\n";
        echo "    <text>\r\n";
        echo "        $rowtext\r\n";
        echo "    </text>\r\n";
        echo "</dataset>\r\n";
    }

    echo "</root>";
?>

By adjusting the URL-Request and the SQL-Query to your needs, you can cover more complex cases, like calling the URL like http://xx.xxx.xxx.xxx/~yyyyyyyw/getdata.php?from=2013-01-01T00:00:00&to=2013-12-31T23:59:59 and adjusting the script to

$from = $_GET["from"];
$to = $_GET["to"];
...
$query = "SELECT id,timestamp,text FROM dataset WHERE timestamp>='$from' AND timestamp<='$to'";

This would for example give you all datasets written within the year 2013.

From you application you can parse this XML (please see How does one parse XML files? or Parse XML document in C#) to objects (e.g. of class MyDataSet), store them in an

IList<MyDataSet>

and let this be returned by a property of an object, for example your main window (Warning: No good practice)

class MainWindow : Window
{
    IList<MyDataSet> _dataSets;

    public IList<MyDataSet> DataSets
    {
        get {
            return dataSets;
        }
    }
}

and then set the ItemSource property of the DataGrid to the list. With the AutoGenerateColumns property of the DataGrid a column for each publicly accessible Property of MyDataSet is generated, if you don't like this and want to have the full control over the contents of the grid, it's possibly by defining the rows in the DataGrid.Columns node and bind them to the appropriate Property (the property Content in this case)

<DataGrid.Columns>
        <DataGridTextColumn Binding="{Binding Content}"></DataGridTextColumn>
</DataGrid.Columns>

This should do as a start.