CODEWITHSUNDEEP
c#sqlasp.netasp.net-authorization
Rilcon42
Rilcon42

Reputation: 9763

Update Asp.Net Claims via SQL statement?

I am trying to add a claim to an existing User, but the query I wrote (and am running in SSQL Management Studio) below doesn't work. Is the query wrong, or is this just not possible?

update [test_MIM].[dbo].[AspNetUserClaims] 
set ClaimType = 'EmployeeNumber',
ClaimValue = '1',
--Id = f.Id,
UserId = f.UserName
from (select Id,UserName FROM [test_MIM].[dbo].[AspNetUsers] where UserName='[email protected]') as f

I commented out the Id column because when I included it the query failed (possibly because Id was auto-generated)

Upvotes: 0

Views: 230

Answers (2)

Aousaf Rashid
Aousaf Rashid

Reputation: 5738

What you can do is use join in your update statement,what join does is it joins two or multiple tables together in one statement :

UPDATE NameHere SET ClaimType = 'EmployeeNumber',ClaimValue = '1', UserId f.UserName FROM [test_MIM].[dbo].[AspNetUserClaims] NameHere JOIN [test_MIM].[dbo].[AspNetUsers] f  on NameHere.id = f.id where f.UserName = '[email protected]'

But it is not ideal,rather pass parameters for each column , then not only that you can save your app from SQL-Injection but also will be able to set column's data-type which might get rid of other problems. A sample :

  UPDATE NameHere SET ClaimType = @ClaimType,ClaimValue = @claimValue, UserId = @Uname FROM [test_MIM].[dbo].[AspNetUserClaims] NameHere JOIN [test_MIM].[dbo].[AspNetUsers] f  on NameHere.id = @NewID where f.UserName = @NewUserName

If you are using SqlCommand,then you can use the parameters as follows :

 cmd.Parameters.Add("@ClaimType", SqlDbType.VarChar).Value = "claimingTyoeString" ////Assuming 'cmd' is the SqlCommand

 .......

 cmd.ExecuteNonQuery();

Upvotes: 0

Rahul
Rahul

Reputation: 77896

Instead use a update-join construct like

update a 
set ClaimType = 'EmployeeNumber',
ClaimValue = '1',
UserId = f.UserName
from [test_MIM].[dbo].[AspNetUserClaims] a
join [test_MIM].[dbo].[AspNetUsers] f 
on a.id = f.id
where f.UserName = '[email protected]'

Upvotes: 1

Related Questions