{"id":2466,"date":"2013-03-07T12:22:51","date_gmt":"2013-03-07T12:22:51","guid":{"rendered":"http:\/\/enjoyasp.net\/?p=2466"},"modified":"2013-03-21T08:10:24","modified_gmt":"2013-03-21T08:10:24","slug":"%e6%95%b0%e6%8d%ae%e5%ba%93%e7%bc%93%e5%ad%98","status":"publish","type":"post","link":"https:\/\/enjoyasp.net\/index.php\/2013\/03\/07\/%e6%95%b0%e6%8d%ae%e5%ba%93%e7%bc%93%e5%ad%98\/","title":{"rendered":"\u6570\u636e\u5e93\u7f13\u5b58"},"content":{"rendered":"

SqlDependency\u7c7b\u6709\u4e00<\/span>OnChangeEventHandler\u65b9\u6cd5\uff0c\u53ef\u6ce8\u518c<\/span>\u59d4\u6258\uff0c<\/span>\u5f53\u63a5\u6536\u5230\u6539\u53d8\u65f6\uff0c\u7528<\/span>\u59d4\u6258<\/span>\u5b9e\u65bd\u6570\u636e\u66f4\u6539\u81ea\u52a8\u901a\u77e5\u524d\u53f0<\/span>
\n\u4f8b\uff1ahttp:\/\/www.360doc.com\/content\/09\/0609\/16\/32573_3830694.shtml<\/a>
\n\u7528\u6b64\u65b9\u6cd5\u53ef\u5b9e\u73b0\u5220\u9664\u8fc7\u671f\u7684\u7f13\u5b58\u3002\u8981\u6ce8\u610f\u7684<\/span>\u662fcommand \u6307\u5b9a\u7684 commandText \u7684\u67e5\u8be2\u7ed3\u679c\u53d1\u751f\u53d8\u5316\u65f6\u89e6\u53d1 OnChange\uff0c\u4e14\u53ea\u89e6\u53d1\u4e00\u6b21\uff0c\u6545\u9700\u8981\u5728\u89e6\u53d1\u4e8b\u4ef6\u4e2d\u518d\u6b21\u5efa\u7acb\u4f9d\u8d56\u548c\u7ed1\u5b9a\u63a5\u53d7\u901a\u77e5.<\/span>sqlDependency start\u540e\uff0c\u4f1a\u5f00\u6237\u4e00\u4e2a\u7ebf\u7a0b\u7b49\u5f85\uff0c\u5f53\u901a\u77e5\u7531\u670d\u52a1\u5668\u7684service Broker\u4f20\u6765\u7684\u65f6\u5019\uff0c\u7ebf\u7a0b\u6536\u5230\u4fe1\u606f\uff0c\u8c03\u7528\u56de\u8c03\u51fd\u6570<\/span><\/p>\n

\n\tsqlDependency\u7684start\u4e0estop\u90fd\u662f\u63a7\u5236\u7ebf\u7a0b\u7684\u5f00\u5173\uff0c\u6ce8\u610f\u6b64\u7ebf\u7a0b\u662f\u5168\u5c40\u6027\u7684\uff0c\u5171\u4eab\u3002<\/span><\/div>\n

\n\u4e0d\u8fc7.net\u540c\u65f6\u6709<\/span>SqlCacheDependency\uff0c\u53ef\u81ea\u52a8\u5b8c\u6210\u5220\u9664\u7f13\u5b58\u7684\u64cd\u4f5c\u3002<\/span><\/p>\n

\u76f8\u5173sql:<\/p>\n

\n\t–\u8ba2\u9605<\/div>\n
\n\t–\u5728\u505a\u51fa\u901a\u77e5\u540e\uff0c\u4f1a\u8fdb\u884c\u5220\u9664\uff0c\u6240\u4ee5\u9700\u8981\u91cd\u65b0\u8ba2\u9605<\/div>\n
\n\t <\/div>\n
\n\tselect * from sys.dm_qn_subscriptions<\/div>\n
\n\t <\/div>\n
\n\t–\u4fe1\u606f\u5bf9\u63a5<\/div>\n
\n\tSELECT TOP 50 * <\/div>\n
\n\tFROM sys.transmission_queue<\/p>\n
\n\t\t–\u5173\u6389\u8ba2\u9605<\/div>\n
\n\t\tKILL QUERY NOTIFICATION SUBSCRIPTION  10<\/div>\n<\/div>\n

\n\u8981\u8fbe\u5230\u7684\u6548\u679c\uff1a
\n\u5c06\u6570\u636e\u5e93\u4e2d\u7684\u6570\u636e\u653e\u5230\u7f13\u5b58\u4e2d\uff0c\u5f53\u6570\u636e\u53d8\u5316\u65f6\uff0c\u81ea\u52a8\u901a\u8fc7\u5e94\u7528\u5220\u9664\u7f13\u5b58
\nSql server 7.0\/2000\u4e0b SqlCacheDependency\u4f7f\u7528\u8f6e\u8be2\u7684\u65b9\u5f0f\u8fdb\u884c\u7f13\u5b58\u5931\u6548\u68c0\u67e5\uff0c
\nSql server 2005\/2008\u4e0b\u589e\u52a0\u4f7f\u7528\u67e5\u8be2\u901a\u77e5\u65b9\u5f0f\u8fdb\u884c\u7f13\u5b58\u5931\u6548\u68c0\u67e5\u3002<\/span><\/p>\n

\u5b9e\u65bd\uff1a<\/span>
\n\u53c2\u8003\uff1ahttp:\/\/space.itpub.net\/16436858\/viewspace-630489<\/a>
\nhttp:\/\/www.cnblogs.com\/over140\/archive\/2009\/01\/15\/1376318.html<\/a><\/span><\/p>\n

\n\t1.\u68c0\u6d4b\u662f\u5426\u5df2\u7ecf\u542f\u7528Service Broker<\/p>\n

\n\t  Select DATABASEpRoPERTYEX('\u6570\u636e\u5e93\u540d\u79f0','IsBrokerEnabled')  — 1 \u8868\u793a\u5df2\u7ecf\u542f\u7528 0 \u8868\u793a\u6ca1\u6709\u542f\u7528<\/p>\n

\n\t2.\u542f\u7528Service Broker                   <\/p>\n

\n\t  ALTER DATABASE \u6570\u636e\u5e93<\/strong><\/u><\/a>\u540d\u79f0 SET ENABLE_BROKER;              <\/p>\n

<\/p>\n

\n\t –WITH ROLLBACK IMMEDIATE;<\/div>\n
\n\tGO<\/div>\n
\n\t <\/div>\n

\n\t3.\u7ed9\u60a8\u7684\u6570\u636e\u5e93\u8bbf\u95ee\u5e10\u53f7\u6388\u4e88\u6743\u9650<\/p>\n

\n\t  GRANT SUBSCRIBE QUERY NOTIFICATIONS TO test<\/p>\n

\n\t  \u6ce8\u610f\uff1a\u8fd9\u4e00\u6b65\u975e\u5e38\u91cd\u8981, \u5982\u679c\u6ca1\u6709\u6743\u9650, \u6570\u636e\u5e93\u6539\u53d8\u7684\u901a\u77e5\u5c06\u65e0\u6cd5\u63a5\u6536, cache\u6c38\u8fdc\u90fd\u4e0d\u4f1a\u88ab\u5237\u65b0\uff0c\u6ce8\u610f sa\u6388\u6b64\u6743\u9650(ms\u7981\u6b62)\uff0c \u6240\u4ee5\uff0c\u6362\u4e2a\u6570\u636e\u5e93\u8bbf\u95ee\u5e10\u53f7\u5373\u53ef.
\n\t\u6216\u8005\u7528\uff1aalter<\/span> <\/span>authorization<\/span> <\/span>on<\/span> <\/span>database<\/span>::[<<\/span>your_SSB_DB<\/span>>]<\/span> <\/span>to<\/span> <\/span>[<\/span>sa<\/span>];<\/span>
\n\t4\uff0c\u7f13\u5b58\u4ee3\u7801<\/p>\n

<\/p>\n

\n\t\/\/\/ <summary><\/div>\n
\n\t    \/\/\/ \u8bbe\u7f6e\u8868\u4f9d\u8d56\u7f13\u5b58<\/div>\n
\n\t    \/\/\/ <\/summary><\/div>\n
\n\t    public static DataTable GetTableCache(string TableName, string CacheDataBase,string ConnStr)<\/div>\n
\n\t    {<\/div>\n
\n\t        System.Web.Caching.Cache objCache = HttpRuntime.Cache;<\/div>\n
\n\t        DataTable dt = new DataTable();<\/div>\n
\n\t        \/\/objCache.Insert(CacheKey, objObject);<\/div>\n
\n\t        if (objCache[TableName] == null)<\/div>\n
\n\t        {<\/div>\n
\n\t            try<\/div>\n
\n\t            {<\/div>\n
\n\t                \/\/get from database<\/div>\n
\n\t                DataSet ds = new DataSet();<\/div>\n
\n\t <\/div>\n
\n\t                SqlDependency.Start(ConnStr);\/\/\u542f\u52a8\u4e0e\u5173\u95ed\u653e\u5728Global\u7684Application_Start,End\u4e2d\u597d\u4e00\u4e9b<\/div>\n
\n\t <\/div>\n
\n\t                using (SqlConnection connection = new SqlConnection(ConnStr))<\/div>\n
\n\t                {<\/div>\n
\n\t                    \/\/\u53ea\u6709sql\u4e2d\u6307\u5b9a\u7684\u5b57\u6bb5\u53d1\u751f\u66f4\u6539\u65f6\u624d\u4f1a\u63d0\u9192\u3002
\n\t                    string sql = string.Format(" SELECT ID,ParentID,Name,OrderBy,OrganID FROM dbo.mdCity");<\/div>\n
\n\t <\/div>\n
\n\t                    using (SqlCommand command = new SqlCommand(sql, connection))<\/div>\n
\n\t                    {<\/div>\n
\n\t                        SqlCacheDependency dependency = new SqlCacheDependency(command);<\/div>\n
\n\t <\/div>\n
\n\t                        \/\/using (SqlDataAdapter adapter = new SqlDataAdapter()) \/\/\u67e5\u8be2\u6570\u636e<\/div>\n
\n\t                        \/\/{<\/div>\n
\n\t                        \/\/    adapter.SelectCommand = command;<\/div>\n
\n\t                        \/\/    adapter.Fill(ds);<\/div>\n
\n\t                        \/\/}<\/div>\n
\n\t <\/div>\n
\n\t                        \/\/HttpRuntime.Cache.Insert("EntityResourceCollection", ds, dependency);<\/div>\n
\n\t                        connection.Open();<\/div>\n
\n\t                        dt.Load(command.ExecuteReader(CommandBehavior.CloseConnection));<\/div>\n
\n\t                        objCache.Insert(TableName, dt, dependency);<\/div>\n
\n\t                    }<\/div>\n
\n\t <\/div>\n
\n\t                }<\/div>\n
\n\t <\/div>\n
\n\t <\/div>\n
\n\t            }<\/div>\n
\n\t            catch<\/div>\n
\n\t            {<\/div>\n
\n\t <\/div>\n
\n\t            }<\/div>\n
\n\t <\/div>\n
\n\t        }<\/div>\n
\n\t        else<\/div>\n
\n\t        {<\/div>\n
\n\t            dt = (DataTable)objCache[TableName];<\/div>\n
\n\t        }<\/div>\n
\n\t <\/div>\n
\n\t        return dt;<\/div>\n
\n\t    }<\/p>\n

\t5,\u6ce8\u610f \u53ea\u6709sql\u4e2d\u6307\u5b9a\u5b57\u6bb5\u53d1\u751f\u66f4\u6539\u65f6\u624d\u4f1a\u63d0\u9192\u3002<\/p>\n

\n\t\t\u5173\u4e8eSqlDependency\u7c7b\uff0c\u5b83\u7684\u9650\u5236\u5f88\u591a\uff0c\u7279\u522b\u8981\u6ce8\u610f\u7684\u5c31\u662fcommand\u7684sql\u8bed\u53e5\u95ee\u9898:<\/div>\n
\n\t\tselect id,name from dbo.test where id<>4 order by id desc <\/div>\n
\n\t\t\u4ed6\u53ea\u80fd\u652f\u6301\u4e0a\u9762\u8fd9\u6837\u7684\u7b80\u5355\u8bed\u53e5<\/div>\n
\n\t\t\u8868\u540d\u524d\u9700\u8981\u67b6\u6784\u540d\uff0c\u5982dbo<\/p>\n

\t\tsql\u6ce8\u610f<\/a><\/strong><\/p>\n

\n\t\t\t\u6ee1\u8db3\u4e0b\u5217\u8981\u6c42\u7684 SELECT \u8bed\u53e5\u652f\u6301\u67e5\u8be2\u901a\u77e5\uff1a<\/p>\n