Accesss数据库的DBhelper类（带分页）
首先配置web.config，使配置文件连接access数据库：
<connectionStrings>
<add name="DBConnection" connectionString="Provider=Microsoft.Jet.Oledb.4.0;Data Source=|DataDirectory|#lcng.mdb" /> //数据库在本网站App_Data的文件夹中
</connectionStrings>
下面是DBhelper类：
1 using System; 2 using System.Collections.Generic; 3 using System.Text; 4 using System.Data; 5 using System.Data.OleDb; 6 using System.Configuration; 7 using System.Reflection; 8 using System.Security.Cryptography; 9 10 public class DBHelper 11 { 12 public static string connectionString = ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString; 13 //不带参数的执行命令 14 public static int ExecuteCommand(string safeSql) 15 { 16 using (OleDbConnection connection = new OleDbConnection(connectionString)) 17 { 18 connection.Open(); 19 OleDbCommand cmd = new OleDbCommand(safeSql, connection); 20 return cmd.ExecuteNonQuery(); 21 } 22 } 23 //带参数的执行命令 24 public static int ExecuteCommand(string sql, params OleDbParameter[] values) 25 { 26 using (OleDbConnection connection = new OleDbConnection(connectionString)) 27 { 28 connection.Open(); 29 OleDbCommand cmd = new OleDbCommand(sql, connection); 30 cmd.Parameters.AddRange(values); 31 return cmd.ExecuteNonQuery(); 32 } 33 } 34 35 public static int GetScalar(string safeSql) 36 { 37 using (OleDbConnection connection = new OleDbConnection(connectionString)) 38 { 39 connection.Open(); 40 OleDbCommand cmd = new OleDbCommand(safeSql, connection); 41 return Convert.ToInt32(cmd.ExecuteScalar()); 42 } 43 } 44 /// <summary> 45 /// 得到一个字段的值 46 /// </summary> 47 /// <param name="sql"></param> 48 /// <returns></returns> 49 public static string GetOneValue(string sql) 50 { 51 using (OleDbConnection connection = new OleDbConnection(connectionString)) 52 { 53 connection.Open(); 54 using (OleDbCommand cmd = new OleDbCommand(sql, connection)) 55 { 56 object obj = cmd.ExecuteScalar(); 57 cmd.Prepare(); 58 return obj != null ? obj.ToString() : string.Empty; 59 } 60 } 61 } 62 63 public static int GetScalar(string sql, params OleDbParameter[] values) 64 { 65 using (OleDbConnection connection = new OleDbConnection(connectionString)) 66 { 67 connection.Open(); 68 OleDbCommand cmd = new OleDbCommand(sql, connection); 69 cmd.Parameters.AddRange(values); 70 return Convert.ToInt32(cmd.ExecuteScalar()); 71 } 72 } 73 74 public static OleDbDataReader GetReader(string safeSql) 75 { 76 OleDbConnection connection = new OleDbConnection(connectionString); 77 connection.Open(); 78 OleDbCommand cmd = new OleDbCommand(safeSql, connection); 79 return cmd.ExecuteReader(CommandBehavior.CloseConnection); 80 } 81 82 public static OleDbDataReader GetReader(string sql, params OleDbParameter[] values) 83 { 84 OleDbConnection connection = new OleDbConnection(connectionString); 85 connection.Open(); 86 OleDbCommand cmd = new OleDbCommand(sql, connection); 87 cmd.Parameters.AddRange(values); 88 return cmd.ExecuteReader(CommandBehavior.CloseConnection); 89 } 90 91 public static DataSet GetDataSet(string safeSql) 92 { 93 using (OleDbConnection connection = new OleDbConnection(connectionString)) 94 { 95 DataSet ds = new DataSet(); 96 OleDbCommand cmd = new OleDbCommand(safeSql, connection); 97 OleDbDataAdapter da = new OleDbDataAdapter(cmd); 98 da.Fill(ds); 99 return ds; 100 } 101 } 102 103 public static DataTable GetDataSet(string sql, params OleDbParameter[] values) 104 { 105 using (OleDbConnection connection = new OleDbConnection(connectionString)) 106 { 107 DataSet ds = new DataSet(); 108 OleDbCommand cmd = new OleDbCommand(sql, connection); 109 cmd.Parameters.AddRange(values); 110 OleDbDataAdapter da = new OleDbDataAdapter(cmd); 111 da.Fill(ds); 112 return ds.Tables[0]; 113 } 114 } 115 /// <summary> 116 /// 分页使用 117 /// </summary> 118 /// <param name="query"></param> 119 /// <param name="passCount"></param> 120 /// <returns></returns> 121 private static string recordID(string query, int passCount) 122 { 123 using (OleDbConnection m_Conn = new OleDbConnection(connectionString)) 124 { 125 m_Conn.Open(); 126 OleDbCommand cmd = new OleDbCommand(query, m_Conn); 127 string result = string.Empty; 128 using (OleDbDataReader dr = cmd.ExecuteReader()) 129 { 130 while (dr.Read()) 131 { 132 if (passCount < 1) 133 { 134 result += "," + dr.GetInt32(0); 135 } 136 passCount--; 137 } 138 } 139 m_Conn.Close(); 140 m_Conn.Dispose(); 141 return result.Substring(1); 142 } 143 } 144 /// <summary> 145 /// ACCESS高效分页 146 /// </summary> 147 /// <param name="pageIndex">当前页码</param> 148 /// <param name="pageSize">分页容量</param> 149 /// <param name="strKey">主键</param> 150 /// <param name="showString">显示的字段</param> 151 /// <param name="queryString">查询字符串，支持联合查询</param> 152 /// <param name="whereString">查询条件，若有条件限制则必须以where 开头</param> 153 /// <param name="orderString">排序规则</param> 154 /// <param name="pageCount">传出参数：总页数统计</param> 155 /// <param name="recordCount">传出参数：总记录统计</param> 156 /// <returns>装载记录的DataTable</returns> 157 public static DataTable ExecutePager(int pageIndex, int pageSize, string strKey, string showString, string queryString, string whereString, string orderString, out int pageCount, out int recordCount) 158 { 159 if (pageIndex < 1) pageIndex = 1; 160 if (pageSize < 1) pageSize = 10; 161 if (string.IsNullOrEmpty(showString)) showString = "*"; 162 if (string.IsNullOrEmpty(orderString)) orderString = strKey + " asc "; 163 using (OleDbConnection m_Conn = new OleDbConnection(connectionString)) 164 { 165 m_Conn.Open(); 166 string myVw = string.Format(" ( {0} ) tempVw ", queryString); 167 string where1 = string.Empty; 168 if (String.IsNullOrEmpty(whereString)) 169 { 170 where1 = ""; 171 } 172 else 173 { 174 where1 = whereString.Trim(); 175 if (where1.StartsWith("and", StringComparison.CurrentCultureIgnoreCase)) 176 { 177 where1 = where1.Substring(3); 178 } 179 } 180 string where2 = String.IsNullOrEmpty(where1) ? "" : "where " + where1; 181 OleDbCommand cmdCount = new OleDbCommand(string.Format(" select count(*) as recordCount from {0} {1}", myVw, where2), m_Conn); 182 recordCount = Convert.ToInt32(cmdCount.ExecuteScalar()); 183 if ((recordCount % pageSize) > 0) 184 pageCount = recordCount / pageSize + 1; 185 else 186 pageCount = recordCount / pageSize; 187 OleDbCommand cmdRecord; 188 if (pageIndex == 1)//第一页 189 { 190 cmdRecord = new OleDbCommand(string.Format("select top {0} {1} from {2} {3} order by {4} ", pageSize, showString, myVw, where2, orderString), m_Conn); 191 } 192 else if (pageIndex > pageCount)//超出总页数 193 { 194 cmdRecord = new OleDbCommand(string.Format("select top {0} {1} from {2} {3} order by {4} ", pageSize, showString, myVw, "where 1=2", orderString), m_Conn); 195 } 196 else 197 { 198 int pageLowerBound = pageSize * pageIndex; 199 int pageUpperBound = pageLowerBound - pageSize; 200 string recordIDs = recordID(string.Format("select top {0} {1} from {2} {3} order by {4} ", pageLowerBound, strKey, myVw, where2, orderString), pageUpperBound); 201 cmdRecord = new OleDbCommand(string.Format("select {0} from {1} where {2} in ({3}) order by {4} ", showString, myVw, strKey, recordIDs, orderString), m_Conn); 202 203 } 204 OleDbDataAdapter dataAdapter = new OleDbDataAdapter(cmdRecord); 205 DataTable dt = new DataTable(); 206 dataAdapter.Fill(dt); 207 m_Conn.Close(); 208 m_Conn.Dispose(); 209 return dt; 210 } 211 } 212 /// <summary> 213 /// MD5加密 214 /// </summary> 215 /// <param name="str"></param> 216 /// <returns></returns> 217 public static string MD5DecryptString(string str) 218 { 219 MD5CryptoServiceProvider md5 = new MD5CryptoServiceProvider(); 220 byte[] md5Source = System.Text.Encoding.UTF8.GetBytes(str); 221 byte[] md5Out = md5.ComputeHash(md5Source); 222 return Convert.ToBase64String(md5Out); 223 } 224 225 /// <summary> 226 /// DES加密字符串 227 /// </summary> 228 /// <param name="sInputString">输入字符</param> 229 /// <param name="sKey">Key</param> 230 /// <returns>加密结果</returns> 231 public string DESEncryptString(string sInputString, string sKey) 232 { 233 try 234 { 235 byte[] data = Encoding.Default.GetBytes(sInputString); 236 byte[] result; 237 DESCryptoServiceProvider DES = new DESCryptoServiceProvider(); 238 DES.Key = ASCIIEncoding.ASCII.GetBytes(sKey); //密钥 239 DES.IV = ASCIIEncoding.ASCII.GetBytes(sKey); //初始化向量 240 ICryptoTransform desencrypt = DES.CreateEncryptor(); //加密器对象 241 result = desencrypt.TransformFinalBlock(data, 0, data.Length); //转换指定字节数组的指定区域 242 return BitConverter.ToString(result); 243 } 244 catch (Exception ex) 245 { 246 //ex.Message = "DES加密异常"; 247 throw ex; 248 } 249 } 250 /// <summary> 251 /// DES解密字符串 252 /// </summary> 253 /// <param name="sInputString">输入字符</param> 254 /// <param name="sKey">Key</param> 255 /// <returns>解密结果</returns> 256 public string DESDecryptString(string sInputString, string sKey) 257 { 258 try 259 { 260 //将字符串转换为字节数组 261 string[] sInput = sInputString.Split("-".ToCharArray()); 262 byte[] data = new byte[sInput.Length]; 263 byte[] result; 264 for (int i = 0; i < sInput.Length; i++) 265 { 266 data[i] = byte.Parse(sInput[i], System.Globalization.NumberStyles.HexNumber); 267 } 268 269 DESCryptoServiceProvider DES = new DESCryptoServiceProvider(); 270 DES.Key = ASCIIEncoding.ASCII.GetBytes(sKey); 271 DES.IV = ASCIIEncoding.ASCII.GetBytes(sKey); 272 ICryptoTransform desencrypt = DES.CreateDecryptor(); 273 result = desencrypt.TransformFinalBlock(data, 0, data.Length); 274 return Encoding.Default.GetString(result); 275 } 276 catch (Exception ex) 277 { 278 //ex.Message = "DES解密异常"; 279 throw ex; 280 } 281 } 282 }
