/// <summary>
/// Returns rows from the DataSource that meet the parameter conditions.
/// </summary>
/// <param name="transactionManager"><see cref="TransactionManager"/> object</param>
/// <param name="parameters">A collection of <see cref="SqlFilterParameter"/> objects.</param>
/// <param name="orderBy">Specifies the sort criteria for the rows in the DataSource (Name ASC; BirthDay DESC, Name ASC);</param>
/// <param name="start">Row number at which to start reading.</param>
/// <param name="pageLength">Number of rows to return.</param>
/// <param name="count">out. The number of rows that match this query.</param>
/// <returns>Returns a typed collection of VProductModelInstructions objects.</returns>
public override VList <VProductModelInstructions> Find(TransactionManager transactionManager, IFilterParameterCollection parameters, string orderBy, int start, int pageLength, out int count)
{
SqlFilterParameterCollection filter = null;
if (parameters == null)
{
filter = new SqlFilterParameterCollection();
}
else
{
filter = parameters.GetParameters();
}
SqlDatabase database = new SqlDatabase(this._connectionString);
DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Production.usp_adwTiers_vProductModelInstructions_Find_Dynamic", typeof(VProductModelInstructionsColumn), filter, orderBy, start, pageLength);
SqlFilterParameter param;
for (int i = 0; i < filter.Count; i++)
{
param = filter[i];
database.AddInParameter(commandWrapper, param.Name, param.DbType, param.GetValue());
}
VList <VProductModelInstructions> rows = new VList <VProductModelInstructions>();
IDataReader reader = null;
try
{
if (transactionManager != null)
{
reader = Utility.ExecuteReader(transactionManager, commandWrapper);
}
else
{
reader = Utility.ExecuteReader(database, commandWrapper);
}
Fill(reader, rows, 0, int.MaxValue);
count = rows.Count;
if (reader.NextResult())
{
if (reader.Read())
{
count = reader.GetInt32(0);
}
}
}
finally
{
if (reader != null)
{
reader.Close();
}
}
return(rows);
}
public List <Teacher> Find(TransactionManager trans, IFilterParameterCollection parameters,
string orderBy, int start, int pageLength, out int count)
{
SqlFilterParameterCollection filter = null;
if (parameters == null)
{
filter = new SqlFilterParameterCollection();
}
else
{
filter = parameters.GetParameters();
}
SqlDatabase db = new SqlDatabase(this._connStr);
DbCommand cmd = SqlCommandHelper.GetCommandWrapper(db, this._pagedSqlFormat,
typeof(TeacherColumn),
filter, this._timeOut,
orderBy, start, pageLength);
List <Teacher> rows = null;
IDataReader reader = null;
try {
if (trans != null)
{
reader = Utility.ExecuteReader(trans, cmd);
}
else
{
reader = Utility.ExecuteReader(db, cmd);
}
rows = Fill(reader, 0, int.MaxValue);
if (rows != null)
{
count = rows.Count;
}
else
{
count = -1;
}
if (reader.NextResult())
{
if (reader.Read())
{
count = reader.GetInt32(0);
}
}
}
finally {
if (reader != null)
{
reader.Close();
}
cmd = null;
}
return(rows);
}
public override TList <Trouble> Find(TransactionManager transactionManager, SqlFilterParameterCollection parameters, string orderBy, int start, int pageLength, out int count)
{
SqlDatabase database = new SqlDatabase(this._connectionString);
DbCommand command = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Trouble_Find_Dynamic", typeof(TroubleColumn), parameters, orderBy, start, pageLength);
if (parameters != null)
{
for (int i = 0; i < parameters.Count; i++)
{
SqlFilterParameter parameter = parameters[i];
database.AddInParameter(command, parameter.Name, parameter.DbType, parameter.Value);
}
}
TList <Trouble> rows = new TList <Trouble>();
IDataReader reader = null;
try
{
if (transactionManager != null)
{
reader = Utility.ExecuteReader(transactionManager, command);
}
else
{
reader = Utility.ExecuteReader(database, command);
}
TroubleProviderBaseCore.Fill(reader, rows, 0, 0x7fffffff);
count = rows.Count;
if (!reader.NextResult())
{
return(rows);
}
if (reader.Read())
{
count = reader.GetInt32(0);
}
}
finally
{
if (reader != null)
{
reader.Close();
}
}
return(rows);
}
/// <summary>
/// Returns rows from the DataSource that meet the parameter conditions.
/// </summary>
/// <param name="transactionManager"><see cref="TransactionManager"/> object</param>
/// <param name="parameters">A collection of <see cref="SqlFilterParameter"/> objects.</param>
/// <param name="orderBy">Specifies the sort criteria for the rows in the DataSource (Name ASC; BirthDay DESC, Name ASC);</param>
/// <param name="start">Row number at which to start reading.</param>
/// <param name="pageLength">Number of rows to return.</param>
/// <param name="count">out. The number of rows that match this query.</param>
/// <returns>Returns a typed collection of Nettiers.AdventureWorks.Entities.TransactionHistory objects.</returns>
public override TList<TransactionHistory> Find(TransactionManager transactionManager, IFilterParameterCollection parameters, string orderBy, int start, int pageLength, out int count)
{
SqlFilterParameterCollection filter = null;
if (parameters == null)
filter = new SqlFilterParameterCollection();
else
filter = parameters.GetParameters();
SqlDatabase database = new SqlDatabase(this._connectionString);
DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Production.usp_adwTiers_TransactionHistory_Find_Dynamic", typeof(TransactionHistoryColumn), filter, orderBy, start, pageLength);
SqlFilterParameter param;
for ( int i = 0; i < filter.Count; i++ )
{
param = filter[i];
database.AddInParameter(commandWrapper, param.Name, param.DbType, param.GetValue());
}
TList<TransactionHistory> rows = new TList<TransactionHistory>();
IDataReader reader = null;
try
{
//Provider Data Requesting Command Event
OnDataRequesting(new CommandEventArgs(commandWrapper, "Find", rows));
if ( transactionManager != null )
{
reader = Utility.ExecuteReader(transactionManager, commandWrapper);
}
else
{
reader = Utility.ExecuteReader(database, commandWrapper);
}
Fill(reader, rows, 0, int.MaxValue);
count = rows.Count;
if ( reader.NextResult() )
{
if ( reader.Read() )
{
count = reader.GetInt32(0);
}
}
//Provider Data Requested Command Event
OnDataRequested(new CommandEventArgs(commandWrapper, "Find", rows));
}
finally
{
if ( reader != null )
reader.Close();
commandWrapper = null;
}
return rows;
}
/// <summary>
/// Returns rows from the DataSource that meet the parameter conditions.
/// </summary>
/// <param name="transactionManager"><see cref="TransactionManager"/> object</param>
/// <param name="parameters">A collection of <see cref="SqlFilterParameter"/> objects.</param>
/// <param name="orderBy">Specifies the sort criteria for the rows in the DataSource (Name ASC; BirthDay DESC, Name ASC);</param>
/// <param name="start">Row number at which to start reading.</param>
/// <param name="pageLength">Number of rows to return.</param>
/// <param name="count">out. The number of rows that match this query.</param>
/// <returns>Returns a typed collection of LibraryManagement.Domain.CustInfo objects.</returns>
public override TList <CustInfo> Find(TransactionManager transactionManager, IFilterParameterCollection parameters, string orderBy, int start, int pageLength, out int count)
{
SqlFilterParameterCollection filter = null;
if (parameters == null)
{
filter = new SqlFilterParameterCollection();
}
else
{
filter = parameters.GetParameters();
}
SqlDatabase database = new SqlDatabase(this._connectionString);
DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.tblCust_info_Find_Dynamic", typeof(CustInfoColumn), filter, orderBy, start, pageLength);
SqlFilterParameter param;
for (int i = 0; i < filter.Count; i++)
{
param = filter[i];
database.AddInParameter(commandWrapper, param.Name, param.DbType, param.GetValue());
}
TList <CustInfo> rows = new TList <CustInfo>();
IDataReader reader = null;
try
{
//Provider Data Requesting Command Event
OnDataRequesting(new CommandEventArgs(commandWrapper, "Find", rows));
if (transactionManager != null)
{
reader = Utility.ExecuteReader(transactionManager, commandWrapper);
}
else
{
reader = Utility.ExecuteReader(database, commandWrapper);
}
Fill(reader, rows, 0, int.MaxValue);
count = rows.Count;
if (reader.NextResult())
{
if (reader.Read())
{
count = reader.GetInt32(0);
}
}
//Provider Data Requested Command Event
OnDataRequested(new CommandEventArgs(commandWrapper, "Find", rows));
}
finally
{
if (reader != null)
{
reader.Close();
}
commandWrapper = null;
}
return(rows);
}
/// <summary>
/// Gets the command wrapper for a dynamically generated query and
/// optionally includes server-side paging.
/// </summary>
/// <param name="database"></param>
/// <param name="commandName"></param>
/// <param name="columnEnum"></param>
/// <param name="parameters"></param>
/// <param name="orderBy"></param>
/// <param name="start"></param>
/// <param name="pageLength"></param>
/// <returns></returns>
public static DbCommand GetCommandWrapper(Database database, String commandName, Type columnEnum, SqlFilterParameterCollection parameters, String orderBy, int start, int pageLength)
{
String query = StoredProcedureProvider.GetProcedureBodyFromEmbeddedResource(commandName);
//query = query.Replace(SqlUtil.PAGE_INDEX, string.Concat(SqlUtil.PAGE_INDEX, Guid.NewGuid().ToString("N").Substring(0,8)));
String sortExpression = Utility.ParseSortExpression(columnEnum, orderBy);
String whereClause = String.Empty;
if ( parameters != null && ! String.IsNullOrEmpty(parameters.FilterExpression) )
{
whereClause = String.Format("where {0}", parameters.FilterExpression);
}
// replace dynamic components
query = String.Format(query, whereClause, sortExpression, start, (start + pageLength));
DbCommand command = database.GetSqlStringCommand(query);
command.CommandTimeout = DataRepository.Provider.DefaultCommandTimeout;
return command;
}
/// <summary>
/// Returns rows from the DataSource that meet the parameter conditions.
/// </summary>
/// <param name="transactionManager"><see cref="TransactionManager"/> object</param>
/// <param name="parameters">A collection of <see cref="SqlFilterParameter"/> objects.</param>
/// <param name="orderBy">Specifies the sort criteria for the rows in the DataSource (Name ASC; BirthDay DESC, Name ASC);</param>
/// <param name="start">Row number at which to start reading.</param>
/// <param name="pageLength">Number of rows to return.</param>
/// <param name="count">out. The number of rows that match this query.</param>
/// <returns>Returns a typed collection of VJobCandidateEducation objects.</returns>
public override VList<VJobCandidateEducation> Find(TransactionManager transactionManager, IFilterParameterCollection parameters, string orderBy, int start, int pageLength, out int count)
{
SqlFilterParameterCollection filter = null;
if (parameters == null)
filter = new SqlFilterParameterCollection();
else
filter = parameters.GetParameters();
SqlDatabase database = new SqlDatabase(this._connectionString);
DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "HumanResources.usp_adwTiers_vJobCandidateEducation_Find_Dynamic", typeof(VJobCandidateEducationColumn), filter, orderBy, start, pageLength);
SqlFilterParameter param;
for ( int i = 0; i < filter.Count; i++ )
{
param = filter[i];
database.AddInParameter(commandWrapper, param.Name, param.DbType, param.GetValue());
}
VList<VJobCandidateEducation> rows = new VList<VJobCandidateEducation>();
IDataReader reader = null;
try
{
if ( transactionManager != null )
{
reader = Utility.ExecuteReader(transactionManager, commandWrapper);
}
else
{
reader = Utility.ExecuteReader(database, commandWrapper);
}
Fill(reader, rows, 0, int.MaxValue);
count = rows.Count;
if ( reader.NextResult() )
{
if ( reader.Read() )
{
count = reader.GetInt32(0);
}
}
}
finally
{
if ( reader != null )
reader.Close();
}
return rows;
}
public static DbCommand GetCommandWrapper(Database database, string commandName, Type columnEnum, SqlFilterParameterCollection parameters, string orderBy, int start, int pageLength)
{
string format = GetProcedureBodyFromEmbeddedResource(commandName);
string text2 = Utility.ParseSortExpression(columnEnum, orderBy);
string text3 = string.Empty;
if (!((parameters == null) || string.IsNullOrEmpty(parameters.FilterExpression)))
{
text3 = string.Format("where {0}", parameters.FilterExpression);
}
format = string.Format(format, new object[] { text3, text2, start, start + pageLength });
DbCommand sqlStringCommand = database.GetSqlStringCommand(format);
sqlStringCommand.CommandTimeout = DataRepository.Provider.DefaultCommandTimeout;
return(sqlStringCommand);
}
public virtual VList <Entity> Find(TransactionManager transactionManager, SqlFilterParameterCollection parameters, string orderBy, int start, int pageLength, out int count)
{
count = 0;
return(null);
}
public virtual VList <Entity> Find(SqlFilterParameterCollection parameters, string orderBy, int start, int pageLength, out int count)
{
return(this.Find(null, parameters, orderBy, start, pageLength, out count));
}
public virtual VList <Entity> Find(TransactionManager transactionManager, SqlFilterParameterCollection parameters, string orderBy)
{
int count = 0;
return(this.Find(transactionManager, parameters, orderBy, 0, 0x7fffffff, out count));
}
public virtual VList <Entity> Find(TransactionManager transactionManager, SqlFilterParameterCollection parameters)
{
return(this.Find(transactionManager, parameters, null));
}
public virtual VList <Entity> Find(SqlFilterParameterCollection parameters, string orderBy)
{
return(this.Find(null, parameters, orderBy));
}
public virtual VList <Entity> Find(SqlFilterParameterCollection parameters)
{
return(this.Find(null, parameters));
}
/// <summary>
/// 将某个表对应的分页查询转变为DBCommand对象
/// </summary>
/// <param name="database"></param>
/// <param name="queryFormat">分页的查询串，注{0}为where clause,{1}为Order By,{2}为start,{3}为start+pageLength</param>
/// <param name="columnEnum"></param>
/// <param name="parameters">如果本参数不为空，将自动构建where语句</param>
/// <param name="timeOut"></param>
/// <param name="orderBy">排序列，如果为空，则以表的第一个列为排序参考列</param>
/// <param name="start"></param>
/// <param name="pageLength"></param>
/// <returns></returns>
public static DbCommand GetCommandWrapper(Database database, String queryFormat, Type columnEnum, SqlFilterParameterCollection parameters, int timeOut, String orderBy, int start, int pageLength)
{
//query = query.Replace(SqlUtil.PAGE_INDEX, string.Concat(SqlUtil.PAGE_INDEX, Guid.NewGuid().ToString("N").Substring(0,8)));
String sortExpression = Utility.ParseSortExpression(columnEnum, orderBy);
String whereClause = String.Empty;
if (parameters != null && !String.IsNullOrEmpty(parameters.FilterExpression))
{
whereClause = String.Format("where {0}", parameters.FilterExpression);
}
// 格式化
queryFormat = String.Format(queryFormat, whereClause, sortExpression, start, (start + pageLength));
DbCommand command = database.GetSqlStringCommand(queryFormat);
if (parameters != null)
{
SqlFilterParameter param;
for (int i = 0; i < parameters.Count; i++)
{
param = parameters[i];
database.AddInParameter(command, param.Name, param.DbType, param.GetValue());
}
}
command.CommandTimeout = timeOut;
return(command);
}
/// <summary>
/// Gets the command wrapper for a dynamically generated query and
/// optionally includes server-side paging.
/// </summary>
/// <param name="database"></param>
/// <param name="commandName"></param>
/// <param name="columnEnum"></param>
/// <param name="parameters"></param>
/// <param name="orderBy"></param>
/// <param name="start"></param>
/// <param name="pageLength"></param>
/// <returns></returns>
public static DbCommand GetCommandWrapper(Database database, String commandName, Type columnEnum, SqlFilterParameterCollection parameters, String orderBy, int start, int pageLength)
{
String query = StoredProcedureProvider.GetProcedureBodyFromEmbeddedResource(commandName);
//query = query.Replace(SqlUtil.PAGE_INDEX, string.Concat(SqlUtil.PAGE_INDEX, Guid.NewGuid().ToString("N").Substring(0,8)));
String sortExpression = Utility.ParseSortExpression(columnEnum, orderBy);
String whereClause = String.Empty;
if (parameters != null && !String.IsNullOrEmpty(parameters.FilterExpression))
{
whereClause = String.Format("where {0}", parameters.FilterExpression);
}
// replace dynamic components
query = String.Format(query, whereClause, sortExpression, start, (start + pageLength));
DbCommand command = database.GetSqlStringCommand(query);
command.CommandTimeout = DataRepository.Provider.DefaultCommandTimeout;
return(command);
}
