nodejs封装mssql

对mssql操作Sqlserver数据库的基本封装: 记录一下:

/**
 * Created by chaozhou on 2015/9/18.
 */
var mssql = require('mssql');
var constclass = require('../config/constClass');

var user = constclass.db.user,
    password = constclass.db.password,
    server = constclass.db.server,
    database = constclass.db.database;

/**
 * 默认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
        useUTC: false
    },
    pool: {
        min: 10,
        max: 100,
        idleTimeoutMillis: 30000
    }
};

var connection = new mssql.Connection(config);
connection.on('error', function (err) {
    console.error(err);
});
connection.connect(function (err) {
    if (err) {
        console.error(err);
    }
});

/**
 * 执行原生Sql
 * @param sql
 * @params 参数对象(可为空,为空表示不加参数)
 * @param callBack(err,recordset)
 */
var querySql = function (sql, params, callBack) {
    var ps = new mssql.PreparedStatement(connection);
    if (params != "") {
        for (var index in params) {
            if (typeof params[index] == "number") {
                ps.input(index, mssql.BigInt);
            } 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);
            });
        });
    });

};

/**
 * 带参数查询
 * @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 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.BigInt);
            } 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);
            });
        });
    });
};


/**
 * 查询所有
 * @param tableName
 * @param callBack
 */
var selectAll = function (tableName, callBack) {
    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);
            });
        });
    });
};

/**
 * 添加
 * @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 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.BigInt);
            } 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) + ") output inserted.* 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);
            });
        });
    });
};

/**
 * 修改
 * @param updateObj     修改内容(必填)
 * @param whereObj      修改对象(必填)
 * @param tableName     表名
 * @param callBack(err,recordset)
 */
var update = function (updateObj, whereObj, tableName, callBack) {
    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.BigInt);
            } 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.BigInt);
            } 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);
            });
        });
    });
};

/**
 * 删除
 * @param deleteObj 删除对象
 * @param tableName 表名
 * @param callBack(err,recordset)
 */
var del = function (whereSql, params, tableName, callBack) {
    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.BigInt);
            } 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);
            });
        });
    });
};

//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.selectAll = selectAll;
exports.add = add;

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏智能计算时代

The Rise of Cognitive Business

When the original Watson won on the TV quiz show Jeopardy! in 2011, it was one c...

2748
来自专栏电光石火

mysql替换某个字段中的某个字符

Msql里面的某个表的某个字段里面存储的是一个人的地址,有一天这个地址的里面的某个地

1986
来自专栏域名资讯

域名trafficked.com8万元海外结拍

域名trafficked.com以1.25万美元(8万元)海外结拍。

1818
来自专栏域名资讯

永辉超市挥9.47亿元入股红旗连锁 但缺好域名

继与腾讯联姻后,永辉超市近期以9.47亿元的价格受让红旗连锁12%股份,双方将通过供应链整合来加强线上电商、社区门店等板块合作。停牌前,红旗连锁每股...

1990
来自专栏美团技术团队

【美团技术团队博客】RACSignal的Subscription深入分析

ReactiveCocoa是一个FRP的思想在Objective-C中的实现框架,目前在美团的项目中被广泛使用。对于ReactiveCocoa的基本用法,网上有...

3464
来自专栏域名资讯

价值22万的5字母域名sanwa.com被启用

如今是互联网时代,域名的需求量也非常大。最近一枚5字母双拼sanwa.com建站了。

20410
来自专栏智能计算时代

Earshot Builds with Watson APIs to Enhance its Marketing

Social Media IS Big Data. On any given day more than 500 million tweets and 55 m...

2676
来自专栏王磊的博客

Express调用mssql驱动公共类dbHelper

直接上代码: /** * Created by chaozhou on 2015/9/18. */ var mssql = require('mssql')...

3867
来自专栏大数据文摘

陆金所融资12亿美元的PPT长什么样?

31112
来自专栏数据猿

大数据投融资5月榜:共57起融资事件,总金额超91.3亿元

编辑 | abby 2017年5月份大数据领域共有57起企业融资事件,其中包括28家中国企业、22家美国企业、3家英国企业、2家印度企业、1家荷兰企业以及一家芬...

3408

扫码关注云+社区