IT工程師數位筆記本

  1     //不聲明為publc的目的：這個類只在程序集當中使用，不必對外。
  2     //盡可能地對外少暴露publc類
  3     class SQLHelper
  4     {
  5         //連接數據庫的字符串
  6         private static string strConn = ConfigurationManager.ConnectionStrings["dbconStr"].ConnectionString;
  7 
  8         /// <summary>
  9         /// 執行非查詢sql語句，如insert、delete、update
 10         /// </summary>
 11         /// <param name="sqlCmd">要執行的sql語句</param>
 12         /// <param name="parameters">sql語句中的參數</param>
 13         /// <returns>執行語句后，受到影響的行數</returns>
 14         public static int ExecuteNoQuery(string sqlCmd, params SqlParameter[] parameters) 
 15         {
 16             using (SqlConnection conn = new SqlConnection(strConn))
 17             {
 18                 conn.Open();
 19                 using (SqlCommand cmd = conn.CreateCommand()) 
 20                 {
 21                     cmd.CommandText = sqlCmd;
 22                     cmd.Parameters.AddRange(parameters);
 23                     return cmd.ExecuteNonQuery();                      
 24                 }
 25             }
 26         }
 27 
 28         /// <summary>
 29         /// 一般用于返回一個數據的查詢數據，如查詢一個學生的數學成績
 30         /// </summary>
 31         /// <param name="sqlCmd">要執行的sql語句</param>
 32         /// <param name="parameters">sql語句中的參數</param>
 33         /// <returns>查詢結果，object類型</returns>
 34         public static object ExecuteScalar(string sqlCmd, params SqlParameter[] parameters) 
 35         {
 36             using (SqlConnection conn = new SqlConnection(strConn)) 
 37             {
 38                 conn.Open();
 39                 using (SqlCommand cmd = conn.CreateCommand()) 
 40                 {
 41                     cmd.CommandText = sqlCmd;
 42                     cmd.Parameters.AddRange(parameters);
 43                     return cmd.ExecuteScalar();
 44                 }
 45             }
 46         }
 47 
 48         /// <summary>
 49         /// 將查詢結果以DataSet的形式返回
 50         /// </summary>
 51         /// <param name="sqlCmd">要執行的sql語句</param>
 52         /// <param name="parameters">sql語句中的參數</param>
 53         /// <returns>數據集合，DataSet類型</returns>
 54         public static DataSet ExecuteDataSet(string sqlCmd, params SqlParameter[] parameters) 
 55         {
 56             using (SqlConnection conn = new SqlConnection(strConn)) 
 57             {
 58                 conn.Open();
 59                 using (SqlCommand cmd = conn.CreateCommand()) 
 60                 {
 61                     cmd.CommandText = sqlCmd;
 62                     cmd.Parameters.AddRange(parameters);
 63                     SqlDataAdapter adpter = new SqlDataAdapter(cmd);
 64                     DataSet dataset = new DataSet();
 65                     adpter.Fill(dataset);
 66                     return dataset;
 67                 }
 68             }
 69         }
 70 
 71         /// <summary>
 72         /// 執行查詢結果數據量較大的查詢語句，如查詢1000個學生的信息
 73         /// </summary>
 74         /// <param name="sqlCmd">要執行的sql語句</param>
 75         /// <param name="parameters">sql語句中的參數</param>
 76         /// <returns>一個Staff類型的List</returns>
 77         public static List<Staff> ExecuteReader(string sqlCmd, params SqlParameter[] parameters) 
 78         {
 79             using (SqlConnection conn = new SqlConnection(strConn)) 
 80             {
 81                 conn.Open();
 82                 using (SqlCommand cmd = conn.CreateCommand()) 
 83                 {
 84                     cmd.CommandText = sqlCmd;
 85                     cmd.Parameters.AddRange(parameters);
 86 
 87                     SqlDataReader reader = cmd.ExecuteReader();
 88                     List<Staff> lStaff = new List<Staff>();
 89                     while (reader.Read())
 90                     {
 91                         lStaff.Add(new Staff()
 92                         {
 93                             Name = (string)reader["Name"],
 94                             Age = (int)reader["Age"],
 95                             Sex = (bool)reader["Sex"],
 96                             Height = (decimal)reader["Height"],
 97                             Salary = (decimal)reader["Salary"],
 98                             //數據庫中有些數據可以為空，如果使用強制轉換的話，會出現錯誤
 99                             //應該使用as來轉換
100                             Department = reader["Department"] as string
101                         });
102                     }
103                     return lStaff;
104                 }
105             }
106         }
107     }

            string sqlCmd = "insert into t_staff(Name,Age,Sex,Height,Salary) values('鄭冰',27,1,1.64,3000)";
            SQLHelper.ExecuteNoQuery(sqlCmd);

            string sqlCmd = "select Salary from t_staff where id > 2";
            DataSet dataset = SQLHelper.ExecuteDataSet(sqlCmd);
            DataTable table = dataset.Tables[0]; 
            foreach(DataRow row in table.Rows)
            {
                MessageBox.Show(row[0].ToString() + " == " + row["Salary"].ToString());
            }

            string sqlCmd = "select Salary from t_staff where Name = @value";
            object salary = SQLHelper.ExecuteScalar(sqlCmd, new SqlParameter("@value", "馬金"));
            MessageBox.Show(salary.ToString());

            string sqlCmd = "select * from t_staff";
            List<Staff> lStaff = SQLHelper.ExecuteReader(sqlCmd);