public ActionResult ExistingCustRec(string fn, string sn, string ph, string em)
{
var sql = new PetaPoco.Sql("Select * from Customer where 1=1");
if (fn != null && fn.Length > 0)
{
sql.Append($" and LOWER(FName) like '%{fn.ToLower()}%'");
}
if (sn != null && fn.Length > 0)
{
sql.Append($" and LOWER(SName) like '%{sn.ToLower()}%'");
}
if (ph != null && fn.Length > 0)
{
sql.Append($" and Phone like '%{ph}%'");
}
if (em != null && fn.Length > 0)
{
sql.Append($" and LOWER(Email) like '%{em.ToLower()}%'");
}
var recs = db.Query <CustomerDets>(sql);
return(PartialView("CustomerSearchPartial", recs));
}
private IList<ScalarFunction> queryForScalarFunctions(Schema schema)
{
var sql = new Sql(@"SELECT
J.[name] AS FunctionName
, J.object_id AS FunctionId
, EP.value AS [Description]
, R.DATA_TYPE AS ReturnDataType
, R.CHARACTER_MAXIMUM_LENGTH AS ReturnTypeMaximumLength
, CONVERT(INT, R.NUMERIC_PRECISION) AS ReturnTypePrecision
, CONVERT(INT, R.NUMERIC_SCALE) AS ReturnTypeScale
FROM sys.objects AS J
INNER JOIN sys.schemas AS S
ON ( J.schema_id = S.schema_id )
INNER JOIN INFORMATION_SCHEMA.ROUTINES AS R
ON ( R.SPECIFIC_SCHEMA = S.[name] AND R.SPECIFIC_NAME = J.[name] )
LEFT OUTER JOIN sys.extended_properties AS EP
ON ( EP.class = 1 AND EP.name = 'MS_Description' AND EP.major_id = J.object_id AND EP.minor_id = 0 )
WHERE J.schema_id = @0
AND J.[type] = 'FN'
ORDER BY J.[name];", schema.SchemaId);
return this.peta.Fetch<ScalarFunction>(sql);
}
/// <summary>
/// 分页获取主键
/// </summary>
/// <param name="userQuery">查询用户的条件</param>
/// <param name="pageSize">每页记录数</param>
/// <param name="pageIndex">页码</param>
/// <returns></returns>
public PagingEntityIdCollection FetchPagingPrimaryKeys(UserQuery userQuery, int pageSize, int pageIndex)
{
PetaPoco.Sql sql = sql = PetaPoco.Sql.Builder;
buildSqlWhere(userQuery, ref sql);
return(CreateDAO().FetchPagingPrimaryKeys <User>((long)pageSize, pageSize, pageIndex, sql));
}
public void SyncData()
{
try
{
//查询最后一次同步时间
channelhistoryarchivesync syncTime = channelhistoryarchivesync.SingleOrDefault("where id=1");
if (syncTime == null)
{
syncTime = new channelhistoryarchivesync();
syncTime.date = DateTime.Now.AddYears(-1);
syncTime.date_created = DateTime.Now;
syncTime.Insert();
}
DateTime syncDate = DateTime.Now.Date;
//查询之前没有同步的数据
Sql channelhistorySql = new Sql();
channelhistorySql.Append(
"select channelNo,DATE_FORMAT(date_created,'%Y-%m-%d') as datestr,count(id) as visitcount")
.Append("from channelHistory")
.Append("WHERE date_created > @0", syncTime.date)
.Append("WHERE date_created <= @0", syncDate)
.Append("group by datestr,channelNo");
IEnumerable<channelhistory> channelhistoryDayList = videoContextDB.GetInstance().Query<channelhistory>(channelhistorySql);
foreach (var channelhistory in channelhistoryDayList)
{
bool isExists = channelhistoryarchive.Exists(
"where channelNo=@0 and date=@1",
channelhistory.channelNo,
channelhistory.datestr);
if (!isExists)
{
channelhistoryarchive channelhistoryarchiveModel = new channelhistoryarchive();
channelhistoryarchiveModel.channelNo = channelhistory.channelNo;
channelhistoryarchiveModel.date = channelhistory.datestr;
channelhistoryarchiveModel.realcount = channelhistory.visitcount;
channelhistoryarchiveModel.date_created = DateTime.Now;
channelhistoryarchiveModel.paymentstate = false;
channelhistoryarchiveModel.Insert();
}
}
//同步的日期
syncTime.date = syncDate;
syncTime.Update();
this.OutPutDialogString(ResponseStatus.Success, "同步成功", "ChannelInfoArchiveList", string.Empty, CallbackType.forward, string.Empty);
}
catch (Exception ex)
{
this.OutPutDialogString(ResponseStatus.Fail, "同步失败", "ChannelInfoArchiveList", string.Empty, CallbackType.forward, string.Empty);
}
}
public Sql Append(Sql sql)
{
if (_rhs != null)
_rhs.Append(sql);
else
_rhs = sql;
return this;
}
/// <summary>
/// 获取前topNumber条记录
/// </summary>
/// <typeparam name="T">The Type representing a row in the result set</typeparam>
/// <param name="topNumber">前多少条数据</param>
/// <param name="sql">PetaPoco.Sql<remarks>要求必须是完整的sql语句</remarks></param>
public IEnumerable <T> FetchTop <T>(int topNumber, PetaPoco.Sql sql)
{
string sqlString = sql.SQL;
object[] args = sql.Arguments;
string sqlTop = BuildTopSql(topNumber, sqlString);
return(Fetch <T>(sqlTop, args));
}
/// <summary>
/// 获取前topNumber条记录
/// </summary>
/// <param name="topNumber">前多少条数据</param>
/// <param name="sql">PetaPoco.Sql</param>
public IEnumerable <object> FetchTopPrimaryKeys <TEntity>(int topNumber, PetaPoco.Sql sql) where TEntity : IEntity
{
string sqlString = sql.SQL;
object[] args = sql.Arguments;
string sqlTop = BuildTopSql <TEntity>(topNumber, sqlString);
return(FetchFirstColumn(sqlTop, args));
}
public void should_return_Sql_object_from_constructor_with_two_parameters()
{
string sqlStatemet = "SELECT * FROM User WHERE UserId = @0 AND UserName = @1";
object[] arguments = new object[2]{ 1, "UserName"};
Sql sql = new Sql(sqlStatemet, arguments);
Assert.IsTrue(sql.SQL == sqlStatemet);
Assert.IsTrue(sql.Arguments[0].Equals(1));
Assert.IsTrue(sql.Arguments[1].Equals("UserName"));
}
public void single_arg()
{
var sql = new Sql();
sql.Append("arg @0", "a1");
Assert.AreEqual(sql.SQL, "arg @0");
Assert.AreEqual(sql.Arguments.Length, 1);
Assert.AreEqual(sql.Arguments[0], "a1");
}
/// <summary>
/// 从UserQuery构建PetaPoco.Sql的where条件
/// </summary>
/// <param name="userQuery">UserQuery查询条件</param>
/// <param name="sql">PetaPoco.Sql对象</param>
private void buildSqlWhere(UserQuery userQuery, ref PetaPoco.Sql sql)
{
if (sql == null)
{
sql = PetaPoco.Sql.Builder;
}
if (!string.IsNullOrEmpty(userQuery.AccountEmailFilter))
{
sql.Where("AccountEmail like @0", "%" + StringUtility.StripSQLInjection(userQuery.AccountEmailFilter) + "%");
}
if (userQuery.IsActivated.HasValue)
{
sql.Where("IsActivated = @0", userQuery.IsActivated);
}
if (userQuery.IsBanned.HasValue)
{
sql.Where("IsBanned = @0", userQuery.IsBanned);
}
if (userQuery.IsModerated.HasValue)
{
if (userQuery.IsModerated.Value)
{
sql.Where("IsModerated = @0 or IsForceModerated = @0", userQuery.IsModerated);
}
else
{
sql.Where("IsModerated = @0 and IsForceModerated = @0", userQuery.IsModerated);
}
}
if (!string.IsNullOrEmpty(userQuery.Keyword))
{
sql.Where("UserName like @0 or TrueName like @0 or NickName like @0", "%" + StringUtility.StripSQLInjection(userQuery.Keyword) + "%");
}
if (!string.IsNullOrEmpty(userQuery.RoleName))
{
sql.Where("UserId in (Select UserId from tn_UsersInRoles where RoleName = @0)", userQuery.RoleName);
}
if (userQuery.RegisterTimeLowerLimit.HasValue)
{
sql.Where("DateCreated >= @0", userQuery.RegisterTimeLowerLimit.Value.ToUniversalTime());
}
if (userQuery.RegisterTimeUpperLimit.HasValue)
{
sql.Where("DateCreated <= @0", userQuery.RegisterTimeUpperLimit.Value.AddDays(1).ToUniversalTime());
}
if (userQuery.UserRankLowerLimit.HasValue)
{
sql.Where("Rank >= @0", userQuery.UserRankLowerLimit);
}
if (userQuery.UserRankUpperLimit.HasValue)
{
sql.Where("Rank<=@0", userQuery.UserRankUpperLimit);
}
}
public void multiple_args()
{
var sql = new Sql();
sql.Append("arg @0 @1", "a1", "a2");
Assert.AreEqual(sql.SQL, "arg @0 @1");
Assert.AreEqual(sql.Arguments.Length, 2);
Assert.AreEqual(sql.Arguments[0], "a1");
Assert.AreEqual(sql.Arguments[1], "a2");
}
public void ConsecutiveOrderBy()
{
var sql = new Sql()
.Append("SELECT * FROM blah");
sql.Append("ORDER BY x");
sql.Append("ORDER BY y");
Expect(sql.SQL, Is.EqualTo("SELECT * FROM blah\nORDER BY x\n, y"));
}
public void unordered_args()
{
var sql = new Sql();
sql.Append("arg @2 @1", "a1", "a2", "a3");
Assert.AreEqual(sql.SQL, "arg @0 @1");
Assert.AreEqual(sql.Arguments.Length, 2);
Assert.AreEqual(sql.Arguments[0], "a3");
Assert.AreEqual(sql.Arguments[1], "a2");
}
public void simple_append()
{
var sql = new Sql();
sql.Append("LINE 1");
sql.Append("LINE 2");
sql.Append("LINE 3");
Assert.AreEqual(sql.SQL, "LINE 1\nLINE 2\nLINE 3");
Assert.AreEqual(sql.Arguments.Length, 0);
}
public Sql Append(Sql sql)
{
if (this._rhs != null)
{
this._rhs.Append(sql);
}
else
{
this._rhs = sql;
}
return this;
}
public void repeated_args()
{
var sql = new Sql();
sql.Append("arg @0 @1 @0 @1", "a1", "a2");
Assert.AreEqual(sql.SQL, "arg @0 @1 @2 @3");
Assert.AreEqual(sql.Arguments.Length, 4);
Assert.AreEqual(sql.Arguments[0], "a1");
Assert.AreEqual(sql.Arguments[1], "a2");
Assert.AreEqual(sql.Arguments[2], "a1");
Assert.AreEqual(sql.Arguments[3], "a2");
}
/// <summary>
/// 存入中獎名單
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnSave_Click(object sender, EventArgs e)
{
PetaPoco.Sql sql = PetaPoco.Sql.Builder;
sql.Append(string.Format("UPDATE {0} SET {1}='1' WHERE {2} IN (SELECT MAX({2}) FROM {0} WHERE {4} IN ({3}) GROUP BY {4})", TableName, WinFlagColumn, PKColumn, Session["DrawIDs"], DistinctColumn));
patwGridView1.Visible = false;
btnExport.Visible = false;
btnSave.Visible = false;
db.Execute(sql);
PatwCommon.RegisterClientScriptAlert(this.Page, "存入成功");
Query();
}
public void append_with_args2()
{
var sql = new Sql();
sql.Append("l1");
sql.Append("l2 @0 @1", "a1", "a2");
sql.Append("l3 @0", "a3");
Expect(sql.SQL, Is.EqualTo("l1\nl2 @0 @1\nl3 @2"));
Expect(sql.Arguments.Length, Is.EqualTo(3));
Expect(sql.Arguments[0], Is.EqualTo("a1"));
Expect(sql.Arguments[1], Is.EqualTo("a2"));
Expect(sql.Arguments[2], Is.EqualTo("a3"));
}
private PetaPoco.Sql GetSql_CommentPageIndexInParentComments(long parentId, SortBy_Comment sortBy)
{
PetaPoco.Sql sql = PetaPoco.Sql.Builder;
sql.Where("parentId=@0", parentId);
if (sortBy == SortBy_Comment.DateCreated)
{
sql.OrderBy("id");
}
else
{
sql.OrderBy("id desc");
}
return(sql);
}
public int GetPageIndexForCommentInParentCommens(long commentId, long parentId, SortBy_Comment sortBy)
{
int pageIndex = 1;
string cacheKey = GetCacheKey_GetChildren(parentId, sortBy);
PagingEntityIdCollection peic = cacheService.Get <PagingEntityIdCollection>(cacheKey);
if (peic == null)
{
peic = CreateDAO().FetchPagingPrimaryKeys <Comment>(PrimaryMaxRecords, PageSize * CacheablePageCount, 1, GetSql_CommentPageIndexInParentComments(parentId, sortBy));
peic.IsContainsMultiplePages = true;
cacheService.Add(cacheKey, peic, CachingExpirationType.ObjectCollection);
}
if (peic != null)
{
IList <long> commentIds = peic.GetTopEntityIds(peic.Count).Cast <long>().ToList();
int commentIndex = commentIds.IndexOf(commentId);
if (commentIndex > 0)
{
pageIndex = commentIndex / ChildPageSize + 1;
}
else
{
PetaPoco.Sql sql = PetaPoco.Sql.Builder
.Select("Count(Id)")
.From("tn_Comments")
.Where("ParentId=@0", parentId);
switch (sortBy)
{
case SortBy_Comment.DateCreated:
sql.Where("Id<@0", commentId);
break;
case SortBy_Comment.DateCreatedDesc:
sql.Where("Id>@0", commentId);
break;
default:
sql.Where("Id<@0", commentId);
break;
}
commentIndex = CreateDAO().FirstOrDefault <int>(sql);
if (commentIndex > 0)
{
pageIndex = commentIndex / ChildPageSize + 1;
}
}
}
return(pageIndex);
}
public void append_instances()
{
var sql = new Sql("l0 @0", "a0");
var sql1 = new Sql("l1 @0", "a1");
var sql2 = new Sql("l2 @0", "a2");
Expect(sql.Append(sql1), Is.SameAs(sql));
Expect(sql.Append(sql2), Is.SameAs(sql));
Expect(sql.SQL, Is.EqualTo("l0 @0\nl1 @1\nl2 @2"));
Expect(sql.Arguments.Length, Is.EqualTo(3));
Expect(sql.Arguments[0], Is.EqualTo("a0"));
Expect(sql.Arguments[1], Is.EqualTo("a1"));
Expect(sql.Arguments[2], Is.EqualTo("a2"));
}
private IList<Table> queryForTables(Schema schema)
{
var sql = new Sql(@"SELECT T.name AS TableName
, COALESCE(EP.value, '') AS [Description]
, T.object_id AS TableId
FROM sys.tables AS T
LEFT OUTER JOIN sys.extended_properties AS EP
ON ( EP.major_id = T.object_id AND EP.minor_id = 0 AND EP.name = 'MS_Description' )
WHERE T.schema_id = @0
ORDER BY T.name", schema.SchemaId);
return this.peta.Fetch<Table>(sql);
}
private PetaPoco.Sql GetSql_CommentPageIndex(string tenantType, long commentedObjectId, SortBy_Comment sortBy)
{
PetaPoco.Sql sql = PetaPoco.Sql.Builder;
sql.Where("tenantTypeId=@0", tenantType);
sql.Where("commentedObjectId=@0", commentedObjectId);
sql.Where("parentId=0");
if (sortBy == SortBy_Comment.DateCreated)
{
sql.OrderBy("id");
}
else
{
sql.OrderBy("id desc");
}
return(sql);
}
private IList<StoredProcedure> queryForStoredProcedures(Schema schema)
{
var sql = new Sql(@"SELECT
SP.[name] AS ProcedureName
, SP.object_id AS ProcedureId
, EP.value AS [Description]
FROM sys.procedures AS SP
LEFT OUTER JOIN sys.extended_properties AS EP
ON ( EP.class = 1 AND EP.name = 'MS_Description' AND EP.major_id = SP.object_id AND EP.minor_id = 0 )
WHERE SP.schema_id = @0
ORDER BY SP.[name];", schema.SchemaId);
return this.peta.Fetch<StoredProcedure>(sql);
}
private IList<View> getViewsForSchema(Schema schema)
{
var sql = new Sql(@"SELECT
V.object_id AS ViewId
, V.[name] AS ViewName
, EP.value AS [Description]
FROM sys.views AS V
LEFT OUTER JOIN sys.extended_properties AS EP
ON ( V.object_id = EP.major_id AND EP.class = 1 AND EP.minor_id = 0 AND EP.[name] = 'MS_Description' )
WHERE schema_id = @0
ORDER BY V.[name];", schema.SchemaId);
return this.peta.Fetch<View>(sql);
}
private IList<string> getIndexColumnNames(Index index, IDbObject parent)
{
var sql = new Sql(@"SELECT C.[name]
FROM sys.indexes AS I
INNER JOIN sys.index_columns AS IC
ON ( I.index_id = IC.index_id AND I.object_id = IC.object_id )
INNER JOIN sys.columns AS C
ON ( C.object_id = IC.object_id AND C.column_id = IC.column_id )
WHERE I.object_id = @0
AND I.index_id = @1
ORDER BY IC.key_ordinal, C.[name];", parent.ObjectId, index.IndexId);
return this.peta.Fetch<string>(sql);
}
private List<Schema> queryForSchemas()
{
//TODO: add description metadata from extended properties
var sql = new Sql(@"SELECT S.[name] AS SchemaName
, S.schema_id AS SchemaId
, EP.value AS [Description]
FROM sys.schemas AS S
LEFT OUTER JOIN sys.extended_properties AS EP
ON ( S.schema_id = EP.major_id AND EP.name = 'MS_Description' )
WHERE S.[name] NOT LIKE 'db__%'
AND S.[name] NOT IN ( 'sys', 'INFORMATION_SCHEMA' )
ORDER BY S.[name];");
return this.peta.Fetch<Schema>(sql);
}
private IList<Index> getTableOrViewIndexes(IDbObject parent)
{
var sql = new Sql(@"SELECT I.[name] AS IndexName
, I.index_id AS IndexId
, EP.value AS [Description]
, I.is_unique AS IsUnique
, I.is_primary_key AS IsPrimaryKey
, I.type_desc AS IndexTypeDescription
FROM sys.indexes AS I
LEFT OUTER JOIN sys.extended_properties AS EP
ON ( EP.class = 7 AND EP.name = 'MS_Description' AND EP.major_id = I.object_id AND EP.minor_id = I.index_id )
WHERE object_id = @0
AND I.index_id != 0
ORDER BY I.[name];", parent.ObjectId);
return this.peta.Fetch<Index>(sql);
}
/// <summary>
/// 將搜尋控制項的值轉換為 SQL Statement
/// </summary>
/// <param name="sql">PetaPoco SQL</param>
/// <param name="SearchItems">用於搜尋的控制項集合</param>
/// <param name="plSearch">搜尋區塊控制項</param>
/// <param name="ViewState">ViewState</param>
public static void ConvertControlToSQL(PetaPoco.Sql sql, IList <SearchItem> SearchItems, Panel plSearch, StateBag ViewState)
{
foreach (SearchItem item in SearchItems)
{
if (((Panel)plSearch).FindControl(item.ControlName).GetType() == typeof(CheckBox))
{
if ((bool)ViewState[item.ControlName])
{
sql.Append(item.SQLStatement, ViewState[item.ControlName]);
}
}
else
{
if (!string.IsNullOrEmpty(Convert.ToString(ViewState[item.ControlName])))
{
sql.Append(item.SQLStatement, ViewState[item.ControlName]);
}
}
}
}
/// <summary>
/// 从UserQuery构建PetaPoco.Sql的where条件
/// </summary>
/// <param name="userQuery">UserQuery查询条件</param>
/// <param name="sql">PetaPoco.Sql对象</param>
private void buildSqlWhere(UserQuery userQuery, ref PetaPoco.Sql sql)
{
if (sql == null)
{
sql = PetaPoco.Sql.Builder;
}
if (!string.IsNullOrEmpty(userQuery.Keyword))
{
sql.Where("UserName like @0", "%" + StringUtility.StripSQLInjection(userQuery.Keyword) + "%");
}
if (userQuery.RegisterTimeLowerLimit.HasValue)
{
sql.Where("DateCreated >= @0", userQuery.RegisterTimeLowerLimit.Value.ToUniversalTime());
}
if (userQuery.RegisterTimeUpperLimit.HasValue)
{
sql.Where("DateCreated <= @0", userQuery.RegisterTimeUpperLimit.Value.AddDays(1).ToUniversalTime());
}
}
public static PetaPoco.Page<PetapocoOrm.MonitorLog.FileWatch> GetList(int pageSize, int pageIndex, int serverId, string filename)
{
PetaPoco.Sql sb = new Sql();
sb.Append("select * from FileWatch where 1=1");
if (serverId >= 1)
{
sb.Append(" and ServerName in ( select ServerName from ServerList where id=" + serverId + ") ", serverId);
}
if (!string.IsNullOrEmpty(filename))
{
sb.Append(" and filename like '%" + WXQ.Common.StringPlus.SQLSafe(filename) + "%'");
}
sb.Append(" order by filename asc , adddate desc ");
PetaPoco.Page<PetapocoOrm.MonitorLog.FileWatch> result = PetapocoOrm.MonitorLog.Db.GetInstance().Page<PetapocoOrm.MonitorLog.FileWatch>(pageIndex, pageSize, sb.ToString());
return result;
}
private IList<TableFunction> queryForTableFunctions(Schema schema)
{
var sql = new Sql(@"SELECT
J.[name] AS FunctionName
, J.object_id AS FunctionId
, EP.value AS [Description]
FROM sys.objects AS J
INNER JOIN sys.schemas AS S
ON ( J.schema_id = S.schema_id )
INNER JOIN INFORMATION_SCHEMA.ROUTINES AS R
ON ( R.SPECIFIC_SCHEMA = S.[name] AND R.SPECIFIC_NAME = J.[name] )
LEFT OUTER JOIN sys.extended_properties AS EP
ON ( EP.class = 1 AND EP.name = 'MS_Description' AND EP.major_id = J.object_id AND EP.minor_id = 0 )
WHERE J.schema_id = @0
AND J.[type] IN ( 'TF', 'IF' )
ORDER BY J.[name];", schema.SchemaId);
return this.peta.Fetch<TableFunction>(sql);
}
private IList<Column> queryForFunctionColumns(TableFunction func)
{
var sql = new Sql(@"SELECT
C.name AS ColumnName
, Y.[name] AS BaseDataTypeName
, C.is_nullable AS AllowNull
FROM sys.objects AS T
INNER JOIN sys.schemas AS S
ON ( T.schema_id = S.schema_id )
INNER JOIN sys.columns AS C
ON ( T.object_id = C.object_id )
INNER JOIN sys.types AS Y
ON ( Y.user_type_id = C.user_type_id )
WHERE T.object_id = @0
AND T.type = 'TF'
ORDER BY C.column_id, C.name;", func.FunctionId);
return this.peta.Fetch<Column>(sql);
}
public List <TRet> Fetch <T1, T2, T3, TRet>(long page, long itemsPerPage, Func <T1, T2, T3, TRet> cb, Sql sql)
{
return(SkipTake <T1, T2, T3, TRet>((page - 1) * itemsPerPage, itemsPerPage, cb, sql.SQL, sql.Arguments));
}
public void ConsecutiveOrderBy()
{
var sql = new Sql()
.Append("SELECT * FROM blah");
sql.Append("ORDER BY x");
sql.Append("ORDER BY y");
Assert.AreEqual(sql.SQL, "SELECT * FROM blah\nORDER BY x\n, y");
}
public void ConsecutiveWhere()
{
var sql = new Sql()
.Append("SELECT * FROM blah");
sql.Append("WHERE x");
sql.Append("WHERE y");
Assert.AreEqual(sql.SQL, "SELECT * FROM blah\nWHERE x\nAND y");
}
public void append_instances()
{
var sql = new Sql("l0 @0", "a0");
var sql1 = new Sql("l1 @0", "a1");
var sql2 = new Sql("l2 @0", "a2");
Assert.AreSame(sql.Append(sql1), sql);
Assert.AreSame(sql.Append(sql2), sql);
Assert.AreEqual(sql.SQL, "l0 @0\nl1 @1\nl2 @2");
Assert.AreEqual(sql.Arguments.Length, 3);
Assert.AreEqual(sql.Arguments[0], "a0");
Assert.AreEqual(sql.Arguments[1], "a1");
Assert.AreEqual(sql.Arguments[2], "a2");
}
public void mysql_user_vars()
{
var sql = new Sql();
sql.Append("arg @@user1 @2 @1 @@@system1", "a1", "a2", "a3");
Assert.AreEqual(sql.SQL, "arg @@user1 @0 @1 @@@system1");
Assert.AreEqual(sql.Arguments.Length, 2);
Assert.AreEqual(sql.Arguments[0], "a3");
Assert.AreEqual(sql.Arguments[1], "a2");
}
public void invalid_arg_index()
{
Assert.Throws<ArgumentOutOfRangeException>(()=>{
var sql = new Sql();
sql.Append("arg @0 @1", "a0");
Assert.AreEqual(sql.SQL, "arg @0 @1");
});
}
public void append_with_args2()
{
var sql = new Sql();
sql.Append("l1");
sql.Append("l2 @0 @1", "a1", "a2");
sql.Append("l3 @0", "a3");
Assert.AreEqual(sql.SQL, "l1\nl2 @0 @1\nl3 @2");
Assert.AreEqual(sql.Arguments.Length, 3);
Assert.AreEqual(sql.Arguments[0], "a1");
Assert.AreEqual(sql.Arguments[1], "a2");
Assert.AreEqual(sql.Arguments[2], "a3");
}
public void mixed_named_and_numbered_args()
{
var sql = new Sql();
sql.Append("arg @0 @name @1 @password @2", "a1", "a2", "a3", new { name = "n", password = "******" });
Assert.AreEqual(sql.SQL, "arg @0 @1 @2 @3 @4");
Assert.AreEqual(sql.Arguments.Length, 5);
Assert.AreEqual(sql.Arguments[0], "a1");
Assert.AreEqual(sql.Arguments[1], "n");
Assert.AreEqual(sql.Arguments[2], "a2");
Assert.AreEqual(sql.Arguments[3], "p");
Assert.AreEqual(sql.Arguments[4], "a3");
}
/// <summary>
/// 清除用户资料（删除用户时使用）
/// </summary>
/// <param name="userId">用户id</param>
public void CleanByUser(long userId)
{
//清除用户数据的时候不需要考虑缓存
PetaPoco.Sql sql_delete = PetaPoco.Sql.Builder.Append("Delete from tn_InviteFriendRecords where UserId=@0 or InvitedUserId=@0", userId);
CreateDAO().Execute(sql_delete);
}
/// <summary>
/// 获取分页查询数据
/// </summary>
/// <param name="pageSize">每页记录数</param>
/// <param name="pageIndex">当前页码(从1开始)</param>
/// <param name="sql">获取当前页码的数据的<see cref="PetaPoco.Sql">PetaPoco.Sql</see></param>
/// <returns></returns>
protected virtual PagingDataSet <TEntity> GetPagingEntities(int pageSize, int pageIndex, PetaPoco.Sql sql)
{
PagingEntityIdCollection peic = CreateDAO().FetchPagingPrimaryKeys <TEntity>(PrimaryMaxRecords, pageSize, pageIndex, sql);
IEnumerable <TEntity> entitiesOfPage = PopulateEntitiesByEntityIds(peic.GetPagingEntityIds(pageSize, pageIndex));
PagingDataSet <TEntity> pagingEntities = new PagingDataSet <TEntity>(entitiesOfPage)
{
PageIndex = pageIndex,
PageSize = pageSize,
TotalRecords = peic.TotalRecords
};
return(pagingEntities);
}
/// <summary>
/// 抽獎方法
/// </summary>
/// <param name="n">欲抽出數量</param>
/// <param name="PKColumn">主索引鍵</param>
/// <param name="MaxDrawQuota">中獎名額</param>
/// <param name="WinFlagColumn">中獎識別欄位</param>
/// <param name="DistinctColumn">排除重複的欄位（判斷使用者身份的唯一值，例如：E-mail、Facebook UID 等）</param>
/// <param name="TableName">資料表</param>
/// <param name="BasicCondition">基本 SQL 條件</param>
/// <param name="IsGroup">若為真，則每個人中獎機率相同；若為假，則名單越多者中獎機率越高。</param>
/// <returns>回傳 DrawResult 類別，其下有 Result（是否成功，布林值）與 Msg（回傳訊息，若成功，則為不重複的欄位值）</returns>
public static DrawResult MakeDraw(int n, int MaxDrawQuota, string PKColumn, string WinFlagColumn, string DistinctColumn, string TableName, string BasicCondition, bool IsGroup)
{
PetaPoco.Database db = new PetaPoco.Database("conn");
int counter = 0;
DrawResult result = new DrawResult();
PetaPoco.Sql sql = PetaPoco.Sql.Builder;
sql.Append(String.Format("SELECT MAX({0}) FROM {1} WHERE 1=1", PKColumn, TableName));
sql.Append(BasicCondition);
if (IsGroup)
{
sql.Append("GROUP BY [" + DistinctColumn + "]");
}
var data = db.Query <DataModel_a12SupauCheckin>(sql);
counter = data.Count();
if (counter < n)
{
result.Result = false;
result.Msg = "名單不足以抽出這樣的數量喔！";
return(result);
}
if (n < 1)
{
result.Result = false;
result.Msg = "數量請至少為 1。";
return(result);
}
if (n > MaxDrawQuota)
{
result.Result = false;
result.Msg = "抽出名額不得大於中獎名額 " + MaxDrawQuota + " 名 喔！";
return(result);
}
#region 檢查剩餘名額
sql = PetaPoco.Sql.Builder;
sql.Append(String.Format("SELECT {0} FROM {1} WHERE {2}='1'", PKColumn, TableName, WinFlagColumn));
sql.Append(BasicCondition);
var r = db.Query <DataModel_a12SupauCheckin>(sql);
// 若目前中獎人數大於等於中獎名額
if (r.Count() >= MaxDrawQuota)
{
result.Result = false;
result.Msg = "名額已滿";
return(result);
}
#endregion
if (!IsGroup)
{
if (n == 1)
{
sql = PetaPoco.Sql.Builder;
sql.Append(String.Format("SELECT TOP 1 {0} FROM {1} WHERE 1=1", DistinctColumn, TableName));
sql.Append(BasicCondition);
sql.Append("ORDER BY NEWID()");
var a = db.SingleOrDefault <DataModel_a12SupauCheckin>(sql);
result.Result = true;
result.Msg = "'" + a.sFBUID.ToString() + "'";
return(result);
}
else
{
string list_column = MakeDraw(n - 1, MaxDrawQuota, PKColumn, WinFlagColumn, DistinctColumn, TableName, BasicCondition, IsGroup).Msg;
sql = PetaPoco.Sql.Builder;
sql.Append(String.Format("SELECT TOP 1 * FROM {0} WHERE 1=1", TableName));
sql.Append(String.Format("{0} AND [{1}] NOT IN ({2})", BasicCondition, DistinctColumn, list_column));
sql.Append("ORDER BY NEWID()");
var a = db.SingleOrDefault <DataModel_a12SupauCheckin>(sql);
result.Result = true;
result.Msg = list_column + ",'" + a.sFBUID.ToString() + "'";
return(result);
}
}
else
{
sql = PetaPoco.Sql.Builder;
sql.Append(String.Format("SELECT TOP {0} {1} FROM {2} WHERE 1=1", n, DistinctColumn, TableName));
sql.Append(BasicCondition);
sql.Append(String.Format("GROUP BY [{0}] ORDER BY NEWID()", DistinctColumn));
var a = db.Query <DataModel_a12SupauCheckin>(sql);
string return_data = "";
foreach (var item in a)
{
return_data += ",'" + item.sFBUID + "'";
}
return_data = return_data.Substring(1, return_data.Length - 1);
result.Result = true;
result.Msg = return_data;
return(result);
}
}
/// <summary>
/// 获取第一列组成的集合
/// </summary>
/// <param name="sql">PetaPoco.Sql</param>
public IEnumerable <object> FetchFirstColumn(PetaPoco.Sql sql)
{
return(FetchFirstColumn(sql.SQL, sql.Arguments));
}
/// <summary>
/// 从MicroblogQuery构建PetaPoco.Sql的where条件
/// </summary>
/// <param name="query">MicroblogQuery查询条件</param>
/// <param name="sql">PetaPoco.Sql对象</param>
private void BuildSqlWhere_MicroblogQuery(MicroblogQuery query, ref PetaPoco.Sql sql)
{
if (sql == null)
{
sql = PetaPoco.Sql.Builder;
}
if (query.UserId.HasValue)
{
sql.Where("UserId = @0", query.UserId);
}
if (!string.IsNullOrEmpty(query.TenantTypeId))
{
sql.Where("TenantTypeId = @0", query.TenantTypeId);
}
if (query.OwnerId.HasValue)
{
sql.Where("OwnerId = @0", query.OwnerId);
}
if (query.isOriginal.HasValue && query.isOriginal.Value)
{
sql.Where("OriginalMicroblogId = 0");
}
else if (query.MediaType.HasValue)
{
switch (query.MediaType)
{
case MediaType.Image:
sql.Where("HasPhoto = 1");
break;
case MediaType.Video:
sql.Where("HasVideo = 1");
break;
case MediaType.Audio:
sql.Where("HasMusic = 1");
break;
}
}
if (!string.IsNullOrEmpty(query.Keyword))
{
sql.Where("Body like @0", "%" + StringUtility.StripSQLInjection(query.Keyword) + "%");
}
if (query.StartDate.HasValue)
{
sql.Where("DateCreated >= @0", query.StartDate);
}
//reply:已修改
if (query.EndDate.HasValue)
{
sql.Where("DateCreated < @0", query.EndDate.Value.AddDays(1));
}
if (query.AuditStatus.HasValue)
{
sql.Where("AuditStatus = @0", (int)query.AuditStatus);
}
sql.OrderBy("MicroblogId desc");
}
/// <summary>
/// 获取可分页的主键集合
/// </summary>
/// <typeparam name="TEntity">实体</typeparam>
/// <param name="maxRecords">最大返回记录数</param>
/// <param name="pageSize">每页记录数</param>
/// <param name="pageIndex">当前页码(从1开始)</param>
/// <param name="primaryKey">主键</param>
/// <param name="sql">PetaPoco.Sql <remarks>要求必须是完整的sql语句</remarks></param>
/// <returns>可分页的实体Id集合</returns>
public PagingEntityIdCollection FetchPagingPrimaryKeys(long maxRecords, int pageSize, int pageIndex, string primaryKey, PetaPoco.Sql sql)
{
string sqlString = sql.SQL;
object[] args = sql.Arguments;
string sqlCount, sqlPage;
BuildPagingPrimaryKeyQueries(maxRecords, (pageIndex - 1) * pageSize, pageSize, primaryKey, sqlString, ref args, out sqlCount, out sqlPage);
// Setup the paged result
long totalRecords = ExecuteScalar <long>(sqlCount, args);
List <object> primaryKeyList = FetchFirstColumn(sqlPage, args).ToList();
return(new PagingEntityIdCollection(primaryKeyList, totalRecords));
}
public void invalid_arg_name()
{
Assert.Throws<ArgumentException>(() =>
{
var sql = new Sql();
sql.Append("arg @name1 @name2", new { x = 1, y = 2 });
Assert.AreEqual(sql.SQL, "arg @0 @1");
});
}
public static List <T> FetchOneToMany <T, T1>(this Database db, Func <T, object> key, Sql Sql)
{
var relator = new Relator();
return(db.Fetch <T, T1, T>((a, b) => relator.OneToMany(a, b, key), Sql));
}
public static List <T> FetchManyToOne <T, T1, T2, T3>(this Database db, Func <T, object> key, Sql Sql)
{
var relator = new Relator();
return(db.Fetch <T, T1, T2, T3, T>((a, b, c, d) => relator.ManyToOne(a, b, c, d, key), Sql));
}