Solution: Web services! You can build a small PHP page to connect to your database and provide data to your application! As long as your web service is running your app should be able to find it (assuming it has an internet connection)!
If you have never played around with PHP then this will be deep water for you. If you've played with just a bit of PHP then you should be okay. Unfortunately I am not going to go into too much PHP depth. We have plenty of tutorials here on DIC for that. If anything just sit back and enjoy the ride.
The over all game plan is is you have this page on your web server. It provides a specific setup to expose a public function which can format data, pull it from a database, or provide a humorous message. Your .NET app recognizes this WSDL and allows you to interact with it like an object! You can see the special types inside, hit that public function as if it was a dll you have referenced locally!
A serious word of warning - the php here is not secured. If someone gets a hold of your web service they can call it just as easily as you and that might lead to some abuse of bandwidth and information. I would highly suggest boning up on PHP security before you put any sensitive information out there!
Here's the tools being used:
Visual Studios 2010, 4.0 framework.
XAMPP 1.7.3 (to create a localhost situation)
PHP: 5.3.1
Eclipse (for the PHP editing)
Nusoap 0.9.5 (a great library to create your WSDL)
Nusoap documentation
Zoltar (to jam to)
Background information:
XAMPP is a handy dandy way to install apache (for the hosting), mysql (for the database), and PHP (for the code) in one fell swoop on a windows box. This won't go through installing that there are plenty of crazy-detailed walk throughs already out there. That's on your shoulders.
Odd terms:
WSDL = Web Services Description Language.
URN = universal resource name
Setup
Okay - you have your tools? You stopped your IIS hosting so XAMPP's apache can work?
Your apache service and mysql services are running?
You've have the nusoap zip downloaded? Excellent. 'lib' folder in your php working folder? Awesome! Let's go.
PHP
Fire up Eclipse and create a new php project. Let's call it "wsdl_tutorial". Keep the default settings and click finish.
Open up windows explorer and navigate to the folder. Add your nusoap "lib" folder to the "wsdl_tutorial" folder.
In this new project add a new PHP file called: "My_Test_Service.php".
Here's the code that goes in it.
<?php
require_once('lib/nusoap.php'); // basic include.. must go at the top
$SERVICE_NAMESPACE = "urn:Testing_Service"; // create a namespace to run under.
$server = new soap_server(); // the soap object from the include above.
// this has many input parameters but we only need two: the service name and the namespace
$server->configureWSDL('Testing_Service', $SERVICE_NAMESPACE);
// Register a method name with the service and make it publicly accessable.
$server->register('Say_Hello',// method name
array('name' => 'xsd:string'),// input parameter called name.. and it's a string.
array('return' => 'xsd:string'),// output - one string is returned called "return"
$SERVICE_NAMESPACE,// namespace
$SERVICE_NAMESPACE . '#hello',// soapaction
'rpc',// style.. remote procedure call
'encoded',// use of the call
'Sends a greeting with your name!'// documentation for people who hook into your service.
);
// here is the method you registered.. it takes in a string and returns a string!
function Say_Hello($sName)
{
return 'Hello ' . $sName . '! Hello world!';
}
//This processes the request and returns a result.
$HTTP_RAW_POST_DATA = isset($HTTP_RAW_POST_DATA) ? $HTTP_RAW_POST_DATA : '';
$server->service($HTTP_RAW_POST_DATA);
?>
Once that is all coded up, save it, and let's test if this worked!
Navigate to the PHP file from your browser.. for me this is:
localhost/workspace/wsdl_tutorial/My_Test_Service.php
You should get a most excellent response! This provides all the pertinent information anyone may need when hitting this service.
For more fun, try adding a "?wsdl" at the end.
localhost/workspace/wsdl_tutorial/My_Test_Service.php?wsdl
VB.NET
Excellent.. well keep all that there and start up Visual Studios.
Create a windows project and add a button and a textbox to it.
Double click on the button to have it create your event.
Now here's where we add the web service call! Right click on your project and go to 'add service reference'.
Leave the options alone, but click on the 'advanced' button.
Up comes the 'service reference settings'... once again ignore all the options, but click 'add web reference'.
Here - in the URL - add the ?wsdl url here and click the green arrow to have Visual Studios explore the web service for you.
If everything went as planned you should see your service information! I am going to change the 'web reference name' to something more fitting... like "My_Test_Service". Click 'add reference' when you are done.
In your solution you should find a new folder, called Web References, added to your solution along with your web reference's name! We are almost there!
Back in your VB.NET code let's use that service!
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
'-- create an instance of your service.
Dim foo As New My_Test_Service.Testing_Service
'-- take input from the text box, feed that to the service, and the display the results.
'-- this will prove the round trip has taken place!
MsgBox(foo.Say_Hello(TextBox1.Text))
End Sub
End Class
Run the code, type something into the input box and click the button. You should get your input string plus our web service's text back!
There's the meat of the tutorial. What you can do now is modify that PHP service page.. register a new method and do complex actions! Take in a value to be searched by the database and return something else. Ping your service for updates based on some factor! The sky's the limit!
Each time when you save go back to your vb.net project, right click on the 'my test service' and do an "update". That will grab your new public methods and make them available to your class.
Advanced
Getting mysql DB data back from your website
Now for a bit of advanced fun. This will run a bit faster, but you should be able to keep up. Open up your MYSQL admin panel and create a new table. Here I called it "test_table", and I added three columns. I also filled it with some test data. Let's see if I can modify the existing service to return this data to application!
Flip back to our PHP file. Let's add a new method that queries the database and returns all the data in this table.
Let me explain something up front - there is a problem with PHP and trying to return soap arrays: it's a pain in the ass. You need to think in terms of building your return datasets by hand. First you create an array for the rows of your data... then you need to wrap that array in another array to have bundle all your rows up. Then you need to make sure everything knows what type what is, and then maybe.. just maybe you can create an instance of your row type.. fill it, add it to a collection, and ship it back to the class. A most frustrating prospect if there ever was one.
Think of this like having to create a structure in .NET... then having to create an object array to hold instances of that structure... that is sort of what we are doing here.
This code can be put between the end of the function "Say_Hello" and the " $HTTP_RAW_POST_DATA".
// complex types are like 'struct' in C#.... it's a way to bind an object with different properties and variables together.
$server->wsdl->addComplexType(
'MyTableData', // the type's name
'complexType', // yes.. indeed it is a complex type.
'struct', // php it's a structure. (only other option is array)
'all', // compositor..
'',// no restriction
array(
'lId' => array('name'=>'lId','type'=>'xsd:string'),
'sValue' => array('name'=>'sValue','type'=>'xsd:string'),
'sText' => array('name'=>'sText','type'=>'xsd:string')
)// the elements of the structure.
);
// Here we need to make another complex type of our last complex type.. but now an array!
$server->wsdl->addComplexType(
'MyTableArray',//glorious name
'complexType',// not a simpletype for sure!
'array',// oh we are an array now!
'',// bah. blank
'SOAP-ENC:Array',
array(),// our element is an array.
array(array('ref'=>'SOAP-ENC:arrayType','wsdl:arrayType'=>'tns:MyTableData[]')),//the attributes of our array.
'tns:MyTableData'// what type of array is this? Oh it's an array of mytable data
);
// Register a method name with the service and make it publicly accessable.
$server->register('Get_Test_Data',// method name
array(),// input parameter - nothing!
array('return' => 'tns:MyTableArray'),// output - object of type MyTableArray.
$SERVICE_NAMESPACE,// namespace
false,// soapaction
'rpc',// style.. remote procedure call
false,// use of the call
' Get all the data from test_table.'// documentation for people who hook into your service.
);
// here is the method you registered.. it takes in nothing and returns an array of MyTableArray
function Get_Test_Data()
{
// woah who has a super unsecure connection to his database? This guy!
$conn = mysql_connect('localhost', 'root', '')
or die('Could not connect: ' . mysql_error());
// make our database point to your 'test' database.
mysql_select_db('test') or die('Could not select database');
// a simple select statement.
$sql = "SELECT lId
,sValue
,sText
FROM test_table ";
//fill the output into something we can use.
$result = mysql_query($sql) or die('Query failed: ' . mysql_error());
// it's always nice to know how many rows we have.
$num_rows = mysql_num_rows($result);
//make a generic array of the size of our result set.
$oReturn[$num_rows];
// create a counter so we can traverse each item in our array.
$lCounter = 0;
//while i can still get data back
while ($line = mysql_fetch_array($result, MYSQL_ASSOC))
{
// for the given index assign it an array of data.. specifically telling it what column gets what chunk of data.
$oReturn[$lCounter] = array('lId'=>$line['lId'], 'sValue'=>$line['sValue'] , 'sText'=>$line['sText']);
// increment the counter for the next run.
$lCounter = $lCounter+1;
}
// return it all.
return $oReturn;
}
Back in the VB.NET project I created a second button, updated the web service reference (right click -> update), and added this code to verify we are getting our data.
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
'-- the web service object
Dim foo As New My_Test_Service.Testing_Service
'-- the glove to catch our data
Dim bar() As My_Test_Service.MyTableData
Try
'-- the call to the method in the web service to get our data.
bar = foo.Get_Test_Data()
'-- we need to cycle through our data to make sure it's here... that means casting it!
For i As Int32 = 0 To bar.Count - 1
Console.WriteLine("lId: " + CType(bar(i), My_Test_Service.MyTableData).lId)
Console.WriteLine("sText: " + CType(bar(i), My_Test_Service.MyTableData).sText)
Console.WriteLine("sValue: " + CType(bar(i), My_Test_Service.MyTableData).sValue)
Console.WriteLine("---------")
Next
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
The result:
Quote
lId: 1
sText: a text
sValue: a
---------
lId: 2
sText: b text
sValue: b
---------
lId: 3
sText: a2
sValue: a
---------
lId: 4
sText: c text
sValue: c
---------
sText: a text
sValue: a
---------
lId: 2
sText: b text
sValue: b
---------
lId: 3
sText: a2
sValue: a
---------
lId: 4
sText: c text
sValue: c
---------
There's your direct call to the database! You know see that with a bit of PHP, digging through a few screens, and some deceptively simple VB.NET you can pull data from your website's mysql database and into your application on your desktop!
Enjoy!
Complete Advanced Code
Spoiler
MultiQuote
| 