Server side MySQL DB Access and accessing resources client side (a jpg) from the tinterwebz

You may or may not know, but I have had a load of trouble trying to implement the MS Push service that we get with XNA on the WP7, in fact, me and the service got on so badly I decided to abandon it and have a go at writing my own.

Now, this post comes with a huge warning, I am not an ASP.NET developer, in my job I am jammed firmly in the world of .NET 2.0 and I very rarely venture out from this world. So with that said, the code I am to present here in this post can almost certainly be done much better in later frameworks and by some one that has a better knowledge of ASP.NET than me. Now that my technical bottom has been covered, on with the code.

Why did I start looking at this? Well, at one of our meetings in the pub we discussed turn based games on the WP7, and I thought that this would be a great way to use the Push service. So I started down the route of creating a simple “Conquer the Stars!” type turn based game, the start of the server side stuff you can find here, but it wont be that interesting as it’s kind of on hold now while I sort out my own API.

So I think I have a mechanism that sort of works now, as I say I am no web master, so if you are clued up and think I am going about this the wrong way then please shout up and let me know.

So the basic idea now is to serve up data from generic handlers, so based on incoming query strings I can return data from the server that the client requires. As yet none of this is encrypted, either in the query string or the data being served back, but I plan to. Also I am not using SSL, yet.

Server Side


For my personal hosting I use tsohost.com, mostly as it’s cheap and they support ASP.NET 4.0 so I don’t have to learn any of that crazy PHP or JSP madness, just stick to good old C# :P With this I also get up to 5 MySQL Database’s as well as some other goodies. If I want to use SSL then they charge about £50 per cert p/a So how do I access my MySQL data on the server.

DBTools.cs


Using MySQL .NET assemblies I created this class to do basic interaction with my Database.

public class DBTools : IDisposable
{
public IDbConnection CNN;
public DBTools(string server, string port, string database, string userID, string password)
{
CNN = new MySqlConnection(string.Format("Data Source={0};Port={1};Database={2};User ID={3};Password={4}", server, port, database, userID, password));
}
public DataSet GetDataSet(string sql)
{
DataSet ds = new DataSet();
IDbDataAdapter da;
da = new MySqlDataAdapter(sql, (MySqlConnection)CNN);
da.Fill(ds);
return ds;
}
public void Close()
{
if (CNN.State != ConnectionState.Closed)
CNN.Close();
}
public void Dispose()
{
Close();
}
}

So how do I serve data from my server? I create a generic handler, for this demo I have created only one, GetTexture.ashx. It has an instance of my DBTools class so I can (in a very basic way) authenticate calls to it.

public class GetTexture : IHttpHandler
{
protected DBTools dbTools = new DBTools(ConfigurationManager.AppSettings["Server"], ConfigurationManager.AppSettings["Port"], ConfigurationManager.AppSettings["Name"], "yourDBUser", "yourDBUserPassword");
public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "text/plain";
context.Response.Cache.SetNoStore();
context.Response.Cache.SetNoServerCaching();
string userName = context.Request.QueryString["usr"];
string userPswd = context.Request.QueryString["pwd"];
DataSet ds = dbTools.GetDataSet(string.Format("SELECT * FROM User WHERE USER_NAME = '{0}' AND PASSWORD = '{1}'", userName, userPswd));
if (ds.Tables != null && ds.Tables[0].Rows.Count > 0)
{
WriteLine(context, "http://www.randomchaos.co.uk/images/sheepRender.jpg");
}
else
{
WriteLine(context, "Error: Access denied, invalid user.");
}
}
public void WriteLine(HttpContext context, string data)
{
context.Response.Write(string.Format("{0}\r\n", data));
}
public bool IsReusable
{
get
{
return true;
}
}
}

So, it is expecting 2 parameters “usr” and “pwd” a bit like this

http://yourdomain/XNAHTTPServer/GetTexture.ashx?usr=nemo&pwd=oxosox, so if the user “nemo” with the password “oxosox” is in my User table, then the page will return a url for me to get an image asset, otherwise it will tell me I don’t have access.

In this case the image coming back from the server is this one

Client Side


So, in my XNA WP7 project (will work on PC games to, but not the Xbox) I have created a HTTPRequest class to call to the server and get the url back, then once it has the url to then download the image and render it to the screen :)

public class HTTPRequest : GameComponent
{
const string REST_Request = "GetTexture.ashx";
public string Server { get; set; }
public string UserName { get; set; }
public string Password { get; set; }
public string ImageAssetUri { get; set; }
public Texture2D Image { get; set; }
protected bool gettingImage = false;
public bool GettingImage { get { return GettingImage; } }
public bool Errored = false;
public HTTPRequest(Game game, string server, string userName, string password)
: base(game)
{
Server = server;
UserName = userName;
Password = password;
}
protected void GetImage()
{
HttpWebRequest request = (HttpWebRequest)WebRequest.Create(new Uri(ImageAssetUri));
IAsyncResult webResult = request.BeginGetResponse(new AsyncCallback(GetImageCallback), request);
}
protected void GetImageCallback(IAsyncResult asynchronousResult)
{
try
{
HttpWebRequest myRequestState = (HttpWebRequest)asynchronousResult.AsyncState;
HttpWebResponse response = (HttpWebResponse)myRequestState.EndGetResponse(asynchronousResult);
Stream s = (Stream)response.GetResponseStream();
Image = Texture2D.FromStream(Game.GraphicsDevice, s);
s.Close();
response.Close();
gettingImage = false;
}
catch (Exception e)
{
MsgBoxError("Network Error!", e.Message, null, "OK");
}
}
public void RequestImage()
{
if (gettingImage)
return;
gettingImage = true;
string uri = string.Format("{0}{1}?usr={2}&pwd={3}", Server, REST_Request, UserName, Password);
HttpWebRequest request = (HttpWebRequest)WebRequest.Create(new Uri(uri));
IAsyncResult webResult = request.BeginGetResponse(new AsyncCallback(RequestImageCallback), request);
}
protected void RequestImageCallback(IAsyncResult asynchronousResult)
{
try
{
HttpWebRequest myRequestState = (HttpWebRequest)asynchronousResult.AsyncState;
HttpWebResponse response = (HttpWebResponse)myRequestState.EndGetResponse(asynchronousResult);
Stream s = (Stream)response.GetResponseStream();
byte[] b = new byte[response.ContentLength];
s.Read(b, 0, b.Length);
string data = new UTF8Encoding().GetString(b, 0, b.Length);
s.Close();
response.Close();
if (!data.Contains("Error:"))
{
ImageAssetUri = data;
GetImage();
}
else
{
MsgBoxError("Server Error!", data, null, "OK");
}
}
catch (Exception e)
{
MsgBoxError("Network Error!", e.Message, null, "OK");
}
}
void MsgBoxError(string title, string message, AsyncCallback callback, params string[] buttons)
{
// Wait for guide to be cleared.
while (Guide.IsVisible) ;
List<string> dialogButtons = new List<string>();
for (int b = 0; b < buttons.Length; b++)
{
dialogButtons.Add(buttons[b]);
}
Guide.BeginShowMessageBox(title,
message,
dialogButtons, 0, MessageBoxIcon.Alert, callback, null);
Errored = true;
}
}

So now I am able to dynamically download an asset to my game and use it. The resulting out put looks like this :) Remember, there are no assets in the XNA project at all…

Source code for both client and server can be downloaded here.

So, if you think what I am attempting is just plain crazy mental, either just the ASP.NET side of things, the security behind what I am doing, or performance or anything then please let me know :D