Sqlserver notes

October 16, 2015

Before deleting data from production server follow these steps. Its a good practice

select * from WriteOff_ReworkUsers
where Ref_ID in (189538,201177,201185,203533,203534,203563,203567)

begin tran
delete from WriteOff_ReworkUsers
where Ref_ID in (189538,201177,201185,203533,203534,203563,203567)
rollback tran

begin tran
delete from WriteOff_ReworkUsers
where Ref_ID in (189538,201177,201185,203533,203534,203563,203567)
commit tran


Entity framework stored procedure always returning int

October 5, 2015

http://www.devtoolshed.com/using-stored-procedures-entity-framework-scalar-return-values

http://stackoverflow.com/questions/18245567/stored-procedure-returns-int-instead-of-result-set

I have a stored procedure that contains dynamic select. Something like this:

ALTER PROCEDURE [dbo].[usp_GetTestRecords]
–@p1 int = 0,
–@p2 int = 0
@groupId nvarchar(10) = 0
AS
BEGIN
SET NOCOUNT ON;

DECLARE @query NVARCHAR(max)

SET @query = ‘SELECT * FROM CUSTOMERS WHERE Id = ‘ + @groupId
/* This actually contains a dynamic pivot select statement */

EXECUTE(@query);
END

Answer :

SET FMTONLY OFF

or


Sqlserver notes: Minus operator in Oracle – Sqlserver Except or Not Exists

September 30, 2015

http://www.sqlservercurry.com/2011/01/minus-keyword-in-sql-server.html

MINUS operator (in oracle) is used to subtract the rows which are available in the second result, from the first result set. SQL Server does not have a built-in MINUS keyword, but in SQL Server, it’s equivalent is the EXCEPT operator or using NOT EXISTS

Here’s an example. Consider the following tables

DECLARE @table1 table(a int, b int)
DECLARE @table2 table(a int, b int)

TEST DATA

INSERT INTO @table1
SELECT 1 as a, 2 as b
UNION ALL
SELECT 1 as a, 2 as b
UNION ALL
SELECT 11 as a, 12 as b
UNION ALL
SELECT 13 as a, 12 as b

INSERT INTO @table2
SELECT 1 as a, 21 as b
UNION ALL
SELECT 1 as a, 12 as b
UNION ALL
SELECT 1 as a, 112 as b
UNION ALL
SELECT 13 as a, 12 as b

Using EXCEPT

SELECT * FROM @table1
EXCEPT
SELECT * FROM @table2
Using NOT EXISTS

SELECT DISTINCT t1.* FROM @table1 as t1
WHERE NOT EXISTS
(SELECT * from @table2 as t2
WHERE t1.a=t2.a and t1.b=t2.b)

NOT EXISTS also has the same functionality of EXCEPT operator i.e. retrieving the rows from the first table, which are not available in the second result set


Dotnet notes : LINQ

July 7, 2015

Use of dynamic in c#

LINQ : FirstOrDefault


Enabling remote debugger in visual studio

June 25, 2015

In production environment
Suppose you dont have visual studio editor. You have only dlls in the production machine. There is no code.
Along with framework you will get Remote Debugger tools in visual studio menu.

In the production machine start remote debugger tool. You will get machineno along with port no.

In development machine attach to process – Qualifier -> Enter the machineno along with portno.
in Attach to : select Automatic :Managed(v4.5,v4.0)code.
And attach to the w3p process
remote debugger


Entity Framework notes

May 24, 2015

How do I delete multiple rows in Entity Framework (without foreach)

db.People.RemoveRange(db.People.Where(x => State == “CA”));

How to delete single row in Entity Framework

use remove() method


Entity Framework notes : Validation failed for one or more entities. See ‘EntityValidationErrors’ property for more details

May 3, 2015

Validation failed for one or more entities. See ‘EntityValidationErrors’ property for more details

try
{
// Your code…
// Could also be before try if you know the exception occurs in SaveChanges

context.SaveChanges();
}
catch (DbEntityValidationException e)
{
foreach (var eve in e.EntityValidationErrors)
{
Console.WriteLine(“Entity of type \”{0}\” in state \”{1}\” has the following validation errors:”,
eve.Entry.Entity.GetType().Name, eve.Entry.State);
foreach (var ve in eve.ValidationErrors)
{
Console.WriteLine(“- Property: \”{0}\”, Error: \”{1}\””,
ve.PropertyName, ve.ErrorMessage);
}
}
throw;
}