Adding SqlCommand parameters in C#

As others have commented my answer was more of a comment than an answer, I will expand. I was merely following SIMILAR to what M4N was offering.
However, I have found, and other noted that although you do NOT NEED to explicitly list all the columns of the table you are inserting, it also implies you are inserting them in the order they are in the table structure. If your parameters were bogus simple like @var1, @var2, @var3, but represented column7, column2, column4 of the table, you are toast, especially if data types are different.

So, the need of explicitly stating the columns prevents ambiguous results as indicated by yet another. If the table structure changes and does not allow nulls, at least you will get an error message that the insert failed because columnX does not allow null values and you can explicitly fix that.

So, back to the answer, by explicitly including the columns to be inserted. I also try NOT to use exact column name as @ parameter, again for ambiguity. Dont want to mentally confuse a column vs parameter, so I would do @parmColumnName -- but again, that's just me. I would have

var queryToDB = new SqlCommand(
@"INSERT INTO [dbo].[FileUploadDBs] 
   ( Name, 
     Path, 
     Blasted, 
     CreatedBy, 
     CreateDate )
   values 
   ( @parmName, 
     @parmPath, 
     @parmBlasted, 
     @parmCreatedBy, 
     @parmCreatedDate)", cons);
                            

         
queryToDB.Parameters.Add("@parmName", SqlDbType.VarChar) { Value = yourVarContainingTheName };
queryToDB.Parameters.Add("@parmPath", SqlDbType.VarChar) { Value = yourVarForThePath };
// guessing on data type for blasted and created by
queryToDB.Parameters.Add("@parmBlasted", SqlDbType.Int) { Value = yourVarForBlasted };
// guessing on data type for created by, could be a foreign key to user lookup table
queryToDB.Parameters.Add("@parmCreatedBy", SqlDbType.Int) { Value = yourVarForCreatedBy };
queryToDB.Parameters.Add("@parmCreateDate", SqlDbType.DateTime2) { Value = DateTime.Now };

Also, since naming the parameters to match, even if you added them out-of-order, but matching the given SQL command, it would find them and be available, but better practice and habit is to add in the same order.

Adding SqlCommand parameters in C#

Related

Recent Posts