CODEWITHSUNDEEP
c#nvarcharsqlparameter
André
André

Reputation: 21

nVarchar and SqlParameter

I'm developing an application which must support several languages. To solve the special characters problem I'm using NVarhcar for my text fields. So my SQL query for a text field is 

insert into tbl_text(text)values(N'Chci tančit v oblasti')

My problem is to put it in SqlCommand, wich is "insert into tbl_text(text)values(N@text)". It saves "N@text" in the DB table, sure.

Do you guys know someway to do it? I'm using C# and SQL 2008.

Sorry if it was hard to understand my question. My English is poor =/

Upvotes: 2

Views: 16317

Answers (5)

Metin Atalay
Metin Atalay

Reputation: 1517

Here is Simple Example

String filePath = @"D:\" + FileName;
SqlCommand command = new SqlCommand();
command.Connection = connection;
command.CommandText = 
    @"DECLARE @TraceId INT = (SELECT MAX(id) FROM sys.traces WITH (NOLOCK))
    SET @TraceId=@TraceId+1

    DECLARE @File NVARCHAR(256);
    Set @File= (@filePath)

    SET @TraceId=@TraceId+1 --Var olandan bir fazla

    DECLARE @MaxFileSize BIGINT = 1 /* max size of file as MegaByte*/
    DECLARE @FileCount INT = 1024 /* max file count for write*/

    exec sp_trace_create @traceid = @TraceId OUTPUT,
                                    @options = 2,
                                    @tracefile = @File,
                                    @maxfilesize = @MaxFileSize, 
                                    @stoptime = NULL,
                                    @filecount = @FileCount

    SELECT @TraceId";

command.Parameters.Add(new SqlParameter("@filePath", SqlDbType.NVarChar)).Value = filePath;

Upvotes: 0

PsychoCoder
PsychoCoder

Reputation: 10755

Add(string, object) has been deprecated for this reason (from Pablo Castro of the SQL Server team):

The problem is that both the C# and the VB.NET compilers will expose very weird behavior for this code:

command.Parameters.Add(“@p”, 0);

you may expect this to use the overload that takes an object and assign the value 0 to it, but instead it will pick the overload that takes a SqlDbType as the second parameter! In order to avoid this (and potentially others) ambiguity between the Add(string, sqldbtype) and Add(string, object), we deprecated Add(string, object) and introduced AddWithValue(string, object). In general, having multiple overloads where the distinguishing parameter type is “object” in one of them is a dangerous thing to do.

Upvotes: 4

Amirshk
Amirshk

Reputation: 8258

Use SQLParameters.

Here is a simple example:

var cmd = _dbCon.CreateCommand();
cmd.CommandText =
    "insert into tbl_text (textfield) values(@textfield)";
cmd.Parameters.Add(new SQLParameter("@textfield", "Chci tančit v oblasti"));
cmd.ExecuteScalar();

Upvotes: 0

BrokenGlass
BrokenGlass

Reputation: 160902

You should parametrize your inserts with SqlParameters which allow you to specify the datatype explicitly. (Also it saves you the headache of figuring out the SQL server injection attack your query caused).

Example:

SqlCommand cmd  = new SqlCommand("insert into tbl_text (text) values(@MYTEXT)", myConnection);
cmd.Parameters.Add(new SqlParameter("@MYTEXT", SqlDbType.NVarChar)).Value = "Chci tančit v";
cmd.ExecuteNonQuery();

Upvotes: 3

František Žiačik
František Žiačik

Reputation: 7614

Don't put "N" before the parameter name, it is only used when using string constant to indicate it is a unicode string. So your query should be: 

insert into tbl_text(text) values (@text)

Upvotes: 1

Related Questions