今天我想用xmlHttp来实现从数据库中读取一张表,然后显示到网页上。我首先会用三层架构来读取数据库中的数据,然后通过xml把数据传递该JavaScript的xmlHttp,在把数据显示到页面上。好,我们马上开始。
首先是建立数据库
CREATE TABLE [dbo].[MOA_Department](
CREATE TABLE [dbo].[MOA_Department](
[departmentId] [int] IDENTITY(1,1) NOT NULL,
[departmentName] [nvarchar](50) NOT NULL,
[parentDepartmentId] [int] NOT NULL,
CONSTRAINT [PK_MOA_Department] PRIMARY KEY CLUSTERED
(
[departmentId] ASC
)
)
还是我上一篇的数据库,具体不多说了,有疑问请参考我的上一篇“无线树”。
建立DB层
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace DB
{
public class SqlHelper
{
private static string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString.ToString();
private static void pareComm(SqlConnection conn, SqlCommand comm, SqlParameter[] par, string sqlStr, CommandType type)
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
comm.Connection = conn;
comm.CommandType = type;
comm.CommandText = sqlStr;
if (par != null)
{
foreach(SqlParameter p in par)
{
comm.Parameters.Add(p);
}
}
}
public static SqlDataReader GetReader(string sqlStr, SqlParameter[] par)
{
SqlConnection conn = new SqlConnection(connStr);
SqlCommand comm = new SqlCommand();
pareComm(conn, comm, par, sqlStr, CommandType.Text);
return comm.ExecuteReader(CommandBehavior.CloseConnection);
}
}
}
建立Model层
using System;
using System.Collections.Generic;
using System.Text;
namespace Model
{
public class Department
{
private int departmentId;
public int DepartmentId
{
get { return departmentId; }
set { departmentId = value; }
}
private string departmentName;
public string DepartmentName
{
get { return departmentName; }
set { departmentName = value; }
}
private int parentDepartmentId;
public int ParentDepartmentId
{
get { return parentDepartmentId; }
set { parentDepartmentId = value; }
}
}
}
Model层的字段是跟数据表的字段一一对应的。
建立DAl数据访问层
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Model;
using DB;
namespace DAL
{
public class DepartmentDal
{
public List<Department> GetAllDepartment()
{
string sqlStr = "select departmentId,departmentName,parentDepartmentId from MOA_Department";
List<Department> itemList = new List<Department>();
SqlDataReader dr = SqlHelper.GetReader(sqlStr, null);
while (dr.Read())
{
Department item = new Department();
if (dr["departmentId"].ToString() != "")
{
item.DepartmentId = Convert.ToInt32(dr["departmentId"].ToString());
}
if (dr["departmentName"] != null && dr["departmentName"].ToString() != string.Empty)
{
item.DepartmentName = dr["departmentName"].ToString().Trim();
}
if (dr["parentDepartmentId"].ToString() != "")
{
item.ParentDepartmentId = Convert.ToInt32(dr["parentDepartmentId"].ToString());
}
itemList.Add(item);
}
if (itemList.Count == 0)
{
dr.Close();
return null;
}
else
{
dr.Close();
return itemList;
}
}
}
}
这里主要是获取数据表MOA_Department中的所有数据,存储在自定义实体类中,然后传递到BLL层中。
建立BLL层
using System;
using System.Collections.Generic;
using System.Text;
using Model;
using DAL;
namespace BLL
{
public class DepartmentBll
{
public List<Department> GetAllDepartment()
{
DepartmentDal manager = new DepartmentDal();
return manager.GetAllDepartment(); // 从DAL层获取数据
}
}
}
建立界面层,分别有两个网页和一个js文件。
首先建立一个从BLL层获取数据的页面,命名为:GetAllDepartment.aspx。页面是空白的,在页面的code-behind中编写一下代码:
using System;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Collections.Generic;
using BLL;
using Model;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
postAllDepartment();
}
}
private void postAllDepartment()
{
DepartmentBll manager = new DepartmentBll();
List<Department> itemList = new List<Department>();
itemList = manager.GetAllDepartment(); // 从BLL层获取数据
string responseText = ""; //初始化要传递的字符串
responseText += "<response>";
responseText += "<count>";
responseText += itemList.Count;
responseText += "</count>";
foreach (Department item in itemList)
{
responseText += "<item>";
responseText += "<departmentId>" + item.DepartmentId + "</departmentId>";
responseText += "<departmentName>" + item.DepartmentName + "</departmentName>";
responseText += "<parentDepartmentId>" + item.ParentDepartmentId + "</parentDepartmentId>";
responseText += "</item>";
}
responseText += "</response>";
Response.Clear(); // 清空response中的值
Response.Expires = 0; // 不使用缓存
Response.ContentType = "text/xml"; // 声明传递的是xml
Response.Write(responseText); // 传递xml
Response.End(); //结束传递
}
}
建立JavaScript文件,新建目录js,在目录js下建立JavaScript文件JScript.js,输入以下代码:
var xmlHttp1;
function createXmlHttpRequest() // 新建xmlHttpRequest,根据浏览器来新建xmlHttpRequest
{
if(window.ActiveXObject)
{
xmlHttp1 = new ActiveXObject("Microsoft.XMLHTTP");
}
else if(window.XMLHttpRequest)
{
xmlHttp1 = new XMLHttpRequest();
}
}
function getAllDepartment()
{
createXmlHttpRequest();
var url = "GetAllDepartment.aspx"; // 初始化从哪个网页上获取数据
xmlHttp1.open("GET",url,true) // 打开连接,“GET”代表用什么方式来传递数据,这里使用get方式;ur是指从哪个网页上传递数据;true代表使用异步方式来传递数据。
xmlHttp1.onreadystatechange = processRequest; //回调函数
xmlHttp1.send(null); //发送null
}
function processRequest() //实现回调函数
{
if(xmlHttp1.readyState == 4)
{
if(xmlHttp1.status == 200) //当回调成功及状态为200时输出数据
{
var result = xmlHttp1.responseXML.getElementsByTagName("response")[0].getElementsByTagName("item"); //获取xml节点的值
var count = xmlHttp1.responseXML.getElementsByTagName("response")[0].getElementsByTagName("count");
var divShow = document.getElementById("showDepartment"); //获取网页中要输出到哪个标签中
divShow.innerHTML = ""; //清空标签中的值
var table = document.createElement("table"); //创建一个table标签
for(var i=0; i<count[0].firstChild.nodeValue; i++)
{
var tr = table.insertRow(); // 在表中插入一行
var td1 = tr.insertCell(); // 在行中插入一列
var td2 = tr.insertCell();
var td3 = tr.insertCell();
td1.innerHTML = result[i].getElementsByTagName("departmentId")[0].firstChild.nodeValue; // 在列中插入数据
td2.innerHTML = result[i].getElementsByTagName("departmentName")[0].firstChild.nodeValue;
td3.innerHTML = result[i].getElementsByTagName("parentDepartmentId")[0].firstChild.nodeValue;
}
divShow.appendChild(table); // 把表添加到标签中
}
}
}
getAllDepartment();
建立显示数据的页面ShowAllDepartment.aspx,前台页面代码:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="ShowAllDepartment.aspx.cs" Inherits="ShowAllDepartment" %>
<!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 runat="server">
<title>无标题页</title>
</head>
<body>
<form id="form1" runat="server">
<div id="showDepartment">
</div>
</form>
</body>
</html>
<script type="text/javascript" language="javascript" src="js/JScript.js">
</script>
编译运行,浏览ShowAllDepartment.aspx,则可以看到数据表显示在网页上