I'm writing a C# program to enter data into SQL Server 2008 database.
I use the following function to enter info into database
public bool AddSupplier(string name, string contactPerson, string telephone, string fax, string type, string payment, string comments)
{
_cmd.CommandText =
"INSERT INTO suppliers (name,contactPerson,telephone,fax,type,payment,active,comments) VALUES (@name,@contactPerson,@telephone,@fax,@type,@payment,@active,@comments);";
_cmd.Parameters.Clear();
_cmd.Parameters.AddWithValue("@name", name);
_cmd.Parameters.AddWithValue("@contactPerson", contactPerson);
_cmd.Parameters.AddWithValue("@telephone", telephone);
_cmd.Parameters.AddWithValue("@fax", fax);
_cmd.Parameters.AddWithValue("@type", type);
_cmd.Parameters.AddWithValue("@payment", payment);
_cmd.Parameters.AddWithValue("@active", 1);
_cmd.Parameters.AddWithValue("@comments", comments);
_con.Open();
_cmd.ExecuteNonQuery();
_con.Close();
return true;
}
This works fine, I can add info in Hebrew and it shows up in the database.
When I try to edit it using this function
public bool EditSupplier(int supplierId,string name, string contactPerson, string telephone, string fax, string type, string payment, string comments)
{
_cmd.CommandText = "UPDATE suppliers SET name='" + name + "' ,contactPerson='" + contactPerson + "',telephone='" + telephone +
"',fax='" + fax + "',type='" + type + "',payment='" + payment + "',comments='" + comments +
"' WHERE supplierId =" + supplierId + ";";
_con.Open();
_cmd.ExecuteNonQuery();
_con.Close();
return true;
}
I end up with ???
instead of the Hebrew input
Just use parameterized SQL for the update command as well as the insert command. You should be doing that anyway in order to prevent SQL Injection attacks - the fact that it plays better with non-ASCII characters is a bonus.
(IIRC, you can use a literal from N'...'
if you really must, but I'd strongly advise you not to.)
Additionally, it would be better to create a new connection and command each time you want to use the database, and using a using
statement to dispose of it at the right time. Don't worry about the efficiency side - the built-in connection pool will handle that.
See more on this question at Stackoverflow