Express调用mssql驱动公共类dbHelper
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了Express调用mssql驱动公共类dbHelper，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含7731字，纯文字阅读大概需要12分钟。
内容图文
*
* Created by Administrator on 2015/9/16.
*/
var mssql = require(‘mssql‘);
var user = "sa",
password = "sa",
server = "192.168.20.135",
database = "GG_ChatDB";
/**
* 默认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);
}
}
}
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();
};
//querySql("select id as roleId,roleName from dbo.role","",function(err,recordset){
// console.dir(recordset);
//});
/**
* 带参数查询
* @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.userTable",3,"","","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 + " ";
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) {
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) + ")";
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({userName:"admin",loginTimes:555},"dbo.userTable",function(err,recordset){
// console.log(recordset);
//});
/**
* 修改
* @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 "; //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);
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();
};
//update({userName:"awdawdaw",password:"awdawdwad"},{id:1},"dbo.userTable",function(err,recordset){
// console.log(recordset);
//});
/**
* 删除
* @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;
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;
Express调用mssql驱动公共类dbHelper
标签：
本文系统来源：http://www.cnblogs.com/stone_w/p/4815887.html
内容总结
以上是互联网集市为您收集整理的Express调用mssql驱动公共类dbHelper全部内容，希望文章能够帮你解决Express调用mssql驱动公共类dbHelper所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】