database.asp

本系统是一套完整游戏金币交易平台,可以建立一个网站实现游戏金币的在线交易

<!--#include file="../fcDom/DOM.asp"-->

<%
/*=========================================================================================
 * Database类
 =========================================================================================*/
//类构造函数(类定义)
function Database() {
	//成员变量
	this.dbconn;	//数据库连接

	//成员函数
	this.close = close;  //关闭数据库
	this.BeginTrans = BeginTrans;		//事务开始
	this.CommitTrans = CommitTrans;		//事务提交
	this.RollbackTrans = RollbackTrans;	//事务回滚
	
	this.getPrimaryKey = getPrimaryKey;	//获取指定表的主键值
	this.saveNoUsedPrimaryKey = saveNoUsedPrimaryKey;	//将生成了但未被使用的主键保存起来
	this.clearNoUsedPrimaryKey = clearNoUsedPrimaryKey;  //清除生成了但未被使用的主键

	this.insertEntity = insertEntity;	//往数据库中插入一条记录
	this.deleteEntity =	deleteEntity;	//从数据库中删除数据，没有删除条件时不处理
	this.updateEntity =	updateEntity;	//根据主健值更新一条记录
	this.saveEntity = saveEntity;		//保存Entity，数据库中已有相应记录时相当于执行updateEntity操作，没有相应记录时执行insertEntity
	this.insertList = insertList;		//逐一插入EntityList下的每一个Entity。
	this.saveList =	saveList;			//逐一保存EntityList下的每一个Entity。保存数据时对每个Entity的操作方式与saveEntity的相同
	this.deleteList = deleteList;		//逐一删除EntityList下的每一个C_DELETE_FLAG#为1的行

	this.expandEntityAll = expandEntityAll ;  //查询一条记录，结果包含所有字段
	this.queryEntityAll = queryEntityAll;  //查询多条记录，结果包含所有字段
	this.queryEntityAllWithPage = queryEntityAllWithPage;  //分页查询，结果包含所有字段
	this.queryEntityAllReturnHiddenData = queryEntityAllReturnHiddenData;  //查询多条记录,并返回为表单的隐藏域
	this.queryEntityAllReturnHiddenDataWithPage = queryEntityAllReturnHiddenDataWithPage;  //分页查询多条记录,并返回为表单的隐藏域


	this.runInsertSql = runInsertSql;
	this.runUpdateSql = runUpdateSql;
	this.runDeleteSql = runDeleteSql;
	this.runQuerySql = runQuerySql;
	this.runQuerySqlWithPage = runQuerySqlWithPage;
	this.runQuerySqlReturnHiddenData = runQuerySqlReturnHiddenData;  //查询多条记录,并返回为表单的隐藏域
	this.runQuerySqlReturnHiddenDataWithPage = runQuerySqlReturnHiddenDataWithPage;  //分页查询多条记录,并返回为表单的隐藏域

	//初始化操作,创建数据库连接
	this.dbconn = Server.CreateObject("ADODB.Connection"); 
	var strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath ("../core/database/game.mdb") + ";"; 				
	//var strConn = "DBQ=" + Server.MapPath("../core/database/game.mdb") + ";Driver={Microsoft Access Driver (*.mdb)};";
	//var strConn = "Driver={SQL Server};Server=127.0.0.1;Database=buyer;UID=sa;PWD=";
	
	//adoConnection.open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath ("/") & "xxx.mdb" 

	this.dbconn.Open(strConn); 
}


//关闭数据库
function close() {
	this.dbconn.close();
	this.dbconn = null;
}

//开始事务
function BeginTrans() {
	this.dbconn.BeginTrans();
}

//提交事务
function CommitTrans() {
	this.dbconn.CommitTrans();
}

//回滚事务
function RollbackTrans() {
	this.dbconn.RollbackTrans();
}



//获取指定表的主键值
function getPrimaryKey(tableName) {	
	try {
		var primaryKey = "";
		var recordSet = Server.CreateObject("ADODB.RecordSet");

		primaryKey = getNoUsedRefNo(this.dbconn, recordSet, tableName);  //先从TRX_REFNO_NOUSE表得到生成但未使用的主键序列
		if(primaryKey=="-1") {  //没找到
			primaryKey = getRefNo(this.dbconn, recordSet, tableName);  //从TRX_REFNO表得到主键序列
			saveNoUsedRefNo(this.dbconn, recordSet, tableName, primaryKey, "U");  //将生成的主键保存起来
		}
		else {
			clearNoUsedRefNo(this.dbconn, recordSet, tableName, primaryKey);
		}
		
		//在primaryKey前加上前缀和日期yyyyMMdd
		var refPref = getRefPref(this.dbconn, recordSet, tableName);
		var date = new Date();
		var year = date.getFullYear();
		var month = date.getMonth() + 1;
		var day = date.getDate();
		month = month<10?("0"+month):(""+month);
		day = day<10?("0"+day):(""+day);
		primaryKey = refPref + year + month + day + primaryKey;

		return primaryKey;
	}
	catch(e) {
		throw e;
	}
	finally {
	}
}

//将生成了但未被使用的主键保存起来
function saveNoUsedPrimaryKey(tableName,primaryKey) {
	try {
		var recordSet = Server.CreateObject("ADODB.RecordSet");
		var refNoLength = getRefNoLength(this.dbconn, recordSet, tableName);
		var refNo = "";
		if(refNoLength>0) refNo = primaryKey.substr(primaryKey.length-refNoLength, refNoLength);
		saveNoUsedRefNo(this.dbconn, recordSet, tableName, refNo, "N");
	}
	catch(e) {
		throw e;
	}
	finally {
	}
}

//清除生成了但未被使用的主键
function clearNoUsedPrimaryKey(tableName,primaryKey) {
	try {
		var recordSet = Server.CreateObject("ADODB.RecordSet");
		var refNoLength = getRefNoLength(this.dbconn, recordSet, tableName);
		var refNo = "";
		if(refNoLength>0) refNo = primaryKey.substr(primaryKey.length-refNoLength, refNoLength);
		clearNoUsedRefNo(this.dbconn, recordSet, tableName, refNo);
	}
	catch(e) {
		throw e;
	}
	finally {
	}
}



//往数据库中插入一条记录,成功返回1,异常抛出
function insertEntity(node) {
	if(node==null) throw "Error:Node is null!";
	
	try {
		var strSQL = "select * from " + node.name;
		//Response.Write(strSQL);

		var recordSet = Server.CreateObject("ADODB.RecordSet");
		recordSet.Open(strSQL, this.dbconn, 1, 3);
		
		/*
		recordSet.AddNew();
		var children = node.child;
		for(var i=0; i<children.length; i++) {
			recordSet(children[i].name) = children[i].value;
		}
		*/
		
		if(node.findNodeByXPath("C_DELETE_FLAG#")!=null) {
			var C_DELETE_FLAG = node.findNodeByXPath("C_DELETE_FLAG#").value;
			//Response.Write(C_DELETE_FLAG);
			if(C_DELETE_FLAG=="0") insertEntityToCache(recordSet, node);  //如果删除标志为0
		}
		else {
			insertEntityToCache(recordSet, node);  //
		}

		recordSet.Update();
		recordSet.close();

		return 1;
	}
	catch(e) {
		throw e;
	}
	finally {
	}
}

function insertEntityToCache(recordSet, node) {
	recordSet.AddNew();
	var children = node.child;
	for(var i=0; i<children.length; i++) {
		if(children[i].name.substr(children[i].name.length-1,1)!="#" && children[i].value!="")
			recordSet(children[i].name) = children[i].value;
	}
}

//从数据库中删除数据，没有删除条件时不处理
//例如:UserDetail/classid[@type='like']/String[@value='abc%'] 同于SQL: delete from UserDetail where classid like 'abc%'
function deleteEntity(node) {
	if(node.child.length<1) return -1;

	var strSQL = "";
	strSQL = "delete from " + node.name + " where ";
	for(var i=0; i<node.child.length; i++) {
		strSQL += node.child[i].name + " " + node.child[i].value;
		if(node.child[i].child[0].name=="String")
			strSQL += " '" + node.child[i].child[0].value + "' and ";
		else
			strSQL += " " + node.child[i].child[0].value + " and ";
	}
	strSQL = strSQL.substr(0, strSQL.length-4); 
	//Response.Write(strSQL);
	this.dbconn.Execute(strSQL);
}

//根据主健值更新一条记录,如果主键不存在或未找到对应记录则返回-1,主键字段名必须为C_MAIN_REF
function updateEntity(node) {
	try {
		var isFindPK = false;  //是否找到主键C_MAIN_REF
		var strSQL = "";
		
		var tmpNode = node.findNodeByXPath("C_MAIN_REF");
		if(tmpNode!=null && tmpNode.value!="") {
			if(tmpNode.value.length>10) {  //如果是主表
				strSQL = "select * from " + node.name + " where C_MAIN_REF='" + tmpNode.value + "' ";
			}
			else {  //如果是明细表
				strSQL = "select * from " + node.name + " where C_MAIN_REF=" + tmpNode.value + " ";
			}
			isFindPK = true;
		}

		if(!isFindPK) return -1;
		
		//Response.Write(strSQL);

		var recordSet = Server.CreateObject("ADODB.RecordSet");
		recordSet.Open(strSQL, this.dbconn, 1, 3);
		if(!recordSet.EOF) {
			var children = node.child;
			for(var i=0; i<children.length; i++) {
				//Response.Write(children[i].name+";");
				if(children[i].name.substr(children[i].name.length-1,1)!="#" && children[i].name.toUpperCase()!="C_MAIN_REF") {
					var fieldType = recordSet.Fields(children[i].name).type;
					if(fieldType==2 || fieldType==3 || fieldType==16 || fieldType==17 || fieldType==18 || fieldType==19 || fieldType==20 || fieldType==21) {  //整型
						if(children[i].value!="") recordSet(children[i].name) = parseInt(children[i].value);
					}
					else if(fieldType==4 || fieldType==5 || fieldType==14 || fieldType==131 ) {  //浮点型
						if(children[i].value!="") recordSet(children[i].name) = parseFloat(children[i].value);
					}
					else if(fieldType==8 || fieldType==129 || fieldType==202 || fieldType==201 || fieldType==203 || fieldType==200 || fieldType==130) {  //字符串型
						recordSet(children[i].name) = children[i].value;
					}
					else if(fieldType==7 || fieldType==133 || fieldType==134 || fieldType==135) {  //日期时间型
						if(children[i].value!="") recordSet(children[i].name) = children[i].value;
					}
					else {  //其它类型
						//Response.Write(children[i].name);
						recordSet(children[i].name) = children[i].value;
					}
					//Response.Write(recordSet.Fields(children[i].name).type+";");
				}
			}
			//Response.Write("<br>");
			recordSet.Update();
			recordSet.close();	
			return 1;
		}
		else {
			recordSet.close();	
			return -1;
		}
		
	}
	catch(e) {
		throw e;
	}
	finally {
	}
}

//保存Entity，数据库中已有相应记录时相当于执行updateEntity操作，没有相应记录时执行insertEntity
function saveEntity(node) {
	var returnVal = this.updateEntity(node);
	if(returnVal!=1) {
		returnVal = this.insertEntity(node);
	}
	
	//从TRX_REFNO_NOUSE表中清除使用的主键
	var tableName = node.name;
	var primaryKey = node.findNodeByXPath("C_MAIN_REF").value;
	//Response.Write(tableName+";"+primaryKey+";");
	this.clearNoUsedPrimaryKey(tableName, primaryKey);  

	return returnVal;

	/*
	if(returnVal==1) {	
		return 1;
	}
	else {
		return this.insertEntity(node);
	}
	*/
}

//逐一插入EntityList下的每一个Entity。
function insertList(node) {
	if(node==null) throw "Error:Node is null!";
	
	try {
		var strSQL = "select * from " + node.value;
		//Response.Write(strSQL);

		var recordSet = Server.CreateObject("ADODB.RecordSet");
		recordSet.Open(strSQL, this.dbconn, 1, 3);
		
		for(var i=0; i<node.child.length; i++) {	
			var deleteFlag = node.child[i].findNodeByXPath("C_DELETE_FLAG#").value;
			//Response.Write(deleteFlag+";");
			if(deleteFlag!="1") {
				insertEntityToCache(recordSet, node.child[i]);
			}
		}

		recordSet.UpdateBatch();
		recordSet.close();
	}
	catch(e) {
		throw e;
	}
	finally {
	}
}

//逐一保存EntityList下的每一个Entity。保存数据时对每个Entity的操作方式与saveEntity的相同
function saveList(node) {
	if(node==null) throw "Error:Node is null!";

	try {
		for(var i=0; i<node.child.length; i++) {	
			var returnVal = this.updateEntity(node.child[i]);
			if(returnVal!=1) {
				returnVal = this.insertEntity(node.child[i]);
			}
		}
		this.deleteList(node);  //逐一删除EntityList下的每一个C_DELETE_FLAG#为1的行
	}
	catch(e) {
		throw e;
	}
	finally {
	}
}

//逐一删除EntityList下的每一个C_DELETE_FLAG#为1的行
function deleteList(node) {
	if(node==null) throw "Error:Node is null!";
	
	try {
		var strSQL = "delete from " + node.value + " ";
		var strPKSet = "";
		//Response.Write(strSQL);

		for(var i=0; i<node.child.length; i++) {	
			var childNode = node.child[i];
			var tmpNode = childNode.findNodeByXPath("C_DELETE_FLAG#");
			if(tmpNode!=null && tmpNode.value!="") {
				var delFlag = tmpNode.value;
				var pk = "";
				if(delFlag=="1") {
					pk = childNode.findNodeByXPath("C_MAIN_REF").value;
					if(pk!="") strPKSet += pk + ",";
				}
			}
		}

		if(strPKSet!="") {
			strPKSet = strPKSet.substr(0,strPKSet.length-1);
			strSQL += "where C_MAIN_REF in(" + strPKSet + ")";
			//Response.Write(strSQL);
			this.runDeleteSql(strSQL);
		}
	}
	catch(e) {
		throw e;
	}
	finally {
	}
}

//查询一条记录，结果包含所有字段, 参数node: UserDetail/classid[String='0001']
//返回值: 1 从数据库查询到1条数据  0 没有查询到数据 -1 输入的参数个数不为1；查询到的记录数超过1条 
function expandEntityAll(node) {
	var strSQL = "select * from " + node.name + " where 1=1 ";
	
	var children = node.child;
	for(var i=0; i<children.length; i++) {
		if(children[i].name.substr(children[i].name.length-1,1)!="#" && children[i].value!="") {
			if(children[i].type=="String")
				strSQL += "and " + children[i].name + "='" + children[i].value + "' ";
			else
				strSQL += "and " + children[i].name + "='" + children[i].value + "' ";
		}
	}
	//Response.Write(strSQL);

	var rs = this.runQuerySql(strSQL);
	if(rs.RecordCount<1) {
		rs.close();
		return 0;
	}
	else if(rs.RecordCount>1) {
		rs.close();
		return -1;
	}
	else {
		var recordCount = rs.RecordCount;
		var fieldsCount = rs.Fields.Count;

		var hiddenData = "";

			for(var j=0; j<fieldsCount; j++) {
				var fieldName = rs.Fields(j).Name;
				var fieldValue = rs(fieldName)+"";
				var fieldType = rs.Fields(j).type;
				
				
				if(fieldType==7 || fieldType==133 || fieldType==134 || fieldType==135) {  //日期时间型
					if(fieldValue!="null") {
						var tmpDate = new Date(rs(fieldName)+"");
						fieldValue = tmpDate.getYear() + "-" + (tmpDate.getMonth()+1) + "-" + tmpDate.getDate();
					}