PageRenderTime 42ms CodeModel.GetById 24ms RepoModel.GetById 0ms app.codeStats 0ms

/Fontes/SisVendas/SisVendas.DataAccess/DataBaseHelper.cs

#
C# | 415 lines | 315 code | 91 blank | 9 comment | 52 complexity | 8235f04ae27b9455b7826675d6761bf3 MD5 | raw file
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Text;
  5. using MySql.Data;
  6. using MySql.Data.MySqlClient;
  7. using System.Configuration;
  8. using System.Data;
  9. using System.Reflection;
  10. namespace SisVendas.DataAccess
  11. {
  12. public class DataBaseHelper
  13. {
  14. #region Variáveis
  15. public static MySqlCommand _myCommand = new MySqlCommand();
  16. private static MySqlTransaction _myTransaction;
  17. #endregion
  18. #region Propriedades
  19. public MySqlConnection MyBdConnection { get; set; }
  20. public string NomeStringConexao { get; set; }
  21. #endregion
  22. #region Construtores
  23. public DataBaseHelper()
  24. {
  25. this.NomeStringConexao = ConfigurationManager.ConnectionStrings["conexao"].ConnectionString;
  26. if (string.IsNullOrEmpty(this.NomeStringConexao))
  27. throw new ApplicationException("");
  28. this.MyBdConnection = new MySqlConnection(this.NomeStringConexao);
  29. }
  30. #endregion
  31. #region Métodos Privados
  32. private string GetCorrectParameterName(string parameterName)
  33. {
  34. if (parameterName[0] != '@')
  35. {
  36. parameterName = "@" + parameterName;
  37. }
  38. return parameterName;
  39. }
  40. #endregion
  41. #region Métodos Públicos
  42. public static DataBaseHelper Create()
  43. {
  44. return new DataBaseHelper();
  45. }
  46. public void OpenConnection()
  47. {
  48. //se o objeto de transação for nulo, significa que nenhuma operação com transação esta sendo feita,
  49. //neste caso, a conexão poderá ser aberta normalmente, do contrário, a conexão utilizada junto com o objeto
  50. //de tranção deverá ser utilizada, não sendo necessária a execução do código abaixo
  51. if (_myTransaction == null)
  52. {
  53. if (this.MyBdConnection.State == System.Data.ConnectionState.Closed)
  54. {
  55. this.MyBdConnection.Open();
  56. }
  57. }
  58. }
  59. public void CloseConection()
  60. {
  61. //se o objeto de transação for nulo, significa que nenhuma operação com transação esta sendo feita,
  62. //neste caso, a conexão poderá ser aberta normalmente, do contrário, a conexão utilizada junto com o objeto
  63. //de tranção deverá ser utilizada, não sendo necessária a execução do código abaixo
  64. if (_myTransaction == null)
  65. this.MyBdConnection.Close();
  66. }
  67. public MySqlParameter BuildParameter(string nome, object valor, DbType tipo, int size)
  68. {
  69. MySqlParameter parametro = new MySqlParameter(this.GetCorrectParameterName(nome), valor);
  70. parametro.DbType = tipo;
  71. parametro.Size = size;
  72. return parametro;
  73. }
  74. public void BuildParameter(string nome, object valor, DbType tipo, int size, List<MySqlParameter> listParametros)
  75. {
  76. MySqlParameter parametro = this.BuildParameter(nome, valor, tipo, size);
  77. listParametros.Add(parametro);
  78. }
  79. public MySqlParameter BuildOutPutParameter(string nome, DbType tipo, int size)
  80. {
  81. MySqlParameter parametro = new MySqlParameter();
  82. parametro.ParameterName = this.GetCorrectParameterName(nome);
  83. parametro.DbType = tipo;
  84. parametro.Size = size;
  85. parametro.Direction = ParameterDirection.Output;
  86. return parametro;
  87. }
  88. public void BuildOutPutParameter(string nome, DbType tipo, int size, List<MySqlParameter> listParametros)
  89. {
  90. MySqlParameter parametro = this.BuildOutPutParameter(nome, tipo, size);
  91. listParametros.Add(parametro);
  92. }
  93. public void ExecuteNonQuery(MySqlCommand command)
  94. {
  95. command.ExecuteNonQuery();
  96. }
  97. public void ExecuteNonQuery(MySqlCommand command, bool openConnection)
  98. {
  99. if (openConnection)
  100. {
  101. this.OpenConnection();
  102. }
  103. this.ExecuteNonQuery(command);
  104. if (openConnection)
  105. {
  106. this.CloseConection();
  107. }
  108. }
  109. public void ExecuteCommands(params MySqlCommand[] commands)
  110. {
  111. Exception erro = null;
  112. MySqlTransaction trans = null;
  113. try
  114. {
  115. this.MyBdConnection.Open();
  116. trans = this.MyBdConnection.BeginTransaction();
  117. for (int i = 0; i < commands.Length; i++)
  118. {
  119. commands[i].Transaction = trans;
  120. this.ExecuteNonQuery(commands[i]);
  121. }
  122. trans.Commit();
  123. this.MyBdConnection.Close();
  124. }
  125. catch(Exception ex)
  126. {
  127. trans.Rollback();
  128. erro = ex;
  129. }
  130. finally
  131. {
  132. this.MyBdConnection.Close();
  133. }
  134. if (erro != null)
  135. {
  136. throw erro;
  137. }
  138. }
  139. public DataTable ExecuteQueryWithResult(string query, params MySqlParameter[] parameters)
  140. {
  141. Exception erro = null;
  142. DataTable dt = new DataTable();
  143. try
  144. {
  145. if (_myTransaction==null)
  146. this.OpenConnection();
  147. MySqlCommand command = this.MyBdConnection.CreateCommand();
  148. command.CommandText = query;
  149. if (_myTransaction != null)
  150. command.Transaction = _myTransaction;
  151. if (parameters != null)
  152. command.Parameters.AddRange(parameters);
  153. MySqlDataAdapter dataAdapter = new MySqlDataAdapter(command);
  154. dataAdapter.Fill(dt);
  155. if (_myTransaction ==null)
  156. this.CloseConection();
  157. }
  158. catch (Exception ex)
  159. {
  160. erro = ex;
  161. }
  162. finally
  163. {
  164. this.CloseConection();
  165. }
  166. if (erro != null)
  167. {
  168. throw erro;
  169. }
  170. return dt;
  171. }
  172. public List<T> ExecuteQueryWithResult<T>(string query, params MySqlParameter[] parameters) where T : new()
  173. {
  174. Exception erro = null;
  175. DataTable dt = new DataTable();
  176. try
  177. {
  178. if (_myTransaction == null)
  179. this.OpenConnection();
  180. MySqlCommand command = this.MyBdConnection.CreateCommand();
  181. command.CommandText = query;
  182. if (_myTransaction != null)
  183. command.Transaction = _myTransaction;
  184. if (parameters != null)
  185. command.Parameters.AddRange(parameters);
  186. MySqlDataAdapter dataAdapter = new MySqlDataAdapter(command);
  187. dataAdapter.Fill(dt);
  188. if (_myTransaction == null)
  189. this.CloseConection();
  190. }
  191. catch (Exception ex)
  192. {
  193. erro = ex;
  194. }
  195. finally
  196. {
  197. this.CloseConection();
  198. }
  199. if (erro != null)
  200. {
  201. throw erro;
  202. }
  203. List<T> lista = GetList<T>(dt);
  204. return lista;
  205. }
  206. //Inicia a transação
  207. public void IniciarTransacao()
  208. {
  209. this.MyBdConnection.Open();
  210. _myTransaction = this.MyBdConnection.BeginTransaction();
  211. }
  212. public void CancelarTransacao()
  213. {
  214. if (_myTransaction != null)
  215. {
  216. _myTransaction.Rollback();
  217. _myTransaction = null;
  218. this.MyBdConnection.Close();
  219. }
  220. }
  221. public void FinalizarTransacao()
  222. {
  223. if (_myTransaction != null)
  224. {
  225. _myTransaction.Commit();
  226. _myTransaction = null;
  227. this.MyBdConnection.Close();
  228. }
  229. }
  230. public void ExecuteNonQuery(string query, params MySqlParameter[] parameters)
  231. {
  232. ExecuteNonQuery(query, CommandType.Text,parameters);
  233. }
  234. public void ExecuteNonQuery(string query, CommandType tipoComando,params MySqlParameter[] parameters)
  235. {
  236. Exception erro = null;
  237. try
  238. {
  239. this.OpenConnection();
  240. MySqlCommand command = this.MyBdConnection.CreateCommand();
  241. command.CommandText = query;
  242. command.CommandType = tipoComando;
  243. //Configura o objeto command para utilizar a transaction caso a mesma tenha sido iniciada
  244. if (_myTransaction != null)
  245. command.Transaction = _myTransaction;
  246. command.Parameters.AddRange(parameters);
  247. this.ExecuteNonQuery(command);
  248. this.CloseConection();
  249. }
  250. catch (Exception ex)
  251. {
  252. erro = ex;
  253. }
  254. finally
  255. {
  256. if (_myTransaction == null)
  257. this.CloseConection();
  258. }
  259. if (erro != null)
  260. {
  261. throw erro;
  262. }
  263. }
  264. public object ExecuteScalar(string query, params MySqlParameter[] parameters)
  265. {
  266. return ExecuteScalar(query,CommandType.Text,parameters);
  267. }
  268. public object ExecuteScalar(string query, CommandType commandType, params MySqlParameter[] parameters)
  269. {
  270. Exception erro = null;
  271. Object objRetorno = -1;
  272. try
  273. {
  274. this.OpenConnection();
  275. MySqlCommand command = this.MyBdConnection.CreateCommand();
  276. command.CommandType = commandType;
  277. command.CommandText = query;
  278. //Configura o objeto para recuperar a transaction
  279. if (_myTransaction != null)
  280. command.Transaction = _myTransaction;
  281. command.Parameters.AddRange(parameters);
  282. objRetorno = command.ExecuteScalar();
  283. this.CloseConection();
  284. }
  285. catch (Exception ex)
  286. {
  287. erro = ex;
  288. }
  289. finally
  290. {
  291. if (_myTransaction == null)
  292. this.CloseConection();
  293. }
  294. if (erro != null)
  295. {
  296. throw erro;
  297. }
  298. return objRetorno;
  299. }
  300. public List<T> GetList<T>(DataTable dt) where T : new()
  301. {
  302. List<T> lista = new List<T>();
  303. foreach (DataRow r in dt.Rows)
  304. {
  305. T t = new T();
  306. foreach (PropertyInfo pi in t.GetType().GetProperties())
  307. {
  308. if (dt.Columns.IndexOf(pi.Name) > -1)
  309. {
  310. if (pi.PropertyType.Equals(typeof(Int32)))
  311. pi.SetValue(t, int.Parse(r[pi.Name].ToString()), null);
  312. if (pi.PropertyType.Equals(typeof(bool)))
  313. pi.SetValue(t, bool.Parse(r[pi.Name].ToString()), null);
  314. if (pi.PropertyType.Equals(typeof(string)))
  315. pi.SetValue(t,r[pi.Name].ToString(), null);
  316. }
  317. }
  318. lista.Add(t);
  319. }
  320. return lista;
  321. }
  322. #endregion
  323. }
  324. }