express下 tedious 连接SQL Server实例

参考链接
 参考博客
Connection的参数：
server(host),
username,
password,
options,
a callback function.
是否加密：可以设置options: {encrypt: true/false}
Request的参数：
SQL statement,
a callback function

const { Connection, Request } = require("tedious");

const config = {
  authentication: {
    options: {
      userName: "填写登录名", // update me
      password: "填写登录密码" // update me
    },
    type: "default"
  },
  server: "localhost", // update me
  options: {
    database: "填写数据库名", //update me
    encrypt: false  //设置为true时会连接失败 Failed to connect to localhost:1433 - self signed certificate
  }
};

var connection = new Connection(config);

// 建立连接，验证通过执行Request
connection.on('connect', function(err) {
  if(err) {
    console.log('Error: ', err)
    throw err;
  }
  // If no error, then good to go...
  executeStatement();
});
 
// Initialize the connection.
connection.connect();

function executeStatement() {
  request = new Request("填写SQL语句", function(err, rowCount) {//rowCount是语句执行影响行数
     if (err) {
       console.log(err);
     } else {
       console.log(rowCount + ' rows');
     }
     connection.close() //关闭连接
   });
	
	//处理select返回的数据
	request.on('row', function(columns) {
	  columns.forEach(function(column) {
	    console.log(column.value);
	  });
	});
	//执行状态返回
	request.on('doneProc',function(r,m,status){
		//成功返回0,一般不会用到,在Request的回调判断err即可
		if(status)
			rows = request;
	});

	// In SQL Server 2000 you may need: connection.execSqlBatch(request);
	connection.execSql(request);
 }

测试在控制台运行如下代码：(得先cd到对应目录)

node 文件名.js

封装的版本

参考博客

//mssql.js
var Connection = require('tedious').Connection;
var Request = require('tedious').Request;
// const { Connection, Request } = require("tedious");
const config = {
    authentication: {
        options: {
            userName: "登录名", // update me
            password: "密码" // update me
        },
        type: "default"
    },
    server: "localhost", // update me 服务地址
    options: {
        database: "mathdrill", //update me 数据库名
        encrypt: false  //设置为true时会连接失败 Failed to connect to localhost:1433 - self signed certificate
    }
}
exports.mssql = function () {
    this.connection = new Connection(config);

    this.query = function (str, callback) {          //执行查询  
        var connection = this.connection;
        var rows = {};
        connection.on('connect', function (err) {                 //连接数据库，执行匿名函数  
            if (err) {
                callback({ 'err': err['message'] + '请检查账号、密码是否正确,且数据库存在' });
            } else {
                var request = new Request(str, function (err, rowCount) {
                    if (err) err = { 'err': err['message'] };
                    callback(err, rows);
                    connection.close();
                });

                var n = 0;
                request.on('row', function (columns) {                            //查询成功数据返回  
                    rows[n] = {};
                    columns.forEach(function (column) {
                        rows[n][column.metadata.colName] = column.value;        //获取数据            
                    });
                    n++;
                });

                connection.execSql(request);                                 //执行sql语句  
            }
        });
    }
	//下行为必须添加
    this.connection.connect();
}

//test.js
 var mssql = require('./mssql.js');  

 var conn = new mssql.mssql();  
 conn.query('SQL语句', function(err,data){  
         if(!err){  
             console.log(data)       //成功返回数据  
         }
         else {  
             console.log(err)      //出错返回  
         }
     }
 );