工作需要筆記

GridView gvExport = new GridView();
  gvExport.DataSource = getDataTable(strQuery);
  gvExport.DataBind();

  string strExportFilename = "ExportedData.xls"; 

protected void btn_excel_Click(object sender, EventArgs e)

{

SELECT
ROW_NUMBER() OVER(ORDER BY CustomerID) AS ROWID

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

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;  

CREATEFUNCTION dbo.splitstring (@stringToSplit VARCHAR(MAX))
RETURNS
 @returnList TABLE([Name][nvarchar](500))ASBEGINDECLARE@name NVARCHAR(255)DECLARE@pos INT

 WHILE CHARINDEX(',',@stringToSplit)>0BEGINSELECT@pos  = CHARINDEX(',',@stringToSplit)SELECT@name = SUBSTRING(@stringToSplit,1,@pos-1)INSERTINTO@returnList 
  SELECT@name

  SELECT@stringToSplit = SUBSTRING(@stringToSplit,@pos+1, LEN(@stringToSplit)-@pos)ENDINSERTINTO@returnList
 SELECT@stringToSplit

 RETURNEND

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

declare@ids table(id int);

<asp:TemplateField>
  <ItemTemplate>
    <asp:Button ID="AddButton" runat="server" 
      CommandName="AddToCart" 
      CommandArgument="<%# ((GridViewRow) Container).RowIndex %>"
      Text="Add to Cart" />
  </ItemTemplate> 
</asp:TemplateField>

在web.config中，增加

<configuration>