Access的DBHelper.cs

using System;<br />using System.Collections.Generic;<br />using System.Text;<br />using System.Data;<br />using System.Data.OleDb;<br />using System.Configuration;<br />namespace TaobaoDAL<br />{<br /> public class DBHelper<br /> {<br /> //引導資料庫連接資料庫調用Web.Config檔案<br /> private static OleDbConnection connection;<br /> //建立串連|DataDirectory|/SchoolDB.mdb<br /> public static OleDbConnection Connection<br /> {//Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.mappath("tushu.mdb")";Persist Security Info=False<br /> get<br /> {<br /> OleDbConnection myConn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|/taobao.mdb");<br /> string connectionString = myConn.ConnectionString;<br /> if (connection == null)<br /> {<br /> connection = new OleDbConnection(connectionString);<br /> //開啟串連<br /> connection.Open();<br /> }<br /> else if (connection.State == System.Data.ConnectionState.Closed)<br /> {<br /> connection.Open();<br /> }<br /> else if (connection.State == System.Data.ConnectionState.Broken)<br /> {<br /> connection.Close();<br /> connection.Open();<br /> }<br /> return connection;<br /> }<br /> }<br /> //（無參）返回執行的行數(刪除修改更新)<br /> public static int ExecuteCommand(string safeSql)<br /> {<br /> OleDbCommand cmd = new OleDbCommand(safeSql, Connection);<br /> int result = cmd.ExecuteNonQuery();<br /> return result;<br /> }<br /> //（有參）<br /> public static int ExecuteCommand(string sql, params OleDbParameter[] values)<br /> {<br /> OleDbCommand cmd = new OleDbCommand(sql, Connection);<br /> cmd.Parameters.AddRange(values);<br /> return cmd.ExecuteNonQuery();<br /> }<br /> //（無參）返回第一行第一列(刪除修改更新)<br /> public static int GetScalar(string safeSql)<br /> {<br /> OleDbCommand cmd = new OleDbCommand(safeSql, Connection);<br /> int result = Convert.ToInt32(cmd.ExecuteScalar());<br /> return result;<br /> }<br /> //（有參）<br /> public static int GetScalar(string sql, params OleDbParameter[] values)<br /> {<br /> OleDbCommand cmd = new OleDbCommand(sql, Connection);<br /> cmd.Parameters.AddRange(values);<br /> int result = Convert.ToInt32(cmd.ExecuteScalar());<br /> return result;<br /> }<br /> //返回一個DataReader（查詢）<br /> public static OleDbDataReader GetReader(string safeSql)<br /> {<br /> OleDbCommand cmd = new OleDbCommand(safeSql, Connection);<br /> OleDbDataReader reader = cmd.ExecuteReader();<br /> return reader;<br /> }<br /> public static OleDbDataReader GetReader(string sql, params OleDbParameter[] values)<br /> {<br /> OleDbCommand cmd = new OleDbCommand(sql, Connection);<br /> cmd.Parameters.AddRange(values);<br /> OleDbDataReader reader = cmd.ExecuteReader();<br /> return reader;<br /> }</p><p> //返回一個DataTable<br /> public static DataTable GetDataSet(string safeSql)<br /> {<br /> DataSet ds = new DataSet();<br /> OleDbCommand cmd = new OleDbCommand(safeSql, Connection);<br /> OleDbDataAdapter da = new OleDbDataAdapter(cmd);<br /> da.Fill(ds);<br /> return ds.Tables[0];<br /> }<br /> public static DataTable GetDataSet(string sql, params OleDbParameter[] values)<br /> {<br /> DataSet ds = new DataSet();<br /> OleDbCommand cmd = new OleDbCommand(sql, Connection);<br /> cmd.Parameters.AddRange(values);<br /> OleDbDataAdapter da = new OleDbDataAdapter(cmd);<br /> da.Fill(ds);<br /> return ds.Tables[0];<br /> }<br /> }<br />}<br />