Whitespace in SQL with C#

Question

I have a Windows application written in C# which connect to SQL database.

I have text fields inside my application and update the database like this:

string name = textBox60.Text;

string sql = "insert into myTable(Name) values ('" + name + "')";
DbHelper.ExecuteNonquery(sql);

public static int ExecuteNonquery(string sql)
    {
        using (SqlConnection cn = new SqlConnection(constr))
        {
            if (cn.State == ConnectionState.Closed)
            {
                cn.Open();
            }
            else if (cn.State == ConnectionState.Open)
            {
                cn.Close();
                cn.Open();
            }
            else if (cn.State == ConnectionState.Broken)
            {
                cn.Close();
                cn.Open();
            }
            using (SqlCommand cm = new SqlCommand(sql, cn))
            {
                return cm.ExecuteNonQuery();
            }
        }
    }

But for every data with type nchar in the database, they are full of white spaces. For example if I fill in abc in the text field, when I check the database it will become "abc___________________" (white spaces) something like this.

How to prevent this other than trim the string only when I read them or to use UPDATE data SET TRIM(data) if I have tons of such data.

Thanks for help.

You should change the column type to `NVARCHAR` to avoid this... — Ivan Golović, Mar 25 '13 at 07:04
`NCHAR(n)` will be padded by SQL Server to the length of `n` - this is the size specified with column declaration. For differences between variable and fixed size columns of data type `CHAR` read this: http://msdn.microsoft.com/en-us/library/ms186939.aspx — Ivan Golović, Mar 25 '13 at 07:07

score 13 · Accepted Answer · answered Mar 25 '13 at 07:07

But for every data with type nchar in the database, they are full of whitespaces.

Yup, that's because the nchar type is a fixed-width type. You're basically telling the database that you want every value for that field to have a length of 20 (or whatever it's set to). You should use nvarchar instead, which is a variable width field.

You should also avoid writing code like this:

string sql = "insert into myTable(Name) values ('" + name + "')";

Instead, you should use parameterized SQL, putting a placeholder parameter in the SQL itself, and then setting the value for the parameter in the command. This will avoid SQL injection attacks, data conversion issues, and keep your code (the SQL) cleanly separated from the data (the parameters). It will mean you need to change your helper method though. (Your helper method looks pretty odd to start with - surely when you've just created your connection, you just need to open it... is that code left over from a time when you didn't create a new connection object on each call?)

Additionally not using parameters in SQL is the best way to slow down your database. When DB receives your SQL it will first need to parse it and after that calculate the plan how to execute the query. This is a significant amount of processing. Now, if you use parameters DB can cache the parsed SQL and execution plan and use that in subsequent SQL. It will just fill in different parameter values. Without using parameters you are forcing DB to always do this calculation, slowing down the DB. — pero, Mar 25 '13 at 09:27

score 6 · Answer 2 · answered Mar 25 '13 at 07:05

Because you've declared the database table using a char(n) rather than a varchar(n), it is always a fixed length, and if you provide a shorter string, then it will be padded with spaces.

If you don't want the spaces, declare the column as varchar(n).

Also, I'm not sure what all of the ceremony is that you're doing with the connection before you use it, but it seems mostly pointless. You've just called new SqlConnection(...). Practically by definition, this means that cn will be closed - there's no need to check its status. Just call Open() on it and move on to creating your command.

(All of the above is predicated on the column being declared as char(n). If it's nchar(n) then you should switch to nvarchar(n)).

Whitespace in SQL with C#

2 Answers2