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:
Click on ‘Out-of-Browser Settings’ button and select ‘Require the elevated trust when running outside the browser’ checkbox as shown below:
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.
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.
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 .NET Standard and the upcoming C# 8.0 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!
Was this article worth reading? Share it with fellow developers too. Thanks!
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