c#(資料庫操作的類)

View Code

  1 using System;  2 using System.Collections.Generic;  3 using System.Text;  4 using System.Data;  5 using System.Data.SqlClient;  6   7 namespace GoodsDAL  8 {  9     /// <summary> 10     /// 對資料庫操作的類 11     /// </summary> 12     public class SQLHelper 13     { 14         /// <summary> 15         /// 資料庫連接函數 16         /// </summary> 17         public SqlConnection getConnection(Boolean isClose) 18         { 19             String conString = "Data Source = .; Initial Catalog = goods; User id = sa; password = sa"; 20             SqlConnection con = new SqlConnection(conString); 21             if (isClose) 22             { 23                 con.Open(); 24             } 25             else 26             { 27                 con.Close(); 28             } 29             return con; 30         } 31  32         /// <summary> 33         /// 執行查詢語句 34         /// </summary> 35         /// <param name="cmdText"></param> 36         /// <param name="cmdType"></param> 37         /// <param name="par"></param> 38         /// <returns>第一行第一列的值</returns> 39         public object exeScalar(string cmdText, CommandType cmdType, params SqlParameter[] par) 40         { 41             SqlCommand cmd = new SqlCommand(cmdText, getConnection(true)); 42             cmd.CommandType = cmdType; 43             if (par != null) 44             { 45                 foreach (SqlParameter p in par) 46                 { 47                     cmd.Parameters.Add(p); 48                 } 49             } 50             object result = Convert.ToInt32(cmd.ExecuteScalar()); 51             getConnection(false); 52             return result; 53         } 54  55         /// <summary> 56         /// 執行更新語句  57         /// </summary> 58         /// <param name="cmdText"></param> 59         /// <param name="cmdType"></param> 60         /// <param name="par"></param> 61         /// <returns>受影響的行數</returns> 62         public int exeNonQuery(string cmdText, CommandType cmdType, params SqlParameter[] par) 63         { 64             SqlCommand cmd = new SqlCommand(cmdText, getConnection(true)); 65             cmd.CommandType = cmdType; 66             if (par != null) 67             { 68                 foreach (SqlParameter p in par) 69                 { 70                     cmd.Parameters.Add(p); 71                 } 72             } 73             int result = cmd.ExecuteNonQuery(); 74             getConnection(false); 75             return result; 76         } 77  78         /// <summary> 79         /// 讀取資料庫中的資料 80         /// </summary> 81         /// <param name="cmdText"></param> 82         /// <param name="cmdType"></param> 83         /// <param name="par"></param> 84         /// <returns>SqlDataReader</returns> 85         public SqlDataReader exeDataReader(string cmdText, CommandType cmdType, params SqlParameter[] par) 86         { 87             SqlCommand cmd = new SqlCommand(cmdText, getConnection(true)); 88             cmd.CommandType = cmdType; 89             if (par != null) 90             { 91                 foreach (SqlParameter p in par) 92                 { 93                     cmd.Parameters.Add(p); 94                 } 95             } 96             SqlDataReader dataReader = cmd.ExecuteReader(); 97             getConnection(false); 98             return dataReader; 99         }100 101         /// <summary>102         /// 擷取資料庫表集合103         /// </summary>104         /// <param name="cmdText"></param>105         /// <param name="cmdType"></param>106         /// <param name="par"></param>107         /// <returns>DataSet集</returns>108         public DataSet exeGetDataSet(string cmdText, CommandType cmdType, params SqlParameter[] par)109         {110             DataSet ds = new DataSet();111             SqlCommand cmd = new SqlCommand(cmdText, getConnection(true));112             cmd.CommandType = cmdType;113             if (par != null)114             {115                 foreach (SqlParameter p in par)116                 {117                     cmd.Parameters.Add(p);118                 }119             }120             SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);121             dataAdapter.Fill(ds);122             return ds;123         }124     }125 }