Express调用mssql驱动公共类dbHelper

直接上代码:

/**
 * 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;

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏企鹅号快讯

卡巴斯基起诉美国国土安全部,称其下达禁用令前没给申辩机会

俄罗斯网络安全公司卡巴斯基实验室(Kaspersky Lab)12月18日对美国国土安全部提起诉讼,要求其取消对卡巴斯基杀毒软件的禁令。 此前的9月13日,美国...

1957
来自专栏安智客

《2018网络安全人才发展白皮书》全文

在9月18日举行的2018国家网络安全宣传周网络安全人才培养分论坛上,《2018网络安全人才发展白皮书》正式发布,公布了目前国内网络安全产业的相关数据。

2486
来自专栏域名资讯

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

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

23410
来自专栏美团技术团队

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

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

4144
来自专栏大数据文摘

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

41412
来自专栏点滴科技资讯

金融科技—数字技术正颠覆金融世界

金融科技(FinTech)正改变金融业的发展格局,并对金融业产生颠覆性影响。本信息图对全球金融科技发展格局进行了深入分析,值得一看! ? ? ? ? ? ?...

3007
来自专栏智能计算时代

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...

2706
来自专栏域名资讯

域名trafficked.com8万元海外结拍

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

1898
来自专栏大数据挖掘DT机器学习

Python抓取上海各地区房价平均值

作者:司开星 http://blog.csdn.net/chroming/article/details/46471155 用Python写了一个抓取上海地区...

4365
来自专栏域名资讯

亿欧网启用i+系列域名完成B1轮融资

中国500强企业沂州集团旗下的沂景投资已于2017年7月完成了对产业创新服务平台亿欧公司B1轮融资。 

420

扫码关注云+社区

领取腾讯云代金券