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);
        }
Example #3
0
        /// <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);
            }
            
        }
Example #5
0
File: Sql.cs Project: jlsg/PetaPoco
        public Sql Append(Sql sql)
        {
            if (_rhs != null)
                _rhs.Append(sql);
            else
                _rhs = sql;

            return this;
        }
Example #6
0
        /// <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));
        }
Example #7
0
        /// <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));
        }
Example #8
0
 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"));
 }
Example #9
0
		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");
		}
Example #10
0
        /// <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);
            }
        }
Example #11
0
		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");
		}
Example #12
0
        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"));
        }
Example #13
0
		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");
		}
Example #14
0
		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);
		}
Example #15
0
File: Sql.cs Project: hbulzy/SYS
 public Sql Append(Sql sql)
 {
     if (this._rhs != null)
     {
         this._rhs.Append(sql);
     }
     else
     {
         this._rhs = sql;
     }
     return this;
 }
Example #16
0
		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");
		}
Example #17
0
    /// <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();
    }
Example #18
0
        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"));
        }
Example #19
0
 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);
 }
Example #20
0
        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);
        }
Example #21
0
        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);
        }
Example #23
0
 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]);
             }
         }
     }
 }
Example #30
0
        /// <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());
            }
        }
Example #31
0
        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);
        }
Example #34
0
 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));
 }
Example #35
0
		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");
		}
Example #36
0
		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");
		}
Example #37
0
		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");
		}
Example #38
0
		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");
		}
Example #39
0
		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");
			});
		}
Example #40
0
		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");
		}
Example #41
0
		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");
		}
Example #42
0
 /// <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);
 }
Example #43
0
        /// <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);
        }
Example #44
0
    /// <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);
        }
    }
Example #45
0
 /// <summary>
 /// 获取第一列组成的集合
 /// </summary>
 /// <param name="sql">PetaPoco.Sql</param>
 public IEnumerable <object> FetchFirstColumn(PetaPoco.Sql sql)
 {
     return(FetchFirstColumn(sql.SQL, sql.Arguments));
 }
Example #46
0
        /// <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");
        }
Example #47
0
        /// <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));
        }
Example #48
0
		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));
        }