SQL kabi kalit so'z yordamida ma'lumotlarni qidirish

Ma'lumotlarni jo'natish bo'yicha jadvalda ma'lumotlarni qidirishga mo'ljallangan SQL saqlangan amaliyotim bor. Lekin men uni ishlatganimda biron bir natija topa olmayapman. Bu mening C# kodi saqlangan amaliyotlaridan foydalanadi:

List SerchResult = new List();

    public DLAdvancedSearch(Common.CommonPersonAdvancedSearch data)
    {
        //Creating Connection Started...
        SqlConnection connection = new SqlConnection("Data Source=(local);Initial Catalog=Khane;Integrated Security=True");
        //Creating Connection Finished.
        //Creating Command To Run started...
        SqlCommand command = new SqlCommand();
        command.Connection = connection;
        //Creating Command To Run Finished.
        //Setting Command Text...
        command.CommandType = CommandType.StoredProcedure;
        command.CommandText = "AdvancedSearch";
        //Setting Command Text Finished.

        //Making And Setting SQL AdvancedSearch Parametters...
        SqlParameter FirstName = new SqlParameter("FirstName", SqlDbType.NVarChar, 50);
        FirstName.Value = data.FirstName;
        command.Parameters.Add(FirstName);

        SqlParameter LastName = new SqlParameter("LastName", SqlDbType.NVarChar, 50);
        LastName.Value = data.LastName;
        command.Parameters.Add(LastName);

        SqlParameter FatherName = new SqlParameter("FatherName", SqlDbType.NVarChar, 50);
        FatherName.Value = data.FatherName;
        command.Parameters.Add(FatherName);

        SqlParameter NationalCode = new SqlParameter("NationalCode", SqlDbType.Int);
        NationalCode.Value = data.NationalCode;
        command.Parameters.Add(NationalCode);

        SqlParameter ShenasnameCode = new SqlParameter("ShenasnameCode", SqlDbType.Int);
        ShenasnameCode.Value = data.ShenasnameCode;
        command.Parameters.Add(ShenasnameCode);

        SqlParameter State = new SqlParameter("State", SqlDbType.NVarChar, 50);
        State.Value = data.State;
        command.Parameters.Add(State);

        SqlParameter City = new SqlParameter("City", SqlDbType.NVarChar, 50);
        City.Value = data.City;
        command.Parameters.Add(City);

        SqlParameter Address = new SqlParameter("Address", SqlDbType.NVarChar, 50);
        Address.Value = data.Address;
        command.Parameters.Add(Address);

        SqlParameter PostalCode = new SqlParameter("PostalCode", SqlDbType.Int);
        PostalCode.Value = data.PostalCode;
        command.Parameters.Add(PostalCode);

        SqlParameter SportType = new SqlParameter("SportType", SqlDbType.NVarChar, 50);
        SportType.Value = data.SportType;
        command.Parameters.Add(SportType);

        SqlParameter SportStyle = new SqlParameter("SportStyle", SqlDbType.NVarChar, 50);
        SportStyle.Value = data.SportStyle;
        command.Parameters.Add(SportStyle);

        SqlParameter RegisterType = new SqlParameter("RegisterType", SqlDbType.NVarChar, 50);
        RegisterType.Value = data.RegisterType;
        command.Parameters.Add(RegisterType);

        SqlParameter Gahremani = new SqlParameter("Gahremani", SqlDbType.NVarChar, 50);
        Gahremani.Value = data.Ghahremani;
        command.Parameters.Add(Gahremani);

        //Making And Setting SQL AdvancedSearch Parametters Finished.

        //Reading Data And Save in SearchResult List...
        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();
        //Reading Data And Save in SearchResult List Finished.


    }

Va bu mening saqlangan amaliyotim:

USE [Khane]
GO
/****** Object:  StoredProcedure [dbo].[AdvancedSearch]    Script Date: 10/28/2011 01:02:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      
-- Create date: 
-- Description: 
-- =============================================
ALTER PROCEDURE [dbo].[AdvancedSearch] 
@FirstName nvarchar(50) = null,
@LastName nvarchar(50) = null,
@FatherName nvarchar(50) = null,
@NationalCode int = null,
@ShenasnameCode int = null,
@State nvarchar(50) =null,
@City nvarchar(30) =null,
@Address nvarchar(250)=null,
@PostalCode int=null,
@SportType nvarchar(50)=null,
@SportStyle nvarchar(50)=null,
@RegisterType nvarchar(50)=null,
@Gahremani nvarchar(50)=null
AS
BEGIN
if @FirstName<>null
begin
select * from PersonsDataTbl where Name like '%'[email protected]+'%'
end

if @LastName<>null
begin
select * from PersonsDataTbl where LastName like '%'[email protected]+'%'
end

if @FatherName<>null
begin
select * from PersonsDataTbl where FatherName like '%'[email protected]+'%'
end

if @NationalCode<>null
begin
select * from PersonsDataTbl where NationalCode like '%'[email protected]+'%'
end

if @ShenasnameCode<>null
begin
select * from PersonsDataTbl where ShenasnameCode like '%'[email protected]+'%'
end

if @State<>null
begin
select * from PersonsDataTbl where State like '%'[email protected]+'%'
end

if @City<>null
begin
select * from PersonsDataTbl where City like '%'[email protected]+'%'
end

if @Address<>null
begin
select * from PersonsDataTbl where Address like '%'[email protected]+'%'
end

if @PostalCode<>null
begin
select * from PersonsDataTbl where PostalCode like '%'[email protected]+'%'
end

if @SportType<>null
begin
select * from PersonsDataTbl where SportType like '%'[email protected]+'%'
end

if @SportStyle<>null
begin
select * from PersonsDataTbl where SportStyle like '%'[email protected]+'%'
end

if @RegisterType<>null
begin
select * from PersonsDataTbl where RegisterType like '%'[email protected]+'%'
end

if @Gahremani<>null
begin
select * from PersonsDataTbl where Ghahremani like '%'[email protected]+'%'
end

END

Nima qilishim kerak?

2
@FirstName uchun qanday parametrni o'tqazasiz va sizning jadvalingizdagi ma'lumotlarga mos keladigan ma'lumot mavjud.
qo'shib qo'ydi muallif Abe Miessler, manba

2 javoblar

Instead of = and <> you should use IS and IS NOT to compare to NULL:

if @FirstName IS NOT NULL
6
qo'shib qo'ydi

Muammoingizni bu erda ajratib olishingiz kerak. Birinchi savol o'zingizga savol berishingiz kerak

O'zim saqlangan amaliyotim kutilganidek ishlaydi?

O'zingizning C# kodingizni tekshirib turing, chunki siz saqlangan tartiblarning ishlashini aniqlaysiz. Bu savolga javobni bilganingizdan so'ng, siz ikkita keyingi savolingizdan birini olasiz:

  1. Why doesn't my stored procedure work?
  2. Why can't I call my working stored procedure successfully from my C# application?

Bunday muammolarni qanday echish kerakligini o'rganish muammoni hal qilishning kalitidir. Bilamizki, sizning aloqa satringiz noto'g'ri bo'lishi mumkin.

3
qo'shib qo'ydi
Bilaman. Ammo menda SQL haqida juda ko'p ma'lumot yo'q va mening SQL kodlarimda juda ko'p xatolarga ega
qo'shib qo'ydi muallif ahmadali shafiee, manba