经过个人学习和项目经验,总结关于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);
}
希望对您的学习和工作有帮助,喜欢的点赞哈。