简单介绍:
本示例主要用EXT2.2+vs.2008.net+C#+sql Server 2005解决GridPanel动态生成数据列,以减免在Html页面里静态配置数据列所带来的麻烦.为了大家更好的更方便的使用本示例,就附加了一个功能:只要添加本地的数据库连接,选择数据库里面的表,就可以对代码进行测试.
功能:
1,动态生成数据列,不用手动在ColumnModel里配置
2,根据输入的数据库连接字符串动态生成ComboBox数据表名,然后直接根据表名就可以实现浏览本地的数据表,
3,实现分页
环境:
1,EXT2.2版本
2,vs.net2008+C#
3,sql Server 2005
下面是项目的文件图
截图:
首页
次页
源代码:
<一>Default.aspx页面
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="GridPanel_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 runat="server">
<title>无标题页</title>
<link rel="stylesheet" type="text/css" href="resources/css/ext-all.css"/><%--顺序1--%>
<script type="text/javascript" src="ExtBase/ext-base.js"></script> <%--顺序2--%>
<script type="text/javascript" src="ExtBase/ext-all.js"></script> <%--顺序3--%>
<script type="text/javascript" src="ExtBase/ext-lang-zh_CN.js"></script><%--顺序4--%>
</head>
<body>
<form id="form1" runat="server">
<div>
<div id="div1"></div>
<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 + '",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:16}});
var pagingBar = new Ext.PagingToolbar
({
displayInfo:true,
emptyMsg:"没有数据显示",
displayMsg:"显示从{0}条数据到{1}条数据，共{2}条数据",
store:newStore,
pageSize:16
});
this.gridPanel = new Ext.grid.GridPanel
({
cm:cm,
id:"grid_panel",renderTo:"grid_div",
store:newStore,
frame:false,
border:true,
layout:"fit",
pageSize:16,
autoWidth:true,
height:400,
viewConfig:{forceFit:true},
bbar:pagingBar
});
}
function MakePanel()
{
this.panel_def = new Ext.Panel
({
layout:"fit",
border:true,
frame:true,
title:"数据浏览（请在下面输入数据库连接串：）",
autoWidth:true,
height:500,
id:"Viewport_ID",
renderTo:"panel_id",
tbar:
[
'',
'服务器名：',{xtype:'field',id:'dbServerID',100,value:"BK5RZVXGSM78C2T"},
'',
'用户名：',{xtype:'field',id:'dbUIDID',50,value:"sa"},
'',
'密码：',{xtype:'field',id:'dbPasswordID',50,value:"0"},
'',
'数据库名：',{xtype:'field',id:'dbNameID',100,value:"Frog"},
'',
{text:'确定',pressed:true,handler:event_click_enter,tooltip:"查看数据库连接是否正确"},
'',
'<div id="hello"></div>',
'',
{text:"<--选择表名",handler:event_select_table,id:"SelectTableID",disabled:false}
],
html: '<div id="grid_div"></div>'
});
}
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,
200,
resizable:true
});
}
function loader()
{
Ext.QuickTips.init();
MakePanel();
}
Ext.onReady(loader);
</script>
</div>
</form>
</body>
</html>
<二>后台代码:
using Newtonsoft.Json;//这个文件必须，请在网上下载，并在项目里对它引用，其他的名字空间省略
public partial class GridPanel_JsonData : System.Web.UI.Page
{
//初始化连接字符串
string dbDtr = "server={0};database={1};uid={2};pwd={3}";
protected void Page_Load(object sender, EventArgs e)
{
string param = Convert.ToString(Request["param"]);
#region 验证数据库连接是否正确，如果正确还需要返回该数据库连接下的所有的表的名称
if(param=="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);
}
string json = JavaScriptConvert.SerializeObject(testList);
Response.Write(json);
}
}
#endregion
#region 分页参数
int pagesize = 5;
int start = 1;
string field, asc_desc;
if (string.IsNullOrEmpty(Request["sort"]))
{
field = "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
//绑定数据列
if (param == "initDataColumnName")
{
string tableName = Convert.ToString(Request["tableName"]);
GetDataColumnName(tableName);
}
//绑定数据
if (param == "initData")
{
string tableName = Convert.ToString(Request["tableName"]);
Bind_Data(field, asc_desc, pagesize, start, tableName);
}
}
private void GetDataColumnName(string tableName)
{
NewMethod();
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("");
}
}
private void Bind_Data(string field, string asc_desc, int pagesize, int start, string tableName)
{
NewMethod();//作用是获取动态生成的数据库连接字符串
DataSet ds = Business.GetPagingData(field, asc_desc, pagesize, start, tableName);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
GetJsonData(ds,tableName);
}
else
{
Response.Write("");
}
}
private void NewMethod()
{
if (Session["DBS"] != null && Convert.ToString(Session["DBS"]) != "")
{
Access.connstring = Convert.ToString(Session["DBS"]);
}
else
{
Response.Write(""); return;
}
}
private void GetJsonData(DataSet ds, string tableName)
{
NewMethod();
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);
}
}
<三>分页功能单独实现
public class Business
{
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);
}
}
<四>数据库访问层:
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;
}
}
}