简单介绍:
本示例主要用EXT2.2+vs.2008.net+C#+sql Server 2005解决GridPanel动态生成数据列,以减免在Html页面里静态配置数据列所带来的麻烦.为了大家更好的更方便的使用本示例,就附加了一个功能:只要添加本地的数据库连接,选择数据库里面的表,就可以对代码进行测试. 另外，为了方便2005.net的朋友们，本例是在asp.net2.0下编译的。
源代码下载
先看看效果图吧
首先需要输入所在服务器下的数据库名称 确定后 会在下面出现Combox列表，然后在列表里选择表名称 确定后即可显示
但有点需要注意 开始时尽量选择那些有ID字段的表 因为 在后台分页页面设置了一个初始的ID 这个ID可以根据需要自己定义或者更改
下面就是在选择正确的表名 确定后出现的效果图
Ext实现的Html源代码
Code
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="MakeGrid._Default" %>
<!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 id="Head1" runat="server">
<title>动态生成GridPanel</title>
<link rel="Stylesheet" type="text/css" href="ExtJS/resources/css/ext-all.css" />
<link rel="Stylesheet" type="text/css" href="ExtJS/resources/css/xtheme-green.css" />
<script type="text/javascript" src="ExtJS/adapter/ext/ext-base.js"></script>
<script type="text/javascript" src="ExtJS/ext-all.js"></script>
<script type="text/javascript" src="ExtJS/ext-lang-zh_CN.js"></script>
</head>
<body>
<form id="form1" runat="server">
<div style="text-align:left;">
<div id="panel_id"></div>
<script type="text/javascript">
var table_Name;
var panel;
var data;
var grid;
var combobox;
var URLSTR;
function DataColumn()
{
this.fields = '';
this.columns = '';
this.addColumns=function(name,caption)
{
if(this.fields.length > 0)
{
this.fields += ',';
}
if(this.columns.length > 0)
{
this.columns += ',';
}
this.fields += '{name:"' + name + '"}';
this.columns += '{header:"' + caption + '",dataIndex:"' + name + '",width:100,sortable:true}';
};
}
function DataGrid(URL)
{
var cm = new Ext.grid.ColumnModel(eval('([' + data.columns + '])'));
cm.defaultSortable = true;
var fields = eval('([' + data.fields + '])');
var newStore = new Ext.data.Store
({
proxy:new Ext.data.HttpProxy({url:URL}),
reader:new Ext.data.JsonReader({totalProperty:"totalPorperty",root:"result",fields:fields})
});
newStore.load({params:{start:0,limit:9}});
var pagingBar = new Ext.PagingToolbar
({
displayInfo:true,
emptyMsg:"没有数据显示",
displayMsg:"显示从{0}条数据到{1}条数据，共{2}条数据",
store:newStore,
pageSize:9
});
this.gridPanel = new Ext.grid.GridPanel
({
cm:cm,
id:"grid_panel",
renderTo:"grid_div",
store:newStore,
frame:false,
border:true,
layout:"fit",
pageSize:16,
width:600,
//autoWidth:true,
height:400,
viewConfig:{forceFit:true},
bbar:pagingBar
});
}
function MakePanel()
{
this.panel_def = new Ext.Panel
({
layout:"fit",
border:true,
frame:true,
title:"数据浏览（请在下面输入数据库连接串：）",
width:660,
height:500,
id:"Viewport_ID",
renderTo:"panel_id",
tbar:
[
'',
'服务器名：',{xtype:'field',id:'dbServerID',width:100,value:"localhost"},
'',
'用户名：',{xtype:'field',id:'dbUIDID',width:50,value:"sa"},
'',
'密码：',{xtype:'field',id:'dbPasswordID',width:50,value:"0"},
'',
'输入数据库名称：',{xtype:'field',id:'dbNameID',width:100,value:""},
'',
{text:'确定',pressed:true,handler:event_click_enter,tooltip:"查看数据库连接是否正确"}
],
html: '<div id="grid_div"></div>',
bbar:
[
'请选择表：',
'<div id="hello"></div>',
'',
{text:"确定",handler:event_select_table,id:"SelectTableID",disabled:false}
]
});
}
function event_select_table()
{
if(!Ext.get("ComboBox_ID"))
{
Ext.Msg.alert("警告消息","请先选择数据库!");return;
}
var tableName = Ext.util.Format.trim(Ext.get("ComboBox_ID").getValue());
if(tableName==""||tableName=="请选择表名")
{
Ext.Msg.alert("警告消息","请选择表名!");return;
}
if(!table_Name)
{
table_Name = tableName;
}
else if(table_Name==tableName)
{
return;
}
else
{
if(grid.gridPanel)
{
grid.gridPanel.destroy();
data.fields = '';
data.columns = '';
}
data.fields = '';
data.columns = '';
table_Name = tableName;
}
data = new DataColumn();
Ext.Ajax.request
({
url:"JsonData.aspx?param=initDataColumnName&tableName="+tableName,
success:function(response,option)
{
if(response.responseText=="")
{
Ext.Msg.alert("提示消息","当前所选中的表-->"+tableName+"<--可能没有数据!");return;
}
var res = Ext.util.JSON.decode(response.responseText);
for(var i=0;i<res.length;i++)
{
for(var p in res[i])
{
data.addColumns(p,p);
}
}
grid = new DataGrid("JsonData.aspx?param=initData&tableName="+tableName);
},
failure:function()
{
Ext.Msg.alert("消息","查询出错---->请打开数据库查看数据表名字是否正确");
}
});
}
function event_click_enter()
{
var dbServer = Ext.get("dbServerID").getValue();
var dbUID = Ext.get("dbUIDID").getValue();
var dbPassword = Ext.get("dbPasswordID").getValue();
var dbName = Ext.get("dbNameID").getValue();
var format = Ext.util.Format;
if(format.trim(dbServer)=="")
{
Ext.Msg.alert('警告消息','服务器名称不能够为空');return;
}
if(format.trim(dbUID)=="")
{
Ext.Msg.alert('警告消息','用户登陆ID不能够为空');return;
}
if(format.trim(dbName)=="")
{
Ext.Msg.alert('警告消息','数据库名称不能够为空');return;
}
var urlStr = "JsonData.aspx?param=initValidateDB"+
"&dbServerID="+dbServer+
"&dbUIDID="+dbUID+
"&dbPasswordID="+dbPassword+
"&dbNameID="+dbName;
if(!combobox)
{
combobox = new MakeComboBox(urlStr); //addField
}
else
{
if(URLSTR!=urlStr)
{
combobox.comboBox.destroy();
combobox = new MakeComboBox(urlStr);
}
}
URLSTR = urlStr;
Ext.Msg.alert('提示消息','现在请选择表名');
}
function MakeComboBox(URL)
{
var store = new Ext.data.Store
({
proxy: new Ext.data.HttpProxy({url:URL}), // 数据源
reader: new Ext.data.JsonReader({},[{name: 'TableName'}])// 如何解析
});
store.load();
this.comboBox = new Ext.form.ComboBox
({
id:"ComboBox_ID",
renderTo:"hello",
editable:false,
store:store,
emptyText:'请选择表名',
typeAhead: true,
triggerAction: 'all',
valueField:'TableName',
displayField: 'TableName',
selectOnFocus:true,
width:200,
resizable:true
});
}
function loader()
{
Ext.QuickTips.init();
MakePanel();
}
Ext.onReady(loader);
</script>
</div>
</form>
</body>
</html>
处理Ext请求的JsonData.aspx页面的源代码
这里请朋友们务必注意，这个处理Ext请求的JsonData.aspx页面的源代码 只有一行 其余的务必务必删除掉，
否则将作为Json数据一起返回 那就错了
Code
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="JsonData.aspx.cs" Inherits="MakeGrid.JsonData" %>
处理Ext请求的JsonData.aspx页面的后台源代码
Code
using System;
using System.Collections;
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 Newtonsoft.Json;
namespace MakeGrid
{
public partial class JsonData : System.Web.UI.Page
{
//初始化连接字符串
string dbDtr = "server={0};database={1};uid={2};pwd={3}";
protected void Page_Load(object sender, EventArgs e)
{
#region 分页参数
int pagesize = 5;
int start = 1;
string field, asc_desc;
if (string.IsNullOrEmpty(Request["sort"]))
{
field = "ID";//务必注意 你选择的表中必须有个字段是ID,如果没有ID，你要做动态的调整
asc_desc = "ASC";
}
else
{
field = Request["sort"];
asc_desc = Request["dir"];
}
if (!string.IsNullOrEmpty(Request["limit"]))
{
pagesize = int.Parse(Request["limit"]);
start = int.Parse(Request["start"]);
}
start = start / pagesize;
start += 1;
#endregion
string param = Convert.ToString(Request["param"]);
if (param != null && param != "")
{
switch (param)
{
case "initValidateDB":
InitValidateDB();
break;
case "initDataColumnName":
InitDataColumnName();
break;
case "initData":
InitData(field, asc_desc, pagesize, start);
break;
default:
break;
}
}
}
//绑定数据
void InitData(string field, string asc_desc, int pagesize, int start)
{
string tableName = Convert.ToString(Request["tableName"]);
Bind_Data(field, asc_desc, pagesize, start, tableName);
}
//绑定数据列
void InitDataColumnName()
{
string tableName = Convert.ToString(Request["tableName"]);
GetDataColumnName(tableName);
}
//验证数据库连接是否正确，如果正确还需要返回该数据库连接下的所有的表的名称
void InitValidateDB()
{
//从前台获取连接字符串
string dbServerID = Convert.ToString(Request["dbServerID"]);
string dbUIDID = Convert.ToString(Request["dbUIDID"]);
string dbPasswordID = Convert.ToString(Request["dbPasswordID"]);
string dbNameID = Convert.ToString(Request["dbNameID"]);
//拼装数据库连接字符串
string dbs = String.Format(dbDtr, dbServerID, dbNameID, dbUIDID, dbPasswordID);
Session["DBS"] = dbs;
Access.connstring = Convert.ToString(Session["DBS"]);
//获取所选择的数据库下面的所有数据表
string sql = String.Format("select [Name] from {0}..sysobjects WHERE [type] IN (N'U')", dbNameID);
DataSet ds = Access.GetDataSet(sql);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
List<Hashtable> testList = new List<Hashtable>();
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
Hashtable ht = new Hashtable();
DataRow row = ds.Tables[0].Rows[i] as DataRow;
ht["TableName"] = Convert.ToString(row["Name"]);
testList.Add(ht);
}
//生成Json字符串
string json = JavaScriptConvert.SerializeObject(testList);
Response.Write(json);
}
}
/// <summary>
/// 获取当前表的所有列名
/// </summary>
/// <param name="tableName"></param>
private void GetDataColumnName(string tableName)
{
GetConnString();
DataSet ds = Access.GetDataSet(String.Format("select top 1 * from {0}", tableName));
//只要ds不为null，则不管该表是否有数据，都有数据列生成
if (ds != null)
{
List<Hashtable> htList = new List<Hashtable>();
foreach (DataColumn col in ds.Tables[0].Columns)
{
Hashtable ht = new Hashtable();
ht.Add(col.ColumnName, col.ColumnName);
htList.Add(ht);
}
try
{
string json = JavaScriptConvert.SerializeObject(htList);
Response.Write(json);
}
catch (Exception ee)
{
string error = ee.Message;
}
}
else
{
Response.Write("");
}
}
/// <summary>
/// 获取数据集合
/// </summary>
/// <param name="field"></param>
/// <param name="asc_desc"></param>
/// <param name="pagesize"></param>
/// <param name="start"></param>
/// <param name="tableName"></param>
private void Bind_Data(string field, string asc_desc, int pagesize, int start, string tableName)
{
GetConnString();//作用是获取动态生成的数据库连接字符串
DataSet ds = Business.GetPagingData(field, asc_desc, pagesize, start, tableName);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
List<Hashtable> hashList = new List<Hashtable>();
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
DataRow row = ds.Tables[0].Rows[i] as DataRow;
Hashtable ht = new Hashtable();
foreach (DataColumn col in ds.Tables[0].Columns)
{
ht.Add(col.ColumnName, row[col.ColumnName]);
}
hashList.Add(ht);
}
int? count = Access.GetCount(String.Format("Select count(*) from {0}", tableName));
string json = "{totalPorperty:" + count + ",result:" + JavaScriptConvert.SerializeObject(hashList) + "}";
Response.Write(json);
}
else
{
Response.Write("");
}
}
/// <summary>
/// 获取连接字符串
/// </summary>
private void GetConnString()
{
if (Session["DBS"] != null && Convert.ToString(Session["DBS"]) != "")
{
Access.connstring = Convert.ToString(Session["DBS"]);
}
else
{
Response.Write(""); return;
}
}
}
}
业务组件类
Code
using System;
using System.Data;
using System.Configuration;
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.Data.SqlClient;
namespace MakeGrid
{
public class Business
{
/// <summary>
/// 实现分页
/// </summary>
/// <param name="field"></param>
/// <param name="asc_desc"></param>
/// <param name="pagesize"></param>
/// <param name="start"></param>
/// <param name="tableName"></param>
/// <returns></returns>
public static DataSet GetPagingData(string field, string asc_desc, int pagesize, int start, string tableName)
{
string sql = "WITH MOVIES AS ( " +
" SELECT ROW_NUMBER() OVER " +
" (ORDER BY " + field + " " + asc_desc + " ) AS Row," +
" *" +
" FROM " + tableName + " )" +
" SELECT *" +
" FROM MOVIES " +
" WHERE Row between (@start-1)* @pagesize+1 and @start*@pagesize";
SqlParameter[] prams =
{
new SqlParameter("@start",start),
new SqlParameter("@pagesize",pagesize)
};
return Access.GetDataSet(sql, prams);
}
}
}
数据组件类
Code
using System;
using System.Data;
using System.Configuration;
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.Data.SqlClient;
namespace MakeGrid
{
public class Access
{
public Access()
{ }
public static string connstring = "";
private static void CreateCommand(SqlConnection conn, SqlCommand cmd, string cmdText, params SqlParameter[] prams)
{
conn.ConnectionString = Access.connstring;
if (conn.State == ConnectionState.Closed)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (prams != null)
{
foreach (SqlParameter p in prams)
cmd.Parameters.Add(p);
}
}
public static DataSet GetDataSet(string cmdText)
{
return GetDataSet(cmdText, null);
}
public static DataSet GetDataSet(string cmdText, params SqlParameter[] prams)
{
using (SqlConnection conn = new SqlConnection())
{
SqlCommand cmd = new SqlCommand();
CreateCommand(conn, cmd, cmdText, prams);
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
cmd.Parameters.Clear();
return ds;
}
}
public static int? GetCount(string cmdText)
{
return GetCount(cmdText, null);
}
public static int? GetCount(string cmdText, params SqlParameter[] prams)
{
using (SqlConnection conn = new SqlConnection())
{
SqlCommand cmd = new SqlCommand();
CreateCommand(conn, cmd, cmdText, prams);
int? count;
count = Convert.ToInt32(cmd.ExecuteScalar());
cmd.Parameters.Clear();
return count;
}
}
}
}
源代码下载