直接上程式碼：
/**
* Created by chaozhou on 2015/9/18.
*/
var mssql = require('mssql');
var user = "sa",
password = "sa",
server = "192.168.20.132",
database = "ggcms";
/**
* 預設config物件
* @type {{user: string, password: string, server: string, database: string, options: {encrypt: boolean}, pool: {min: number, idleTimeoutMillis: number}}}
*/
var config = {
user: user,
password: password,
server: server, // You can use 'localhost\\instance' to connect to named instance
database: database,
options: {
encrypt: true // Use this if you're on Windows Azure
},
pool: {
min: 0,
idleTimeoutMillis: 3000
}
};
/**
* 初始化config
* @param user
* @param password
* @param server
* @param database
*/
var initConfig = function (user, password, server, database) {
config = {
user: user,
password: password,
server: server, // You can use 'localhost\\instance' to connect to named instance
database: database,
options: {
encrypt: true // Use this if you're on Windows Azure
},
pool: {
min: 0,
idleTimeoutMillis: 3000
}
}
};
/**
* 恢復預設config
*/
var restoreDefaults = function () {
config = {
user: user,
password: password,
server: server, // You can use 'localhost\\instance' to connect to named instance
database: database,
options: {
encrypt: true // Use this if you're on Windows Azure
},
pool: {
min: 0,
idleTimeoutMillis: 3000
}
};
};
/**
* 執行原生Sql
* @param sql
* @params 引數物件(可為空，為空表示不加引數)
* @param callBack(err,recordset)
*/
var querySql = function (sql, params, callBack) {
var connection = new mssql.Connection(config, function (err) {
var ps = new mssql.PreparedStatement(connection);
if (params != "") {
for (var index in params) {
if (typeof params[index] == "number") {
ps.input(index, mssql.Int);
} else if (typeof params[index] == "string") {
ps.input(index, mssql.NVarChar);
}
}
}
console.log("sql:" + sql);
ps.prepare(sql, function (err) {
if (err)
console.log(err);
ps.execute(params, function (err, recordset) {
callBack(err, recordset);
ps.unprepare(function (err) {
if (err)
console.log(err);
});
});
});
});
restoreDefaults();
};
/**
* 帶引數查詢
* @param tableName 表名
* @param topNumber 前topNumber條
* @param whereSql whereSql
* @param params 查詢引數物件（可為""，為""表示不加任何引數，如果此項為""，則whereSql必須也為""）
* @param orderSql 排序Sql（可為""，為""表示不排序）
* @param callBack
*/
var select = function (tableName, topNumber, whereSql, params, orderSql, callBack) {
var connection = new mssql.Connection(config, function (err) {
var ps = new mssql.PreparedStatement(connection);
var sql = "select * from " + tableName + " ";
if (topNumber != "") {
sql = "select top(" + topNumber + ") * from " + tableName + " ";
}
sql += whereSql + " ";
if (params != "") {
for (var index in params) {
if (typeof params[index] == "number") {
ps.input(index, mssql.Int);
} else if (typeof params[index] == "string") {
ps.input(index, mssql.NVarChar);
}
}
}
sql += orderSql;
console.log(sql);
ps.prepare(sql, function (err) {
if (err)
console.log(err);
ps.execute(params, function (err, recordset) {
callBack(err, recordset);
ps.unprepare(function (err) {
if (err)
console.log(err);
});
});
});
});
restoreDefaults();
};
//select("dbo.userInfo",3,"where id = @id",{id:1},"order by id",function(err,recordset){
// console.log(recordset);
//});
/**
* 查詢所有
* @param tableName
* @param callBack
*/
var selectAll = function (tableName, callBack) {
var connection = new mssql.Connection(config, function (err) {
var ps = new mssql.PreparedStatement(connection);
var sql = "select * from " + tableName + " ";
console.log("sql:" + sql);
ps.prepare(sql, function (err) {
if (err)
console.log(err);
ps.execute("", function (err, recordset) {
callBack(err, recordset);
ps.unprepare(function (err) {
if (err)
console.log(err);
});
});
});
});
restoreDefaults();
};
//selectAll("dbo.userTable",function(err,recordset){
// console.log(recordset);
//});
/**
* 新增
* @param addObj 新增物件（必填）
* @param tableName 表名
* @param callBack(err,recordset)
*/
var add = function(addObj,tableName,callBack){ //{id:3,userName:'admin'...} insert into dbo.tags(id,name) values(@id,@name)
var connection = new mssql.Connection(config, function (err) {
var ps = new mssql.PreparedStatement(connection);
var sql = "insert into " + tableName + "(";
if (addObj != "") {
for (var index in addObj) {
if (typeof addObj[index] == "number") {
ps.input(index, mssql.Int);
} else if (typeof addObj[index] == "string") {
ps.input(index, mssql.NVarChar);
} else if (typeof addObj[index] == "object") {
ps.input(index, mssql.DateTime);
}
sql += index + ",";
}
sql = sql.substr(0, sql.length - 1) + ")values(";
for (var index in addObj) {
sql = sql + "@" + index + ",";
}
}
sql = sql.substr(0, sql.length - 1) + ")";
console.log(sql);
ps.prepare(sql, function (err) {
if (err)
console.log(err);
ps.execute(addObj, function (err, recordset) {
callBack(err, recordset);
ps.unprepare(function (err) {
if (err)
console.log(err);
});
});
});
});
restoreDefaults();
};
//add({"updateTime":new Date(),name:'awdaw,3awdwa,3434'},"dbo.template",function(err,recordset){
// console.log(recordset);
//});
//var add = function (addObj, tableName, callBack) {
// var connection = new mssql.Connection(config, function (err) {
// var ps = new mssql.PreparedStatement(connection);
// var sql = "insert into " + tableName + "(";
// if (addObj != "") {
// for (var index in addObj) {
// if (typeof addObj[index] == "number") {
// ps.input(index, mssql.Int);
// } else if (typeof addObj[index] == "string") {
// ps.input(index, mssql.NVarChar);
// }
// sql += index + ",";
// }
// sql = sql.substring(0, sql.length - 1) + ") values(";
// for (var index in addObj) {
// if (typeof addObj[index] == "number") {
// sql += addObj[index] + ",";
// } else if (typeof addObj[index] == "string") {
// sql += "'" + addObj[index] + "'" + ",";
// }
// }
// }
// sql = sql.substring(0, sql.length - 1) + ")";
// console.log("sql:" + sql);
// ps.prepare(sql, function (err) {
// if (err)
// console.log(err);
// ps.execute(addObj, function (err, recordset) {
// callBack(err, recordset);
// ps.unprepare(function (err) { //回收連線至連線池
// if (err)
// console.log(err);
// });
// });
// });
// });
// restoreDefaults();
//};
/**
* 修改
* @param updateObj 修改內容（必填）
* @param whereObj 修改物件（必填）
* @param tableName 表名
* @param callBack(err,recordset)
*/
var update = function(updateObj, whereObj, tableName, callBack){
var connection = new mssql.Connection(config, function (err) {
var ps = new mssql.PreparedStatement(connection);
var sql = "update " + tableName + " set ";
if (updateObj != "") {
for (var index in updateObj) {
if (typeof updateObj[index] == "number") {
ps.input(index, mssql.Int);
} else if (typeof updateObj[index] == "string") {
ps.input(index, mssql.NVarChar);
} else if (typeof updateObj[index] == "object") {
ps.input(index, mssql.DateTime);
}
sql += index + "=@" + index + ",";
}
sql = sql.substr(0, sql.length - 1) + " where ";
}
if (whereObj != "") {
for (var index in whereObj) {
if (typeof whereObj[index] == "number") {
ps.input(index, mssql.Int);
} else if (typeof whereObj[index] == "string") {
ps.input(index, mssql.NVarChar);
} else if (typeof whereObj[index] == "object") {
ps.input(index, mssql.DateTime);
}
sql += index + "=@" + index + ",";
}
}
sql = sql.substr(0, sql.length - 1);
var whereStr = JSON.stringify(whereObj);
var updateStr = JSON.stringify(updateObj);
whereObj = JSON.parse(updateStr.substr(0,updateStr.length -1) + "," + whereStr.substr(1,whereStr.length));
console.log(sql);
ps.prepare(sql, function (err) {
if (err)
console.log(err);
ps.execute(whereObj, function (err, recordset) {
callBack(err, recordset);
ps.unprepare(function (err) {
if (err)
console.log(err);
});
});
});
});
restoreDefaults();
};
//update({name:"awdawd",context:'awdaw33434',updateTime:'2015-09-25'},{id:2},"dbo.template",function(err,recordset){
// console.log(recordset);
//});
//var update = function (updateObj, whereObj, tableName, callBack) {
// var connection = new mssql.Connection(config, function (err) {
// var ps = new mssql.PreparedStatement(connection);
// var sql = "update " + tableName + " set "; //update userTable set userName = 'admin',loginTimes = 12,password = 'admin'
// if (updateObj != "") {
// for (var index in updateObj) {
// if (typeof updateObj[index] == "number") {
// ps.input(index, mssql.Int);
// sql += index + "=" + updateObj[index] + ",";
// } else if (typeof updateObj[index] == "string") {
// ps.input(index, mssql.NVarChar);
// sql += index + "=" + "'" + updateObj[index] + "'" + ",";
// }
// }
// }
// sql = sql.substring(0, sql.length - 1) + " where ";
// if (whereObj != "") {
// for (var index in whereObj) {
// if (typeof whereObj[index] == "number") {
// ps.input(index, mssql.Int);
// sql += index + "=" + whereObj[index] + " and ";
// } else if (typeof whereObj[index] == "string") {
// ps.input(index, mssql.NVarChar);
// sql += index + "=" + "'" + whereObj[index] + "'" + " and ";
// }
// }
// }
// sql = sql.substring(0, sql.length - 5);
// console.log("sql:" + sql);
// ps.prepare(sql, function (err) {
// if (err)
// console.log(err);
// ps.execute(updateObj, function (err, recordset) {
// callBack(err, recordset);
// ps.unprepare(function (err) { //回收連線至連線池
// if (err)
// console.log(err);
// });
// });
// });
// });
// restoreDefaults();
//};
/**
* 刪除
* @param deleteObj 刪除物件
* @param tableName 表名
* @param callBack(err,recordset)
*/
var del = function (whereSql, params, tableName, callBack) {
var connection = new mssql.Connection(config, function (err) {
var ps = new mssql.PreparedStatement(connection);
var sql = "delete from " + tableName + " ";
if (params != "") {
for (var index in params) {
if (typeof params[index] == "number") {
ps.input(index, mssql.Int);
} else if (typeof params[index] == "string") {
ps.input(index, mssql.NVarChar);
}
}
}
sql += whereSql;
console.log("sql:" + sql);
ps.prepare(sql, function (err) {
if (err)
console.log(err);
ps.execute(params, function (err, recordset) {
callBack(err, recordset);
ps.unprepare(function (err) { //回收連線至連線池
if (err)
console.log(err);
});
});
});
});
restoreDefaults();
};
//del("where id = @id",{id:16},"dbo.userTable",function(err,recordset){
// console.log(recordset);
//});
exports.initConfig = initConfig;
exports.config = config;
exports.del = del;
exports.select = select;
exports.update = update;
exports.querySql = querySql;
exports.restoreDefaults = restoreDefaults;
exports.selectAll = selectAll;
exports.add = add;