CODEWITHSUNDEEP
.net-coredapper
Mr.Smithyyy
Mr.Smithyyy

Reputation: 1329

.net core insert using dapper with parameters

I'm new to using Dapper and I'm a bit lost on following the instructions for an insert statement.

I have the following model:

User

public class User
{
    public int UserId { get; set;}
    public string UserName { get; set; }
    public string Password { get; set; }
}

And the following controller which is retrieving data from a JSON ajax call (I have tried 3 different ways based on three different answers I've seen.

Attempt 1 Error: Incorrect syntax near UserId

public IActionResult UserData([FromBody] User user)
{
    using (SqlConnection cn = new SqlConnection(@"Server=.\SQLEXPRESS..."))
    {
        cn.Query<User>(@"INSERT INTO User VALUES UserId = @UserId, UserName = @UserName, Password = @Password", 
        new
        {
            UserId = user.UserId,
            UserName = user.UserName,
            Password = user.Password
         });

     return StatusCode(200);
     }
}

Attempt 2 Error: Incorrect syntax near the keyword Table

public IActionResult UserData([FromBody] User user)
{
    using (SqlConnection cn = new SqlConnection(@"Server=.\SQLEXPRESS..."))
    {
        cn.Execute(@"INSERT Table(User) VALUES(@UserId, @UserName, @Password)", 
        new
        {
            UserId = user.UserId,
            UserName = user.UserName,
            Password = user.Password
         });

     return StatusCode(200);
     }
}

Attempt 3 (Tried using Dapper.Contrib) Error: Invalid object name Users (I don't know what this is, Users doesn't exist anywhere as I don't use plurals

public IActionResult UserData([FromBody] User user)
{
    using (SqlConnection cn = new SqlConnection(@"Server=.\SQLEXPRESS..."))
    {
        cn.Insert<User>(new User
        {
            UserId = user.UserId,
            UserName = user.UserName,
            Password = user.Password
         });

     return StatusCode(200);
     }
}

Upvotes: 0

Views: 2390

Answers (1)

Shyju
Shyju

Reputation: 218722

In the code you provided for Attempt 1, Your SQL insert statement does not look correct!

Also, If you are simply inserting, you may use the Execute method. This should work.

If your table has only those 3 columns, you can do this.

var q=@"INSERT INTO [User] VALUES (@UserId,@UserName,@Password)";
cn.Execute(q,user);

If your table has more columns than what you specified in the insert query, you should explicitly specify the column names(in the order) in the INSERT statement.

var q=@"INSERT INTO [User](UserId,UserName,Password) VALUES (@UserId,@UserName,@Password)";
cn.Execute(q,user);

Note that the keyword User is a reserved keyword. You can wrap it with square brackets to avoid the error.

Use [User] instead of User.

Reference : https://technet.microsoft.com/en-us/library/dd776381(v=sql.105).aspx#BasicSyntax

Upvotes: 3

Related Questions