【C#】連接資料庫實作(MSSQL)

2021-01-27

15496
0
SQL
2021-01-28

這篇要介紹關聯性資料庫，筆者選用MSSQL作為後續工作使用，筆者試著用簡單的概念來說明，順便作個工作紀錄囉。內容範圍包含:

SQL、T-SQL、DBMS、RDBM
MSSQL安裝說明
SQL資料型態、key
匯入檔案、備份
簡單的連線測試

資料庫與資料庫管理系統

什麼是資料庫
如同字面上所見，像車庫、金庫都是存放某種物品的地方，所以資料庫就是儲存資料的地方。資料庫能以各種形式存在，廣義來說，手寫筆記本、excel表格都算是資料庫吧，但隨著資料越來越多，會衍生出別的問題，例如每個人紀錄邏輯不同、紙本的保存、搜尋資料的成本等。現在電腦運算能力越來越強大，透過電腦化的資料儲存方式，可以提供許多好處。前人已針對電腦化資料庫制定了一些準則與規範，我們只要搭配資料庫管理系統，就可以有效率的對資料進行檢索、排序、計算等操作，透過電腦可提高資料品質、降低處理或管理成本，也能提供較高的安全性。

關聯式資料庫軟體
常聽見的資料庫軟體有

MySQL
Microsoft SQL Server(MsSQL)
PostgreSQL
Oracle Database

上述4個資料庫管理系統都屬於關聯式資料庫(Relational DataBase System, RDBS )，既然有關聯性資料庫，就有非關聯性的資料庫(例: NoSQL)，但筆者也還沒碰過，故不在今天討論範圍。我們先來看AWS如何介紹關聯性資料庫(參1)，「表格的每一直欄儲存特定類型的資料，而每個欄位儲存某個屬性的實際數值。表格中的橫列代表一個物件或實體的一組相關數值」。如果用Excel來解釋應該就更直觀了，直欄代表國英數成績(各項資料)，橫列則代表一個人(實體)。整張表格在RDBS的世界裡稱為Table，Table可以透過鍵(Key)來建立Table彼此間的關聯，透過join的功能將兩張Table結合，如此一來減少儲存很多重複的資料唷!

SQL
中文又稱結構化查詢語言(Structured Query Language, SQL)，關聯式資料庫使用 SQL或結構式查詢語言做為主要的通訊界面。美國國家標準協會 (ANSI) 在 1986 年將 SQL 納入標準，所有常見的關聯式資料庫引擎都支援標準 ANSI SQL，也就是說只要我們學會了SQL，即使換了不同的資料庫系統，仍然可以使用SQL來進行資料處理的工作。Transact-SQL(T-SQL)是具有批次與區塊特性的SQL指令集合，因為軟體開發公司不同，彼此間還是會有差異性。ANSI SQL基礎語法又分為DDL(資料定義語言)、DML(資料操縱語言)、DCL(資料控制語言) (參2)，所以別人口中常說的SQL語法，其實就是指ANSI SQL，其中最常用到的會是DML，就是常聽到的Insert、Select、Update、Delete (CRUD) (參3)。

安裝關聯性資料庫及系統管理介面
筆者選擇安裝SQL Server (MSSQL)，因為主要使用C#在開發，同樣都是微軟的搭配起來應該比較不會有怪問題，而且安裝過程中不太需要手動選擇參數，安裝過程可參考參4，之前筆者也有試過Python搭配MySQL的組合啦，如果有興趣想安裝的也可以參考看看(參5)。另外有個小提醒，除了安裝SQL Server之外，也要記得下載並安裝server管理介面SQL Server Management Studio (SSMS)，安裝完成後請重新開機。

SSMS基礎操作

如同前面提到的，要操作資料庫其實可以直接用SQL語法，但這樣學習曲線就會高很多，第一光連線問題就會卡很久，第二SQL語法不熟，所以透過SSMS這套介面，可以即刻的把結果回傳並呈現出來，這樣應該會比較有感覺，但SQL語法後續還是要學著去熟悉它的。

連線方式
先開啟SSMS，伺服器類型預設是資料庫引擎，伺服器名稱可輸入FQDN或 IP，初次使用或像筆者後續都在自己的電腦作專案，只要把伺服器名稱改成localhost或至Sql Server Configuration Manager查詢本機的IP即可(參6)。驗證預設是windows驗證，使用者預設會是Windows 使用者名稱，若Root有進行資料庫權限管理的情況下，驗證的部分則要選用SQL Server驗證，帳號密碼就是由管理者協助開通的那組囉。

建立資料庫及表單
完成連線後，對左欄的資料庫按右鍵，可以新增資料庫，再對資料庫中的資料表按右鍵，可以新建表單。成功後可對表單進行設計。

資料型別
完成上個步驟後，會看到表單的設計畫面。主要常見的別態會有

1、字串型別(char):
加上var關鍵字，可將沒用到的空間去除。後續取用資料時，若使用char時，需要加上trim()來去除空格。Char因為長度固定，故存取速度較快，空間跟速度就看需求取捨囉，另外，值得一提的是，要儲存中文字的時候，建議選擇Unicode的nchar或nvarchar，原因詳見參7。字串型別中還有Binary，但筆者並未使用到，後續有機會再研究囉。

2、數值型別:
基本為int、float。int可再依照數字的範圍優化儲存位元組，例如Tinyint、Smallint等，float則可依精準度及有效位數需求作調整，如real、decimal等。

3、布林值型別
在MSSQL中並沒有獨立的布林值型別，是透過數值型別的Bit來表示，Bit只能為0,1,null，字串值若為True及False，則會被轉成1與0。

4、日期時間型別
在作專案時常會需要紀錄日期與時間，像生日、登入時間、建立資料的日期等，這時就可以使用datetime、timestamp等型別來完成。更多詳情請參考(參8)

5、uniqueidentifier
前面小節有提到，關聯性資料庫強項就是靠一張張Table並找出關聯性合併起來。故每個Table一定要有個欄位足以代表身分，簡單來說就是唯一值，像是學號、身分證字號，絕對不可能重複出現。雖然目前設定成字串也不會有問題，但筆者後續在寫專案要進行資料庫讀取時，是使用GUID作為唯一值，會碰到一些轉型的問題，所以就還是設成uniqueidentifier囉。小提醒，即便是uniqueidentifier，還是要記得手動設定主鍵。

初始化設定
預設值或繫結一欄可以使用TSQ的語法，達到初始值設定，例如NEWID()、GETDATE()

手動建立資料
這裡補充一個小觀念，因為每筆資料都很重要，所以通常每個表單都會有唯一識別欄位、建立時間，另外，對於企業而言客戶的資料都是相當寶貴的，通常不會輕易的刪除，故會建立欄位(例如: isDelete)來實現軟刪除，只要布林值為true就視為已經被刪除了，前端就不會顯出來囉。

下SQL進行CRUD
按下上排工具列的「新增查詢」，就可直接測試SQL語法囉，SSMS中可以用滑鼠反白，決定要執行哪一行語法。反白後按下上排工具列的「執行」，就可以看到操作的結果囉。另外筆者順便測試一下，MSSQL要使用布林值作為篩選條件，確實可以下字串或是數字。

備份及匯入檔案
因筆者有換電腦工作，故嘗試著將資料庫轉移，可透過「備份」將整個資料庫輸出為.bak檔，再透過「還原資料庫」重新載入。也可以透過excel上傳(參9、參10)，當表單越來越多時，開始就要建立彼此的關聯性，這裡會牽扯到foreign key與ER Model，有機會再聊吧。

使用ADO.NET操作資料庫

接下來我們要使用ADO.NET連接到SQL server，在這個範例裡，createTime與資料庫的型別不一樣，後續在做專案的時候，對於GUID、Datetime等型別一直有點困擾，會碰到型別與Sql字串有點矛盾的地方。留給大家後續去體會囉。這裡筆者簡單嘗試了傳送布林值，透過string 的"false"或int的 0都可以成功建立唷。

static void Main(string[] args)
{
    //先建立連線的字串，並宣告連線
    string connectString = "Data Source = localhost; Initial Catalog = BlogTest; Integrated Security = SSPI";
    SqlConnection sqlConnection = new SqlConnection(connectString);
    
    //開啟連線
    sqlConnection.Open();
  
    //建立要insert至資料庫的資料
    Guid GUID = Guid.NewGuid();
    string userName = "monkey";
    int Age = 88;
    string Aka = "阿猩";
    String  createTime = "1999-09-09 09:09:09";
    bool isDelete = false; //或是int isDelete = 0;

    //將sql語法組成字串
    String sqlString = $@"insert into UserInfo (GUID, userName, Age, Aka, createTime, isDelete)
                          values('{GUID}','{userName}',{Age},'{Aka}','{createTime}','{isDelete}')";
    Console.WriteLine(sqlString);

    //執行sql語法
    SqlCommand command = new SqlCommand(sqlString, sqlConnection);
 
    //取回結果並顯示
    int result = command.ExecuteNonQuery();
    Console.WriteLine($"成功新增{result}筆資料!");
    Console.ReadLine();

    //關閉連線
    sqlConnection.Close();
}

成功的話就會顯示下面的畫面囉

而資料庫也會多出一筆資料

要做其他資料庫操作，例如查詢等等，就留給各位自行去Google SQL語法啦，也是一樣透過字串組合的概念，再送入SqlCommand，到這邊已經完成大致的工作了，但你以為這樣就結束了嗎?不!!因為透過字串的組合，會衍生出SQL-Injection的安全概念，既然可以透過字串的組合給SQL Server下達指令，那就可以讓有心人士偷偷藏一些語法在字串中，導致操作的結果會超出預期，至於在C#中要如何防範SQL-Injection的內容，就留到下篇再講啦!

參考資料

回首頁

阿猩的程式蛋糕店

熱衷於自己喜歡的事情，時間久了，就能讓生硬的內容變成一塊美味的蛋糕。