分享一个nodejsexpress连接sqlserver的单元

网站一般都是连mysql&＃xff0c;我们搞windows app的 都是用sqlserver

必须连mssql&＃xff0c;而mssql不用连接池则四处报错

寻得一很好用的源码&＃xff1a;

//mssql.js
/***sqlserver Model**/
const mssql &＃61; require("mssql");
const conf &＃61; require("./connect.js");const pool &＃61; new mssql.ConnectionPool(conf)
const poolConnect &＃61; pool.connect()pool.on(&＃39;error&＃39;, err &＃61;> {console.log(&＃39;error: &＃39;, err)
})
/*** 自由查询* &＃64;param sql sql语句&＃xff0c;例如: &＃39;select * from news where id &＃61; &＃64;id&＃39;* &＃64;param params 参数&＃xff0c;用来解释sql中的&＃64;*&＃xff0c;例如&＃xff1a; { id: id }* &＃64;param callBack 回调函数*/
let querySql &＃61; async function (sql, params, callBack) {try {let ps &＃61; new mssql.PreparedStatement(await poolConnect);if (params !&＃61; "") {for (let index in params) {if (typeof params[index] &＃61;&＃61; "number") {ps.input(index, mssql.Int);} else if (typeof params[index] &＃61;&＃61; "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);});});});} catch (e) {console.log(e)}
};/*** 按条件和需求查询指定表* &＃64;param tableName 数据库表名&＃xff0c;例&＃xff1a;&＃39;news&＃39;* &＃64;param topNumber 只查询前几个数据&＃xff0c;可为空&＃xff0c;为空表示查询所有* &＃64;param whereSql 条件语句&＃xff0c;例&＃xff1a;&＃39;where id &＃61; &＃64;id&＃39;* &＃64;param params 参数&＃xff0c;用来解释sql中的&＃64;*&＃xff0c;例如&＃xff1a; { id: id }* &＃64;param orderSql 排序语句&＃xff0c;例&＃xff1a;&＃39;order by created_date&＃39;* &＃64;param callBack 回调函数*/
let select &＃61; async function (tableName, topNumber, whereSql, params, orderSql, callBack) {try {let ps &＃61; new mssql.PreparedStatement(await poolConnect);let sql &＃61; "select * from " &＃43; tableName &＃43; " ";if (topNumber !&＃61; "") {sql &＃61; "select top(" &＃43; topNumber &＃43; ") * from " &＃43; tableName &＃43; " ";}sql &＃43;&＃61; whereSql &＃43; " ";if (params !&＃61; "") {for (let index in params) {if (typeof params[index] &＃61;&＃61; "number") {ps.input(index, mssql.Int);} else if (typeof params[index] &＃61;&＃61; "string") {ps.input(index, mssql.NVarChar);}}}sql &＃43;&＃61; 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);});});});} catch (e) {console.log(e)}
};/*** 查询指定表的所有数据* &＃64;param tableName 数据库表名* &＃64;param callBack 回调函数*/
let selectAll &＃61; async function (tableName, callBack) {try {let ps &＃61; new mssql.PreparedStatement(await poolConnect);let sql &＃61; "select * from " &＃43; tableName &＃43; " ";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);});});});} catch (e) {console.log(e)}
};/*** 添加字段到指定表* &＃64;param addObj 需要添加的对象字段&＃xff0c;例&＃xff1a;{ name: &＃39;name&＃39;, age: 20 }* &＃64;param tableName 数据库表名* &＃64;param callBack 回调函数*/
let add &＃61; async function (addObj, tableName, callBack) {try {let ps &＃61; new mssql.PreparedStatement(await poolConnect);let sql &＃61; "insert into " &＃43; tableName &＃43; "(";if (addObj !&＃61; "") {for (let index in addObj) {if (typeof addObj[index] &＃61;&＃61; "number") {ps.input(index, mssql.Int);} else if (typeof addObj[index] &＃61;&＃61; "string") {ps.input(index, mssql.NVarChar);}sql &＃43;&＃61; index &＃43; ",";}sql &＃61; sql.substring(0, sql.length - 1) &＃43; ") values(";for (let index in addObj) {if (typeof addObj[index] &＃61;&＃61; "number") {sql &＃43;&＃61; addObj[index] &＃43; ",";} else if (typeof addObj[index] &＃61;&＃61; "string") {sql &＃43;&＃61; "&＃39;" &＃43; addObj[index] &＃43; "&＃39;" &＃43; ",";}}}sql &＃61; sql.substring(0, sql.length - 1) &＃43; ") SELECT &＃64;&＃64;IDENTITY id"; // 加上SELECT &＃64;&＃64;IDENTITY id才会返回idps.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);});});});} catch (e) {console.log(e)}
};/*** 更新指定表的数据* &＃64;param updateObj 需要更新的对象字段&＃xff0c;例&＃xff1a;{ name: &＃39;name&＃39;, age: 20 }* &＃64;param whereObj 需要更新的条件&＃xff0c;例: { id: id }* &＃64;param tableName 数据库表名* &＃64;param callBack 回调函数*/
let update &＃61; async function (updateObj, whereObj, tableName, callBack) {try {let ps &＃61; new mssql.PreparedStatement(await poolConnect);let sql &＃61; "update " &＃43; tableName &＃43; " set ";if (updateObj !&＃61; "") {for (let index in updateObj) {if (typeof updateObj[index] &＃61;&＃61; "number") {ps.input(index, mssql.Int);sql &＃43;&＃61; index &＃43; "&＃61;" &＃43; updateObj[index] &＃43; ",";} else if (typeof updateObj[index] &＃61;&＃61; "string") {ps.input(index, mssql.NVarChar);sql &＃43;&＃61; index &＃43; "&＃61;" &＃43; "&＃39;" &＃43; updateObj[index] &＃43; "&＃39;" &＃43; ",";}}}sql &＃61; sql.substring(0, sql.length - 1) &＃43; " where ";if (whereObj !&＃61; "") {for (let index in whereObj) {if (typeof whereObj[index] &＃61;&＃61; "number") {ps.input(index, mssql.Int);sql &＃43;&＃61; index &＃43; "&＃61;" &＃43; whereObj[index] &＃43; " and ";} else if (typeof whereObj[index] &＃61;&＃61; "string") {ps.input(index, mssql.NVarChar);sql &＃43;&＃61; index &＃43; "&＃61;" &＃43; "&＃39;" &＃43; whereObj[index] &＃43; "&＃39;" &＃43; " and ";}}}sql &＃61; 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);});});});} catch (e) {console.log(e)}
};/*** 删除指定表字段* &＃64;param whereSql 要删除字段的条件语句&＃xff0c;例&＃xff1a;&＃39;where id &＃61; &＃64;id&＃39;* &＃64;param params 参数&＃xff0c;用来解释sql中的&＃64;*&＃xff0c;例如&＃xff1a; { id: id }* &＃64;param tableName 数据库表名* &＃64;param callBack 回调函数*/
let del &＃61; async function (whereSql, params, tableName, callBack) {try {let ps &＃61; new mssql.PreparedStatement(await poolConnect);let sql &＃61; "delete from " &＃43; tableName &＃43; " ";if (params !&＃61; "") {for (let index in params) {if (typeof params[index] &＃61;&＃61; "number") {ps.input(index, mssql.Int);} else if (typeof params[index] &＃61;&＃61; "string") {ps.input(index, mssql.NVarChar);}}}sql &＃43;&＃61; 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);});});});} catch (e) {console.log(e)}
};exports.config &＃61; conf;
exports.del &＃61; del;
exports.select &＃61; select;
exports.update &＃61; update;
exports.querySql &＃61; querySql;
exports.selectAll &＃61; selectAll;
exports.add &＃61; add;

连接字符串

// var mysql &＃61; require(&＃39;mysql&＃39;);
// var connection &＃61; mysql.createConnection({
// host: &＃39;localhost&＃39;,
// user: &＃39;root&＃39;,
// password: &＃39;123456&＃39;,
// database: &＃39;vantstore&＃39;
// });// module.exports &＃61; connection;//配置数据库参数module.exports &＃61; {user: &＃39;sa&＃39;,password: &＃39;123456&＃39;,server: &＃39;127.0.0.1&＃39;,database: &＃39;xxxx&＃39;,requestTimeout: 180000, // for timeout settingconnectionTimeout: 180000, // for timeout setting"options": {"encrypt": false, // need to stop ssl checking in case of local db"enableArithAbort": true}
}

使用方法&＃xff1a;

const mssqldb &＃61; require("../db/mssql.js");//注册
router.post(&＃39;/reg&＃39;, function (req, res, next) {var userName &＃61; req.body.userName;var password &＃61; req.body.password;var usercode &＃61; req.body.usercode;var sql &＃61; &＃39;SELECT 1 FROM m_cxt_userinfo WHERE usercode&＃61;&＃64;usercode&＃39;;
//这里mssqldb.querySql(sql, {usercode: userName}, function (err, result) {if (err) {res.json({status: 500,msg: err,data: &＃39;&＃39;});return;}if (result.recordset.length !&＃61;&＃61; 0) {res.json({status: 0,msg: &＃39;用户已存在&＃xff01;&＃39;,data: &＃39;&＃39;});return;}