.net基础---数据库操作
ADO.NET
ADO.NET是.NET数据库的访问架构，ADO是ActiveX Data Objects的缩写。ADO.NET是数据库应用程序和数据源之间沟通的桥梁，主要提供一个面向对象的数据访问架构，用于快速实现对数据库进行操作（curd）
ADO.NET中的五个主要对象
- 1、DbConnection:数据库连接对象，主要用于实现程序与数据库之间建立连接。需要注意的是：在开发过程中尽量避免过于频繁的创建/释放connection，因为过多的数据库连接可能会导致数据性能瓶颈。
- 2、DbCommand:数据库指令对象，主要是对数据库发送相关指令操作数据库中数据。
- 3、DbDataAdapter：数据适配器，主要是可以实现数据源和dataset/datatable之间的数据传输适配。它可以实现将数据库数据适配到dataset中（通过Fill方法），也可以实现将dataset中的数据变更更新到数据库中（通过Update方法）。
- 4、DataSet:数据集，数据暂存区，它是支持ADO.NET断开式、分布式数据方案的核心对象，是实现基于非连接的数据查询的核心组件。DataSet可以视为是数据库中的指定数据被复制内存中而创建的小型数据库。DataSet本身只是一个数据暂存区，它没有能力直接与数据库沟通的，它与数据库之间一般是由DbDataAdapter来实现的。
- 5、DbDataReader:它提供了顺序的，只读的方式读取Command对象获得的数据结果集，DataReader会以独占的方式打开数据库连接。DataReader只执行读操作，并且每次只在内存缓冲区里存储结果集的一条数据，所以使用Datareader对象的效率比较高，如果要查询大量数据，同时不需要修改数据时，DataReader是非常好的选择。
DbHelper
1 public class MysqlDbHelper 2 { 3 public static string connectionString = "Server=localhost;Port=3306;Database=myDataBase;Uid=myUsername;Pwd=myPassword;"; 4 public static MySqlConnection GetConnection() 5 { 6 return new MySqlConnection(connectionString); 7 } 8 9 #region sync 10 11 public static int ExecuteNonQuery(string cmdText, CommandType commandType = CommandType.Text, Dictionary<string, object> parameters = null) 12 { 13 using (var connection =GetConnection()) 14 using (var command = new MySqlCommand()) 15 { 16 PrepareCommand(command, connection,cmdText, commandType, null,parameters); 17 return command.ExecuteNonQuery(); 18 } 19 } 20 21 public object ExecuteScalar(string cmdText, CommandType commandType = CommandType.Text, Dictionary<string, object> parameters = null) 22 { 23 using (var connection = GetConnection()) 24 using (var command = new MySqlCommand()) 25 { 26 PrepareCommand(command,connection, cmdText, commandType, null, parameters); 27 return command.ExecuteScalar(); 28 } 29 30 } 31 32 public DataTable ExecuteDataTable(string cmdText, CommandType commandType = CommandType.Text, Dictionary<string, object> parameters = null) 33 { 34 DataTable datatable = new DataTable(); 35 using (var connection = GetConnection()) 36 using (var command = new MySqlCommand()) 37 { 38 PrepareCommand(command,connection, cmdText, commandType, null, parameters); 39 MySqlDataAdapter adapter = new MySqlDataAdapter(command); 40 adapter.Fill(datatable); 41 return datatable; 42 43 } 44 } 45 46 public DataSet ExecuteDataSet(string cmdText, CommandType commandType = CommandType.Text, Dictionary<string, object> parameters = null) 47 { 48 DataSet dataset = new DataSet(); 49 using (var connection = GetConnection()) 50 using (var command = new MySqlCommand()) 51 { 52 PrepareCommand(command, connection,cmdText, commandType, null, parameters); 53 MySqlDataAdapter adapter = new MySqlDataAdapter(command); 54 adapter.Fill(dataset); 55 return dataset; 56 } 57 } 58 59 public MySqlDataReader ExecuteReader(string cmdText, CommandType commandType = CommandType.Text, Dictionary<string, object> parameters = null) 60 { 61 using (var connection = GetConnection()) 62 using (var command = new MySqlCommand()) 63 { 64 PrepareCommand(command,connection, cmdText, commandType, null, parameters); 65 return command.ExecuteReader(); 66 } 67 } 68 69 #endregion 70 71 #region async
72 73 public async Task<int> ExecuteNonQueryAsync(string cmdText, CommandType commandType = CommandType.Text, Dictionary<string, object> parameters = null, MySqlTransaction mySqlTransaction = null) 74 { 75 using (var connection = GetConnection()) 76 using (var command = new MySqlCommand()) 77 { 78 PrepareCommand(command,connection, cmdText, commandType, mySqlTransaction, parameters); 79 return await command.ExecuteNonQueryAsync(); 80 } 81 }
82 83 public async Task<object> ExecuteScalarAsync(string cmdText, CommandType commandType = CommandType.Text, Dictionary<string, object> parameters = null) 84 { 85 using (var connection = GetConnection()) 86 using (var command = new MySqlCommand()) 87 { 88 PrepareCommand(command,connection, cmdText, commandType, null, parameters); 89 return await command.ExecuteScalarAsync(); 90 } 91 92 }
93 94 public async Task<DataTable> ExecuteDataTableAsync(string cmdText, CommandType commandType = CommandType.Text, Dictionary<string, object> parameters = null) 95 { 96 DataTable datatable = new DataTable(); 97 using (var connection = GetConnection()) 98 using (var command = new MySqlCommand()) 99 {100 PrepareCommand(command, connection, cmdText, commandType, null, parameters);101 MySqlDataAdapter adapter = new MySqlDataAdapter(command);102 await adapter.FillAsync(datatable);103 return datatable;104 }105 }
106 107 public async Task<DataSet> ExecuteDataSetAsync(string cmdText, CommandType commandType = CommandType.Text, Dictionary<string, object> parameters = null)108 {109 DataSet dataset = new DataSet();110 using (var connection = GetConnection())111 using (var command = new MySqlCommand())112 {113 PrepareCommand(command, connection, cmdText, commandType, null, parameters);114 MySqlDataAdapter adapter = new MySqlDataAdapter(command);115 await adapter.FillAsync(dataset);116 return dataset;117 }118 }119 /// <summary>120 /// reader121 /// </summary>122 /// <param name="cmdText"></param>123 /// <param name="commandType"></param>124 /// <param name="parameters"></param>125 /// <returns></returns>126 public static async Task<DbDataReader> ExecuteReaderAsync(string cmdText, CommandType commandType = CommandType.Text, Dictionary<string, object> parameters = null)127 {128 using (var connection = GetConnection())129 using (var command = new MySqlCommand())130 {131 PrepareCommand(command,connection, cmdText, commandType, null, parameters);132 return await command.ExecuteReaderAsync();133 }134 }135 136 #endregion137 138 #region private Method139 140 private static void PrepareCommand(MySqlCommand command,MySqlConnection connection, string commandText, CommandType commandType, MySqlTransaction transaction, Dictionary<string, object> parameters)141 {142 command.CommandText = commandText;143 command.CommandType = commandType;144 if (connection.State != ConnectionState.Open)145 {146 connection.Open();147 }148 command.Connection = connection;149 if (transaction != null)150 {151 command.Transaction = transaction;152 }153 AttachParameter(command, parameters);154 }155 156 private static void AttachParameter(MySqlCommand command, Dictionary<string, object> parameters)157 {158 if (parameters != null && parameters.Count > 0)159 {160 foreach (var parameterItem in parameters)161 {162 command.Parameters.Add(new MySqlParameter(parameterItem.Key, parameterItem.Value));163 }164 }165 }166 167 #endregion168 }
以上是简单的数据操作帮助类，对于非常简单的增删改查还是可以应付的（但是对于事务的支持是不够的）。
简单封装Db操作--支持事务
一、 创建Db操作类（不再使用静态的dbHelper）
1 public class MysqlOperator 2 { 3 private readonly MySqlConnection _mySqlConnection; 4 public MysqlOperator(MySqlConnection mySqlConnection) 5 { 6 this._mySqlConnection = mySqlConnection; 7 } 8 #region sync 9 public int ExecuteNonQuery(string cmdText, CommandType commandType = CommandType.Text, Dictionary<string, object> parameters = null,MySqlTransaction mySqlTransaction=null) 10 { 11 using (var command = new MySqlCommand()) 12 { 13 PrepareCommand(command, cmdText, commandType, mySqlTransaction, parameters); 14 return command.ExecuteNonQuery(); 15 } 16 } 17 18 public object ExecuteScalar(string cmdText, CommandType commandType = CommandType.Text, Dictionary<string, object> parameters = null) 19 { 20 using (var command = new MySqlCommand()) 21 { 22 PrepareCommand(command, cmdText, commandType, null, parameters); 23 return command.ExecuteScalar(); 24 } 25 26 } 27 28 public DataTable ExecuteDataTable(string cmdText, CommandType commandType = CommandType.Text, Dictionary<string, object> parameters = null) 29 { 30 DataTable datatable = new DataTable(); 31 32 using (var command = new MySqlCommand()) 33 { 34 PrepareCommand(command, cmdText, commandType, null, parameters); 35 MySqlDataAdapter adapter = new MySqlDataAdapter(command); 36 adapter.Fill(datatable); 37 return datatable; 38 39 } 40 } 41 public DataSet ExecuteDataSet(string cmdText, CommandType commandType = CommandType.Text, Dictionary<string, object> parameters = null) 42 { 43 DataSet dataset = new DataSet(); 44 45 using (var command = new MySqlCommand()) 46 { 47 PrepareCommand(command, cmdText, commandType, null, parameters); 48 MySqlDataAdapter adapter = new MySqlDataAdapter(command);
49 adapter.Fill(dataset); 50 return dataset;
51 } 52 } 53 public MySqlDataReader ExecuteReader(string cmdText, CommandType commandType = CommandType.Text, Dictionary<string, object> parameters = null) 54 { 55 using (var command = new MySqlCommand()) 56 { 57 PrepareCommand(command, cmdText, commandType, null, parameters); 58 return command.ExecuteReader(); 59 } 60 } 61 #endregion 62 #region async
63 public async Task<int> ExecuteNonQueryAsync(string cmdText, CommandType commandType = CommandType.Text, Dictionary<string, object> parameters = null, MySqlTransaction mySqlTransaction = null) 64 { 65 using (var command = new MySqlCommand()) 66 { 67 PrepareCommand(command, cmdText, commandType, mySqlTransaction, parameters); 68 return await command.ExecuteNonQueryAsync(); 69 } 70 } 71 72 public async Task<object> ExecuteScalarAsync(string cmdText, CommandType commandType = CommandType.Text, Dictionary<string, object> parameters = null) 73 { 74 using (var command = new MySqlCommand()) 75 { 76 PrepareCommand(command, cmdText, commandType, null, parameters); 77 return await command.ExecuteScalarAsync(); 78 } 79 80 } 81 82 //public async Task<DataTable> ExecuteDataTableAsync(string cmdText, CommandType commandType = CommandType.Text, Dictionary<string, object> parameters = null) 83 //{ 84 // DataTable datatable = new DataTable(); 85 86 // using (var command = new MySqlCommand()) 87 // { 88 // PrepareCommand(command, cmdText, commandType, null, parameters); 89 // MySqlDataAdapter adapter = new MySqlDataAdapter(command); 90 // await adapter.FillAsync(datatable); 91 // return datatable;
92 // } 93 //} 94 //public async Task<DataSet> ExecuteDataSetAsync(string cmdText, CommandType commandType = CommandType.Text, Dictionary<string, object> parameters = null) 95 //{ 96 // DataSet dataset = new DataSet(); 97 98 // using (var command = new MySqlCommand()) 99 // {100 // PrepareCommand(command, cmdText, commandType, null, parameters);101 // MySqlDataAdapter adapter = new MySqlDataAdapter(command);102 // await adapter.FillAsync(dataset);103 // return dataset;104 // }105 //}106 107 108 /// <summary>109 /// 110 /// </summary>111 /// <param name="cmdText"></param>112 /// <param name="commandType"></param>113 /// <param name="parameters"></param>114 /// <returns></returns>115 public async Task<DbDataReader> ExecuteReaderAsync(string cmdText, CommandType commandType = CommandType.Text, Dictionary<string, object> parameters = null)116 {117 using (var command = new MySqlCommand())118 {119 PrepareCommand(command, cmdText, commandType, null, parameters);120 return await command.ExecuteReaderAsync();121 }122 }123 #endregion124 #region private Method125 private void PrepareCommand(MySqlCommand command, string commandText, CommandType commandType, MySqlTransaction transaction, Dictionary<string, object> parameters)126 {127 command.CommandText = commandText;128 command.CommandType = commandType;129 if (_mySqlConnection.State != ConnectionState.Open)130 {131 _mySqlConnection.Open();132 }133 command.Connection = _mySqlConnection;134 if (transaction != null)135 {136 command.Transaction = transaction;137 }138 AttachParameter(command, parameters);
139 }140 private void AttachParameter(MySqlCommand command, Dictionary<string, object> parameters)141 {142 if (parameters != null && parameters.Count > 0)143 {144 foreach (var parameterItem in parameters)145 {146 command.Parameters.Add(new MySqlParameter(parameterItem.Key, parameterItem.Value));147 }148 }149 }150 #endregion151 }
二、定义数据操作管理类
1 public class SqlManagement : IDisposable 2 { 3 private readonly IConfiguration _configuration; 4 private readonly MysqlOperator _mysqlOperator; 5 private readonly string _connectionString; 6 public SqlManagement(IConfiguration configuration) 7 { 8 this._configuration = configuration; 9 this._mySqlConnection = new MySqlConnection(_configuration.GetConnectionString("mysqlconnection"));10 this._mysqlOperator = new MysqlOperator(_mySqlConnection);11 }12 private MySqlConnection _mySqlConnection;13 private MySqlTransaction _mySqlTransaction;14 public void BeginTransaction()15 {16 _mySqlTransaction = _mySqlConnection.BeginTransaction();17 }18 public void Commit()19 {20 if (_mySqlTransaction != null)21 {22 _mySqlTransaction.Commit();23 }24 if (_mySqlConnection != null)25 {26 _mySqlConnection.Close();27 }28 }29 public void Rollback()30 {31 if (_mySqlTransaction != null)32 {33 _mySqlTransaction.Rollback();34 }35 if (_mySqlConnection != null)36 {37 _mySqlConnection.Close();38 }39 }40 public void Dispose()41 {42 if (_mySqlConnection != null)43 {44 _mySqlConnection.Dispose();45 _mySqlConnection = null;46 }47 }48 49 #region 50 public int ExecuteNonQuery(string cmdText, CommandType commandType = CommandType.Text, Dictionary<string, object> parameters = null)51 {52 if (_mySqlTransaction != null)53 {54 return _mysqlOperator.ExecuteNonQuery(cmdText, commandType, parameters, _mySqlTransaction);55 }56 else57 {58 return _mysqlOperator.ExecuteNonQuery(cmdText, commandType, parameters);59 }60 }61 62 public object ExecuteScalar(string cmdText, CommandType commandType = CommandType.Text, Dictionary<string, object> parameters = null)63 {64 return _mysqlOperator.ExecuteScalar(cmdText, commandType, parameters);65 }66 #endregion67 }
其实已经有很多组件已经实现了很好的数据库操作的封装了，例如Dapper、EF等
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
