User-1256377279 posted
Hi Guys,
I am using DbContext ExecuteSqlCommand to execute below stored procedure but i always get -1 even if there is 1 record or no records.
SP
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SprFindPlaceByPlaceName]
(
@PlaceName nvarchar (500),
@PlaceNameQuoted nvarchar(502),
@CivilParish nvarchar (500),
@CivilParishQuoted nvarchar(502),
@localAdmin nvarchar (500),
@localAdminQuoted nvarchar(502),
@WiderAdmin nvarchar (500),
@WiderAdminQuoted nvarchar(502),
@Country nvarchar (60),
@CountryQuoted nvarchar(62),
@DateRange varchar (50),
@DateRangeQuoted Varchar(52)
)
AS
DECLARE @sql nvarchar(2000)
DECLARE @where nvarchar(2000)
DECLARE @fields nvarchar(2000)
SET @sql = ''
SET @where = ''
SET @fields = ''
SET @where += 'Where TcRecords.RecordId = SA.RecordId '
SET @where += ' AND SA.ItemTypeId = 48 and PlaceName = N''' + @PlaceName + ''''
SET @where += ' AND CivilParish = N''' + @CivilParish + ''''
SET @fields += ', CivilParish'
SET @where += ' AND LocalAdminUnit = N''' + @localAdmin + ''''
SET @fields += ', LocalAdminUnit as LocalAdmin'
SET @where += ' AND WiderAdminUnit = N''' + @WiderAdmin + ''''
SET @fields += ', WiderAdminUnit as WiderAdmin'
SET @where += ' AND Country = N''' + @Country + ''''
SET @fields += ', Country'
SET @where += ' AND DateRange = N''' + @DateRange + ''''
SET @fields += ', DateRange'
SET @sql = 'SELECT MainName as PlaceName'
SET @sql += @fields
SET @sql += ', ItemXob, TcRecords.itemtypeid, TcRecords.RecordId,
TcRecords.LastModifiedOn, TcRecords.LastModifiedBy
FROM TcRecords, TcShreddedAuthority as SA '
SET @sql += @where
PRINT @sql
EXEC sp_executesql @stmt = @sql
RETURN
C#
try
{
var affectedRows = db.Database.ExecuteSqlCommand("exec SprFindPlaceByPlaceName @PlaceName, @PlaceNameQuoted, @CivilParish, @CivilParishQuoted, @localAdmin, @LocalAdminQuoted, @WiderAdmin, @WiderAdminQuoted, @Country, @CountryQuoted, @DateRange, @DateRangeQuoted",
new SqlParameter("@PlaceName", p_placeName),
new SqlParameter("@PlaceNameQuoted", placeNameQuoted),
new SqlParameter("@CivilParish", p_civilParish),
new SqlParameter("@CivilParishQuoted", civilParishQuoted),
new SqlParameter("@localAdmin", p_localAdmin),
new SqlParameter("@LocalAdminQuoted", localAdminQuoted),
new SqlParameter("@WiderAdmin", p_widerAdmin),
new SqlParameter("@WiderAdminQuoted", widerAdminQuoted),
new SqlParameter("@Country", p_country),
new SqlParameter("@CountryQuoted", countryQuoted),
new SqlParameter("@DateRange", p_dateRange),
new SqlParameter("@DateRangeQuoted", dateRangeQuoted));
if (affectedRows != 0)
{
result.Result = false;
result.FailureDescription.Add("Place name is not unique");
}
}
catch (Exception ex)
{
result.Result = false;
result.FailureDescription.Add(ex.Message);
}
Many Thanks
Shabbir
