DbUtils.cs 5.5 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208
  1. using Logs;
  2. using System;
  3. using System.Collections.Generic;
  4. using System.Data;
  5. using System.Data.SqlClient;
  6. using System.Linq;
  7. using System.Text;
  8. using cuidian.Common;
  9. namespace cuidian.Sql
  10. {
  11. public class DbUtils
  12. {
  13. /// <summary>
  14. /// 返回第一行第一列
  15. /// </summary>
  16. /// <param name="sql"></param>
  17. /// <param name="constr"></param>
  18. /// <returns></returns>
  19. public static object ExecuteScalar(string sql, SqlConnection con)
  20. {
  21. SqlCommand cmd = null;
  22. object rtn = null;
  23. try
  24. {
  25. if (con.State == System.Data.ConnectionState.Closed)
  26. con.Open();
  27. cmd = new SqlCommand(__ProcessSql(sql), con);
  28. rtn = cmd.ExecuteScalar();
  29. }
  30. catch (Exception e)
  31. {
  32. LogHelper.Debug(e.Message, e);
  33. LogHelper.Debug("错误sql语句:"+ sql);
  34. throw e;
  35. }
  36. finally
  37. {
  38. if (con.State == ConnectionState.Open) con.Close();
  39. }
  40. return rtn;
  41. }
  42. /// <summary>
  43. /// 返回语句执行受影响的
  44. /// </summary>
  45. /// <param name="sql"></param>
  46. /// <param name="constr"></param>
  47. /// <returns></returns>
  48. public static int ExecuteNonQuery(string sql, SqlConnection con)
  49. {
  50. int rtn = 0;
  51. SqlCommand cmd;
  52. try
  53. {
  54. if (con.State == System.Data.ConnectionState.Closed)
  55. {
  56. con.Open();
  57. }
  58. cmd = new SqlCommand(__ProcessSql(sql), con);
  59. rtn = cmd.ExecuteNonQuery();
  60. }
  61. catch (Exception ex)
  62. {
  63. LogHelper.Debug(ex.Message);
  64. LogHelper.Debug(sql);
  65. throw ex;
  66. }
  67. finally
  68. {
  69. if (con.State == ConnectionState.Open) con.Close();
  70. }
  71. return rtn;
  72. }
  73. /// <summary>
  74. /// 填充datatable
  75. /// </summary>
  76. /// <param name="sql"></param>
  77. /// <param name="con"></param>
  78. /// <returns></returns>
  79. public static DataTable Fill(string sql, SqlConnection con)
  80. {
  81. SqlCommand cmd = new SqlCommand(__ProcessSql(sql), con);
  82. return Fill(cmd, con);
  83. }
  84. public static DataTable Fill(SqlCommand cmd, SqlConnection con)
  85. {
  86. DataTable dt = new DataTable();
  87. try
  88. {
  89. if (con.State == System.Data.ConnectionState.Closed)
  90. {
  91. con.Open();
  92. }
  93. cmd.Connection = con;
  94. SqlDataAdapter da = new SqlDataAdapter(cmd);
  95. int i = da.Fill(dt);
  96. }
  97. catch (Exception e)
  98. {
  99. Logs.LogHelper.Debug(cmd.CommandText);
  100. Logs.LogHelper.Debug(e.Message, e);
  101. throw e;
  102. }
  103. finally
  104. {
  105. con.Close();
  106. }
  107. return dt;
  108. }
  109. public static DataSet Fill(Dictionary<string, string> sqls, SqlConnection con)
  110. {
  111. DataSet ds = new DataSet();
  112. SqlCommand cmd;
  113. try
  114. {
  115. if (con.State == System.Data.ConnectionState.Closed)
  116. {
  117. con.Open();
  118. }
  119. foreach (var item in sqls)
  120. {
  121. cmd = new SqlCommand(item.Value, con);
  122. DataTable dt = ds.Tables[item.Key];
  123. if (dt == null)
  124. {
  125. dt = Fill(__ProcessSql( item.Value), con);
  126. dt.TableName = item.Key;
  127. ds.Tables.Add(dt);
  128. }
  129. }
  130. }
  131. catch (Exception e)
  132. {
  133. Logs.LogHelper.Debug(e.Message, e);
  134. throw e;
  135. }
  136. finally
  137. {
  138. con.Close();
  139. }
  140. return ds;
  141. }
  142. /// <summary>
  143. /// 处理特殊字符
  144. /// </summary>
  145. /// <param name="sql"></param>
  146. /// <returns></returns>
  147. private static string __ProcessSql(string sql)
  148. {
  149. //string rtn = sql.Replace("'", "''");
  150. return sql;
  151. }
  152. public static bool IsHaveObject(string name,EnumDataBaseOjbect type, string accId = "")
  153. {
  154. if(accId == "") accId = AppSeting.Instance.GetValue("default");
  155. switch (type)
  156. {
  157. case EnumDataBaseOjbect.Procedure:
  158. return __IsHaveProcedure(name,accId);
  159. break;
  160. case EnumDataBaseOjbect.Table:
  161. break;
  162. case EnumDataBaseOjbect.View:
  163. break;
  164. default:
  165. break;
  166. }
  167. return false;
  168. }
  169. private static bool __IsHaveProcedure(string name, string accid)
  170. {
  171. string sql = "select count(1) from sysobjects where id = object_id('{0}') and xtype='p'";
  172. string rtn = ExecuteScalar(string.Format(sql,name), ConnectionUtils.Instance.GetConnection(accid)).ToString();
  173. return rtn == "1";
  174. }
  175. }
  176. }