Блог

Я хочу передать несколько параметров хранимой процедуре с помощью SQL-запроса.

Но при этом я получаю эту ошибку, если какой-либо параметр равен нулю

System.Data.SqlClient.SQLException: ‘Параметризованный запрос’ (@P_Employee_Code nvarchar(4000),@P_Administration_Code nvarchar’ ожидает параметр ‘@P_Employee_Code’, который не был предоставлен.’

Это мой код для передачи параметров в мою хранимую процедуру

 int? Employee_Code_As_Int = string.IsNullOrEmpty(Employee_Code) ? null : (int?)Convert.ToInt32(Employee_Code);
int? Administration_Code_As_Int = string.IsNullOrEmpty(Administration_Code) ? null : (int?)Convert.ToInt32(Administration_Code);
int? Department_Code_As_Int = string.IsNullOrEmpty(Department_Code) ? null : (int?)Convert.ToInt32(Department_Code);
int? Job_Code_As_Int = string.IsNullOrEmpty(Job_Code) ? null : (int?)Convert.ToInt32(Job_Code);
int? Branch_Code_As_Int = string.IsNullOrEmpty(Branch_Code) ? null : (int?)Convert.ToInt32(Branch_Code);
int? Level_Job_Code_As_Int = string.IsNullOrEmpty(Level_Job_Code) ? null : (int?)Convert.ToInt32(Level_Job_Code);
int? Type_Of_Workers_Code_As_Int = string.IsNullOrEmpty(Type_Of_Workers_Code) ? null : (int?)Convert.ToInt32(Type_Of_Workers_Code);
int? RelationShip_Code_As_Int = string.IsNullOrEmpty(RelationShip_Code) ? null : (int?)Convert.ToInt32(RelationShip_Code);
int? Vacation_Calculate_Type_Code_As_Int = string.IsNullOrEmpty(Vacation_Calculate_Type_Code) ? null : (int?)Convert.ToInt32(Vacation_Calculate_Type_Code);
int? ConCostmor_Code_As_Int = string.IsNullOrEmpty(ConCostmor_Code) ? null : (int?)Convert.ToInt32(ConCostmor_Code);
int? Insurance_Type_Code_As_Int = string.IsNullOrEmpty(Insurance_Type_Code) ? null : (int?)Convert.ToInt32(Insurance_Type_Code);
int? Nationality_Code_As_Int = string.IsNullOrEmpty(Nationality_Code) ? null : (int?)Convert.ToInt32(Nationality_Code);
int? Gender_Code_As_Int = string.IsNullOrEmpty(Gender_Code) ? null : (int?)Convert.ToInt32(Gender_Code);
DateTime? Date_Hiring_From_As_DateTime = Date_Hiring_From == string.Empty ? null : (DateTime?)Convert.ToDateTime(Date_Hiring_From);
DateTime? Date_Hiring_To_As_DateTime = Date_Hiring_to == string.Empty ? null : (DateTime?)Convert.ToDateTime(Date_Hiring_to);
//
//
SqlParameter P_Employee_Code = new SqlParameter("P_Employee_Code", Employee_Code_As_Int);
SqlParameter P_Administration_Code = new SqlParameter("P_Administration_Code", Administration_Code_As_Int);
SqlParameter P_Department_Code = new SqlParameter("P_Department_Code", Department_Code_As_Int);
SqlParameter P_Job_Code = new SqlParameter("P_Job_Code", Job_Code_As_Int);
SqlParameter P_Branch_Code = new SqlParameter("P_Branch_Code", Branch_Code_As_Int);
SqlParameter P_Level_Job_Code = new SqlParameter("P_Level_Job_Code", Level_Job_Code_As_Int);
SqlParameter P_Type_Of_Worker_Code = new SqlParameter("P_Type_Of_Worker_Code", Type_Of_Workers_Code_As_Int);
SqlParameter P_RelationShip_Code = new SqlParameter("P_RelationShip_Code", RelationShip_Code_As_Int);
SqlParameter P_Vacation_Calcualtion_Code = new SqlParameter("P_Vacation_Calcualtion_Code", Vacation_Calculate_Type_Code_As_Int);
SqlParameter P_ConCustmer_Code = new SqlParameter("P_ConCustmer_Code", ConCostmor_Code_As_Int);
SqlParameter P_Insurance_Type_Code = new SqlParameter("P_Insurance_Type_Code", Insurance_Type_Code_As_Int);
SqlParameter P_Nationality_Code = new SqlParameter("P_Nationality_Code", Nationality_Code_As_Int);
SqlParameter P_Gender_Code = new SqlParameter("P_Gender_Code", Gender_Code_As_Int);
SqlParameter P_Date_Hiring_From = new SqlParameter("P_Date_Hiring_From", Date_Hiring_From_As_DateTime);
SqlParameter P_Date_Hiring_To = new SqlParameter("P_Date_Hiring_To", Date_Hiring_To_As_DateTime);

BindingSource bs = new BindingSource();

//
// R1
if (Report_Number == "1")
{
    var Employee_Data = db.Database.SqlQuery<SR1_Result>("EXEC SR1 @P_Employee_Code,"  
                                                  "@P_Administration_Code , @P_Department_Code ,"  
                                                  "@P_Job_Code , @P_Branch_Code , "  
                                                  "@P_Level_Job_Code," 
                                                  "@P_Type_Of_Worker_Code, @P_RelationShip_Code," 
                                                  "@P_Vacation_Calculation_Code, @P_ConCustmer_Code," 
                                                  "@P_Insurance_Type_Code , @P_Nationality_Code," 
                                                  "@P_Gender_Code, @P_Date_Hiring_From,@P_Date_Hiring_To" ,
                                                  P_Employee_Code , P_Administration_Code, P_Department_Code ,
                                                  P_Job_Code, P_Branch_Code, P_Level_Job_Code, P_Type_Of_Worker_Code,
                                                  P_RelationShip_Code, P_Vacation_Calculation_Code, P_ConCustomer_Code,
                                                  P_Insurance_Type_Code, P_Nationality_Code, P_Gender_Code,
                                                  P_Date_Hiring_From, P_Date_Hiring_To)
        

        .Select(u => new
        {
            u.EmployeeCode,
            u.EmployeeName,
            u.JobName,
            u.Date_Hiring,
            u.AdministrationName,
            u.DepartmentName,
            u.BranchName,
        })
        .ToList();
    bs.DataSource = Employee_Data;
  

И это моя хранимая процедура

 ALTER PROCEDURE [dbo].[SR1]
  @P_Employee_Code int = NULL,
  @P_Administration_Code tinyint = NULL,
  @P_Department_Code tinyint = NULL,
  @P_Jop_Code smallint = NULL,
  @P_Pranch_Code tinyint = NULL,
  @P_Level_Jop_Code tinyint = NULL,
  @P_Type_Of_Worker_Code tinyint = NULL,
  @P_RelationShip_Code tinyint = NULL,
  @P_Vacation_Calcualtion_Code tinyint = NULL,
  @P_ConCustmer_Code tinyint = NULL,
  @P_Insurance_Type_Code tinyint = NULL,
  @P_Nationality_Code tinyint = NULL,
  @P_Gender_Code tinyint = NULL,
  @P_Date_Hiring_From datetime = NULL,
  @P_Date_Hiring_To datetime = NULL
AS
BEGIN
    SELECT         
        EmployeeCode,EmployeeName,
        JobName,
        Date_Hiring,
        Nat_Salary,
        AdministrationName,
        DepartmentName,
        BranchName
    FROM            
        Employee_List_Code_Name_Jop_DateHiring s
    WHERE
        (s.EmployeeCode = @P_Employee_Code OR @P_Employee_Code IS NULL)
        AND
        (s.AdministrationCode = @P_Administration_Code OR @P_Administration_Code IS NULL)
        AND 
        (s.DepartmentCode = @P_Department_Code OR @P_Department_Code IS NULL)
        AND 
        (s.JobCode = @P_Job_Code OR @P_Job_Code IS NULL)
        AND
        (s.BranchCode = @P_Branch_Code OR @P_Branch_Code IS NULL)
        AND
        (s.JobLevelCode = @P_Level_Job_Code OR @P_Level_Job_Code IS NULL)
        AND 
        (s.TypeOfWorkersCode = @P_Type_Of_Worker_Code OR @P_Type_Of_Worker_Code IS NULL)
        AND 
        (s.RelationShipCode = @P_RelationShip_Code OR @P_RelationShip_Code IS NULL)
        AND
        (s.Vacation_Calculate_Type_Code = @P_Vacation_Calculation_Code OR  @P_Vacation_Calculation_Code IS NULL)
        AND 
        (@P_ConCustomer_Code IS NULL OR S.ConCustmerCode = @P_ConCustomer_Code)
        AND 
        (@P_Insurance_Type_Code is null or S.Insurance_Code =@P_Insurance_Type_Code)
      AND     
      (s.GenderCode=@P_Gender_Code or @P_Gender_Code is null)
      AND
      (s.NationalityCode=@P_Nationality_Code or @P_Nationality_Code is null)
      AND 
      (@P_Date_Hiring_From is null or @P_Date_Hiring_To is null) or S.Date_Hiring BETWEEN @P_Date_Hiring_From AND @P_Date_Hiring_To
       
 
END 



  

И это класс хранимых процедур

 public partial class SR1_Result
{
    public int EmployeeCode { get; set; }
    public string EmployeeName { get; set; }
    public string JobName { get; set; }
    public Nullable<System.DateTime> Date_Hiring { get; set; }
    public Nullable<double> Nat_Salary { get; set; }
    public string AdministrationName { get; set; }
    public string DepartmentName { get; set; }
    public string BranchName { get; set; }
}
  

Как я могу исправить эту ошибку?

Вы не можете передать значение C # null в SqlParameter , вам нужно передать System.DBNull.Value . Итак

 SqlParameter P_Employee_Code = new SqlParameter("P_Employee_Code", Employee_Code_As_Int);
  

Должно быть

 SqlParameter P_Employee_Code = new SqlParameter("@P_Employee_Code", SqlDbType.Int) { Value = Employee_Code_As_Int ?? (object)System.DBNull.Value};
  

Обратите внимание на то, что рекомендуется использовать этот метод, который указывает точный тип данных, а не полагаться на автоматическое обнаружение.

Также рекомендуется включать @ символ.

Я создал методы расширения для обработки этого, вот те, для int и int?

 public static SqlParameter ToSqlIntParameter(this int value, string name)
{
    return new SqlParameter(name.IndexOf("@") > -1 ? name : "@"   name, SqlDbType.Int) { Value = value };
}

public static SqlParameter ToSqlIntParameter(this int? value, string name)
{
    return new SqlParameter(name.IndexOf("@") > -1 ? name : "@"   name, SqlDbType.Int) { Value = value ?? (object)DBNull.Value };
}
  

Поэтому при использовании этих ваш код станет:

 SqlParameter P_Employee_Code = Employee_Code_As_Int.ToSqlIntParameter("@P_Employee_Code");
  

Я создал этот метод, и он работает хорошо:

Создайте класс для преобразования null значения в DBNull.Value .

 public static object GetDataValue(object value)
{
    if (value == null)
    {
        return DBNull.Value;
    }

    return value;
}
  

и используйте этот SqlParameter:

 SqlParameter P_Employee_Code = new SqlParameter("@P_Employee_Code",GetDataValue(Employee_Code_As_Int));