参考链接
参考博客
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) //出错返回
}
}
);
测试node test.js
, 成功会打印数据对象