精简Command版SqlHelper

我在写CSharp步伐对数据库举利用用时发现Connection对象起到了连接数据库的做用，实际实行SQL语句使用的是Command对象的方法，以是对SqlHelper举行了重写，详细如下：

一、创建一个ParameterCommand对象，只包罗CommandText和Parameters属性，紧张用于以事件的方式批量实行SQL语句，我感觉比创建List commandTexts和List> paras两个参数方便，也不轻易堕落

1. public class ParameterCommand
2. {
4. private List<DbParameter> paras = new List<DbParameter>();
6. public string CommandText { get; set; }
8. public List<DbParameter> Parameters
9. {
10. get
11. {
12. return paras;
13. }
14. }
16. }

复制代码

二、精简Command版SqlHelper代码如下，传入Command对象做为参数用于实行SQL语句

1. public static class SqlHelper
2. {
4. private static void ResetCommandProperty(DbCommand command, string commandText, params DbParameter[] paras)
5. {
6. command.Parameters.Clear();
7. command.CommandText = commandText;
8. command.Parameters.AddRange(paras);
9. }
11. public static void ExecuteNonQuery(DbCommand command, List<ParameterCommand> paraCommands)
12. {
13. command.Transaction = command.Connection.BeginTransaction();
14. foreach (ParameterCommand paraCommand in paraCommands)
15. {
16. try
17. {
18. ResetCommandProperty(command, paraCommand.CommandText,paraCommand.Parameters.ToArray());
19. command.ExecuteNonQuery();
20. }
21. catch (Exception ex)
22. {
23. command.Transaction.Rollback();
24. throw ex;
25. }
26. }
27. command.Transaction.Commit();
28. }
30. public static void ExecuteNonQuery(DbCommand command, string commandText, params DbParameter[] paras)
31. {
32. ResetCommandProperty(command, commandText, paras);
33. command.ExecuteNonQuery();
34. }
36. public static DbDataReader ExecuteReader(DbCommand command, ParameterCommand paraCommand)
37. {
38. ResetCommandProperty(command, paraCommand.CommandText, paraCommand.Parameters.ToArray());
39. return command.ExecuteReader();
40. }
42. public static DbDataReader ExecuteReader(DbCommand command, string commandText, params DbParameter[] paras)
43. {
44. ResetCommandProperty(command, commandText, paras);
45. return command.ExecuteReader();
46. }
48. public static object ExecuteScalar(DbCommand command, ParameterCommand paraCommand)
49. {
50. ResetCommandProperty(command, paraCommand.CommandText,paraCommand.Parameters.ToArray());
51. return command.ExecuteScalar();
52. }
54. public static object ExecuteScalar(DbCommand command, string commandText, params DbParameter[] paras)
55. {
56. ResetCommandProperty(command, commandText, paras);
57. return command.ExecuteScalar();
58. }
60. public static DataTable ExecuteTable(DbCommand command, ParameterCommand paraCommand)
61. {
62. return ExecuteTable(command, paraCommand.CommandText, paraCommand.Parameters.ToArray());
63. }
65. public static DataTable ExecuteTable(DbCommand command, string commandText, params DbParameter[] paras)
66. {
67. DataTable table = new DataTable();
68. ResetCommandProperty(command, commandText, paras);
69. using (DbDataAdapter adapter = DbProviderFactories.GetFactory(command.Connection).CreateDataAdapter())
70. {
71. adapter.SelectCommand = command;
72. adapter.Fill(table);
73. }
74. return table;
75. }
77. }

复制代码

三、封装的通用DatabaseClient

1. public abstract class DatabaseClient
2. {
3. private DbConnection connection;
5. public abstract DbConnection GetConnection();
7. private DbCommand GetCommand()
8. {
9. if (connection == null)
10. {
11. connection = GetConnection();
12. }
13. if (connection.State == ConnectionState.Broken)
14. {
15. connection.Close();
16. }
17. if (connection.State == ConnectionState.Closed)
18. {
19. connection.Open();
20. }
21. return connection.CreateCommand();
22. }
24. public void ExecuteNonQuery(List<ParameterCommand> paraCommands)
25. {
26. using (DbCommand command = GetCommand())
27. {
28. SqlHelper.ExecuteNonQuery(command, paraCommands);
29. }
30. }
32. public void ExecuteNonQuery(string commandText, params DbParameter[] paras)
33. {
34. using (DbCommand command = GetCommand())
35. {
36. SqlHelper.ExecuteNonQuery(command, commandText, paras);
37. }
38. }
40. public DbDataReader ExecuteReader(ParameterCommand paraCommand)
41. {
42. using (DbCommand command = GetCommand())
43. {
44. return SqlHelper.ExecuteReader(command, paraCommand);
45. }
46. }
48. public DbDataReader ExecuteReader(string commandText, params DbParameter[] paras)
49. {
50. using (DbCommand command = GetCommand())
51. {
52. return SqlHelper.ExecuteReader(command, commandText, paras);
53. }
54. }
56. public object ExecuteScalar(ParameterCommand paraCommand)
57. {
58. using (DbCommand command = GetCommand())
59. {
60. return SqlHelper.ExecuteScalar(command, paraCommand);
61. }
62. }
64. public object ExecuteScalar(string commandText, params DbParameter[] paras)
65. {
66. using (DbCommand command = GetCommand())
67. {
68. return SqlHelper.ExecuteScalar(command, commandText, paras);
69. }
70. }
72. public DataTable ExecuteTable(ParameterCommand paraCommand)
73. {
74. using (DbCommand command = GetCommand())
75. {
76. return SqlHelper.ExecuteTable(command, paraCommand);
77. }
78. }
80. public DataTable ExecuteTable(string commandText, params DbParameter[] paras)
81. {
82. using (DbCommand command = GetCommand())
83. {
84. return SqlHelper.ExecuteTable(command, commandText, paras);
85. }
86. }
88. }

复制代码

四、举个栗子：MySQL版客户端

1. public class MySqlClient : DatabaseClient
2. {
3. private string connectionString;
5. public MySqlClient(string dataSource, string userName, string password)
6. {
7. connectionString = "DataSource=" + dataSource + ";UserName=" + userName + ";Password=" + password + ";Charset=utf8";
8. }
10. public MySqlClient(string dataSource, string userName, string password, string database)
11. {
12. connectionString = "DataSource=" + dataSource + ";UserName=" + userName + ";Password=" + password + "Database=" + database + ";Charset=utf8";
13. }
15. public override System.Data.Common.DbConnection GetConnection()
16. {
17. return new MySqlConnection(connectionString);
18. }
19. }

复制代码