Click here to Skip to main content
15,887,596 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more: , +
Hi ,

I am trying to send Unicode characters to procedure

C#
var employess = db.spEnqGetEmployeesOfUser(filter.LogUserID, filter.StartIndex, filter.EndIndex, filterText, sortText).ToList();


Below is what i am sending in filtertext :-
EmployeeName LIKE N'%लोपे%'

SQL
CREATE PROCEDURE [dbo].[spEnqGetEmployeesOfUser](
					@UserID INT,
					@StartNumber INT,
					@PageSize INT,
					@Filter NVARCHAR(MAX),
					@Sort VARCHAR(MAX)
				   )
AS
BEGIN


However when i try to check in my sql profileer below is what i get

exec [dbo].[spEnqGetEmployeesOfUser] @UserID=4,@StartNumber=0,@PageSize=15,@Filter='EmployeeName LIKE N''%????%''',@Sort=''

what i am doing wrong , my paramter is varchar and I am also sending varchar text only why it gets converted into ???? symbols which in case not retuning me proper records

What I have tried:

SQL
CREATE PROCEDURE [dbo].[spEnqGetEmployeesOfUser](
					@UserID INT,
					@StartNumber INT,
					@PageSize INT,
					@Filter NVARCHAR(MAX),
					@Sort VARCHAR(MAX)
				   )
AS
BEGIN

 DECLARE @Employees Table 
(
	RowNumber INT PRIMARY KEY,
	TotalCount INT,
	UserID INT UNIQUE NOT NULL,
	EmployeeID INT UNIQUE NOT NULL,
	EmployeeCode NVARCHAR(150) NOT NULL,
	EmployeeName NVARCHAR(150),
	Division NVARCHAR(150)
)

IF (@Sort IS NULL OR LTRIM(RTRIM(@Sort)) = '')
BEGIN
	SET @Sort = 'EmployeeName'
END

DECLARE @SQLQuery AS NVARCHAR(MAX)
SET @SQLQuery = ' SELECT * FROM (
	SELECT ROW_NUMBER() OVER(ORDER BY ' + @Sort + ') AS RowNumber,COUNT(UserID) OVER() AS TotalCount,  MPR.* FROM (
		SELECT u.UserID,u.EmployeeID,e.Code AS EmployeeCode,u.Name AS EmployeeName, u.Division 
		FROM F_UserByHierarchy(' +  CAST(@UserID AS VARCHAR(15)) + ') u
		INNER JOIN [User] us ON u.UserID = us.UserID
		INNER JOIN Employee e ON u.EmployeeID = e.EmployeeID
		INNER JOIN DesignationLevel dl ON us.DesignationLevelID = dl.DesignationLevelID
		WHERE dl.IsMappingRequires = 1
		) MPR '

IF  (@Filter IS NOT NULL AND LTRIM(RTRIM(@Filter)) <> '')
BEGIN
	SET @SQLQuery = @SQLQuery + ' WHERE ' + @Filter
END		

SET @SQLQuery	= @SQLQuery + ' ) MP
		WHERE RowNumber BETWEEN ' + CAST((@StartNumber + 1) AS VARCHAR(15)) +  
	   ' AND ' + CAST(((@StartNumber + @PageSize))  AS VARCHAR(15)) 

--SELECT @SQLQuery
INSERT INTO @Employees 
EXEC (@SQLQuery)

SELECT * FROM @Employees
END
Posted
Updated 16-Mar-16 23:40pm
v3

1 solution

It could be that SQL Profiler is unable to read that char while SQL itself is ok with it.

Check that it actually doesn't work by creating an empty table and inserting unicode into it. Check on the DB if it works.

You can send unicode chars in an array instead. You'll need to know the unicode of each char. You can get that with this query:
SQL
DECLARE @nstring nchar(12);
SET @nstring = N'लो';
SELECT UNICODE(@nstring)


In you sql you'll have to decode it from an int:
SQL
DECLARE @uni int;
SET @uni= N'लो';
SELECT NCHAR(@uni)


That is all a massive hack tho.

Check that the SP parameters are set as NVarChar. You may have to look at the context.designer.cs to find it

Let me know if those ideas don't work
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900