msnodesql的安装
npm install msnodesql
使用msnodesql写的增删改查
var sql=require('msnodesql');
var conn_str="Driver={SQL Server Native Client 11.0};Server={127.0.0.1};Database=AccJiaowu;uid=sa;PWD=XXX;";
//添加
var insert = function(sqlscript,callback) {
sql.open(conn_str, function (err, conn) {
if (err) {
console.log('发生错误');
}
sql.queryRaw(conn_str,sqlscript, function (err, results) {
if (err) {
console.log(err);
callback(JSON.stringify("error"));
}else {
callback(JSON.stringify("ok"));
}
});
});
}
//修改
var update = function(sqlscript,callback) {
sql.open(conn_str, function (err, conn) {
if (err) {
console.log('发生错误');
}
sql.queryRaw(conn_str, sqlscript, function (err, results) {
if (err) {
console.log(err);
callback(JSON.stringify("error"));
}
else {
callback(JSON.stringify("ok"));
}
});
});
}
//查询返回集合
var selectList = function(sqlscript,callback) {
sql.open(conn_str, function (err, conn) {
if (err) {
console.log('发生错误');
}
sql.queryRaw(conn_str, sqlscript, function (err, results) {
if (err) {
console.log(err);
}else {
var txt=toJsonArray(results);
var jsonObj=eval("("+txt+")");
console.log(jsonObj);
callback(jsonObj);
}
});
});
}
//查询返回单个数据
var selectOne = function(sqlscript,callback) {
sql.open(conn_str, function (err, conn) {
if (err) {
console.log('发生错误');
}
sql.queryRaw(conn_str, sqlscript, function (err, result) {
if (err) {
console.log(err);
}else {
var txt=toJson(result);
var jsonObj=eval("("+txt+")");
console.log(jsonObj);
callback(jsonObj);
}
});
});
}
//将查询结果转成json串
function toJson(dt)
{
var jsonString;
if (dt != undefined && dt.rows.length == 1)
{
var rowLen=dt.rows.length;
var colLen=dt.meta.length;
jsonString="{";
for (var j = 0; j < colLen; j++)
{
if (j < colLen - 1)
{
jsonString+="\"" + dt.meta[j].name + "\":" + "\"" + dt.rows[0][j]+ "\",";
}
else if (j == colLen - 1)
{
jsonString+="\"" + dt.meta[j].name + "\":" + "\"" + dt.rows[0][j] + "\"";
}
}
jsonString+="}";
return jsonString;
}
return jsonString;
}
//将查询结果转成json数组
function toJsonArray(dt)
{
var jsonString;
if (dt != undefined && dt.rows.length > 0)
{
var rowLen=dt.rows.length;
var colLen=dt.meta.length;
jsonString="{";
jsonString+="\"data\":[";
for (var i = 0; i < rowLen; i++)
{
jsonString+="{";
for (var j = 0; j < colLen; j++)
{
if (j < colLen - 1)
{
jsonString+="\"" + dt.meta[j].name + "\":" + "\"" + dt.rows[i][j]+ "\",";
}
else if (j == colLen - 1)
{
jsonString+="\"" + dt.meta[j].name + "\":" + "\"" + dt.rows[i][j] + "\"";
}
}
if (i == rowLen - 1)
{
jsonString+="}";
}
else
{
jsonString+="},";
}
}
jsonString+="]}";
return jsonString;
}
return jsonString;
}
exports.insert = insert;
exports.update = update;
exports.selectOne = selectOne;
exports.selectList = selectList;
写sql调用封装的方法
var handlers = require("./handlers");
var result = "";
var http = require('http'),
url = require("url");
http.createServer(function (req,res){
var arg = url.parse(req.url,true).query; //方法一arg => aa=001&bb=002
console.log(arg);
console.log(url.parse(req.url).query);
var userID = arg.userID;
var userName = arg.userName;
var password = arg.password;
var age = arg.age;
var pathname = url.parse(req.url).pathname;
console.log("userID:"+userID+";userName:"+userName+";password="+password+";age:"+age+";pathname:"+pathname);
if(pathname == "/selectList"){
var selSql = "select userID,userName,password,age from Table_1 with(nolock) ";
handlers.selectList(selSql,function(data){
console.log("select data:"+data);
result = data;
});
}else if(pathname == "/selectOne"){
var selSql = "select count(1) as cnt from Table_1 with(nolock) where userName ='"+userName+"' and password='"+password+"'";
handlers.selectOne(selSql,function(data){
console.log("select data:"+data);
result = data;
});
}else if(pathname == "/insert"){
var insSql = "insert into Table_1 (userName,password,age) values('"+userName+"','"+password+"',"+ age +")";
handlers.insert(insSql,function(data){
console.log("insert data:"+data);
result = data;
});
}else if(pathname == "/update"){
var updSql = "update Table_1 set userName = '"+userName+"',password = '"+password+"',age = "+age+" where userID = "+userID;
handlers.update(insSql,function(data){
console.log("update data:"+data);
result = data;
});
}
//res.writeHead(200,{'Content-Type':'text/html;charset=utf-8'}); //'Access-Control-Allow-Origin':'http://localhost/'}
console.log(result);
var str = arg.callback + '(' + JSON.stringify(result) + ')'
console.log(str);
res.end(str);
}).listen(8080,"localhost");
console.log('Server running at http://localhost:8080/')
ajax调用
$.ajax({
url: 'http://localhost:8080/selectList', //请求的是3000端口，应该属于跨域调用，因此dataType用jsonp
dataType: "jsonp",
//jsonpCallback: "_test", //可以自定义‘处理’函数，默认是callback
cache: false,
jsonp:'callback', //默认的传递处理函数是callback
timeout: 5000,
data : {},
success: function(data) {
var userList = data.data;
var tabStr="";
for(var i=0;i<userList.length;i++){
tabStr += "<tr>";
tabStr += "<td>"+userList[i].userID+"</td>";
tabStr += "<td>"+userList[i].userName+"</td>";
tabStr += "<td>"+userList[i].password+"</td>";
tabStr += "<td>"+userList[i].age+"</td>";
tabStr += "</tr>"
}
$("#dataTab").html(tabStr);
},
error: function(jqXHR, textStatus, errorThrown) {
alert('error ' + textStatus + " " + errorThrown);
}
});