CODEWITHSUNDEEP
c#sqlwhitespace
AkariKamigishi
AkariKamigishi

Reputation: 277

Whitespace in SQL with C#

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.

Upvotes: 2

Views: 4036

Answers (2)

Jon Skeet
Jon Skeet

Reputation: 1499950

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?)

Upvotes: 13

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239646

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)).

Upvotes: 6

Related Questions