Tuesday, June 10, 2008

How can I connect to the database in Silverlight project? (VS 2008 Beta 2)

Silverlight applications behave like a client side application. So we willnot be able to connect to the DB on the server from the silverlight application as the application and DB are on different layers.
So how do we access DB from Silverlight?
There are different ways to access DB from Silverlight application.
i) Webservices
ii) WCF Service
iii) A normal ASPX page.
In the 1st and 2nd methods the code can be of the same type. A sample service code is as follows

[WebMethod]public string RetrieveTexture()
{
try
{
MySqlConnection _mysqlConnection = new MySqlConnection();_mysqlConnection.ConnectionString = ConfigurationManager.ConnectionStrings["mySqlConnection"].ToString();
_mysqlConnection.Open();
MySqlDataAdapter da = new MySqlDataAdapter();
da.SelectCommand = new MySqlCommand("SELECT * FROM myTablename LIMIT 0, 20", _mysqlConnection);DataSet ds = new DataSet();
da.Fill(ds);
StringBuilder sb = new StringBuilder();
sb.Append("");
sb.Append("");
foreach (DataRow dr in ds.Tables[0].Rows)
{
sb.Append("");sb.Append("");
sb.Append(dr[0].ToString());
sb.Append("
");
sb.Append("");
sb.Append(dr[1].ToString());
sb.Append("
");sb.Append("");
sb.Append(dr[4].ToString());
sb.Append("
");
sb.Append("");
sb.Append(dr[5].ToString());
sb.Append("
");sb.Append("
");
}
sb.Append("
");
_mysqlConnection.Close();
return sb.ToString();
}
catch (Exception ex)
{
return string.Empty;
}
C# Code:----------------------------------------------------------------------------------------------------public partial class Page : UserControl
{
public Page()
{
InitializeComponent();
Loaded += new RoutedEventHandler(UserControl_Loaded);
}
void UserControl_Loaded(object sender, RoutedEventArgs e)
{
BasicHttpBinding bind = new BasicHttpBinding();
bind.MaxReceivedMessageSize = 2147483647;
bind.MaxBufferSize = 2147483647;
EndpointAddress endpoint = new EndpointAddress("http://localhost:51103/serviceTest_Web/myService.asmx");theSercice.myServiceSoapClient textureSoapClient = new serviceTest.theSercice.myServiceSoapClient(bind, endpoint);
textureSoapClient.RetrieveTextureAsync();
textureSoapClient.RetrieveTextureCompleted +=
new EventHandler(textureSoapClient_RetrieveTextureCompleted);
}

void textureSoapClient_RetrieveTextureCompleted(object sender, serviceTest.theSercice.RetrieveTextureCompletedEventArgs e)
{
if (e.Error == null)
displayData(e.Result);
}
void displayData(string xmlContent)
{
try
{
if (xmlContent != string.Empty)
{
XDocument xmlProducts = XDocument.Parse(xmlContent);
var textures = from texture in xmlProducts.Descendants("Texture")
select new
{
itemId = (string)texture.Element("item").Value.PadLeft(10, '0'),
txArtist = (string)texture.Element("Artist").Value.PadLeft(10, '0'),
txPath = (string)"http://www.texturearchive.com/thumbs/" + texture.Element("class1").Value + "/" + texture.Element("class2").Value + "/" + texture.Element("item").Value.PadLeft(10, '0') + "_" + texture.Element("Artist").Value.PadLeft(10, '0') + "_tn.jpg"
};
//Bug: http://silverlight.net/forums/t/11147.aspx
List texturesList = new List();foreach (var t in textures)
{
Texture pdt = new Texture { itemId = t.itemId, txArtist = t.txArtist, txPath = t.txPath };
texturesList.Add(pdt);
}
texturesDataGrid.ItemsSource = texturesList;
}
else
{
//errMessage.Visibility = Visibility.Visible;
texturesDataGrid.ItemsSource = null;
}
}
catch (Exception ex)
{
Console.Write(ex.Message);
}
}
public class Texture
{
public string itemId { get; set; }
public string txArtist { get; set; }
public string txclass1 { get; set; }
public string txclass2 { get; set; }
public string txPath { get; set; }
}

This same logic can be acheved from a ASPX page which will render the above output in XML format.
For Example the ASPX page should have the following code in the page load event
protected void Page_Load(object sender, EventArgs e)
{
Response.Clear();
Response.ContentType = "text/xml";
Response.Write(“Any XML String”);
Response.End();
}

1 comment:

Sweet Sweet said...

How can I use that aspx page in my silverlight? Could you tell me the details? I have a problem at my silverlight page. I cannot use ADO.Net or LINQ or Web Service at my silverlight page. My database is SQL Azure and I cannot use ADO.Net or LINQ and for my requirement, I cannot use web service. So, How could I connect to database(actually it is a stored procedure) in silverlight page?