Posts

Showing posts from June, 2019

Insert Identity Column in a table

insert an identity column in that table by using the following code:

ALTERTABLE dbo.tablename ADD AUTOID INTIDENTITY(1,1) 

MSSQL Remove duplicate Rows

Select the duplicate key values into a holding table. For example:
SELECT col1, col2, col3=count(*) INTO holdkey FROM t1 GROUP BY col1, col2 HAVING count(*) > 1 Select the duplicate rows into a holding table, eliminating duplicates in the process. For example:       SELECT DISTINCT t1.*         INTO holddups FROM t1, holdkey  WHERE t1.col1 = holdkey.col1         AND t1.col2 = holdkey.col2At this point, the holddups table should have unique PKs, however, this will not be the case if t1 had        duplicate PKs, yet unique rows (as in the SSN example above). Verify that each key in holddups is unique, and that you do not have duplicate keys, yet unique rows. If so, you must stop here and reconcile which of the rows you wish to keep for a given duplicate key value. For example, the query: SELECT col1, col2, count(*) FROM holddups GROUP BY col1, col2 should return a count of 1 for each row. If yes, proceed to step 5 below. If no, yo…

Read TFS Project name from TFS Project collection using c#

Image
Using Team foundation api.
 References:

C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\ReferenceAssemblies\v2.0\Microsoft.TeamFoundation.dll
C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\ReferenceAssemblies\v2.0\Microsoft.TeamFoundation.Client.dll
C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\ReferenceAssemblies\v2.0\Microsoft.TeamFoundation.Common.dll
C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\ReferenceAssemblies\v2.0\Microsoft.TeamFoundation.WorkItemTracking.Client.dll


Code:
using Microsoft.TeamFoundation.Client;
using Microsoft.TeamFoundation.Server;
using System;
using System.Linq;

classProgram
{privatestatic ICommonStructureService _commonStructureService;
staticvoid Main(string[] args)
{
ReadProject("https://tfssite.com/tfs/UK_ProjectCollection");
ReadProject("https://tfssite.com/tfs/IN_ProjectCollection");
Console.ReadKey();
}publicstaticvoid Rea…

GIT - SSL certificate problem: unable to get local issuer certificate

Image
Error encountered while cloning the remote repository: Git failed with a fatal error. unable to access 'https://tfssite.company.com/tfs/uk_projectcollection/SalesPortal/_git/SalesPortalWeb/': SSL certificate problem: unable to get local issuer certificate
Cloning into 'C:\Users\sojha\Source\Repos\SalesPortalWeb'... Error encountered while cloning the remote repository: Git failed with a fatal error. unable to access 'https://tfssite.company.com/tfs/uk_projectcollection/SalesPortal/_git/SalesPortalWeb/': SSL certificate problem: unable to get local issuer certificate



Solution: Open Visual Studio Command prompt
GIT provides an option to choose from OpenSSL and Secure Channel. Choosing secure channel in git global solves this issue.
Run the script:
git config --global http.sslBackend schannel

Entity Framework Core with Existing Database

Image
Entity Framework Core with Existing Database
Entity Framework Core only supports code first database aproach.
To use existing database, we'll need to reverse Engineer Model from existing Database.
Open Visual studio.Menu > tools >Nuget Package Manager > Note: All the tables in the database must have Primary key defined. else you'll get error like:
Unable to generate entity type for table 'dbo.xOrdersMBS'.
Unable to identify the primary key for table 'dbo.xActivePricesEPB'.
Unable to generate entity type for table 'dbo.xActivePricesEPB'.
Unable to identify the primary key for table 'dbo.xLeadTimePBOM'.
Unable to generate entity type for table 'dbo.xLeadTimePBOM'.


 In the console, run the below power shell commands:

Using Windows Authentication:
Scaffold-DbContext "Server=DatabaseServerAddress;Database=DatabaseName;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -f

Using SQL Account:
Scaffold…