工作需要筆記

GridView gvExport = new GridView();
  gvExport.DataSource = getDataTable(strQuery);
  gvExport.DataBind();
  string strExportFilename = "ExportedData.xls"; 

protected void btn_excel_Click(object sender, EventArgs e)
{
//呼叫的片段、方式
// export_excel(要匯出的 Gridview 名稱, 匯出的檔名,模式); // 1=會加入日期時間
export_excel("gridview1", "output",1);
}

SELECT
ROW_NUMBER() OVER(ORDER BY CustomerID) AS ROWID
,*
FROM Orders

DECLARE @valueList varchar(8000)
DECLARE @pos INT
DECLARE @len INT
DECLARE @value varchar(8000)

SET @valueList = 'aa,bb,cc,f,sduygfdctys,w,e,r,t,sd sdf sdf,yyy yyy yy,'

set @pos = 0
set @len = 0

WHILE CHARINDEX(',', @valueList, @pos+1)>0
BEGIN
set @len = CHARINDEX(',', @valueList, @pos+1) - @pos
set @value = SUBSTRING(@valueList, @pos, @len)
--SELECT @pos, @len, @value /*this is here for debugging*/

PRINT @value
--Here is you value
--DO YOUR STUFF HERE
--DO YOUR STUFF HERE
--DO YOUR STUFF HERE
--DO YOUR STUFF HERE
--DO YOUR STUFF HERE

set @pos = CHARINDEX(',', @valueList, @pos+@len) +1
END
http://techforpassion.blogspot.tw/2013/03/how-to-split-comma-separated-string-and.html

DECLARE @RowData VARCHAR(MAX);
SET @RowData = '1,2,3,;4,5,6,;7,8,9,;';

WITH Num1 (n) AS (SELECT 1 UNION ALL SELECT 1),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Num3 (n) AS (SELECT 1 FROM Num2 AS X, Num2 AS Y),
Num4 (n) AS (SELECT 1 FROM Num3 AS X, Num3 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num4),
SplitRows (row_data, rn) AS (
SELECT SUBSTRING(';' + @RowData + ';', n + 1,
CHARINDEX(';', ';' + @RowData + ';', n + 1) - n - 1),
n + 1 - LEN(REPLACE(LEFT(@RowData, n), ';', ''))
FROM Nums
WHERE SUBSTRING(';' + @RowData + ';', n, 1) = ';'
AND n < LEN(';' + @RowData + ';')),
SplitColumns (rn, column_data, column_idx) AS (
SELECT rn,
SUBSTRING(',' + row_data + ',', n + 1,
CHARINDEX(',', ',' + row_data + ',', n + 1) - n - 1),
n + 1 - LEN(REPLACE(LEFT(row_data, n), ',', ''))
FROM Nums
JOIN SplitRows
ON SUBSTRING(',' + row_data + ',', n, 1) = ','
AND n < LEN(',' + row_data + ',')),
Pivoted (col1, col2, col3) AS (
SELECT [1], [2], [3]
FROM SplitColumns AS S
PIVOT
(MAX(column_data) FOR column_idx IN ([1], [2], [3])) AS P)
SELECT col1, col2, col3
FROM Pivoted
WHERE col1 IS NOT NULL;

declare@id int,@stuff varchar(20)declare@tmp table(
id int notnull, stuff varchar(20)primarykey(id))insert@tmp
select id, stuff from mastertable
where condition1 > condition2

selecttop1@id=id,@stuff=stuff from@tmp

while(@@rowcount>0)beginprint@stuff
deletefrom@tmp where id=@id
selecttop1@id=id,@stuff=stuff from@tmp
end
http://stackoverflow.com/questions/18827729/tsql-perform-code-for-each-row-of-an-select

<asp:TemplateField>
<ItemTemplate>
<asp:Button ID="AddButton" runat="server"
CommandName="AddToCart"
CommandArgument="<%# ((GridViewRow) Container).RowIndex %>"
Text="Add to Cart" />
</ItemTemplate>
</asp:TemplateField>
https://msdn.microsoft.com/zh-tw/library/Bb907626(v=VS.100).aspx

在web.config中，增加
<configuration>
    <system.web>
    <httpRuntime requestValidationMode="2.0"/>