public List <Order> QueryOrder(string condition, QueryPageInfo page)
{
var sql = "SELECT * FROM CPOrder where 1=1 {0} order by StartTime ";
var sqlCount = "SELECT COUNT(*) FROM CPOrder where 1=1 {0}";
var sqlSplit = "limit {0} offset {0}*{1}";
sql = string.Format(sql, condition);
sqlCount = string.Format(sqlCount, condition);
page.RecordTotal = Convert.ToInt32(_db.ExecuteScalar(sqlCount));
sqlSplit = string.Format(sqlSplit, page.PageSize, page.PageIndex - 1);
sql += sqlSplit;
List <Order> list = new List <Order>();
using (var rdr = _db.ExecuteReader(sql))
{
if (rdr == null)
{
return(list);
}
while (rdr.Read())
{
var runner = ReaderToRunner(rdr);
list.Add(runner);
}
}
return(list);
}
public static string GetFirstPartPagination(QueryPageInfo queryPageInfo)
{
if (queryPageInfo == null)
{
return(FirstPart);
}
string sTop = string.Format("TOP {0} ", queryPageInfo.PageSize);
string sOut = string.Format(
FirstPartInternal,
MyConstants.PrimaryKeyTableName,
string.Format(MySqlStatments.SelectIntoPrimaryTable, sTop));
return(sOut);
}
/// <summary>
/// Prepares an Instance level command when using pagination.
/// Pagination returns results in one or more pages of data, where the user specifies the page size.
/// The pagination feature is not implemented in this sample data access agent
/// </summary>
/// <param name="command">IDbCommand that is created</param>
/// <param name="matchingParamsCollection">Contains the query parameters that are used to generate the WHERE statement</param>
/// <param name="queryPageInfo"></param>
protected override void PrepareInstancePageQueryCommand
(
IDbCommand command,
Collection <CatalogElement[]> matchingParamsCollection,
QueryPageInfo queryPageInfo
)
{
command.CommandText = "exec sp_executeSQL @CMD";
string cmdText = GetInstanceCommandText(matchingParamsCollection, queryPageInfo);
command.Parameters.Add(new SqlParameter("@CMD", cmdText));
command.CommandType = CommandType.Text;
}
protected void Button2_Click(object sender, EventArgs e)
{
People p = new People();
p.id = "1";
p.Name = "哈哈";
p.Phone = 110;
p.Money = 1000000M;
_PeopleDAL.Update(p);
QueryPageInfo<People> queryinfo = new QueryPageInfo<People>();
queryinfo.Skip = 0;
queryinfo.Take = 5;
queryinfo.ReturnFields = "id,Name";
// queryinfo.Querys = new TermQuery(new Term("id","1"));
queryinfo.Querys = new MatchAllDocsQuery();
_PeopleDAL.QueryList(queryinfo);
Response.Write(queryinfo.Total);
Response.Write(jss.Serialize(queryinfo.Data.Select(s => new { id = s.id, name = s.Name })));
_PeopleDAL.Dispose();
}
public AeInfoExtended[] GetAeTitles(AeInfoExtended searchParams, QueryPageInfo queryPageInfo)
{
DbCommand dbCommand;
IDataReader reader;
List <AeInfoExtended> aetitles;
dbCommand = DatabaseProvider.DbProviderFactory.CreateCommand();
aetitles = new List <AeInfoExtended>();
InitializeGetAeTitlesCommand(dbCommand, searchParams, queryPageInfo);
using (reader = DatabaseProvider.ExecuteReader(dbCommand))
{
while (reader.Read())
{
aetitles.Add(GetClientInformation(reader));
}
}
return(aetitles.ToArray());
}
protected override void InitializeGetAeTitlesCommand(DbCommand command, AeInfoExtended searchParams, QueryPageInfo queryPageInfo)
{
command.CommandText =
@"SELECT * " +
@"FROM [AeInfo] " +
GenerateWhereStatement(searchParams);
if (queryPageInfo.PagingType != QueryPageInfoEnum.None)
{
string paging =
command.CommandText +=
@" ORDER BY [AETitle] " +
@" OFFSET @PageSize * (@PageNumber - 1) ROWS " +
@" FETCH NEXT @PageSize ROWS ONLY";
DbParameter p0 = command.CreateParameter();
p0.ParameterName = "@PageNumber";
p0.Value = queryPageInfo.PageNumber;
command.Parameters.Add(p0);
DbParameter p1 = command.CreateParameter();
p1.ParameterName = "@PageSize";
p1.Value = queryPageInfo.PageSize;
command.Parameters.Add(p1);
}
}
protected abstract void InitializeGetAeTitlesCommand(DbCommand dbCommand, AeInfoExtended searchParams, QueryPageInfo queryPageInfo);
private string GetInstanceCommandText(Collection <CatalogElement[]> matchingParamsCollection, QueryPageInfo queryPageInfo)
{
try
{
string sOrderByField = "SOPInstanceUID";
StringBuilder sb = new StringBuilder(500);
string s;
// Skip the pagination for now!
queryPageInfo = null;
if (queryPageInfo != null)
{
string sTemp = MyConstants.SelectCommandText.GetFirstPartPagination(queryPageInfo);
sb.Append(sTemp);
string originalTableQuery = MyConstants.SelectCommandText.FromClause.Image;
originalTableQuery += InjectJoinForInstanceQuery();
sb.Append(originalTableQuery);
string sWhere = SqlProviderUtilities.GenerateWherePaginationStatement(matchingParamsCollection, "Instance", "SOPInstanceUID", sOrderByField, queryPageInfo, originalTableQuery);
sb.Append(sWhere);
}
else
{
s = MyConstants.SelectCommandText.FirstPart;
sb.Append(s);
s = MyConstants.SelectCommandText.FromClause.Image;
sb.Append(s);
s = SqlProviderUtilities.GenerateWhereStatement(matchingParamsCollection);
sb.Append(s);
}
s = MyConstants.SelectCommandText.MIDDLE_PART;
sb.Append(s);
s = MyConstants.SelectCommandText.SelectStatements.PatientEntity;
sb.Append(s);
s = MyConstants.SelectCommandText.SelectStatements.StudyEntity;
sb.Append(s);
s = MyConstants.SelectCommandText.SelectStatements.SeriesEntity;
sb.Append(s);
s = MyConstants.SelectCommandText.SelectStatements.InstanceEntity;
sb.Append(s);
s = MyConstants.SelectCommandText.LAST_PART;
sb.Append(s);
return(sb.ToString());
}
catch (Exception exception)
{
System.Diagnostics.Debug.WriteLine(exception.Message);
System.Diagnostics.Debug.Assert(false);
throw;
}
}
private async void QueryData()
{
this.TCDataHistoryCollection.Clear();
this.Records.Clear();
this.Unknowns.Clear();
await Task.Factory.StartNew(() =>
{
QueryPageInfo page = new QueryPageInfo();
page.PageIndex = 1;
page.PageSize = 20;
var list = SqliteHelper.Instance.QueryOrder("", page);
foreach (var item in list)
{
Application.Current.Dispatcher.Invoke(() =>
{
this.TCDataHistoryCollection.Add(item);
});
}
});
this.Records.Add(new DataRecord {
Time = "12:22:00", Input = "220", X = "℃", Voltage = "23", Ammeter = "34"
});
this.Unknowns.Add(new RecordStatus {
Index = 1, Status = "未连接"
});
this.Unknowns.Add(new RecordStatus {
Index = 2, Status = "未连接"
});
this.Unknowns.Add(new RecordStatus {
Index = 3, Status = "未连接"
});
this.Unknowns.Add(new RecordStatus {
Index = 4, Status = "未连接"
});
this.Unknowns.Add(new RecordStatus {
Index = 5, Status = "未连接"
});
var line1 = new LineSeries();
line1.Values = new ChartValues <double>();
line1.Values.Add(4.0);
line1.Values.Add(15.0);
line1.Values.Add(23.0);
line1.Values.Add(5.0);
line1.Values.Add(7.0);
line1.Values.Add(9.0);
line1.Values.Add(12.0);
VSeries.Add(line1);
VLables.Add("1.0");
VLables.Add("2.0");
VLables.Add("3.0");
VLables.Add("4.0");
VLables.Add("5.0");
VLables.Add("6.0");
VLables.Add("7.0");
VLables.Add("8.0");
var line2 = new LineSeries();
line2.Values = new ChartValues <double>();
line2.Values.Add(12.0);
line2.Values.Add(8.0);
line2.Values.Add(2.0);
line2.Values.Add(10.0);
line2.Values.Add(4.0);
line2.Values.Add(9.0);
line2.Values.Add(5.0);
ASeries.Add(line2);
ALables.Add("1.0");
ALables.Add("2.0");
ALables.Add("3.0");
ALables.Add("4.0");
ALables.Add("5.0");
ALables.Add("6.0");
ALables.Add("7.0");
ALables.Add("8.0");
}