Introduction
For those few of you who frequently read my articles, first of all, thanks, and second of all, you may find it weird that I write yet another article about databases in Windows 8.1; but, as you will see, this technique works entirely differently than in the previous articles. Today, I will demonstrate how to connect to an SQL Server database through a VB service and JavaScript.
Databases
For an overview about databases in general, have a read through these articles:
- Exploring the Data Controls in Visual Basic.NET
- Doing Data Extraction with Visual Basic.NET
- Using Parameterized Queries and Reports in VB.NET Database Applications
- Working with Blobs and VB.NET
- Using SQL Stored Procedures with VB.NET
- Databases and Windows 8.1 and VB
Services
For more information about WCF Services, have a read through these resources:
Our Project
The project, or projects rather, that you will create today will use two entirely different technologies to achieve one simple goal: connect to an SQl Server database. The first project you will create will be a WCF Service that will run in the background and its functionalities will be called from the JavaScript project that you will create.
Let’s start with the WCF Service. Create a new WCF project by following these steps:
- Click File.
- Click New Project.
- Click Visual Basic.
- Click WCF.
- Click WCF Service Application, as shown in Figure 1.
Figure 1: WCF Service Application Project
Once the project has been created, you will notice that your Solution Explorer’s content may look different than what you are accustomed to, as shown in Figure 2:
Figure 2: Service Application in Solution Explorer
Open the IService1.vb file and add the following code:
Imports System.Data Imports System.ServiceModel Imports System.ServiceModel.Web <ServiceContract> _ Public Interface IService ' Query data <OperationContract> _ <WebGet(ResponseFormat:=WebMessageFormat.Json, _ BodyStyle:=WebMessageBodyStyle.Wrapped)> _ Function GetSQL(ByRef blnParam As Boolean) As DataSet End Interface
Here, I basically create a Service Contract. This is essentially what the Service should ultimately do. The OperationContract is the function that you need to supply for the service to do what you want it to do. In this case, the Function named GetSQL is a function we will create momentaraly inside the Service1.svc file.
Open the Service1.svc file and add the following code:
Imports System.Data Imports System.Data.SqlClient Public Class Service Implements IService Private sqlCon As New SqlConnection("Data Source=(local); _ Initial Catalog=SQL_Ex;Integrated Security =SSPI;") Public Function GetSQL(ByRef blnParam As Boolean) As + DataSet Implements IService.GetSQL Try sqlCon.Open() Dim strSql As String = "select Name, Surname _ from PersonalDetails" Dim ds As New DataSet() Dim sqlDa As New SqlDataAdapter(strSql, sqlCon) sqlDa.Fill(ds) blnParam = True Return ds Catch blnParam = False Return Nothing Finally sqlCon.Close() End Try End Function End Class
Here, I created the GetSQL Function that obtains the data from the SQL database table named PersonalDetails from the database named SQL_Ex via the use of the standard data manipulation objects.
JavaScript Project
Add the JavaScript project to your existing WCF Service Application project by following these steps:
- Click File.
- Click Add.
- Click New Project.
- Click Other Languages.
- Click JavaScript.
- Click Windows Apps and select the Blank App template, as shown in Figure 3.
Figure 3: JavaScript Windows App Project
You will notice the Solution Explorer resembles Figure 4.
Figure 4: JavaScript Solution Explorer
Open the displayed default.htm file and add the following HTML code:
<div id="input"> <button id="btnData">GetData</button> </div> <div id="lvDataTemplates" data-win-control= "WinJS.Binding.Template" style="display: none"> <div class="lvDataTextItem"> <div class="BasicTitleStyle"> <span>Name:</span> <span data-win-bind="innerText: Name"></span> </div> <div class="BasicTextStyle"> <span>Surname:</span> <span data-win-bind="innerText: Surname"></span> </div> </div> </div>
This simply creates the interface of your web page. This web page will ultimately display the data that is fed from the WCF Service. Now, add the final piece of the puzzle: the JavaScript library. Open the js folder and open the default.js file. Add the following code:
// http://go.microsoft.com/fwlink/?LinkId=232509 (function () { "use strict"; var app = WinJS.Application; var activation = Windows.ApplicationModel.Activation; app.onactivated = function (args) { if (args.detail.kind === activation.ActivationKind.launch) { if (args.detail.previousExecutionState !== activation.ApplicationExecutionState.terminated) { } else { } args.setPromise(WinJS.UI.processAll().then(function completed() { var getdataButton = document.getElementById('btnData'); getdataButton.addEventListener("click", getDatabuttonclick, false); })); } }; app.oncheckpoint = function (args) { }; // event handler of get data button function getDatabuttonclick() { document.getElementById('error').innerText = ""; document.getElementById('btnData').style.setAttribute("disabled", "disabled"); var baseURI = "http://localhost:42920/Service.svc/GetSQL"; var xmlDoc; WinJS.xhr({ type: "get", url: baseURI }).then(function (response) { if (eval('(' + response.responseText + ')').queryParam == true) { var items = []; var resulttxt = eval('(' + response.responseText + ')').querySqlResult; if (window.DOMParser) { var parser = new DOMParser(); xmlDoc = parser.parseFromString(resulttxt, "text/xml"); } else {// Internet Explorer xmlDoc = new ActiveXObject("Microsoft.XMLDOM"); xmlDoc.async = false; xmlDoc.loadXML(resulttxt); } var nodes = xmlDoc.querySelectorAll("Table"); for (var i = 0; i < nodes.length; i++) { var item = new Object(); item.Name = nodes[i].childNodes[0].textContent; item.Surname = nodes[i].childNodes[1].textContent; items.push(item); } var list = new WinJS.Binding.List(items); document.getElementById('listView').winControl.itemDataSource = list.dataSource; document.getElementById('btnData').removeAttribute("disabled"); } else { writeError("Error!"); } }); } function writeError(text) { document.getElementById("error").innerText = text; } app.start(); })();
The preceding JavaScript code reads the data fed to it from the service and interprets it into a displayable format for your web page.
Conclusion
Obviously, this was just a quick and dirty sample, but at least now you know how to read data from an SQL Server database with your Windows Store application. This is me, signing off. Cheers!