I have a controller listed here which should be calling the Dapper method, however I cannot get it to execute. It's not throwing any exceptions and it is redirecting under the try catch however the procedure doesn't seem to be running as no new users are ever created.
The connection set up works with simple inline .Query methods however as soon as I attempt to do this with a stored procedure, it fails.
Model:
public class User
{
public int UserID { get; set; }
public string Username { get; set; }
public string Email { get; set; }
public string Password { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public Boolean ActiveB { get; set; }
}
View:
@model ScaleBase.Models.FullUser
@{
ViewData["Title"] = "Create";
}
<h2>Create</h2>
<h4>User</h4>
<hr />
<div class="row">
<div class="col-md-4">
<form asp-action="Create">
<div asp-validation-summary="ModelOnly" class="text-danger"></div>
<div class="form-group">
<label asp-for="OrganisationID" class="control-label"></label>
<input asp-for="OrganisationID" class="form-control" />
<span asp-validation-for="OrganisationID" class="text-danger"></span>
</div>
<div class="form-group">
<label asp-for="ClientID" class="control-label"></label>
<input asp-for="ClientID" class="form-control" />
<span asp-validation-for="ClientID" class="text-danger"></span>
</div>
<div class="form-group">
<label asp-for="TeamID" class="control-label"></label>
<input asp-for="TeamID" class="form-control" />
<span asp-validation-for="TeamID" class="text-danger"></span>
</div>
<div class="form-group">
<label asp-for="Email" class="control-label"></label>
<input asp-for="Email" class="form-control" />
<span asp-validation-for="Email" class="text-danger"></span>
</div>
<div class="form-group">
<label asp-for="Username" class="control-label"></label>
<input asp-for="Username" class="form-control" />
<span asp-validation-for="Username" class="text-danger"></span>
</div>
<div class="form-group">
<label asp-for="Password" class="control-label"></label>
<input asp-for="Password" type="password" class="form-control" />
<span asp-validation-for="Password" class="text-danger"></span>
</div>
<div class="form-group">
<label asp-for="FirstName" class="control-label"></label>
<input asp-for="FirstName" class="form-control" />
<span asp-validation-for="FirstName" class="text-danger"></span>
</div>
<div class="form-group">
<label asp-for="LastName" class="control-label"></label>
<input asp-for="LastName" class="form-control" />
<span asp-validation-for="LastName" class="text-danger"></span>
</div>
<div class="form-group">
<div class="checkbox">
<label>
<input asp-for="ActiveB" /> @Html.DisplayNameFor(model => model.ActiveB)
</label>
</div>
</div>
<div class="form-group">
<input type="submit" value="Create" class="btn btn-default" />
</div>
</form>
</div>
</div>
Controller:
public ActionResult Create(IFormCollection collection)
{
try
{
DynamicParameters parameters = new DynamicParameters();
parameters.Add("@Organisation", collection["OrganisationID"]);
parameters.Add("@ClientID1", collection["ClientID"]);
parameters.Add("@Team", collection["TeamID"]);
parameters.Add("@Email", collection["Email"]);
parameters.Add("@UserName", collection["UserName"]);
parameters.Add("@Password", collection["Password"]);
parameters.Add("@FirstName", collection["FirstName"]);
parameters.Add("@LastName", collection["LastName"]);
var affectedRows = _dapperRepo.CreateUser(parameters);
return RedirectToAction(nameof(Index));
}
catch (Exception)
{
throw;
}
}
Dapper repo:
public async Task<User> CreateUser(DynamicParameters parameters)
{
using (IDbConnection conn = Connection)
{
string sproc = "EXEC sproc_NewUser @Organisation, @Client1, @Team, @Email @UserName, @Password, @FirstName, @LastName";
conn.Open();
var result = await conn.QueryAsync(sproc, parameters, commandType: CommandType.StoredProcedure);
return result.FirstOrDefault();
}
}
Stored procedure:
BEGIN TRY
BEGIN TRANSACTION NewUser
DECLARE @salt UNIQUEIDENTIFIER = NEWID()
INSERT INTO [dbo].[User] (Username, Email, FirstName, LastName, Password, Salt, Active)
VALUES (@UserName, @Email, @FirstName, @LastName, HASHBYTES('SHA2_512', @Password+CAST(@salt AS NVARCHAR(36))), @salt, 1)
INSERT INTO [dbo].[UserOrganisations] (UserID, OrganisationID)
VALUES (IDENT_CURRENT('User'), @Organisation)
INSERT INTO [dbo].[UserClients] (UserID, ClientID)
VALUES (IDENT_CURRENT('User'), @Client1)
IF @Client2 IS NOT NULL
BEGIN
INSERT INTO [dbo].[UserClients] (UserID, ClientID)
VALUES (IDENT_CURRENT('User'), @Client2)
END
IF @Client3 IS NOT NULL
BEGIN
INSERT INTO [dbo].[UserClients] (UserID, ClientID)
VALUES (IDENT_CURRENT('User'), @Client3)
END
IF @Client4 IS NOT NULL
BEGIN
INSERT INTO [dbo].[UserClients] (UserID, ClientID)
VALUES (IDENT_CURRENT('User'), @Client4)
END
IF @Client5 IS NOT NULL
BEGIN
INSERT INTO [dbo].[UserClients] (UserID, ClientID)
VALUES (IDENT_CURRENT('User'), @Client5)
END
INSERT INTO [dbo].[UserTeams] (UserID, TeamID)
VALUES (IDENT_CURRENT('User'), @Team)
INSERT INTO [dbo].[UserPermission] (UserID, HolidayCount, HolidayUsed, TemplateID, ConfigState1, ConfigState2, ConfigState3, ConfigState4, ConfigState5)
VALUES (IDENT_CURRENT('User'), @Holiday, 0, 1, 255, null, null, null, null)
INSERT INTO [dbo].[UserTime] (UserID, Scale, StartTime, EndTime)
VALUES (IDENT_CURRENT('User'), 1, @StartTime, @EndTime)
COMMIT TRANSACTION NewUser
PRINT 'Success'
SELECT
[UserID], [Username], [Email], [Firstname], [Lastname], [Active]
FROM
[User]
WHERE
[UserID] = IDENT_CURRENT('User')
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
BEGIN
ROLLBACK TRANSACTION NewUser
PRINT 'Failed'
END
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage
RETURN 'Error'
END CATCH
Delclare ALL paramaters which are listed in your stored procedure. @Client2, @ClientID3 ect will cause an error of undeclared paramaters.
Changing the implementation of this to .ExecuteAsync and executing in the controller seem to resolve this issue.
public Async Task<IActionResult> Create(IFormCollection collection)
{
try
{
DynamicParameters parameters = new DynamicParameters();
parameters.Add("@Organisation", collection["OrganisationID"]);
parameters.Add("@ClientID1", collection["ClientID"]);
parameters.Add("@Team", collection["TeamID"]);
parameters.Add("@Email", collection["Email"]);
parameters.Add("@UserName", collection["UserName"]);
parameters.Add("@Password", collection["Password"]);
parameters.Add("@FirstName", collection["FirstName"]);
parameters.Add("@LastName", collection["LastName"]);
var affectedRows = _dapperRepo.CreateUser(parameters);
using (IDbConnection conn = Connection)
{
string sproc = "EXEC sproc_NewUser @Organisation, @Client1, @Team, @Email @UserName, @Password, @FirstName, @LastName";
conn.Open();
var result = await conn.QueryAsync(sproc, parameters, commandType: CommandType.StoredProcedure);
var result2 = result.FirstOrDefault();
}
return RedirectToAction(nameof(Index));
}
catch (Exception)
{
throw;
}
}