// 寫入
using (var sw = new StreamWriter("data.txt", false, Encoding.UTF8))
{
for (int i = 0; i < reader.FieldCount; i++)
sw.Write(reader.GetName(i) + (i == reader.FieldCount-1 ? "" : ","));
sw.WriteLine();
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
sw.Write(reader[i].ToString() + (i == reader.FieldCount-1 ? "" : ","));
sw.WriteLine();
}
}
// 讀取
using (var sr = new StreamReader("data.txt", Encoding.UTF8))
{
string line;
while ((line = sr.ReadLine()) != null)
{
// 移除自增 ID (假設第一欄)
line = Regex.Replace(line, @"^\d+,", "");
string[] fields = line.Split(new[] {','}, StringSplitOptions.RemoveEmptyEntries);
if (fields.Length == 10) { /* 寫入 DB */ }
}
}
string sql = "SELECT * FROM Users WHERE Username = @username AND Password = @pwd";
using (var cmd = new SqlCommand(sql, conn))
{
cmd.Parameters.AddWithValue("@username", uid); // 簡易
// 或明確指定型別、長度 (推薦)
cmd.Parameters.Add("@pwd", SqlDbType.NVarChar, 50).Value = pwd;
using (var rdr = cmd.ExecuteReader()) { ... }
}
原理:參數化讓資料庫將輸入視為字面值而非可執行程式碼,徹底阻斷注入。
| 類型 | 關鍵字 | 結果集 |
|---|---|---|
| 交叉聯結 | CROSS JOIN | 笛卡兒積 (A×B) |
| 內連接 | INNER JOIN | 符合 ON 條件的配對列 |
| 左外部連接 | LEFT JOIN | 左表全保留,右表無配對則 NULL |
| 右外部連接 | RIGHT JOIN | 右表全保留 |
| 完整外部連接 | FULL JOIN | 雙表全保留 |
SELECT t2.num*10 + t1.num + 1 AS n
FROM tblNumber t1 CROSS JOIN tblNumber t2
ORDER BY 1; -- 產生 1~100 (tblNumber 有 0~9)
(SELECT ...) AS dt。TOP。
-- 方法 1: BETWEEN (需連續 ID)
SELECT * FROM Student WHERE StuId BETWEEN (@page-1)*@pageSize+1 AND @page*@pageSize;
-- 方法 2: ROW_NUMBER() (SQL 2005+ 標準分頁)
SELECT * FROM (
SELECT ROW_NUMBER() OVER (ORDER BY StuName) AS rn, *
FROM Student
) t WHERE t.rn BETWEEN (@page-1)*@pageSize+1 AND @page*@pageSize;
-- 方法 3: OFFSET/FETCH (SQL 2012+)
SELECT * FROM Student
ORDER BY StuName
OFFSET (@page-1)*@pageSize ROWS FETCH NEXT @pageSize ROWS ONLY;
// 總頁數
int totalPages = (int)Math.Ceiling(totalRows * 1.0 / pageSize);
lblPager.Text = String.Format("{0}/{1}", currentPage, totalPages);
// 頁碼跳轉 (Regex 替換頁碼部分)
lblPager.Text = Regex.Replace(lblPager.Text, @"^\d+/", currentPage + "/");
-- 必須搭配 OVER (ORDER BY ...)
SELECT ROW_NUMBER() OVER (ORDER BY StuName) AS rn, * FROM Student;
常用於:分頁、去重保留第一筆、排名、Top-N per Group。
這份筆記串聯了資料匯出入、參數化查詢、JOIN 邏輯、派生表分頁與 ROW_NUMBER 視窗函數。現代開發建議:全面參數化、優先使用 OFFSET/FETCH 分頁、大量資料匯入用 SqlBulkCopy/TVP、複雜查詢下推資料庫層 (SP/TVF)。