Connection Error in mssql node module

Question

I am using mssql node module in my project, All are good till the time I am not make any async call.

On the multiple async call its start give me connection error "Connection is closed". can someone look into the code and help me out.

==== SqlServerDAL.js is some thing like this

 var sql = require('mssql');
 var _sqlConfig = require("../../../../config/dbconfig.json");

function SqlServerDAL() {
    this._sqlConfig = _sqlConfig;
    this._sqlConnection = null;
}

/**
 * @param : request > request or query parmanter to excute sql query
 * @param : callback > after execution neect to send response back to serr  
 * @param : sqlReqConf > to build the requst object from the configuration
 */
SqlServerDAL.prototype.executeRequest = function (request, callback) {

    var self = this;

    self._sqlConnection = new sql.Connection(this._sqlConfig);

    this._sqlConnection.connect().then(function () {

        var _sqlRequest = new sql.Request(self._sqlConnection);

        try {
            _sqlRequest.input('someVar','someValue')

            _sqlRequest.execute('myStoreProc').then(function (recordsets) {
                callback(null, recordsets);
            }).catch(function (sqlEx) {
                callback({
                    code: "ERR0003",
                    message: sqlEx.message,
                    type: sqlEx.name
                }, null);
            });
        } catch (sqlEx) {
            callback({
                code: "ERR0004",
                message: sqlEx.message,
                type: sqlEx.name
            }, null);
        }

    }).catch(function (sqlEx) {
        callback({
            code: "ERR0001",
            message: sqlEx.message,
            type: sqlEx.name
        }, null);
    });
};

module.exports = new SqlServerDAL();

this Above file I am calling from dal.js

/*jslint vars: true, plusplus: true, devel: true, nomen: true, indent: 4, maxerr: 50 */ /*global require, module,logger */

    var Q = require("q");
    var sqlServerDAL = require('./sqlDAL/sqlServerDAL');

    /**
     * 
     * @constructor:  
     */

    function DAL() {}

    /**
     * @param : customRequst : this json object will come from respective dbschema files,
     *                       This object bening used to build sqlRequest object
     */

    DAL.prototype.executeRequest = function (customRequest) {

        var deferred = Q.defer();

        sqlServerDAL.executeRequest(customRequst, function (sqlEx, recordsets) {

            if (recordsets) {
                logger.info("_sqlRequest is successfully executed:" + JSON.stringify(recordsets));
                deferred.resolve(recordsets);
            } else if (sqlEx) {
                deferred.reject({
                    code: sqlEx.code,
                    message: sqlEx.message,
                    type: sqlEx.name
                });
            }
        });

        return deferred.promise;
    };


    module.exports = new DAL();

// my controller is like

 app.use("mycontroller", function(req, res, next){
 var dal = new Dal();
 dal.executRequest(function(result){
     res.send(result)
 } , function(err){
   console.log("got some error" + errr)
 })
  })

Answer 1

Yes I got the answer, Due to async call new Request(self.connection) is getting old object which is closed by the older function call.. So the Answer is we need to create new request like this.

this._sqlConnection.connect().then(function (connection ) {
  var _sqlRequest = new sql.Request(connection);
})