David's blog

原先在php下写了个操作数据库的类，用jscript又重写了一遍。

// Sample
var db = new dbstuff();
db.connect(dbserver, dbuser, dbpwd, dbname);

var sql = "SELECT @@VERSION as dbv";
var query = db.query(sql);
while(arr = db.fetch_array(query)){
  echo(arr["dbv"]);
}
db.close();

function dbstuff(){
  ///////////////////
  // Attributes
  ///////////////////
  // Private
  var conn = Server.CreateObject("ADODB.Connection");
  // Public
  this.state = false;
  this.recordcount = -1;
  this.querynum = 0;
  ///////////////////
  // Methods
  ///////////////////
  // Open a new connection to the MSSQL server
  this.connect = function(dbserver, dbuser, dbpwd, dbname){
    try{
      if(this.state){
        conn.Close();
        this.state = false;
      }
      conn.CommandTimeout = 180;
      conn.ConnectionString = "Provider=SQLOLEDB; Data Source="+dbserver+"; UID="+dbuser+"; PWD="+dbpwd+";Initial Catalog="+dbname;
      conn.Open();
    }catch(e){
      die("Can not connect to Database Server.");
    }
    this.state = true;
  }

  // Performs a query on the database
  this.query = function(sql){
    try{
      var query = conn.Execute(sql);
    }catch(e){
      die("Database Query Error.", e, sql);
    }
    this.querynum++;
    return query;
  }

  // Fetch a result row as an associative array
  this.fetch_array = function(rs, freeresult){
    if(rs == undefined){return false;}
    try{
      if(rs.State != 1){return false;}
    }catch(e){
      die("Fetch Data Error.", e);
    }
    if(!rs.EOF){
      var arr = new Array();
      var colums = rs.Fields.Count;
      var i, fld;
      for(i=0; i < colums; i++){
        arr[rs.Fields(i).Name.toLowerCase()] = rs.Fields(i).Value;
      }
      rs.MoveNext();
      return arr;
    }else{
      if(freeresult == undefined){
        this.free_result(rs);
      }
      return false;
    }
  }

  // Fetch a result row as enumerated array
  this.fetch_row = function(rs, freeresult){
    if(rs == undefined){return false;}
    try{
      if(rs.State != 1){return false;}
    }catch(e){
      die("Fetch Data Error.", e);
    }
    if(!rs.EOF){
      var arr = new Array();
      var colums = rs.Fields.Count;
      for(var i=0; i < colums; i++){
        arr[i] = rs.Fields(i).Value;
      }
      rs.MoveNext();
      return arr;
    }else{
      if(freeresult == undefined){
        this.free_result(rs);
      }
      return false;
    }
  }

  // Fetch all result rows as associative array 
  this.fetch_all = function(rs, freeresult) {
    if(rs == undefined){return false;}
    try{
      if(rs.State != 1){return false;}
    }catch(e){
      die("Fetch Data Error.", e);
    }
    var arr = new Array();
    var colums = rs.Fields.Count;
    var rows = 0;
    var i;
    while(!rs.EOF){
      arr[rows] = new Array();
      for(i=0; i < colums; i++){
        arr[rows][rs.Fields(i).Name.toLowerCase()] = rs.Fields(i).Value;
      }
      rows++;
      rs.MoveNext();
    }
    if(freeresult == undefined){
      this.free_result(rs);
    }
    return arr;
  }

  // Open recordset
  this.recordset = function(sql, cursortype, locktype){
    if(cursortype == undefined){cursortype = 3;}
    if(locktype == undefined){locktype = 3;}
    try{
      var rs = Server.CreateObject("ADODB.RecordSet");
      rs.CursorLocation = 3;
      rs.Open(sql, conn, cursortype, locktype);
    }catch(e){
      die("Database Query Error.", e, sql);
    }
    this.querynum++;
    return rs;
  }

  // Fetch all result rows in one page as associative array 
  this.fetch_page = function(sql, pagesize, curpage, cursortype, locktype){
    if(pagesize == undefined){pagesize = 20;}
    if(curpage == undefined){curpage = 1;}
    if(cursortype == undefined){cursortype = 3;}
    if(locktype == undefined){locktype = 3;}
    try{
      var rs = Server.CreateObject("ADODB.RecordSet");
      rs.CursorLocation = 3;
      rs.CacheSize = pagesize;
      rs.Open(sql, conn, cursortype, locktype);
    }catch(e){
      die("Database Query Error.", e, sql);
    }
    this.querynum++;

    if(rs.State != 1){
      return false;
    } else if(rs.EOF && rs.BOF){
      this.recordcount = 0;
      return false;
    } else {
      this.recordcount = rs.recordcount;
      rs.PageSize = pagesize;
      if(!curpage){
        curpage = 1;
      }else if(curpage > rs.PageCount){
        curpage = rs.PageCount;
      }
      rs.AbsolutePage = curpage;
      var arr = new Array();
      var colums = rs.Fields.Count;
      var i,j;
      for(i=0; i< pagesize; i++){
        arr[i] = new Array();
        for(j=0; j < colums; j++){
          arr[i][rs.Fields(j).Name.toLowerCase()] = rs.Fields(j).Value;
        }
        rs.MoveNext();
        if(rs.EOF){break;}
      }
      this.free_result(rs);
      return arr;
    }
  }

  // Fetch one field value of a result row
  this.result = function(rs, row, mixedfld, freeresult){
    if(rs == undefined) {return false;}
    try{
      if(rs.State != 1){return false;}
    }catch(e){
      die("Fetch Data Error.", e);
    }
    if(row == undefined) {row = 0;}
    if(mixedfld == undefined) {mixedfld = 0;}
    if(freeresult == undefined) {
      freeresult = true;
    }else{
      freeresult = false;
    }
    if(rs.EOF){
      this.free_result(rs);
      return false;
    }
    if(row){rs.Move(row);}
    var returnvalue = rs.Fields(mixedfld).value;
    if(freeresult){this.free_result(rs);}
    return returnvalue;
  }

  // Get the number of rows in a result
  this.num_rows = function(rs){
    if(rs == undefined) {return false;}
    try{
      if(rs.State != 1){return false;}
    }catch(e){
      die("Fetch Data Error.", e);
    }
    return rs.RecordCount();
  }

  // Get the number of fields in a result 
  this.num_fields = function(rs){
    if(rs == undefined) {return false;}
    try{
      if(rs.State != 1){return false;}
    }catch(e){
      die("Fetch Data Error.", e);
    }
    return rs.Fields.Count;
  }
  
  // Get the auto generated id used in the last query
  this.insert_id = function(){
    query = this.query("SELECT @@IDENTITY");
    return this.result(query);
  }

  // Get the number of affected rows in a previous SQL operation
  // It should be called immediately after a SQL operation
  this.affected_rows = function(){
    query = this.query("SELECT @@ROWCOUNT");
    return this.result(query);
  }
  // Get the mssql version
  this.version = function(){
    query = this.query("SELECT @@VERSION");
    return this.result(query);
  }
  // Release resource
  this.free_result = function(rs){
    if(rs == undefined){return;}
    try{
      if(rs.State == 1){
        rs.Close();
      }
      rs = null;
    }catch(e){
      die("Close RecordSet Error.", e);
    }
  }
  // Close db connection
  this.close = function(){
    if(this.state){
      try{
        conn.Close();
        conn = null;
      }catch(e){
        die("Close Database Connection Error.", e);
      }
      this.state = false;
    }
  }
}

///////////////////

function echo(str){
  Response.Write(str+"\n");
}

function die(errmsg, err){
  Response.Clear();
  Response.CharSet = "utf-8";
  // error message
  if(errmsg == undefined){errmsg="";}
  if(errmsg) echo("
"+errmsg+"
");
  // error detail
  if(err != undefined){
    if(err.constructor == Error){
      echo("
Error Number: " + String(err.number & 0xFFFF) + "
\n
Error Detail: "+err.description+"
");
    }
  }

  Response.End();
}