node.js 通过tedious 连接SQL SERVER

来源：互联网发布：校园网mac地址修改编辑：程序博客网时间：2024/05/02 00:28

github地址: https://github.com/tediousjs/tedious

安装: npm install tedious

例子:

var Connection = require('tedious').Connection;var Request = require('tedious').Request;var connection = new Connection({'userName':'sa',                    //用户名'password':'123456',    //密码'server':'127.0.0.1',               //数据库地址'options':{'port':1433,                //端口号'database':'test',          //数据库名'encrypt':true              //是否启用加密传输,测试了两台机器,一台开了无法连接}    });connection.on('connect',function(err){//判断有没有出错if(err){console.log(err);}else{//生成sql执行语句,并指定接收数据完毕后执行的回调函数var request = new Request('select * from tt',function(err,rowCount){//判断有没有出错if(err){console.log(err);}else rows['rowCount'] = rowCount; //rowCount是语句执行影响行数console.log(rows);connection.close();               //记得关闭连接});var rows = {};var n = 0;//查询数据返回,select才有返回,每次一行记录request.on('row',function(columns){rows[n] = {};//遍历出列名和值columns.forEach(function(s){rows[n][s.metadata.colName] = s.value;   //列名和值});n++;});//执行状态返回request.on('doneProc',function(r,m,status){//成功返回0,一般不会用到,在Request的回调判断err即可if(status)rows = request;});//执行语句connection.execSql(request);}     });

如果想执行 insert语句后返回id,可以在回调里再执行:

request = new Request("select @@identity",function(err, rowCount){    //这个是返回插入成功的idcallback(param);                                                  //把查询结果返回给回调函数});request.on('row', function(columns) { param['id'] = columns[0].value;   //获取id});   connection.execSql(request);

封装起来比较方便使用:

var Connection = require('tedious').Connection;var Request = require('tedious').Request;exports.mssql = function(config){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语句}});}}

保存成 mssql.js,然后在这样使用:

     var mssql = require('./mssql.js');     var conn = new mssql.mssql({'userName':'sa','password':'123456','server':'127.0.0.1','options':{'port':1344,'database':'test'}});     conn.query('select * from tt', function(err,data){if(!err){console.log(data)       //成功返回数据}else {console.log(err)      //出错返回}     );

1 0