DbHelperSQL.cs
4.24 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
using System;
using System.Collections.Generic;
using System.Data;
//using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
namespace XingYe_ACS.Common
{
public class DbHelperSQL
{
/// <summary>
/// 执行SQL语句，返回影响的记录数
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <returns>影响的记录数</returns>
public static int ExecuteSql(string SQLString)
{
SQLString = SQLString.Replace("'null'", "null");
int Line = 0;
MySqlCommand mySqlCm = null;
MySqlConnection mySqlCn = null;
try
{
using (mySqlCn = new MySqlConnection(App.ConSql))
{
mySqlCn.Open();
mySqlCm = new MySqlCommand(SQLString, mySqlCn);
Line = mySqlCm.ExecuteNonQuery();
mySqlCn.Close();
}
}
#pragma warning disable CS0168 // 声明了变量“Ex”，但从未使用过
catch (Exception Ex)
#pragma warning restore CS0168 // 声明了变量“Ex”，但从未使用过
{
//if (!Ex.Message.Contains("事务在触发器中结束。批处理已中止"))
// App.ExFile.MessageError("ExecuteSql", SQLString + ";\n" + Ex.Message.Replace(Environment.NewLine, ""));
}
finally
{
mySqlCn.Dispose();
mySqlCm.Dispose();
}
return Line;
}
/// <summary>
/// 事务批量操作数据库
/// </summary>
/// <param name="SQLString">需要执行的SQL语句集合</param>
/// <returns>是否成功</returns>
public static bool SqlTransOperator(string MySQLString)
{
MySQLString = MySQLString.Replace("'null'", "null");
MySqlConnection mySqlCn = new MySqlConnection(App.ConSql);
mySqlCn.Open();
MySqlTransaction mySqlTran = mySqlCn.BeginTransaction();
string[] sqlList = MySQLString.Split(';');
List<MySqlCommand> mySqlCmdList = new List<MySqlCommand>();
foreach (string strCm in sqlList)
{
if (strCm == "") continue;
MySqlCommand mySqlcm = new MySqlCommand(strCm, mySqlCn);
mySqlcm.Transaction = mySqlTran;
mySqlCmdList.Add(mySqlcm);
}
try
{
foreach (MySqlCommand mySqlComm in mySqlCmdList)
{
mySqlComm.ExecuteNonQuery();
}
mySqlTran.Commit();
mySqlCn.Close();
}
catch (Exception Ex)
{
mySqlTran.Rollback();
App.ExFile.MessageError("SqlTransOperator", MySQLString + ";\n" + Ex.Message.Replace(Environment.NewLine, ""));
return false;
}
finally
{
foreach (MySqlCommand cm in mySqlCmdList)
{
cm.Dispose();
}
mySqlTran.Dispose();
mySqlCn.Dispose();
}
return true;
}
/// <summary>
/// 执行查询语句，返回DataSet
/// </summary>
/// <param name="SQLString">查询语句</param>
/// <returns>DataSet</returns>
public static DataSet Query(string SQLString)
{
DataSet ds = new DataSet();
MySqlConnection mySqlCn = null;
MySqlDataAdapter mySqlDa = null;
try
{
using (mySqlCn = new MySqlConnection(App.ConSql))
{
mySqlCn.Open();
mySqlDa = new MySqlDataAdapter(SQLString, mySqlCn);
mySqlDa.Fill(ds);
mySqlCn.Close();
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
mySqlCn.Dispose();
mySqlDa.Dispose();
}
return ds;
}
}
}