Connecting to a SQL Server Database with VB and JavaScript in Windows 8.1

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:

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:

  1. Click File.
  2. Click New Project.
  3. Click Visual Basic.
  4. Click WCF.
  5. Click WCF Service Application, as shown in Figure 1.

Server1
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:

Server2
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:

  1. Click File.
  2. Click Add.
  3. Click New Project.
  4. Click Other Languages.
  5. Click JavaScript.
  6. Click Windows Apps and select the Blank App template, as shown in Figure 3.

Server3
Figure 3: JavaScript Windows App Project

You will notice the Solution Explorer resembles Figure 4.

Server4
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!

Hannes DuPreez
Hannes DuPreez
Ockert J. du Preez is a passionate coder and always willing to learn. He has written hundreds of developer articles over the years detailing his programming quests and adventures. He has written the following books: Visual Studio 2019 In-Depth (BpB Publications) JavaScript for Gurus (BpB Publications) He was the Technical Editor for Professional C++, 5th Edition (Wiley) He was a Microsoft Most Valuable Professional for .NET (2008–2017).

More by Author

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Must Read