Given database table Users, (Name, Family Name, Age and Sex). I want to create an update statement whereas any of these columns can be null - not edited / coming from client of some kind. The client creates an object User { Name, FamilyName, Age, Sex }, it will fill only changed properties so basically i'm looking for a way to guess how to build the query and how to send the data to it.
I don't know how to handle this issue at all besides getting whole row and merging it's data with object i've received from client. So far it is what i've done: select > merge > update.
Is there any other way ?
Suppose that your User class is something like this one
public class User
{
public int UserID { get; set; }
public string Name {get; set;}
public string FamilyName {get;set;}
public int? Age { get; set; }
public string Sex { get; set; }
}
(Note the int field defined as Nullable<int>
to allow the insertion of a null value in the corresponding field)
Now, your code that sets the fields, reflecting a null value for your null properties, could be written simply as a normal update. All the stuff required to pass a null value as a parameter is done by the Dapper internals
// Initialize just two fields and leave the other to their defaults
// (null for both strings and nullable ints)
User u = new User();
u.UserID = 1;
u.Name = "Steve";
bool ok = UpdateUser(u);
if(ok) ......
public UpdateUser(User info)
{
using(SqlConnection cnn = new SqlConnection(@"Data Source=(LOCAL);
Initial Catalog=TestDB;
Integrated Security=True;"))
{
cnn.Open();
// Prepare the parameters to pass to Dapper Execute
var pms = new
{
UserID = info.UserID
FirstName = info.Name,
FamilyName = info.FamilyName, // <- this is null
Age = info.Age, // <- this is null
Sex = info.Sex // <- this is null
};
int rows = cnn.Execute(@"UPDATE [UserTable]
SET FirstName= @FirstName,
LastName = @LastName,
Age = @Age,
Sex = @Sex
WHERE UserID = @UserID",
pms);
return rows != 0;
}
}