CODEWITHSUNDEEP
c#sqlsql-server-2008collation
Kerem Celebi
Kerem Celebi

Reputation: 77

Set non-Latin alphabet for SQL on C# side

The problem is when user insert non-Latin word to database, if collation not set to that alphabet, the word comes like '?????' But if user insert word like N'word' or I select it like N'word' there is no problem.
I use hastable function on C# side so is there any solution for use N'word' format automaticly or must I use N before text everytime I call function?
I already use NVARCHAR format by the way.

The simple way to ask;
I don't want to use N'word' format every time when I select item from database.
Is there any trigger, stored procedure or function for add automaticly N before text I select.

Upvotes: 0

Views: 516

Answers (2)

Guffa
Guffa

Reputation: 700342

You shouldn't use the N'word' format at all, you should use parameters. When you concatenate values into a string to create a query, it's wide open to SQL injection attacks unless you managage to escape all input exactly right.

Put parameters in the query and add the values to the Parameters collection of the command. Example:

string query = "select Id from Persons where Name = @Name";

using (SqlCommand cmd = new SqlCommand(query, connection)) {

  cmd.Parameters.Add('@Name', SqlDbType.NVarChar, 50).value = name;

  using (SqlDataReader reader = cmd.ExecuteReader()) {
    while (reader.Read()) {
      ...
    }
  }

}

Using unicode types like NVarChar for the parameter ensures that the parameter value is sent as unicode, equivalent to the N'word' format.

Upvotes: 2

nomail
nomail

Reputation: 650

Why don't you use some sort of ORM. Dapper is a lightweight ORM: https://github.com/StackExchange/dapper-dot-net

It will solve your problems with parameter passing if this is the troubles you are having

Upvotes: 0

Related Questions