node.js结合edge连接sqlserver数据库读取数据并显示

比眉伴天荒 2022-08-07 04:43 207阅读 0赞

原文：[http://tech.pro/tutorial/1852/how-to-leverage-sql-server-with-nodejs-using-edgejs][http_tech.pro_tutorial_1852_how-to-leverage-sql-server-with-nodejs-using-edgejs]（How to leverage SQL Server with Node.js using Edge.js）

注意连接字符串的处理，文章中这样说道：

### Configure your connection string ###

Before you can use Edge.js with SQL Server, you must set an environment variable named `EDGE_SQL_CONNECTION_STRING` to a valid ADO.NET connection string. For example:

> set EDGE_SQL_CONNECTION_STRING=Data Source=localhost;Initial Catalog=node-test;Integrated Security=True

EDGE\_SQL\_CONNECTION\_STRING是全局环境变量，用于指定连接的数据库字符串

## Further reading ##

*  [Edge.js Readme][]
 *  [Edge.js is 32x faster than a cross-process call][]
 *  [Introducing Node.js Tools for Visual Studio][]
 *  [Access SQL Azure from a Node.js app...][]

按照上面文章描述，我们建立一个数据库node-test，然后执行sql语句：

IF EXISTS(SELECT 1 FROM sys.tables WHERE object_id = OBJECT_ID('SampleUsers'))
    BEGIN;
        DROP TABLE SampleUsers;
    END;
    GO
    
    CREATE TABLE SampleUsers (
        Id INTEGER NOT NULL IDENTITY(1, 1),
        FirstName VARCHAR(255) NOT NULL,
        LastName VARCHAR(255) NOT NULL,
        Email VARCHAR(255) NOT NULL,
        CreateDate  VARCHAR(20)--DATETIME NOT NULL DEFAULT(getdate()),
        PRIMARY KEY (Id)
    );
    GO
    
    INSERT INTO SampleUsers(FirstName,LastName,Email,CreateDate) VALUES('Orla','Sweeney','nunc@convallisincursus.ca','Apr 13, 2013');
    INSERT INTO SampleUsers(FirstName,LastName,Email,CreateDate) VALUES('Zia','Pickett','porttitor.tellus.non@Duis.com','Aug 31, 2013');
    INSERT INTO SampleUsers(FirstName,LastName,Email,CreateDate) VALUES('Justina','Ayala','neque.tellus.imperdiet@temporestac.com','Jul 28, 2013');
    INSERT INTO SampleUsers(FirstName,LastName,Email,CreateDate) VALUES('Levi','Parrish','adipiscing.elit@velarcueu.com','Jun 21, 2013');
    INSERT INTO SampleUsers(FirstName,LastName,Email,CreateDate) VALUES('Pearl','Warren','In@dignissimpharetra.org','Mar 3, 2013');
    INSERT INTO SampleUsers(FirstName,LastName,Email,CreateDate) VALUES('Rinah','Compton','egestas@feliseget.ca','Oct 24, 2013');
    INSERT INTO SampleUsers(FirstName,LastName,Email,CreateDate) VALUES('Hasad','Shepherd','fermentum.metus@velit.com','Sep 15, 2013');
    INSERT INTO SampleUsers(FirstName,LastName,Email,CreateDate) VALUES('Noelani','Hill','lacus@non.org','Jun 6, 2013');
    INSERT INTO SampleUsers(FirstName,LastName,Email,CreateDate) VALUES('Nicole','Jacobson','risus@mattis.com','Aug 8, 2013');
    INSERT INTO SampleUsers(FirstName,LastName,Email,CreateDate) VALUES('Alika','Acosta','Duis@felis.ca','Nov 23, 2013');
    
    select * from SampleUsers

![SouthEast][]  
编写node.js脚本：

保存为server-sql-query.js（注意，它使用了edge）  
打开dos，指定数据库连接字符串，文章中说明有两种写法的（set和SETX【它要大写的】），随便用一个，我用的是set语句：

set EDGE_SQL_CONNECTION_STRING=Data Source=localhost;Initial Catalog=node-test;uid=sa;pwd=Jon123;

然后node命令运行刚刚新建的server-sql-query.js  
![SouthEast 1][]  
成功运行便会打印出“node服务器正在监听8080端口”的信息  
然后我们在浏览器中输入 http://localhost:8080地址便会显示如下结果：

![SouthEast 2][]

到此结束。

ps：文章中靠近结尾处，作者的指出的一些看法挺好的，一并摘录此处留着研究

## Final thoughts ##

Edge.js appears to be a very promising solution to bridge the gap between Node.js and the world of .NET.

*  Although .NET code can be executed in-line, I highly recommend managing all .NET code in a separate assembly.
 *  An ORM can make your life much easier. I prefer Micro-ORMs that aren't heavy-handed and let me do my own thing. Unfortunately, not many ORMs have adopted async support. [AsyncPoco][] and [Insight.Database][] look promising, but I have not tried them.
 *  If you use Visual Studio, download and install the [Node.js Tools for Visual Studio][].
 *  Remember, stay async in .NET as much as possible!
 *  Test, test, test! Profile your application's memory, CPU, and concurrency under load to ensure something isn't going terribly wrong between Node.js and .NET.
 *  If your motivation for using Node.js is concurrency and scalability, or reducing your Microsoft licensing footprint, you may want to consider benchmarking Edge.js against a message queue architecture. Take a look at using [RabbitMQ][] or [ZeroMQ][]between your Node.js and Windows environments. A message-based architecture has **many** benefits. Use the solution that works best for you.
 *  Your mileage may vary.
 *  Just because you can, doesn't mean you should.
 *  Consume copious amounts of caffeine and bacon.

其中说道基于edge.js的消息队列2个项目，挺好的。

[http_tech.pro_tutorial_1852_how-to-leverage-sql-server-with-nodejs-using-edgejs]: http://tech.pro/tutorial/1852/how-to-leverage-sql-server-with-nodejs-using-edgejs
[Edge.js Readme]: https://github.com/tjanczuk/edge#edgejs-run-net-and-nodejs-code-in-process
[Edge.js is 32x faster than a cross-process call]: https://github.com/tjanczuk/edge/wiki/Performance
[Introducing Node.js Tools for Visual Studio]: http://www.hanselman.com/blog/IntroducingNodejsToolsForVisualStudio.aspx
[Access SQL Azure from a Node.js app...]: http://tomasz.janczuk.org/2013/06/access-sql-azure-from-nodejs-app.html
[SouthEast]: /images/20220805/a21329f81d5e4885ae1a19a47d7a0953.png
[SouthEast 1]: /images/20220805/86a3202172404e6290eae71285da584f.png
[SouthEast 2]: /images/20220805/16365b76497a442e9ffff098307b87a0.png
[AsyncPoco]: https://github.com/tmenier/AsyncPoco
[Insight.Database]: https://github.com/jonwagner/Insight.Database
[Node.js Tools for Visual Studio]: https://nodejstools.codeplex.com/
[RabbitMQ]: http://www.rabbitmq.com/
[ZeroMQ]: http://zeromq.org/