C#•2y ago

✅ Dapper Generic Repository Error in Insert Method (SOLVED)

public void Insert(Entity entity)
    {
        using (var connection = dbContext.CreateConnection())
        {
            string tableName = typeof(Entity).Name;
            PropertyInfo[] properties = typeof(Entity).GetProperties();

            string columns = string.Join(", ", properties.Select(p => p.Name));
            string parameters = string.Join(", ", properties.Select(p => "@" + p.Name));
            string query = $"INSERT INTO {tableName} ({columns}) VALUES ({parameters})";
            // Query result: INSERT INTO Category (Name, [Order], Id, CreatedAt, CreatedBy, UpdatedAt, UpdatedBy) 
VALUES (@Name, @[Order], @Id, @CreatedAt, @CreatedBy, @UpdatedAt, @UpdatedBy)

            query = query.Replace("Order", "[Order]");  // I added a replace restriction because it is a special name in Order SQL.

            connection.Execute(query, entity);
        }
    }

public void Insert(Entity entity)
    {
        using (var connection = dbContext.CreateConnection())
        {
            string tableName = typeof(Entity).Name;
            PropertyInfo[] properties = typeof(Entity).GetProperties();

            string columns = string.Join(", ", properties.Select(p => p.Name));
            string parameters = string.Join(", ", properties.Select(p => "@" + p.Name));
            string query = $"INSERT INTO {tableName} ({columns}) VALUES ({parameters})";
            // Query result: INSERT INTO Category (Name, [Order], Id, CreatedAt, CreatedBy, UpdatedAt, UpdatedBy) 
VALUES (@Name, @[Order], @Id, @CreatedAt, @CreatedBy, @UpdatedAt, @UpdatedBy)

            query = query.Replace("Order", "[Order]");  // I added a replace restriction because it is a special name in Order SQL.

            connection.Execute(query, entity);
        }
    }

ERROR: Microsoft.Data.SqlClient.SqlException: 'Must declare the scalar variable "@".' Hello everyone, I'm coding a Generic Repository with Dapper (database: MSSQL). I coded the Get and Delete operations, but I am getting the above error in the Insert operation. How do I solve this error?

6 Replies

phaseshift•2y ago

what is the value of parameters in the debugger?

BekirKOP•2y ago

parameters: @Name, @[Order], @Id, @CreatedAt, @CreatedBy, @UpdatedAt, @UpdatedBy

phaseshift•2y ago

@[Order] - is that valid?

BekirKOP•2y ago

Maybe its not valid. I didnt thought the error was for that. But if I wouldn’t use query.replace then it throws another error: Incorrect syntax near the keyword 'Order'. What should I do for that Yes. You are correct. I deleted query.replace then I updated the code like all columns has bracket:

string columns = string.Join(", ", properties.Select(p => "[" + p.Name + "]"));

string columns = string.Join(", ", properties.Select(p => "[" + p.Name + "]"));

Then my new error is: : 'Cannot insert explicit value for identity column in table 'Category' when IDENTITY_INSERT is set to OFF I hope i'll handle it. I'm happy to my error is changed khskshkhsk Yes I did it now. Thanks a lot buddy @phaseshift. . Codes are successfully working now I was getting this error because I also take the Id value as a parameter. I specified that it should not take the Id column and it was fixed.

phaseshift•2y ago

glad you got it fixed 😁 I was only guessing - dont really know SQL 😮

BekirKOP•2y ago

ahahhahahhahah your guess is good 🙂

Gaming

Programming

✅ Dapper Generic Repository Error in Insert Method (SOLVED)