asp.net mysql网站模板_asp.net 查询sql数据表的网页模板

1 usingSystem;2 usingSystem.Collections.Generic;3 usingSystem.Configuration;4 usingSystem.Data;5 usingSystem.Data.SqlClient;6 usingSystem.IO;7 usingSystem.Web.UI;8 usingSystem.Web.UI.HtmlControls;9 usingSystem.Web.UI.WebControls;10 usingSystem.Xml.Linq;11

12 namespaceCong.Utility13 {14 public delegate void DelegatePageIndexChange(objectobj, GridViewPageEventArgs e);15

16 public classQueryHelper17 {18 private string xmlName { get; set; }19 private Page page { get; set; }20 private GridView gv { get; set; }21

22 privateDelegatePageIndexChange dele;23

24 public QueryHelper(stringxmlName, Page page, DelegatePageIndexChange dele)25 {26 this.xmlName =xmlName;27 this.page =page;28 this.gv = page.FindControl("gv") asGridView;29 this.dele =dele;30

31 gv.PageIndexChanging +=Gv_PageIndexChanging;32 }33

34 public void BindData(objectobjIndex)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 conditions = new List();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()) asHtmlInputText;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 sorts = new List();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, objectobjIndex)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 = newBoundField();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(objectsender, GridViewPageEventArgs e)121 {122 dele(sender, e);123 BindData(e.NewPageIndex);124 }125 }126 }