Posted By

mn232nm on 11/02/08


Tagged

data access


Versions (?)

Who likes this?

1 person have marked this snippet as a favorite

umang_nine


AccessHelper


 / Published in: C#
 

AccessHelper

  1. using System;
  2. using System.Collections.Generic;
  3. using System.Text;
  4. using System.Data;
  5. using System.Data.OleDb;
  6.  
  7. namespace Web.DataAccess
  8. {
  9. public class AccessHelper
  10. {
  11. #region private method & constructor
  12.  
  13. private AccessHelper() { }
  14.  
  15. private static void PrepareCommand(OleDbCommand command, OleDbConnection connection, OleDbTransaction transaction, string commandText, OleDbParameter[] commandParameters, out bool mustCloseConnection)
  16. {
  17. if (null == command) throw new ArgumentNullException("command");
  18. if (null == commandText || string.Empty == commandText) throw new ArgumentNullException("commandText");
  19.  
  20. if (ConnectionState.Closed == connection.State)
  21. {
  22. connection.Open();
  23. mustCloseConnection = true;
  24. }
  25. else
  26. {
  27. mustCloseConnection = false;
  28. }
  29.  
  30. command.Connection = connection;
  31.  
  32. command.CommandText = commandText;
  33.  
  34. if (null != transaction)
  35. {
  36. if (null == transaction.Connection) throw new ArgumentException("transaction对象commited和rollbacked需要一个已打开的事务", "transaction");
  37. command.Transaction = transaction;
  38. }
  39.  
  40. command.CommandType = CommandType.Text;
  41.  
  42. if (null != commandParameters)
  43. {
  44. AttachParameters(command, commandParameters);
  45. }
  46. }
  47.  
  48. private static void AttachParameters(OleDbCommand command, OleDbParameter[] commandParameters)
  49. {
  50. if (null == command) throw new ArgumentNullException("command");
  51.  
  52. if (null != commandParameters)
  53. {
  54. foreach (OleDbParameter p in commandParameters)
  55. {
  56. if (null != p)
  57. {
  58. if ((ParameterDirection.Input == p.Direction || ParameterDirection.InputOutput == p.Direction) && (null == p.Value))
  59. {
  60. p.Value = DBNull.Value;
  61. }
  62. command.Parameters.Add(p);
  63. }
  64. }
  65. }
  66. }
  67.  
  68. #endregion private method & constructor
  69.  
  70. #region ExecuteNonQuery
  71.  
  72. public static int ExecuteNonQuery(string connectionstring, string commandText)
  73. {
  74. return ExecuteNonQuery(connectionstring, commandText, (OleDbParameter[])null);
  75. }
  76.  
  77. public static int ExecuteNonQuery(string connectionString, string commandText, params OleDbParameter[] commandParameters)
  78. {
  79. if (null == connectionString || string.Empty == connectionString) throw new ArgumentNullException("connectionString");
  80.  
  81. using (OleDbConnection connection = new OleDbConnection(connectionString))
  82. {
  83. return ExecuteNonQuery(connection, commandText, commandParameters);
  84. }
  85. }
  86.  
  87. public static int ExecuteNonQuery(OleDbConnection connection, string commandText)
  88. {
  89. return ExecuteNonQuery(connection, commandText, (OleDbParameter[])null);
  90. }
  91.  
  92. public static int ExecuteNonQuery(OleDbConnection connection, string commandText, params OleDbParameter[] commandParameters)
  93. {
  94. if (null == connection) throw new ArgumentNullException("connection");
  95.  
  96. OleDbCommand cmd = new OleDbCommand();
  97. bool mustCloseConnection = false;
  98.  
  99. PrepareCommand(cmd, connection, (OleDbTransaction)null, commandText, commandParameters, out mustCloseConnection);
  100.  
  101. int retval = cmd.ExecuteNonQuery();
  102.  
  103. cmd.Parameters.Clear();
  104.  
  105. if (mustCloseConnection) connection.Close();
  106.  
  107. return retval;
  108. }
  109.  
  110. public static int ExecuteNonQuery(OleDbTransaction transaction, string commandText)
  111. {
  112. return ExecuteNonQuery(transaction, commandText, (OleDbParameter[])null);
  113. }
  114.  
  115. public static int ExecuteNonQuery(OleDbTransaction transaction, string commandText, params OleDbParameter[] commandParameters)
  116. {
  117. if (null == transaction) throw new ArgumentNullException("transaction");
  118. if (null != transaction && null == transaction.Connection) throw new ArgumentException("transaction对象commited和rollbacked需要一个已打开的事务", "transaction");
  119.  
  120. OleDbCommand cmd = new OleDbCommand();
  121. bool mustCloseConnection = false;
  122. PrepareCommand(cmd, transaction.Connection, transaction, commandText, commandParameters, out mustCloseConnection);
  123.  
  124. int retval = cmd.ExecuteNonQuery();
  125. cmd.Parameters.Clear();
  126.  
  127. return retval;
  128. }
  129.  
  130. #endregion ExecuteNonQuery
  131.  
  132. #region ExecuteScalar
  133.  
  134. public static object ExecuteScalar(string connectionString, string commandText)
  135. {
  136. return ExecuteScalar(connectionString, commandText, (OleDbParameter[])null);
  137. }
  138.  
  139. public static object ExecuteScalar(string connectionString, string commandText, params OleDbParameter[] commandParameters)
  140. {
  141. if (null == connectionString && string.Empty == connectionString) throw new ArgumentNullException("connectionString");
  142.  
  143. using (OleDbConnection connection = new OleDbConnection(connectionString))
  144. {
  145. return ExecuteScalar(connection, commandText, commandParameters);
  146. }
  147. }
  148.  
  149. public static object ExecuteScalar(OleDbConnection connection, string commandText)
  150. {
  151. return ExecuteScalar(connection, commandText, (OleDbParameter[])null);
  152. }
  153.  
  154. public static object ExecuteScalar(OleDbConnection connection, string commandText, params OleDbParameter[] commandParameters)
  155. {
  156. if (null == connection) throw new ArgumentNullException("connection");
  157.  
  158. OleDbCommand cmd = new OleDbCommand();
  159.  
  160. bool mustCloseConnection = false;
  161. PrepareCommand(cmd, connection, (OleDbTransaction)null, commandText, commandParameters, out mustCloseConnection);
  162.  
  163. object retval = cmd.ExecuteScalar();
  164. cmd.Parameters.Clear();
  165.  
  166. if (mustCloseConnection)
  167. connection.Close();
  168.  
  169. return retval;
  170. }
  171.  
  172. public static object ExecuteScalar(OleDbTransaction transaction, string commandText)
  173. {
  174. return ExecuteScalar(transaction, commandText, (OleDbParameter[])null);
  175. }
  176.  
  177. public static object ExecuteScalar(OleDbTransaction transaction, string commandText, params OleDbParameter[] commandParameters)
  178. {
  179. if (null == transaction) throw new ArgumentNullException("transaction");
  180. if (null != transaction && null == transaction.Connection) throw new ArgumentException("transaction对象commited和rollbacked需要一个已打开的事务", "transaction");
  181.  
  182. OleDbCommand cmd = new OleDbCommand();
  183. bool mustCloseConnection = false;
  184. PrepareCommand(cmd, transaction.Connection, transaction, commandText, commandParameters, out mustCloseConnection);
  185.  
  186. object retval = cmd.ExecuteScalar();
  187. cmd.Parameters.Clear();
  188.  
  189. return retval;
  190. }
  191.  
  192. #endregion ExecuteScalar
  193.  
  194. #region ExecuteReader
  195.  
  196. private enum OleDbConnectionOwnership
  197. {
  198. /// <summary>
  199. /// 连接对象是由SqlHelper管理的
  200. /// </summary>
  201. Internal,
  202. /// <summary>
  203. /// 连接对象是由调用者管理的
  204. /// </summary>
  205. External
  206. }
  207.  
  208. private static OleDbDataReader ExecuteReader(OleDbConnection connection, OleDbTransaction transaction, string commandText, OleDbParameter[] commandParameters, OleDbConnectionOwnership connectionOwnership)
  209. {
  210. if (null == connection) throw new ArgumentNullException("connection");
  211.  
  212. bool mustCloseConnection = false;
  213.  
  214. OleDbCommand cmd = new OleDbCommand();
  215. try
  216. {
  217. PrepareCommand(cmd, connection, transaction, commandText, commandParameters, out mustCloseConnection);
  218.  
  219. OleDbDataReader dataReader;
  220.  
  221. if (OleDbConnectionOwnership.External == connectionOwnership)
  222. {
  223. dataReader = cmd.ExecuteReader();
  224. }
  225. else
  226. {
  227. dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
  228. }
  229.  
  230. bool canClear = true;
  231.  
  232. foreach (OleDbParameter commandParameter in cmd.Parameters)
  233. {
  234. if (ParameterDirection.Input != commandParameter.Direction) canClear = false;
  235. }
  236.  
  237. if (canClear)
  238. {
  239. cmd.Parameters.Clear();
  240. }
  241.  
  242. return dataReader;
  243. }
  244. catch
  245. {
  246. if (mustCloseConnection) connection.Close();
  247. throw;
  248. }
  249. }
  250.  
  251. public static OleDbDataReader ExecuteReader(OleDbConnection connection, string commandText)
  252. {
  253. return ExecuteReader(connection, commandText, (OleDbParameter[])null);
  254. }
  255.  
  256. public static OleDbDataReader ExecuteReader(OleDbConnection connection, string commandText, params OleDbParameter[] commandParameters)
  257. {
  258. return ExecuteReader(connection, (OleDbTransaction)null, commandText, commandParameters, OleDbConnectionOwnership.External);
  259. }
  260.  
  261. public static OleDbDataReader ExecuteReader(OleDbTransaction transaction, string commandText)
  262. {
  263. return ExecuteReader(transaction, commandText, (OleDbParameter[])null);
  264. }
  265.  
  266. public static OleDbDataReader ExecuteReader(OleDbTransaction transaction, string commandText, params OleDbParameter[] commandParameters)
  267. {
  268. if (null == transaction) throw new ArgumentNullException("transaction");
  269. if (null != transaction && null == transaction.Connection) throw new ArgumentException("transaction对象commited和rollbacked需要一个已打开的事务", "transaction");
  270.  
  271. return ExecuteReader(transaction.Connection, transaction, commandText, commandParameters, OleDbConnectionOwnership.External);
  272. }
  273.  
  274. public static OleDbDataReader ExecuteReader(string connectionString, string commandText)
  275. {
  276. return ExecuteReader(connectionString, commandText, (OleDbParameter[])null);
  277. }
  278.  
  279. public static OleDbDataReader ExecuteReader(string connectionString, string commandText, params OleDbParameter[] commandParameters)
  280. {
  281. if (null == connectionString || string.Empty == connectionString) throw new ArgumentNullException("connectionString");
  282. OleDbConnection connection = null;
  283. try
  284. {
  285. connection = new OleDbConnection(connectionString);
  286. connection.Open();
  287.  
  288. return ExecuteReader(connection, (OleDbTransaction)null, commandText, commandParameters, OleDbConnectionOwnership.Internal);
  289. }
  290. catch
  291. {
  292. if (connection != null) connection.Close();
  293. throw;
  294. }
  295. }
  296.  
  297. #endregion ExecuteReader
  298.  
  299. #region ExecuteDataset
  300.  
  301. public static DataSet ExecuteDataset(string connectionString, string commandText)
  302. {
  303. return ExecuteDataset(connectionString, commandText, (OleDbParameter[])null);
  304. }
  305.  
  306. public static DataSet ExecuteDataset(string connectionString, string commandText, params OleDbParameter[] commandParameters)
  307. {
  308. if (null == connectionString || string.Empty == connectionString) throw new ArgumentNullException("connectionString");
  309.  
  310. using (OleDbConnection connection = new OleDbConnection(connectionString))
  311. {
  312. return ExecuteDataset(connectionString, commandText, commandParameters);
  313. }
  314. }
  315.  
  316. public static DataSet ExecuteDataset(OleDbConnection connection, string commandText)
  317. {
  318. return ExecuteDataset(connection, commandText, (OleDbParameter[])null);
  319. }
  320.  
  321. public static DataSet ExecuteDataset(OleDbConnection connection, string commandText, params OleDbParameter[] commandParameters)
  322. {
  323. if (null == connection) throw new ArgumentNullException("connection");
  324.  
  325. OleDbCommand cmd = new OleDbCommand();
  326. bool mustCloseConnection = false;
  327. PrepareCommand(cmd, connection, (OleDbTransaction)null, commandText, commandParameters, out mustCloseConnection);
  328.  
  329. using (OleDbDataAdapter da = new OleDbDataAdapter(cmd))
  330. {
  331. DataSet ds = new DataSet();
  332. da.Fill(ds);
  333.  
  334. cmd.Parameters.Clear();
  335.  
  336. if (mustCloseConnection)
  337. connection.Close();
  338.  
  339. return ds;
  340. }
  341. }
  342.  
  343. public static DataSet ExecuteDataset(OleDbTransaction transaction, string commandText)
  344. {
  345. return ExecuteDataset(transaction, commandText, (OleDbParameter[])null);
  346. }
  347.  
  348. public static DataSet ExecuteDataset(OleDbTransaction transaction, string commandText, params OleDbParameter[] commandParameters)
  349. {
  350. if (null == transaction) throw new ArgumentNullException("transaction");
  351. if (null != transaction && null == transaction.Connection) throw new ArgumentException("transaction对象commited和rollbacked需要一个已打开的事务", "transaction");
  352.  
  353. OleDbCommand cmd = new OleDbCommand();
  354. bool mustCloseConnection = false;
  355.  
  356. PrepareCommand(cmd, transaction.Connection, transaction, commandText, commandParameters, out mustCloseConnection);
  357.  
  358. using (OleDbDataAdapter da = new OleDbDataAdapter(cmd))
  359. {
  360. DataSet ds = new DataSet();
  361.  
  362. da.Fill(ds);
  363.  
  364. cmd.Parameters.Clear();
  365.  
  366. return ds;
  367. }
  368. }
  369.  
  370. #endregion ExecuteDataset
  371. }
  372. }

Report this snippet  

You need to login to post a comment.