Asp.Net 操作 Sqlserver通用類

基於SqlServer的資料通用類

using System;<br />using System.Collections.Generic;<br />using System.Linq;<br />using System.Text;<br />using System.Data;<br />using System.Data.SqlClient;<br />using System.Configuration;</p><p>namespace DAL<br />{<br /> /// <summary><br /> /// 說明：資料訪問通用類，供DAL內部調用<br /> /// </summary><br /> partial class SqlDbHelper<br /> {<br /> private string connectionString;</p><p> /// <summary><br /> /// 設定資料庫連接字串<br /> /// </summary><br /> public string ConnectionString<br /> {<br /> get { return connectionString; }<br /> set { connectionString = value; }<br /> }</p><p> public SqlDbHelper()</p><p> {<br /> connectionString=ConfigurationManager.ConnectionStrings["con"].ConnectionString;</p><p> }</p><p> /// <summary><br /> /// 建構函式<br /> /// </summary><br /> /// <param name="connectstring">資料庫連接字串</param><br /> public SqlDbHelper(string connectstring)<br /> {<br /> this.ConnectionString = connectionString;<br /> }</p><p> /// <summary><br /> /// 執行一個查詢，並返回結果集<br /> /// </summary><br /> /// <param name="sql">要執行的查詢SQL文本命令</param><br /> /// <returns>返回查詢結果集</returns><br /> public DataTable ExecuteDataTable(string sql)<br /> {<br /> return ExecuteDataTable(sql, CommandType.Text, null);<br /> }<br /> /// <summary><br /> /// 執行一個查詢,並返回查詢結果<br /> /// </summary><br /> /// <param name="sql">要執行的SQL語句</param><br /> /// <param name="commandType">要執行的查詢語句的類型，如預存程序或者SQL文本命令</param><br /> /// <returns>返回查詢結果集</returns><br /> public DataTable ExecuteDataTable(string sql, CommandType commandType)<br /> {<br /> return ExecuteDataTable(sql, commandType, null);<br /> }<br /> /// <summary><br /> /// 執行一個查詢,並返回查詢結果<br /> /// </summary><br /> /// <param name="sql">要執行的SQL語句</param><br /> /// <param name="commandType">要執行的查詢語句的類型，如預存程序或者SQL文本命令</param><br /> /// <param name="parameters">Transact-SQL 陳述式或預存程序的參數數組</param><br /> /// <returns></returns><br /> public DataTable ExecuteDataTable(string sql, CommandType commandType, SqlParameter[] parameters)<br /> {<br /> DataTable data = new DataTable();//執行個體化DataTable，用於裝載查詢結果集<br /> using (SqlConnection connection = new SqlConnection(connectionString))<br /> {<br /> using (SqlCommand command = new SqlCommand(sql, connection))<br /> {<br /> command.CommandType = commandType;//設定command的CommandType為指定的CommandType<br /> //如果同時傳入了參數，則添加這些參數<br /> if (parameters != null)<br /> {<br /> foreach (SqlParameter parameter in parameters)<br /> {<br /> command.Parameters.Add(parameter);<br /> }<br /> }<br /> //通過包含查詢SQL的SqlCommand執行個體來執行個體化SqlDataAdapter<br /> SqlDataAdapter adapter = new SqlDataAdapter(command); </p><p> adapter.Fill(data);//填充DataTable<br /> }<br /> }<br /> return data;<br /> }<br /> /// <summary><br /> ///<br /> /// </summary><br /> /// <param name="sql">要執行的查詢SQL文本命令</param><br /> /// <returns></returns><br /> public SqlDataReader ExecuteReader(string sql)<br /> {<br /> return ExecuteReader(sql, CommandType.Text, null);<br /> }<br /> /// <summary><br /> ///<br /> /// </summary><br /> /// <param name="sql">要執行的SQL語句</param><br /> /// <param name="commandType">要執行的查詢語句的類型，如預存程序或者SQL文本命令</param><br /> /// <returns></returns><br /> public SqlDataReader ExecuteReader(string sql, CommandType commandType)<br /> {<br /> return ExecuteReader(sql, commandType, null);<br /> }<br /> /// <summary><br /> ///<br /> /// </summary><br /> /// <param name="sql">要執行的SQL語句</param><br /> /// <param name="commandType">要執行的查詢語句的類型，如預存程序或者SQL文本命令</param><br /> /// <param name="parameters">Transact-SQL 陳述式或預存程序的參數數組</param><br /> /// <returns></returns><br /> public SqlDataReader ExecuteReader(string sql, CommandType commandType, SqlParameter[] parameters)<br /> {<br /> SqlConnection connection = new SqlConnection(connectionString);<br /> SqlCommand command = new SqlCommand(sql, connection);<br /> //如果同時傳入了參數，則添加這些參數<br /> if (parameters != null)<br /> {<br /> foreach (SqlParameter parameter in parameters)<br /> {<br /> command.Parameters.Add(parameter);<br /> }<br /> }<br /> connection.Open();<br /> //CommandBehavior.CloseConnection參數指示關閉Reader對象時關閉與其關聯的Connection對象<br /> return command.ExecuteReader(CommandBehavior.CloseConnection);<br /> }<br /> /// <summary><br /> ///<br /> /// </summary><br /> /// <param name="sql">要執行的查詢SQL文本命令</param><br /> /// <returns></returns><br /> public Object ExecuteScalar(string sql)<br /> {<br /> return ExecuteScalar(sql, CommandType.Text, null);<br /> }<br /> /// <summary><br /> ///<br /> /// </summary><br /> /// <param name="sql">要執行的SQL語句</param><br /> /// <param name="commandType">要執行的查詢語句的類型，如預存程序或者SQL文本命令</param><br /> /// <returns></returns><br /> public Object ExecuteScalar(string sql, CommandType commandType)<br /> {<br /> return ExecuteScalar(sql, commandType, null);<br /> }<br /> /// <summary><br /> ///<br /> /// </summary><br /> /// <param name="sql">要執行的SQL語句</param><br /> /// <param name="commandType">要執行的查詢語句的類型，如預存程序或者SQL文本命令</param><br /> /// <param name="parameters">Transact-SQL 陳述式或預存程序的參數數組</param><br /> /// <returns></returns><br /> public Object ExecuteScalar(string sql, CommandType commandType, SqlParameter[] parameters)<br /> {<br /> object result = null;<br /> using (SqlConnection connection = new SqlConnection(connectionString))<br /> {<br /> using (SqlCommand command = new SqlCommand(sql, connection))<br /> {<br /> command.CommandType = commandType;//設定command的CommandType為指定的CommandType<br /> //如果同時傳入了參數，則添加這些參數<br /> if (parameters != null)<br /> {<br /> foreach (SqlParameter parameter in parameters)<br /> {<br /> command.Parameters.Add(parameter);<br /> }<br /> }<br /> connection.Open();//開啟資料庫連接<br /> result = command.ExecuteScalar();<br /> }<br /> }<br /> return result;//返回查詢結果的第一行第一列，忽略其它行和列<br /> }<br /> /// <summary><br /> /// 對資料庫執行增刪改操作<br /> /// </summary><br /> /// <param name="sql">要執行的查詢SQL文本命令</param><br /> /// <returns></returns><br /> public int ExecuteNonQuery(string sql)<br /> {<br /> return ExecuteNonQuery(sql, CommandType.Text, null);<br /> } </p><p> /// <summary><br /> /// 對資料庫執行增刪改操作<br /> /// </summary><br /> /// <param name="sql">要執行的SQL語句</param><br /> /// <param name="commandType">要執行的查詢語句的類型，如預存程序或者SQL文本命令</param><br /> /// <returns></returns><br /> public int ExecuteNonQuery(string sql, CommandType commandType)<br /> {<br /> return ExecuteNonQuery(sql, commandType, null);<br /> }<br /> /// <summary><br /> /// 對資料庫執行增刪改操作<br /> /// </summary><br /> /// <param name="sql">要執行的SQL語句</param><br /> /// <param name="commandType">要執行的查詢語句的類型，如預存程序或者SQL文本命令</param><br /> /// <param name="parameters">Transact-SQL 陳述式或預存程序的參數數組</param><br /> /// <returns></returns><br /> public int ExecuteNonQuery(string sql, CommandType commandType, SqlParameter[] parameters)<br /> {<br /> int count = 0;<br /> using (SqlConnection connection = new SqlConnection(connectionString))<br /> {<br /> using (SqlCommand command = new SqlCommand(sql, connection))<br /> {<br /> command.CommandType = commandType;//設定command的CommandType為指定的CommandType<br /> //如果同時傳入了參數，則添加這些參數<br /> if (parameters != null)<br /> {<br /> foreach (SqlParameter parameter in parameters)<br /> {<br /> command.Parameters.Add(parameter);<br /> }<br /> }<br /> connection.Open();//開啟資料庫連接<br /> count = command.ExecuteNonQuery();<br /> }<br /> }<br /> return count;//返回執行增刪改操作之後，資料庫中受影響的行數<br /> }<br /> /// <summary><br /> /// 返回當前串連的資料庫中所有由使用者建立的資料庫<br /> /// </summary><br /> /// <returns></returns><br /> public DataTable GetTables()<br /> {<br /> DataTable data = null;<br /> using (SqlConnection connection = new SqlConnection(connectionString))<br /> {<br /> connection.Open();//開啟資料庫連接<br /> data = connection.GetSchema("Tables");<br /> }<br /> return data;<br /> } </p><p> }<br />}</p><p>

 

 

以下是一個簡單增刪改查帶的調用代碼

using System;<br />using System.Collections.Generic;<br />using System.Linq;<br />using System.Text;<br />using Model;<br />using System.Data;<br />using System.Data.SqlClient;</p><p>namespace DAL<br />{<br /> /// <summary><br /> /// 對資料進行增刪改查的類<br /> /// </summary><br /> public class StudentDal<br /> {<br /> SqlDbHelper Dbhelp = new SqlDbHelper();<br /> /// <summary><br /> /// 返回記錄條數<br /> /// </summary><br /> /// <returns></returns><br /> public int Count()<br /> {<br /> string sql = "select count(1) from Student";</p><p> return int.Parse(Dbhelp.ExecuteScalar(sql).ToString());<br /> }</p><p> /// <summary><br /> /// 建立學生資訊<br /> /// </summary><br /> /// <param name="st">學生</param><br /> /// <returns></returns><br /> public bool Create(Student st)<br /> {</p><p> string sql = "insert into Student (name,age,school) values(@name,@age,@school)";<br /> SqlParameter[] paramters = new SqlParameter[]<br /> {<br /> new SqlParameter("@name",st.Name),<br /> new SqlParameter("@age",st.Age),<br /> new SqlParameter("@school",st.School)<br /> };<br /> return Dbhelp.ExecuteNonQuery(sql, CommandType.Text, paramters) > 0;</p><p> }</p><p> /// <summary><br /> /// 根據ID取出使用者實體<br /> /// </summary><br /> /// <param name="id">學生ID</param><br /> /// <returns></returns><br /> public Student Read(int id)<br /> {<br /> string sql = string.Format("select * from Student where id={0}", id);<br /> DataTable table = Dbhelp.ExecuteDataTable(sql);<br /> if (table.Rows.Count > 0)<br /> {<br /> DataRow row = table.Rows[0];<br /> Student st = new Student()<br /> {<br /> Id = int.Parse(row["id"].ToString()),<br /> Name = row["name"].ToString(),<br /> Age = int.Parse(row["age"].ToString()),<br /> School = row["school"].ToString()<br /> };</p><p> return st;<br /> }<br /> else<br /> {<br /> return null;<br /> }<br /> }</p><p> /// <summary><br /> /// 修改學生資訊<br /> /// </summary><br /> /// <param name="st"></param><br /> /// <returns></returns><br /> public bool Uudate(Student st)<br /> {<br /> string sql = "update student set [name]=@name,age=@age,school=@school where id=@id";<br /> SqlParameter[] paramters = new SqlParameter[]<br /> {<br /> new SqlParameter("@name",st.Name),<br /> new SqlParameter("@age",st.Age),<br /> new SqlParameter("@school",st.School),<br /> new SqlParameter("@id",st.Id)<br /> };</p><p> return Dbhelp.ExecuteNonQuery(sql,CommandType.Text,paramters) > 0;</p><p> }</p><p> /// <summary><br /> /// 根據學生ID刪除資訊<br /> /// </summary><br /> /// <param name="Id"></param><br /> /// <returns></returns><br /> public bool Delete(int Id)<br /> {<br /> string sql = "delete from student where id=" + Id;<br /> return Dbhelp.ExecuteNonQuery(sql)>0;<br /> }</p><p> public DataTable GetInform()<br /> {<br /> string sql = "select * from student ";<br /> return Dbhelp.ExecuteDataTable(sql);<br /> }</p><p> }<br />}<br />