locked
Handle SQL Server database RRS feed

  • Question

  • I've added a MS SQL Server (database.mdf) in my Excel task pane app and populated with SQL, great ! :). I would like to write and read to it from home.js

    I need some example code to put my home.js file.

    This code won't work. Can i use ActiveXObject in task pane app? I think not

            var connection = new ActiveXObject("ADODB.Connection");
            var connectionstring = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename='C:\database.mdf';Integrated Security=True;Connect Timeout=30";
            connection.Open(connectionstring);
            var rs = new ActiveXObject("ADODB.Recordset");
            rs.Open("SELECT * FROM tblStation", connection);
            rs.MoveFirst
            while (!rs.eof) {
                document.write(rs.fields(1));
                rs.movenext;
            }
            rs.close;
            connection.close;

    This code won't work either:

    var Connection = require('tedious').Connection;
            var config = {
                userName: '',
                password: '',
                server: 'Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename="C:\NS.mdf";Integrated Security=True',
                // When you connect to Azure SQL Database, you need these next options.  
                // options: { encrypt: true, database: 'AdventureWorks' }
            };
            var connection = new Connection(config);
            connection.on('connect', function (err) {
                // If no error, then good to proceed.  
                console.log("Connected");
                executeStatement();
            });
    
            var Request = require('tedious').Request;
            var TYPES = require('tedious').TYPES;
    
            function executeStatement() {
                request = new Request("SELECT * FROM tblStation;", function (err) {
                    if (err) {
                        console.log(err);
                    }
                });
                var result = "";
                request.on('row', function (columns) {
                    columns.forEach(function (column) {
                        if (column.value === null) {
                            console.log('NULL');
                        } else {
                            result += column.value + " ";
                        }
                    });
                    console.log(result);
                    result = "";
                });
    
                request.on('done', function (rowCount, more) {
                    console.log(rowCount + ' rows returned');
                });
                connection.execSql(request);
            }
      
    • Edited by Pacecal Wednesday, July 27, 2016 9:43 AM
    Wednesday, July 27, 2016 9:05 AM

Answers

  • >>>I can't find an example that use a sql server database or other method to read stored values stored in a database.

    The JavaScript API for Office enables you to create web applications that interact with the object models in Office host applications. Your application will reference the office.js library, which is a script loader. The office.js library loads the object models that are applicable to the Office application that is running the add-in. 

    You are not able to use JavaScript API for Office to read stored values stored in a database, so I suggest that you could use ASP.Net server side technology to retrieve data from SQL Server, please refer to below steps and codes:

    Right click to Add "New Item" then add Web Service(ASMX) named "FetchCustomer"

    /// <summary>
    /// Summary description for FetchCustomer
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
    [System.Web.Script.Services.ScriptService]
    public class FetchCustomer : System.Web.Services.WebService
    {
    
        [WebMethod]
        public string GetCustomer(string customerID)
        {
            //please modify these codes based on your requirement
            //you code that connect Sql Server then read data from Sql Server
            string jsonResult = string.Empty;
    
            var jsonSerialiser = new JavaScriptSerializer();
    
            List<Customer> customers = BuildData();
            jsonResult = jsonSerialiser.Serialize(customers.Where(cust=>cust.CustomerID==customerID).ToList<Customer>());
    
            return jsonResult;
        }
    
        public List<Customer> BuildData()
        {
            return new List<Customer> {new Customer{CustomerID="001",CustomerName="John"},new Customer{CustomerID="002",CustomerName="Tom"}};
        }
    }
    public class Customer
    {
        public string CustomerID { get; set; }
        public string CustomerName { get; set; }
    
    }


    Open Home.html

    <body>
      ......
      <div id="CustomerDetails">
      </div>
      <button id="get-data-from-database">Get data from database</button>
      ......
    </body>
    <head>
    ......
    <script type="text/javascript">
        $(document).ready(function () {
            $('#get-data-from-database').click(function () {
                $("#CustomerDetails").html("Starting...");
                $.ajax({
                    type: "POST",
                    contentType: "application/json; charset=utf-8",
                    url: "FetchCustomer.asmx/GetCustomer",
                    data: "{ customerID: '" + "001" + "'}",
                    dataType: "json",
                    success: function (data) {
                        $("#CustomerDetails").html(data.d);
                    }
                });
            });
        });
    </script>
    </head>

    The result:


    You could get JSON data, then use this data based on your requirement.

    For more information, click here to refer about JavaScript API for Office reference

                
    • Marked as answer by Pacecal Friday, July 29, 2016 7:24 AM
    Friday, July 29, 2016 6:41 AM

All replies

  • >>>I've added a MS SQL Server (database.mdf) in my Excel task pane app and populated with SQL, great ! :). I would like to write and read to it from home.js

    According to your description, since the most basic Office Add-in consists of a static HTML page that is displayed inside an Office application, but doesn't interact with either the Office document or any other Internet resource. However, because it is a web application, you can use any technologies, both client and server side, that your hosting provider supports (such as ASP.net, PHP, or Node.js). To interact with Office clients and documents, you can use the office.js JavaScript API that we provide. I suggest that you could use a web API that you can use to retrieve the data from your SQL Server.

    For more information, click here to refer about Office Add-ins platform overview

    Thursday, July 28, 2016 2:27 AM
  • Please read my question...

    I've studied most examples: http://dev.office.com/code-samples#?filters=office%20add-ins

    I can't find an example that use a sql server database or other method to read stored values stored in a database.

    I need an example that's read information out of a database or other resource.

    Thursday, July 28, 2016 5:18 AM
  • >>>I can't find an example that use a sql server database or other method to read stored values stored in a database.

    The JavaScript API for Office enables you to create web applications that interact with the object models in Office host applications. Your application will reference the office.js library, which is a script loader. The office.js library loads the object models that are applicable to the Office application that is running the add-in. 

    You are not able to use JavaScript API for Office to read stored values stored in a database, so I suggest that you could use ASP.Net server side technology to retrieve data from SQL Server, please refer to below steps and codes:

    Right click to Add "New Item" then add Web Service(ASMX) named "FetchCustomer"

    /// <summary>
    /// Summary description for FetchCustomer
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
    [System.Web.Script.Services.ScriptService]
    public class FetchCustomer : System.Web.Services.WebService
    {
    
        [WebMethod]
        public string GetCustomer(string customerID)
        {
            //please modify these codes based on your requirement
            //you code that connect Sql Server then read data from Sql Server
            string jsonResult = string.Empty;
    
            var jsonSerialiser = new JavaScriptSerializer();
    
            List<Customer> customers = BuildData();
            jsonResult = jsonSerialiser.Serialize(customers.Where(cust=>cust.CustomerID==customerID).ToList<Customer>());
    
            return jsonResult;
        }
    
        public List<Customer> BuildData()
        {
            return new List<Customer> {new Customer{CustomerID="001",CustomerName="John"},new Customer{CustomerID="002",CustomerName="Tom"}};
        }
    }
    public class Customer
    {
        public string CustomerID { get; set; }
        public string CustomerName { get; set; }
    
    }


    Open Home.html

    <body>
      ......
      <div id="CustomerDetails">
      </div>
      <button id="get-data-from-database">Get data from database</button>
      ......
    </body>
    <head>
    ......
    <script type="text/javascript">
        $(document).ready(function () {
            $('#get-data-from-database').click(function () {
                $("#CustomerDetails").html("Starting...");
                $.ajax({
                    type: "POST",
                    contentType: "application/json; charset=utf-8",
                    url: "FetchCustomer.asmx/GetCustomer",
                    data: "{ customerID: '" + "001" + "'}",
                    dataType: "json",
                    success: function (data) {
                        $("#CustomerDetails").html(data.d);
                    }
                });
            });
        });
    </script>
    </head>

    The result:


    You could get JSON data, then use this data based on your requirement.

    For more information, click here to refer about JavaScript API for Office reference

                
    • Marked as answer by Pacecal Friday, July 29, 2016 7:24 AM
    Friday, July 29, 2016 6:41 AM
  • Tnx a  lot, this is great!!!

     public List<Customer> BuildData()
            {
                return new List<Customer> {
                    new Customer { CustomerID = "001", CustomerName = "John", WorkID = "001" },
                    new Customer { CustomerID = "002", CustomerName = "Tom", WorkID = "001" },
                    new Customer { CustomerID = "003", CustomerName = "David", WorkID = "002" }
                };
            }
    
            public List<Work> BuildData()
            {
                return new List<Work> {
                    new Work { WorkID = "001", WorkName = "New York" },
                    new Work { WorkID = "002", WorkName = "Dallas" },
                    new Work { WorkID = "003", WorkName = "Washington" }
                };
            }
    So the next step for me is to figer out how to get the relations working. How can i generate John is working in New York? Another step is to populate the data in Excel.


    • Edited by Pacecal Friday, July 29, 2016 7:27 AM
    Friday, July 29, 2016 7:24 AM
  • >>>How can i generate John is working in New York? Another step is to populate the data in Excel.

    According to your description, you could parse JSON data into array, then write of values from an array to a range object, refer to the sample code shows writing of values from an array to a range object:
    // Run a batch operation against the Excel object model. Use the context argument to get access to the Excel document.
    Excel.run(function (ctx) {
    
        // Create a proxy object for the sheet
        var sheet = ctx.workbook.worksheets.getActiveWorksheet();
        // Values to be updated
        var values = [
                     ["Type", "Estimate"],
                     ["Transportation", 1670]
                     ];
        // Create a proxy object for the range
        var range = sheet.getRange("A1:B2");
    
        // Assign array value to the proxy object's values property.
        range.values = values;
    
        // Queue a command to load the text property for the proxy range object.
        range.load('text');
    
        // Synchronizes the state between JavaScript proxy objects and real objects in Excel by executing instructions queued on the context
        return ctx.sync().then(function() {
                console.log("Done");
        });
    }).catch(function(error) {
            console.log("Error: " + error);
            if (error instanceof OfficeExtension.Error) {
                console.log("Debug info: " + JSON.stringify(error.debugInfo));
            }
    });

    For more information, click here to refer about Excel JavaScript API programming overview

    Tuesday, August 2, 2016 9:30 AM
  • Thx for the code that put's data in Excel ! :) There are enough samples on the internet.

    What is the syntax to parse the array?

    var values = new Customer("001")? How can i call the procedure from asmx? I tried, but with no luuk.

    How can i get a single value instead of an array??

    How can i use json as relational database?

    Tnx for your patience :)


    • Edited by Pacecal Tuesday, August 2, 2016 10:20 AM
    Tuesday, August 2, 2016 10:20 AM
  • Hi Pacecal,

    According to your description, the most basic Office Add-in consists of a static HTML page that is displayed inside an Office application, but doesn't interact with either the Office document or any other Internet resource. However, because it is a web application, you can use any technologies, both client and server side, that your hosting provider supports (such as ASP.net, PHP, or Node.js). To interact with Office clients and documents, you can use the office.js JavaScript API that we provide.

    These issues is not related to develop Apps for Office 2013, but relate to web application client and server side technologies. So I suggest that you could start with ASP.Net and JQuery to learn web application client and server side technologies.

    Thanks for your understanding.

    Wednesday, August 3, 2016 9:52 AM
  • Hai David,

    I've started a new disussion.

    Wednesday, August 3, 2016 12:37 PM