ASP 除了 VBScript,還可以指定 JScript 作為伺服器端語言。用 JavaScript 寫 ASP 的好處很明顯:函式是一等物件、物件字面值簡潔、prototype 繼承靈活,組織程式碼比 VBScript 舒服得多。以下示範如何封裝一個完整的 ADO 資料庫操作層。
<%
function getConfig(config, args) {
if (args) {
for (var proto in args) { config[proto] = args[proto]; }
}
return config;
}
function getConnection() { return new ActiveXObject("ADODB.Connection"); }
function getRecordset() { return new ActiveXObject("ADODB.Recordset"); }
var DB = {};
DB.ConnectionString = 'Provider=Sqloledb;User ID=sa;Password=sa;'
+ 'Initial Catalog=T;Data Source=WWW-D17F81FA113\\SQLEXPRESS;';
getConfig 是整個封裝的核心:傳入預設值物件與覆寫值物件,回傳合併後的結果。這就是後來 jQuery $.extend 的雛形。
// 新增一條記錄,回傳新產生的 IDENT_CURRENT
DB.Add = function (table, keyValueCol) {
var Conn = getConnection();
Conn.Open(DB.ConnectionString);
var Rs = getRecordset();
Rs.Open('select * from ' + table + ' where 1=2', Conn, 3, 2); // 3=adOpenStatic, 2=adLockOptimistic
Rs.AddNew();
for (var key in keyValueCol) { Rs.Fields.Item(key).Value = keyValueCol[key]; }
Rs.Update();
Rs.Close(); Rs = null;
Conn.Close(); Conn = null;
return DB.Get("select IDENT_CURRENT('" + table + "') as ID")["ID"];
};
// 修改一條記錄
DB.Upd = function (sql, keyValueCol) {
var Conn = getConnection();
Conn.Open(DB.ConnectionString);
var Rs = getRecordset();
Rs.Open(sql, Conn, 3, 2);
for (var key in keyValueCol) { Rs.Fields.Item(key).Value = keyValueCol[key]; }
Rs.Update();
Rs.Close(); Rs = null;
Conn.Close(); Conn = null;
};
where 1=2 是個小技巧:用一個必定為假的条件開啟可寫的結果集,取得欄位結構但不撈任何資料,之後就能用 AddNew 逐欄填值。
// 執行無回傳結果的查詢
DB.Exe = function (sql) {
var Conn = getConnection();
Conn.Open(DB.ConnectionString);
Conn.Execute(sql);
Conn.Close(); Conn = null;
};
// 取得單筆記錄,轉成 JS 物件
DB.Get = function (sql) {
var _record = null;
var Conn = getConnection();
Conn.Open(DB.ConnectionString);
var Rs = getRecordset();
Rs.Open(sql, Conn, 1, 1);
if (!Rs.EOF) {
_record = {};
for (var i = 0; i < Rs.Fields.Count; i++) {
_record[Rs.Fields.Item(i).Name] = Rs.Fields.Item(i).Value;
}
}
Rs.Close(); Rs = null;
Conn.Close(); Conn = null;
return _record;
};
DB.Batch 示範了如何用閉包實作「有狀態的物件」:回傳的函式帶著自己的 Conn 與 Rs,讓呼叫端可以連續做多次操作而只開一次連線。
DB.Batch = function () {
var Conn = getConnection();
var Rs = getRecordset();
var _Batch = this;
_Batch.Open = function (sql) {
Conn.Open(DB.ConnectionString);
Rs.Open(sql, Conn, 3, 2);
};
_Batch.Add = function (table, keyValueCol) {
Rs.AddNew();
for (var key in keyValueCol) { Rs.Fields.Item(key).Value = keyValueCol[key]; }
Rs.Update();
return DB.Get("Select IDENT_CURRENT('" + table + "') as ID")["ID"];
};
_Batch.Get = function () {
var record_arr = [];
while (!Rs.EOF) {
var _record = {};
for (var i = 0; i < Rs.Fields.Count; i++) {
_record[Rs.Fields.Item(i).Name] = Rs.Fields.Item(i).Value;
}
record_arr.push(_record);
Rs.MoveNext();
}
return record_arr;
};
_Batch.Close = function () {
Rs.Close(); Rs = null;
Conn.Close(); Conn = null;
};
};
DB.List = function () {
var _Config;
var _List = this;
_List.Page = { PS: 20, AP: 1, PC: 1, RC: 1 }; // 每頁筆數、目前頁、頁數、總筆數
_List.Query = function () {
_Config = getConfig({
fields: " * ", table: null, where: " 1=1 ", sort: " ID desc ", pk: " ID "
}, arguments[0]);
_List.Page.RC = DB.Get("select count(" + _Config.pk + ") as [count] from "
+ _Config.table + " where " + _Config.where).count;
_List.Page.PC = Math.ceil(_List.Page.RC / _List.Page.PS);
if (_List.Page.AP > _List.Page.PC) _List.Page.AP = _List.Page.PC;
};
_List.Get = function (p) {
p = isNaN(p) ? 1 : parseInt(p);
_List.Page.AP = p;
var sql = '';
if (p > 1) {
sql = "select top " + _List.Page.PS + " " + _Config.fields
+ " from " + _Config.table
+ " where " + _Config.where + " and " + _Config.pk
+ " not in(select top " + (p - 1) * _List.Page.PS + " " + _Config.pk
+ " from " + _Config.table + " where " + _Config.where
+ " order by " + _Config.sort + ") order by " + _Config.sort;
} else {
sql = "select top " + _List.Page.PS + " " + _Config.fields
+ " from " + _Config.table + " where " + _Config.where
+ " order by " + _Config.sort;
}
var Batch = new DB.Batch();
Batch.Open(sql);
var return_arr = Batch.Get();
Batch.Close();
return return_arr;
};
};
這裡用的是 SQL Server 的經典「NOT IN + TOP」分頁法:先取出前 (p-1)*PS 筆的主鍵,再用 NOT IN 排除,剩下的取前 PS 筆。
DB.Reader = function () {
var Conn = getConnection();
var Rs = getRecordset();
var _Reader = this;
_Reader.EOF = false;
_Reader.Open = function (sql) {
Conn.Open(DB.ConnectionString);
Rs.Open(sql, Conn, 1, 1); // 1=adOpenForwardOnly, 1=adLockReadOnly
_Reader.EOF = Rs.EOF;
};
_Reader.Read = function () {
if (!Rs.EOF) {
var _record = {};
for (var i = 0; i < Rs.Fields.Count; i++) {
_record[Rs.Fields.Item(i).Name] = Rs.Fields.Item(i).Value;
}
Rs.MoveNext();
return _record;
} else {
_Reader.EOF = true;
}
};
_Reader.Close = function () {
Rs.Close(); Rs = null;
Conn.Close(); Conn = null;
};
};
%>
它有三個不能照抄的地方:連線字串硬寫在程式碼裡且使用 sa 帳號與空白密碼;SQL 全部用字串拼接,等於完全沒有防範 SQL Injection;沒有錯誤處理,任何一步失敗都會留下未釋放的連線。現代寫法應該把連線資訊移到外部設定、全面改用參數化查詢(Command.Parameters.Append),並用 try-catch-finally 確保清理。
不過作為一份「如何用 JavaScript 的物件語法組織伺服器端程式碼」的教材,它的分層思路仍然值得參考。