Insert Identity Column in a table

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

ALTER TABLE dbo.tablename ADD AUTOID INT IDENTITY(1,1) 

MSSQL Remove duplicate Rows

1. 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

2. 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.col2

3. At 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, you have duplicate keys, yet unique rows, and need to decide which 
   rows to save. This will usually entail either discarding a row, or creating
   a new unique key value for this row. Take one of these two steps for each 
   such duplicate PK in the holddups table.
   

4. Delete the duplicate rows from the original table. For example:
        DELETE t1
        FROM t1, holdkey
        WHERE t1.col1 = holdkey.col1
        AND t1.col2 = holdkey.col2  

5. Put the unique rows back in the original table. For example:
        INSERT t1 SELECT * FROM holddups

Another Way:

delete from table1

WHERE id IN (

SELECT t1.id

FROM table1 t1, table1 t2

WHERE t1.name = t2.name AND t1.id > t2.id

) 

Read TFS Project name from TFS Project collection using c#

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;

class Program
{ private static ICommonStructureService _commonStructureService;
static void Main(string[] args)
{
ReadProject("https://tfssite.com/tfs/UK_ProjectCollection");
ReadProject("https://tfssite.com/tfs/IN_ProjectCollection");
Console.ReadKey();
} public static void ReadProject(String URL)
{
writeFile("Project Collection: " + URL);
TfsTeamProjectCollection tpc = new TfsTeamProjectCollection(new Uri(URL));
var collection = TfsTeamProjectCollectionFactory.GetTeamProjectCollection(tpc.Uri);
_commonStructureService = collection.GetService<ICommonStructureService>(); var projects = _commonStructureService.ListAllProjects().OrderBy(a => a.Name);
foreach (var project in projects)
{

writeFile(project.Name);
} writeFile("------------------------------------------------------------------------------");
} public static void writeFile(String message) {
using (System.IO.StreamWriter file =
new System.IO.StreamWriter(@"C:\temp\tfs.txt", true))
{
file.WriteLine(message);
}
}
}

Output is the list of project names in tfs.txt file. 

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

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

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 >
• open PM Console

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-DbContext "data source=DatabaseServerAddress;initial catalog=DatabaseName;uid=UserName;pwd=Password;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -f

Note: -f force create the files if the file exist in the folder Models.

Add nuget packages to Artifacts in Azure devops

download latest nuget.exe from the nuget site. (google it)

copy the nuget.exe in d: (any location is fine)

Use visual studio -> Package manager console. 

run the below command

D:\nuget.exe push -Source "IGTelerikFeed" -ApiKey az D:\Telerik.UI.for.AspNet.Core.2020.2.617.nupkg

[Note: It will ask Credential to connect to the devops when you press enter. ]

EntityFramework Insert error due to PrimaryKey

MyProject.Controllers.CurrencyCodesController Unable to update the EntitySet 'CurrencyCodes' because it has a DefiningQuery and no <InsertFunction> element exists in the <ModificationFunctionMapping> element to support the current operation. System.Data.Entity.Infrastructure.DbUpdateException: Unable to update the EntitySet 'CurrencyCodes' because it has a DefiningQuery and no <InsertFunction> element exists in the <ModificationFunctionMapping> element to support the current operation. ---> System.Data.Entity.Core.UpdateException: Unable to update the EntitySet 'CurrencyCodes' because it has a DefiningQuery and no <InsertFunction> element exists in the <ModificationFunctionMapping> element to support the current operation.

​ Scenario:  The error occured while trying to insert the currencycodes entity to the database.  [HttpPost] [ValidateAntiForgeryToken] public ActionResult Create([Bind(Include = "cucCurrencyCode,cucDescription,cucMultiplier,cucRate,cucInUse")] CurrencyCode currencyCode) { if (ModelState.IsValid) { try { db.CurrencyCodes.Add(currencyCode); db.SaveChanges(); } catch (Exception ex) { log.Error(ex.Message, ex); } return RedirectToAction("Index"); } return View(currencyCode); } Solution:  1) Check Primary key in .edmx file.  In my case cucInUse was set as Entity key. I removed it. 2) Now Right click the .edmx file and click "Open with...". Select XML (text) Editor 3)  Find the element <EntitySet name = "CurrencyCodes"...> 4) Change store:Schema="dbo" => Schema="dbo" 5) delete element <DefiningQuery> 6) Clean the project.  7) close Visual studio. 8) Open visual studio. Open the project.  9) Clean and build the project.  10) Run the application.

Fix for SSIS not picking correct configuration file

When there are more then one configuration file for a single package, the SSIS jobs sometimes do not automatically pick the correct configuration depending on how the settings has been made. 

In the SSIS package (using Visual Studio) Make sure you've done following correctly:

1) In the package property, Set Delay Validation =  false
2) Uncheck , Enable Package Configuration. 


steps for 2):

a) open the ssis package in visual studio.

b) In the Control flow tab, right click on the blank space of the canvas.

c) go to Package Configurations...

d) uncheck Enable Package Configuartion and press Close.

e) Save the ssis package and copy that to the deployed folder.

How to remove Workspace in TFS 2010-2018

This solution Works for TFS 2010+.

Open Visual studio Command prompt and run the following script.


tf workspace /delete /server:[Site:Port/path] WORKSPACENAME


for example:
tf workspace /delete /server:http://tfs.site.com:8080/tfs sanjeebPC

Use log4net for all logging in c# application

Exception logging is hugely important within an application, not just to ensure we know when an error has occured but also to help use trace back the thread to help investigate where the error originated.

Our strategy is to use log4net for all our logging needs.

This page helps cover the follow areas

1. Setting up logging across the entire applications

2. Including 2 appenders so we can be notified when they occur and ensure a tracable debug log file is available.

Setting up your Applications for Logging

Reference log4net in your applications

in Global.asax under the start_application method include the following

    void Application_Start(object sender, EventArgs e)
    {
        // link up log4net configuration
        log4net.Config.XmlConfigurator.Configure();
    }

On the page where you wish to log information declare a new static variable in the class header

log4net.ILog log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

Whenever you wish to now log an event in this class run one of the follow commands, ensure you include the exception when needed

log.Error("An unhandled exception occured", ex);

To ensure you catch all unhandled exceptions include the following in your Global.asax file. Remember to declare the log variable in the class too.

    void Application_Error(object sender, EventArgs e) 
    {
        // Code that runs when an unhandled error occurs
        // get last exception
        Exception ex = Server.GetLastError().GetBaseException();
        // log it
        log.Error("An unhandled exception occured", ex);
    }

Web.Config Settings

  <configSections>
    <section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler, log4net"/>
  </configSections>

<log4net>
    <!-- The DebugFileAppender writes all messages to a log file-->
    <appender name="DebugFileAppender" type="log4net.Appender.RollingFileAppender">
      <file value="L:\[APPNAME]\Log.txt" /> <!-- These should be located on the "L" drive (if available) and stored under a folder named for the specific application -->      <datePattern value="'ApplicationLog.'yyyy-MM-dd'.txt'" />
      <staticLogFileName value="false" />
      <rollingStyle value="Composite" />
      <maxSizeRollBackups value="10" />
      <maximumFileSize value="5MB" />
      <appendToFile value="true" />
      <layout type="log4net.Layout.PatternLayout">
        <conversionPattern value="%d [%t] %-5p %c %m%n" />
      </layout>
    </appender>    <!-- The EmailAppender sends an email when something matches the filters-->
    <appender name="EmailAppender" type="log4net.Appender.SmtpAppender">
      <evaluator type="log4net.Core.LevelEvaluator">
        <threshold value="DEBUG"/>
      </evaluator>
      <!--  The filters are processed in order:
            1) match the Inserted New User message
            2) match any WARN or higher messages
            3) reject everything else -->
      <filter type="log4net.Filter.StringMatchFilter">
        <stringToMatch value="Inserted a new user" />
        <acceptOnMatch value="true" />
      </filter>
      <filter type="log4net.Filter.LevelRangeFilter">
        <levelMin value="WARN" />
        <acceptOnMatch value="true" />
      </filter>
      <filter type="log4net.Filter.DenyAllFilter" />
      <!--  The SmtpAppender authenticates against the mail server, the buffersize of 10 provides 10 lines
            of context when an error happens. -->
      <subject value="[SEVERITY] [ENV] [APPNAME] - [EXCP_MSG]" />
      <to value="itteam@site.com" />
      <from value="noreply@site.com" />
      <smtpHost value="smtpmail.lu.sii.com" />
      <bufferSize value="10" />
      <lossy value="true" />
      <layout type="log4net.Layout.PatternLayout">
        <param name="ConversionPattern" value="%d [%t] %-5p %c %m%n" />
      </layout>
    </appender>
    <appender name="EventLogAppender" type="log4net.Appender.EventLogAppender" >
      <applicationName value="MyApp" />


     <layout type="log4net.Layout.PatternLayout">


        <conversionPattern value="%date [%thread] %-5level %logger [%property{NDC}] - %message%newline" />


      </layout>


    </appender>

    <root>
      <!-- add other appenders here and the log messages will be sent to every listed appender -->
      <appender-ref ref="DebugFileAppender" />
      <appender-ref ref="EmailAppender" />
    </root>
  </log4net>