Send Unicode String From C# To SQL Server 2008 DB

Question

I want to send some unicode strings (for example "ش") to my SQL DataBase stored procedure. and my code is :

SqlConnection connection = new SqlConnection("Data Source=(local);Initial Catalog=Khane;Integrated Security=True");

SqlCommand command = new SqlCommand();
command.Connection = connection;
command.CommandType = CommandType.Text; 



command.CommandText = s;
connection.Open();



SqlDataReader reader = command.ExecuteReader();

while (reader.Read())
{
Common.CommonPersonSerchResult res = new Common.CommonPersonSerchResult();

res.ID = (int)reader.GetValue(0);
res.FirstName = reader.GetValue(1).ToString();
res.LastName = reader.GetValue(2).ToString();
res.FatherName = reader.GetValue(3).ToString();
res.NationalCode = (int)reader.GetValue(4);
res.ShenasnameCode = (int)reader.GetValue(5);
res.BirthDate = reader.GetValue(6).ToString();
res.State = reader.GetValue(7).ToString();
res.City = reader.GetValue(8).ToString();
res.PostalCode = (int)reader.GetValue(10);
res.SportType = reader.GetValue(11).ToString();
res.SportStyle = reader.GetValue(12).ToString();
res.RegisterType = reader.GetValue(13).ToString();
res.Ghahremani = reader.GetValue(14).ToString();

SerchResult.Add(res);

}

connection.Close();

But I can't see any results but I know There is some rows can be show

this is my stored procedure:

USE [Khane]
GO
/****** Object:  StoredProcedure [dbo].[QuickSerch]    Script Date: 10/19/2011 18:31:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[QuickSerch] 
@item nvarchar(300)
AS
BEGIN    
select * from PersonsDataTbl 
where 
Name like '%@item%' or 
LastName like '%@item%' or
FatherName like '%@item%' or
NationalCode like '%@item%' or
ShenasnameCode like '%@item%' or
BirthDate like '%@item%' or
State like '%@item%' or
City like '%@item%' or
Address like '%@item%' or
PostalCode like '%@item%' or
SportType like '%@item%' or
SportStyle like '%@item%' or
RegisterType like '%@item%' or
Ghahremani like '%@item%' 
END

Tom H · Accepted Answer · 2011-10-19 15:38:06Z

4

Your stored procedure code should be:

LIKE '%' + @item + '%'

Otherwise you're looking for values that contain the literal string "@item".

Also, be aware that your code isn't likely to perform very well if you have a large number of rows in your table. Between the OR statements and the leading wildcards it won't be able to make use of any indexes. You might want to look into fulltext searching.

answered Oct 19, 2011 at 15:38

Tom H

47.6k15 gold badges90 silver badges134 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

StuartLC Over a year ago

+1 And I guess the collation and accenting might need to be investigated too. I sincerely hope that there aren't many rows in the DB ... table scan city.

Ahmadali Shafiee Over a year ago

thanks but I don't know fulltext search and this Project doesn't needs it.Thanks agin.LIKE '%' + @item + '%' worked

Collectives™ on Stack Overflow

Send Unicode String From C# To SQL Server 2008 DB

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related