訪問數據庫 - lcxBlog－IT工程師數位筆記本

文章出處

1.　連接數據庫

　　在做項目的時候，我們都需要通過程序來訪問SQL Server

　　通過ADO.NET 這一技術，我們可以在程序中，向數據庫提交執行SQL語句的一堆類。

　　本機訪問直接訪問“Windows驗證”，但是一般項目都是單獨的數據庫服務器，程序在另外一臺電腦上連接SQLServer，保障安全操作。

　　 在做項目過程中不要啟用sa賬戶，而是建立專用的賬戶，這是基于安全考慮。

　　啟用sa賬戶的方法：進入屬性頁面后，可以啟用sa賬戶和修改密碼

2.　連接數據庫的字符串　　

在C#代碼中，用來連接數據庫的字符串格式：

Data Source=127.0.0.1,1433;

Initial Catalog=mydb;　　

User ID=sa;　　

Password=123456

　　Data Source 要連接的數據庫的IP地址和端口，IP地址,端口，IP地址必須填寫，端口可以不填寫。連接本機的話填 . 或者127.0.0.1 就可以了；其他機子的話，填寫IP地址，如192.168.1.100。

　　Initial Catalog 要訪問的數據庫名

　　User ID 用戶名

　　Password 密碼

3.　使用代碼連接數據庫

　　要想使用SqlConnection類必須引用下面的命名空間： using System.Data.SqlClient;

　　通過SqlConnection類創建到SQLServer的連接,SqlConnection代表一個數據庫連接。

　　string strConn = "Data Source=127.0.0.1,1433;Initial Catalog=MyTest;User ID=sa;Password=sqlserver2012";
    using (SqlConnection conn = new SqlConnection(strConn))
　　{
    　　conn.Open();
　　　　 // do something          
　　}

　　為什么連接數據庫的時候，使用了關鍵字 using ？

　　1. 通過使用using，可以達到簡化資源釋放的目的，凡是繼承了IDisposable 接口的類都可以通過using來達到自動釋放資源的目的。執行到 } 這里的時候，就會自動執行釋放資源的方法。我們不必再寫close()、Dispose()等方法來關閉和釋放資源。這也是微軟提供的一個簡化資源釋放的方式。

　　2. 如果數據庫的連接一直被占用的話，假如占用的客戶端很多時，服務器就會不堪重負了。另外，由于網絡的不穩定性，容易造成不必要的麻煩。所以在需要對數據庫進行操作的時候，去開啟數據庫的連接，不需要的時候及時地關閉。這樣服務器就可以同時承受更多客戶端的訪問。

 1             string strConn = "Data Source=127.0.0.1,1433;Initial Catalog=MyTest;User ID=sa;Password=sqlserver2012";
 2             using (SqlConnection conn = new SqlConnection(strConn))
 3             {
 4                 conn.Open();
 5                 //通過SqlCommand類向數據庫發出指令
 6                 using (SqlCommand cmd = conn.CreateCommand())
 7                 {
 8                     //直接拼接字符串的查詢語句，在輸入 1' or '1' = '1 后，會出現SQL注入漏洞攻擊
 9                     //cmd.CommandText = "select Salary from T_Staff WHERE Name = '" + tBoxStaffName.Text + "'";
10 
11                     //可以擁有多個參數
12                     cmd.CommandText = "select Salary from T_Staff where Name = @Name and Age = @Age";
13                     //需要對多個參數進行依次賦值
14                     cmd.Parameters.Add(new SqlParameter("@Name", tBoxStaffName_Copy.Text));
15                     cmd.Parameters.Add(new SqlParameter("@Age", tBoxStaffAge.Text));
16 
17                     //用來從服務器中讀取數據的類
18                     using (SqlDataReader reader = cmd.ExecuteReader())
19                     {
20                         while (reader.Read())
21                         {
22                             MessageBox.Show(tBoxStaffName_Copy.Text + "的年齡是" + tBoxStaffAge.Text + "，工資是：" + reader["Salary"]);
23                         }
24                     }
25                 }
26             }

4.　SQLCommand類

　　向數據庫發出指令的類

　　要執行的SQL語句存儲在 CommandText 這個屬性中。

　　常用的方法：

　　　 1. void ExecuteNonQuery() 一般用來執行Update、Delete、Insert類型的sql語句，也就是執行非查詢類型的sql語句。

　　　 2. Object ExecuteScalar() 返回一個Object類型的值，一般用來執行只有一行或一列的返回值的SQL語句。

　　　 3. SqlDataReader ExecuteReader() 用來執行有多條查詢結果的SQL語句。

5.　SQLDataReader類　

　　通過它，我們可以從服務器中讀取數據，一般來說這些數據會比較大。它所獲得的查詢結果是放在數據庫中的，如果將這些數據傳到客戶端的話，會占用客戶端太多的內存。所以這個類在查詢結果數據量較大的時候使用。

　　讀取這些數據的方法：

　　我們可以通過索引器或者GetString、GetInt32等方法來獲取數據。

　　注意：

　　　　1.　　通過索引器來讀取數據時，獲取的數據的類型是Object類型，在項目中要將這些數據強轉為對應的類型。

　　　　2.　　使用SQLDataReader來讀取數據時，必須保證客戶端與數據的連接不能斷開；也就是說，我們必須在打開數據庫連接之后，釋放數據庫連接之前使用它。

　　while ( reader.Read() )
    {
            Console.WriteLine( reader[0] ): //獲取第一列的數據
            Console.WriteLine( reader["Name"] ):   //獲取列名為Name這一列的數據
            Console.WriteLine( reader.GetString(1) ): //第2列為string類型，獲取第2列的數值，string類型
　　 }

　　初始時，指針指向第一條數據之前，每調用一次 reader指針向下移動一格，只要沒有移動到最后一格之后，Read() 的返回值就為true。

　　每調用一次Read()，reader就會從數據庫中獲取一條數據，并且傳送到客戶端上。所以在我們停止獲取數據之前，必須保證數據庫的連接不能斷開。

6.　DataSet類

　　它相當于一個復雜集合，如 List

　　與SqlDataReader 不同的是，我們需要先將數據從服務器傳送到客戶端的DataSet中，然后客戶端直接從這里面獲取數據。所以，在所查詢的數據量較小的時候，使用DataSet可以減輕服務器的負擔。

　　使用DataSet來讀取數據：

 1             string strConn = "Data Source=127.0.0.1,1433;Initial Catalog=MyTest;User ID=sa;Password=sqlserver2012";
 2             using (SqlConnection conn = new SqlConnection(strConn)) 
 3             {
 4                 conn.Open();
 5                 using (SqlCommand cmd = conn.CreateCommand()) 
 6                 {
 7                     cmd.CommandText = "select * from T_Staff";
 8 
 9                     //SqlDataAdapter是一個幫我們把SqlCommand查詢結果填充到DataSet中的類
10                     SqlDataAdapter adapter = new SqlDataAdapter(cmd);
11                     //DataSet相當于本地的一個復雜的集合（就像List一樣）
12                     DataSet dataSet = new DataSet();
13                     //執行查詢語句，并且將查詢結果填充到DataSet中
14                     adapter.Fill(dataSet);
15 
16                     //把查詢的結果放入表中
17                     DataTableCollection tableColl = dataSet.Tables;
18                     DataTable table = tableColl[0];
19                     //集合中放的是每一行的數據
20                     DataRowCollection rowsColl = table.Rows;
21 
22                     //使用循環來獲取每一行的數據
23                     foreach (DataRow row in rowsColl)
24                     {
25                         lStaff.Add(new Staff()
26                         {
27                             Name = (string)row["Name"],
28                             Age = (int)row["Age"],
29                             Sex = (bool)row["Sex"],
30                             Height = (decimal)row["Height"],
31                             Salary = (decimal)row["Salary"],
32                             Department = (string)row["Department"]
33                         });
34                     }
35                 }
36             }

7.　SQL語句參數化查詢

　　采用直接拼接sql語句字符串的方法

　　 cmd.CommandText = "select Salary from T_Staff WHERE Name = '" + tBoxStaffName.Text + "'";

　　這里的tBoxStaffName.Text 通常是客戶端的文本框上的內容，這樣做容易出現SQL注入漏洞攻擊。

　 cmd.CommandText = "select Salary from T_Staff WHERE Name = '1' or '1' = '1'";

　　執行上的語句后，會把表中所有員工的工資都查出來，造成了數據的泄漏。

　　為了避免這樣的問題，我們使用參數化查詢。

　　 1. 先將參數放入sql語句中，參數的格式： @Name ，必須以@開頭

　　 2. 對參數進行賦值

                    //直接拼接字符串的查詢語句，在輸入 1' or '1' = '1 后，會出現SQL注入漏洞攻擊
                    //cmd.CommandText = "select Salary from T_Staff WHERE Name = '" + tBoxStaffName.Text + "'";

                    //可以擁有多個參數
                    cmd.CommandText = "select Salary from T_Staff where Name = @Name and Age = @Age";
                    //需要對多個參數進行依次賦值
                    cmd.Parameters.Add(new SqlParameter("@Name", tBoxStaffName_Copy.Text));
                    cmd.Parameters.Add(new SqlParameter("@Age", tBoxStaffAge.Text));