Datagrid: Lazy-loading from SQL DB?

In Silverlight 2, is there an out-of-the-box way to have the Datagrid populate itself from SQL server as new data are scrolled into view?

I mistakenly thought that this was enabled in the Entity Framework, and that the calls only grabbed stubs. However, once I tried this out for large data sets performance dropped off significantly.

I am comming from Java and I am new to Silverlight, however I seem to remember seeing some kind of just-in-time loading of a DataGrid in a Silverlight 2 presentation by one of MS Silverlight developers in a Silverlight 2 preview?

Thanks
RDL

 

Hi,
I haven't messed with data in SL yet but do plan too and have been following John Papa...
Take a look at this article on the April MSDN, and the links on that same page:
http://msdn.microsoft.com/en-us/magazine/dd569758.aspx
Salute,
Mark

 

I found what I was looking for. Ronnie Saurenmann posted a great video tutorial. I think Ronnie might be on the Silverlight development team and working on the DataGrid because he posted several (great) video tutorials (See the "Archives" panel) on developing applications with it.

The video tutorial shows a Master-Details DataGrid with"scrolled", search, saving back to the server, and paging using WCF service and Linq to SQL. It builds on some previous posts which build up to this. The sourcecode is here: ScrollingLargeResult.zip . Just be careful about the "messageSize" which is set to 64k in the config file.

This uses the Northwind database, and I am trying to get this up on DASP but I haven't had much success. If someone gets it up an running before I do, could you post the steps needed to move this from VS to DASP? I got ADO.net running and I hoped I could take those same steps, but no luck thus far....

RDL

 

Very cool.
The WCF part would trip me up right now, haven't messed with that and it is very complex.
LINQ and Silverlight are a good match but I have chatted with some of the SL team about the memory it eats up, which is a big concern on Shared servers.
Apparently, and I am not sure about this, it will get addressed in v3 as Msft is changing directions with dynamic data...seems obvious anyway.
Salute,
Mark

 

You need to get the WCF service going. So you need to create a folder on your site and make it a web application. Put the Service1.svc file in that folder along with your web.config and the bin folder with the dll's.

If you do this right you should be able to go to www.yoursite.com/newWebAppFolder/Service1.svc

and see an html page letting you know it works

you'll need to change the web.config where it says the following and put in your info

<connectionStrings>
<add name="C__NORTHWND_MDFConnectionString" connectionString="Data Source=localhost;Initial Catalog=C:\NORTHWND.MDF;Persist Security Info=True;User ID=sa;Password=Locarno1"
providerName="System.Data.SqlClient" />
<add name="AdventureWorksConnectionString" connectionString="Data Source=localhost;Initial Catalog=AdventureWorks;Persist Security Info=True;User ID=sa;Password=Locarno1"
providerName="System.Data.SqlClient" />
</connectionStrings>

I didnt try this project because I dont have SQL, but in order to get a different wcf service working I had to change some security settings such as

<authentication mode="None"/>

and

</behaviors>
<bindings>
<basicHttpBinding>
<binding name="SilverlightApplication4.Web.Service1">
<security mode="None">
</security>
</binding>
</basicHttpBinding>
</bindings>
<services>

good luck

 

I encountered problems running the application in the ZIP file above. I was actually using an earlier version of that same application that Ronnie was using in a previous tutorial (With source code: SCLCRUD updated.zip). The specific problem with application .ZIP file I mention in the previous post was that Ronnie built with Silverlight Beta 2 and there are now incompadibilities in the XAML with the official release of SL2. Xaml gets a bit messy, and my attempts to modify it were fruitless.

Nonetheless, I did manage to get the earlier version of the application running on DASP and I plan to follow his tutorial to implement the paged scrolling.

In case people are new to discountASP.net (DASP), Silverlight (SL), or .NET, I'll explain the key steps in detail:

==============================================
1. Start VS and open the application in "SCLRUD updated.zip"

2. Go to "Solution Explorer"

3. In SilverlightApplication4->References delete: System.Windows.Controls.Data. Then re-add that same reference. Apparently its locked to an old version and this will fix the XAML error.

4. In Server Explorer, add a data connection to the NORTHWIND database and clicking on the properties, copy the connection string and past it into the connection string in web.config.

5. Test it on your own machine. If you search your VS Install directory there is a command line tool in Visual Studio 9.0 called WcfTestClient.exe which allows you to connect to your db with http://localhost:xxxx/Service1.svc and make calls (Where xxxx is the port number)

//Hosting it on DASP
6. To host this on DASP you need to change to files: web.config, and ServiceReferences.ClientConfig. In both files I am using the name "yourdaspwebsite.com" for the dasp hosted site, and which you will need to change.

Here is the system.service model I use in web.config:

/*---------------------------------------------------*/
<system.serviceModel>
<behaviors>
<serviceBehaviors>
<behavior name="SilverlightApplication4_Web.Service1Behavior">
<serviceMetadata httpGetEnabled="true"/>
<serviceDebug includeExceptionDetailInFaults="false"/>
</behavior>
</serviceBehaviors>
</behaviors>
<services>
<service behaviorConfiguration="SilverlightApplication4_Web.Service1Behavior" name="SilverlightApplication4_Web.Service1">
<!--`
<endpoint address="" binding="basicHttpBinding" contract="SilverlightApplication4_Web.IService1">
-->
<endpoint address="" binding="basicHttpBinding" contract="SilverlightApplication4_Web.IService1">

</endpoint>
<endpoint address="mex" binding="mexHttpBinding" contract="IMetadataExchange"/>
</service>
</services>


<serviceHostingEnvironment>
<baseAddressPrefixFilters>
<add prefix="http://yourdaspwebsite.com" />
</baseAddressPrefixFilters>
</serviceHostingEnvironment>

</system.serviceModel>
/*END---------------------------------------------------*/


And for ServiceReferences.ClientConfig, I set my <client> section to:


/*START---------------------------------------------------*/
<client>
<endpoint address="http://yourdaspwebsite.com/Service1.svc" binding="basicHttpBinding"
bindingConfiguration="BasicHttpBinding_IService1" contract="ServiceReference1.IService1"
name="BasicHttpBinding_IService1" />
</client>
</system.serviceModel>
/*END---------------------------------------------------*/

After that....

7. I uploaded the NORTHWIND.MDF data base file to a App_Data directory at the root directory on DASP.

8. Attached the database to DASP using their host control panel

9. Copied the DASP database connection string and replaced it with the one in my web.config file (Change password=***** to your DASP password).

10. Right clicked on SilverlightApplication4_Web folder in VS and "Publish"ed the website to the root directory in DASP using FTP upload.

11. Used IIS7.0 to disable "Basic" authentication (Not sure if this was necessary but it is habbit).

Once there you can point to:

http://yourdaspdomain.com/SilverlightApplication4TestPage.aspx

Also you can test your DB seperate from your application with VS's wcftestclient.exe command line program:

wcftestclient.exe http://yourdaspdomain.com/Service1.svc

This utility is great because it can tell you if your db is working properly or if you are just having application issues.

 

I think if you configure the service reference in visual studio to point at your service running on dasp then when you build the solution, ServiceReferences.ClientConfig should get updated with correct information.

So were you able to connect to your service without modifying authentication mode and security mode?

edit: I suppose step "11. Used IIS7.0 to disable "Basic" authentication", might be doing same as changing in web.config

 

Ya, that's correct. I haven't tired other procedures.

Best,
RDL

 

I managed to figure things out.

There are a couple of keys to this:

1) When you make your service call you need to add the "out" keyword. Your service should have something like this:


#region IInsertVectorService Members
public Binary GetPage(int skip, int take, out int recordCount)
{
MyClassesDataContext db = new MyClassesDataContext();
var query = from v in db.MyClassesTable
select v;
//This will show up in the e.Result struct on the callback)
recordCount = query.Count();

//Now take a page
if(skip >= recordCount) skip = skip - recordCount;
return query.Skip(skip).take(take).ToList();
}
#endregion

2) You'll also have to mess with the ScrollBar (See the video above) to determine how many pages you are requesting.

A final note, I was hoping to use deferred loading on some images which were built into the class. However, "deferred loading" wasn't working in the way I suspected. If deferred loading is enabled for the variable, it will load it before submitting it back to the server. You can see this if you put a System.Diagnostics.Debug statement in the DataContext designer class. I am guessing (??) that deferred loading only when the element is a key which refers to another table????

Best,
RDL