星期五, 11月 28, 2008

解決重複新增問題..

剛剛逛網站..逛到我好朋友的blog..裡面有一篇文章"避免因重新整理導致的重複新增問題"..看到他的作法..我也把我的作法提供一下..首先我會模擬兩個測試頁..test.aspx(填寫頁)..test1.aspx(結果頁)..看code說故事ㄅ..

test.aspx

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
<asp:Button ID="Button1" runat="server" Text="Button" onclick="Button1_Click" />
</form>
</body>
</html>

test.cs

using System;
using System.Web;

public partial class test : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.Cache.SetNoStore();
Response.Cache.SetExpires(DateTime.MinValue);
}
protected void Button1_Click(object sender, EventArgs e)
{
//這裡寫你要寫的code
Server.Transfer("~/test1.aspx", true);
}
}

test1.aspx

<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
填寫內容:<asp:Literal ID="Literal1" runat="server"></asp:Literal>
</div>
</form>
</body>
</html>

test1.cs

using System;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class test1 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (PreviousPage != null)
Literal1.Text = (Page.PreviousPage.FindControl("TextBox1") as TextBox).Text;
}
}

這樣流程合理..按前一頁也不會出現之前所填的資料內容..參考一下ㄅ..DevilDevilDevilDevilDevilDevilDevilDevilDevilDevilDevil..

參考:
http://demo.tc/view.aspx?id=410
http://bibby.be/2008/04/cache.html

http://msdn.microsoft.com/en-us/library/ms525800.aspx

星期二, 11月 25, 2008

LinqDataSource的分頁功能測試研究..

今天看到朋友demo在小舖問了一個問題"新元件DataPager的疑惑"..裡面主要是討論效率分頁的問題..剛剛好奇測試一下..直接看code..

.aspx

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<asp:LinqDataSource ID="LinqDataSource1" runat="server"
ContextTypeName="da.LinqToSqlDataContext"
Select="new (ProductName)" TableName="Products"
oncontextcreating="LinqDataSource1_ContextCreating" >
</asp:LinqDataSource>
<asp:DataPager ID="DataPager1" runat="server" PagedControlID="ListView1"
PageSize="5">
<Fields>
<asp:NextPreviousPagerField ButtonType="Button" ShowFirstPageButton="True"
ShowNextPageButton="False" ShowPreviousPageButton="False" />
<asp:NumericPagerField />
<asp:NextPreviousPagerField ButtonType="Button" ShowLastPageButton="True"
ShowNextPageButton="False" ShowPreviousPageButton="False" />
</Fields>
</asp:DataPager>
<asp:ListView ID="ListView1" runat="server" DataSourceID="LinqDataSource1" >
<LayoutTemplate>
<table>
<tr><th>名稱</th></tr>
<asp:PlaceHolder ID="itemPlaceholder" runat="server">
</asp:PlaceHolder>
</table>
</LayoutTemplate>
<ItemTemplate>
<tr><td><asp:Label ID="Label1" runat="server"
Text='<%# Eval("ProductName") %>'></asp:Label></td></tr>
</ItemTemplate>
</asp:ListView>
</form>
</body>
</html>

這是第一次讀取時的tsql..

dfsdf

點選分頁時的tsql..

cddfdf

抱持著有圖有真相的的精神..LinqDataSource真是不錯..^^..懶得自己code分頁的人可以用啦..如果想要自己自由自在的控制分頁..


參考:

星期五, 11月 21, 2008

用ObjectDataSource來處理分頁效率

今天試寫了一下用objectdatasource+girdview去把分頁這功能做出來..留下紀錄..
給之後要寫分頁的朋友參考..PigPigPigPigPigPig..

---store procedure---

CREATE PROCEDURE dbo._Paging

@PageIndex INT,
@PageSize INT,
@PageNumberCount INT OUTPUT

AS
SET NOCOUNT ON

begin
SELECT @PageNumberCount = COUNT(ProductID) FROM Products
end

begin
WITH tempTable AS (
SELECT ROW_NUMBER() OVER (ORDER BY ProductID)
AS Row, ProductID, ProductName, UnitPrice, UnitsInStock
FROM Products)

SELECT ProductID, ProductName, UnitPrice, UnitsInStock
FROM tempTable
WHERE Row between
(@PageIndex - 1) * @PageSize + 1 and @PageIndex * @PageSize
end

RETURN

------.cs--------

public class test1
{
int _PageNumberCount;
public DataTable getDataTable(int startRowIndex, int maximumRows)
{
int pageIndex = (startRowIndex / maximumRows) + 1;

using (SqlConnection sqlConn =
new SqlConnection(ConfigurationManager
.ConnectionStrings["NORTHWNDConnectionString"].ConnectionString))
{
SqlCommand sqlComm = sqlConn.CreateCommand();
sqlComm.CommandText = "_Paging";
sqlComm.CommandType = CommandType.StoredProcedure;
DataTable dt = new DataTable();

sqlComm.Parameters.AddWithValue("@PageIndex", pageIndex);
sqlComm.Parameters.AddWithValue("@PageSize", maximumRows);
SqlParameter sp = new SqlParameter("@PageNumberCount", 0);
sp.Direction = ParameterDirection.Output;
sqlComm.Parameters.Add(sp);
sqlConn.Open();
dt.Load(sqlComm.ExecuteReader());
_PageNumberCount = Convert.ToInt32(sp.Value);
return dt;
}
}
public int GetTotleRowsCount()
{
return _PageNumberCount;
}
}

------.aspx------

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Untitled Page</title>
</head>

<body>
<form id="form1" runat="server">
<div>
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" EnablePaging="True"
SelectMethod="getDataTable" TypeName="test1"
SelectCountMethod="GetTotleRowsCount">
</asp:ObjectDataSource>
<br />
<asp:GridView ID="GridView1" runat="server" AllowPaging="True"
DataSourceID="ObjectDataSource1" PageSize="5">
</asp:GridView>
</div>
</form>
</body>
</html>

這樣就可以做到很有效率的分頁功能了...Money EyesMoney EyesMoney EyesMoney EyesMoney EyesMoney Eyes..

參考資料:
http://davidhayden.com/blog/dave/archive/2005/12/30/2652.aspx
http://aspalliance.com/910_Efficient_Data_Paging_with_the_ASPNET_20_DataList_Control_and_ObjectDataSource.3

星期三, 11月 19, 2008

無敵機械狗..

這機器狗太強了..不會跌倒ㄟ..不過..越看怎麼覺得越噁心..好像"大蜘蛛"..SurpriseSurpriseSurpriseSurpriseSurprise..

星期二, 11月 18, 2008

重置資料表的primary key

之前被人家問到..怎把把資料庫的table的primary key重置阿..今天花了一點時間查一下..很簡單..只要..

DBCC CHECKIDENT (資料表, RESEED, 0)

這樣就可以reset了..然後順便把設定primary key的方式也給各位參考一下..

set identity_insert test on
insert test (pk,a,b,c) values (999,'ooo',1234,'20051010 10:25:10')
set identity_insert test off

這樣就可以直接設定了..Tongue out..過建議是..別去動它..就讓系統去自然發生好了..如果需要"順眼"或是編號需要..sql server 2005以上請愛用row_number()..這個函式..Vampire BatVampire BatVampire BatVampire BatVampire BatVampire BatVampire BatVampire Bat..

星期日, 11月 16, 2008

sql injection的筆記

今天在在網路上找有關sql injection的文章時..看到兩個蠻好用的東西..記錄下來..

1.在Pet Shop找到一段regular expression用來測是否有sql injection的..我大概測試一下..好像還蠻ok的..

\s?;\s?|\s?drop\s|\s?grant\s|^'|\s?--|\s?union\s|\s?delete\s|\s?truncate\s|\s?sysobjects\s?|\s?xp_.*?|\s?syslogins\s?|\s?sysremote\s?|\s?sysusers\s?|\s?sysxlogins\s?|\s?sysdatabases\s?|\s?aspnet_.*?|\s?exec\s?|


2.在DotNetNuke看到一個method..

/// <summary>
/// This function verifies raw SQL statements to prevent SQL injection attacks
/// and replaces a similar function (PreventSQLInjection) from the Common.Globals.vb module
/// </summary>
/// <param name="strSQL">This is the string to be filtered</param>
/// <returns>Filtered UserInput</returns>
/// <remarks>
/// This is a private function that is used internally by the InputFilter function
/// </remarks>
private string FormatRemoveSQL( string strSQL )
{
string strCleanSQL = strSQL;

if( strSQL != null )
{
Array BadCommands = ";,--,create,drop,select,insert,delete,update,union,sp_,xp_".Split( ',' );

// strip any dangerous SQL commands
int intCommand;
for( intCommand = 0; intCommand <= BadCommands.Length - 1; intCommand++ )
{
strCleanSQL = Regex.Replace( strCleanSQL, Convert.ToString( BadCommands.GetValue( intCommand ) ),
" ", RegexOptions.IgnoreCase );
}

// convert any single quotes
strCleanSQL = strCleanSQL.Replace( "'", "''" );
}

return strCleanSQL;
}

有問題在一起討論ㄅ..PigPigPigPigPigPigPigPig..

ps:如果大家想測試..可以去這個網站..有一堆sql injection的code給你玩玩看..

相關網站:

星期三, 11月 12, 2008

動態換佈景主題..

動態換佈景主題..這好像是大家常問的問題..今天試玩了一下..然後查了一下資料知道屬性的強制性是..

Page.Theme > 頁面定義的屬性 > Page.StyleSheetTheme

因為..我想比較靈活的去控制我的頁面..所以我會選擇動態去改Page.StyleSheetTheme這屬性..不過比較麻煩要處理的問題是..Page.StyleSheetTheme是唯獨的..查一下msdn也有建議的解決方法..看code比較快ㄅ..Big Grin..

這是我的app_themes

dfd

.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="test.aspx.cs" Inherits="test"
ValidateRequest="false" StylesheetTheme="Theme1" %>

<!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>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True">
<asp:ListItem>Theme1</asp:ListItem>
<asp:ListItem>Theme2</asp:ListItem>
</asp:DropDownList>
</form>
</body>
</html>

.cs

using System;
using System.Linq;

public partial class test : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

}
public override string StyleSheetTheme
{
get
{
if (Request.Params.AllKeys.Contains("DropDownList1"))
return Request.Params["DropDownList1"];
return "Theme1";
}
set
{
base.StyleSheetTheme = value;
}
}
}

這樣就行了..有空自己玩玩ㄅ..HappyHappyHappyHappyHappyHappyHappy..

參考: