首先要安装 mssql 包: npm install mssql
第一种:
var sql = require('mssql');
//连接方式2:"mssql://用户名:密码@ip地址:1433(默认端口号)/数据库名称"
sql.connect("mssql://sa:*******@localhost:1433/Test").then(function() {
//sql.connect("mssql://sa:123@localhost:1433/test").then(function() {
// Query
new sql.Request().query('INSERT INTO [dbo].[T1]([fName]) VALUES(77777)').then(function(recordset) {
console.log(recordset);
});
// Stored Procedure
});
第二种:
dbHelper.js
var mssql = require('mssql');
var db = {};
var config = {
user: 'sa',
password: '*******',
server: '127.0.0.1',
database: 'Test',
port:1433,
options: {
encrypt: true // windows on
},
pool: {
min: 0,
max: 10,
idleTimeoutMillis: 3000
}
};
//执行sql,返回数据.
db.sql = function (sql, callBack) {
var connection = new mssql.ConnectionPool(config, function (err) {
if (err) {
console.log(err);
return;
}
var ps = new mssql.PreparedStatement(connection);
ps.prepare(sql, function (err) {
if (err){
console.log(err);
return;
}
ps.execute('', function (err, result) {
if (err){
console.log(err);
return;
}
ps.unprepare(function (err) {
if (err){
console.log(err);
callback(err,null);
return;
}
callBack(err, result);
});
});
});
});
};
module.exports = db;
var db = require('./dbHelper.js');
db.sql('select * from T1',function(err,result){
if (err) {
console.log(err);
return;
}
console.log('===',result);
});
脚本:
CREATE TABLE [dbo].[T1](
[fId] [int] IDENTITY(1,1) NOT NULL,
[fName] [nvarchar](50) NULL,
CONSTRAINT [PK_T1] PRIMARY KEY CLUSTERED
(
[fId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
数据库事务:dbTransaction.js
var dbTransaction = {};
var sql = require('mssql');
var config = {
user: 'sa',
password: 'xinfu978',
server: '127.0.0.1',
database: 'Test',
port:1433,
options: {
encrypt: true // Use this if you're on Windows Azure
},
pool: {
min: 0,
max: 10,
idleTimeoutMillis: 3000
}
};
dbTransaction.getTransaction = function(callback){
var connection = new sql.ConnectionPool(config, function (err) {
var transaction = new sql.Transaction(connection);
callback(sql, transaction);
}) ;
};
module.exports = dbTransaction;
var async = require('async');
var dbTransaction = require('./dbTransaction.js');
dbTransaction.getTransaction(function(sql, transaction){
//开启事物
transaction.begin(function(err) {
if (err) {
console.log(err);
return;
}
//定义一个变量,如果自动回滚,则监听回滚事件并修改为true,无须手动回滚
var rolledBack = false;
//监听回滚事件
transaction.on('rollback', function(aborted) {
console.log('监听回滚');
console.log('aborted值 :', aborted);
rolledBack = true;
});
//监听提交事件
transaction.on('commit', function() {
console.log('监听提交');
rolledBack = true;
});
var request = new sql.Request(transaction);
var task1 = function(callback){
request.query("INSERT INTO [dbo].[T1]([fName]) VALUES(1111)", function(err, result) {
if (err) {
console.log(err);
callback(err, null);
return;
}
console.log('第一条语句成功');
callback(null, result) ;
}) ;
};
var task2 = function(callback){
request.query("INSERT INTO [dbo].[T1]([fName]) VALUES(22222)", function(err, result) {
if (err) {
console.log(err);
callback(err, null);
return;
}
console.log('第二条语句成功');
callback(null, result) ;
}) ;
};
var task3 = function(callback){
request.query("INSERT INTO [dbo].[T12]([fName]) VALUES(3333)", function(err, result) {
if (err) {
console.log(err);
callback(err, null);
return;
}
console.log('第三条语句成功');
callback(null, result) ;
}) ;
} ;
async.series([task1, task2, task3],function(err,result){
// var err = "11";
if (err) {
console.log('出现错误,执行回滚');
if (!rolledBack) {
//如果sql语句错误会自动回滚,如果程序错误手动执行回滚,不然事物会一致挂起.
transaction.rollback(function(err) {
if (err) {
console.log('rollback err :',err);
return;
}
console.log('回滚成功');
});
}
} else {
console.log('无错误,执行提交');
//执行提交
transaction.commit(function(err) {
if (err) {
console.log('commit err :',err);
return;
}
console.log('提交成功');
});
}
}) ;
});
}) ;