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);
        }
        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 [email protected] and [email protected]",
                         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);
            }
            
        }
示例#3
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"));
 }
示例#4
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");
		}
示例#5
0
文件: Sql.cs 项目: jlsg/PetaPoco
        public Sql Append(Sql sql)
        {
            if (_rhs != null)
                _rhs.Append(sql);
            else
                _rhs = sql;

            return this;
        }
示例#6
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);
		}
示例#7
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");
		}
示例#8
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");
		}
示例#9
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"));
        }
示例#10
0
文件: Sql.cs 项目: hbulzy/SYS
 public Sql Append(Sql sql)
 {
     if (this._rhs != null)
     {
         this._rhs.Append(sql);
     }
     else
     {
         this._rhs = sql;
     }
     return this;
 }
示例#11
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");
		}
示例#12
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"));
        }
示例#13
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);
        }
        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<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<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);
        }
示例#20
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<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);
        }
        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);
        }
示例#23
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");
		}
示例#24
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");
		}
示例#25
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");
		}
示例#26
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");
		}
示例#27
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");
			});
		}
示例#28
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");
			});
		}
示例#29
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");
		}
示例#30
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 = "p" });

			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");
		}