asp.net 查询sql数据表的网页模板
最近因为工作需求，要制作一个网页模板，主要是用于快速开发，可以查询Sql数据表信息的模板，
昨天做好了，这个只是一个Demo，但是功能已经齐全了，
开发新的网站时，需要新增一个xml，复制粘贴网页的前端和后台，注意，前端的样式和逻辑还是要分开写的。
这样，基本上在10分钟以内，就能开发一个新的网页（只有核心的查询功能）。
这是效果图：
下面记录一下这个Demo：
首先是要在Web项目的web.config中配置数据库的连接字符串和xml文件名：
1 <appSettings>
2 <add key="xmlDir" value="xml"/>
3 appSettings>
4 <connectionStrings>
5 <add name="connStr" connectionString="Data Source=10.76.25.XX;DATABASE=XXX;Persist Security Info=True;User ID=sa;Password=XXXXXX;Max Pool Size=300;" providerName="System.Data.SqlClient"/>
6 connectionStrings>
接着，我有做好了一个帮助类QueryHelper：
1 using System;
2 using System.Collections.Generic;
3 using System.Configuration;
4 using System.Data;
5 using System.Data.SqlClient;
6 using System.IO;
7 using System.Web.UI;
8 using System.Web.UI.HtmlControls;
9 using System.Web.UI.WebControls;
10 using System.Xml.Linq;
11
12 namespace Cong.Utility
13 {
14 public delegate void DelegatePageIndexChange(object obj, GridViewPageEventArgs e);
15
16 public class QueryHelper
17 {
18 private string xmlName { get; set; }
19 private Page page { get; set; }
20 private GridView gv { get; set; }
21
22 private DelegatePageIndexChange dele;
23
24 public QueryHelper(string xmlName, Page page, DelegatePageIndexChange dele)
25 {
26 this.xmlName = xmlName;
27 this.page = page;
28 this.gv = page.FindControl("gv") as GridView;
29 this.dele = dele;
30
31 gv.PageIndexChanging += Gv_PageIndexChanging;
32 }
33
34 public void BindData(object objIndex)
35 {
36 var xmlDir = ConfigurationManager.AppSettings["xmlDir"];
37 //获取xml节点
38 string path = AppDomain.CurrentDomain.BaseDirectory + (xmlDir == null ? "" : (xmlDir.ToString() + "\\")) + xmlName;
39 if (!File.Exists(path))
40 throw new Exception("xml is not exist.");
41
42 XDocument xdoc = XDocument.Load(path);
43 XElement root = xdoc.Root;
44
45 //获取基本查询语句的变量
46 string tableName = root.Element("tableName").Value;
47 string topNumber = root.Element("top").Value;
48
49 //拼接where条件语句
50 List<string> conditions = new List<string>();
51 List ps = new List();
52 foreach (var ele in root.Element("field").Elements())
53 {
54 if (ele.Element("search").Value.Equals("0"))
55 {
56 HtmlInputText tb = page.FindControl("search_" + ele.Name.ToString()) as HtmlInputText;
57 if (tb == null)
58 continue;
59
60 if (!tb.Value.Trim().Equals(""))
61 {
62 conditions.Add(string.Format("{0} like '%'+@{0}+'%'", ele.Name.ToString()));
63 ps.Add(new SqlParameter("@" + ele.Name.ToString(), tb.Value.Trim()));
64 }
65 }
66 }
67 //是否有删除标识
68 foreach (var ele in root.Element("flag").Elements())
69 {
70 if (ele.Value.Equals("0"))
71 conditions.Add(ele.Name + " " + ele.Value);
72 }
73 string strWhere = "";
74 if (conditions.Count > 0)
75 strWhere = "where " + string.Join(" and ", conditions.ToArray());
76
77 //拼接sort条件语句
78 List<string> sorts = new List<string>();
79 foreach (var ele in root.Element("sorts").Elements())
80 {
81 sorts.Add(ele.Element("field").Value + " " + ele.Element("type").Value);
82 }
83 string strOrderBy = "";
84 if (sorts.Count > 0)
85 strOrderBy = "order by " + string.Join(",", sorts.ToArray());
86
87 //拼接sql语句
88 string sql = string.Format("select top {0} * from {1} {2} {3}", topNumber, tableName, strWhere, strOrderBy);
89 DataTable dt = SqlHelper.ExecuteDataTable(sql, ps);
90
91 BindGV(dt, root, objIndex);
92 }
93
94 public void BindGV(DataTable dt, XElement root, object objIndex)
95 {
96 gv.DataSource = null;
97 gv.Columns.Clear();
98 gv.AutoGenerateColumns = false;
99 gv.AllowPaging = true;
100 gv.PageSize = int.Parse(root.Element("pageSize").Value);
101
102 BoundField gvColumn = null;
103 foreach (var ele in root.Element("field").Elements())
104 {
105 if (ele.Element("visible").Value.Equals("0"))
106 {
107 gvColumn = new BoundField();
108 gvColumn.HeaderText = ele.Element("cn").Value;
109 gvColumn.DataField = ele.Name.ToString();
110 gv.Columns.Add(gvColumn);
111 gvColumn = null;
112 }
113 }
114
115 gv.DataSource = dt;
116 gv.PageIndex = objIndex == null ? 0 : Convert.ToInt32(objIndex);
117 gv.DataBind();
118 }
119
120 private void Gv_PageIndexChanging(object sender, GridViewPageEventArgs e)
121 {
122 dele(sender, e);
123 BindData(e.NewPageIndex);
124 }
125 }
126 }
然后，是SqlHelper，这个就不放出来了，以前也发过。
接着创建一个xml，我是放在项目主目录下的“xml”文件夹内，内容如下：
1 <?xml version="1.0" encoding="utf-8" ?>
2 <root>
3 <tableName>V_121_LOT_LISTtableName>
4 <top>100top>
5 <field>
6 <lot_id>
7 <cn>Idcn>
8 <visible>0visible>
9 <search>0search>
10 lot_id>
11 <lot_code>
12 <cn>批号cn>
13 <visible>0visible>
14 <search>0search>
15 lot_code>
16 <plan_qty>
17 <cn>生产数量cn>
18 <visible>1visible>
19 <search>1search>
20 plan_qty>
21 <now_qty>
22 <cn>当前数量cn>
23 <visible>0visible>
24 <search>1search>
25 now_qty>
26 field>
27 <flag>
28 <state_flg>1state_flg>
29 <del_flg>1del_flg>
30 flag>
31 <sorts>
32 <sort>
33 <field>lot_idfield>
34 <type>desctype>
35 sort>
36 sorts>
37 <pageSize>20pageSize>
38 root>
最后，创建一个WebForm.aspx，
下面分别是后台和前端的代码：
1 using Cong.Utility;
2 using System;
3 using System.Web.UI.WebControls;
4
5 namespace QueryTemplet
6 {
7 public partial class lot : System.Web.UI.Page
8 {
9 private static string xmlName = "lot.xml";
10
11 protected QueryHelper qh = null;
12
13 protected void Page_Load(object sender, EventArgs e)
14 {
15 qh = new QueryHelper(xmlName, this, Gv_PageIndexChanging);
16 qh.BindData(ViewState["pageIndex"]);
17 }
18
19 private void Gv_PageIndexChanging(object sender, GridViewPageEventArgs e)
20 {
21 ViewState["pageIndex"] = e.NewPageIndex;
22 }
23
24 protected void btnSearch_Click(object sender, EventArgs e)
25 {
26 qh.BindData(ViewState["pageIndex"]);
27 }
28 }
29 }
1 <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="lot.aspx.cs" Inherits="QueryTemplet.lot" %> 2 3 4 5 "http://www.w3.org/1999/xhtml"> 6 "server"> 7 "Content-Type" content="text/html; charset=utf-8" /> 823 249 10 11
前端的代码，自己再套上样式和编写js代码就可以使用了。
最后，也放出另一个效果图，我做好这个新的页面，只复制粘贴了几分钟。
就这些，欢迎交流。
源码下载：http://download.csdn.net/detail/cycong108/9739562