.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=john_yong;Pwd=root3295;"; 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 static 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 static 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 static 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 static 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 static 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 static 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 static 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 static 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 /// reader 121 /// </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 #endregion 137 138 #region private Method 139 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 #endregion 168 }
以上是简单的数据操作帮助类，对于非常简单的增删改查还是可以应付的（但是对于事务的支持是不够的）。
简单封装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 #endregion 124 #region private Method 125 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 #endregion 151 }
二、定义数据操作管理类
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 else 57 { 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 #endregion 67 }
其实已经有很多组件已经实现了很好的数据库操作的封装了，例如Dapper、EF等。此处仅算是对数据库操作的回顾！
----------
本文地址：https://www.cnblogs.com/johnyong/p/14152891.html
