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…

Accepted Answer

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

Whitespace in SQL with C#

Solution

Problem