CODEWITHSUNDEEP
c#.netado.net
twaldron
twaldron

Reputation: 2752

Best way to validate data before inserting

I am using ado.net to call a sproc and insert data. What is the best way to make sure none of the parameters are null before i Insert? The table will allow nulls on some columns but in this case i need to ensure each value is not null before it gets inserted. I could just check each value for null and throw a nullargument exception before it assign it the SqlParameter. Just not sure if this is the best way to do it.

Thanks

here is a bit of my code for reference

  using (var con = new SqlConnection(ConnectionString.GetWebTablesConnectionString()))
        using (var cmd = new SqlCommand("InsertNewCustomer", con))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("dealerid", SqlDbType.Int).Value = dealerid;
            cmd.Parameters.Add("firstname", SqlDbType.NVarChar, 50).Value = customerToInsert.FirstName;
            cmd.Parameters.Add("lastname", SqlDbType.NVarChar, 50).Value = customerToInsert.LastName;
            cmd.Parameters.Add("mailingaddress", SqlDbType.NVarChar, 50).Value = customerToInsert.MailingAddress.Addr;
            cmd.Parameters.Add("mailingcity", SqlDbType.NVarChar, 30).Value = customerToInsert.MailingAddress.City;
            cmd.Parameters.Add("mailingstate", SqlDbType.NVarChar, 25).Value = customerToInsert.MailingAddress.State;
            cmd.Parameters.Add("mailingzip", SqlDbType.NVarChar, 10).Value = customerToInsert.MailingAddress.Zip;
            cmd.Parameters.Add("mailingcountry", SqlDbType.NVarChar, 2).Value = customerToInsert.MailingAddress.Country;
            cmd.Parameters.Add("homephone", SqlDbType.NVarChar, 20).Value = customerToInsert.HPhone;
            cmd.Parameters.Add("email", SqlDbType.NVarChar, 75).Value = customerToInsert.Email;
            cmd.Parameters.Add("referredBy", SqlDbType.NVarChar, 50).Value = customerToInsert.ReferredBy;

            con.Open();
            cmd.ExecuteNonQuery();
         }

Upvotes: 1

Views: 2644

Answers (2)

Mikhail
Mikhail

Reputation: 898

Of course, you can go and write 10-15 if-else statements. However, I would not recommend doing so if you plan to scale your solution and have the separation of concerns in place as well as avoiding the code duplication. Based on my experience, I would recommend you to use the following library:

http://fluentvalidation.codeplex.com/

It's a rule-based validation engine that is very easy to use. Just look at the examples on Codeplex. It's a very simple and straightforward framework. Basically, you can add the rules for validating your domain objects and encapsulate the validation rules into the entity-specific validation class. It will allow you to easily add one more "stored procedure call" without conditional logic and it will not violate the Open-Closed principle (you'll need to add a new validator instead of modifying the existing one)

You will need to implement your own custom validator (for instance, CustomerValidator class) Check the validity of the Customer object even before you open the connection and start initializing the SqlCommand:

CustomerValidator validator = new CustomerValidator();
ValidationResult results = validator.Validate(customerToInsert);
if(results.IsValid){
    using (var con = new SqlConnection(ConnectionString.GetWebTablesConnectionString()))
        using (var cmd = new SqlCommand("InsertNewCustomer", con))
        {
            //Do the actual insert / SP call here, your object is valid
        }
    }
}

Moreover, you can now write the Unit test for your validation rules that are encapsulated into the CustomerValidator class.

Upvotes: 3

paparazzo
paparazzo

Reputation: 45096

Agree with validation on the UI but I would also check at the data layer.

if(string.IsNullOrEmpty(dealerid)) throw new ArgumentNullException("dealerid");

This test for Null Or Empty. If you want to accept empty then only test for null.

If dealerID is a string make sure it will parse to Int.

May want to do other stuff like trim(). Unless they specifically want to include leading and trailing spaces it makes for messy queries.

Often missed is SQL does not accept as broad a date range as .NET so need to test for date range.

Upvotes: 1

Related Questions