酷奇個人網站 – 第 5 頁 – 全新的繁體中文 WordPress 網站

[轉][SQL Server]幾種分頁(Paging)寫法

來源 http://www.dotblogs.com.tw/jimmyyu/archive/2009/11/09/11499.aspx

以下介紹幾種在SQL Server上簡單的分頁做法，大致分為三種：

1.Order By搭配not in

2.Temp Table

3.ROW_NUMBER() function

以下以Northwind資料庫作為範例：

Order By搭配not in

1
Select Top 10 CustomerID, CompanyName ,ContactName, Country from Customers where CustomerID Not in

2
(Select top 10 CustomerID from Customers order by CustomerID)

3
Order by CustomerID

此做法是透過Where條件中的not in排除掉前十筆資料，以下是前20筆資料：

not in會幫忙排除掉CustomerID為1-10筆的內容，因此我們可以Select到11-20筆的資料結果：

Temp Table

1
select   identity(int,1,1) RowNum, CustomerID, CompanyName, ContactName, Country  into TempTable from Customers

將Select出來的欄位資料放到另一個Temp Table中，該Temp Table的第一個欄位是identity，為自動遞增的欄位，接著我們可對該Temp Table的RowNum(identity欄位)進行條件過濾；

1
Select * from Temp where RowNum >= 11 AND RowNum <= 20

由於第一個欄位是自動遞增，我們就可以將它當成是我們所要的RowNum來進行查詢，得到的資料如下：

ROW_NUMBER() Function

這個功能是在SQL Server 2005後的版本才有提供的，我們可透過以下的語法來進行處理：

1
SELECT  *

2
FROM (SELECT  ROW_NUMBER() OVER (ORDER BY CustomerID)

3
AS RowNum, CustomerID, CompanyName, ContactName, Country FROM Customers)

4
AS NewTable

5
WHERE RowNum >= 11 AND RowNum <= 20

Select 出來的資料如下：

在管理便利性上，我個人比較不傾向選擇Temp Table的方式，1.3兩種做法是我比較會去使用的，而如果可以選擇的話我應該會以ROW_NUMBER()這個Function的解法優先，我們以這兩句SQL的執行計畫中來看就可以看出使用ROW_NUMBER()的執行成本是較低的，主要差異應在於ROW_NUMBER()只做了一次的Clustered Index Scan，而另一個方法則是兩次：

C# 字串變數, 如何包含雙引號或反斜線

如果字串裡包括特殊符號,例如換行符號,雙引號等,可以用\,例如\” 表示雙引號

以下是其他的範例

string a="abc\"dd"; //以 \" 表示雙引號
string b= "C:\\Program Files\\";  //以 \\ 表示反斜線

若在字串前加@,會比較方便撰寫,範例如下
string d = @"C:\Program Files\"; //加了@, 只需要寫一個反斜線即可,會比較簡單
string e = @"""C:\Program Files\"""; //加了@, 用"" 二個雙引號來表示一個雙引號

SqlDataReader

SqlDataReader一次只能讀取一行

實際用法為

//建立SQL命令對象
SqlCommand myCommand = new SqlCommand(strSQL, dbConn);
//得到Data結果集
SqlDataReader myDataReader = myCommand.ExecuteReader();

while (myDataReader.Read())
{

string i = myDataReader.GetName(0); //string 顯示欄位名稱 0為第一欄 1為第二欄以此類推

object j = myDataReader.GetValue(0); //object 顯示內容值 0為第一欄位內容 1為第二欄位內容以此類推

}

DataTable.NewRow 方法

MSDN上的範例

注意 column也要加入，這樣 Datasource才會找到對應的欄位名稱

範例

下列範例會使用 NewRow 方法來建立 DataTable、加入判斷資料表結構描述的兩個 DataColumn 物件，並建立多個新的 DataRow 物件。然後，DataRow 物件是使用 Add 方法來加入至 DataRowCollection。

private void MakeDataTableAndDisplay()
{
    // Create new DataTable and DataSource objects.
    DataTable table = new DataTable();

    // Declare DataColumn and DataRow variables.
    DataColumn column;
    DataRow row; 
    DataView view;

    // Create new DataColumn, set DataType, ColumnName and add to DataTable.    
    column = new DataColumn();
    column.DataType = System.Type.GetType("System.Int32");
    column.ColumnName = "id";
    table.Columns.Add(column);

    // Create second column.
    column = new DataColumn();
    column.DataType = Type.GetType("System.String");
    column.ColumnName = "item";
    table.Columns.Add(column);

    // Create new DataRow objects and add to DataTable.    
    for(int i = 0; i < 10; i++)
    {
        row = table.NewRow();
        row["id"] = i;
        row["item"] = "item " + i.ToString();
        table.Rows.Add(row);
    }

    // Create a DataView using the DataTable.
    view = new DataView(table);

    // Set a DataGrid control's DataSource to the DataView.
    dataGrid1.DataSource = view;
}

[轉][.NET]將DataTable轉成List物件

來源 http://www.dotblogs.com.tw/rainmaker/archive/2013/11/05/126727.aspx

今天看同事分享時，看到他們將DataTable轉成物件時，使用for…each的方式。

雖然有很多的ORM框架可以幫我們做到同樣的效果，但如果手動要做的話，有那些方式呢?

1
//要轉換的物件

2
public class Document

3
{

4
    public string CompId { get; set; }

5
    public string HandleUnit { get; set; }

6
    public string No { get; set; }

7
}

方法1:使用for…each (for…next也可以)

01
//將DataTable轉成List<物件>

02
DataTable dt = new DataTable();

03
dt.Columns.Add("CompId", typeof(string));

04
dt.Columns.Add("HandleUnit", typeof(string));

05
dt.Columns.Add("No", typeof(string));

06
dt.Rows.Add(new object[] {"655", "EBS", "001" });

07
dt.Rows.Add(new object[] { "655", "ODM", "002" });

08
dt.Rows.Add(new object[] { "655", "OCS", "003" });

09

10
//Way 1, for next

11
List<Document> Way1 = new List<Document>();

12
foreach (DataRow dr in dt.Rows)

13
{

14
    Document doc = new Document();

15
    doc.CompId = dr.Field<string>("CompId");

16
    doc.HandleUnit = dr.Field<string>("HandleUnit");

17
    doc.No = dr.Field<string>("No");

18
    Way1.Add(doc);

19
}

方式2:建立Extension Methods透過Reflection來簡化(參考:fetch datarow to c# object)

01
public static class DataTableExtensions

02
{

03
    public static IList<T> ToList<T>(this DataTable table) where T : new()

04
    {

05
        IList<PropertyInfo> properties = typeof(T).GetProperties().ToList();

06
        IList<T> result = new List<T>();

07

08
        foreach (var row in table.Rows)

09
        {

10
            var item = CreateItemFromRow<T>((DataRow)row, properties);

11
            result.Add(item);

12
        }

13

14
        return result;

15
    }

16

17
    public static IList<T> ToList<T>(this DataTable table, Dictionary<string, string> mappings) where T : new()

18
    {

19
        IList<PropertyInfo> properties = typeof(T).GetProperties().ToList();

20
        IList<T> result = new List<T>();

21

22
        foreach (var row in table.Rows)

23
        {

24
            var item = CreateItemFromRow<T>((DataRow)row, properties, mappings);

25
            result.Add(item);

26
        }

27

28
        return result;

29
    }

30

31
    private static T CreateItemFromRow<T>(DataRow row, IList<PropertyInfo> properties) where T : new()

32
    {

33
        T item = new T();

34
        foreach (var property in properties)

35
        {

36
            property.SetValue(item, row[property.Name], null);

37
        }

38
        return item;

39
    }

40

41
    private static T CreateItemFromRow<T>(DataRow row, IList<PropertyInfo> properties, Dictionary<string, string> mappings) where T : new()

42
    {

43
        T item = new T();

44
        foreach (var property in properties)

45
        {

46
            if (mappings.ContainsKey(property.Name))

47
                property.SetValue(item, row[mappings[property.Name]], null);

48
        }

49
        return item;

50
    }

51
}

方式2.1:如果欄位名稱跟物件屬性名稱相同，則直接對應。

1
DataTable dt2 = new DataTable();

2
dt2.Columns.Add("CompId", typeof(string));

3
dt2.Columns.Add("HandleUnit", typeof(string));

4
dt2.Columns.Add("No", typeof(string));

5
dt2.Rows.Add(new object[] { "655", "EBS", "001" });

6
dt2.Rows.Add(new object[] { "655", "ODM", "002" });

7
dt2.Rows.Add(new object[] { "655", "OCS", "003" });

8
//Way 2, 如果欄位名稱跟屬性一樣，就直接Assign

9
var Wary2 = dt2.ToList<Document>();

方式2.2:如果欄位名稱跟物件屬性名稱不同，則建立Dictionary型態的Mapping物件，如下，

01
DataTable dt3 = new DataTable();

02
dt3.Columns.Add("CompId", typeof(string));

03
dt3.Columns.Add("HandleUnit", typeof(string));

04
dt3.Columns.Add("No", typeof(string));

05
dt3.Rows.Add(new object[] { "655", "EBS", "001" });

06
dt3.Rows.Add(new object[] { "655", "ODM", "002" });

07
dt3.Rows.Add(new object[] { "655", "OCS", "003" });

08
//Way 3, 如果欄位跟屬性不同，就建一個Mapping表

09

10
var mappings = new Dictionary<string, string>();

11
mappings.Add("CompId", "CompId");

12
mappings.Add("HandleUnit", "HandleUnit");

13
mappings.Add("No", "No");

14
var Way3 = dt3.ToList<Document>(mappings);

相信一定有一堆人會問說，為何要這樣做呢? 為何不使用ORM呢? 為什麼? 為什麼? …..等很多的疑問

同事回說，因為只是針對舊有的系統新增一個小的模組，而目前系統的Table有300多個，時間有限 … So…. 就先這樣搞…

C# 在使用時 array 時，要先宣告陣列大小。
若一開始不確定陣列大小，
想要使用時再動態增加大小，可以使用 List 來達成此效果

例如：
List<string> my1d; // 1維：List 裡面放不確定數量的 stirng
List<List<string>> my2d;// 2維：List 裡面放不確定數量的 List<string>

範例：以 List 做成類似 3維陣列，但每一維度，底下元素個數不固定

/*
希望達成此效果
Array (
    0 => Array(
            0 => Array("A1", "A2", "A3", "A4"),
            1 => Array("B1", "B2", "B3")
        ),
    1 => Array(
            0 => Array("C1", "C2")
        )
);
*/
List<List<List<string>>> my3d = new List<List<List<string>>>();
List<string> my1d;
List<List<string>> my2d;
//
my2d = new List<List<string>>();
my1d = new List<string>();
my1d.Add("A1");
my1d.Add("A2");
my1d.Add("A3");
my1d.Add("A4");
my2d.Add(my1d);
my1d = new List<string>();
my1d.Add("B1");
my1d.Add("B2");
my1d.Add("B3");
my2d.Add(my1d);
my3d.Add(my2d);
//
my2d = new List<List<string>>();
my1d = new List<string>();
my1d.Add("C1");
my1d.Add("C2");
my2d.Add(my1d);
my3d.Add(my2d);
Console.WriteLine("my3d[0][0][2] = " + my3d[0][0][2]); // my3d[0][0][2] = A3
Console.WriteLine("my3d[0][1][0] = " + my3d[0][1][0]); // my3d[0][1][0] = B1
Console.WriteLine("my3d[1][0][1] = " + my3d[1][0][1]); // my3d[1][0][1] = C2
Console.WriteLine("my3d.Count = " + my3d.Count); // my3d.Count = 2
Console.WriteLine("my3d[0].Count = " + my3d[0].Count); // my3d[0].Count = 2
Console.WriteLine("my3d[1].Count = " + my3d[1].Count); // my3d[1].Count = 1
Console.WriteLine("my3d[0][0].Count = " + my3d[0][0].Count); // my3d[0][0].Count = 4
Console.WriteLine("my3d[0][1].Count = " + my3d[0][1].Count); // my3d[0][1].Count = 3
Console.WriteLine("my3d[1][0].Count = " + my3d[1][0].Count); // my3d[1][0].Count = 2

資料繫結以及Container.DataItem幾種方式與用法，效率分析

靈活的運用資料繫結操作

綁定到簡單屬性:<%#UserName%>

綁定到集合:<asp:ListBox id=”ListBox1″ datasource='<%# myArray%>’ runat=”server”>

綁定到運算式:<%#(class1.property1.ToString() + “,” + class1.property2.ToString())%>

綁定到方法傳回值:<%# GetSafestring(str) %>

綁定到Hashtable:<%# ((DictionaryEntry)Container.DataItem).Key%>

綁定到ArrayList:<%#Container.DataItem %>

若陣列裡裡放的是物件則可能要進行必要的轉換後再綁定如:

<%#((物件類型)Container.DataItem).屬性%>

綁定到DataView,DataTable,DataSet:

<%#((DataRowView)Container.DataItem)[“欄位名”]%>或

<%#((DataRowView)Container.DataItem).Rows[0][“欄位名”]%>

要格式化則:

<%#string.Format(“格式”,((DataRowView)Container.DataItem)[“欄位名”])%>

<%#DataBinder.Eval(Container.DataItem,”欄位名”,”格式”)%>

綁定到DataReader:

<%#((IDataReader)Container.DataItem).欄位名%>

當然為了方便一般使用最多的就是DataBinder類的Eval方法了.不過這樣對於同時要綁定大量的資料效率要低一些

在綁定資料時經常會用到這個句程式：<%# DataBinder.Eval(Container.DataItem,”xxxx”)%>或者<%# DataBinder.Eval(Container,”DataItem.xxxx”)%>

今天又學到一種，而且微軟也說這種方法的效率要比以上兩種高。

<%# ((DataRowView)Container.DataItem)[“xxxx”]%>

很有用的，這樣可以在前臺頁面做好多事情了。

還要記住要這樣用必須要在前臺頁面導入名稱空間System.Data,否則會建置錯誤資訊。

<%@ Import namespace=”System.Data” %>

這種用法其實和<%# ((DictionaryEntry)Container.DataItem).Key%>是一個道理。

綁定到DataSet、DataTable時:

<%#((System.Data.DataRowView)Container.DataItem)[“欄位名”]%>

<%#((System.Data.DataRowView)Container.DataItem)[索引]%>

綁定到DataReader時:

<%#((System.Data.Common.DbDataRecord)Container.DataItem)[索引]%>

<%#((System.Data.Common.DbDataRecord)Container.DataItem)[“欄位名”]%>

關鍵是Container這個東西，它比較神秘。它的名稱空間是System.ComponentModel。對於它我還需要進一步理解。

初學.NET,現在在看DataGrid控制項，在ItemTemplate顯示資料時,

DataBinder.Eval(Container.DataItem,”Name”)和Container.DataItem(“Name”)有什麼區別？

DataBinder是System.Web裡面的一個靜態類，它提供了Eval方法用於簡化資料繫結運算式的編寫，但是它使用的方式是通過Reflection等開銷比較大的方法來達到易用性，因此其性能並不是最好的。而Container則根本不是任何一個靜態的物件或方法，它是ASP.NET頁面編譯器在資料繫結事件處理常式內部聲明的區域變數，其類型是可以進行資料繫結的控制項的資料容器類型（如在Repeater內部的資料繫結容器叫RepeaterItem），在這些容器類中基本都有DataItem屬性，因此你可以寫Container.DataItem，這個屬性返回的是你正在被綁定的資料來源中的那個資料項目。如果你的資料來源是DataTable，則這個資料項目的類型實際是DataRowView。

C#的 Code Blocks 模式，微軟MSDN的範例

http://msdn.microsoft.com/en-us/library/vstudio/f0111sbh(v=vs.100).aspx
底下為程式碼

<%@ Page Language="C#" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">
    protected void Page_Load(object sender, EventArgs e)
    {
        if (IsPostBack)
        {
            Response.Write("<br />Page has been posted back.");
        }
    }
</script>

<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    Hello.<br />
    <asp:Button ID="Button1" runat="server" 
    Text="Click to Post Back" />
    </div>
    </form>
</body>
</html>

ASP.NET framework 4.0具有潛在危險 Request.Form

web.config 中加入

<system.web>
<httpRuntime requestValidationMode="2.0" />
</system.web>

會出錯的網頁中加入

<%@ Page validateRequest="false" %>

一切就解決了!!

[轉]MSSQL(limit) → MYSQL

來源 http://www.dotblogs.com.tw/easonwei/archive/2012/09/24/75008.aspx

最近將MYSQL查詢改成MSSQL

關於查詢限制條件記錄於下：

MYSQL

1
select * from [table] where ... LIMIT 123

MSSQL

1
select top 123* from [table] where ...

–限制資料筆數(只取前面0~123筆)

–(方法A)效率較好

1
select top 123 *

2
 from [user]

3
 order by emp_id desc

–(方法B)效率較差

1
select *

2
  from (select ROW_NUMBER() over (order by emp_id asc) rownum,

3
  * 

4
 from [user] ) as yourselect

5
  where rownum between 0 and 123

6
   order by emp_id desc

–用來做分頁查詢(只取第123~150筆)

–(方法C)效率較好

1
select *

2
  from (  select ROW_NUMBER() over (order by emp_id asc) rownum,

3
 *

4
  from [user] ) as yourselect

5
  where rownum between 123 and 150

6
 order by emp_id desc

–(方法D)效率較差

1
SELECT TOP 150 *

2
  FROM [user] WHERE user_id_seq NOT IN

3
  ( SELECT TOP 123 user_id_seq FROM [user] ORDER BY user_id_seq ASC )

4
  ORDER BY emp_id ASC