建网站网站建设,网页论坛怎么实现,网站客户端ip做爬虫,百度一下百度搜索入口c# DbHelper的封装
基于ADO.NET框架#xff0c;封装了适用于多个关系型数据库的DbHelper。通过简洁明了的代码#xff0c;实现了对各种数据库的高效操作。
public class DbHelper{private readonly DataBase _dataBase;public DbHelper(DataBase dataBase){_dataBase data…c# DbHelper的封装
基于ADO.NET框架封装了适用于多个关系型数据库的DbHelper。通过简洁明了的代码实现了对各种数据库的高效操作。
public class DbHelper{private readonly DataBase _dataBase;public DbHelper(DataBase dataBase){_dataBase dataBase;}public DataBase GetDataBase(){return _dataBase;}public DbConnection GetDbConnection(){var conn _dataBase.CreationConnection();if (conn.State ConnectionState.Closed){conn.Open();}return conn;}/// summary/// 执行语句/// /summary/// param namesqlsql语句/param/// param namecmdParms参数/param/// returns/returnspublic int Execute(string sql, params DbParameter[] cmdParms){using (DbConnection connection GetDbConnection()){using (DbCommand cmd connection.CreateCommand()){try{PrepareCommand(cmd, connection, null, sql, cmdParms);int rows cmd.ExecuteNonQuery();cmd.Parameters.Clear();return rows;}catch (DbException e){throw e;}}}}/// summary/// 批量查询/// /summary/// param namesqlsql语句/param/// param namecmdParms参数/param/// returns/returnspublic DataSet Query(string sql, params DbParameter[] cmdParms){using (DbConnection connection GetDbConnection()){DataSet ds new DataSet();try{DbProviderFactory factory DbProviderFactories.GetFactory(connection);DbCommand command factory.CreateCommand();PrepareCommand(command, connection, null, sql, cmdParms);DbDataAdapter adapter factory.CreateDataAdapter();adapter.SelectCommand command;adapter.Fill(ds, ds);adapter.Dispose();command.Dispose();}catch (DbException ex){throw ex;}return ds;}}/// summary/// 批量查询/// /summary/// typeparam nameT/typeparam/// param namesqlsql语句/param/// param namereader数据读取器/param/// param namecmdParms参数/param/// returns/returns/// exception crefException/exceptionpublic ListT QueryT(string sql, FuncIDataReader, T reader, params DbParameter[] cmdParms){if (reader null)throw new Exception(数据读取器是空的!);ListT list new ListT();using (DbConnection connection GetDbConnection()){using (DbCommand cmd connection.CreateCommand()){try{PrepareCommand(cmd, connection, null, sql, cmdParms);DbDataReader myReader cmd.ExecuteReader();cmd.Parameters.Clear();while (myReader.Read()){list.Add(reader(myReader));}myReader.Close();}catch (DbException e){throw e;}}}return list;}/// summary/// 单个查询/// /summary/// typeparam nameT/typeparam/// param namesqlsql语句/param/// param namereader数据读取器/param/// param namecmdParms参数/param/// returns/returns/// exception crefException/exceptionpublic T QueryFirstOrDefaultT(string sql, FuncIDataReader, T reader, params DbParameter[] cmdParms){if (reader null){throw new Exception(数据读取器是空的!);}var model default(T);using (DbConnection connection GetDbConnection()){using (DbCommand cmd connection.CreateCommand()){try{PrepareCommand(cmd, connection, null, sql, cmdParms);DbDataReader myReader cmd.ExecuteReader();cmd.Parameters.Clear();if (myReader.Read())model reader(myReader);myReader.Close();}catch (DbException e){throw e;}}}return model;}/// summary/// 执行存储过程/// /summary/// param namestoredProcName存储过程名/param/// param nameparameters存储过程参数/param/// returns/returnspublic DataSet RunProcedure(string storedProcName, DbParameter[] parameters){using (DbConnection connection GetDbConnection()){DataSet dataSet new DataSet();connection.Open();DbDataAdapter sqlDA DbProviderFactories.GetFactory(connection).CreateDataAdapter();sqlDA.SelectCommand BuildQueryCommand(connection, storedProcName, parameters);sqlDA.Fill(dataSet, ds);sqlDA.SelectCommand.Dispose();sqlDA.Dispose();return dataSet;}}/// summary/// 执行存储过程返回SqlDataReader ( 注意调用该方法后一定要对SqlDataReader进行Close )/// /summary/// param namestoredProcName存储过程名/param/// param nameparameters存储过程参数/param/// returnsSqlDataReader/returnspublic DbDataReader RunProcedureToReader(string storedProcName, DbParameter[] parameters){using (DbConnection connection GetDbConnection()){DbDataReader returnReader;connection.Open();DbCommand command BuildQueryCommand(connection, storedProcName, parameters);command.CommandType CommandType.StoredProcedure;returnReader command.ExecuteReader(CommandBehavior.CloseConnection);command.Dispose();return returnReader;}}/// summary/// 执行存储过程/// /summary/// param namestoredProcName存储过程名/param/// param nameparameters存储过程参数/param/// returnsSqlDataReader/returnspublic T RunProcedureT(string storedProcName, FuncIDataReader, T reader, DbParameter[] parameters){if (reader null){throw new Exception(数据读取器是空的!);}T t default(T);using (DbConnection connection GetDbConnection()){DbDataReader returnReader;connection.Open();DbCommand command BuildQueryCommand(connection, storedProcName, parameters);command.CommandType CommandType.StoredProcedure;returnReader command.ExecuteReader(CommandBehavior.CloseConnection);command.Dispose();if (returnReader.Read())t reader(returnReader);returnReader.Close();}return t;}/// summary/// 执行存储过程/// /summary/// param namestoredProcName存储过程名/param/// param nameparameters存储过程参数/param/// returnsSqlDataReader/returnspublic ListT RunProcedureToListT(string storedProcName, FuncIDataReader, T reader, DbParameter[] parameters){if (reader null){throw new Exception(数据读取器是空的!);}ListT list new ListT();using (DbConnection connection GetDbConnection()){DbDataReader returnReader;connection.Open();DbCommand command BuildQueryCommand(connection, storedProcName, parameters);command.CommandType CommandType.StoredProcedure;returnReader command.ExecuteReader(CommandBehavior.CloseConnection);command.Dispose();while (returnReader.Read())list.Add(reader(returnReader));returnReader.Close();}return list;}/// summary/// 返回首行首列/// /summary/// param namesqlsql语句/param/// param namecmdParms参数/param/// returns/returnspublic object ExecuteScalar(string sql, params DbParameter[] cmdParms){object result null;using (DbConnection connection GetDbConnection()){using (DbCommand cmd connection.CreateCommand()){try{PrepareCommand(cmd, connection, null, sql, cmdParms);result cmd.ExecuteScalar();}catch (DbException e){throw e;}}}return result;}/// summary/// 分页列表/// /summary/// typeparam nameT/typeparam/// param nametablename表名(可以自定)/param/// param namepage分页信息/param/// param namereader读取器/param/// param namewhere条件/param/// param namefield字段/param/// param nameorder排序/parampublic ListT QueryWithPageT(string tablename, PageInfo page, FuncIDataReader, T reader, string where , string field *, string order , params DbParameter[] cmdParms){long offset page.Index * page.PageSize;string sql SELECT field FROM tablename;sql ListPageSql(sql, where, order);sql sql Limit(offset, page.PageSize);string sql2 SELECT COUNT(0) FROM tablename;sql2 ListPageSql(sql2, where, );string sql3 sql ; sql2;ListT list new ListT();using (DbConnection conn GetDbConnection()){using (DbCommand cmd conn.CreateCommand()){try{PrepareCommand(cmd, conn, null, sql3, cmdParms);DbDataReader myReader cmd.ExecuteReader();cmd.Parameters.Clear();while (myReader.Read()){list.Add(reader(myReader));}if (myReader.NextResult() myReader.Read())page.Count myReader.GetInt64Ex(0);myReader.Close();}catch (MySqlException e){throw new Exception(e.Message);}}}return list;}/// summary/// 组装分页sql/// /summary/// param namesql基础sql/param/// param namewhere条件/param/// param nameorder排序/param/// returns/returnsprivate string ListPageSql(string sql, string where, string order){if (!string.IsNullOrEmpty(where)){sql sql WHERE where;}if (!string.IsNullOrEmpty(order)){sql sql order;}return sql;}/// summary/// 分页/// /summary/// param nameoffset偏移/param/// param namesize每页显示数据尺寸/param/// returns/returns/// exception crefException/exceptionpublic string Limit(long offset, long size){if (offset -1){if (_dataBase.DbType ! DbBaseType.SqlServer){return LIMIT size;}}else{if (_dataBase.DbType DbBaseType.MySql){return string.Format(LIMIT {0},{1}, offset, size);}if (_dataBase.DbType DbBaseType.PostgreSql || _dataBase.DbType DbBaseType.Sqlite){return string.Format( LIMIT {0} OFFSET {1}, size, offset);}}throw new Exception(暂时不支持其它分页语法);}public DbParameter CreateDbParameter(string parameterName, DbType dbType, object value){using(DbConnection connection GetDbConnection()){DbParameter dbParameter DbProviderFactories.GetFactory(connection).CreateParameter();dbParameter.ParameterName parameterName;dbParameter.DbType dbType;dbParameter.Value value;return dbParameter;}}protected void PrepareCommand(DbCommand cmd, DbConnection conn, DbTransaction trans, string cmdText, DbParameter[] cmdParms){cmd.Connection conn;cmd.CommandText cmdText;if (trans ! null)cmd.Transaction trans;cmd.CommandType CommandType.Text;SetParameters(cmd, cmdParms);}private DbCommand BuildQueryCommand(DbConnection connection, string storedProcName, DbParameter[] parameters){DbCommand command connection.CreateCommand();command.CommandText storedProcName;command.CommandType CommandType.StoredProcedure;SetParameters(command, parameters);return command;}private void SetParameters(DbCommand command, DbParameter[] cmdParms){if (cmdParms ! null){foreach (var parameter in cmdParms){if ((parameter.Direction ParameterDirection.InputOutput||parameter.Direction ParameterDirection.Input)(parameter.Value null)){parameter.Value DBNull.Value;}command.Parameters.Add(parameter);}}}}
