Godaddy SQL Server 2005 C# February 2, 2008

Posted by headwinds in ASP.NET, Actionscript, SQL Server.
trackback

for some odd reason, I woke up at 6:30 this morning on a mission to crack my .Net connection. Perhaps, it had something to do with watching the latest episode of Lost (S4e1) and the classic Quadrophenia last night.

I consider myself a beginner with ASP.NET and SQL Server 2005 but I’ve had some experience mining databases with PHP and MySQL. I’m an expert in Flash actionscript though, and have heard great things about C# being very similar in syntax to AS3. I’ve also worked at 2 large .NET shops (Eloqua and Navantis) working closely with .NET developers on Flash/.NET integrations, and drank too much of their kool-aid. Also, at Push, our lead back-end developer, Mark Aucoin, is quite the .Net pusher.

I’d like to offer a very brief tutorial [pretty much just the configuration scripts which I found extremely hard to find ] about what to do after you’ve setup your SQL Server 2005 database from your Godaddy admin panel — Godaddy provides adequate help about how to do this but seems to be missing this solution or at least I couldn’t find it.

Goal: to create a webservice so that Flash (AS 3) can talk to ASP.NET and SQL Server.

You basically need to create these 2 files:

// web.config
//—————————————————

<configuration>
<appSettings>
<add key=”ConnectionInfo” value=”server=yourserver; uid=yourusename; pwd=yourpassword; database=yourdatabase;” />

</configuration>
</appSettings>

//————————————————–
// WebService.asmx
//—————————————————
using System;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Data;
using System.Diagnostics;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;

[WebService(Namespace = "http://www.headwinds.net/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
public class WebService : System.Web.Services.WebService
{

[WebMethod]
public DataSet GetCarbonQuizTable()
{
SqlConnection Con;

string sSQL;
// its recommended that you put your connection string in your web config file but I’ll show you an alternative:
//sConstring=”user id=yourid; password=yourpassword;database=yourdatabase;server=yourserver”;
// ["ConnectionInfo"] <— square brackets — very important
string sConstring = ConfigurationSettings.AppSettings["ConnectionInfo"];
// you can add complex sql here but for this tutorial I’m just going to return the entire table
// once you’ve mastered this you can progress to stored procedures and views
sSQL = “select * from YourTableName”;
Con = new SqlConnection(sConstring);
DataSet ds = new DataSet();
Con.Open();
SqlDataAdapter da = new SqlDataAdapter(sSQL, Con);
da.Fill(ds, “CarbonQuiz”);
return ds;
}
}
//—————————————————

Once you’ve configured these files to match your credentials, you upload both of them to the root of your hosting account. You can test the WebService.asmx by opening it in a browser.

Next, you can see the results in Flash CS3.

//TestWebservice.fla
//—————————————————
import alducente.services.WebService;
import flash.events.*;

var ws:WebService = new WebService();
ws.addEventListener(Event.CONNECT, connected);
ws.connect(“http://www.headwinds.net/WebService.asmx?WSDL”); // enter the path to your webservice here
ws.cacheResults = true;

var initTime:Number;

//Once connected, call an available method on the service named “ResolveIP” with the appropriate parameters
function connected(e:Event):void
{
trace (“CONNECTED!”);
ws.GetCarbonQuizTable(onComplete);

}

//This function is called when there is a response from the sesrvice
function onComplete(serviceRespone:XML):void{
trace(“\nWeb Service Result: “);
var time:Number = getTimer();
trace(“Call duration: “+(time – initTime)+” milliseconds”);
initTime = time;
trace(serviceRespone);
}
//—————————————————

RESOURCES

a decent read
programming C# from O’Reilly

GOFR – Flash AS3 webservice package by Carlo Aducente

I highly recommend the following series especially #8 — big eureka moment for me when it all comes together.
SQL ASP.NET Tutorials

the above are free, you can also get 500 more for $49 from here:
www.learnvisualstudio.net

Get the free Visual Studio Express suite

Godaddy Hosting — enter DIGG and get 10% off