本控件采用了jquery技术。
一、upFile.html内容如下:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head>
<meta http-equiv="Content-Type" content="text/html; charset=GBK" />
<title>FileUpload</title>
<link rel="stylesheet" href="js/jquery/themes/flora/flora.all.css" type="text/css" media="screen" title="Flora (Default)" />
<link rel="stylesheet" href="css/FileUps.css" type="text/css" />
<script type="text/javascript" src="js/jquery/jquery.js" language="javascript"></script>
<script type="text/javascript" src="js/jquery/ui/ui.core.js"></script>
<script type="text/javascript" src="js/jquery/ui/ui.dialog.js"></script>
<script type="text/javascript" src="js/jquery/ui/ui.resizable.js"></script>
<script type="text/javascript" src="js/jquery/ui/ui.draggable.js"></script>
<script type="text/javascript" src="js/jquery/ui/ui.tabs.js"></script>
<script type="text/javascript" src="js/ajaxfileupload.js" language="javascript"></script>
<script type="text/javascript" src="js/FinancePortal.js"></script>
<!--<script type="text/javascript" src="js/blackbody.js"></script>-->
<script type="text/javascript">
var userId="admin";
var key;
var name;
</script>
</head>
<body>
<script type="text/javascript">
var timeId;
var groupId;
function ajaxFileUpload(divid,basegroup,typen,onsuccess,onerror){
$("#div1").ajaxStart(function(){
$("#div1").css("display","block");
$(this).dialog({
modal: true,
overlay: {
opacity: 0.5,
background: "black"
}
});
}).ajaxComplete(function(){
$(this).dialog("destroy");
});
$.ajaxFileUpload({
url:'doupFiles.ashx?id='+userId+'&&ks='+divid+'&&group='+basegroup+'',
secureuri:false,
fileElementId:''+typen+'',
dataType:'json',
success:function(data,status)
{
if(data.error=='success')
onsuccess(data.msg);
else
onerror(data.msg);
},
error:function(data,status,e)
{
onerror(e);
}
});
}
function loadData(divname,dbname)
{
if(divname=="sayDetail")
{
var param="<ParamSet><CmdParam><dname>"+dbname+"</dname></CmdParam></ParamSet>";
DoQuery(userId,key,"LoadSalary:SalaryDetail",param,function(html){
$("#sayDetail").html(html);
$("#sayDetail table").attr("class","repeat-all");
$("#sayDetail tr").click(function(){
if($(this).attr("id")!="tbTitle"){
timeId=$(this).find("td[name='TimeID']").text();
groupId=$(this).find("td[name='groupID']").text();
$("#sayDetail tr[class='dark']").removeClass("dark");
$(this).addClass("dark");
}
else
{
$("#sayDetail tr[class='dark']").removeClass("dark");
}
});
},ShowError);
}
else
{
var paras="<ParamSet><CmdParam><dbname>"+dbname+"</dbname></CmdParam></ParamSet>";
DoQuery(userId,key,"ChargePanel:ChargeTerm",paras,function(html){
$("#"+divname).html(html);
$("#"+divname+" table").attr("class","tbCharge-all");
},ShowError);
}
}
$(document).ready(function(){
$("#tabsEx1 > ul").tabs({
selected: 1
});
Login(userId,"111",function(rkey,rname,chgPsw){
key=rkey;
name=rname;
$("#tabsEx1>div>button[name='upload']").click(function(){
var p=$(this).parent();
var basegroup=p.attr("basegroup");
var typen=p.find("input[name='fileToUpload']").attr("id");
var divid=p.attr("id");
ajaxFileUpload(divid,basegroup,typen,function(result){
if(divid!="Salary"){
loadData("chge","TuitionTest..");
loadData("usstchge","TuitionTest..");
loadData("nightchge","TuitionTest..");
loadData("masterchge","TuitionTest..");
}
else
{
loadData("sayDetail","SalaryTest..");
}
}, ShowError);
});
$("#tabsEx1>div>button[name='delete']").click(function(){
var dname="TuitionTest..";
var para="<ParamSet><CmdParam><dbname>"+dname+"</dbname></CmdParam></ParamSet>";
DoQuery(userId,key,"DeleteChange",para,function(html){
alert(html);
loadData("chge",dname);
loadData("usstchge",dname);
loadData("nightchge",dname);
loadData("masterchge",dname);
},ShowError)
});
$("#DeleteSaraly").click(function(){
if(timeId!=null && groupId!=null && timeId!="" && groupId!=""){
var dbname="SalaryTest..";
var param="<ParamSet><CmdParam><dname>"+dbname+"</dname><timeId>"+timeId+"</timeId><groupId>"+groupId+"</groupId></CmdParam></ParamSet>";
DoQuery(userId,key,"DeleteSalarys",param,function(html){
alert(html);
loadData("sayDetail",dbname);
},ShowError)
timeId=null;
groupId=null;
}
else
{
alert('请选择删除的行!');
}
});
loadData("chge","TuitionTest..");
loadData("usstchge","TuitionTest..");
loadData("nightchge","TuitionTest..");
loadData("masterchge","TuitionTest..");
loadData("sayDetail","SalaryTest..");
},ShowError);
})
</script>
<div id="maindiv" class="div-dsay">
<div id="div1" class="ui-dialog ui-draggable" style="display:none" title="上传文件..">
<center>
<img id="Img1" src="image/loadingAnimation.gif" alt="file uploading..." />
<br /><span>正在上传文件...</span>
</center>
</div>
<div id="tabsEx1" class="ui-tabs-nav div-tabsEx1">
<ul style="height:30px;">
<li class="ui-tabs-nav-item"><a href="#Charge"><span>收费</span></a></li>
<li class="ui-tabs-nav-item"><a href="#UsstCharge"><span>本专科</span></a></li>
<li class="ui-tabs-nav-item"><a href="#NightCharge"><span>夜大学</span></a></li>
<li class="ui-tabs-nav-item"><a href="#MasterCharge"><span>研究生</span></a></li>
<li class="ui-tabs-nav-item"><a href="#Salary"><span>工资</span></a></li>
</ul>
<div id="Charge" basegroup="3">
<p>文件上传:<input type="file" id="fileToUpload" size="40" name="fileToUpload" /></p>
<button id="buttonUpload" class="button" name="upload">上传</button>
<button id="btnDelete" class="button" name="delete">删除</button><br />
<div id="chge" class="div-detail-div"></div>
</div>
<div id="UsstCharge" basegroup="3">
<p>文件上传:<input type="file" id="Usstfile" size="40" name="fileToUpload" /></p>
<button id="btnUsst" class="button" name="upload">上传</button>
<button id="btnDelLog" class="button" name="delete">删除</button><br />
<div id="usstchge" class="div-detail-div"></div>
</div>
<div id="NightCharge">
<p>文件上传:<input type="file" id="Nightfile" size="40" name="fileToUpload" /></p>
<button id="btnNight" class="button" name="upload">上传</button>
<button id="btnDelNight" class="button" name="delete">删除</button><br />
<div id="nightchge" class="div-detail-div"></div>
</div>
<div id="MasterCharge">
<p>文件上传:<input type="file" id="Masterfile" size="40" name="fileToUpload" /></p>
<button id="btnMaster" class="button" name="upload">上传</button>
<button id="btnDelMaster" class="button" name="delete">删除</button><br />
<div id="masterchge" class="div-detail-div"></div>
</div>
<div id="Salary" basegroup="4">
<p>文件上传:<input type="file" id="fileToloads" size="40" name="fileToUpload" /> </p>
<button id="btnSqrary" class="button" name="upload">上传</button>
<button id="DeleteSaraly" class="button" name="DeleteSaraly">删除</button> <br />
<div id="sayDetail" class="div-detail-div"></div>
</div>
</div>
</div>
</body>
</html>
备注:js文件在jquery的官方网站去下载。
二、ajaxfileupload.js文件如下:
jQuery.extend({
createUploadIframe: function(id, uri)
{
//create frame
var frameId = 'jUploadFrame' + id;
if(window.ActiveXObject) {
var io = document.createElement('<iframe id="' + frameId + '" name="' + frameId + '" />');
if(typeof uri== 'boolean'){
io.src = 'javascript:false';
}
else if(typeof uri== 'string'){
io.src = uri;
}
}
else {
var io = document.createElement('iframe');
io.id = frameId;
io.name = frameId;
}
io.style.position = 'absolute';
io.style.top = '-1000px';
io.style.left = '-1000px';
document.body.appendChild(io);
return io
},
createUploadForm: function(id, fileElementId)
{
//create form
var formId = 'jUploadForm' + id;
var fileId = 'jUploadFile' + id;
var form = $('<form action="" method="POST" name="' + formId + '" id="' + formId + '" enctype="multipart/form-data"></form>');
var oldElement = $('#' + fileElementId);
var newElement = $(oldElement).clone();
$(oldElement).attr('id', fileId);
$(oldElement).before(newElement);
$(oldElement).appendTo(form);
//set attributes
$(form).css('position', 'absolute');
$(form).css('top', '-1200px');
$(form).css('left', '-1200px');
$(form).appendTo('body');
return form;
},
ajaxFileUpload: function(s) {
// TODO introduce global settings, allowing the client to modify them for all requests, not only timeout
s = jQuery.extend({}, jQuery.ajaxSettings, s);
var id = new Date().getTime()
var form = jQuery.createUploadForm(id, s.fileElementId);
var io = jQuery.createUploadIframe(id, s.secureuri);
var frameId = 'jUploadFrame' + id;
var formId = 'jUploadForm' + id;
// Watch for a new set of requests
if ( s.global && ! jQuery.active++ )
{
jQuery.event.trigger( "ajaxStart" );
}
var requestDone = false;
// Create the request object
var xml = {}
if ( s.global )
jQuery.event.trigger("ajaxSend", [xml, s]);
// Wait for a response to come back
var uploadCallback = function(isTimeout)
{
var io = document.getElementById(frameId);
try
{
if(io.contentWindow)
{
xml.responseText = io.contentWindow.document.body?io.contentWindow.document.body.innerHTML:null;
xml.responseXML = io.contentWindow.document.XMLDocument?io.contentWindow.document.XMLDocument:io.contentWindow.document;
}else if(io.contentDocument)
{
xml.responseText = io.contentDocument.document.body?io.contentDocument.document.body.innerHTML:null;
xml.responseXML = io.contentDocument.document.XMLDocument?io.contentDocument.document.XMLDocument:io.contentDocument.document;
}
}catch(e)
{
jQuery.handleError(s, xml, null, e);
}
if ( xml || isTimeout == "timeout")
{
requestDone = true;
var status;
try {
status = isTimeout != "timeout" ? "success" : "error";
// Make sure that the request was successful or notmodified
if ( status != "error" )
{
// process the data (runs the xml through httpData regardless of callback)
var data = jQuery.uploadHttpData( xml, s.dataType );
// If a local callback was specified, fire it and pass it the data
if ( s.success )
s.success( data, status );
// Fire the global callback
if( s.global )
jQuery.event.trigger( "ajaxSuccess", [xml, s] );
} else
jQuery.handleError(s, xml, status);
} catch(e)
{
status = "error";
jQuery.handleError(s, xml, status, e);
}
// The request was completed
if( s.global )
jQuery.event.trigger( "ajaxComplete", [xml, s] );
// Handle the global AJAX counter
if ( s.global && ! --jQuery.active )
jQuery.event.trigger( "ajaxStop" );
// Process result
if ( s.complete )
s.complete(xml, status);
jQuery(io).unbind()
setTimeout(function()
{ try
{
$(io).remove();
$(form).remove();
} catch(e)
{
jQuery.handleError(s, xml, null, e);
}
}, 100)
xml = null
}
}
// Timeout checker
if ( s.timeout > 0 )
{
setTimeout(function(){
// Check to see if the request is still happening
if( !requestDone ) uploadCallback( "timeout" );
}, s.timeout);
}
try
{
// var io = $('#' + frameId);
var form = $('#' + formId);
$(form).attr('action', s.url);
$(form).attr('method', 'POST');
$(form).attr('target', frameId);
if(form.encoding)
{
form.encoding = 'multipart/form-data';
}
else
{
form.enctype = 'multipart/form-data';
}
$(form).submit();
} catch(e)
{
jQuery.handleError(s, xml, null, e);
}
if(window.attachEvent){
document.getElementById(frameId).attachEvent('onload', uploadCallback);
}
else{
document.getElementById(frameId).addEventListener('load', uploadCallback, false);
}
return {abort: function () {}};
},
uploadHttpData: function( r, type ) {
var data = !type;
data = type == "xml" || data ? r.responseXML : r.responseText;
// If the type is "script", eval it in global context
if ( type == "script" )
jQuery.globalEval( data );
// Get the JavaScript object, if JSON is used.
if ( type == "json" )
eval( "data = " + data );
// evaluate scripts within html
if ( type == "html" )
jQuery("<div>").html(data).evalScripts();
//alert($('param', data).each(function(){alert($(this).attr('value'));}));
return data;
}
})
三、doupFiles.ashx
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.IO;
using System.Text;
using System.Data.SqlClient;
using System.Web.Services;
using System.Web.Services.Protocols;
using FinancePortal.Utilities;
using System.Threading;
namespace FinancePortal
{
/// <summary>
/// $codebehindclassname$ 的摘要说明
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
public class doupFiles : IHttpHandler
{
public void ProcessRequest(HttpContext context)
{
string msg = "上传完毕";
string error = "success";
if (UserStatus.IsUsered) //是否有用户正在上传同一个文件
{
error = "error";
msg = "不能同时上传文件,请您等待...";
}
else
{
//获取一个以当前系统时间为文件名
string filename = string.Format("{0}{1}{2}{3}{4}{5}", DateTime.Today.Year, DateTime.Today.Month, DateTime.Today.Day, System.DateTime.Now.Hour, System.DateTime.Now.Minute, System.DateTime.Now.Second);
try
{
string userId = context.Request.QueryString["id"];
string FileN = context.Request.QueryString["ks"]; //得到参数值
int basegroupid = int.Parse(context.Request.QueryString["group"]);
UserStatus.IsUsered = true;
if (context.Request.Files.Count > 0)
{
HttpPostedFile file = context.Request.Files[0];
string othername = Path.GetFileNameWithoutExtension(file.FileName); //得到原压缩文件名
if (file.ContentLength == 0)
{
error = "error";
msg = "上传文件不能为空!";
}
else
{
file.SaveAs(context.Server.MapPath("FileUp") + "//" + filename + ".zip"); //上传文件到服务器
//添加参数
SqlParameter[] paras ={ new SqlParameter("@BaseGroup",SqlDbType.Int),
new SqlParameter("@UserID",SqlDbType.NVarChar,50),
new SqlParameter("@note",SqlDbType.NVarChar,100)
};
paras[0].Value = basegroupid;
paras[1].Value = userId;
paras[2].Value = othername;
SqlParameter[] parar ={ paras[0], paras[1] }; //设置缴费参数
UpFileLoad fileup = new UpFileLoad();
if (FileN.Equals(EnuNumber.enumDataType.Charge.ToString()) || FileN.Equals(EnuNumber.enumDataType.UsstCharge.ToString()) || FileN.Equals(EnuNumber.enumDataType.MasterCharge.ToString())
|| FileN.Equals(EnuNumber.enumDataType.NightCharge.ToString()))
{
fileup.ProccessRarFile(filename, FileN, "proc_Tuition", parar); //调用上传方法
}
else if (FileN.Equals(EnuNumber.enumDataType.Salary.ToString()))
{
fileup.ProccessRarFile(filename, FileN, "proc_Salary", paras);
}
error = "success";
msg = "上传完毕";
}
}
}
catch (Exception ex) //捕获异常
{
error = "error";
msg = ErrorProcess.ErrorMessage(ex);
}
finally
{
UserStatus.IsUsered = false; //释放资源
try
{
DirectoryInfo info = CreateFolders.checkValidSessionPath("FileUp//temp//");
if (info.GetFiles().Length > 1)
{
foreach (FileInfo filein in info.GetFiles())
{
filein.Delete(); //删除文件
}
}
}
catch (IOException er)
{ }
try
{
CreateFolders.DeleteFile("FileUp//" + filename + ".zip"); //删除压缩包
}
catch (IOException eex)
{ }
}
}
//context.Response.ContentType = "text/xml";
string result = "{ error:'" + error + "',msg:'" + msg + "'}";
context.Response.Write(result);
}
public bool IsReusable
{
get
{
return false;
}
}
}
}
四、FinancePortal.js
function ShowMsg(title, msg){
$("<div id=/"MsgDialog/" class=/"ui-dialog ui-draggable/" title=/""+title+"/"></div>").html(msg).dialog({
modal: true,
overlay: {
opacity: 0.5,
background: "black"
},
buttons: {
"确定": function(){
$(this).dialog("destroy");
$("#ErrorDialog").remove();
}
}
});
}
function ShowError(msg){
ShowMsg("错误", msg);
}
function ResultProcess(result, onsuccess, onerror) {
var pos = result.indexOf(":");
var status = result.substring(0, pos);
var text = result.substring(pos+1);
if( status == "success")
onsuccess(text);
else
onerror(text);
}
function Login(userId, userPsw, onsuccess, onerror)
{
$.ajax({
url: "UserAuth.ashx",
cache: false,
data: {uid:userId, psw: userPsw, type: "login"},
success: function(html){
ResultProcess(html, function(result){
var res=result.split(",");
onsuccess(res[0], res[1], res[2]);
}, onerror);
},
error: function (XMLHttpRequest, textStatus, errorThrown) {
onerror(textStatus+":"+XMLHttpRequest.status+"; "+XMLHttpRequest.responseText);
}
});
}
function Logout(ukey)
{
$.ajax({
url: "UserAuth.ashx",
cache: false,
data: {key: ukey, type: "logout"}
});
}
function ChangePassword(ukey, utype, userId, oldp, newp, onsuccess, onerror) {
$.ajax({
url: "UserAuth.ashx",
cache: false,
data: {key:ukey, user: userId, oldPsw: oldp, newPsw: newp, type: utype},
success: function(html){
ResultProcess(html, function(result){
onsuccess(result);
}, onerror);
},
error: function (XMLHttpRequest, textStatus, errorThrown) {
onerror(textStatus+":"+XMLHttpRequest.status+"; "+XMLHttpRequest.responseText);
}
});
}
function ChangeMyPassword(ukey, oldp, newp, onsuccess, onerror) {
ChangePassword(ukey, "chgMyPsw", "", oldp, newp, onsuccess, onerror);
}
function ChangeUserPassword(ukey, userId, newp, onsuccess, onerror) {
ChangePassword(ukey, "chgUserPsw", userId, "", newp, onsuccess, onerror);
}
function LoadUserFunc(userid, key, onsuccess, onerror) {
$.ajax({
url: "DataQuery.ashx",
cache: false,
data: {uid:userid, key: key, queryName: "UserFunc:ShowInTable", params: ""},
success: function(html){
ResultProcess(html, onsuccess, onerror);
},
error: function (XMLHttpRequest, textStatus, errorThrown) {
onerror(textStatus+":"+XMLHttpRequest.status);
}
});
}
function LoadQueryFrame(usrKey, elem, onerror){
var setting=$(elem);
var xsqlFuncName=setting.attr("xsqlFuncName");
var xsqlFrame=setting.attr("xsqlFrame");
var xsqlFrameParam=setting.attr("xsqlFrameParam");
var xsqlsrcSelector=setting.attr("xsqlsrcSelector");
var xsqldstSelector=setting.attr("xsqldstSelector");
$.ajax({
url: "QueryFrames.ashx",
cache: false,
data: {key: usrKey, name: xsqlFuncName, frame: xsqlFrame, selector: xsqlsrcSelector},
dataType: "html",
success: function(res){
ResultProcess(res, function(html){
//var cont=html;
//cont="<script type=/"text/javascript/">/n var funcName=/""+xsqlFuncName+"/";/n</script>/n"+cont;
$(xsqldstSelector).html(html);
}, onerror);
},
error: function (XMLHttpRequest, textStatus, errorThrown) {
onerror(textStatus+":"+XMLHttpRequest.status);
}
});
}
function DoQuery(userid, key, qname, qparam, onsuccess, onerror)
{
$.ajax({
url: "DataQuery.ashx",
cache: false,
data: {uid:userid, key: key, queryName: qname, params: qparam},
success: function(html){
ResultProcess(html, onsuccess, onerror);
},
error: function (XMLHttpRequest, textStatus, errorThrown) {
onerror(textStatus+":"+XMLHttpRequest.status);
}
});
}
五、类UpFileLoad.cs
/*
* 说明:本类实现解压缩和上传文本文件到数据库表中
*/
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Web.UI.MobileControls;
using System.IO;
using System.IO.Compression;
using System.Data.SqlClient;
using System.Text;
using System.Runtime.InteropServices;
using Microsoft.Win32;
using System.Diagnostics;
using Microsoft.VisualBasic;
using System.Xml;
using System.Xml.XPath;
using System.Collections.Generic;
using System.Text.RegularExpressions;
/// <summary>
/// UpFileLoad 的摘要说明
/// </summary>
public class UpFileLoad
{
/// <summary>
/// 获取数据库连接字符串
/// </summary>
private string connstring = System.Configuration.ConfigurationManager.AppSettings["DBConnection"];
public UpFileLoad()
{
// TODO: 在此处添加构造函数逻辑
//
}
/// <summary>
/// 获取数据库名称
/// </summary>
/// <param name="filename"></param>
/// <returns></returns>
public string GetConnectiong(string filename)
{
string dbname = string.Empty;
foreach (UpFileSet upset in UpXmlFile.sel[filename])
{
dbname = upset.DbName;
break;
}
return dbname;
}
/// <summary>
/// 解压文件并上传文件
/// </summary>
/// <param name="Zipname"></param>
/// <param name="userId"></param>
/// <param name="upfilename"></param>
/// <param name="produrename"></param>
/// <param name="paras"></param>
public void ProccessRarFile(string Zipname, string upfilename,string procedurename,SqlParameter[] paras)
{
try
{
String the_Zip; //要解压的文件
String the_info;
ProcessStartInfo the_StartInfo;
Process the_Process;
string pathfile = System.Web.HttpContext.Current.Server.MapPath("FileUp//temp//");
string pathara = System.Web.HttpContext.Current.Server.MapPath("FileUp//"); //找到文件夹的父文件夹
string PathName = System.Web.HttpContext.Current.Server.MapPath("FileUp//7za");
the_Zip = PathName.ToString();
the_info = " e " + pathara + Zipname + ".zip " + "-o" + pathfile+" *.txt -r"; //解压的命令
the_StartInfo = new ProcessStartInfo();
the_StartInfo.FileName = the_Zip;
the_StartInfo.Arguments = the_info;
the_StartInfo.WindowStyle = ProcessWindowStyle.Hidden; //隐藏进程窗体
the_StartInfo.CreateNoWindow = true;
using (the_Process = new Process())
{
the_Process.StartInfo = the_StartInfo;
the_Process.Start();
the_Process.WaitForExit(); //等待进程结束
}
string path = string.Empty;
DirectoryInfo info = CreateFolders.checkValidSessionPath("FileUp//temp//"); //查找文件
if (info.GetFiles().Length > 1)
{
pathfile = System.Web.HttpContext.Current.Server.MapPath("FileUP//temp//");
}
else
{
throw new Exception("压缩包是空的,请上传有文件的压缩包!"); //抛异常
}
bool targe = this.CheckFile(upfilename); //检查文件是否与配置文件一样
if (!targe)
{
throw new Exception("压缩包缺少文件,请重新上传!");
}
this.UpLoad(pathfile,upfilename); //调用写数据库方法
this.ExecuteProduce(procedurename, paras);
}
catch
{
throw;
}
}
/// <summary>
/// 处理上传好的数据数据
/// </summary>
/// <param name="procname"></param>
/// <param name="paraments"></param>
private void ExecuteProduce(string procname, SqlParameter[] paraments)
{
using (SqlConnection connect = new SqlConnection(connstring))
{
connect.Open();
using (SqlTransaction tran = connect.BeginTransaction())
{
try
{
//处理密码,执行存储过程
if (procname != null)
Command.ExecuteNonQuery(tran, CommandType.StoredProcedure, procname, paraments);
else
throw new Exception("存储过程不能为空,请查看数据库是否创建");
tran.Commit();
}
catch
{
tran.Rollback();
throw;
}
}
}
}
/// <summary>
/// 把文件写入数据库表
/// </summary>
/// <param name="filePath"></param>
/// <param name="UserId"></param>
/// <param name="produrename"></param>
/// <param name="paras"></param>
private void UpLoad(string filePath,string upname)
{
using (SqlConnection conn = new SqlConnection(connstring))
{
conn.Open(); //打开连接
using (SqlTransaction trans = conn.BeginTransaction())
{
try
{
string DELETE_TABLE = string.Empty; //删除表的字符串
SqlDataAdapter da = new SqlDataAdapter();
string dbname = GetConnectiong(upname);
if (string.IsNullOrEmpty(dbname)) //检查数据库是否存在
throw new Exception("请检查xml文件配置是否与数据库匹配");
foreach (UpFileSet upSet in UpXmlFile.sel[upname])
{
foreach (UpFile upf in upSet.M_upFiles)
{
DELETE_TABLE += "truncate table " +dbname+".."+upf.TableName+" /r/n"; //获取SQL语句
}
}
Command.ExecuteNonQuery(trans, DELETE_TABLE, null); //删除数据表中原来的数据
foreach (UpFileSet upSet in UpXmlFile.sel[upname])
{
foreach (UpFile upf in upSet.M_upFiles)
{
ImportToDataBase(filePath, upf.TableName,upf.FileName,dbname,da, trans); //填充数据表
}
}
trans.Commit(); //提交事务
}
catch(KeyNotFoundException ex)
{
throw new Exception(ex.Message+",请查看配置文件或xml文件是否有该文件");
}
catch
{
trans.Rollback(); //事务回滚
throw;
}
}
}
}
/// <summary>
/// 检查几个文本文件是否存在
/// </summary>
/// <returns></returns>
private bool CheckFile(string filename)
{
bool flag = false;
DirectoryInfo info = CreateFolders.checkValidSessionPath("FileUp//temp//"); //找文件路径
FileInfo[] fileinfo = info.GetFiles();
List<UpFileSet> list = UpXmlFile.sel[filename]; //得到配置文件中的文件
foreach (UpFileSet upset in list)
{
if (fileinfo.Length == upset.M_upFiles.Count) //是否存在相同的文件
{
flag = true;
break; }
}
return flag;
}
/// <summary>
/// 把文本文件导入数据库
/// </summary>
/// <param name="filePath">文本文件路径</param>
/// <param name="table">文本文件名或数据库表名</param>
/// <param name="da"></param>
/// <param name="tran">事务处理</param>
public void ImportToDataBase(string filePath, string table,string filename,string dbname,SqlDataAdapter da,SqlTransaction tran)
{
da.SelectCommand = new SqlCommand("select * from " + dbname+".."+table + " where 0=1", tran.Connection, tran);
DataTable dt = new DataTable(); //新建DataTable
da.Fill(dt);
da.SelectCommand.UpdatedRowSource = UpdateRowSource.None;
da.SelectCommand = new SqlCommand("select * from " + dbname+".."+table, tran.Connection, tran);
da.SelectCommand.UpdatedRowSource = UpdateRowSource.None;
da.SelectCommand.CommandTimeout = 900;
SqlCommandBuilder cbuilder = new SqlCommandBuilder(da); //初始化新实例
string insertCmd = "";
string p = "";
for (int i = 0; i < dt.Columns.Count; i++)
{
insertCmd += "[";
insertCmd += dt.Columns[i].ColumnName; //处理表的列
insertCmd += "],";
p += "@p";
p += (i + 1).ToString(); //处理表中的数据
p += ",";
}
insertCmd = insertCmd.Substring(0, insertCmd.Length - 1); //得到列
p = p.Substring(0, p.Length - 1); //得到列中数据
string cmdText = "insert into " + dbname+".."+table + "(" + insertCmd + ") values (" + p + ")";
SqlCommand command = new SqlCommand(cmdText, tran.Connection, tran);
command.CommandTimeout = 900; //设置超时时间
foreach (SqlParameter para in cbuilder.GetInsertCommand().Parameters)
{
command.Parameters.Add(para.ParameterName, para.SqlDbType, para.Size, para.SourceColumn); //添加参数
}
da.InsertCommand = command;
da.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
da.InsertCommand.CommandTimeout = 900;
ImportToDataTable(filePath + filename, ref dt);
da.UpdateBatchSize = 400; //设置每次到服务器的往返过程中处理的行数
da.Update(dt);
dt.Dispose(); //释放资源
}
/// <summary>
/// 把文本文件的数据存入传进来的DataTable中
/// </summary>
/// <param name="filePath">文本文件的文件绝对路径</param>
/// <param name="dt">DataTable,传引用,可以修改</param>
private void ImportToDataTable(string filePath, ref DataTable dt)
{
int fileNumber = FileSystem.FreeFile(); //读取文件号
try
{
FileSystem.FileOpen(fileNumber, filePath, OpenMode.Input, OpenAccess.Read, OpenShare.LockReadWrite, -1);
DataRow dr;
int columns = dt.Columns.Count; //得到数据表的总列数
while (!FileSystem.EOF(fileNumber)) //读取文本文件
{
dr = dt.NewRow();
object obj = null;
for (int i = 0; i < columns; i++)
{
FileSystem.Input(fileNumber, ref obj); //打开顺序文件的顺序,并赋值给表的列
dr[i] = obj;
}
dt.Rows.Add(dr); //添加行
}
FileSystem.FileClose(fileNumber); //关闭文件
}
catch
{
FileSystem.FileClose(fileNumber);
throw;
}
}
}
/// <summary>
/// 加载xml文件
/// </summary>
public static class UpXmlFile
{
static UpXmlFile()
{
//得到操作xml文件对象
XPathDocument doc = new XPathDocument(System.Web.HttpContext.Current.Server.MapPath("UpLoadFiles.xml"));
XPathNavigator nva = doc.CreateNavigator(); //定位到当前节点
XPathNodeIterator upFiles = nva.Select("/Files/UpLoadFile"); //得到指定的节点
foreach (XPathNavigator upfile in upFiles)
{
XPathNodeIterator files = upfile.Select("file"); //得到子节点
string name = upfile.GetAttribute("name", "");
string dbname = upfile.GetAttribute("dbname", ""); //得到子节点属性
List<UpFileSet> listfile = new List<UpFileSet>();
List<UpFile> filelist = new List<UpFile>();
foreach (XPathNavigator file in files)
{
UpFile xml1 = new UpFile(file.GetAttribute("name", ""), file.GetAttribute("tabname", ""));
filelist.Add(xml1);
}
UpFileSet upSet = new UpFileSet(dbname, filelist);
listfile.Add(upSet);
sel.Add(name, listfile);
}
}
public static Dictionary<string, List<UpFileSet>> sel = new Dictionary<string, List<UpFileSet>>(); //提供外界使用的集合
}
/// <summary>
/// 文件名和表名类
/// </summary>
public class UpFile
{
public UpFile(string fn, string tn)
{
fileName = fn;
tableName = tn;
}
/// <summary>
/// 文本文件名,带扩展名的
/// </summary>
public string FileName
{
get { return fileName; }
}
/// <summary>
/// 数据库表名
/// </summary>
public string TableName
{
get { return tableName; }
}
private string fileName; //文件名
private string tableName; //表名
}
/// <summary>
/// 封装数据库名和上传类型名称
/// </summary>
public class UpFileSet
{
public UpFileSet(string name, List<UpFile> upfiles)
{
m_dbName = name;
m_upFiles = upfiles;
}
/// <summary>
/// 数据库名称
/// </summary>
public string DbName
{
get { return m_dbName; }
}
public List<UpFile> M_upFiles
{
get { return m_upFiles; }
}
private string m_dbName; //数据库名
private List<UpFile> m_upFiles; //文件表名列表
}
/// <summary>
/// 枚举类
/// </summary>
public class EnuNumber
{
/// <summary>
/// 类型-枚举
/// </summary>
public enum enumDataType
{
Charge, //缴费
UsstCharge, //本专科缴费
NightCharge, //夜大缴费
MasterCharge, //研究生缴费
Outlay, //金费
Salary //工资
}
}
#region 操作文件
/// <summary>
/// 操作文件夹及文件
/// </summary>
public class CreateFolders
{
/// <summary>
/// 删除一个文件夹下面的子文件夹和文件
/// </summary>
/// <param name="Folderpathname"></param>
public static void DeleteChildFolder(string Folderpathname)
{
if (Folderpathname.Trim().Length > 0)
{
try
{
string Createpath = System.Web.HttpContext.Current.Server.MapPath(Folderpathname);
if (Directory.Exists(Createpath))
{
Directory.Delete(Createpath, true); //删除指定的目录并(如果指示)删除该目录中的任何子目录
}
}
catch
{
throw;
}
}
}
/// <summary>
/// 删除文件,必须指定文件名和扩展名(文件全名)
/// </summary>
/// <param name="Filepathname"></param>
public static void DeleteFile(string Filepathname)
{
try
{
FileInfo DeleFile = new FileInfo(System.Web.HttpContext.Current.Server.MapPath(Filepathname).ToString());
DeleFile.Delete(); //删除文件
}
catch
{
throw;
}
}
/// <summary>
/// 删除整个文件夹及其子文件夹和文件
/// </summary>
/// <param name="FolderPathName"></param>
public static void DeleParentFolder(string FolderPathName)
{
try
{
DirectoryInfo DelFolder = new DirectoryInfo(System.Web.HttpContext.Current.Server.MapPath(FolderPathName).ToString());
if (DelFolder.Exists)
{
DelFolder.Delete(true);
//DelFolder.Delete();
}
}
catch
{
throw;
}
}
/// <summary>
/// 读取文件夹的文件
/// </summary>
/// <param name="FilePathName"></param>
/// <returns></returns>
public static DirectoryInfo checkValidSessionPath(string FilePathName)
{
try
{
DirectoryInfo mainDir = new DirectoryInfo(System.Web.HttpContext.Current.Server.MapPath(FilePathName));
return mainDir;
}
catch
{
throw;
}
}
}
#endregion
#region 操作数据表
public abstract class Command
{
/// <summary>
/// Execute a SqlCommand (that returns no resultset) using an existing SQL Transaction
/// using the provided parameters.
/// </summary>
/// <param name="trans"></param>
/// <param name="cmdType"></param>
/// <param name="cmdText"></param>
/// <param name="cmdParms"></param>
/// <returns></returns>
public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
{
SqlCommand cmd = new SqlCommand();
cmd.CommandTimeout = 900;
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
public static int ExecuteNonQuery(SqlTransaction trans, string cmdText, params SqlParameter[] cmdParms)
{
return ExecuteNonQuery(trans, CommandType.Text, cmdText, cmdParms);
}
/// <summary>
/// Prepare a command for execution
/// </summary>
/// <param name="commd"></param>
/// <param name="conn"></param>
/// <param name="trans"></param>
/// <param name="cmdType"></param>
/// <param name="cmdText"></param>
/// <param name="paras"></param>
private static void PrepareCommand(SqlCommand commd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] paras)
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
commd.Connection = conn;
commd.CommandText = cmdText;
if (trans != null)
commd.Transaction = trans;
commd.CommandType = cmdType;
if (paras != null)
{
foreach (SqlParameter para in paras)
{
commd.Parameters.Add(para);
}
}
}
}
#endregion
/// <summary>
/// 操作同步数据标识
/// </summary>
public static class UserStatus
{
private static bool _isUsered = false;
/// <summary>
/// 是否正在上传文件
/// </summary>
public static bool IsUsered
{
get { return UserStatus._isUsered; }
set { UserStatus._isUsered = value; }
}
}
六、xml文件UpLoadFiles.xml
<?xml version="1.0" encoding="utf-8" ?>
<Files>
<!--收费导入信息-->
<UpLoadFile name="Charge" dbname="USST_Saraly">
<file name="tblCharge.txt" tabname="tblCharge"></file>
<file name="tblChargeMode.txt" tabname="tblChargeMode"></file>
<file name="tblChargeRecord.txt" tabname="tblChargeRecord"></file>
<file name="tblItemMain.txt" tabname="tblItemMain"></file>
<file name="tblItemSub.txt" tabname="tblItemSub"></file>
<file name="tblMItemType.txt" tabname="tblMItemType"></file>
<file name="tblReturnRecord.txt" tabname="tblReturnRecord"></file>
<file name="tblSet.txt" tabname="tblSet"></file>
<file name="tblStudent.txt" tabname="tblStudent"></file>
<file name="tblTerm.txt" tabname="tblTerm"></file>
</UpLoadFile>
<!--本专科生数据导入信息-->
<UpLoadFile name="UsstCharge" dbname="TuitionTest">
<file name="tblCharge.txt" tabname="tblCharge"></file>
<file name="tblChargeMode.txt" tabname="tblChargeMode"></file>
<file name="tblChargeRecord.txt" tabname="tblChargeRecord"></file>
<file name="tblItemMain.txt" tabname="tblItemMain"></file>
<file name="tblItemSub.txt" tabname="tblItemSub"></file>
<file name="tblMItemType.txt" tabname="tblMItemType"></file>
<file name="tblReturnRecord.txt" tabname="tblReturnRecord"></file>
<file name="tblSet.txt" tabname="tblSet"></file>
<file name="tblStudent.txt" tabname="tblStudent"></file>
<file name="tblTerm.txt" tabname="tblTerm"></file>
</UpLoadFile>
<!--夜大学数据导入信息-->
<UpLoadFile name="NightCharge" dbname="USST_Saraly">
<file name="tblCharge.txt" tabname="tblCharge"></file>
<file name="tblChargeMode.txt" tabname="tblChargeMode"></file>
<file name="tblChargeRecord.txt" tabname="tblChargeRecord"></file>
<file name="tblItemMain.txt" tabname="tblItemMain"></file>
<file name="tblItemSub.txt" tabname="tblItemSub"></file>
<file name="tblMItemType.txt" tabname="tblMItemType"></file>
<file name="tblReturnRecord.txt" tabname="tblReturnRecord"></file>
<file name="tblSet.txt" tabname="tblSet"></file>
<file name="tblStudent.txt" tabname="tblStudent"></file>
<file name="tblTerm.txt" tabname="tblTerm"></file>
</UpLoadFile>
<!--研究生数据导入信息-->
<UpLoadFile name="MasterCharge" dbname="USST_Saraly">
<file name="tblCharge.txt" tabname="tblCharge"></file>
<file name="tblChargeMode.txt" tabname="tblChargeMode"></file>
<file name="tblChargeRecord.txt" tabname="tblChargeRecord"></file>
<file name="tblItemMain.txt" tabname="tblItemMain"></file>
<file name="tblItemSub.txt" tabname="tblItemSub"></file>
<file name="tblMItemType.txt" tabname="tblMItemType"></file>
<file name="tblReturnRecord.txt" tabname="tblReturnRecord"></file>
<file name="tblSet.txt" tabname="tblSet"></file>
<file name="tblStudent.txt" tabname="tblStudent"></file>
<file name="tblTerm.txt" tabname="tblTerm"></file>
</UpLoadFile>
<!--经费导入信息-->
<UpLoadFile name="Outlay" dbname="USST_OUTLAY">
<file name="JF_BCtrl.txt" tabname="JF_BCtrl"></file>
<file name="JF_BCtrlPZ.txt" tabname="JF_BCtrlPZ"></file>
<file name="JF_BDetail.txt" tabname="JF_BDetail"></file>
<file name="JF_BMain.txt" tabname="JF_BMain"></file>
<file name="JF_DW.txt" tabname="JF_DW"></file>
<file name="JF_KM.txt" tabname="JF_KM"></file>
</UpLoadFile>
<!--工资导入信息-->
<UpLoadFile name="Salary" dbname="SalaryTest">
<file name="Employees.txt" tabname="UP_Employees"></file>
<file name="EmployeeType.txt" tabname="UP_EmployeeType"></file>
<file name="Departments.txt" tabname="UP_Departments"></file>
<file name="Items.txt" tabname="UP_Items"></file>
<file name="Type_Field.txt" tabname="UP_Type_Field"></file>
<file name="SystemObject.txt" tabname="UPSystemObject"></file>
</UpLoadFile>
</Files>
七、Web.config
<?xml version="1.0" encoding="utf-8"?>
<configuration>
<appSettings>
<add key="DBConnection" value="Data Source=Sttu;Persist Security Info=True;User ID=#stmdt2008#;Context Connection=False;Password=seaskyd;Initial Catalog=FinancePortal;MultipleActiveResultSets=True"/>
<add key="XSqlPath" value="/bin"/>
<add key="XSqlFile" value="XSqlSet.xml"/>
<add key="userKey" value="1234"/>
<add key="Expire" value="20"/>
</appSettings>
<connectionStrings/>
<system.web>
<httpRuntime executionTimeout="1000" maxRequestLength="102400"/>
<compilation debug="true" />
<authentication mode="Windows" />
<!--
<customErrors mode="RemoteOnly" defaultRedirect="GenericErrorPage.htm">
<error statusCode="403" redirect="NoAccess.htm" />
<error statusCode="404" redirect="FileNotFound.htm" />
</customErrors>
-->
</system.web>
</configuration>
八、XSqlCenter.cs
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Collections.Generic;
using System.Security.Cryptography;
using System.Text;
using System.Web;
using System.Xml;
using System.Xml.XPath;
using FinancePortal.Utilities;
using XSqlEngine.QueryManger;
using XSqlEngine.Request;
using XSqlEngine.Database.MSSql;
namespace FinancePortal
{
static class XSqlCenter
{
static XSqlCenter()
{
string path_ = HttpRuntime.AppDomainAppPath+ConfigurationManager.AppSettings["XSqlPath"];
string fileName_=ConfigurationManager.AppSettings["XSqlFile"];
using (FileStream file_ = File.Open(path_+"//"+fileName_, FileMode.Open))
{
s_xSqlManger = new XSqlManger(file_, path_);
}
string strConn_ = ConfigurationManager.AppSettings["DBConnection"];
s_dataAccess = new DataAccess(strConn_);
s_userKey = ConfigurationManager.AppSettings["userKey"];
double exp_;
if(!double.TryParse(ConfigurationManager.AppSettings["Expire"], out exp_))
throw new Exception("在配置文件中获取“Expire”失败。");
s_logExpire = TimeSpan.FromMinutes(exp_);
s_userCtrl=new UserControl();
s_funcCtrl=new FuncControl();
}
public static XSqlManger s_xSqlManger;
public static DataAccess s_dataAccess;
public static UserControl s_userCtrl;
public static FuncControl s_funcCtrl;
public static string s_userKey;
public static TimeSpan s_logExpire;
}
public class QueryEnvironment
{
public QueryEnvironment(HttpRequest request, XSqlManger manger)
{
m_reqData = new RequestData(request);
KeyValuePair<XSqlInfo, XDataTrans> fullQuery_
= manger.GetQueryInfoX(m_reqData.queryName);
m_xsqlInfo = fullQuery_.Key;
m_xDataTrans = fullQuery_.Value;
try
{
m_curUser = XSqlCenter.s_userCtrl.CheckUser(m_reqData.key);
}
catch
{
if (m_xsqlInfo.auth)
throw;
}
if(m_xsqlInfo.auth)
LoadEvnParam();
}
private void LoadEvnParam()
{
XSqlInfo xSql = XSqlCenter.s_xSqlManger.GetQueryInfo("UserEvnParam");
string evnData_=XSqlCenter.s_dataAccess.Execute(xSql, null, m_reqData, this.getEnvParam);
Helpers.LoadEvnPrams(m_evnParams, evnData_);
}
public string getEnvParam(string name)
{
try
{
string param_ = null;
if (name == ":xQuery")
param_ = m_xDataTrans.text;
else if (name == ":uid")
param_= m_curUser.id;
else if (m_curUser.evnParams != null
&& m_curUser.evnParams.ContainsKey(name))
{
param_ = m_curUser.evnParams[name];
}
else if (m_curUser.curFunc != null)
{
if (name == ":funcId")
param_ = m_curUser.curFunc.id.ToString();
else if (m_curUser.curFunc.evnParams != null
&& m_curUser.curFunc.evnParams.ContainsKey(name))
param_ = m_curUser.curFunc.evnParams[name];
else
param_ = m_evnParams[name];
}
else
param_= m_evnParams[name];
return param_;
}
catch
{
return null;
}
}
public XSqlInfo xSql
{
get { return m_xsqlInfo; }
}
public XDataTrans xDataTrans
{
get { return m_xDataTrans; }
}
public RequestData requestData
{
get { return m_reqData; }
}
private RequestData m_reqData;
private UserInfo m_curUser;
private XSqlInfo m_xsqlInfo;
private XDataTrans m_xDataTrans;
private Dictionary<string, string> m_evnParams=new Dictionary<string,string>();
}
public class UserInfo
{
public UserInfo(string name, string id, int baseGroup
, string pid, bool chgPsw, string extData)
{
m_name = name;
m_id = id;
m_key = Helpers.MakeMD5(name+id+XSqlCenter.s_userKey+m_logTime.ToString());
m_baseGroup = baseGroup;
m_chgPsw = chgPsw;
m_pid = pid;
Helpers.LoadEvnPrams(m_evnParams, extData);
}
public string name
{
get { return m_name; }
}
public string id
{
get { return m_id; }
}
public string key
{
get { return m_key; }
}
public int baseGroup
{
get { return m_baseGroup; }
}
public string pid
{
get { return m_pid; }
}
public bool chgPsw
{
get { return m_chgPsw; }
}
public Dictionary<string, string> evnParams
{
get { return m_evnParams; }
}
public FuncInfo curFunc
{
get { return m_curFunc; }
set { m_curFunc = value; }
}
public DateTime logTime
{
get { return m_logTime; }
}
private string m_name;
private string m_id;
private string m_key;
private int m_baseGroup;
private bool m_chgPsw;
private string m_pid;
private DateTime m_logTime=DateTime.Now;
private Dictionary<string, string> m_evnParams=new Dictionary<string,string>();
private FuncInfo m_curFunc;
}
public class FuncInfo
{
public FuncInfo(int id, string name, string text, string frame, string frameParam
, string srcSelector, string dstSelector, string extData)
{
m_funcId = id;
m_name = name;
m_text = text;
m_frame = frame;
m_frameParam = frameParam;
m_srcSelector = srcSelector;
m_dstSelector = dstSelector;
Helpers.LoadEvnPrams(m_evnParams, extData);
}
public int id
{
get { return m_funcId; }
}
public string name
{
get { return m_name; }
}
public string text
{
get { return m_text; }
}
public string frame
{
get { return m_frame; }
}
public string frameParam
{
get { return m_frameParam; }
}
public string srcSelector
{
get { return m_srcSelector; }
}
public string dstSelector
{
get { return m_dstSelector; }
}
public Dictionary<string, string> evnParams
{
get { return m_evnParams; }
}
private int m_funcId;
private string m_name;
private string m_text;
private string m_frame;
private string m_frameParam;
private string m_srcSelector;
private string m_dstSelector;
private Dictionary<string, string> m_evnParams = new Dictionary<string, string>();
}
public class UserControl
{
public UserControl()
{}
public UserInfo Login(string userId, string psw)
{
string sql_ = "select rtrim(UserId) as UserId, rtrim(UserName) as UserName, BaseGroup, rtrim(Pid) as Pid, PswChange, ExtData"
+" from Users"
+" where UserId=@uid and Password=@psw";
SqlParameter[] uparams_ = new SqlParameter[2];
uparams_[0] = new SqlParameter("@uid", userId);
uparams_[1] = new SqlParameter("@psw", psw);
SqlDataReader dbuser_ = XSqlCenter.s_dataAccess.Execute(sql_, uparams_);
if (!dbuser_.Read())
throw new Exception("用户 "+userId+" 不存在或密码错误");
string uid_ = dbuser_.GetString(1)
, name_ = dbuser_.GetString(0)
, pid_ = dbuser_.IsDBNull(3) ? "" : dbuser_.GetString(3)
, extData_ = dbuser_.IsDBNull(5) ? "" : dbuser_.GetString(5);
int baseGroup_ = dbuser_.GetInt32(2);
bool chgPsw_ = dbuser_.GetBoolean(4);
UserInfo user_ = new UserInfo(uid_, name_, baseGroup_
, pid_, chgPsw_, extData_);
string oldKey_=null;
foreach (UserInfo oldUser_ in m_Users.Values)
{
if (oldUser_.id == userId)
{
oldKey_ = oldUser_.key;
break;
}
}
if (!string.IsNullOrEmpty(oldKey_))
m_Users.Remove(oldKey_);
if (m_Users.ContainsKey(user_.key))
m_Users[user_.key] = user_;
else
m_Users.Add(user_.key, user_);
return user_;
}
public void Logout(string key)
{
if (m_Users.ContainsKey(key))
m_Users.Remove(key);
}
public UserInfo CheckUser(string key)
{
if (!m_Users.ContainsKey(key))
throw new Exception("101,用户未登录");
UserInfo user_ = m_Users[key];
if (user_.logTime + XSqlCenter.s_logExpire < DateTime.Now)
{
m_Users.Remove(key);
throw new Exception("101,用户登录已经超时");
}
return user_;
}
public void ChangeMyPassword(string key, string oldPsw, string newPsw)
{
if (string.IsNullOrEmpty(oldPsw))
throw new Exception("旧密码无效。");
if (string.IsNullOrEmpty(newPsw))
throw new Exception("新密码无效。");
UserInfo user_=this.CheckUser(key);
string uid_ = user_.id;
string chkusr_ = "select count(*) from Users where UserId=@uid and Password=@psw";
string setpsw_ = "update Users set Password=@newPsw "
+ "where UserId=@uid and Password=@psw";
SqlParameter[] uparams_ = new SqlParameter[2];
SqlParameter[] cparams_ = new SqlParameter[3];
cparams_[0] = new SqlParameter("@uid", uid_);
cparams_[1] = new SqlParameter("@psw", oldPsw);
cparams_[2] = new SqlParameter("@newPsw", newPsw);
uparams_[0] = new SqlParameter("@uid", uid_);
uparams_[1] = new SqlParameter("@psw", oldPsw);
using (SqlDataReader dbuser_ = XSqlCenter.s_dataAccess.Execute(chkusr_, uparams_))
{
if (!dbuser_.Read())
throw new Exception("旧密码不匹配。");
int cu_ = dbuser_.GetInt32(0);
if(cu_<1)
throw new Exception("旧密码不匹配。");
}
XSqlCenter.s_dataAccess.Execute(setpsw_, cparams_);
}
public void ChangeUserPassword(string key, string uid, string newPsw)
{
if (string.IsNullOrEmpty(uid))
throw new Exception("用户名无效。");
if (string.IsNullOrEmpty(newPsw))
throw new Exception("新密码无效。");
UserInfo user_ = this.CheckUser(key);
if (user_.baseGroup != 1)
throw new Exception("你不是管理员,无权修改用户密码。");
string setpsw_ = "update Users set Password=@newPsw "
+ "where UserId=@uid";
SqlParameter[] uparams_ = new SqlParameter[2];
uparams_[0] = new SqlParameter("@uid", uid);
uparams_[1] = new SqlParameter("@newPsw", newPsw);
XSqlCenter.s_dataAccess.Execute(setpsw_, uparams_);
}
private Dictionary<string, UserInfo> m_Users=new Dictionary<string,UserInfo>();
}
public class FuncControl
{
public FuncControl()
{
string sql_ = "select FuncId, rtrim(FuncName) as FuncName, rtrim(text) as text, rtrim(Frame) as Frame, rtrim(FrameParam) as FrameParam"
+ ", rtrim(srcSelector) as srcSelector, rtrim(dstSelector) as dstSelector, ExtData"
+ " from Functions";
SqlDataReader dbFuncs_ = XSqlCenter.s_dataAccess.Execute(sql_, null);
while (dbFuncs_.Read())
{
int id_ = dbFuncs_.GetInt32(0);
string name_=dbFuncs_.GetString(1)
, text_=dbFuncs_.GetString(2)
, frame_=dbFuncs_.GetString(3)
, param_=dbFuncs_.IsDBNull(4)? "": dbFuncs_.GetString(4)
, srcSel_ = dbFuncs_.IsDBNull(5) ? "" : dbFuncs_.GetString(5)
, dstSel_ = dbFuncs_.IsDBNull(6) ? "" : dbFuncs_.GetString(6)
, extData_ = dbFuncs_.IsDBNull(7) ? "" : dbFuncs_.GetString(7);
FuncInfo func_ = new FuncInfo(id_, name_, text_
, frame_, param_, srcSel_
, dstSel_, extData_);
m_Funcs.Add(func_.name, func_);
}
}
public FuncInfo getFunc(string name)
{
if (!m_Funcs.ContainsKey(name))
throw new Exception("功能 "+name+" 不存在.");
return m_Funcs[name];
}
private Dictionary<string, FuncInfo> m_Funcs = new Dictionary<string, FuncInfo>();
}
}
九、XSqlSet.xml
<?xml version="1.0" encoding="utf-8" ?>
<XSqlSet>
<!--Main queries, user m-->
<!--File upload queries created and edited by wq-->
<!--上传缴费记录信息-->
<XSql name="ChargePanel">
<Text>
select(select
(SELECT rtrim(Name) as "@term",convert(varchar(20),InitDate,120) as "@createdate"
FROM {dbname}tblTerm ORDER BY ObjectID for xml path('term'),type) as "termSet",
(SELECT TOP 1 convert(varchar(20),ImportTime,120) as "@importtime",rtrim(UserId) as "@userid" FROM {dbname}SF_ImportLog ORDER BY objId DESC
for xml path('user'),type) as "LogSet"
for xml path('charge'), type).query('{:xQuery}')
</Text>
<DataTrans name="ChargeTerm" type="xquery">
<div>
<span>现有收费数据列表:上传时间:{data(/charge/LogSet/user/@importtime)} 上传用户:{data(/charge/LogSet/user/@userid)}</span>
<table id="tbCharge">
<tr>
<td name="term">学期</td>
<td name="createdate">创建时间</td>
</tr>
{
for $term in /charge/termSet/term
return
<tr>
<td name="term">{data($term/@term)}</td>
<td name="createdate">{data($term/@createdate)}</td>
</tr>
}
</table>
</div>
</DataTrans>
</XSql>
<!--删除缴费记录-->
<XSql name="DeleteChange">
<Text>
truncate table {dbname}tblCharge
truncate table {dbname}tblChargeMode
truncate table {dbname}tblChargeRecord
truncate table {dbname}tblItemMain
truncate table {dbname}tblItemSub
truncate table {dbname}tblMItemType
truncate table {dbname}tblReturnRecord
truncate table {dbname}tblSet
truncate table {dbname}tblStudent
truncate table {dbname}tblTerm
truncate table {dbname}SF_ImportLog
truncate table {dbname}SF_Password
select '删除成功!'
</Text>
</XSql>
<!--上传工资-->
<XSql name="LoadSalary">
<Text>
select(select rtrim(TimeID) as "@TimeID",rtrim(GroupID) as "@groupID",
(rtrim(ATypeName)+','+rtrim(BTypeName)) as "@account",rtrim(Note) as "@note",
convert(varchar(20),ImportTime,120) as "@importtiem",rtrim(UserID) as "@userId"
FROM {dname}GZ_ImportLog ORDER BY TimeId DESC,GroupID DESC
for xml path('saraly'),type,root('saralys')).query('{:xQuery}')
</Text>
<DataTrans name="SalaryDetail" type="xquery">
<div>
<span>现有工资数据列表:</span>
<table id="tbSalary">
<tr id="tbTitle">
<td>数据时间</td>
<td>批次</td>
<td>账套</td>
<td>描述</td>
<td>上传时间</td>
<td>上传用户</td>
</tr>
{
for $saraly in /saralys/saraly
return
<tr>
<td name="TimeID">{data($saraly/@TimeID)}</td>
<td name="groupID">{data($saraly/@groupID)}</td>
<td name="account">{data($saraly/@account)}</td>
<td name="note">{data($saraly/@note)}</td>
<td name="importtiem">{data($saraly/@importtiem)}</td>
<td name="userId">{data($saraly/@userId)}</td>
</tr>
}
</table>
</div>
</DataTrans>
</XSql>
<!--删除工资信息-->
<XSql name="DeleteSalarys">
<Text>
DELETE FROM {dname}GZ_Employees WHERE TimeId='{timeId}' AND GroupID={groupId}
DELETE FROM {dname}GZ_EmployeeType WHERE TimeId='{timeId}' AND GroupID={groupId}
DELETE FROM {dname}GZ_Items WHERE TimeId='{timeId}' AND GroupID={groupId}
DELETE FROM {dname}GZ_Type_Field WHERE TimeId='{timeId}' AND GroupID={groupId}
DELETE FROM {dname}GZ_ImportLog WHERE TimeId='{timeId}' AND GroupID={groupId}
DELETE t1 FROM {dname}GZ_Password t1 LEFT JOIN {dname}GZ_Employees t2 ON t1.Employeeid=t2.EmployeeId WHERE t2.employeeid is null
select '删除成功!'
</Text>
</XSql>
</XSqlSet>
一、upFile.html内容如下:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head>
<meta http-equiv="Content-Type" content="text/html; charset=GBK" />
<title>FileUpload</title>
<link rel="stylesheet" href="js/jquery/themes/flora/flora.all.css" type="text/css" media="screen" title="Flora (Default)" />
<link rel="stylesheet" href="css/FileUps.css" type="text/css" />
<script type="text/javascript" src="js/jquery/jquery.js" language="javascript"></script>
<script type="text/javascript" src="js/jquery/ui/ui.core.js"></script>
<script type="text/javascript" src="js/jquery/ui/ui.dialog.js"></script>
<script type="text/javascript" src="js/jquery/ui/ui.resizable.js"></script>
<script type="text/javascript" src="js/jquery/ui/ui.draggable.js"></script>
<script type="text/javascript" src="js/jquery/ui/ui.tabs.js"></script>
<script type="text/javascript" src="js/ajaxfileupload.js" language="javascript"></script>
<script type="text/javascript" src="js/FinancePortal.js"></script>
<!--<script type="text/javascript" src="js/blackbody.js"></script>-->
<script type="text/javascript">
var userId="admin";
var key;
var name;
</script>
</head>
<body>
<script type="text/javascript">
var timeId;
var groupId;
function ajaxFileUpload(divid,basegroup,typen,onsuccess,onerror){
$("#div1").ajaxStart(function(){
$("#div1").css("display","block");
$(this).dialog({
modal: true,
overlay: {
opacity: 0.5,
background: "black"
}
});
}).ajaxComplete(function(){
$(this).dialog("destroy");
});
$.ajaxFileUpload({
url:'doupFiles.ashx?id='+userId+'&&ks='+divid+'&&group='+basegroup+'',
secureuri:false,
fileElementId:''+typen+'',
dataType:'json',
success:function(data,status)
{
if(data.error=='success')
onsuccess(data.msg);
else
onerror(data.msg);
},
error:function(data,status,e)
{
onerror(e);
}
});
}
function loadData(divname,dbname)
{
if(divname=="sayDetail")
{
var param="<ParamSet><CmdParam><dname>"+dbname+"</dname></CmdParam></ParamSet>";
DoQuery(userId,key,"LoadSalary:SalaryDetail",param,function(html){
$("#sayDetail").html(html);
$("#sayDetail table").attr("class","repeat-all");
$("#sayDetail tr").click(function(){
if($(this).attr("id")!="tbTitle"){
timeId=$(this).find("td[name='TimeID']").text();
groupId=$(this).find("td[name='groupID']").text();
$("#sayDetail tr[class='dark']").removeClass("dark");
$(this).addClass("dark");
}
else
{
$("#sayDetail tr[class='dark']").removeClass("dark");
}
});
},ShowError);
}
else
{
var paras="<ParamSet><CmdParam><dbname>"+dbname+"</dbname></CmdParam></ParamSet>";
DoQuery(userId,key,"ChargePanel:ChargeTerm",paras,function(html){
$("#"+divname).html(html);
$("#"+divname+" table").attr("class","tbCharge-all");
},ShowError);
}
}
$(document).ready(function(){
$("#tabsEx1 > ul").tabs({
selected: 1
});
Login(userId,"111",function(rkey,rname,chgPsw){
key=rkey;
name=rname;
$("#tabsEx1>div>button[name='upload']").click(function(){
var p=$(this).parent();
var basegroup=p.attr("basegroup");
var typen=p.find("input[name='fileToUpload']").attr("id");
var divid=p.attr("id");
ajaxFileUpload(divid,basegroup,typen,function(result){
if(divid!="Salary"){
loadData("chge","TuitionTest..");
loadData("usstchge","TuitionTest..");
loadData("nightchge","TuitionTest..");
loadData("masterchge","TuitionTest..");
}
else
{
loadData("sayDetail","SalaryTest..");
}
}, ShowError);
});
$("#tabsEx1>div>button[name='delete']").click(function(){
var dname="TuitionTest..";
var para="<ParamSet><CmdParam><dbname>"+dname+"</dbname></CmdParam></ParamSet>";
DoQuery(userId,key,"DeleteChange",para,function(html){
alert(html);
loadData("chge",dname);
loadData("usstchge",dname);
loadData("nightchge",dname);
loadData("masterchge",dname);
},ShowError)
});
$("#DeleteSaraly").click(function(){
if(timeId!=null && groupId!=null && timeId!="" && groupId!=""){
var dbname="SalaryTest..";
var param="<ParamSet><CmdParam><dname>"+dbname+"</dname><timeId>"+timeId+"</timeId><groupId>"+groupId+"</groupId></CmdParam></ParamSet>";
DoQuery(userId,key,"DeleteSalarys",param,function(html){
alert(html);
loadData("sayDetail",dbname);
},ShowError)
timeId=null;
groupId=null;
}
else
{
alert('请选择删除的行!');
}
});
loadData("chge","TuitionTest..");
loadData("usstchge","TuitionTest..");
loadData("nightchge","TuitionTest..");
loadData("masterchge","TuitionTest..");
loadData("sayDetail","SalaryTest..");
},ShowError);
})
</script>
<div id="maindiv" class="div-dsay">
<div id="div1" class="ui-dialog ui-draggable" style="display:none" title="上传文件..">
<center>
<img id="Img1" src="image/loadingAnimation.gif" alt="file uploading..." />
<br /><span>正在上传文件...</span>
</center>
</div>
<div id="tabsEx1" class="ui-tabs-nav div-tabsEx1">
<ul style="height:30px;">
<li class="ui-tabs-nav-item"><a href="#Charge"><span>收费</span></a></li>
<li class="ui-tabs-nav-item"><a href="#UsstCharge"><span>本专科</span></a></li>
<li class="ui-tabs-nav-item"><a href="#NightCharge"><span>夜大学</span></a></li>
<li class="ui-tabs-nav-item"><a href="#MasterCharge"><span>研究生</span></a></li>
<li class="ui-tabs-nav-item"><a href="#Salary"><span>工资</span></a></li>
</ul>
<div id="Charge" basegroup="3">
<p>文件上传:<input type="file" id="fileToUpload" size="40" name="fileToUpload" /></p>
<button id="buttonUpload" class="button" name="upload">上传</button>
<button id="btnDelete" class="button" name="delete">删除</button><br />
<div id="chge" class="div-detail-div"></div>
</div>
<div id="UsstCharge" basegroup="3">
<p>文件上传:<input type="file" id="Usstfile" size="40" name="fileToUpload" /></p>
<button id="btnUsst" class="button" name="upload">上传</button>
<button id="btnDelLog" class="button" name="delete">删除</button><br />
<div id="usstchge" class="div-detail-div"></div>
</div>
<div id="NightCharge">
<p>文件上传:<input type="file" id="Nightfile" size="40" name="fileToUpload" /></p>
<button id="btnNight" class="button" name="upload">上传</button>
<button id="btnDelNight" class="button" name="delete">删除</button><br />
<div id="nightchge" class="div-detail-div"></div>
</div>
<div id="MasterCharge">
<p>文件上传:<input type="file" id="Masterfile" size="40" name="fileToUpload" /></p>
<button id="btnMaster" class="button" name="upload">上传</button>
<button id="btnDelMaster" class="button" name="delete">删除</button><br />
<div id="masterchge" class="div-detail-div"></div>
</div>
<div id="Salary" basegroup="4">
<p>文件上传:<input type="file" id="fileToloads" size="40" name="fileToUpload" /> </p>
<button id="btnSqrary" class="button" name="upload">上传</button>
<button id="DeleteSaraly" class="button" name="DeleteSaraly">删除</button> <br />
<div id="sayDetail" class="div-detail-div"></div>
</div>
</div>
</div>
</body>
</html>
备注:js文件在jquery的官方网站去下载。
二、ajaxfileupload.js文件如下:
jQuery.extend({
createUploadIframe: function(id, uri)
{
//create frame
var frameId = 'jUploadFrame' + id;
if(window.ActiveXObject) {
var io = document.createElement('<iframe id="' + frameId + '" name="' + frameId + '" />');
if(typeof uri== 'boolean'){
io.src = 'javascript:false';
}
else if(typeof uri== 'string'){
io.src = uri;
}
}
else {
var io = document.createElement('iframe');
io.id = frameId;
io.name = frameId;
}
io.style.position = 'absolute';
io.style.top = '-1000px';
io.style.left = '-1000px';
document.body.appendChild(io);
return io
},
createUploadForm: function(id, fileElementId)
{
//create form
var formId = 'jUploadForm' + id;
var fileId = 'jUploadFile' + id;
var form = $('<form action="" method="POST" name="' + formId + '" id="' + formId + '" enctype="multipart/form-data"></form>');
var oldElement = $('#' + fileElementId);
var newElement = $(oldElement).clone();
$(oldElement).attr('id', fileId);
$(oldElement).before(newElement);
$(oldElement).appendTo(form);
//set attributes
$(form).css('position', 'absolute');
$(form).css('top', '-1200px');
$(form).css('left', '-1200px');
$(form).appendTo('body');
return form;
},
ajaxFileUpload: function(s) {
// TODO introduce global settings, allowing the client to modify them for all requests, not only timeout
s = jQuery.extend({}, jQuery.ajaxSettings, s);
var id = new Date().getTime()
var form = jQuery.createUploadForm(id, s.fileElementId);
var io = jQuery.createUploadIframe(id, s.secureuri);
var frameId = 'jUploadFrame' + id;
var formId = 'jUploadForm' + id;
// Watch for a new set of requests
if ( s.global && ! jQuery.active++ )
{
jQuery.event.trigger( "ajaxStart" );
}
var requestDone = false;
// Create the request object
var xml = {}
if ( s.global )
jQuery.event.trigger("ajaxSend", [xml, s]);
// Wait for a response to come back
var uploadCallback = function(isTimeout)
{
var io = document.getElementById(frameId);
try
{
if(io.contentWindow)
{
xml.responseText = io.contentWindow.document.body?io.contentWindow.document.body.innerHTML:null;
xml.responseXML = io.contentWindow.document.XMLDocument?io.contentWindow.document.XMLDocument:io.contentWindow.document;
}else if(io.contentDocument)
{
xml.responseText = io.contentDocument.document.body?io.contentDocument.document.body.innerHTML:null;
xml.responseXML = io.contentDocument.document.XMLDocument?io.contentDocument.document.XMLDocument:io.contentDocument.document;
}
}catch(e)
{
jQuery.handleError(s, xml, null, e);
}
if ( xml || isTimeout == "timeout")
{
requestDone = true;
var status;
try {
status = isTimeout != "timeout" ? "success" : "error";
// Make sure that the request was successful or notmodified
if ( status != "error" )
{
// process the data (runs the xml through httpData regardless of callback)
var data = jQuery.uploadHttpData( xml, s.dataType );
// If a local callback was specified, fire it and pass it the data
if ( s.success )
s.success( data, status );
// Fire the global callback
if( s.global )
jQuery.event.trigger( "ajaxSuccess", [xml, s] );
} else
jQuery.handleError(s, xml, status);
} catch(e)
{
status = "error";
jQuery.handleError(s, xml, status, e);
}
// The request was completed
if( s.global )
jQuery.event.trigger( "ajaxComplete", [xml, s] );
// Handle the global AJAX counter
if ( s.global && ! --jQuery.active )
jQuery.event.trigger( "ajaxStop" );
// Process result
if ( s.complete )
s.complete(xml, status);
jQuery(io).unbind()
setTimeout(function()
{ try
{
$(io).remove();
$(form).remove();
} catch(e)
{
jQuery.handleError(s, xml, null, e);
}
}, 100)
xml = null
}
}
// Timeout checker
if ( s.timeout > 0 )
{
setTimeout(function(){
// Check to see if the request is still happening
if( !requestDone ) uploadCallback( "timeout" );
}, s.timeout);
}
try
{
// var io = $('#' + frameId);
var form = $('#' + formId);
$(form).attr('action', s.url);
$(form).attr('method', 'POST');
$(form).attr('target', frameId);
if(form.encoding)
{
form.encoding = 'multipart/form-data';
}
else
{
form.enctype = 'multipart/form-data';
}
$(form).submit();
} catch(e)
{
jQuery.handleError(s, xml, null, e);
}
if(window.attachEvent){
document.getElementById(frameId).attachEvent('onload', uploadCallback);
}
else{
document.getElementById(frameId).addEventListener('load', uploadCallback, false);
}
return {abort: function () {}};
},
uploadHttpData: function( r, type ) {
var data = !type;
data = type == "xml" || data ? r.responseXML : r.responseText;
// If the type is "script", eval it in global context
if ( type == "script" )
jQuery.globalEval( data );
// Get the JavaScript object, if JSON is used.
if ( type == "json" )
eval( "data = " + data );
// evaluate scripts within html
if ( type == "html" )
jQuery("<div>").html(data).evalScripts();
//alert($('param', data).each(function(){alert($(this).attr('value'));}));
return data;
}
})
三、doupFiles.ashx
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.IO;
using System.Text;
using System.Data.SqlClient;
using System.Web.Services;
using System.Web.Services.Protocols;
using FinancePortal.Utilities;
using System.Threading;
namespace FinancePortal
{
/// <summary>
/// $codebehindclassname$ 的摘要说明
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
public class doupFiles : IHttpHandler
{
public void ProcessRequest(HttpContext context)
{
string msg = "上传完毕";
string error = "success";
if (UserStatus.IsUsered) //是否有用户正在上传同一个文件
{
error = "error";
msg = "不能同时上传文件,请您等待...";
}
else
{
//获取一个以当前系统时间为文件名
string filename = string.Format("{0}{1}{2}{3}{4}{5}", DateTime.Today.Year, DateTime.Today.Month, DateTime.Today.Day, System.DateTime.Now.Hour, System.DateTime.Now.Minute, System.DateTime.Now.Second);
try
{
string userId = context.Request.QueryString["id"];
string FileN = context.Request.QueryString["ks"]; //得到参数值
int basegroupid = int.Parse(context.Request.QueryString["group"]);
UserStatus.IsUsered = true;
if (context.Request.Files.Count > 0)
{
HttpPostedFile file = context.Request.Files[0];
string othername = Path.GetFileNameWithoutExtension(file.FileName); //得到原压缩文件名
if (file.ContentLength == 0)
{
error = "error";
msg = "上传文件不能为空!";
}
else
{
file.SaveAs(context.Server.MapPath("FileUp") + "//" + filename + ".zip"); //上传文件到服务器
//添加参数
SqlParameter[] paras ={ new SqlParameter("@BaseGroup",SqlDbType.Int),
new SqlParameter("@UserID",SqlDbType.NVarChar,50),
new SqlParameter("@note",SqlDbType.NVarChar,100)
};
paras[0].Value = basegroupid;
paras[1].Value = userId;
paras[2].Value = othername;
SqlParameter[] parar ={ paras[0], paras[1] }; //设置缴费参数
UpFileLoad fileup = new UpFileLoad();
if (FileN.Equals(EnuNumber.enumDataType.Charge.ToString()) || FileN.Equals(EnuNumber.enumDataType.UsstCharge.ToString()) || FileN.Equals(EnuNumber.enumDataType.MasterCharge.ToString())
|| FileN.Equals(EnuNumber.enumDataType.NightCharge.ToString()))
{
fileup.ProccessRarFile(filename, FileN, "proc_Tuition", parar); //调用上传方法
}
else if (FileN.Equals(EnuNumber.enumDataType.Salary.ToString()))
{
fileup.ProccessRarFile(filename, FileN, "proc_Salary", paras);
}
error = "success";
msg = "上传完毕";
}
}
}
catch (Exception ex) //捕获异常
{
error = "error";
msg = ErrorProcess.ErrorMessage(ex);
}
finally
{
UserStatus.IsUsered = false; //释放资源
try
{
DirectoryInfo info = CreateFolders.checkValidSessionPath("FileUp//temp//");
if (info.GetFiles().Length > 1)
{
foreach (FileInfo filein in info.GetFiles())
{
filein.Delete(); //删除文件
}
}
}
catch (IOException er)
{ }
try
{
CreateFolders.DeleteFile("FileUp//" + filename + ".zip"); //删除压缩包
}
catch (IOException eex)
{ }
}
}
//context.Response.ContentType = "text/xml";
string result = "{ error:'" + error + "',msg:'" + msg + "'}";
context.Response.Write(result);
}
public bool IsReusable
{
get
{
return false;
}
}
}
}
四、FinancePortal.js
function ShowMsg(title, msg){
$("<div id=/"MsgDialog/" class=/"ui-dialog ui-draggable/" title=/""+title+"/"></div>").html(msg).dialog({
modal: true,
overlay: {
opacity: 0.5,
background: "black"
},
buttons: {
"确定": function(){
$(this).dialog("destroy");
$("#ErrorDialog").remove();
}
}
});
}
function ShowError(msg){
ShowMsg("错误", msg);
}
function ResultProcess(result, onsuccess, onerror) {
var pos = result.indexOf(":");
var status = result.substring(0, pos);
var text = result.substring(pos+1);
if( status == "success")
onsuccess(text);
else
onerror(text);
}
function Login(userId, userPsw, onsuccess, onerror)
{
$.ajax({
url: "UserAuth.ashx",
cache: false,
data: {uid:userId, psw: userPsw, type: "login"},
success: function(html){
ResultProcess(html, function(result){
var res=result.split(",");
onsuccess(res[0], res[1], res[2]);
}, onerror);
},
error: function (XMLHttpRequest, textStatus, errorThrown) {
onerror(textStatus+":"+XMLHttpRequest.status+"; "+XMLHttpRequest.responseText);
}
});
}
function Logout(ukey)
{
$.ajax({
url: "UserAuth.ashx",
cache: false,
data: {key: ukey, type: "logout"}
});
}
function ChangePassword(ukey, utype, userId, oldp, newp, onsuccess, onerror) {
$.ajax({
url: "UserAuth.ashx",
cache: false,
data: {key:ukey, user: userId, oldPsw: oldp, newPsw: newp, type: utype},
success: function(html){
ResultProcess(html, function(result){
onsuccess(result);
}, onerror);
},
error: function (XMLHttpRequest, textStatus, errorThrown) {
onerror(textStatus+":"+XMLHttpRequest.status+"; "+XMLHttpRequest.responseText);
}
});
}
function ChangeMyPassword(ukey, oldp, newp, onsuccess, onerror) {
ChangePassword(ukey, "chgMyPsw", "", oldp, newp, onsuccess, onerror);
}
function ChangeUserPassword(ukey, userId, newp, onsuccess, onerror) {
ChangePassword(ukey, "chgUserPsw", userId, "", newp, onsuccess, onerror);
}
function LoadUserFunc(userid, key, onsuccess, onerror) {
$.ajax({
url: "DataQuery.ashx",
cache: false,
data: {uid:userid, key: key, queryName: "UserFunc:ShowInTable", params: ""},
success: function(html){
ResultProcess(html, onsuccess, onerror);
},
error: function (XMLHttpRequest, textStatus, errorThrown) {
onerror(textStatus+":"+XMLHttpRequest.status);
}
});
}
function LoadQueryFrame(usrKey, elem, onerror){
var setting=$(elem);
var xsqlFuncName=setting.attr("xsqlFuncName");
var xsqlFrame=setting.attr("xsqlFrame");
var xsqlFrameParam=setting.attr("xsqlFrameParam");
var xsqlsrcSelector=setting.attr("xsqlsrcSelector");
var xsqldstSelector=setting.attr("xsqldstSelector");
$.ajax({
url: "QueryFrames.ashx",
cache: false,
data: {key: usrKey, name: xsqlFuncName, frame: xsqlFrame, selector: xsqlsrcSelector},
dataType: "html",
success: function(res){
ResultProcess(res, function(html){
//var cont=html;
//cont="<script type=/"text/javascript/">/n var funcName=/""+xsqlFuncName+"/";/n</script>/n"+cont;
$(xsqldstSelector).html(html);
}, onerror);
},
error: function (XMLHttpRequest, textStatus, errorThrown) {
onerror(textStatus+":"+XMLHttpRequest.status);
}
});
}
function DoQuery(userid, key, qname, qparam, onsuccess, onerror)
{
$.ajax({
url: "DataQuery.ashx",
cache: false,
data: {uid:userid, key: key, queryName: qname, params: qparam},
success: function(html){
ResultProcess(html, onsuccess, onerror);
},
error: function (XMLHttpRequest, textStatus, errorThrown) {
onerror(textStatus+":"+XMLHttpRequest.status);
}
});
}
五、类UpFileLoad.cs
/*
* 说明:本类实现解压缩和上传文本文件到数据库表中
*/
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Web.UI.MobileControls;
using System.IO;
using System.IO.Compression;
using System.Data.SqlClient;
using System.Text;
using System.Runtime.InteropServices;
using Microsoft.Win32;
using System.Diagnostics;
using Microsoft.VisualBasic;
using System.Xml;
using System.Xml.XPath;
using System.Collections.Generic;
using System.Text.RegularExpressions;
/// <summary>
/// UpFileLoad 的摘要说明
/// </summary>
public class UpFileLoad
{
/// <summary>
/// 获取数据库连接字符串
/// </summary>
private string connstring = System.Configuration.ConfigurationManager.AppSettings["DBConnection"];
public UpFileLoad()
{
// TODO: 在此处添加构造函数逻辑
//
}
/// <summary>
/// 获取数据库名称
/// </summary>
/// <param name="filename"></param>
/// <returns></returns>
public string GetConnectiong(string filename)
{
string dbname = string.Empty;
foreach (UpFileSet upset in UpXmlFile.sel[filename])
{
dbname = upset.DbName;
break;
}
return dbname;
}
/// <summary>
/// 解压文件并上传文件
/// </summary>
/// <param name="Zipname"></param>
/// <param name="userId"></param>
/// <param name="upfilename"></param>
/// <param name="produrename"></param>
/// <param name="paras"></param>
public void ProccessRarFile(string Zipname, string upfilename,string procedurename,SqlParameter[] paras)
{
try
{
String the_Zip; //要解压的文件
String the_info;
ProcessStartInfo the_StartInfo;
Process the_Process;
string pathfile = System.Web.HttpContext.Current.Server.MapPath("FileUp//temp//");
string pathara = System.Web.HttpContext.Current.Server.MapPath("FileUp//"); //找到文件夹的父文件夹
string PathName = System.Web.HttpContext.Current.Server.MapPath("FileUp//7za");
the_Zip = PathName.ToString();
the_info = " e " + pathara + Zipname + ".zip " + "-o" + pathfile+" *.txt -r"; //解压的命令
the_StartInfo = new ProcessStartInfo();
the_StartInfo.FileName = the_Zip;
the_StartInfo.Arguments = the_info;
the_StartInfo.WindowStyle = ProcessWindowStyle.Hidden; //隐藏进程窗体
the_StartInfo.CreateNoWindow = true;
using (the_Process = new Process())
{
the_Process.StartInfo = the_StartInfo;
the_Process.Start();
the_Process.WaitForExit(); //等待进程结束
}
string path = string.Empty;
DirectoryInfo info = CreateFolders.checkValidSessionPath("FileUp//temp//"); //查找文件
if (info.GetFiles().Length > 1)
{
pathfile = System.Web.HttpContext.Current.Server.MapPath("FileUP//temp//");
}
else
{
throw new Exception("压缩包是空的,请上传有文件的压缩包!"); //抛异常
}
bool targe = this.CheckFile(upfilename); //检查文件是否与配置文件一样
if (!targe)
{
throw new Exception("压缩包缺少文件,请重新上传!");
}
this.UpLoad(pathfile,upfilename); //调用写数据库方法
this.ExecuteProduce(procedurename, paras);
}
catch
{
throw;
}
}
/// <summary>
/// 处理上传好的数据数据
/// </summary>
/// <param name="procname"></param>
/// <param name="paraments"></param>
private void ExecuteProduce(string procname, SqlParameter[] paraments)
{
using (SqlConnection connect = new SqlConnection(connstring))
{
connect.Open();
using (SqlTransaction tran = connect.BeginTransaction())
{
try
{
//处理密码,执行存储过程
if (procname != null)
Command.ExecuteNonQuery(tran, CommandType.StoredProcedure, procname, paraments);
else
throw new Exception("存储过程不能为空,请查看数据库是否创建");
tran.Commit();
}
catch
{
tran.Rollback();
throw;
}
}
}
}
/// <summary>
/// 把文件写入数据库表
/// </summary>
/// <param name="filePath"></param>
/// <param name="UserId"></param>
/// <param name="produrename"></param>
/// <param name="paras"></param>
private void UpLoad(string filePath,string upname)
{
using (SqlConnection conn = new SqlConnection(connstring))
{
conn.Open(); //打开连接
using (SqlTransaction trans = conn.BeginTransaction())
{
try
{
string DELETE_TABLE = string.Empty; //删除表的字符串
SqlDataAdapter da = new SqlDataAdapter();
string dbname = GetConnectiong(upname);
if (string.IsNullOrEmpty(dbname)) //检查数据库是否存在
throw new Exception("请检查xml文件配置是否与数据库匹配");
foreach (UpFileSet upSet in UpXmlFile.sel[upname])
{
foreach (UpFile upf in upSet.M_upFiles)
{
DELETE_TABLE += "truncate table " +dbname+".."+upf.TableName+" /r/n"; //获取SQL语句
}
}
Command.ExecuteNonQuery(trans, DELETE_TABLE, null); //删除数据表中原来的数据
foreach (UpFileSet upSet in UpXmlFile.sel[upname])
{
foreach (UpFile upf in upSet.M_upFiles)
{
ImportToDataBase(filePath, upf.TableName,upf.FileName,dbname,da, trans); //填充数据表
}
}
trans.Commit(); //提交事务
}
catch(KeyNotFoundException ex)
{
throw new Exception(ex.Message+",请查看配置文件或xml文件是否有该文件");
}
catch
{
trans.Rollback(); //事务回滚
throw;
}
}
}
}
/// <summary>
/// 检查几个文本文件是否存在
/// </summary>
/// <returns></returns>
private bool CheckFile(string filename)
{
bool flag = false;
DirectoryInfo info = CreateFolders.checkValidSessionPath("FileUp//temp//"); //找文件路径
FileInfo[] fileinfo = info.GetFiles();
List<UpFileSet> list = UpXmlFile.sel[filename]; //得到配置文件中的文件
foreach (UpFileSet upset in list)
{
if (fileinfo.Length == upset.M_upFiles.Count) //是否存在相同的文件
{
flag = true;
break; }
}
return flag;
}
/// <summary>
/// 把文本文件导入数据库
/// </summary>
/// <param name="filePath">文本文件路径</param>
/// <param name="table">文本文件名或数据库表名</param>
/// <param name="da"></param>
/// <param name="tran">事务处理</param>
public void ImportToDataBase(string filePath, string table,string filename,string dbname,SqlDataAdapter da,SqlTransaction tran)
{
da.SelectCommand = new SqlCommand("select * from " + dbname+".."+table + " where 0=1", tran.Connection, tran);
DataTable dt = new DataTable(); //新建DataTable
da.Fill(dt);
da.SelectCommand.UpdatedRowSource = UpdateRowSource.None;
da.SelectCommand = new SqlCommand("select * from " + dbname+".."+table, tran.Connection, tran);
da.SelectCommand.UpdatedRowSource = UpdateRowSource.None;
da.SelectCommand.CommandTimeout = 900;
SqlCommandBuilder cbuilder = new SqlCommandBuilder(da); //初始化新实例
string insertCmd = "";
string p = "";
for (int i = 0; i < dt.Columns.Count; i++)
{
insertCmd += "[";
insertCmd += dt.Columns[i].ColumnName; //处理表的列
insertCmd += "],";
p += "@p";
p += (i + 1).ToString(); //处理表中的数据
p += ",";
}
insertCmd = insertCmd.Substring(0, insertCmd.Length - 1); //得到列
p = p.Substring(0, p.Length - 1); //得到列中数据
string cmdText = "insert into " + dbname+".."+table + "(" + insertCmd + ") values (" + p + ")";
SqlCommand command = new SqlCommand(cmdText, tran.Connection, tran);
command.CommandTimeout = 900; //设置超时时间
foreach (SqlParameter para in cbuilder.GetInsertCommand().Parameters)
{
command.Parameters.Add(para.ParameterName, para.SqlDbType, para.Size, para.SourceColumn); //添加参数
}
da.InsertCommand = command;
da.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
da.InsertCommand.CommandTimeout = 900;
ImportToDataTable(filePath + filename, ref dt);
da.UpdateBatchSize = 400; //设置每次到服务器的往返过程中处理的行数
da.Update(dt);
dt.Dispose(); //释放资源
}
/// <summary>
/// 把文本文件的数据存入传进来的DataTable中
/// </summary>
/// <param name="filePath">文本文件的文件绝对路径</param>
/// <param name="dt">DataTable,传引用,可以修改</param>
private void ImportToDataTable(string filePath, ref DataTable dt)
{
int fileNumber = FileSystem.FreeFile(); //读取文件号
try
{
FileSystem.FileOpen(fileNumber, filePath, OpenMode.Input, OpenAccess.Read, OpenShare.LockReadWrite, -1);
DataRow dr;
int columns = dt.Columns.Count; //得到数据表的总列数
while (!FileSystem.EOF(fileNumber)) //读取文本文件
{
dr = dt.NewRow();
object obj = null;
for (int i = 0; i < columns; i++)
{
FileSystem.Input(fileNumber, ref obj); //打开顺序文件的顺序,并赋值给表的列
dr[i] = obj;
}
dt.Rows.Add(dr); //添加行
}
FileSystem.FileClose(fileNumber); //关闭文件
}
catch
{
FileSystem.FileClose(fileNumber);
throw;
}
}
}
/// <summary>
/// 加载xml文件
/// </summary>
public static class UpXmlFile
{
static UpXmlFile()
{
//得到操作xml文件对象
XPathDocument doc = new XPathDocument(System.Web.HttpContext.Current.Server.MapPath("UpLoadFiles.xml"));
XPathNavigator nva = doc.CreateNavigator(); //定位到当前节点
XPathNodeIterator upFiles = nva.Select("/Files/UpLoadFile"); //得到指定的节点
foreach (XPathNavigator upfile in upFiles)
{
XPathNodeIterator files = upfile.Select("file"); //得到子节点
string name = upfile.GetAttribute("name", "");
string dbname = upfile.GetAttribute("dbname", ""); //得到子节点属性
List<UpFileSet> listfile = new List<UpFileSet>();
List<UpFile> filelist = new List<UpFile>();
foreach (XPathNavigator file in files)
{
UpFile xml1 = new UpFile(file.GetAttribute("name", ""), file.GetAttribute("tabname", ""));
filelist.Add(xml1);
}
UpFileSet upSet = new UpFileSet(dbname, filelist);
listfile.Add(upSet);
sel.Add(name, listfile);
}
}
public static Dictionary<string, List<UpFileSet>> sel = new Dictionary<string, List<UpFileSet>>(); //提供外界使用的集合
}
/// <summary>
/// 文件名和表名类
/// </summary>
public class UpFile
{
public UpFile(string fn, string tn)
{
fileName = fn;
tableName = tn;
}
/// <summary>
/// 文本文件名,带扩展名的
/// </summary>
public string FileName
{
get { return fileName; }
}
/// <summary>
/// 数据库表名
/// </summary>
public string TableName
{
get { return tableName; }
}
private string fileName; //文件名
private string tableName; //表名
}
/// <summary>
/// 封装数据库名和上传类型名称
/// </summary>
public class UpFileSet
{
public UpFileSet(string name, List<UpFile> upfiles)
{
m_dbName = name;
m_upFiles = upfiles;
}
/// <summary>
/// 数据库名称
/// </summary>
public string DbName
{
get { return m_dbName; }
}
public List<UpFile> M_upFiles
{
get { return m_upFiles; }
}
private string m_dbName; //数据库名
private List<UpFile> m_upFiles; //文件表名列表
}
/// <summary>
/// 枚举类
/// </summary>
public class EnuNumber
{
/// <summary>
/// 类型-枚举
/// </summary>
public enum enumDataType
{
Charge, //缴费
UsstCharge, //本专科缴费
NightCharge, //夜大缴费
MasterCharge, //研究生缴费
Outlay, //金费
Salary //工资
}
}
#region 操作文件
/// <summary>
/// 操作文件夹及文件
/// </summary>
public class CreateFolders
{
/// <summary>
/// 删除一个文件夹下面的子文件夹和文件
/// </summary>
/// <param name="Folderpathname"></param>
public static void DeleteChildFolder(string Folderpathname)
{
if (Folderpathname.Trim().Length > 0)
{
try
{
string Createpath = System.Web.HttpContext.Current.Server.MapPath(Folderpathname);
if (Directory.Exists(Createpath))
{
Directory.Delete(Createpath, true); //删除指定的目录并(如果指示)删除该目录中的任何子目录
}
}
catch
{
throw;
}
}
}
/// <summary>
/// 删除文件,必须指定文件名和扩展名(文件全名)
/// </summary>
/// <param name="Filepathname"></param>
public static void DeleteFile(string Filepathname)
{
try
{
FileInfo DeleFile = new FileInfo(System.Web.HttpContext.Current.Server.MapPath(Filepathname).ToString());
DeleFile.Delete(); //删除文件
}
catch
{
throw;
}
}
/// <summary>
/// 删除整个文件夹及其子文件夹和文件
/// </summary>
/// <param name="FolderPathName"></param>
public static void DeleParentFolder(string FolderPathName)
{
try
{
DirectoryInfo DelFolder = new DirectoryInfo(System.Web.HttpContext.Current.Server.MapPath(FolderPathName).ToString());
if (DelFolder.Exists)
{
DelFolder.Delete(true);
//DelFolder.Delete();
}
}
catch
{
throw;
}
}
/// <summary>
/// 读取文件夹的文件
/// </summary>
/// <param name="FilePathName"></param>
/// <returns></returns>
public static DirectoryInfo checkValidSessionPath(string FilePathName)
{
try
{
DirectoryInfo mainDir = new DirectoryInfo(System.Web.HttpContext.Current.Server.MapPath(FilePathName));
return mainDir;
}
catch
{
throw;
}
}
}
#endregion
#region 操作数据表
public abstract class Command
{
/// <summary>
/// Execute a SqlCommand (that returns no resultset) using an existing SQL Transaction
/// using the provided parameters.
/// </summary>
/// <param name="trans"></param>
/// <param name="cmdType"></param>
/// <param name="cmdText"></param>
/// <param name="cmdParms"></param>
/// <returns></returns>
public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
{
SqlCommand cmd = new SqlCommand();
cmd.CommandTimeout = 900;
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
public static int ExecuteNonQuery(SqlTransaction trans, string cmdText, params SqlParameter[] cmdParms)
{
return ExecuteNonQuery(trans, CommandType.Text, cmdText, cmdParms);
}
/// <summary>
/// Prepare a command for execution
/// </summary>
/// <param name="commd"></param>
/// <param name="conn"></param>
/// <param name="trans"></param>
/// <param name="cmdType"></param>
/// <param name="cmdText"></param>
/// <param name="paras"></param>
private static void PrepareCommand(SqlCommand commd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] paras)
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
commd.Connection = conn;
commd.CommandText = cmdText;
if (trans != null)
commd.Transaction = trans;
commd.CommandType = cmdType;
if (paras != null)
{
foreach (SqlParameter para in paras)
{
commd.Parameters.Add(para);
}
}
}
}
#endregion
/// <summary>
/// 操作同步数据标识
/// </summary>
public static class UserStatus
{
private static bool _isUsered = false;
/// <summary>
/// 是否正在上传文件
/// </summary>
public static bool IsUsered
{
get { return UserStatus._isUsered; }
set { UserStatus._isUsered = value; }
}
}
六、xml文件UpLoadFiles.xml
<?xml version="1.0" encoding="utf-8" ?>
<Files>
<!--收费导入信息-->
<UpLoadFile name="Charge" dbname="USST_Saraly">
<file name="tblCharge.txt" tabname="tblCharge"></file>
<file name="tblChargeMode.txt" tabname="tblChargeMode"></file>
<file name="tblChargeRecord.txt" tabname="tblChargeRecord"></file>
<file name="tblItemMain.txt" tabname="tblItemMain"></file>
<file name="tblItemSub.txt" tabname="tblItemSub"></file>
<file name="tblMItemType.txt" tabname="tblMItemType"></file>
<file name="tblReturnRecord.txt" tabname="tblReturnRecord"></file>
<file name="tblSet.txt" tabname="tblSet"></file>
<file name="tblStudent.txt" tabname="tblStudent"></file>
<file name="tblTerm.txt" tabname="tblTerm"></file>
</UpLoadFile>
<!--本专科生数据导入信息-->
<UpLoadFile name="UsstCharge" dbname="TuitionTest">
<file name="tblCharge.txt" tabname="tblCharge"></file>
<file name="tblChargeMode.txt" tabname="tblChargeMode"></file>
<file name="tblChargeRecord.txt" tabname="tblChargeRecord"></file>
<file name="tblItemMain.txt" tabname="tblItemMain"></file>
<file name="tblItemSub.txt" tabname="tblItemSub"></file>
<file name="tblMItemType.txt" tabname="tblMItemType"></file>
<file name="tblReturnRecord.txt" tabname="tblReturnRecord"></file>
<file name="tblSet.txt" tabname="tblSet"></file>
<file name="tblStudent.txt" tabname="tblStudent"></file>
<file name="tblTerm.txt" tabname="tblTerm"></file>
</UpLoadFile>
<!--夜大学数据导入信息-->
<UpLoadFile name="NightCharge" dbname="USST_Saraly">
<file name="tblCharge.txt" tabname="tblCharge"></file>
<file name="tblChargeMode.txt" tabname="tblChargeMode"></file>
<file name="tblChargeRecord.txt" tabname="tblChargeRecord"></file>
<file name="tblItemMain.txt" tabname="tblItemMain"></file>
<file name="tblItemSub.txt" tabname="tblItemSub"></file>
<file name="tblMItemType.txt" tabname="tblMItemType"></file>
<file name="tblReturnRecord.txt" tabname="tblReturnRecord"></file>
<file name="tblSet.txt" tabname="tblSet"></file>
<file name="tblStudent.txt" tabname="tblStudent"></file>
<file name="tblTerm.txt" tabname="tblTerm"></file>
</UpLoadFile>
<!--研究生数据导入信息-->
<UpLoadFile name="MasterCharge" dbname="USST_Saraly">
<file name="tblCharge.txt" tabname="tblCharge"></file>
<file name="tblChargeMode.txt" tabname="tblChargeMode"></file>
<file name="tblChargeRecord.txt" tabname="tblChargeRecord"></file>
<file name="tblItemMain.txt" tabname="tblItemMain"></file>
<file name="tblItemSub.txt" tabname="tblItemSub"></file>
<file name="tblMItemType.txt" tabname="tblMItemType"></file>
<file name="tblReturnRecord.txt" tabname="tblReturnRecord"></file>
<file name="tblSet.txt" tabname="tblSet"></file>
<file name="tblStudent.txt" tabname="tblStudent"></file>
<file name="tblTerm.txt" tabname="tblTerm"></file>
</UpLoadFile>
<!--经费导入信息-->
<UpLoadFile name="Outlay" dbname="USST_OUTLAY">
<file name="JF_BCtrl.txt" tabname="JF_BCtrl"></file>
<file name="JF_BCtrlPZ.txt" tabname="JF_BCtrlPZ"></file>
<file name="JF_BDetail.txt" tabname="JF_BDetail"></file>
<file name="JF_BMain.txt" tabname="JF_BMain"></file>
<file name="JF_DW.txt" tabname="JF_DW"></file>
<file name="JF_KM.txt" tabname="JF_KM"></file>
</UpLoadFile>
<!--工资导入信息-->
<UpLoadFile name="Salary" dbname="SalaryTest">
<file name="Employees.txt" tabname="UP_Employees"></file>
<file name="EmployeeType.txt" tabname="UP_EmployeeType"></file>
<file name="Departments.txt" tabname="UP_Departments"></file>
<file name="Items.txt" tabname="UP_Items"></file>
<file name="Type_Field.txt" tabname="UP_Type_Field"></file>
<file name="SystemObject.txt" tabname="UPSystemObject"></file>
</UpLoadFile>
</Files>
七、Web.config
<?xml version="1.0" encoding="utf-8"?>
<configuration>
<appSettings>
<add key="DBConnection" value="Data Source=Sttu;Persist Security Info=True;User ID=#stmdt2008#;Context Connection=False;Password=seaskyd;Initial Catalog=FinancePortal;MultipleActiveResultSets=True"/>
<add key="XSqlPath" value="/bin"/>
<add key="XSqlFile" value="XSqlSet.xml"/>
<add key="userKey" value="1234"/>
<add key="Expire" value="20"/>
</appSettings>
<connectionStrings/>
<system.web>
<httpRuntime executionTimeout="1000" maxRequestLength="102400"/>
<compilation debug="true" />
<authentication mode="Windows" />
<!--
<customErrors mode="RemoteOnly" defaultRedirect="GenericErrorPage.htm">
<error statusCode="403" redirect="NoAccess.htm" />
<error statusCode="404" redirect="FileNotFound.htm" />
</customErrors>
-->
</system.web>
</configuration>
八、XSqlCenter.cs
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Collections.Generic;
using System.Security.Cryptography;
using System.Text;
using System.Web;
using System.Xml;
using System.Xml.XPath;
using FinancePortal.Utilities;
using XSqlEngine.QueryManger;
using XSqlEngine.Request;
using XSqlEngine.Database.MSSql;
namespace FinancePortal
{
static class XSqlCenter
{
static XSqlCenter()
{
string path_ = HttpRuntime.AppDomainAppPath+ConfigurationManager.AppSettings["XSqlPath"];
string fileName_=ConfigurationManager.AppSettings["XSqlFile"];
using (FileStream file_ = File.Open(path_+"//"+fileName_, FileMode.Open))
{
s_xSqlManger = new XSqlManger(file_, path_);
}
string strConn_ = ConfigurationManager.AppSettings["DBConnection"];
s_dataAccess = new DataAccess(strConn_);
s_userKey = ConfigurationManager.AppSettings["userKey"];
double exp_;
if(!double.TryParse(ConfigurationManager.AppSettings["Expire"], out exp_))
throw new Exception("在配置文件中获取“Expire”失败。");
s_logExpire = TimeSpan.FromMinutes(exp_);
s_userCtrl=new UserControl();
s_funcCtrl=new FuncControl();
}
public static XSqlManger s_xSqlManger;
public static DataAccess s_dataAccess;
public static UserControl s_userCtrl;
public static FuncControl s_funcCtrl;
public static string s_userKey;
public static TimeSpan s_logExpire;
}
public class QueryEnvironment
{
public QueryEnvironment(HttpRequest request, XSqlManger manger)
{
m_reqData = new RequestData(request);
KeyValuePair<XSqlInfo, XDataTrans> fullQuery_
= manger.GetQueryInfoX(m_reqData.queryName);
m_xsqlInfo = fullQuery_.Key;
m_xDataTrans = fullQuery_.Value;
try
{
m_curUser = XSqlCenter.s_userCtrl.CheckUser(m_reqData.key);
}
catch
{
if (m_xsqlInfo.auth)
throw;
}
if(m_xsqlInfo.auth)
LoadEvnParam();
}
private void LoadEvnParam()
{
XSqlInfo xSql = XSqlCenter.s_xSqlManger.GetQueryInfo("UserEvnParam");
string evnData_=XSqlCenter.s_dataAccess.Execute(xSql, null, m_reqData, this.getEnvParam);
Helpers.LoadEvnPrams(m_evnParams, evnData_);
}
public string getEnvParam(string name)
{
try
{
string param_ = null;
if (name == ":xQuery")
param_ = m_xDataTrans.text;
else if (name == ":uid")
param_= m_curUser.id;
else if (m_curUser.evnParams != null
&& m_curUser.evnParams.ContainsKey(name))
{
param_ = m_curUser.evnParams[name];
}
else if (m_curUser.curFunc != null)
{
if (name == ":funcId")
param_ = m_curUser.curFunc.id.ToString();
else if (m_curUser.curFunc.evnParams != null
&& m_curUser.curFunc.evnParams.ContainsKey(name))
param_ = m_curUser.curFunc.evnParams[name];
else
param_ = m_evnParams[name];
}
else
param_= m_evnParams[name];
return param_;
}
catch
{
return null;
}
}
public XSqlInfo xSql
{
get { return m_xsqlInfo; }
}
public XDataTrans xDataTrans
{
get { return m_xDataTrans; }
}
public RequestData requestData
{
get { return m_reqData; }
}
private RequestData m_reqData;
private UserInfo m_curUser;
private XSqlInfo m_xsqlInfo;
private XDataTrans m_xDataTrans;
private Dictionary<string, string> m_evnParams=new Dictionary<string,string>();
}
public class UserInfo
{
public UserInfo(string name, string id, int baseGroup
, string pid, bool chgPsw, string extData)
{
m_name = name;
m_id = id;
m_key = Helpers.MakeMD5(name+id+XSqlCenter.s_userKey+m_logTime.ToString());
m_baseGroup = baseGroup;
m_chgPsw = chgPsw;
m_pid = pid;
Helpers.LoadEvnPrams(m_evnParams, extData);
}
public string name
{
get { return m_name; }
}
public string id
{
get { return m_id; }
}
public string key
{
get { return m_key; }
}
public int baseGroup
{
get { return m_baseGroup; }
}
public string pid
{
get { return m_pid; }
}
public bool chgPsw
{
get { return m_chgPsw; }
}
public Dictionary<string, string> evnParams
{
get { return m_evnParams; }
}
public FuncInfo curFunc
{
get { return m_curFunc; }
set { m_curFunc = value; }
}
public DateTime logTime
{
get { return m_logTime; }
}
private string m_name;
private string m_id;
private string m_key;
private int m_baseGroup;
private bool m_chgPsw;
private string m_pid;
private DateTime m_logTime=DateTime.Now;
private Dictionary<string, string> m_evnParams=new Dictionary<string,string>();
private FuncInfo m_curFunc;
}
public class FuncInfo
{
public FuncInfo(int id, string name, string text, string frame, string frameParam
, string srcSelector, string dstSelector, string extData)
{
m_funcId = id;
m_name = name;
m_text = text;
m_frame = frame;
m_frameParam = frameParam;
m_srcSelector = srcSelector;
m_dstSelector = dstSelector;
Helpers.LoadEvnPrams(m_evnParams, extData);
}
public int id
{
get { return m_funcId; }
}
public string name
{
get { return m_name; }
}
public string text
{
get { return m_text; }
}
public string frame
{
get { return m_frame; }
}
public string frameParam
{
get { return m_frameParam; }
}
public string srcSelector
{
get { return m_srcSelector; }
}
public string dstSelector
{
get { return m_dstSelector; }
}
public Dictionary<string, string> evnParams
{
get { return m_evnParams; }
}
private int m_funcId;
private string m_name;
private string m_text;
private string m_frame;
private string m_frameParam;
private string m_srcSelector;
private string m_dstSelector;
private Dictionary<string, string> m_evnParams = new Dictionary<string, string>();
}
public class UserControl
{
public UserControl()
{}
public UserInfo Login(string userId, string psw)
{
string sql_ = "select rtrim(UserId) as UserId, rtrim(UserName) as UserName, BaseGroup, rtrim(Pid) as Pid, PswChange, ExtData"
+" from Users"
+" where UserId=@uid and Password=@psw";
SqlParameter[] uparams_ = new SqlParameter[2];
uparams_[0] = new SqlParameter("@uid", userId);
uparams_[1] = new SqlParameter("@psw", psw);
SqlDataReader dbuser_ = XSqlCenter.s_dataAccess.Execute(sql_, uparams_);
if (!dbuser_.Read())
throw new Exception("用户 "+userId+" 不存在或密码错误");
string uid_ = dbuser_.GetString(1)
, name_ = dbuser_.GetString(0)
, pid_ = dbuser_.IsDBNull(3) ? "" : dbuser_.GetString(3)
, extData_ = dbuser_.IsDBNull(5) ? "" : dbuser_.GetString(5);
int baseGroup_ = dbuser_.GetInt32(2);
bool chgPsw_ = dbuser_.GetBoolean(4);
UserInfo user_ = new UserInfo(uid_, name_, baseGroup_
, pid_, chgPsw_, extData_);
string oldKey_=null;
foreach (UserInfo oldUser_ in m_Users.Values)
{
if (oldUser_.id == userId)
{
oldKey_ = oldUser_.key;
break;
}
}
if (!string.IsNullOrEmpty(oldKey_))
m_Users.Remove(oldKey_);
if (m_Users.ContainsKey(user_.key))
m_Users[user_.key] = user_;
else
m_Users.Add(user_.key, user_);
return user_;
}
public void Logout(string key)
{
if (m_Users.ContainsKey(key))
m_Users.Remove(key);
}
public UserInfo CheckUser(string key)
{
if (!m_Users.ContainsKey(key))
throw new Exception("101,用户未登录");
UserInfo user_ = m_Users[key];
if (user_.logTime + XSqlCenter.s_logExpire < DateTime.Now)
{
m_Users.Remove(key);
throw new Exception("101,用户登录已经超时");
}
return user_;
}
public void ChangeMyPassword(string key, string oldPsw, string newPsw)
{
if (string.IsNullOrEmpty(oldPsw))
throw new Exception("旧密码无效。");
if (string.IsNullOrEmpty(newPsw))
throw new Exception("新密码无效。");
UserInfo user_=this.CheckUser(key);
string uid_ = user_.id;
string chkusr_ = "select count(*) from Users where UserId=@uid and Password=@psw";
string setpsw_ = "update Users set Password=@newPsw "
+ "where UserId=@uid and Password=@psw";
SqlParameter[] uparams_ = new SqlParameter[2];
SqlParameter[] cparams_ = new SqlParameter[3];
cparams_[0] = new SqlParameter("@uid", uid_);
cparams_[1] = new SqlParameter("@psw", oldPsw);
cparams_[2] = new SqlParameter("@newPsw", newPsw);
uparams_[0] = new SqlParameter("@uid", uid_);
uparams_[1] = new SqlParameter("@psw", oldPsw);
using (SqlDataReader dbuser_ = XSqlCenter.s_dataAccess.Execute(chkusr_, uparams_))
{
if (!dbuser_.Read())
throw new Exception("旧密码不匹配。");
int cu_ = dbuser_.GetInt32(0);
if(cu_<1)
throw new Exception("旧密码不匹配。");
}
XSqlCenter.s_dataAccess.Execute(setpsw_, cparams_);
}
public void ChangeUserPassword(string key, string uid, string newPsw)
{
if (string.IsNullOrEmpty(uid))
throw new Exception("用户名无效。");
if (string.IsNullOrEmpty(newPsw))
throw new Exception("新密码无效。");
UserInfo user_ = this.CheckUser(key);
if (user_.baseGroup != 1)
throw new Exception("你不是管理员,无权修改用户密码。");
string setpsw_ = "update Users set Password=@newPsw "
+ "where UserId=@uid";
SqlParameter[] uparams_ = new SqlParameter[2];
uparams_[0] = new SqlParameter("@uid", uid);
uparams_[1] = new SqlParameter("@newPsw", newPsw);
XSqlCenter.s_dataAccess.Execute(setpsw_, uparams_);
}
private Dictionary<string, UserInfo> m_Users=new Dictionary<string,UserInfo>();
}
public class FuncControl
{
public FuncControl()
{
string sql_ = "select FuncId, rtrim(FuncName) as FuncName, rtrim(text) as text, rtrim(Frame) as Frame, rtrim(FrameParam) as FrameParam"
+ ", rtrim(srcSelector) as srcSelector, rtrim(dstSelector) as dstSelector, ExtData"
+ " from Functions";
SqlDataReader dbFuncs_ = XSqlCenter.s_dataAccess.Execute(sql_, null);
while (dbFuncs_.Read())
{
int id_ = dbFuncs_.GetInt32(0);
string name_=dbFuncs_.GetString(1)
, text_=dbFuncs_.GetString(2)
, frame_=dbFuncs_.GetString(3)
, param_=dbFuncs_.IsDBNull(4)? "": dbFuncs_.GetString(4)
, srcSel_ = dbFuncs_.IsDBNull(5) ? "" : dbFuncs_.GetString(5)
, dstSel_ = dbFuncs_.IsDBNull(6) ? "" : dbFuncs_.GetString(6)
, extData_ = dbFuncs_.IsDBNull(7) ? "" : dbFuncs_.GetString(7);
FuncInfo func_ = new FuncInfo(id_, name_, text_
, frame_, param_, srcSel_
, dstSel_, extData_);
m_Funcs.Add(func_.name, func_);
}
}
public FuncInfo getFunc(string name)
{
if (!m_Funcs.ContainsKey(name))
throw new Exception("功能 "+name+" 不存在.");
return m_Funcs[name];
}
private Dictionary<string, FuncInfo> m_Funcs = new Dictionary<string, FuncInfo>();
}
}
九、XSqlSet.xml
<?xml version="1.0" encoding="utf-8" ?>
<XSqlSet>
<!--Main queries, user m-->
<!--File upload queries created and edited by wq-->
<!--上传缴费记录信息-->
<XSql name="ChargePanel">
<Text>
select(select
(SELECT rtrim(Name) as "@term",convert(varchar(20),InitDate,120) as "@createdate"
FROM {dbname}tblTerm ORDER BY ObjectID for xml path('term'),type) as "termSet",
(SELECT TOP 1 convert(varchar(20),ImportTime,120) as "@importtime",rtrim(UserId) as "@userid" FROM {dbname}SF_ImportLog ORDER BY objId DESC
for xml path('user'),type) as "LogSet"
for xml path('charge'), type).query('{:xQuery}')
</Text>
<DataTrans name="ChargeTerm" type="xquery">
<div>
<span>现有收费数据列表:上传时间:{data(/charge/LogSet/user/@importtime)} 上传用户:{data(/charge/LogSet/user/@userid)}</span>
<table id="tbCharge">
<tr>
<td name="term">学期</td>
<td name="createdate">创建时间</td>
</tr>
{
for $term in /charge/termSet/term
return
<tr>
<td name="term">{data($term/@term)}</td>
<td name="createdate">{data($term/@createdate)}</td>
</tr>
}
</table>
</div>
</DataTrans>
</XSql>
<!--删除缴费记录-->
<XSql name="DeleteChange">
<Text>
truncate table {dbname}tblCharge
truncate table {dbname}tblChargeMode
truncate table {dbname}tblChargeRecord
truncate table {dbname}tblItemMain
truncate table {dbname}tblItemSub
truncate table {dbname}tblMItemType
truncate table {dbname}tblReturnRecord
truncate table {dbname}tblSet
truncate table {dbname}tblStudent
truncate table {dbname}tblTerm
truncate table {dbname}SF_ImportLog
truncate table {dbname}SF_Password
select '删除成功!'
</Text>
</XSql>
<!--上传工资-->
<XSql name="LoadSalary">
<Text>
select(select rtrim(TimeID) as "@TimeID",rtrim(GroupID) as "@groupID",
(rtrim(ATypeName)+','+rtrim(BTypeName)) as "@account",rtrim(Note) as "@note",
convert(varchar(20),ImportTime,120) as "@importtiem",rtrim(UserID) as "@userId"
FROM {dname}GZ_ImportLog ORDER BY TimeId DESC,GroupID DESC
for xml path('saraly'),type,root('saralys')).query('{:xQuery}')
</Text>
<DataTrans name="SalaryDetail" type="xquery">
<div>
<span>现有工资数据列表:</span>
<table id="tbSalary">
<tr id="tbTitle">
<td>数据时间</td>
<td>批次</td>
<td>账套</td>
<td>描述</td>
<td>上传时间</td>
<td>上传用户</td>
</tr>
{
for $saraly in /saralys/saraly
return
<tr>
<td name="TimeID">{data($saraly/@TimeID)}</td>
<td name="groupID">{data($saraly/@groupID)}</td>
<td name="account">{data($saraly/@account)}</td>
<td name="note">{data($saraly/@note)}</td>
<td name="importtiem">{data($saraly/@importtiem)}</td>
<td name="userId">{data($saraly/@userId)}</td>
</tr>
}
</table>
</div>
</DataTrans>
</XSql>
<!--删除工资信息-->
<XSql name="DeleteSalarys">
<Text>
DELETE FROM {dname}GZ_Employees WHERE TimeId='{timeId}' AND GroupID={groupId}
DELETE FROM {dname}GZ_EmployeeType WHERE TimeId='{timeId}' AND GroupID={groupId}
DELETE FROM {dname}GZ_Items WHERE TimeId='{timeId}' AND GroupID={groupId}
DELETE FROM {dname}GZ_Type_Field WHERE TimeId='{timeId}' AND GroupID={groupId}
DELETE FROM {dname}GZ_ImportLog WHERE TimeId='{timeId}' AND GroupID={groupId}
DELETE t1 FROM {dname}GZ_Password t1 LEFT JOIN {dname}GZ_Employees t2 ON t1.Employeeid=t2.EmployeeId WHERE t2.employeeid is null
select '删除成功!'
</Text>
</XSql>
</XSqlSet>