Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Save value in uppercase when adding row to database

I have three columns in the db table that looks as follow: enter image description here

When I add a new row, it should store the value on column fieldname in uppercase. How can I do that?

like image 675
softshipper Avatar asked Aug 02 '16 06:08

softshipper


People also ask

How can insert data in uppercase in SQL?

The UPPER() function converts a string to upper-case.

How do you add values to uppercase?

The UCASE() function converts the value of a field to uppercase.

How do I make the first letter capital in SQL Server?

We use SQL UPPER function to convert the characters in the expression into uppercase. It converts all characters into capital letters.

How can we check both lowercase and uppercase in SQL?

Case sensitive search in SQL Server can be achieved either by using COLLATE or by using BINARY_CHECKSUM(). COLLATE is the T-SQL clause used to define collation. BINARY_CHECKSUM() is a built-in system function used to compare the binary check-sum value.


1 Answers

Since you tagged the question with entity framework, I assume you want to do it in your data layer or close to DB. There's a number of ways for doing this.

You could override SaveChanges() in your context. This will move the logic away from the model, but still ensure that the correct value is saved. Also, if you want it on several entities you can use an interface. When it's an interface you can do it for several of your entities without any duplicate code, as long as it's the same property. Otherwise you would need an attribute and reflection. Reusability is pretty high, but it adds some overhead to your SaveChanges().

public class CustomerEntity()
{
    public string Name {get;set;}
}

public MyCustomContext : DbContext
{    
    // Other stuff...

    public override int SaveChanges()
    {
        foreach (var entry in ChangeTracker.Entries<CustomerEntity>())
        {
            if (entry.State == EntityState.Modified || entry.State == EntityState.Added)
            {
                // Possibly check for null or if it's changed at all.
                entry.Entity.Name = entry.Entity.Name.ToUpper();
            }
        }
        return base.SaveChanges();
    }
}

And with an interface:

public interface INameIsAlwaysUpperCase
{
    string Name {get;set;}
}

public MyCustomContext : DbContext
{    
    // Other stuff...

    public override int SaveChanges()
    {
        foreach (var entry in ChangeTracker.Entries<INameIsAlwaysUpperCase>())
        {
            if (entry.State == EntityState.Modified || entry.State == EntityState.Added)
            {
                // Possibly check for null or if it's changed at all.
                entry.Entity.Name = entry.Entity.Name.ToUpper();
            }
        }
        return base.SaveChanges();
    }
}

You can add a custom validation. This will throw exception if it's not saved correctly. That way you can move the responsibility to the consumer of the model. However, depending on your scenario, you might not want to throw an exception. This is my favourite since it forces the consumer to do it the right way. As per comments, why throw when you can silently convert it? Yes, it's a valid question. For me it's about forcing the consumer of the data layer to use it correctly, and not let the daya layer decide what to to with the data. I personally don't like it when the business layer asks the data layer to save one thing, and then the data layer saves another thing. If lower case isn't a valid option, then it shouldn't be saved. I don't think it's much more different from using [Required]. But it's really about context and what works in your particular case.

public class CustomerEntity() : IValidatableObject
{
    public string Name {get;set;}

    public IEnumerable<ValidationResult> Validate(ValidationContext validationContext)
    {
        // Possibly check for null here as well...
        if (this.Name.ToUpper() != this.Name)
        {
            yield return new ValidationResult("You need to save as upper!"); 
        }
    }
}

Use a property that manages this for you. This may be the simplest solution, even if I like to keep my entities "clean". It's absolutely the solution that will require least effort. However, the reusability is low, and what if you use your entitites all over the application and want the value to be lower case until it's actually saved? That's not possible. But, again, I think it comes down to your particular situation. If you want the value to be upper case even before you save it, this is probably the best solution!

public class CustomerEntity()
{
    string _name;
    public string Name 
    {
        get { return _name; }
        set { _name = value.ToUpper(); } // Check for null ?
    }
}

Do it when saving. This moves the logic to when you're saving your entity. This is probably the least preferable option, since the reusability is non-existing. What happens in Update()? However, the OP specifically states "When I add a new row", so it may only be applicable when adding new entities. And in that case it could very well be the most prefered choice since it allows updates to have lower case. But it would have to depend on the use case.

public void AddCustomer(string name)
{
    var customer = new CustomerEntity
    {
        Name = name.ToUpper()
    };
    _context.Customers.Add(customer);
}
like image 73
smoksnes Avatar answered Sep 22 '22 17:09

smoksnes