經過個人學習和項目經驗，總結關于ADO.NET實現數據查詢操作的方法做簡單封裝，希望喜歡的朋友點贊并收藏。話不多說，直接上代碼：

/// <summary>/// 數據庫連接字符串/// </summary>protected static string ConnectionString{get{return ConfigurationManager.ConnectionStrings["FTRTPContext"].ConnectionString;}}/// <summary>/// 數據庫實例工廠/// </summary>protected static DbProviderFactory DbProvider{get{string providerName = ConfigurationManager.ConnectionStrings["FTRTPContext"].ProviderName;return DbProviderFactories.GetFactory(providerName);}}/// <summary>/// 創建一個DbParameter參數對象/// </summary>/// <param name="parameterName">參數名稱</param>/// <param name="val">參數值</param>/// <returns>DbParameter對象</returns>public virtual DbParameter CreateDbParameter(string parameterName, object val){var parame = DbProvider.CreateParameter();parame.ParameterName = parameterName;parame.Value = val;return parame;}/// <summary>/// 創建一個DbParameter參數對象/// </summary>/// <param name="parameterName">參數名稱</param>/// <param name="dbType">參數類型</param>/// <param name="dbSize">參數大小(字節),short/Int16=2;float/int/Int32=4;double/long/Int64=8;decimal=16;string類型按照字符長度而定</param>/// <param name="val">參數值</param>/// <returns>DbParameter對象</returns>public virtual DbParameter CreateDbParameter(string parameterName, DbType dbType, int dbSize, object val){var parame = DbProvider.CreateParameter();parame.ParameterName = parameterName;parame.DbType = dbType;parame.Size = dbSize;parame.Value = val;return parame;}/// <summary>/// 執行非查詢操作(添加/修改/刪除)/// </summary>/// <param name="sqlString">參數化sql語句</param>/// <param name="cmdParms">參數數組</param>/// <returns>操作影響行數</returns>public virtual int ExecuteNonQuery(string sqlString, params DbParameter[] cmdParms){using (var conn = DbProvider.CreateConnection()){conn.ConnectionString = ConnectionString;using (var cmd = PrepareCommand(conn, sqlString, cmdParms)){int rows = cmd.ExecuteNonQuery();cmd.Parameters.Clear();return rows;}}}/// <summary>/// 執行sql查詢，返回第一行第一列/// </summary>/// <param name="sqlString">參數化sql語句</param>/// <param name="cmdParms">參數數組</param>/// <returns>第一行第一列的值</returns>public virtual object ExecuteScalar(string sqlString, params DbParameter[] cmdParms){using (DbConnection conn = DbProvider.CreateConnection()){conn.ConnectionString = ConnectionString;using (DbCommand cmd = PrepareCommand(conn, sqlString, cmdParms)){object obj = cmd.ExecuteScalar();cmd.Parameters.Clear();conn.Close();return obj;}}}/// <summary>/// 執行sql查詢，返回第一行第一列(泛型)/// </summary>/// <typeparam name="TSource">返回的值類型</typeparam>/// <param name="sqlString">參數化sql語句</param>/// <param name="cmdParms">參數數組</param>/// <returns>第一行第一列的值</returns>public virtual TSource ExecuteScalar<TSource>(string sqlString, params DbParameter[] cmdParms) where TSource : struct{object obj = ExecuteScalar(sqlString, cmdParms);if ((Object.Equals(obj, null)) || (Object.Equals(obj, DBNull.Value))){return default(TSource);}return typeof(TSource) == obj.GetType() ? (TSource)obj : (TSource)Convert.ChangeType(obj, typeof(TSource));}/// <summary>/// 執行sql查詢，返回DataReader/// </summary>/// <param name="sqlString">參數化sql語句</param>/// <param name="cmdParms">參數數組</param>/// <returns>DbDataReader對象</returns>public virtual DbDataReader ExecuteReader(string sqlString, params DbParameter[] cmdParms){var conn = DbProvider.CreateConnection();try{conn.ConnectionString = ConnectionString;var cmd = PrepareCommand(conn, sqlString, cmdParms);var myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);cmd.Parameters.Clear();return myReader;}catch (DbException ex){conn.Close();throw ex;}}/// <summary>/// 執行sql查詢，返回DataReader/// </summary>/// <param name="dataReaderCallback">委托方法，讀取DataReader</param>/// <param name="sqlString">參數化sql語句</param>/// <param name="cmdParms">參數數組</param>public virtual void ExecuteReader(Action<DbDataReader> dataReaderCallback, string sqlString, params DbParameter[] cmdParms){using (var conn = DbProvider.CreateConnection()){conn.ConnectionString = ConnectionString;var cmd = PrepareCommand(conn, sqlString, cmdParms);var myReader = cmd.ExecuteReader();cmd.Parameters.Clear();dataReaderCallback(myReader);myReader.Dispose();}}/// <summary>/// 執行sql查詢，返回DataSet/// </summary>/// <param name="sqlString">參數化sql語句</param>/// <param name="cmdParms">參數數組</param>/// <returns>DataSet對象</returns>public virtual DataSet ExecuteDataSet(string sqlString, params DbParameter[] cmdParms){using (var conn = DbProvider.CreateConnection()){conn.ConnectionString = ConnectionString;using (var cmd = PrepareCommand(conn, sqlString, cmdParms)){using (var adapter = DbProvider.CreateDataAdapter()){adapter.SelectCommand = cmd;var ds = new DataSet();adapter.Fill(ds);return ds;}}}}/// <summary>/// 以事務形式執行多條語句的批量新增或修改操作/// </summary>/// <param name="sqlStringList">sql語句</param>/// <returns>事務是否執行成功</returns>public virtual bool ExecuteSqlTran(List<string> sqlStringList){var cmd = DbProvider.CreateCommand();try{cmd.Connection = DbProvider.CreateConnection();cmd.Connection.ConnectionString = ConnectionString;cmd.Connection.Open();cmd.CommandType = CommandType.Text;cmd.Transaction = cmd.Connection.BeginTransaction();foreach (var sqlString in sqlStringList){cmd.CommandText = sqlString;cmd.ExecuteNonQuery();}cmd.Transaction.Commit();return true;}catch{cmd.Transaction.Rollback();return false;}finally{cmd.Connection.Close();}}/// <summary>/// 以事務形式執行同一個語句的批量新增或修改操作/// </summary>/// <param name="sqlString">參數化sql語句</param>/// <param name="cmdParams">參數數組</param>/// <returns>事務是否執行成功</returns>public virtual bool ExecuteSqlTran(string sqlString, List<DbParameter[]> cmdParams){var cmd = DbProvider.CreateCommand();try{cmd.Connection = DbProvider.CreateConnection();cmd.Connection.ConnectionString = ConnectionString;cmd.Connection.Open();cmd.CommandText = sqlString;cmd.CommandType = CommandType.Text;cmd.Transaction = cmd.Connection.BeginTransaction();foreach (DbParameter[] parames in cmdParams){cmd.Parameters.AddRange(parames);cmd.ExecuteNonQuery();cmd.Parameters.Clear();}cmd.Transaction.Commit();return true;}catch{cmd.Transaction.Rollback();return false;}finally{cmd.Connection.Close();}}/// <summary>/// 以事務形式執行同一個語句的批量新增或修改操作/// </summary>/// <param name="sqlStringWithParames">參數化sql語句以及參數數組</param>/// <returns>事務是否執行成功</returns>public virtual bool ExecuteSqlTran(Dictionary<string, List<DbParameter[]>> sqlStringWithParames){var cmd = DbProvider.CreateCommand();try{cmd.Connection = DbProvider.CreateConnection();cmd.Connection.ConnectionString = ConnectionString;cmd.Connection.Open();cmd.CommandType = CommandType.Text;cmd.Transaction = cmd.Connection.BeginTransaction();foreach (var sqlString in sqlStringWithParames.Keys){cmd.CommandText = sqlString;List<DbParameter[]> paramesList = sqlStringWithParames[sqlString];cmd.Parameters.AddRange(paramesList[0]);cmd.ExecuteNonQuery();for (var i = 1; i < paramesList.Count; i++){DbParameter[] parames = paramesList[i];for (var j = 0; j < parames.Length; j++){cmd.Parameters[j].Value = parames[j].Value;}cmd.ExecuteNonQuery();}cmd.Parameters.Clear();}cmd.Transaction.Commit();return true;}catch{cmd.Transaction.Rollback();return false;}finally{cmd.Connection.Close();}}/// <summary>/// 表值參數保存數據/// </summary>/// <param name="dt"></param>/// <param name="sqlStatement"></param>/// <param name="tableName"></param>/// <returns></returns>public virtual bool ExecuteSqlBulk(DataTable dt, string sqlStatement, string tableName){var sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["FTRTPContext"].ConnectionString);var cmd = new SqlCommand(sqlStatement, sqlConn);var catParam = cmd.Parameters.AddWithValue("@NewBulkTestTvp", dt);catParam.SqlDbType = SqlDbType.Structured;//表值參數的名字叫BulkUdt，在上面的建立測試環境的SQL中有。 catParam.TypeName = tableName;// "dbo.BulkUdt";try{sqlConn.Open();if (dt != null && dt.Rows.Count != 0){cmd.ExecuteNonQuery();}}catch (Exception ex){throw ex;}finally{sqlConn.Close();}return true;}/// <summary>/// 初始化DbCommand對象/// </summary>/// <param name="conn">數據庫連接</param>/// <param name="sqlString">參數化sql語句</param>/// <param name="cmdParms">參數數組</param>/// <returns>DbCommand對象</returns>protected DbCommand PrepareCommand(DbConnection conn, string sqlString, params DbParameter[] cmdParms){if (conn.State != ConnectionState.Open){conn.Open();}DbCommand cmd = DbProvider.CreateCommand();cmd.Connection = conn;cmd.CommandText = sqlString;cmd.CommandType = CommandType.Text;if (cmdParms != null){foreach (DbParameter parm in cmdParms){cmd.Parameters.Add(parm);}}return cmd;}/// <summary>/// 分頁查詢數據(公共)/// </summary>/// <param name="strSql">查詢語句</param>/// <param name="order">分頁排序</param>/// <param name="pageSize">每頁顯示記錄數</param>/// <param name="pageIndex">當前第幾頁</param>/// <param name="record">總記錄數</param>/// <returns>DbDataReader對象</returns>public abstract DataSet SelectPageingPublic(string strSql, string order, int pageSize, int pageIndex, out int record);

對于目前需要處理的數據庫操作基本都在了吧，有不清楚的可以留言探討。

最后一個抽象方法實現如下所示：

/// <summary>/// 分頁查詢數據（公用）/// </summary>/// <param name="strSql">表名稱</param>/// <param name="order">分頁排序</param>/// <param name="pageSize">每頁顯示記錄數</param>/// <param name="pageIndex">當前第幾頁</param>/// <param name="record">總記錄數</param>/// <returns>DbDataReader對象</returns>public override DataSet SelectPageingPublic(string strSql, string order, int pageSize, int pageIndex, out int record){if (!string.IsNullOrEmpty(order) && order.IndexOf("order by", StringComparison.Ordinal) < 0){order = "order by " + order;}var countSql = string.Format("select count(1) from ({0}) e", strSql);//查詢總數record = ExecuteScalar<int>(countSql);var offsetCount = (pageIndex - 1) * pageSize;const string sqlTemplate = "select * from (select row_number() over({1}) as rowno, * from ({0})e ) f where rowno>{3} and rowno<=({2}+{3}) {1} ";var sqlSelectData = string.Format(sqlTemplate, strSql, order, pageSize, offsetCount);return ExecuteDataSet(sqlSelectData);}

　　希望對您的學習和工作有幫助，喜歡的點贊哈。

轉載于:https://www.cnblogs.com/fqzhong2007/p/8336594.html

與50位技術專家面對面20年技術見證，附贈技術全景圖

總結

以上是生活随笔為你收集整理的AQO.NET实现数据操作封装的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。