Accessing Local Database using Silverlight 4

Posted by: Mahesh Sabnis , on 11/11/2010, in Category Silverlight 2, 3, 4 and 5
Views: 36300
Abstract: 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.

I started my carrier as a VB 6.0 Developer 12 yrs ago. During this period, I used to work with ADO (the data access technology during that time) for accessing data and performing CRUD operations. It’s been a long time since then. Now I use the latest .NET technologies for various types of application development and training. Currently I am using Silverlight 4.0 which contains some fantastic features like COM enhancement programming.

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.

Note: VB.NET code of this article can obtained by using this free convertor


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">
    <Grid.ColumnDefinitions>
        <ColumnDefinition Width="453*" />
        <ColumnDefinition Width="487*" />
    </Grid.ColumnDefinitions>
    <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"
                    ColumnWidth="*"/>
    <Grid Grid.Column="1" Height="272" HorizontalAlignment="Left" Margin="13,15,0,0" Name="grid1" VerticalAlignment="Top" Width="454">
        <Grid.RowDefinitions>
            <RowDefinition Height="38*" />
            <RowDefinition Height="36" />
            <RowDefinition Height="37*" />
            <RowDefinition Height="35*" />
            <RowDefinition Height="126*" />
        </Grid.RowDefinitions>
        <Grid.ColumnDefinitions>
            <ColumnDefinition Width="225*" />
            <ColumnDefinition Width="229*" />
        </Grid.ColumnDefinitions>
        <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" />
    </Grid>
</Grid>

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;

The ‘dynamic’ keyword is new in C# 4.0 and you can read this article to understand it better var vs dynamic keyword in C# 4.0

Step 6: Write the following methods for creating and opening a connection using ADODB data access providers as below:


private void CloseConnection()

{
    Conn.Close();
}
 
private void CreateConnection()
{
    Conn = AutomationFactory.CreateObject("ADODB.Connection");
    recordSet = AutomationFactory.CreateObject("ADODB.RecordSet");
 
    Conn.Open(@"Provider=SQLNCLI10.1;Data Source=.;Database=Company;Integrated Security=SSPI");
}

Step 7: In the MainPage.Xaml.cs define the following class:
 
 
public class Employee
{
    public int EmpNo { get; set; }
    public string EmpName { get; set; }
    public int Salary { get; set; }
    public int DeptNo { get; set; }
}

Step 8: Write the following method which will retrieve the rows from the table using RecordSet object and Load them in List<Employee> object as below:

private void LoadData()
{
    recordSet.Open("Select * from Employee", Conn);
    while (!recordSet.EOF)
    {
        lstEmp.Add(
                new Employee()
                {
                    EmpNo = recordSet.Fields.Item("EmpNo").Value,
                    EmpName = recordSet.Fields.Item("EmpName").Value,
                    Salary = recordSet.Fields.Item("Salary").Value,
                    DeptNo = recordSet.Fields.Item("DeptNo").Value,
                }
            );
        recordSet.MoveNext();
    }
    recordSet.Close();
}

Step 9: In the ‘Get Data’ button click event write the following code:

private void btnGetData_Click(object sender, RoutedEventArgs e)
{
    try
    {
        CreateConnection();
 
        LoadData();
        CloseConnection();
        lstEmp = new List<Employee>();
        dgEmployee.ItemsSource = lstEmp;
 
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message); 
    }

}

Step 10: Write the following code in the ‘Insert’ button click event. This code will insert a new record in the Employee table using ‘Execute’ method of the connection object.

private void btnInsert_Click(object sender, RoutedEventArgs e)
{
    try
    {
        CreateConnection();
        string strInsert = "Insert into Employee Values(" + Convert.ToInt32(txteno.Text) + ",'" + txtename.Text + "'," + Convert.ToInt32(txtsal.Text) + "," + Convert.ToInt32(txtdno.Text) + ")";
        Conn.Execute(strInsert);
        CloseConnection();
 
        MessageBox.Show("Record Inserted..."); 
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message); 
    }
}


Step 11: Run the application and click on the ‘Get Data’ button. The DataGrid will display all the records from the local database. Also enter data in the textboxes and click on the ‘Insert’ button. The following result will be displayed.


Silverlight Local Database App 

Conclusion: With Out-of-Browser capabilities and elevated trust permission rights provided in Silverlight 4.0, it is very easy for developers to develop light-weight applications for those customers who are interested in rich UX and also want the application to interact with local data store, file system etc.

The entire source code of this article can be downloaded over here

This article has been editorially reviewed by Suprotim Agarwal.

Absolutely Awesome Book on C# and .NET

C# and .NET have been around for a very long time, but their constant growth means there’s always more to learn.

We at DotNetCurry are very excited to announce the The Absolutely Awesome Book on C# and .NET. This is a 500 pages concise technical eBook available in PDF, ePub (iPad), and Mobi (Kindle).

Organized around concepts, this eBook aims to provide a concise, yet solid foundation in C# and .NET, covering C# 6.0, C# 7.0 and .NET Core, with chapters on the latest .NET Core 3.0, .NET Standard and the C# 8.0 (final release) too. Use these concepts to deepen your existing knowledge of C# and .NET, to have a solid grasp of the latest in C# and .NET OR to crack your next .NET Interview.

Click here to Explore the Table of Contents or Download Sample Chapters!

What Others Are Reading!
Was this article worth reading? Share it with fellow developers too. Thanks!
Share on LinkedIn
Share on Google+

Author
Mahesh Sabnis is a DotNetCurry author and Microsoft MVP having over 17 years of experience in IT education and development. He is a Microsoft Certified Trainer (MCT) since 2005 and has conducted various Corporate Training programs for .NET Technologies (all versions). Follow him on twitter @maheshdotnet


Page copy protected against web site content infringement 	by Copyscape




Feedback - Leave us some adulation, criticism and everything in between!
Comment posted by Marius Filo on Wednesday, July 6, 2011 8:33 AM
Hello !
Very nice post, it's exactly what I've looked for !
One little (BIG) problem: using exactly the code you provided (slightly adapted to my needs: structure and source -- Visual foxpro dbf table), everything works almost fine. The problem is the time needed to retrieve data: for a simple SELECT ... WHERE, on a .dbf containing 2000 records, the 600 records that satisfies the query are loaded in about 60 seconds !. My computer is definitely out of question: Intel P4 6600, 2 Gigs of RAM, etc.
Do you have any idea about why is it happening ?
Thanks,
Marius
Comment posted by Shahid on Wednesday, July 18, 2012 2:44 PM
Hi Mahesh,
Quite an informative post. Kinda new in Silverlight. I downloaded and have run the sample project and getting an error:  Object Reference not set to an instance of an object. Would you give me an insight where am I going wrong.
Comment posted by Niraj Zambad on Saturday, August 4, 2012 12:42 AM
I want to do CRUD operation on MS Access Database using Silverlight 4.0
So please do reply how to do it???
Comment posted by Lyle on Tuesday, August 26, 2014 4:18 PM
Hi Mahesh,
Quite an informative post. Kinda new in Silverlight. I downloaded and have run the sample project and getting an error:  Object Reference not set to an instance of an object. Would you give me an insight where am I going wrong.

Categories

JOIN OUR COMMUNITY

POPULAR ARTICLES

C# .NET BOOK

C# Book for Building Concepts and Interviews

Tags

JQUERY COOKBOOK

jQuery CookBook