Accessing Local Database using Silverlight 4

In this article, we will explore the mechanism of communicating with the local SQL Database using the out-of-browser features of Silverlight 4.0 with elevated trust permissions. This has brought back my golden days of VB 6.0 programming

Now before we start, let’s think why should we use the feature of accessing local database using Silverlight 4.0 application using its browser application. Well here’s a hypothetical scenario:

An application exists for marketing personals where the items for sales information are stored in the local database. To reduce the complexity of communication with the ‘external database server’ for accessing information, a Silverlight 4.0 Out-of-Browser application installed on the Laptop of these personals, needs access to the local database to fetch the available items for making a sale.

Creating a Silverlight 4.0 application for accessing local database

Step 1: Open VS2010 and create a new Silverlight 4.0 application. Call it ‘SILV4_Accessing_LocalDatabase’.

Step 2: In this project, add a reference to ‘Microsoft.CSharp’.

Step 3: Right click on the Silverlight application and select properties and check the ‘Enable Running application out of browser’ check box as below:

Silverlight Enable Out Of Browser 
Click on ‘Out-of-Browser Settings’ button and select ‘Require the elevated trust when running outside the browser’ checkbox as shown below:

Silverlight Elevated Trust

Step 4: Open MainPage.xaml and add the following Xaml:
<Grid x:Name="LayoutRoot" Background="White">
        <ColumnDefinition Width="453*" />
        <ColumnDefinition Width="487*" />
    <Button Content="Get Data" Height="23" HorizontalAlignment="Left"
            Margin="81,12,0,0" Name="btnGetData" VerticalAlignment="Top"
            Width="200" Click="btnGetData_Click" />
    <sdk:DataGrid AutoGenerateColumns="True" Height="193"
                    HorizontalAlignment="Left" Margin="23,53,0,0" Name="dgEmployee"
                    VerticalAlignment="Top" Width="393"
    <Grid Grid.Column="1" Height="272" HorizontalAlignment="Left" Margin="13,15,0,0" Name="grid1"VerticalAlignment="Top" Width="454">
            <RowDefinition Height="38*" />
            <RowDefinition Height="36" />
            <RowDefinition Height="37*" />
            <RowDefinition Height="35*" />
            <RowDefinition Height="126*" />
            <ColumnDefinition Width="225*" />
            <ColumnDefinition Width="229*" />
        <TextBlock Height="23" HorizontalAlignment="Left" Margin="14,10,0,0" Name="textBlock1"Text="EmpNo:" VerticalAlignment="Top" Width="186" />
        <TextBlock Height="23" HorizontalAlignment="Left" Margin="14,5,0,0" Name="textBlock2"Text="EmpName:" VerticalAlignment="Top" Width="186" Grid.Row="1" />
        <TextBlock Height="23" HorizontalAlignment="Left" Margin="14,5,0,0" Name="textBlock3"Text="Salary:" VerticalAlignment="Top" Width="186" Grid.Row="2" />
        <TextBlock Height="23" HorizontalAlignment="Left" Margin="13,6,0,0" Name="textBlock4"Text="DeptNo:" VerticalAlignment="Top" Width="186" Grid.Row="3" />
        <TextBox Grid.Column="1" Height="23" HorizontalAlignment="Left" Margin="21,10,0,0"Name="txteno" VerticalAlignment="Top" Width="180" />
        <TextBox Grid.Column="1" Grid.Row="1" Height="23" HorizontalAlignment="Left"Margin="21,5,0,0" Name="txtename" VerticalAlignment="Top" Width="180" />
        <TextBox Grid.Column="1" Grid.Row="2" Height="23" HorizontalAlignment="Left"Margin="21,5,0,0" Name="txtsal" VerticalAlignment="Top" Width="180" />
        <TextBox Grid.Column="1" Grid.Row="3" Height="23" HorizontalAlignment="Left"Margin="21,6,0,0" Name="txtdno" VerticalAlignment="Top" Width="180" />
        <Button Content="Insert" Grid.Column="1" Grid.Row="4" Height="23"HorizontalAlignment="Left" Margin="21,21,0,0" Name="btnInsert" VerticalAlignment="Top" Width="180"Click="btnInsert_Click" />
Note: Ignore the events shown here for the time being. We will come to it shortly
Open MainPage.xaml and use the namespace below:
using System.Runtime.InteropServices.Automation;

Step 5: Declare the following object for the programming:

dynamic Conn;
dynamic recordSet;
List<Employee> lstEmp = null;