DataHelper.cs 42 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980981982983984985986987988989990991992993994995996997998999100010011002100310041005100610071008100910101011101210131014101510161017101810191020102110221023102410251026102710281029103010311032103310341035103610371038103910401041104210431044104510461047104810491050105110521053105410551056105710581059106010611062106310641065106610671068106910701071107210731074107510761077107810791080108110821083108410851086108710881089109010911092109310941095109610971098109911001101110211031104110511061107110811091110111111121113111411151116111711181119112011211122112311241125112611271128112911301131
  1. using Oracle.ManagedDataAccess.Client;
  2. using System;
  3. using System.Collections.Generic;
  4. using System.Data;
  5. using System.Text;
  6. namespace ClassFile
  7. {
  8. class DataHelper
  9. {
  10. //系统默认的的连接字符串
  11. private string ConnectionStrings = UAS_PLCDataReader.Properties.Settings.Default.Properties["MES"].DefaultValue.ToString();
  12. //用户选择的数据库的连接字符串
  13. public static string DBConnectionString;
  14. public static OracleConnection connection = null;
  15. OracleCommand command = null;
  16. int ReconnectTime = 0;
  17. /// <summary>
  18. /// 执行构造函数的时候打开数据库的链接
  19. /// </summary>
  20. public DataHelper()
  21. {
  22. try
  23. {
  24. //如果选择的是默认数据则直接用配置文件的信息连接,否则选择数据库的账套信息
  25. if (DBConnectionString == null || DBConnectionString == ConnectionStrings)
  26. connection = new OracleConnection(ConnectionStrings);
  27. else
  28. connection = new OracleConnection(DBConnectionString);
  29. connection.Open();
  30. }
  31. catch (Exception e) { /*LogManager.DoLog(e.Message);*/ }
  32. }
  33. /// <summary>
  34. /// 根据表名获取该表字段数据类型
  35. /// </summary>
  36. public DataTable GetColumnDataType(string TableName)
  37. {
  38. DataTable dt = new DataTable();
  39. command = new OracleCommand("select Column_Name,Data_Type from cols where TABLE_name=upper('" + TableName + "')", connection);
  40. Reconnect(command);
  41. OracleDataAdapter ad = new OracleDataAdapter(command);
  42. ad.Fill(dt);
  43. ad.Dispose();
  44. command.Dispose();
  45. return dt;
  46. }
  47. /// <summary>
  48. /// 获取第一行第一列的信息
  49. /// </summary>
  50. public object getFieldDataByCondition(string TableName, string Field, string Condition)
  51. {
  52. DataTable dt = new DataTable();
  53. string sql = "select " + Field + " from " + TableName + " where " + Condition;
  54. command = new OracleCommand(sql, connection);
  55. Console.WriteLine(sql);
  56. Reconnect(command);
  57. OracleDataAdapter ad = new OracleDataAdapter();
  58. ad.SelectCommand = command;
  59. try
  60. {
  61. ad.Fill(dt);
  62. }
  63. catch (Exception)
  64. {
  65. connection = new OracleConnection(DBConnectionString);
  66. connection.Open();
  67. command = new OracleCommand(sql, connection);
  68. ad = new OracleDataAdapter();
  69. ad.SelectCommand = command;
  70. ad.Fill(dt);
  71. }
  72. ad.Dispose();
  73. command.Dispose();
  74. if (dt.Rows.Count > 0)
  75. {
  76. return dt.Rows[0][0];
  77. }
  78. else
  79. {
  80. return "";
  81. }
  82. }
  83. /// <summary>
  84. /// 执行打印的SQL
  85. /// </summary>
  86. /// <param name="SQL">SQL语句</param>
  87. /// <param name="Parameters">动态添加的参数,主要根据条码枪扫描获取</param>
  88. /// <returns></returns>
  89. public object ExecutePrintSQL(string SQL, params string[] Parameters)
  90. {
  91. //按照?拆分数据,然后以:Param替换问号,同时添加参数
  92. string[] Param = SQL.Split('?');
  93. int ParamNum = Param.Length - 1;
  94. //条码打印必然存在需要维护的参数
  95. if (ParamNum > 0)
  96. {
  97. StringBuilder sb = new StringBuilder();
  98. for (int i = 0; i < ParamNum; i++)
  99. {
  100. sb.Append(Param[i] + ":Param" + i);
  101. }
  102. command = new OracleCommand(sb.ToString(), connection);
  103. for (int i = 0; i < ParamNum; i++)
  104. {
  105. command.Parameters.Add("Param" + i, OracleDbType.Varchar2, Parameters[i], ParameterDirection.Input);
  106. }
  107. OracleDataAdapter ad = new OracleDataAdapter(command);
  108. DataTable dt = new DataTable();
  109. ad.Fill(dt);
  110. ad.Dispose();
  111. command.Dispose();
  112. return dt;
  113. }
  114. return "参数错误,请检查SQL语句";
  115. }
  116. /// <summary>
  117. /// 获取指定表的记录的条数 ,带条件
  118. /// </summary>
  119. /// <returns></returns>
  120. public int getRowCount(string TableName, string Condition)
  121. {
  122. DataTable dt = new DataTable();
  123. string sql = "select count(1) from " + TableName + " where " + Condition;
  124. command = new OracleCommand(sql, connection);
  125. Reconnect(command);
  126. OracleDataAdapter ad = new OracleDataAdapter(command);
  127. ad.Fill(dt);
  128. ad.Dispose();
  129. command.Dispose();
  130. return int.Parse(dt.Rows[0][0].ToString());
  131. }
  132. /// <summary>
  133. /// 获取指定表的记录的条数 ,不带条件
  134. /// </summary>
  135. /// <param name="TableName"></param>
  136. /// <returns></returns>
  137. public int getRowCount(string TableName)
  138. {
  139. DataTable dt = new DataTable();
  140. string sql = "select count(1) from " + TableName;
  141. command = new OracleCommand(sql, connection);
  142. Reconnect(command);
  143. OracleDataAdapter ad = new OracleDataAdapter(command);
  144. ad.Fill(dt);
  145. ad.Dispose();
  146. command.Dispose();
  147. return int.Parse(dt.Rows[0][0].ToString());
  148. }
  149. /// <summary>
  150. /// 通过表名和获取单行的记录
  151. /// </summary>
  152. public DataTable getFieldsDataByCondition(string TableName, string[] Fields, string Condition)
  153. {
  154. DataTable dt = new DataTable();
  155. string sql = "select ";
  156. sql += AddField(Fields);
  157. sql += " from " + TableName + " where " + Condition + " and rownum=1";
  158. command = new OracleCommand(sql, connection);
  159. Reconnect(command);
  160. OracleDataAdapter ad = new OracleDataAdapter(command);
  161. try
  162. {
  163. ad.Fill(dt);
  164. }
  165. catch (Exception)
  166. {
  167. connection = new OracleConnection(DBConnectionString);
  168. connection.Open();
  169. command = new OracleCommand(sql, connection);
  170. ad = new OracleDataAdapter();
  171. ad.SelectCommand = command;
  172. ad.Fill(dt);
  173. }
  174. ad.Dispose();
  175. command.Dispose();
  176. return dt;
  177. }
  178. /// <summary>
  179. /// 将DataTable导入到指定的表中
  180. /// </summary>
  181. /// <param name="DataTable"></param>
  182. /// <param name="TableName"></param>
  183. public void InsertDataTable(DataTable DataTable, string TableName)
  184. {
  185. for (int i = 0; i < DataTable.Rows.Count; i++)
  186. {
  187. for (int j = 0; j < DataTable.Columns.Count; j++)
  188. {
  189. }
  190. }
  191. }
  192. /// <summary>
  193. /// 按分页获取数据
  194. /// </summary>
  195. /// <param name="TableName">表名</param>
  196. /// <param name="Fields">查询字段</param>
  197. /// <param name="CurrentPage">当前页面</param>
  198. /// <param name="PageSize">页面展示条数</param>
  199. /// <param name="Caller"></param>
  200. /// <returns></returns>
  201. // SELECT * FROM (SELECT A.* FROM (SELECT* FROM datalist) A WHERE ROWNUM <= 50) WHERE ROWNUM >= 21
  202. public DataTable getFieldsDatasByPageing(string TableName, string Fields, int CurrentPage, int PageSize, string Caller, params string[] condition)
  203. {
  204. DataTable dt = new DataTable();
  205. StringBuilder sql = new StringBuilder();
  206. //先查询出配置出去的列
  207. //获取查询的列
  208. string[] caption = GetCaptionFromField(Fields);
  209. //获取对应列的描述
  210. string[] field = GetField(Fields);
  211. sql.Append(" select * from (select RowNum RN, A.* from (select ");
  212. sql.Append(AddField(caption));
  213. if (condition.Length > 0)
  214. {
  215. if (condition[0] != null && condition[0].Trim() != "")
  216. sql.Append(" from " + TableName + " where " + condition[0] + " ) A where ROWNUM <=" + CurrentPage * PageSize + ") where RN>" + (CurrentPage - 1) * PageSize);
  217. else
  218. sql.Append(" from " + TableName + ") A where ROWNUM <= " + CurrentPage * PageSize + ") where RN> " + (CurrentPage - 1) * PageSize);
  219. }
  220. command = new OracleCommand(sql.ToString(), connection);
  221. Reconnect(command);
  222. OracleDataAdapter ad = new OracleDataAdapter(command);
  223. ad.Fill(dt);
  224. ad.Dispose();
  225. command.Dispose();
  226. dt.Columns.RemoveAt(0);
  227. foreach (DataColumn dc in dt.Columns)
  228. {
  229. dc.ColumnName = field[dt.Columns.IndexOf(dc)];
  230. dc.Caption = caption[dt.Columns.IndexOf(dc)];
  231. }
  232. return dt;
  233. }
  234. /// <summary>
  235. /// 通过表名,字段和条件获取DataTable类型的数据
  236. /// </summary>
  237. public DataTable getFieldsDatasByCondition(string TableName, string[] Fields, string Condition)
  238. {
  239. DataTable dt = new DataTable();
  240. string sql = "select ";
  241. sql += AddField(Fields);
  242. sql += " from " + TableName + " where " + Condition;
  243. command = new OracleCommand(sql, connection);
  244. Reconnect(command);
  245. OracleDataAdapter ad = new OracleDataAdapter(command);
  246. try
  247. {
  248. ad.Fill(dt);
  249. }
  250. catch (Exception)
  251. {
  252. connection = new OracleConnection(DBConnectionString);
  253. connection.Open();
  254. command = new OracleCommand(sql, connection);
  255. ad = new OracleDataAdapter();
  256. ad.SelectCommand = command;
  257. ad.Fill(dt);
  258. }
  259. ad.Dispose();
  260. command.Dispose();
  261. return dt;
  262. }
  263. /// <summary>
  264. /// 通过表名,字段获取DataTable类型的数据
  265. /// </summary>
  266. public DataTable getFieldsDatas(string TableName, string Fields)
  267. {
  268. DataTable dt = new DataTable();
  269. string sql = "select ";
  270. sql += Fields;
  271. sql += " from " + TableName;
  272. command = new OracleCommand(sql, connection);
  273. Reconnect(command);
  274. OracleDataAdapter ad = new OracleDataAdapter(command);
  275. ad.SelectCommand = command;
  276. try
  277. {
  278. ad.Fill(dt);
  279. }
  280. catch (Exception)
  281. {
  282. connection = new OracleConnection(DBConnectionString);
  283. connection.Open();
  284. command = new OracleCommand(sql, connection);
  285. ad = new OracleDataAdapter();
  286. ad.SelectCommand = command;
  287. ad.Fill(dt);
  288. }
  289. ad.Dispose();
  290. command.Dispose();
  291. return dt;
  292. }
  293. /// <summary>
  294. /// 根据DataTable和指定的表名更新数据,如果需要保存新增的数据则需要传递一条Insert的SQL
  295. /// </summary>
  296. /// <param name="DataTable"></param>
  297. /// <param name="TableName"></param>
  298. /// <param name="Condition"></param>
  299. public void UpDateTableByCondition(DataTable DataTable, string TableName, string PrimaryKey, params string[] sql)
  300. {
  301. if (DataTable == null)
  302. {
  303. return;
  304. }
  305. StringBuilder sb = new StringBuilder();
  306. //预防插入的DataTable中存在不属于该表的列,在进行下一步操作之前全部剔除
  307. DataTable data = (DataTable)ExecuteSql("select Column_Name,Data_Type from cols where TABLE_name=upper('" + TableName + "')", "select");
  308. //将所有的字段拼接起来
  309. for (int i = 0; i < data.Rows.Count; i++)
  310. {
  311. sb.Append("#" + data.Rows[i]["Column_Name"].ToString());
  312. }
  313. //移除掉所有不属于该表的列
  314. for (int i = DataTable.Columns.Count - 1; i >= 0; i--)
  315. {
  316. if (!sb.ToString().Contains(DataTable.Columns[i].ColumnName.ToUpper()))
  317. {
  318. DataTable.Columns.RemoveAt(i);
  319. }
  320. }
  321. sb.Clear();
  322. //计算有多少个是新加的行,根据主键为空来进行判断
  323. int NewRowCount = 0;
  324. for (int i = 0; i < DataTable.Rows.Count; i++)
  325. {
  326. if (DataTable.Rows[i][PrimaryKey] == null || DataTable.Rows[i][PrimaryKey].ToString() == "")
  327. {
  328. NewRowCount = NewRowCount + 1;
  329. }
  330. }
  331. if (sql.Length > 0)
  332. {
  333. if (NewRowCount > 0)
  334. {
  335. //获取参数的个数
  336. int paramsNum = sql[0].Split(':').Length - 1;
  337. //解析参数的数据
  338. string[] param = GetParamFromSQL(sql[0]);
  339. //新建一个二维数组去
  340. string[][] param_array = new string[paramsNum][];
  341. //实例化每个一维数组
  342. for (int i = 0; i < paramsNum; i++)
  343. {
  344. param_array[i] = new string[NewRowCount];
  345. }
  346. //设置每列参数的索引
  347. int num = 0;
  348. //变量所有的行,如果有主键为空的则移除,不为空的进行参数的拼接
  349. for (int i = DataTable.Rows.Count - 1; i >= 0; i--)
  350. {
  351. if (DataTable.Rows[i][PrimaryKey] == null || DataTable.Rows[i][PrimaryKey].ToString() == "")
  352. {
  353. //当为新添加行的时候才去设置参数,设置过后索引+1
  354. for (int j = 0; j < paramsNum; j++)
  355. {
  356. param_array[j][num] = DataTable.Rows[i][param[j]].ToString();
  357. }
  358. DataTable.Rows.RemoveAt(i);
  359. num++;
  360. }
  361. }
  362. BatchInsertDataTable(sql[0], param, param_array);
  363. }
  364. }
  365. //不是新增行的启用更新的方法
  366. sb.Append("update " + TableName + " set ");
  367. //拼接语句,特殊处理日期
  368. foreach (DataColumn dc in DataTable.Columns)
  369. {
  370. if (!dc.DataType.ToString().Equals("System.DateTime"))
  371. {
  372. sb.Append(dc.Caption + "=:" + dc.Caption + ",");
  373. }
  374. else
  375. {
  376. sb.Append(dc.Caption + "=:" + dc.Caption + ",");
  377. }
  378. }
  379. sb.Remove(sb.Length - 1, 1);
  380. sb.Append(" where " + PrimaryKey + "=:" + PrimaryKey);
  381. command = new OracleCommand(sb.ToString(), connection);
  382. OracleDataAdapter ad = new OracleDataAdapter(command);
  383. // 参数的长度是DataTable的行数决定的
  384. command.ArrayBindCount = DataTable.Rows.Count;
  385. //默认全部是Varchar2类型的
  386. OracleDbType ob = OracleDbType.Varchar2;
  387. for (int i = 0; i < DataTable.Columns.Count; i++)
  388. {
  389. object[] param = new object[DataTable.Rows.Count];
  390. for (int j = 0; j < DataTable.Rows.Count; j++)
  391. {
  392. DateTime dt = DateTime.Now;
  393. if (DateTime.TryParse(DataTable.Rows[j][i].ToString(), out dt))
  394. {
  395. param[j] = dt;
  396. ob = OracleDbType.Date;
  397. }
  398. else
  399. {
  400. ob = OracleDbType.Varchar2;
  401. param[j] = DataTable.Rows[j][i];
  402. }
  403. }
  404. //添加批量更新的参数
  405. command.Parameters.Add(new OracleParameter(DataTable.Columns[i].Caption, ob, param, ParameterDirection.Input));
  406. }
  407. ad.UpdateCommand = command;
  408. ad.Update(DataTable);
  409. ad.Dispose();
  410. command.Dispose();
  411. }
  412. /// <summary>
  413. /// 获取DbFind的数据的DataTable的结构
  414. /// </summary>
  415. /// <param name="field"></param>
  416. /// <param name="caller"></param>
  417. /// <returns></returns>
  418. public DataTable GetDbFindDataTable(string field, string caller)
  419. {
  420. string sql = "select * from dbfindsetui where ds_caller='" + caller + "' and ds_whichui='" + field + "'";
  421. DataTable dt = (DataTable)ExecuteSql(sql, "select");
  422. if (dt.Rows.Count != 0)
  423. {
  424. //通过#号分割字段
  425. string[] dbfield = dt.Rows[0]["ds_findtoui"].ToString().Split('#');
  426. string[] cnfield = dt.Rows[0]["ds_dbcaption"].ToString().Split('#');
  427. //获取查询要查询的Table
  428. string dbtable = dt.Rows[0]["ds_tables"].ToString();
  429. //拼接查询的字段
  430. for (int i = 0; i < dbfield.Length; i++)
  431. {
  432. dbfield[i] = dbfield[i].Split(',')[0];
  433. }
  434. //新建一个空的DataTable
  435. DataTable dt1 = new DataTable();
  436. //往空的DataTable添加结构,ColumnName是中文,Caption是实际的字段名称
  437. for (int i = 0; i < cnfield.Length; i++)
  438. {
  439. dt1.Columns.Add(cnfield[i]);
  440. dt1.Columns[i].Caption = dbfield[i];
  441. }
  442. //返回一个带有结构的空的DataTable
  443. //DbFind.BindTable1 = dbtable;
  444. return dt1;
  445. }
  446. else
  447. {
  448. return null;
  449. }
  450. }
  451. /// <summary>
  452. /// 检测内容是否存在
  453. /// </summary>
  454. /// <param name="TableName"></param>
  455. /// <param name="Condition"></param>
  456. /// <returns></returns>
  457. public bool CheckExist(string TableName, string Condition)
  458. {
  459. string sql = "select count(1) from " + TableName + " where " + Condition;
  460. command = new OracleCommand(sql, connection);
  461. Reconnect(command);
  462. OracleDataAdapter ad = new OracleDataAdapter(command);
  463. DataTable dt = new DataTable();
  464. try
  465. {
  466. ad.Fill(dt);
  467. }
  468. catch (Exception)
  469. {
  470. connection = new OracleConnection(DBConnectionString);
  471. connection.Open();
  472. command = new OracleCommand(sql, connection);
  473. ad = new OracleDataAdapter();
  474. ad.SelectCommand = command;
  475. ad.Fill(dt);
  476. }
  477. ad.Dispose();
  478. command.Dispose();
  479. return int.Parse(dt.Rows[0][0].ToString()) > 0;
  480. }
  481. /// <summary>
  482. /// 直接执行SQL,同时传入SQL的类型
  483. /// </summary>
  484. /// <param name="SQL"></param>
  485. /// <param name="Type"></param>
  486. /// <returns></returns>
  487. public object ExecuteSql(string SQL, string Type, params object[] names)
  488. {
  489. object result = null;
  490. command = new OracleCommand(SQL, connection);
  491. Reconnect(command);
  492. //用来拼接参数的
  493. if (names.Length > 0)
  494. {
  495. string[] par = SQL.Split(':');
  496. //用来存参数的数组
  497. StringBuilder[] addpar = new StringBuilder[par.Length - 1];
  498. for (int i = 0; i < par.Length - 1; i++)
  499. {
  500. //新建一个char类型的数组用来存储每个字节的变量
  501. char[] c = par[i + 1].ToCharArray();
  502. addpar[i] = new StringBuilder();
  503. for (int j = 0; j < c.Length; j++)
  504. {
  505. if (c[j] != ' ' && c[j] != ',' && c[j] != ')')
  506. {
  507. addpar[i].Append(c[j]);
  508. }
  509. else
  510. {
  511. break;
  512. }
  513. }
  514. }
  515. for (int i = 0; i < addpar.Length; i++)
  516. command.Parameters.Add(new OracleParameter(addpar[i].ToString(), OracleDbType.Varchar2, names[i], ParameterDirection.Input));
  517. }
  518. Console.WriteLine(SQL);
  519. switch (Type.ToUpper())
  520. {
  521. case "SELECT":
  522. OracleDataAdapter ad = new OracleDataAdapter(command);
  523. result = new DataTable();
  524. try
  525. {
  526. ad.Fill((DataTable)result);
  527. }
  528. catch (Exception)
  529. {
  530. connection = new OracleConnection(DBConnectionString);
  531. connection.Open();
  532. command = new OracleCommand(SQL, connection);
  533. ad = new OracleDataAdapter();
  534. ad.SelectCommand = command;
  535. ad.Fill((DataTable)result);
  536. }
  537. break;
  538. case "DELETE":
  539. try
  540. {
  541. result = command.ExecuteNonQuery();
  542. }
  543. catch (Exception)
  544. {
  545. command.Connection = new OracleConnection(DBConnectionString);
  546. command.Connection.Open();
  547. result = command.ExecuteNonQuery();
  548. }
  549. break;
  550. case "UPDATE":
  551. try
  552. {
  553. result = command.ExecuteNonQuery();
  554. }
  555. catch (Exception)
  556. {
  557. command.Connection = new OracleConnection(DBConnectionString);
  558. command.Connection.Open();
  559. result = command.ExecuteNonQuery();
  560. }
  561. break;
  562. case "INSERT":
  563. try
  564. {
  565. result = command.ExecuteNonQuery();
  566. }
  567. catch (Exception)
  568. {
  569. command.Connection = new OracleConnection(DBConnectionString);
  570. command.Connection.Open();
  571. result = command.ExecuteNonQuery();
  572. }
  573. break;
  574. }
  575. command.Dispose();
  576. return result;
  577. }
  578. /// <summary>
  579. /// 为了同步BS端的条码维护,检测时允许问号的存在,在检测时默认将问号换成:Param参数
  580. /// </summary>
  581. /// <param name="SQL"></param>
  582. public void CheckSQL(string SQL)
  583. {
  584. SQL = SQL.Replace("?", ":Param");
  585. command = new OracleCommand(SQL, connection);
  586. command.ExecuteNonQuery();
  587. command.Dispose();
  588. }
  589. public int GetDistinctRowCount(string TableName, string Field)
  590. {
  591. DataTable dt = new DataTable();
  592. string sql = "select distinct count('" + Field + "') from " + TableName;
  593. command = new OracleCommand(sql, connection);
  594. Reconnect(command);
  595. OracleDataAdapter ad = new OracleDataAdapter(command);
  596. try
  597. {
  598. ad.Fill(dt);
  599. }
  600. catch (Exception)
  601. {
  602. connection = new OracleConnection(DBConnectionString);
  603. connection.Open();
  604. command = new OracleCommand(sql, connection);
  605. ad = new OracleDataAdapter();
  606. ad.SelectCommand = command;
  607. ad.Fill(dt);
  608. }
  609. ad.Dispose();
  610. command.Dispose();
  611. return int.Parse(dt.Rows[0][0].ToString());
  612. }
  613. /// <summary>
  614. /// 根据Caller获取流水号
  615. /// </summary>
  616. /// <param name="Caller"></param>
  617. /// <returns></returns>
  618. public string GetSerialNumberByCaller(string Caller)
  619. {
  620. string SerialNumber = getFieldDataByCondition("MaxNumbers", "mn_number", "mn_tablename='" + Caller + "'").ToString();
  621. UpdateByCondition("MaxNumbers", "mn_number=mn_number+1", "mn_tablename='" + Caller + "'");
  622. return SerialNumber;
  623. }
  624. /// <summary>
  625. /// 根据主键ID删除表的数据
  626. /// </summary>
  627. /// <param name="TableName">表名</param>
  628. /// <param name="ID">主键</param>
  629. /// <param name="DeleteID">需要删除主键ID的数组</param>
  630. public void DeleteDataByID(string TableName, string ID, string[] DeleteID)
  631. {
  632. string sql = "delete from " + TableName + " where " + ID + " =:DeleteID";
  633. command = new OracleCommand(sql, connection);
  634. Reconnect(command);
  635. command.ArrayBindCount = DeleteID.Length;
  636. command.Parameters.Add(new OracleParameter("DeleteID", OracleDbType.Long, DeleteID, ParameterDirection.Input));
  637. try
  638. {
  639. command.ExecuteNonQuery();
  640. }
  641. catch (Exception)
  642. {
  643. command.Connection = new OracleConnection(DBConnectionString);
  644. command.Connection.Open();
  645. command.ExecuteNonQuery();
  646. }
  647. command.Dispose();
  648. }
  649. /// <summary>
  650. /// 通过序列的名称获取序列
  651. /// </summary>
  652. /// <param name="SeqName"></param>
  653. /// <returns></returns>
  654. public string GetSEQ(string SeqName)
  655. {
  656. DataTable dt = new DataTable();
  657. dt = (DataTable)ExecuteSql("SELECT " + SeqName + ".NEXTVAL FROM DUAL", "select");
  658. return dt.Rows[0][0].ToString();
  659. }
  660. /// <summary>
  661. /// 通过序列的名称获取序列
  662. /// </summary>
  663. /// <param name="SeqName"></param>
  664. /// <returns></returns>
  665. public string[] GetSEQ(string SeqName, int Num)
  666. {
  667. DataTable dt = new DataTable();
  668. dt = (DataTable)ExecuteSql("SELECT " + SeqName + ".nextval FROM DUAL CONNECT BY LEVEL<=" + Num, "select");
  669. string[] SerialNum = new string[dt.Rows.Count];
  670. for (int i = 0; i < dt.Rows.Count; i++)
  671. {
  672. SerialNum[i] = dt.Rows[i][0].ToString();
  673. }
  674. return SerialNum;
  675. }
  676. public void SaveDataTable(DataTable dt, string TableName, string ID, params string[] sql)
  677. {
  678. if (dt == null)
  679. {
  680. return;
  681. }
  682. StringBuilder sb = new StringBuilder();
  683. //预防插入的DataTable中存在不属于该表的列,在进行下一步操作之前全部剔除
  684. DataTable data = (DataTable)ExecuteSql("select Column_Name,Data_Type from cols where TABLE_name=upper('" + TableName + "')", "select");
  685. //将所有的字段拼接起来
  686. for (int i = 0; i < data.Rows.Count; i++)
  687. {
  688. sb.Append("#" + data.Rows[i]["Column_Name"].ToString());
  689. }
  690. //移除掉所有不属于该表的列
  691. for (int i = dt.Columns.Count - 1; i >= 0; i--)
  692. {
  693. if (!sb.ToString().Contains(dt.Columns[i].ColumnName.ToUpper()))
  694. {
  695. dt.Columns.RemoveAt(i);
  696. }
  697. }
  698. sb.Clear();
  699. //计算有多少个是新加的行,根据主键为空来进行判断
  700. int NewRowCount = 0;
  701. for (int i = 0; i < dt.Rows.Count; i++)
  702. {
  703. if (dt.Rows[i][ID] == null || dt.Rows[i][ID].ToString() == "")
  704. {
  705. NewRowCount = NewRowCount + 1;
  706. }
  707. }
  708. if (sql.Length > 0)
  709. {
  710. if (NewRowCount > 0)
  711. {
  712. //获取参数的个数
  713. int paramsNum = sql[0].Split(':').Length - 1;
  714. //解析参数的数据
  715. string[] param = GetParamFromSQL(sql[0]);
  716. //新建一个二维数组去
  717. string[][] param_array = new string[paramsNum][];
  718. //实例化每个一维数组
  719. for (int i = 0; i < paramsNum; i++)
  720. {
  721. param_array[i] = new string[NewRowCount];
  722. }
  723. //设置每列参数的索引
  724. int num = 0;
  725. //变量所有的行,如果有主键为空的则移除,不为空的进行参数的拼接
  726. for (int i = dt.Rows.Count - 1; i >= 0; i--)
  727. {
  728. if (dt.Rows[i][ID] == null || dt.Rows[i][ID].ToString() == "")
  729. {
  730. //当为新添加行的时候才去设置参数,设置过后索引+1
  731. for (int j = 0; j < paramsNum; j++)
  732. {
  733. param_array[j][num] = dt.Rows[i][param[j]].ToString();
  734. }
  735. dt.Rows.RemoveAt(i);
  736. num++;
  737. }
  738. }
  739. BatchInsertDataTable(sql[0], param, param_array);
  740. }
  741. }
  742. sb.Clear();
  743. sb.Append("update " + TableName + " set ");
  744. int ColumnCount = dt.Columns.Count;
  745. int RowCount = dt.Rows.Count;
  746. //存数据的参数
  747. List<string[]> Parameter = new List<string[]>();
  748. //存参数名的参数
  749. string[] ParName = new string[ColumnCount];
  750. for (int i = 0; i < ColumnCount; i++)
  751. {
  752. ParName[i] = dt.Columns[i].ColumnName;
  753. if (i == dt.Columns.Count - 1)
  754. sb.Append(dt.Columns[i].ColumnName + "=:" + dt.Columns[i].ColumnName);
  755. else
  756. sb.Append(dt.Columns[i].ColumnName + "=:" + dt.Columns[i].ColumnName + ",");
  757. }
  758. sb.Append(" where " + ID + " =:" + ID);
  759. //先添加参数
  760. Parameter.Add(ParName);
  761. //添加参数的具体内容
  762. for (int i = 0; i < ColumnCount; i++)
  763. {
  764. string[] par = new string[RowCount];
  765. for (int j = 0; j < RowCount; j++)
  766. {
  767. par[j] = dt.Rows[j][i].ToString();
  768. }
  769. Parameter.Add(par);
  770. }
  771. BatchInsert(sb.ToString(), Parameter.ToArray());
  772. }
  773. /// <summary>
  774. /// 批量通过SQL来执行插入操作 ,参数的第一个数一个string[]数组,用来传递需要添加的参数的名称
  775. /// 之后的是名称参数数组对应的 ,所有的插入参数数据长度必须是一致的
  776. /// </summary>
  777. /// <param name="sql"></param>
  778. /// <param name="names"></param>
  779. public void BatchInsert(string sql, params object[][] names)
  780. {
  781. command = new OracleCommand(sql, connection);
  782. Reconnect(command);
  783. command.ArrayBindCount = names[1].Length;
  784. //因为第一个数组保存的是参数的名称,所以循环从1而不是0开始
  785. //将第一个数组的下标固定为0作为循环添加的参数的名称
  786. for (int i = 1; i <= names[0].Length; i++)
  787. {
  788. command.Parameters.Add(new OracleParameter(names[0][i - 1].ToString(), OracleDbType.Varchar2, names[i], ParameterDirection.Input));
  789. }
  790. try
  791. {
  792. command.ExecuteNonQuery();
  793. }
  794. catch (Exception)
  795. {
  796. command.Connection = new OracleConnection(DBConnectionString);
  797. command.Connection.Open();
  798. command.ExecuteNonQuery();
  799. }
  800. command.Dispose();
  801. }
  802. public void BatchInsertDataTable(string sql, string[] param, params object[][] param1)
  803. {
  804. command = new OracleCommand(sql, connection);
  805. Reconnect(command);
  806. command.ArrayBindCount = param1[0].Length;
  807. //因为第一个数组保存的是参数的名称,所以循环从1而不是0开始
  808. //将第一个数组的下标固定为0作为循环添加的参数的名称
  809. for (int i = 0; i < param.Length; i++)
  810. {
  811. command.Parameters.Add(new OracleParameter(param[i].ToString(), OracleDbType.Varchar2, param1[i], ParameterDirection.Input));
  812. }
  813. try
  814. {
  815. command.ExecuteNonQuery();
  816. }
  817. catch (Exception)
  818. {
  819. command.Connection = new OracleConnection(DBConnectionString);
  820. command.Connection.Open();
  821. command.ExecuteNonQuery();
  822. }
  823. command.Dispose();
  824. }
  825. /// <summary>
  826. /// 取Configs表中的配置,进行该客户是否执行某个操作
  827. /// </summary>
  828. /// <param name="Code"></param>
  829. /// <param name="Caller"></param>
  830. /// <returns></returns>
  831. public object GetConfig(string Code, string Caller)
  832. {
  833. DataTable dt = new DataTable();
  834. string sql = "select Data from configs where code='" + Code + "' and caller='" + Caller + "'";
  835. dt = (DataTable)ExecuteSql(sql, "select");
  836. if (dt.Rows.Count == 0)
  837. {
  838. return "";
  839. }
  840. else
  841. {
  842. return dt.Rows[0]["Data"];
  843. }
  844. }
  845. //将数据类型的列类型转换为DataTable
  846. public DataTable DataTypeColumnToDataTable(DataTable dt)
  847. {
  848. DataTable dt1 = new DataTable();
  849. dt1.Rows.Add();
  850. foreach (DataRow dr in dt.Rows)
  851. {
  852. dt1.Columns.Add(dr[0].ToString());
  853. int index = dt.Rows.IndexOf(dr);
  854. if (dr[1].ToString() == "NUMBER")
  855. {
  856. dt1.Rows[0][index] = 0;
  857. }
  858. if (dr[1].ToString() == "VARCHAR2")
  859. {
  860. dt1.Rows[0][index] = "这是一段文字";
  861. }
  862. if (dr[1].ToString() == "DATE")
  863. {
  864. dt1.Rows[0][index] = DateTime.Now.ToString("yyyy-MM-dd");
  865. }
  866. if (dr[1].ToString() == "FLOAT")
  867. {
  868. dt1.Rows[0][index] = 1.0;
  869. }
  870. if (dr[1].ToString() == "CLOB")
  871. {
  872. dt1.Rows[0][index] = "一段长文字";
  873. }
  874. }
  875. return dt1;
  876. }
  877. /// <summary>
  878. /// 通过条件更新
  879. /// </summary>
  880. /// <param name="TableName"></param>
  881. /// <param name="update"></param>
  882. /// <param name="condition"></param>
  883. public string UpdateByCondition(string TableName, string update, string condition)
  884. {
  885. string sql = "update " + TableName + " set " + update + " where " + condition;
  886. command = new OracleCommand(sql, connection);
  887. Console.WriteLine(sql);
  888. Reconnect(command);
  889. try
  890. {
  891. command.ExecuteNonQuery();
  892. }
  893. catch (Exception)
  894. {
  895. command.Connection = new OracleConnection(DBConnectionString);
  896. command.Connection.Open();
  897. command.ExecuteNonQuery();
  898. }
  899. command.Dispose();
  900. return sql;
  901. }
  902. /// <summary>
  903. /// 调用存储过程
  904. /// </summary>
  905. /// <param name="ProcedureName"></param>
  906. /// <param name="param"></param>
  907. public void CallProcedure(string ProcedureName, ref string[] param)
  908. {
  909. command = new OracleCommand(ProcedureName);
  910. command.Connection = connection;
  911. Reconnect(command);
  912. command.CommandText = ProcedureName;
  913. command.CommandType = CommandType.StoredProcedure;
  914. for (int i = 0; i < param.Length; i++)
  915. command.Parameters.Add(new OracleParameter(param[i].ToString(), OracleDbType.Varchar2, 200, param[i], ParameterDirection.InputOutput));
  916. try
  917. {
  918. command.ExecuteNonQuery();
  919. }
  920. catch (Exception)
  921. {
  922. command.Connection = new OracleConnection(DBConnectionString);
  923. command.Connection.Open();
  924. command.ExecuteNonQuery();
  925. }
  926. for (int i = 0; i < command.Parameters.Count; i++)
  927. param[i] = command.Parameters[i].Value.ToString();
  928. command.Dispose();
  929. }
  930. /// <summary>
  931. /// 出现异常进行回滚的执行方法
  932. /// </summary>
  933. /// <param name="SQL"></param>
  934. public void ExecuteSQLTran(params string[] SQL)
  935. {
  936. OracleTransaction tx = connection.BeginTransaction();
  937. command = new OracleCommand();
  938. command.Connection = connection;
  939. command.Transaction = tx;
  940. try
  941. {
  942. foreach (string sql in SQL)
  943. {
  944. if (!String.IsNullOrEmpty(sql))
  945. {
  946. Console.WriteLine(sql);
  947. command.CommandText = sql;
  948. try
  949. {
  950. command.ExecuteNonQuery();
  951. }
  952. catch (Exception)
  953. {
  954. command.Connection = new OracleConnection(DBConnectionString);
  955. command.Connection.Open();
  956. command.ExecuteNonQuery();
  957. }
  958. }
  959. }
  960. tx.Commit();
  961. }
  962. catch (Exception E)
  963. {
  964. tx.Rollback();
  965. throw new Exception(E.Message);
  966. }
  967. command.Dispose();
  968. }
  969. /// <summary>
  970. /// 用于将string 的数组转换成SQL的查询内容
  971. /// </summary>
  972. /// <param name="Fields"></param>
  973. /// <returns></returns>
  974. private string AddField(string[] Fields)
  975. {
  976. string sql = " ";
  977. foreach (string field in Fields)
  978. {
  979. sql += field + ",";
  980. }
  981. return sql.Substring(0, sql.Length - 1);
  982. }
  983. /// <summary>
  984. /// 通过查询的内容获取到字段的描述
  985. /// </summary>
  986. /// <param name="field"></param>
  987. /// <returns></returns>
  988. private static string[] GetCaptionFromField(string field)
  989. {
  990. string[] caption = field.Split(',');
  991. for (int i = 0; i < caption.Length; i++)
  992. {
  993. caption[i] = caption[i].Substring(0, caption[i].LastIndexOf("as")).Trim();
  994. }
  995. return caption;
  996. }
  997. /// <summary>
  998. /// 通过查询的语句获取查询的字段
  999. /// </summary>
  1000. /// <param name="field"></param>
  1001. /// <returns></returns>
  1002. private static string[] GetField(string field)
  1003. {
  1004. string[] fields = field.Split(',');
  1005. for (int i = 0; i < fields.Length; i++)
  1006. {
  1007. fields[i] = fields[i].Substring(fields[i].LastIndexOf("as") + 2, fields[i].Length - fields[i].LastIndexOf("as") - 2).Trim();
  1008. }
  1009. return fields;
  1010. }
  1011. public object GetLabelParam(string sql)
  1012. {
  1013. DataTable dt = new DataTable();
  1014. command = new OracleCommand(sql, connection);
  1015. Reconnect(command);
  1016. OracleDataAdapter ad = new OracleDataAdapter();
  1017. ad.SelectCommand = command;
  1018. try
  1019. {
  1020. ad.Fill(dt);
  1021. }
  1022. catch (Exception)
  1023. {
  1024. connection = new OracleConnection(DBConnectionString);
  1025. connection.Open();
  1026. command = new OracleCommand(sql, connection);
  1027. ad = new OracleDataAdapter();
  1028. ad.SelectCommand = command;
  1029. ad.Fill(dt);
  1030. }
  1031. if (dt.Rows.Count > 0)
  1032. {
  1033. ad.Dispose();
  1034. command.Dispose();
  1035. return dt.Rows[0][0];
  1036. }
  1037. else
  1038. {
  1039. command.Dispose();
  1040. return "";
  1041. }
  1042. }
  1043. public static string[] GetParamFromSQL(string SQL)
  1044. {
  1045. string[] par = SQL.Split(':');
  1046. //用来存参数的数组
  1047. StringBuilder[] addpar = new StringBuilder[par.Length - 1];
  1048. string[] param = new string[par.Length - 1];
  1049. for (int i = 0; i < par.Length - 1; i++)
  1050. {
  1051. //新建一个char类型的数组用来存储每个字节的变量
  1052. char[] c = par[i + 1].ToCharArray();
  1053. addpar[i] = new StringBuilder();
  1054. for (int j = 0; j < c.Length; j++)
  1055. {
  1056. if (c[j] != ' ' && c[j] != ',' && c[j] != ')')
  1057. {
  1058. addpar[i].Append(c[j]);
  1059. }
  1060. else
  1061. {
  1062. break;
  1063. }
  1064. }
  1065. }
  1066. for (int i = 0; i < par.Length - 1; i++)
  1067. {
  1068. param[i] = addpar[i].ToString();
  1069. }
  1070. return param;
  1071. }
  1072. public void Dispose()
  1073. {
  1074. }
  1075. private void Reconnect(OracleCommand cmd)
  1076. {
  1077. if (cmd.Connection.State == ConnectionState.Closed)
  1078. {
  1079. cmd.Connection.Open();
  1080. //LogManager.DoLog("超时重连");
  1081. }
  1082. }
  1083. }
  1084. }