Pass non english value as parameter to sql procedure from c#[Answered] RSS

• • Reply
  binustrat

  Member

  35 Points

  156 Posts

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

  Aug 02, 2020 11:38 AM|binustrat|LINK

  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

   

• • Reply
  XuDong Peng

  Contributor

  2090 Points

  668 Posts

  Re: Pass non english value as parameter to sql procedure from c#

  Aug 03, 2020 08:48 AM|XuDong Peng|LINK

  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

  .NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today.
• • Reply
  binustrat

  Member

  35 Points

  156 Posts

  Re: Pass non english value as parameter to sql procedure from c#

  Aug 04, 2020 07:27 AM|binustrat|LINK

  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

• • Reply
  XuDong Peng

  Contributor

  2090 Points

  668 Posts

  Re: Pass non english value as parameter to sql procedure from c#

  Aug 05, 2020 09:52 AM|XuDong Peng|LINK

  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

  .NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today.