locked
Pass non english value as parameter to sql procedure from c# RRS feed

  • Question

  • User-257070954 posted

    HI all,

               How can we pass non English values as parameter to sql stored procedure from c#?

        My requirement is, if one customer search product his own language other than english i want to display. For this purpose i am using one parameter in procedure.

    Below i am giving my procedure.Please let me know where i need to make change

    ALTER PROCEDURE [dbo].[GETALLProduct]
    @Product NVARCHAR(100)=NULL,
    @Category VARCHAR(5)=NULL
    AS
    BEGIN

    SELECT Id,ProductName, Image AS Image2, Price,Brand ,UOM
    FROM tblProduct
    WHERE (@Product IS NULL OR ProductName LIKE '%' + @Product + '%' OR Brand LIKE '%' + @Product + '%' )AND (@Category IS NULL OR CategoryId=@Category)
    OR (@Product IS NULL OR ProdMal LIKE N'%' + @Product + '%' OR BrandMal LIKE N'%' + @Product + '%' ) FOR JSON PATH
    END

     

    Sunday, August 2, 2020 11:38 AM

Answers

  • User-939850651 posted

    Hi binustrat,

    I created a simple example. In fact, there is no need to manually add the prefix 'N' to use stored procedures.

    You need to make sure that the type of parameters and fields is Nvarchar.

    Please refer to code below:

    using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ToString()))
                {
                    using (SqlCommand cmd = new SqlCommand("mulLanguageProc",conn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.AddWithValue("@Product", "അരി");
                        conn.Open();
                        DataTable dt = new DataTable();
                        SqlDataAdapter sda = new SqlDataAdapter(cmd);
                        sda.Fill(dt);
                    }
                }
    create table tblProduct(
    [Id] int identity(1,1) primary key,
    [ProductName] Nvarchar(50) not null,
    [Image] Nvarchar(255) not null,
    [Price] float not null,
    [Brand] Nvarchar(50) not null
    )
    
    create proc mulLanguageProc
    @Product NVARCHAR(100)=NULL
    AS
    BEGIN
    SELECT Id,ProductName, [Image], Price,Brand
    FROM tblProduct
    WHERE (@Product IS NULL OR ProductName LIKE '%' + @Product + '%' OR Brand LIKE '%' + @Product + '%' )
    END

    Result:

    Best regards,

    Xudong Peng

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, August 5, 2020 9:52 AM

All replies

  • User-939850651 posted

    Hi binustrat,

    First, make sure that your database can store unicode and that your database uses the correct collation settings.

    Second, use Nvarchar to store the value of the column, and add the prefix "N" in front of the fuzzy query condition, like this:

    ALTER PROCEDURE [dbo].[GETALLProduct]
    @Product NVARCHAR(100)=NULL,
    @Category VARCHAR(5)=NULL
    AS
    BEGIN
    
    SELECT Id,ProductName, Image AS Image2, Price,Brand ,UOM
    FROM tblProduct
    WHERE (@Product IS NULL OR ProductName LIKE N'%' + @Product + '%' OR Brand LIKE N'%' + @Product + '%' )AND (@Category IS NULL OR CategoryId=@Category)
    OR (@Product IS NULL OR ProdMal LIKE N'%' + @Product + '%' OR BrandMal LIKE N'%' + @Product + '%' ) FOR JSON PATH
    END

    And I found a case named How to pass SQL stored procedure NVARCHAR parameter with Hebrew? ,it is very similar to your request, I think you can refer to it for more details.

    Best regards,

    Xudong Peng

    Monday, August 3, 2020 8:48 AM
  • User-257070954 posted

    Hi XuDong Peng,

                  Thanks for response . As per your suggestion i tried below query that's not working

    Declare @Product NVARCHAR(50)
    SET @Product='അരി' -- This is my language
    select * from tblProduct where  ProdMal LIKE N'%' +@Product + '%'

                But below query working as expected

    Declare @Product NVARCHAR(50)
    SET @Product=N'അരി'
    select * from tblProduct where  ProdMal LIKE '%' +@Product + '%'

      IF  i get non english parameter,  tried a non english value prefixed with N. But not working. Below is query

    Declare @Product NVARCHAR(50)
    Declare @Product2 NVARCHAR(50)
    SET @Product='അരി'
    SET @Product2='N'+@Product
    select * from tblProduct where  ProdMal LIKE N'%' +@Product2 + '%'

    So here my question is how we prefix with N, if we get non english parameter value. Because my second query worked as expected

    Tuesday, August 4, 2020 7:27 AM
  • User-939850651 posted

    Hi binustrat,

    I created a simple example. In fact, there is no need to manually add the prefix 'N' to use stored procedures.

    You need to make sure that the type of parameters and fields is Nvarchar.

    Please refer to code below:

    using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ToString()))
                {
                    using (SqlCommand cmd = new SqlCommand("mulLanguageProc",conn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.AddWithValue("@Product", "അരി");
                        conn.Open();
                        DataTable dt = new DataTable();
                        SqlDataAdapter sda = new SqlDataAdapter(cmd);
                        sda.Fill(dt);
                    }
                }
    create table tblProduct(
    [Id] int identity(1,1) primary key,
    [ProductName] Nvarchar(50) not null,
    [Image] Nvarchar(255) not null,
    [Price] float not null,
    [Brand] Nvarchar(50) not null
    )
    
    create proc mulLanguageProc
    @Product NVARCHAR(100)=NULL
    AS
    BEGIN
    SELECT Id,ProductName, [Image], Price,Brand
    FROM tblProduct
    WHERE (@Product IS NULL OR ProductName LIKE '%' + @Product + '%' OR Brand LIKE '%' + @Product + '%' )
    END

    Result:

    Best regards,

    Xudong Peng

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, August 5, 2020 9:52 AM