C# SqlParameters Short Hand

Question

I'm taking data that is in a List of Record objects and putting their contents in to a database:

// Processes a Record and adds it to the database
public bool addRecord(SqlConnection db, List<Record> recordsToAdd)
{
    using (SqlCommand command = db.CreateCommand())
    {
        foreach (Record record in recordsToAdd)
        {
            // Set the query command text
            command.CommandText = @"INSERT INTO SMDGROUP_STPRODMASTER (PRODCODE, TOTFREE, TOTPHYS, ITEMTYPE, PRODESC) VALUES ('@PRODCODE', '@TOTFREE', '@TOTPHYS', '@ITEMTYPE', '@PRODESC')";

            SqlParameter param1 = new SqlParameter("@CURSTAT", record.curstat);
            SqlParameter param2 = new SqlParameter("@ITEMDESC", record.itemdesc);
            SqlParameter param3 = new SqlParameter("@PRODCODE", record.prodcode);
            SqlParameter param4 = new SqlParameter("@TOTFREE", record.totfree);
            SqlParameter param5 = new SqlParameter("@TOTPHYS", record.totphys);
            SqlParameter param6 = new SqlParameter("@ITEMTYPE", record.itemtype);
            SqlParameter param7 = new SqlParameter("@PRODESC", record.proddesc);

            command.Parameters.Add(param1);
            command.Parameters.Add(param2);
            command.Parameters.Add(param3);
            command.Parameters.Add(param4);
            command.Parameters.Add(param5);
            command.Parameters.Add(param6);
            command.Parameters.Add(param7);

            // Execute the query
            command.ExecuteNonQuery();
        }
        return true;
    }
}

Here's my Record class:

class Record
{
    public string curstat { get; set; }
    public string itemtype { get; set; }
    public string itemdesc { get; set; }
    public string prodcode { get; set; }
    public string proddesc { get; set; }
    public string totfree { get; set; }
    public string totphys { get; set; }
}

Just from looking at the code, I've got a feeling that there is a shorter way of achieving this.

But secondly, I'm not even sure if I've done it correctly that the @PARAMETER values are being replaced.

If I view the contents of command, it still shows the query string with the @ parameters.

Also, I'm getting this error on command.ExecuteNonQuery():

String or binary data would be truncated.

The statement has been terminated.

So, my questions are:

Is there a shorter way to set and add multiple parameters to the query?
What could be causing the error?

The error is probably caused by trying to insert data into a column that is not large enough to store it. For example trying to store abcdef in a varchar(5) would cause this error. — Sir Crispalot
– Sir Crispalot, Commented Nov 16, 2011 at 8:46

Mamta D · Accepted Answer · 2011-11-16 12:07:43Z

21

You have a bigger constructor:

 command.Parameters.Add(
    "@CategoryName", SqlDbType.VarChar, 80).Value = "toasters";

edited Nov 16, 2011 at 12:07

Mamta D

6,4803 gold badges30 silver badges43 bronze badges

answered Nov 16, 2011 at 8:46

Royi Namir

149k145 gold badges502 silver badges833 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Rowland Shaw Over a year ago

The benefit of this approach is that will truncate the data to the specified data size and type

Robert McKee Over a year ago

The downside to that approach is that is will silently corrupt your data without any warning. I don't agree with the approach that Microsoft took on this. It really should throw an exception, not silently truncate your data.

M4N · Accepted Answer · 2011-11-16 08:48:18Z

5

Using the method AddWithValue will make the code a little bit shorter:

command.Parameters.AddWithValue("@CURSTAT", record.curstat);
//...

answered Nov 16, 2011 at 8:48

M4N

96.8k45 gold badges225 silver badges265 bronze badges

1 Comment

marc_s Over a year ago

You should check out Can we stop using AddWithValue() already? and stop using .AddWithValue() - it can lead to unexpected and surprising results...

Matt · Accepted Answer · 2011-11-16 08:52:30Z

3

I do it a bit differntly.

I have both a extension method and a static method to create SqlParameters.

public static SqlParameter ToParam(this object v,string name){
return new SqlParameter(name,v);
}

Then I do something like this:

var p = new List<SqlParameter>();
p.Add(record.curstat.ToParam("@curstat"));
p.Add(record.itemdesc.ToParam("@itemdesc"));
//etc...

command.Parameters.AddRange(p.ToList());

answered Nov 16, 2011 at 8:52

Matt

3,6802 gold badges29 silver badges33 bronze badges

Comments

Tim · Accepted Answer · 2011-11-16 08:52:01Z

2

The String or binary data would be truncated. most likely means your putting too many characters into one of your VARCHAR fields. I.e., if your column PRODDESC is a VARCHAR(50), and the string you're trying to insert is 70 characters, you will see that error.

Others have addressed alternative ways of doing the parameters so you can reduce the lines of code.

answered Nov 16, 2011 at 8:52

Tim

28.6k8 gold badges65 silver badges80 bronze badges

Comments

Iravanchi · Accepted Answer · 2011-11-16 08:52:32Z

2

For a shorter syntax, you can use AddRange method of the SqlParameterCollection class. It means:

command.Parameters.AddRange(new [] {
    new SqlParameter(...),
    new SqlParameter(...),
    new SqlParameter(...) });

The error you're getting indicates that a string value doesn't fit in the table column or parameter, and is being truncated. You should check the length of the column in comparison to the data being inserted, or specify the length of parameters using another overload of the SqlParameter constructor.

answered Nov 16, 2011 at 8:52

Iravanchi

5,1679 gold badges43 silver badges59 bronze badges

Comments

Brandon Moore · Accepted Answer · 2011-11-16 09:19:18Z

1

If you wanted to use the following class:

Class MyParam
{
    public string name {get;set;}
    public object value {get;set;}
}

then you could have a List called myParams and do:

foreach(var p in myParams) command.Parameters.AddWithValue(p.name, p.value);

You obviously have to link the parameters and values somehow and there's no way around that. But if you do it in a class like this then the code that actually does the action is only one line long.

answered Nov 16, 2011 at 9:19

Brandon Moore

8,84015 gold badges69 silver badges124 bronze badges

Comments

il_guru · Accepted Answer · 2011-11-16 09:51:23Z

1

I think the message

String or binary data would be truncated.

The statement has been terminated.

comes from an error in your Command Text: in the SQL Query the parameters, even if they are strings, do not need to be quoted.

Replace the command with this

command.CommandText = @"INSERT INTO SMDGROUP_STPRODMASTER 
      (PRODCODE, TOTFREE, TOTPHYS, ITEMTYPE, PRODESC) 
      VALUES (@PRODCODE, @TOTFREE, @TOTPHYS, @ITEMTYPE, @PRODESC)";

To shorten the code i think you could add somewhere (for example in your record class or in an helper class) a method that creates an array of parameter from a record object and then call the AddRange function. It should keeps this function cleaner and you could use it also in other part of you code.

edited Nov 16, 2011 at 9:51

answered Nov 16, 2011 at 9:43

il_guru

8,5483 gold badges46 silver badges52 bronze badges

Comments

James · Accepted Answer · 2011-11-16 09:52:36Z

1

In regards to the error, it's a truncation problem i.e. the length of your parameter is longer than what your column can hold. To resolve this, be more specific when passing your parameters e.g. new SqlParameter("@MyParameter", SqlDbType.VarChar, 30).

Personally I don't think their is anything wrong with how your currently adding the parameters it's readable and does the job. If, however, you want to reduce the amont of lines of code in your function you could either go with what @Royi has suggested or simply package up the parameter adding into another method.

edited Nov 16, 2011 at 9:52

answered Nov 16, 2011 at 8:51

James

82.4k19 gold badges172 silver badges245 bronze badges

5 Comments

Brandon Moore Over a year ago

Actually Tim's answer is more correct. That exception isn't about the parameter size, but about the column size.

James Over a year ago

@BrandonMoore - If you read my answer I clearly state your column is expecting less characters than what your parameter is giving i.e. the parameter data is overflowing the column size. It's the same answer worded differently - I have updated to be even more clear.

Brandon Moore Over a year ago

Fair enough. I was focused on the part about "To get around this you should be more specific when passing your parameters".

James Over a year ago

Yeah just re-read that part and it appears as though I am suggesting a "workaround" instead of the correct approach. Updated.

Brandon Moore Over a year ago

It wasn't unclear. I was just mistaken for believing passing too large a value to a parameter would still cause an error.

THess · Accepted Answer · 2019-11-27 21:01:36Z

0

This worked for me:

     comando.Parameters.Add(
            "@EmailAddress", SqlDbType.VarChar).Value = EmailAddress;

edited Nov 27, 2019 at 21:01

THess

1,0071 gold badge15 silver badges21 bronze badges

answered Nov 27, 2019 at 19:34

The Zeth

1

Collectives™ on Stack Overflow

C# SqlParameters Short Hand

9 Answers 9

2 Comments

1 Comment

Comments

Comments

Comments

Comments

Comments

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

9 Answers 9

2 Comments

1 Comment

Comments

Comments

Comments

Comments

Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related