✅ T-SQL Statement in Linq
Hey, I'm trying to reduce the amount of stored procedures and write that stuff in C# / Linq.
Right now I'm trying to rewrite this:
if @shouldValidate = 1
begin
while @RowCount >= @ViewId
begin
begin try
select @ViewName = ViewName from #ViewData where #ViewData.ViewId = @ViewId
set @SqlQuery = N'set noexec on; SELECT TOP 1 * FROM [' + @ViewName + ']; set noexec off;';
exec sp_executesql @SqlQuery;
update #ViewData
SET ViewStatus = 1,
ViewMessage = 'No Error'
where #ViewData.ViewId = @ViewId;
end try
begin catch
update #ViewData
SET ViewStatus = 2,
ViewMessage = ERROR_MESSAGE()
where #ViewData.ViewId = @ViewId;
end catch
set @ViewId = @ViewId + 1
end;
end;
background: from time to time an external software partner changes his release and some views from my colleagues run on an error then, because some tables got changed and columns are missing.
only twist there is the "set noexec on" to avoid resultsets and being able to loop that stuff without having x results for x views.
an example result is added as image.
Can anyone help me to write that in Linq?
I'm new to that and tried this so far, but doesnt work:
public async Task<ViewEntity[]> ValidateViews(string searchText)
{
viewData = await GetViews(searchText);
foreach (ViewEntity data in viewData)
{
try
{
var result = await _database.ViewEntities.FromSql($"set noexec on; SELECT TOP 1 * FROM [{data.ViewName}]; set noexec off;").ToListAsync();
data.ViewStatus = 1;
data.ViewMessage = "No Error";
}
catch (Exception ex)
{
data.ViewStatus = 2;
data.ViewMessage = ex.Message;
}
}
return viewData.ToArray();
}
having a problem with viewEntities there, because it doesnt fit to the result..and probably more, but you can tell me that xD
Thanks for any advice in the right direction! :)
Best regards
Exi
Right now I'm trying to rewrite this:
if @shouldValidate = 1
begin
while @RowCount >= @ViewId
begin
begin try
select @ViewName = ViewName from #ViewData where #ViewData.ViewId = @ViewId
set @SqlQuery = N'set noexec on; SELECT TOP 1 * FROM [' + @ViewName + ']; set noexec off;';
exec sp_executesql @SqlQuery;
update #ViewData
SET ViewStatus = 1,
ViewMessage = 'No Error'
where #ViewData.ViewId = @ViewId;
end try
begin catch
update #ViewData
SET ViewStatus = 2,
ViewMessage = ERROR_MESSAGE()
where #ViewData.ViewId = @ViewId;
end catch
set @ViewId = @ViewId + 1
end;
end;
background: from time to time an external software partner changes his release and some views from my colleagues run on an error then, because some tables got changed and columns are missing.
only twist there is the "set noexec on" to avoid resultsets and being able to loop that stuff without having x results for x views.
an example result is added as image.
Can anyone help me to write that in Linq?
I'm new to that and tried this so far, but doesnt work:
public async Task<ViewEntity[]> ValidateViews(string searchText)
{
viewData = await GetViews(searchText);
foreach (ViewEntity data in viewData)
{
try
{
var result = await _database.ViewEntities.FromSql($"set noexec on; SELECT TOP 1 * FROM [{data.ViewName}]; set noexec off;").ToListAsync();
data.ViewStatus = 1;
data.ViewMessage = "No Error";
}
catch (Exception ex)
{
data.ViewStatus = 2;
data.ViewMessage = ex.Message;
}
}
return viewData.ToArray();
}
having a problem with viewEntities there, because it doesnt fit to the result..and probably more, but you can tell me that xD
Thanks for any advice in the right direction! :)
Best regards
Exi
