C#學習網誌: 11月 2017

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

SQL 2005/2008 Paging Method Using Derived Table

SQL 2005/2008 Paging Method Using CTE

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
Version	CPU (ms)	Reads	Writes	Duration
2000	328	24427	444	327
2005/2008 Derived	125	9242	0	178
2005/2008 CTE	127	9242	0	173
2012	46	8733	0	44
Query Middle Page
Version	CPU (ms)	Reads	Writes	Duration
2000	312	24658	444	313
2005/2008 Derived	157	9472	0	173
2005/2008 CTE	156	9472	0	175
2012	125	8963	0	135

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

2017年11月28日 星期二