GooglePrettify

2017年11月28日 星期二

How get yesterday and tomorrow datetime in c#

You can find this info right in the API reference.
var today = DateTime.Now;
var tomorrow = today.AddDays(1);
var yesterday = today.AddDays(-1);

from : https://stackoverflow.com/questions/8203900/how-get-yesterday-and-tomorrow-datetime-in-c-sharp

SQL Server Pagination

For 2005 / 2008 / 2008 R2
;WITH cte AS
(
    SELECT  Journals.JournalId, 
            Journals.Year, 
            Journals.Title, 
            ArticleCategories.ItemText,
            ROW_NUMBER() OVER 
                     (ORDER BY Journals.JournalId,ArticleCategories.ItemText) AS RN
    FROM    Journals LEFT OUTER JOIN
            ArticleCategories 
             ON Journals.ArticleCategoryId = ArticleCategories.ArticleCategoryId
)
    SELECT  JournalId, 
            Year, 
            Title, 
            ItemText
FROM cte
WHERE RN BETWEEN 11 AND 20
For 2012 this is simpler
SELECT Journals.JournalId,
       Journals.Year,
       Journals.Title,
       ArticleCategories.ItemText
FROM   Journals
       LEFT OUTER JOIN ArticleCategories
         ON Journals.ArticleCategoryId = ArticleCategories.ArticleCategoryId
ORDER  BY Journals.JournalId,
          ArticleCategories.ItemText 
OFFSET  10 ROWS 
FETCH NEXT 10 ROWS ONLY 

from : https://stackoverflow.com/questions/5620758/t-sql-skip-take-stored-procedure

Comparing performance for different SQL Server paging methods

For this test we will run each paging method through a SQL Server 2012 database and compare their explain plans as well as some basic performance statistics using SQL Profiler. Here is the complete test script. Note: To update the query to fetch the first 50 records simply update the start/end variables.
--SQL 2000 Paging Method
DECLARE @Start INT
DECLARE @End INT
SELECT @Start = 14000,@End = 14050

            
CREATE TABLE #employees (RowNumber INT IDENTITY(1,1),
                LastName VARCHAR(100),FirstName VARCHAR(100),
                EmailAddress VARCHAR(100))

  
INSERT INTO #employees (LastName, FirstName, EmailAddress)
SELECT LastName, FirstName, EmailAddress 
  FROM Employee 
 ORDER BY LastName, FirstName, EmailAddress
SELECT LastName, FirstName, EmailAddress 
  FROM #employees
 WHERE RowNumber > @Start AND RowNumber <= @End

  
DROP TABLE #employees

  
GO

  
--SQL 2005/2008 Paging Method Using Derived Table
DECLARE @Start INT
DECLARE @End INT
SELECT @Start = 14000,@End = 14050

  
SELECT LastName, FirstName, EmailAddress
FROM (SELECT LastName, FirstName, EmailAddress,
      ROW_NUMBER() OVER (ORDER BY LastName, FirstName, EmailAddress) AS RowNumber
      FROM Employee) EmployeePage
WHERE RowNumber > @Start AND RowNumber <= @End
ORDER BY LastName, FirstName, EmailAddress
GO

  
--SQL 2005/2008 Paging Method Using CTE
DECLARE @Start INT
DECLARE @End INT
SELECT @Start = 14000,@End = 14050;

  
WITH EmployeePage AS
(SELECT LastName, FirstName, EmailAddress,
 ROW_NUMBER() OVER (ORDER BY LastName, FirstName, EmailAddress) AS RowNumber
 FROM Employee)
SELECT LastName, FirstName, EmailAddress
FROM EmployeePage
WHERE RowNumber > @Start AND RowNumber <= @End
ORDER BY LastName, FirstName, EmailAddress
GO

  
--SQL SERVER 2012
SELECT LastName, FirstName, EmailAddress
FROM Employee
ORDER BY LastName, FirstName, EmailAddress
OFFSET 14000 ROWS
FETCH NEXT 50 ROWS ONLY;

Explain Plans

First let's take a look at the explain plans for each of these queries. The following explain plans appear in the same order as the queries in the test script above.

SQL 2000 Paging Method

SQL2000 - Paging Method - 1

SQL2000 - Paging Method - 2

SQL2000 - Paging Method - 3


SQL 2005/2008 Paging Method Using Derived Table

SQL2005-2008 - Derived table - 1

SQL2005-2008 - Derived table - 2


SQL 2005/2008 Paging Method Using CTE

SQL2005-2008 - CTE - 1

SQL2005-2008 - CTE - 2


SQL SERVER 2012



After analyzing these explain plans it's pretty safe to assume that when we look at the profiler results, the SQL Server 2000 query is probably going to perform the worst given that it basically has to run two queries and the second query is a complete scan of a temporary table. Interesting thing I noted after reviewing these explain plans was that the SQL Server 2005/2008 version generates the exact same query plan for both the CTE and Derived table version. Comparing these two plans to the SQL Server 2012 version we might expect that the 2012 version will perform slightly better given that it only has to perform one sort. Let's take a look at the performance numbers to confirm.

SQL Profiler Results

To analyze the performance we'll run the above queries for the first page returned as well as for a page of data somewhere in the middle of the result set. I ran the queries above 10 times and the numbers in the chart below represent the averages of the statistics collected with SQL Profiler.
Query First Page
VersionCPU (ms)ReadsWritesDuration
200032824427444327
2005/2008 Derived12592420178
2005/2008 CTE12792420173
2012468733044
Query Middle Page
VersionCPU (ms)ReadsWritesDuration
200031224658444313
2005/2008 Derived15794720173
2005/2008 CTE15694720175
201212589630135
Looking at these SQL Profiler results we can confirm what we had suspected after viewing the explain plans. The SQL Server 2000 version performs much poorer than the other methods and the new OFFSET and FETCH clause performs only slightly better that it's 2005/2008 conterpart using ROW_NUMBER(). The only exception to this is that when querying the first page of the result set, which all applications usually do, it performs quite a bit better than all the other methods. Almost three times faster than using the ROW_NUMBER() function.

Summary

As we can see from all the results, the new SQL Server 2012 feature is the best option for implementing a server-side page of a query result. The other added benefit is that it is also by far the most straightforward and easy to understand of all the methods.

from : https://www.mssqltips.com/sqlservertip/2696/comparing-performance-for-different-sql-server-paging-methods/

Sql Server bulk update in stored procedure

CREATE PROCEDURE [dbo].[Update_Customers]
      @tblCustomers CustomerType READONLY
AS
BEGIN
      SET NOCOUNT ON;
      --UPDATE EXISTING RECORDS
      UPDATE Customers
      SET Name = c2.Name
      ,Country = c2.Country
      FROM Customers c1
      INNER JOIN @tblCustomers c2
      ON c1.CustomerId = c2.Id
 
      --INSERT NON-EXISTING RECORDS
      INSERT INTO Customers
      SELECT Id, Name, Country
      FROM @tblCustomers
      WHERE Id NOT IN(SELECT CustomerId FROM Customers)
END

from : https://www.aspsnippets.com/Articles/SqlBulkCopy--Bulk-Insert-records-and-Update-existing-rows-if-record-exists-using-C-and-VBNet.aspx

2017年11月23日 星期四

Using Dictionary.TryGet

HashSet<long> portSet;
if (!ipMetaHashId2PortSetDic.TryGetValue(metaHashId4IP, out portSet))
{
portSet = new HashSet<long>();
ipMetaHashId2PortSetDic[metaHashId4IP] = portSet;
}
portSet.Add(port);

2017年11月20日 星期一

Visual Studio 2013 or 2015 EditorPackage did not load correctly constantly

This has become a popular question so I thought I add an explicit answer how to (at least) temporarily fix the problem as already stated in the question:
I stop Visual Studio and delete the following folder (completely, not just contents)
%LocalAppData%\Microsoft\VisualStudio\12.0\ComponentModelCache
If you are using VS2015, then the path should be:
%LocalAppData%\Microsoft\VisualStudio\14.0\ComponentModelCache
I don't keep a backup of the folder. It is created automatically again when starting Visual Studio.
I have never found a solution how to permanently fix the problem in that working environment. In my new developing environment (new projects and new workplace) the problem never happens.

from : https://stackoverflow.com/questions/29694329/visual-studio-2013-or-2015-editorpackage-did-not-load-correctly-constantly