将参数值（有时为空值）传递给存储过程

本文介绍了将参数值（有时为空值）传递给存储过程的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我想传递参数值有时会将null值存入存储过程，我存储的procuder看起来像这样

i want to pass parameter value sometimes get null value into stored procedure,my stored procuder look like this

USE [Stock] GO ALTER Proc [dbo].[Add_ItemQte] @ID_Item int, @ID_Supplier int, @Qantity_ItemQte int, @Date_ItemQte date, @Lenght_ItemQte int, @Widht_ItemQte int, @WightT_ItemQte float, @ID_Location int, @ID_Project int=null AS INSERT INTO ItemQuantity ([ID_Item] ,[ID_Supplier] ,[Qantity_ItemQte] ,[Date_ItemQte] ,[Lenght_ItemQte] ,[Widht_ItemQte] ,[WightT_ItemQte] ,[ID_Location] ,[ID_Project]) VALUES (@ID_Item ,@ID_Supplier ,@Qantity_ItemQte ,@Date_ItemQte ,@Lenght_ItemQte ,@Widht_ItemQte ,@WightT_ItemQte ,@ID_Location ,@ID_Project)

我的c＃代码看起来像这样

my c# code look like this

public void Add_ItemQte(int Name, int Supplier, int Qantity, DateTime Date, int Lenght,int Widht,double WightT,int Location, Nullable<int> Project) { DAL.DataAccessLayer DAL = new DAL.DataAccessLayer(); DAL.Open(); SqlParameter[] param = new SqlParameter[9]; param[0] = new SqlParameter("@ID_Item", SqlDbType.Int); param[0].Value = Name; param[1] = new SqlParameter("@ID_Supplier", SqlDbType.Int); param[1].Value = Supplier; param[2] = new SqlParameter("@Qantity_ItemQte", SqlDbType.Int); param[2].Value = Qantity; param[3] = new SqlParameter("@Date_ItemQte", SqlDbType.Date); param[3].Value = Date; param[4] = new SqlParameter("@Lenght_ItemQte", SqlDbType.Int); param[4].Value = Lenght; param[5] = new SqlParameter("@Widht_ItemQte", SqlDbType.Int); param[5].Value = Widht; param[6] = new SqlParameter("@WightT_ItemQte", SqlDbType.Float); param[6].Value = WightT; param[7] = new SqlParameter("@ID_Location", SqlDbType.Int); param[7].Value = Location; param[8] = new SqlParameter("@ID_Project", SqlDbType.Int); param[8].Value = Project; DAL.ExcuteCommande("Add_ItemQte", param); DAL.Close(); }

我使用此代码保存我的数据

and i use this code to save my data

for (int i=0;i<gridView1.RowCount;i++) { prd.Add_ItemQte(Convert.ToInt32(gridView1.GetRowCellValue(i, "IDNom")), Convert.ToInt32(gridView1.GetRowCellValue(i, "IDFournisseur")), Convert.ToInt32(gridView1.GetRowCellValue(i, "Quantité")), Convert.ToDateTime(gridView1.GetRowCellValue(i, "Date")), Convert.ToInt32(gridView1.GetRowCellValue(i, "Longueur")), Convert.ToInt32(gridView1.GetRowCellValue(i, "Largeur")), Convert.ToDouble(gridView1.GetRowCellValue(i, "Poids Total")), Convert.ToInt32(gridView1.GetRowCellValue(i, "IDLocalisation")), Convert.ToInt32(gridView1.GetRowCellValue(i, "IDProjet"))); }

当IDProjet列具有代码运行且没有错误的值但是当它不包含任何值时我得到此错误：对象不能从DBNull转换为其他类型。如果值为null，我不想保存0，因为我将根据该列进行一些过滤，如何解决此问题，提前谢谢。 我尝试过： i尝试使用Nullable< int>但是没有为我工作

when IDProjet column have a value the code run without error but when it does not contain any value i get this error:Object cannot be cast from DBNull to other types.I do not want to save 0 if the value is null because i will do some filtering depending on that column, how i can solve this problem,thanks in advance. What I have tried: i try to use Nullable<int> but did not work for me

推荐答案

我得到了Steve的帮助，这就是解决方案 I get help from Steve and this is the solution for (int i=0;i<gridView1.RowCount;i++) { object value = gridView1.GetRowCellValue(i, "IDProjet"); int? prjID = null; if(value != null && Int32.TryParse(value.ToString(), out int temp)) prjID = temp; prd.Add_ItemQte(...., prjID); }

param[8] = new SqlParameter("@ID_Project", SqlDbType.Int); param[8].Value = Project.HasValue ? Project : (object)DbNull.Value;