.net基础---数据库操作

2022-02-08 11:27:55

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         /// 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://i.cnblogs.com/posts/edit;postId=14152891

.net基础---数据库操作

码农公寓

ADO.NET

DbHelper

简单封装Db操作--支持事务

相关文章