微软暑期项目时-技术篇2
2010-03-22 14:31 三皮开发时 阅读(263) 评论(0) 收藏 举报目录：
1.万能数据库分页(存储过程)
2.DataList分页
3.字串超过自定义范围用...来标识
4.小技巧 ctrl+a ,ctrl+k,f
5.gridView 细节
6.几种数据控件获取其值
7.DataList,ListView 加入行样式
8.数据控件在原有获取的DataSet上新增列 并赋值
9.session和hashtable 秒用 例如购物车~
---------------------------------------------------------------------------------------------------------------------------------------------------
1.万能数据库分页
--/*-----存储过程 分页处理 孙伟 2005-03-28创建 -------*/
--/*-----存储过程 分页处理 浪尘 2008-9-1修改----------*/
--/*----- 对数据进行了2分处理使查询前半部分数据与查询后半部分数据性能相同 -------*/
alter PROCEDURE [dbo].[up_Pager]
(
@tblName nvarchar(200), ----要显示的表或多个表的连接
@fldName nvarchar(500) = '*', ----要显示的字段列表
@pageSize int = 10, ----每页显示的记录个数
@page int = 1, ----要显示那一页的记录
@fldSort nvarchar(200) = null, ----排蜃侄瘟斜砘蛱跫?
@Sort bit = 0, ----排序方法，0为升序，1为降序(如果是多字段排列Sort指代最后一个排序字段的排列顺序(最后一个排序字段不加排序标记)--程序传参如：' SortA Asc,SortB Desc,SortC ')
@strCondition nvarchar(1000) = null, ----查询条件,不需where
@ID nvarchar(150), ----主表的主键
@Dist bit = 0, ----是否添加查询字段的 DISTINCT 默认0不添加/1添加
@pageCount int = 1 output, ----查询结果分页后的总页数
@Counts int = 1 output ----查询到的记录数
)
AS
SET NOCOUNT ON
Declare @sqlTmp nvarchar(1000) ----存放动态生成的SQL语句
Declare @strTmp nvarchar(1000) ----存放取得查询结果总数的查询语句
Declare @strID nvarchar(1000) ----存放取得查询开头或结尾ID的查询语句
Declare @strSortType nvarchar(10) ----数据排序规则A
Declare @strFSortType nvarchar(10) ----数据排序规则B
Declare @SqlSelect nvarchar(50) ----对含有DISTINCT的查询进行SQL构造
Declare @SqlCounts nvarchar(50) ----对含有DISTINCT的总数查询进行SQL构造
declare @timediff datetime --耗时测试时间差
select @timediff=getdate()
if @Dist = 0
begin
set @SqlSelect = 'select '
set @SqlCounts = 'Count(*)'
end
else
begin
set @SqlSelect = 'select distinct '
set @SqlCounts = 'Count(DISTINCT '+@ID+')'
end
if @Sort=0
begin
set @strFSortType=' ASC '
set @strSortType=' DESC '
end
else
begin
set @strFSortType=' DESC '
set @strSortType=' ASC '
end
--------生成查询语句--------
--此处@strTmp为取得查询结果数量的语句
if @strCondition is null or @strCondition='' --没有设置显示条件
begin
set @sqlTmp = @fldName + ' From ' + @tblName
set @strTmp = @SqlSelect+' @Counts='+@SqlCounts+' FROM '+@tblName
set @strID = ' From ' + @tblName
end
else
begin
set @sqlTmp = + @fldName + 'From ' + @tblName + ' where (1>0) ' + @strCondition
set @strTmp = @SqlSelect+' @Counts='+@SqlCounts+' FROM '+@tblName + ' where (1>0) ' + @strCondition
set @strID = ' From ' + @tblName + ' where (1>0) ' + @strCondition
end
----取得查询结果总数量-----
exec sp_executesql @strTmp,N'@Counts int out ',@Counts out
declare @tmpCounts int
if @Counts = 0
set @tmpCounts = 1
else
set @tmpCounts = @Counts
--取得分页总数
set @pageCount=(@tmpCounts+@pageSize-1)/@pageSize
/**//**//**//**当前页大于总页数 取最后一页**/
if @page>@pageCount
set @page=@pageCount
--/*-----数据分页2分处理-------*/
declare @pageIndex int --总数/页大小
declare @lastcount int --总数%页大小
set @pageIndex = @tmpCounts/@pageSize
set @lastcount = @tmpCounts%@pageSize
if @lastcount > 0
set @pageIndex = @pageIndex + 1
else
set @lastcount = @pagesize
--//***显示分页
if @strCondition is null or @strCondition='' --没有设置显示条件
begin
if @pageIndex<2 or @page<=@pageIndex / 2 + @pageIndex % 2 --前半部分数据处理
begin
if @page=1
set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName
+' order by '+ @fldSort +' '+ @strFSortType
else
begin
if @Sort=1
begin
set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName
+' where '+@ID+' <(select min('+ @ID +') from ('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-1) as Varchar(20)) +' '+ @ID +' from '+@tblName
+' order by '+ @fldSort +' '+ @strFSortType+') AS TBMinID)'
+' order by '+ @fldSort +' '+ @strFSortType
end
else
begin
set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName
+' where '+@ID+' >(select max('+ @ID +') from ('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-1) as Varchar(20)) +' '+ @ID +' from '+@tblName
+' order by '+ @fldSort +' '+ @strFSortType+') AS TBMinID)'
+' order by '+ @fldSort +' '+ @strFSortType
end
end
end
else
begin
set @page = @pageIndex-@page+1 --后半部分数据处理
if @page <= 1 --最后一页数据显示
set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@lastcount as VARCHAR(4))+' '+ @fldName+' from '+@tblName
+' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType
else
if @Sort=1
begin
set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName
+' where '+@ID+' >(select max('+ @ID +') from('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +' '+ @ID +' from '+@tblName
+' order by '+ @fldSort +' '+ @strSortType+') AS TBMaxID)'
+' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType
end
else
begin
set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName
+' where '+@ID+' <(select min('+ @ID +') from('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +' '+ @ID +' from '+@tblName
+' order by '+ @fldSort +' '+ @strSortType+') AS TBMaxID)'
+' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType
end
end
end
else --有查询条件
begin
if @pageIndex<2 or @page<=@pageIndex / 2 + @pageIndex % 2 --前半部分数据处理
begin
if @page=1
set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName
+' where 1=1 ' + @strCondition + ' order by '+ @fldSort +' '+ @strFSortType
else if(@Sort=1)
begin
set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName
+' where '+@ID+' <(select min('+ @ID +') from ('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-1) as Varchar(20)) +' '+ @ID +' from '+@tblName
+' where (1=1) ' + @strCondition +' order by '+ @fldSort +' '+ @strFSortType+') AS TBMinID)'
+' '+ @strCondition +' order by '+ @fldSort +' '+ @strFSortType
end
else
begin
set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName
+' where '+@ID+' >(select max('+ @ID +') from ('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-1) as Varchar(20)) +' '+ @ID +' from '+@tblName
+' where (1=1) ' + @strCondition +' order by '+ @fldSort +' '+ @strFSortType+') AS TBMinID)'
+' '+ @strCondition +' order by '+ @fldSort +' '+ @strFSortType
end
end
else
begin
set @page = @pageIndex-@page+1 --后半部分数据处理
if @page <= 1 --最后一页数据显示
set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@lastcount as VARCHAR(4))+' '+ @fldName+' from '+@tblName
+' where (1=1) '+ @strCondition +' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType
else if(@Sort=1)
set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName
+' where '+@ID+' >(select max('+ @ID +') from('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +' '+ @ID +' from '+@tblName
+' where (1=1) '+ @strCondition +' order by '+ @fldSort +' '+ @strSortType+') AS TBMaxID)'
+' '+ @strCondition+' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType
else
set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName
+' where '+@ID+' <(select min('+ @ID +') from('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +' '+ @ID +' from '+@tblName
+' where (1=1) '+ @strCondition +' order by '+ @fldSort +' '+ @strSortType+') AS TBMaxID)'
+' '+ @strCondition+' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType
end
end
exec sp_executesql @strTmp --执行@strTmp, sp_executesql 执行sql.
-------
exec up_Pager
'tb_Vote',--表名
'*',--查询的字段
5,--每行记录的个数
1,--显示那一页的记录，这里是现实第一页的记录
'voteID',--排序的字段列表或条件
1,--为排序的字段列表 排序方法 0为升序，1为降序
'and voteID%2 <> 0 or voteTitle=''定饭了没''',
--查询条件这里是显示单数和voteTitle='定饭了没'注意这里的字符串中字符串的格式: ''定饭了没'' 都是单撇号
voteID,--主表的主键
0 ----是否添加查询字段的 DISTINCT 默认0不添加/1添加
----------------------------------------------------------------------------------------------------------------------------
2.DataList 分页
public void Bind()
{
//获取当前页码
int curpage = Convert.ToInt32(lblPage.Text);
//使用PageDataSource类实现DataList控件分页功能
PagedDataSource ps = new PagedDataSource();
//配置dataset
SqlConnection con = new SqlConnection(str);
SqlCommand cmd = new SqlCommand("select * from tb_Vote", con);
SqlDataAdapter adapt = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adapt.Fill(ds);
//填充数据源..
ps.DataSource = ds.Tables[0].DefaultView;
//允许分页
ps.AllowPaging = true;
//显示分页页码的数量
ps.PageSize = 3;
// 显示页面的索引 假如将最后页的事件里加入 ps.CurrentPageIndex=0; 那么点击最后页 会跳到首页.
ps.CurrentPageIndex = curpage-1;
//启用控件
lnkbtnOne.Enabled = true;//第一页
lnkbtnNext.Enabled = true;//下一页
lnkbtnUp.Enabled = true;//上一页
lnkbtnBack.Enabled = true;//最后页
//如果当前页为第一页时
if (curpage == 1)
{
//不启用第一页按钮
lnkbtnOne.Enabled = false;
//不启用上一页按钮
lnkbtnUp.Enabled = false;
}
//如果当前页为最后页
if (curpage == ps.PageCount)
{
//不穷下一页按钮
lnkbtnNext.Enabled = false;
//不启用下一页按钮
lnkbtnBack.Enabled = false;
}
//显示分页的总页数
labBackPage.Text = ps.PageCount.ToString();
//绑定
DataList1.DataSource = ps;
DataList1.DataKeyField = "voteID";
DataList1.DataBind();
}
//第一页
protected void lnkbtnOne_Click(object sender, EventArgs e)
{
lblPage.Text = "1";
DataList1.EditItemIndex = -1;
Bind();
}
//上一页
protected void lnkbtnUp_Click(object sender, EventArgs e)
{
lblPage.Text = Convert.ToString(Convert.ToInt32(lblPage.Text) - 1);
DataList1.EditItemIndex =- 1;
Bind();
}
//下一页
protected void lnkbtnNext_Click(object sender, EventArgs e)
{
lblPage.Text = Convert.ToString(Convert.ToInt32(lblPage.Text )+1);
DataList1.EditItemIndex = -1;
Bind();
}
//最后页
protected void lnkbtnBack_Click(object sender, EventArgs e)
{
//之前写的有错误，最后页(页总数)赋予给当前页
lblPage.Text = labLastText.Text;
DataList1.EditItemIndex = -1;
Bind();
DataList1.EditItemIndex = -1;
Bind();
}
//注：如果有什么编辑的话，下一页后还是会有编辑状态 那么加入 DataList1.EditIndex=-1(负1) 喔了.
-----------------------------------------------------------------------------------------------------------------------------------------------
3.控件的小使用：控制文本长度 如果超过自定义范围用...代替，放上去时显示所有信息
<asp:Label ID="Label2" runat="server" Text='<%#(Ev
上段代码为DataList代码.
4.XML代码或cs里代码 全选 ctrl+K,F 排序
-----------------------------------------------------------------------------------------------------------------------------
5.<asp:TemplateField HeaderText ="单价">
<HeaderStyle HorizontalAlign="Center" />
<ItemStyle HorizontalAlign="Center" />
<ItemTemplate >
<%#Ev
</ItemTemplate>
</asp:TemplateField>
-----------------------------------------------------------------------------------------------------------------------------------------------
6.((TextBox)GridView1.Rows[e.RowIndex].Cells[3].Controls[0]).Text.Trim();//GridView控件寻值
((TextBox)e.Item.FindControl("txtEmail")).Text.Trim();//ListView 在ItemCommand下形式
((TextBox)e.Item.FindControl("txtEmail")).Text;//DataList控件寻值 ItemCommand 下形式
------- 介绍的是：GridView 没有EditItemTemplate 所以ItemCommand 也就写不出编辑框形式，只能用他提供的Edit事件，ListView 和DataList 区别是在 ListView不可以在ItemCommand下写出编辑框 没有e.Item.ItemIndex属性 所以编辑框/select框 的弹出形式只能用他自己提供的事件下写ListView1.EditIndex = e.NewEditIndex;Bind(); 然而在继续更新，取消，删除，等附加操作时可以在ItemCommand下写 eg: 更新 按钮属性 CommandName=“ed” ItemCommand事件下
if(e.CommandName=="ed").....除此之外ListView 比DataList多几个Template eg:InsertTemplate ...
另，介绍下控件属性弹出框
WebControl webcontrol = ((LinkButton)e.Item.FindControl("lbtnDelete")) as LinkButton;
Jxmstc.Utility.MessageBox.ShowConfirm(webcontrol, "角色还有用户组关联，确定删除？");
//引用方法--哟，不错哦
public static void ShowConfirm(System.Web.UI.WebControls.WebControl Control, string msg)
{
Control.Attributes.Add("on
}
-----------------------------------------------------------------------------------------------------------------------------------------
7.DataList,ListView 加入行样式方法
<tr on
***
<td>**</td>
<td>**</td>
**
<tr>
只要在你想要操作的模板下 在tr 加入属性
on
既可.
---------------------------------------------------------------------------------------------------------------------------------------
8.
SqlConnection con = new SqlConnection(str);
SqlCommand cmd = new SqlCommand("select * from tb_Vote", con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
DataColumn c1 = new DataColumn("NewColumn");
ds.Tables[0].Columns.Add(c1);
int i = 1;
foreach (DataRow row in ds.Tables[0].Rows)
{
row["NewColumn"] = "NewAdd"+i.ToString();
i++;
}
GridView1.DataSource = ds.Tables[0].DefaultView;
GridView1.DataBind();
DataList1.DataSource = ds.Tables[0].DefaultView;
DataList1.DataBind();
//DataTable dt = new DataTable();
//DataColumn c1 = new DataColumn("voteID");
//DataColumn c2 = new DataColumn("voteTitle");
//DataColumn c3 = new DataColumn("voteContent");
//DataColumn c4 = new DataColumn("again add column");
//dt.Columns.Add(c1);
//dt.Columns.Add(c2);
//dt.Columns.Add(c3);
//dt.Columns.Add(c4);
//DataRow row;
//for (int i = 1; i <= 3; i++)
//{
// row = dt.NewRow();
// row["voteID"] = i.ToString();
// row["voteTitle"] = "voteTitle" + i.ToString();
// row["voteContent"] = "voteContent" + i.ToString();
// row["again add column"] = "asdf" + i.ToString();
// dt.Rows.Add(row);
//}
//GridView1.DataSource = dt.DefaultView;
//GridView1.DataBind();
//DataList1.DataSource = dt.DefaultView;
//DataList1.DataBind();
---------------------------------------------------------------------------------------------------------------------------------------------------10.
if (Session["1"] == null)
{
hash = new Hashtable();
hash.Add("一", 1);
Session["1"] = hash;
s1 = hash["一"].ToString();
}
else
{
hash = (Hashtable)Session["1"];
if (hash.Contains("一"))
{
int count = Convert.ToInt32(hash["一"].ToString());
hash["一"] = count + 1;
}
}
浙公网安备 33010602011771号